ビューの作成 - AnalyticDB - Alibaba Cloud ドキュメントセンター

ビューは、1つ以上のテーブルからのクエリの結果で構成されるが、実際のデータを格納しない仮想テーブルです。ビューは複雑なクエリを簡素化し、データセキュリティを強化します。このトピックでは、CREATE VIEWステートメントを実行してビューを作成する方法について説明します。

使用上の注意

AnalyticDB for MySQLとMySQL間のビューの互換性:

3.1.9.0より前のバージョン
AnalyticDB for MySQLはMySQLのデフォルト動作と互換性がありません。ビューに列が追加または削除された場合、システムはSELECT * from <view_name>; ステートメントの応答から列数の変更を識別し、ビューを使用できないと判断し、次のエラーを報告します。view '<view_name>' is stale; it must be recreated.
V3.1.9.0以降
AnalyticDB for MySQLは、MySQLのデフォルト動作と互換性があります。 CREATE VIEWステートメントを使用してビューを作成すると、AnalyticDB for MySQLは、ステートメント内のアスタリスク (*) を解析した後のステートメントを特定の列に保存します。この場合、列を追加または削除するときにエラーは発生しません。

MySQLのデフォルト動作との互換性を確保するために、V3.1.9.0以降のAnalyticDB for MySQLクラスターを使用してビューを作成することを推奨します。これにより、あいまいなセマンティクスやエラーなど、アスタリスク (*) によって引き起こされる予期しない問題が防止されます。

説明

AnalyticDB for MySQLのマイナーバージョンを照会するには

Data Lakehouse Editionクラスターで、SELECT adb_version(); 文を実行します。クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。

V3.1.9.0以降のAnalyticDB for MySQLクラスターとMySQLの動作との互換性は、特別な影響を与える可能性があります。たとえば、列Cの名前を列Dに変更すると、列A、列B、および列Cを参照しているときに列Cが見つからず、エラーが報告されます。これは、SQL構文のチェックと認証が実行される解析ステージが、列が削除される最適化ステージよりも早いため、期待される結果です。列Cがクエリに使用されているかどうかに関係なく、解析段階でエラーが報告されます。 3.1.9.0より前のバージョンのAnalyticDB for MySQLでは、ビュー内の列数がアスタリスク (*) で参照されている列数と同じかどうかのみがチェックされます。この場合、エラーは報告されず、システムは列Cをベーステーブルの3番目の列にマッピングします。 RENAME操作後に列Cをクエリしても、エラーは報告されません。これは予想外の結果である。

3.1.9.0より前のバージョンのAnalyticDB for MySQLクラスターの特別な動作との互換性が必要な場合は、CREATE VIEWステートメントにヒントを追加するか、特定のグローバルパラメーターを設定できます。

1つのビューのCREATE VIEWステートメントに、/* + LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false */ のヒントを追加します。例：
```
/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;
```
すべてのビューに必要な設定を適用するには、次のグローバルパラメーターを設定します。SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;

構文

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

オプション	必須 / 任意	説明
`または交換`	非対象	既存のビューが同じ名前を使用するかどうかに基づいてビューを作成します。次のルールが適用されます。同じ名前を使用する既存のビューがない場合、AnalyticDB for MySQLはビューを作成します。既存のビューが同じ名前を使用する場合、AnalyticDB for MySQLは既存のビューを削除し、ビューを作成します。説明このオプションを指定しない場合、既存のビューが同じ名前を使用している場合、ビューは作成できません。
`[SQLセキュリティ]`	非対象	ビューからデータを照会するときのセキュリティ認証の方法を定義します。有効な値： `INVOKER`: `呼び出し元`の権限を使用してSQLステートメントを実行します。この認証方法を使用すると、ビューからデータを照会するときに、インボーカーに次の権限があるかどうかをシステムで確認できます。ビューを照会する権限ビューによって参照されるオブジェクトを照会する権限前述の権限を持つインボーカーだけが、ビューからデータをクエリできます。 `DEFINER`: `definer`の権限を使用してSQL文を実行します。この認証方法を使用すると、ビューからデータを照会するときに、インボーカと定義者が次の権限を持っているかどうかをシステムで確認できます。ビューをクエリする権限 (インボーカーによる) ビューによって参照されるオブジェクトを照会する権限 (定義者による) 定義者の権限が取り消されると、インボーカーがビューを照会する権限を持っていても、ビューからデータを照会することはできません。説明このオプションを指定しない場合、AnalyticDB for MySQLのデフォルトの認証方法として`INVOKER`が使用されます。インボーカーがビューからデータを照会する場合、インボーカーにはビューとビューによって参照されるオブジェクトを照会する権限が必要です。このオプションは、V3.1.4.0以降のAnalyticDB for MySQLクラスターで使用できます。 AnalyticDB For MySQLクラスターのマイナーバージョンを照会する方法については、クラスターのマイナーバージョンを表示するにはどうすればよいですか。をご参照ください。クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
`view_name`	対象	ビューの名前。説明ビューに名前を付けるときに、ビューの名前の前にビューを含むデータベースの名前を追加することもできます。例: `adb_demo.view` データベースを指定しない場合、ビューは現在のデータベースに作成されます。
`select_statement`	対象	ビューのデータソース。

