Membuat dan mengelola tabel virtual

Halaman ini menjelaskan cara membuat dan mengelola tampilan Spanner. Untuk mengetahui informasi selengkapnya tentang tampilan Spanner, lihat Tentang tampilan.

Izin

Untuk membuat, memberikan, dan mencabut akses ke tampilan, Anda harus memiliki izin spanner.database.updateDdl.

Membuat tampilan

Untuk membuat tampilan, gunakan pernyataan DDL CREATE VIEW untuk memberi nama tampilan dan memberikan kueri yang menentukannya. Pernyataan ini memiliki dua bentuk:

  • CREATE VIEW menentukan tampilan baru di database saat ini. Jika tampilan bernama view_name sudah ada, pernyataan CREATE VIEW akan gagal.

  • CREATE OR REPLACE VIEW menentukan tampilan baru di database saat ini. Jika tampilan bernama view_name sudah ada, definisinya akan diganti.

Sintaksis untuk pernyataan CREATE VIEW adalah:

{CREATE | CREATE OR REPLACE } VIEW  view_name
SQL SECURITY { INVOKER | DEFINER }
AS query

Karena tampilan adalah tabel virtual, query yang Anda tentukan harus memberikan nama untuk semua kolom dalam tabel virtual tersebut.

Selain itu, Spanner memeriksa query yang Anda tentukan menggunakan resolusi nama yang ketat, yang berarti bahwa semua nama objek skema yang digunakan dalam kueri harus memenuhi syarat sehingga dapat mengidentifikasi satu objek skema secara tidak ambigu. Misalnya, dalam contoh yang mengikuti kolom SingerId dalam tabel Singers harus memenuhi syarat sebagai Singers.SingerId.

Anda harus menentukan SQL SECURITY sebagai INVOKER atau DEFINER dalam pernyataan CREATE VIEW atau CREATE OR REPLACE VIEW. Untuk informasi selengkapnya tentang perbedaan antara kedua jenis keamanan, lihat Tentang tampilan.

Misalnya, asumsikan tabel Singers ditentukan sebagai berikut:

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

Anda dapat menentukan tampilan SingerNames dengan hak pemanggil sebagai berikut:

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Tabel virtual yang dibuat saat tampilan SingerNames digunakan dalam kueri memiliki dua kolom, yaitu SingerId dan Name.

Meskipun definisi tampilan SingerNames ini valid, definisi ini tidak mematuhi praktik terbaik transmisi jenis data untuk memastikan stabilitas di seluruh perubahan skema, seperti yang dijelaskan di bagian berikutnya.

Praktik terbaik saat membuat tampilan

Untuk meminimalkan kebutuhan untuk memperbarui definisi tampilan, secara eksplisit transmisikan jenis data semua kolom tabel dalam kueri yang menentukan tampilan. Saat Anda melakukannya, definisi tampilan dapat tetap valid di seluruh perubahan skema pada jenis kolom.

Misalnya, definisi tampilan SingerNames berikut mungkin menjadi tidak valid karena mengubah jenis data kolom di tabel Singers.

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Anda dapat mencegah tampilan menjadi tidak valid dengan secara eksplisit mentransmisikan kolom ke jenis data yang diinginkan, sebagai berikut:

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

Memberikan dan mencabut akses ke tampilan

Sebagai pengguna kontrol akses terperinci, Anda harus memiliki hak istimewa SELECT pada tampilan. Untuk memberikan hak istimewa SELECT pada tampilan ke peran database:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Untuk mencabut hak istimewa SELECT pada tampilan dari peran database:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Membuat kueri tampilan

Cara membuat kueri hak pemanggil atau tampilan hak penentu sama. Namun, bergantung pada jenis keamanan tampilan, Spanner mungkin perlu atau tidak perlu memeriksa objek skema yang direferensikan dalam tampilan terhadap peran database akun utama yang memanggil kueri.

Membuat kueri tampilan hak pemanggil

Jika tampilan memiliki hak pemanggil, pengguna harus memiliki hak istimewa di semua objek skema dasar tampilan untuk membuat kueri.

Misalnya, jika peran database memiliki akses ke semua objek yang direferensikan oleh tampilan SingerNames, peran tersebut dapat membuat kueri tampilan SingerNames:

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Membuat kueri tampilan hak penentu

Jika tampilan memiliki hak penentu, pengguna dapat membuat kueri tampilan tanpa memerlukan hak istimewa pada objek pokok selama Anda memberikan hak istimewa SELECT pada tampilan tersebut kepada peran yang diperlukan.

Dalam contoh berikut, pengguna dengan peran database Analis ingin membuat kueri tampilan SingerNames. Namun, pengguna ditolak aksesnya karena SingerNames adalah tampilan hak pemanggil dan peran Analyst tidak memiliki akses ke semua objek yang mendasarinya. Dalam hal ini, jika Anda memutuskan untuk memberi Analis akses ke tampilan, tetapi tidak ingin memberinya akses ke tabel Singers, Anda dapat mengganti jenis keamanan tampilan ke hak penentu. Setelah Anda mengganti jenis keamanan tampilan, berikan akses peran Analis ke tampilan. Pengguna kini dapat membuat kueri tabel virtual SingerNames, meskipun tidak memiliki akses ke tabel Singers.

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Mengganti tampilan

Anda dapat mengganti tampilan menggunakan pernyataan CREATE OR REPLACE VIEW untuk mengubah definisi tampilan atau jenis keamanan tampilan.

Mengganti tampilan mirip dengan melepas dan membuat ulang tampilan. Setiap pemberian akses yang diberikan ke tampilan awal harus diberikan lagi setelah mengganti tampilan.

Untuk mengganti tampilan hak pemanggil dengan tampilan hak penentu:

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY DEFINER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Menghapus tampilan

Setelah tampilan dihapus, peran database dengan hak istimewa di dalamnya tidak lagi memiliki akses. Untuk menghapus tampilan, gunakan pernyataan DROP VIEW. 

DROP VIEW SingerNames;

Mendapatkan informasi tentang tampilan

Anda bisa mendapatkan informasi tentang tampilan dalam database dengan membuat kueri tabel dalam skema INFORMATION_SCHEMA-nya.

  • Tabel INFORMATION_SCHEMA.TABLES memberikan nama semua tampilan yang ditentukan.

  • Tabel INFORMATION_SCHEMA.VIEWS menyediakan nama, definisi tampilan, jenis keamanan, dan teks kueri dari semua tampilan yang ditentukan. Pengguna FGAC yang memiliki hak istimewa SELECT pada tampilan dapat mendapatkan informasi tentang tampilan dari tabel INFORMATION_SCHEMA.VIEWS. Pengguna FGAC lainnya memerlukan peran spanner_info_reader jika mereka tidak memiliki hak istimewa SELECT untuk tampilan.

Untuk memeriksa definisi tampilan dan jenis keamanan tampilan yang disebut ProductSoldLastWeek:

  SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = 'ProductSoldLastWeek';