カテゴリ：

テーブル、ビュー、シーケンス DDL

CREATE VIEW

1つ以上の既存のテーブル（またはその他の有効なクエリ式）のクエリに基づいて、現在のスキーマまたは指定されたスキーマに新しいビューを作成します。

構文

CREATE [ OR REPLACE ] [ SECURE ] [ RECURSIVE ] VIEW [ IF NOT EXISTS ] <name>
  [ ( <column_list> ) ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

必須パラメーター

名前

ビューの識別子を指定します。ビューが作成されるスキーマに対して一意である必要があります。

また、識別子はアルファベット文字で始まる必要があり、識別子文字列全体が二重引用符で囲まれていない限り、スペースや特殊文字を含めることはできません（例： "My object"）。二重引用符で囲まれた識別子も大文字と小文字が区別されます。

詳細については、 識別子の要件 をご参照ください。

ステートメントの選択

ビューの作成に使用されるクエリを指定します。1つ以上のソーステーブルまたはその他の有効な SELECT ステートメントに配置できます。このクエリは、ビューのテキスト/定義として機能し、 SHOW VIEWS 出力と VIEWS 情報スキーマビューに表示されます。

オプションのパラメーター

SECURE

ビューが安全であることを指定します。セキュアビューの詳細については、 安全なビューの使用 をご参照ください。

デフォルト：値なし（ビューは安全ではない）

RECURSIVE

ビューが必ずしも CTE （共通テーブル式）を使用せずに再帰構文を使用してそれ自体を参照できることを指定します。一般的な再帰ビュー、および特に RECURSIVE キーワードの詳細については、 再帰的ビュー（非マテリアライズドビューのみ） および以下の再帰ビューの例をご参照ください。

デフォルト: 値なし（ビューは再帰的ではない、または CTE を使用することによってのみ再帰的）

列リスト

列の名前を変更したり、新しいビューの列にコメントを追加したりする場合は、列名と（必要に応じて）列に関するコメントを指定する列リストを含めます。（列のデータ型を指定する必要なし。）

ビューの列のいずれかが式（単純な列名だけでなく）に基づいている場合、ビューの各列に列名を指定する必要があります。たとえば、次の場合には列名が必要です。

CREATE VIEW v1 (pre_tax_profit, taxes, after_tax_profit) AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

列ごとにオプションのコメントを指定できます。例:

CREATE VIEW v1 (pre_tax_profit COMMENT 'revenue minus cost',
                taxes COMMENT 'assumes taxes are a fixed percentage of profit',
                after_tax_profit)
    AS
    SELECT revenue - cost, (revenue - cost) * tax_rate, (revenue - cost) * (1.0 - tax_rate)
    FROM table1;

コメントは、列名がわかりにくい場合に特に役立ちます。

コメントを表示するには、 DESCRIBE VIEW を使用します。

COPY GRANTS

OR REPLACE 句を使用して新しいビューが作成されるときに、元のビューのアクセス許可を保持します。

このパラメーターは、既存のビューから新しいビューに OWNERSHIP を 除く すべての権限をコピーします。新しいビューは、スキーマ内のオブジェクトタイプに定義された将来の許可を 継承しません。デフォルトでは、 CREATE VIEW ステートメントを実行するロールが新しいビューを所有します。

パラメーターが CREATE VIEW ステートメントに含まれていない場合、新しいビューは元のビューに付与された明示的なアクセス権を継承 しません が、スキーマ内のオブジェクトタイプに定義された将来の権限を継承します。

許可をコピーする操作は、 CREATE VIEW ステートメントで（つまり、同じトランザクション内で）アトミックに発生することに注意してください。

デフォルト：値なし（付与はコピーされない）

COMMENT = '文字列リテラル'

ビューのコメントを指定します。

デフォルト：値なし

使用上の注意

• ビュー定義には、 ORDER BY 句（例： create view v1 as select * from t1 ORDER BY column1）を含めることができます。ただし、Snowflakeは、ほとんどのビュー定義から ORDER BY 句を 除外 することをお勧めします。ビューが並べ替えの恩恵を受けないコンテキストで使用される場合、 ORDER BY 句は不要なコストを追加します。たとえば、ビューが結合で使用され、結合列が ORDER BY 列と同じでない場合、ビューの結果を並べ替えるための追加コストは通常無駄になります。クエリ結果を並べ替える必要がある場合は、通常、ビュー自体ではなく、ビューを使用するクエリで ORDER BY を指定する方が効率的です。

• ビュー定義は動的ではありません。特に列がドロップされたときなど、基になるソースがビュー定義と一致しないように変更された場合、ビューは自動的に更新されません。例：

  • ソーステーブルの特定の列を参照するビューが作成され、その後その列はテーブルからドロップされます。

  • ビューはテーブルの SELECT * を使用して作成され、その後、列はテーブルからドロップされます。

  これらのシナリオのいずれかで、ビューをクエリすると、列の不一致エラーが返されます。

• ビューのソーステーブルがドロップされた場合、ビューのクエリは object does not exist エラーを返します。

• スキーマには、同じ名前のテーブルとビューを含めることはできません。同じ名前のテーブルがスキーマに既に存在する場合、 CREATE VIEW ステートメントはエラーを生成します。

• ビューが作成されると、テーブルおよびその他のデータベースオブジェクトへの 非修飾 参照は、セッションの現在のスキーマではなく、 ビューのスキーマで 解決されます。同様に、部分的に修飾されたオブジェクト（つまり、schema.object）は、セッションの現在のデータベースではなく、ビューのデータベースで解決されます。

  SEARCH_PATH セッションパラメーター（存在する場合）は無視されます。

• COPY GRANTS を使用して、

  • データ共有：

    • 既存のセキュリティ保護されたビューが別のアカウントと共有されていた場合、置換ビューも共有されます。

    • 既存の保護されたビューがデータコンシューマとしてアカウントと共有され、アクセスがアカウント内の他のロールにさらに許可された場合（親データベースの GRANT IMPORTED PRIVILEGES を使用）、置換ビューにもアクセスが許可されます。

  • 置換ビューの SHOW GRANTS 出力には、コピーされた権限の被付与者が、ステートメントが実行されたときの現在のタイムスタンプとともに、 CREATE VIEW ステートメントを実行したロールとしてリストされます。

• ビューを作成し、そのビューに対する権限をロールに付与すると、ロールは、ビューがアクセスする基になるテーブルに対する権限を持っていなくても、そのビューを使用できます。つまり、ビューを作成して、ロールにテーブルのサブセットのみへのアクセス権を付与できます。例えば、同じテーブルの医療診断情報ではなく医療請求情報にアクセスするビューを作成できます。次に、そのビューの権限を「会計士」ロールに付与して、会計士が患者の診断を見ずに請求情報を確認できるようにします。

• 設計上、 SHOW VIEWS コマンドはセキュアビューに関する情報を提供しません。安全なビューに関する情報を表示するには、Information Schemaの VIEWS ビューを使用し、ビューを所有するロールを使用する必要があります。

• 再帰的ビューは列名リストを提供する必要があります。

• 再帰的ビューを定義するときは、無限再帰を防止してください。再帰的ビュー定義の WHERE 句は、通常、データの階層の最後のレベルを処理した後にデータを使い果たすことにより、再帰を最終的に停止できるようにする必要があります。

移植ノート

• 一部のベンダーは FORCE キーワードをサポートしています。

  CREATE OR REPLACE FORCE VIEW ...
  

  Snowflakeは FORCE キーワードを受け入れますが、サポートはしていません。つまり、このキーワードを使用しても構文エラーは発生しませんが、基になるデータベースオブジェクト（テーブルまたはビュー）がまだ存在しない場合は、 FORCE を使用しても強制的にビューを作成しません。存在しないテーブルまたはビューのビューを作成しようとすると、 FORCE キーワードが使用されていてもエラーメッセージが表示されます。

• ビューでテーブルを検索するとき、一部のベンダーはアクティブなスキーマで修飾されていないテーブル名を検索します。Snowflakeは、 ビューとと同じスキーマ内 で、修飾されていないテーブル名を検索します。Snowflakeに移植するときは、完全修飾テーブル名を使用するようにビューを更新することを検討してください。

例

現在のスキーマに、テーブルからすべての行を選択するコメント付きのビューを作成します。

CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable;

SHOW VIEWS;

+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+
| created_on                      | name              | reserved | database_name | schema_name | owner    | comment   | text                                                                     |
|---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------|
| Thu, 19 Jan 2017 15:00:37 -0800 | MYVIEW            |          | MYTEST1       | PUBLIC      | SYSADMIN | Test view | CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

次の例は、ビューが安全であることを除いて、前の例と同じです。

CREATE OR REPLACE SECURE VIEW myview COMMENT='Test secure view' AS SELECT col1, col2 FROM mytable;

SELECT is_secure FROM information_schema.views WHERE view_name = 'MYVIEW';

以下に、再帰ビューを作成する2つの方法を示します。

まず、テーブルを作成してロードします。

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

再帰的な CTEを使用してビューを作成し、ビューをクエリします。

CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
   WITH RECURSIVE employee_hierarchy_cte (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_cte.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_cte.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_cte
       WHERE employee_hierarchy_cte.employee_ID = employees.manager_ID
   )
   SELECT * 
      FROM employee_hierarchy_cte
);

SELECT * 
    FROM employee_hierarchy 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL | NULL                        | President                  |
| Vice President Engineering |          10 |          1 | 1                           | President                  |
| Vice President HR          |          20 |          1 | 1                           | President                  |
| Programmer                 |         100 |         10 | 10                          | Vice President Engineering |
| QA Engineer                |         101 |         10 | 10                          | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | 20                          | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

キーワード RECURSIVEを使用してビューを作成し、ビューをクエリします。

CREATE RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

SELECT * 
    FROM employee_hierarchy_02 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL | NULL                        | President                  |
| Vice President Engineering |          10 |          1 | 1                           | President                  |
| Vice President HR          |          20 |          1 | 1                           | President                  |
| Programmer                 |         100 |         10 | 10                          | Vice President Engineering |
| QA Engineer                |         101 |         10 | 10                          | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | 20                          | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+