例

データの準備
AnalyticDB for MySQLクラスターの特権アカウントを使用して、次の操作を実行します。
1. user1という名前のアカウントを作成します。
```
CREATE USER user1 IDENTIFIED BY 'user1_pwd';
```
2. adb_demoという名前の既存のデータベースにt1という名前のテーブルを作成します。
```
Create Table `t1` (
 `id` bigint AUTO_INCREMENT,
 `id_province` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`);
```
  テストデータをt1テーブルに挿入します。
```
INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');
```
ビューの作成
説明
次の例は、ビューを作成するときに異なる認証方法を指定する方法を示しています。ビューのデータはt1テーブルから取得されます。
- SQLセキュリティをINVOKERに設定して、v1という名前のビューを作成します。
```
CREATE SQL SECURITY INVOKER VIEW v1 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- SQLセキュリティをDEFINERに設定して、v2という名前のビューを作成します。
```
CREATE SQL SECURITY DEFINER VIEW v2 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- v3という名前のビューを作成し、SQL SECURITYオプションを指定しないでください。この場合、INVOKER認証方式が使用されます。
```
CREATE VIEW v3 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
アクセス許可の比較
- 特権アカウントを使用して、user1アカウントに3つのビューを照会する権限を付与します。
```
GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
```
  この場合、user1アカウントを使用してAnalyticDB for MySQLクラスターのadb_demoデータベースに接続した後、user1アカウントを使用してv2ビューからのみデータを照会できます。
```
SELECT * FROM v2;
```
  次の応答が返されます。
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```
  SELECTステートメントを使用してv1またはv3ビューからデータをクエリすると、エラーが返されます。
```
SELECT * FROM v1;
```
  または
```
SELECT * FROM v3;
```
  前述のSELECTステートメントのいずれかを実行すると、次のエラーメッセージが返されます。
```
ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
```
- user1アカウントに3つのビューを照会する権限が付与されたら、特権アカウントを使用してuser1アカウントにt1テーブルを照会する権限を付与します。
```
GRANT SELECT ON adb_demo.t1 to user1@'%';
```
  この場合、user1アカウントを使用してAnalyticDB for MySQLクラスターのadb_demoデータベースに接続した後、user1アカウントを使用してv1、v2、およびv3ビューのデータを照会できます。
```
SELECT * FROM v1;
```
  または
```
SELECT * FROM v2;
```
  または
```
SELECT * FROM v3;
```
  上記のSELECTステートメントのいずれかを実行すると、次の結果が返されます。
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```

よくある質問

列名は、実表で小文字で設定されます。ビュー結果セットで列名が大文字で表示されるのはなぜですか?

デフォルトでは、AnalyticDB for MySQLビュー結果セットの列名は大文字と小文字が区別されません。ビュー結果セットで列名を小文字で表示する場合は、次のステートメントを実行してVIEW_OUTPUT_NAME_CASE_SENSITIVEパラメーターをtrueに設定できます。

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

ベストプラクティス

詳細については、「ビューを使用した権限管理」をご参照ください。

使用上の注意

構文

例

よくある質問

列名は、実表で小文字で設定されます。 ビュー結果セットで列名が大文字で表示されるのはなぜですか?

ベストプラクティス

列名は、実表で小文字で設定されます。ビュー結果セットで列名が大文字で表示されるのはなぜですか?