CREATE VIEW - AnalyticDB - Alibaba Cloud Documentation Center

View adalah tabel virtual yang dibuat berdasarkan hasil kueri dari satu atau beberapa tabel. View tidak menyimpan data aktual, melainkan menyediakan cara untuk menyederhanakan kueri kompleks dan meningkatkan keamanan data. Topik ini menjelaskan cara membuat view menggunakan pernyataan CREATE VIEW.

Catatan penggunaan

AnalyticDB for MySQL memiliki perilaku view yang kompatibel dengan MySQL sebagai berikut:

Versi sebelum 3.1.9.0

Kluster AnalyticDB for MySQL tidak mendukung perilaku default MySQL. Setelah Anda menambahkan atau menghapus kolom pada sebuah view, kueri terhadap view tersebut menggunakan SELECT * FROM <view_name>; tidak konsisten dengan MySQL. Sistem mendeteksi ketidaksesuaian jumlah kolom dan menganggap view tersebut tidak tersedia, sehingga mengembalikan error berikut: View '<view_name>' is stale; it must be re-created.
V3.1.9.0 dan seterusnya

Kluster AnalyticDB for MySQL mendukung perilaku default MySQL. Saat membuat view, kluster memperluas * dalam pernyataan SQL menjadi daftar kolom eksplisit sebelum menyimpannya. Dengan demikian, penambahan atau penghapusan kolom selanjutnya tidak memengaruhi view tersebut.

Oleh karena itu, kami merekomendasikan penggunaan kluster yang menjalankan V3.1.9.0 atau versi lebih baru untuk membuat view. Hal ini memastikan kompatibilitas dengan perilaku default MySQL serta menghindari semantik dan error tak terduga akibat penggunaan * dalam view.

Catatan

Untuk melihat versi minor kluster Data Lakehouse Edition, jalankan SELECT adb_version();. Untuk melakukan upgrade versi minor, hubungi dukungan teknis.

Pada kluster AnalyticDB for MySQL yang menjalankan V3.1.9.0 atau versi lebih baru dan kompatibel dengan perilaku MySQL, efek samping dapat terjadi dalam kasus khusus. Misalnya, jika Anda mengganti nama kolom dari C menjadi D dan view tersebut perlu mereferensikan kolom A, B, dan C secara akurat, maka view tersebut menjadi tidak tersedia dan mengembalikan error karena kolom C tidak lagi ada. Ini merupakan perilaku yang diharapkan. Error tetap dikembalikan meskipun kueri pada akhirnya tidak menggunakan kolom C, karena pemangkasan kolom (column pruning) terjadi selama fase optimasi, sedangkan pemeriksaan sintaksis SQL dan verifikasi izin dilakukan selama fase penguraian (parsing). Namun, pada versi sebelum 3.1.9.0, semua pemeriksaan lolos tanpa error karena hanya jumlah kolom view yang diperiksa—jumlah tersebut tetap sama setelah operasi penggantian nama—dan jumlah kolom yang direferensikan oleh * saat ini tetap konsisten. Setelah pemeriksaan ketersediaan view lolos, kolom C dipetakan ke kolom ketiga dalam urutan tabel dasar. Akibatnya, meskipun Anda melakukan kueri terhadap kolom C setelah operasi penggantian nama, tidak ada error yang dikembalikan, tetapi hasil kuerinya tidak sesuai harapan.

Jika bisnis Anda memerlukan perilaku khusus kluster AnalyticDB for MySQL yang menjalankan versi sebelum 3.1.9.0, Anda dapat menambahkan petunjuk (hint) tertentu atau mengonfigurasi pengaturan saat membuat view untuk mencapai perilaku yang diharapkan.

Untuk mengonfigurasi satu view, tambahkan /*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/. Contoh berikut menunjukkan cara menambahkan hint tersebut:
```
/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;
```
Untuk mengonfigurasi semua view dalam kluster, jalankan pernyataan berikut: SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;

Sintaksis

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

Parameter	Wajib	Deskripsi
`OR REPLACE`	Tidak	Membuat view menggunakan aturan yang ditentukan berdasarkan apakah view dengan nama yang sama sudah ada. Aturannya sebagai berikut: Jika tidak ada view dengan nama yang sama, AnalyticDB for MySQL langsung membuat view baru. Jika view dengan nama yang sama sudah ada, AnalyticDB for MySQL menghapus view yang ada lalu membuat yang baru. Catatan Jika parameter ini tidak ditentukan dan view dengan nama yang sama sudah ada, pembuatan view akan gagal.
`[SQL SECURITY]`	Tidak	Metode verifikasi keamanan yang digunakan saat data dalam view dikueri. Nilai yang valid: `INVOKER`: menjalankan pernyataan SQL kueri sebagai `INVOKER` (pemanggil). Dengan metode verifikasi keamanan ini, sistem memverifikasi apakah pemanggil memiliki izin berikut saat mengakses data Tampilan: Izin kueri pada view tersebut. Izin kueri pada objek yang direferensikan oleh view tersebut. Data view hanya dapat dikueri jika pemanggil memiliki kedua izin tersebut. `DEFINER`: menjalankan pernyataan SQL kueri sebagai `DEFINER` (pendefinisi). Dengan metode verifikasi keamanan ini, sistem memverifikasi apakah pemanggil dan pendefinisi memiliki izin berikut saat data view dikueri: Pemanggil memiliki izin kueri pada view tersebut. Pendefinisi memiliki izin kueri pada objek yang direferensikan oleh view tersebut. Setelah izin pendefinisi dicabut, view tidak dapat dikueri meskipun pemanggil masih memiliki izin kueri pada view tersebut. Catatan Jika parameter ini tidak ditentukan, AnalyticDB for MySQL secara default menggunakan metode verifikasi keamanan `INVOKER`. Dalam hal ini, pemanggil harus memiliki izin kueri pada view maupun pada objek yang direferensikan oleh view tersebut. Parameter ini hanya didukung oleh kluster AnalyticDB for MySQL yang menjalankan V3.1.4.0 atau versi yang lebih baru. Untuk melihat versi minor kluster, lihat 如何查看实例版本信息. Untuk melakukan upgrade versi minor, hubungi dukungan teknis.
`view_name`	Ya	Nama view tersebut. Catatan Anda juga dapat menambahkan nama database sebelum nama view untuk menentukan database tempat view tersebut berada, misalnya `adb_demo.view`. Jika Anda tidak menambahkan nama database, view tersebut secara default berada di database saat ini.
`select_statement`	Ya	Sumber data view tersebut.

Contoh

Siapkan data uji

Gunakan akun istimewa kluster AnalyticDB for MySQL untuk melakukan operasi berikut:

Buat akun bernama user1:

CREATE USER user1 IDENTIFIED BY 'user1_pwd';

Buat database bernama adb_demo dan tabel bernama t1 di dalam database tersebut. Pernyataan berikut membuat tabel t1:

Create Table `t1` (
 `id` bigint AUTO_INCREMENT,
 `id_province` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`);

Masukkan data uji ke dalam tabel t1:

INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');

Buat Tampilan

Catatan
Contoh berikut membuat view atas tabel t1 dengan metode verifikasi keamanan yang berbeda untuk menunjukkan perbedaan efek izin antara DEFINER dan INVOKER.
- Untuk membuat view bernama v1 dan mengatur SQL SECURITY ke INVOKER, jalankan pernyataan berikut:
```
CREATE SQL SECURITY INVOKER VIEW v1
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- Untuk membuat view bernama v2 dan mengatur SQL SECURITY ke DEFINER, jalankan pernyataan berikut:
```
CREATE SQL SECURITY DEFINER VIEW v2
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- Untuk membuat view bernama v3 tanpa menentukan SQL SECURITY (dalam hal ini, sistem secara default menggunakan INVOKER), jalankan pernyataan berikut:
```
CREATE VIEW v3
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
Bandingkan izin
- Gunakan akun istimewa untuk memberikan izin kepada user1 hanya untuk mengkueri ketiga view tersebut:
```
GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
```
  Dalam kasus ini, setelah user1 terhubung ke database adb_demo kluster AnalyticDB for MySQL, user1 hanya dapat mengkueri view v2. Pernyataan kueri:
```
SELECT * FROM v2;
```
  Hasil berikut dikembalikan:
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```
  Namun, error dikembalikan saat user1 mengkueri view v1 atau v3. Pernyataan kueri:
```
SELECT * FROM v1
```
  atau
```
SELECT * FROM v3
```
  Kedua pernyataan tersebut mengembalikan error berikut:
```
ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
```
- Setelah memberikan izin kepada user1 untuk mengkueri ketiga view tersebut, gunakan akun istimewa untuk memberikan izin kepada user1 untuk mengkueri tabel t1:
```
GRANT SELECT ON adb_demo.t1 to user1@'%';
```
  Dalam kasus ini, setelah user1 terhubung ke database adb_demo kluster AnalyticDB for MySQL, user1 dapat mengkueri semua view v1, v2, dan v3. Pernyataan kueri:
```
SELECT * FROM v1;
```
  atau
```
SELECT * FROM v2;
```
  atau
```
SELECT * FROM v3;
```
  Ketiga pernyataan kueri tersebut mengembalikan hasil yang sama:
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```

FAQ

Nama kolom yang didefinisikan dalam huruf kecil di tabel dasar muncul dalam huruf kapital di set hasil view. Mengapa?

Nama kolom set hasil view AnalyticDB for MySQL secara default tidak peka terhadap huruf besar/kecil (case-insensitive). Jika Anda ingin nama kolom set hasil view ditampilkan dalam huruf kecil, atur nilai VIEW_OUTPUT_NAME_CASE_SENSITIVE menjadi true untuk mengaktifkan sensitivitas huruf besar/kecil. Jalankan pernyataan berikut:

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

Praktik terbaik

Untuk informasi selengkapnya, lihat Pengelolaan Izin Menggunakan Tampilan.