Membuat tampilan terwujud

Dokumen ini menjelaskan cara membuat tampilan terwujud di BigQuery. Sebelum membaca dokumen ini, pahami Pengantar tampilan terwujud.

Sebelum memulai

Berikan peran Identity and Access Management (IAM) yang memberi pengguna izin yang diperlukan untuk melakukan setiap tugas dalam dokumen ini.

Izin yang diperlukan

Untuk membuat tampilan terwujud, Anda memerlukan izin IAM bigquery.tables.create.

Setiap peran IAM yang telah ditetapkan berikut menyertakan izin yang Anda perlukan untuk membuat tampilan terwujud:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Untuk mengetahui informasi selengkapnya tentang Identity and Access Management (IAM) BigQuery, lihat Kontrol akses dengan IAM.

Membuat tampilan terwujud

Untuk membuat tampilan terwujud, pilih salah satu opsi berikut:

SQL

Gunakan pernyataan CREATE MATERIALIZED VIEW. Contoh berikut membuat tampilan terwujud untuk jumlah klik untuk setiap ID produk:

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
  QUERY_EXPRESSION
);

    Ganti kode berikut:

    • PROJECT_ID: nama project tempat Anda ingin membuat tampilan terwujud—misalnya, myproject.
    • DATASET: nama set data BigQuery tempat Anda ingin membuat tampilan yang diwujudkan—misalnya, mydataset. Jika Anda membuat tampilan yang diwujudkan melalui tabel BigLake Amazon Simple Storage Service (Amazon S3) (pratinjau), pastikan set data berada di wilayah yang didukung.
    • MATERIALIZED_VIEW_NAME: nama tampilan terwujud yang ingin Anda buat—misalnya, my_mv.
    • QUERY_EXPRESSION: ekspresi kueri GoogleSQL yang menentukan tampilan terwujud—misalnya, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

Contoh

Contoh berikut membuat tampilan terwujud untuk jumlah klik untuk setiap ID produk:

CREATE MATERIALIZED VIEW myproject.mydataset.my_mv_table AS (
  SELECT
    product_id,
    SUM(clicks) AS sum_clicks
  FROM
    myproject.mydataset.my_base_table
  GROUP BY
    product_id
);

Terraform

Gunakan resource google_bigquery_table.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

Contoh berikut membuat tampilan bernama my_materialized_view:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "my_materialized_view"
  deletion_protection = false # set to "true" in production

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

Untuk menerapkan konfigurasi Terraform di project Google Cloud, selesaikan langkah-langkah di bagian berikut.

Menyiapkan Cloud Shell

  1. Luncurkan Cloud Shell.

  2. Tetapkan project Google Cloud default tempat Anda ingin menerapkan konfigurasi Terraform.

    Anda hanya perlu menjalankan perintah ini sekali per project, dan dapat dijalankan di direktori mana pun.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Variabel lingkungan akan diganti jika Anda menetapkan nilai eksplisit dalam file konfigurasi Terraform.

Menyiapkan direktori

Setiap file konfigurasi Terraform harus memiliki direktorinya sendiri (juga disebut modul root).

  1. Di Cloud Shell, buat direktori dan file baru di dalam direktori tersebut. Nama file harus memiliki ekstensi .tf—misalnya main.tf. Dalam tutorial ini, file ini disebut sebagai main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Jika mengikuti tutorial, Anda dapat menyalin kode contoh di setiap bagian atau langkah.

    Salin kode contoh ke dalam main.tf yang baru dibuat.

    Atau, salin kode dari GitHub. Tindakan ini direkomendasikan jika cuplikan Terraform adalah bagian dari solusi menyeluruh.

  3. Tinjau dan ubah contoh parameter untuk diterapkan pada lingkungan Anda.
  4. Simpan perubahan Anda.
  5. Lakukan inisialisasi Terraform. Anda hanya perlu melakukan ini sekali per direktori. 
    terraform init

    Secara opsional, untuk menggunakan versi penyedia Google terbaru, sertakan opsi -upgrade: 

    terraform init -upgrade

Menerapkan perubahan

  1. Tinjau konfigurasi dan pastikan resource yang akan dibuat atau diupdate oleh Terraform sesuai yang Anda inginkan: 
    terraform plan

    Koreksi konfigurasi jika diperlukan.

  2. Terapkan konfigurasi Terraform dengan menjalankan perintah berikut dan memasukkan yes pada prompt: 
    terraform apply

    Tunggu hingga Terraform menampilkan pesan "Apply complete!".

  3. Buka project Google Cloud Anda untuk melihat hasilnya. Di Konsol Google Cloud, buka resource Anda di UI untuk memastikan bahwa Terraform telah membuat atau mengupdatenya.

API

Panggil metode tables.insert dan teruskan resource Table dengan kolom materializedView yang ditentukan:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "PROJECT_ID",
    "datasetId": "DATASET",
    "tableId": "MATERIALIZED_VIEW_NAME"
  },
  "materializedView": {
    "query": "QUERY_EXPRESSION"
  }
}

Ganti kode berikut:

  • PROJECT_ID: nama project tempat Anda ingin membuat tampilan terwujud—misalnya, myproject.
  • DATASET: nama set data BigQuery tempat Anda ingin membuat tampilan yang diwujudkan—misalnya, mydataset. Jika Anda membuat tampilan yang diwujudkan melalui tabel BigLake Amazon Simple Storage Service (Amazon S3) (pratinjau), pastikan set data berada di wilayah yang didukung.
  • MATERIALIZED_VIEW_NAME: nama tampilan terwujud yang ingin Anda buat—misalnya, my_mv.
  • QUERY_EXPRESSION: ekspresi kueri GoogleSQL yang menentukan tampilan terwujud—misalnya, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

Contoh

Contoh berikut membuat tampilan terwujud untuk jumlah klik untuk setiap ID produk:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

Java

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Java di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Java API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

Setelah berhasil dibuat, tampilan terwujud akan muncul di panel Explorer BigQuery di Konsol Google Cloud. Contoh berikut menunjukkan skema tampilan terwujud:

Skema tampilan terwujud di Konsol Google Cloud

Kecuali jika Anda menonaktifkan refresh otomatis, BigQuery akan memulai refresh penuh asinkron untuk tampilan terwujud. Kueri selesai dengan cepat, tetapi refresh awal mungkin akan terus dijalankan.

Kontrol akses

Anda dapat memberikan akses ke tampilan terwujud di tingkat set data, tingkat tampilan, atau tingkat kolom. Anda juga dapat menetapkan akses pada level yang lebih tinggi dalam hierarki resource IAM.

Membuat kueri tampilan terwujud memerlukan akses ke tampilan serta tabel dasarnya. Untuk membagikan tampilan terwujud, Anda dapat memberikan izin ke tabel dasar atau mengonfigurasi tampilan terwujud sebagai tampilan yang diotorisasi. Untuk mengetahui informasi selengkapnya, lihat Tampilan yang diotorisasi.

Untuk mengontrol akses ke tampilan di BigQuery, lihat Tampilan yang diotorisasi.

Dukungan kueri tampilan terwujud

Tampilan terwujud menggunakan sintaksis SQL terbatas. Kueri harus menggunakan pola berikut:

[ WITH cte [, …]]
SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

from_item:
    {
      table_name [ as_alias ]
      | { join_operation | ( join_operation ) }
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

Batasan kueri

Tampilan terwujud memiliki batasan berikut.

Persyaratan gabungan

Gabungan dalam kueri tampilan terwujud harus merupakan output. Komputasi, pemfilteran, atau penggabungan berdasarkan nilai gabungan tidak didukung. Misalnya, pembuatan tampilan dari kueri berikut tidak didukung karena menghasilkan nilai yang dihitung dari agregat, COUNT(*) / 10 as cnt.

SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt
FROM mydataset.mytable
GROUP BY ts_hour;

Hanya fungsi agregasi berikut yang saat ini didukung:

  • ANY_VALUE (tetapi tidak di atas STRUCT)
  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG (tetapi tidak di atas ARRAY atau STRUCT)
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • HLL_COUNT.INIT
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • MAX_BY (tetapi tidak di atas STRUCT)
  • MIN_BY (tetapi tidak di atas STRUCT)
  • SUM

Fitur SQL yang tidak didukung

Fitur SQL berikut tidak didukung dalam tampilan terwujud:

Dukungan LEFT OUTER JOIN dan UNION ALL

Untuk meminta masukan atau dukungan terkait fitur ini, kirim email ke bq-mv-help @google.com.

Tampilan terwujud inkremental mendukung LEFT OUTER JOIN dan UNION ALL. Tampilan terwujud dengan pernyataan LEFT OUTER JOIN dan UNION ALL memiliki batasan yang sama dengan tampilan terwujud inkremental lainnya. Selain itu, smart tuning tidak didukung untuk tampilan terwujud dengan union all atau left outer join.

Contoh

Contoh berikut membuat tampilan terwujud inkremental agregat dengan LEFT JOIN. Tampilan ini diperbarui secara bertahap saat data ditambahkan ke tabel kiri.

CREATE MATERIALIZED VIEW dataset.mv
AS (
  SELECT
    s_store_sk,
    s_country,
    s_zip,
    SUM(ss_net_paid) AS sum_sales,
  FROM dataset.store_sales
  LEFT JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY 1, 2, 3
);

Contoh berikut membuat tampilan terwujud inkremental agregat dengan UNION ALL. Tampilan ini diperbarui secara bertahap saat data ditambahkan ke salah satu atau kedua tabel. Untuk mengetahui informasi selengkapnya tentang update inkremental, lihat Update Inkremental.

CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour)
AS (
  SELECT
    SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales
  FROM
    (SELECT ts, sales from dataset.table1 UNION ALL
     SELECT ts, sales from dataset.table2)
  GROUP BY 1
);

Pembatasan kontrol akses

  • Jika kueri pengguna untuk tampilan terwujud menyertakan kolom tabel dasar yang tidak dapat mereka akses karena keamanan tingkat kolom, kueri akan gagal dengan pesan Access Denied.
  • Jika pengguna membuat kueri tampilan terwujud tetapi tidak memiliki akses penuh ke semua baris dalam tabel dasar tampilan terwujud, BigQuery akan menjalankan kueri tersebut terhadap tabel dasar, alih-alih membaca data tampilan terwujud. Hal ini memastikan bahwa kueri mematuhi semua batasan kontrol akses. Batasan ini juga berlaku saat membuat kueri tabel dengan kolom yang disamarkan pada data.

Klausa WITH dan ekspresi tabel umum (CTE)

Tampilan terwujud mendukung klausa WITH dan ekspresi tabel umum. Tampilan terwujud dengan klausa WITH tetap harus mengikuti pola dan batasan tampilan terwujud tanpa klausa WITH.

Contoh

Contoh berikut menunjukkan tampilan terwujud menggunakan klausa WITH:

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, *
  FROM mydataset.mytable
)
SELECT ts_hour, COUNT(*) AS cnt
FROM tmp
GROUP BY ts_hour;

Contoh berikut menunjukkan tampilan terwujud menggunakan klausa WITH yang tidak didukung karena berisi dua klausa GROUP BY:

WITH tmp AS (
  SELECT city, COUNT(*) AS population
  FROM mydataset.mytable
  GROUP BY city
)
SELECT population, COUNT(*) AS cnt
GROUP BY population;

Tampilan terwujud di atas tabel BigLake

Untuk membuat tampilan terwujud pada tabel BigLake, tabel BigLake harus mengaktifkan proses cache metadata pada data Cloud Storage dan tampilan terwujud harus memiliki nilai opsi max_staleness yang lebih besar dari tabel dasar. Tampilan terwujud atas tabel BigLake mendukung kumpulan kueri yang sama seperti tampilan terwujud lainnya.

Contoh

Pembuatan tampilan gabungan sederhana menggunakan tabel dasar BigLake:

CREATE MATERIALIZED VIEW sample_dataset.sample_mv
    OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND)
AS SELECT COUNT(*) cnt
FROM dataset.biglake_base_table;

Untuk detail tentang batasan tampilan terwujud atas tabel BigLake, lihat tampilan terwujud atas tabel BigLake.

Tampilan terwujud di atas tabel Apache Iceberg

Untuk meminta masukan atau dukungan terkait fitur ini, kirim email ke bq-mv-help@google.com.

Anda dapat mereferensikan tabel Iceberg besar dalam tampilan terwujud, bukan memigrasikan data tersebut ke penyimpanan yang dikelola BigQuery.

Membuat tampilan terwujud di atas tabel Iceberg

Untuk membuat tampilan terwujud di atas Iceberg, ikuti langkah-langkah berikut:

  1. Dapatkan tabel Iceberg menggunakan salah satu metode berikut:

    Contoh

    CREATE EXTERNAL TABLE mydataset.myicebergtable
  WITH CONNECTION `myproject.us.myconnection`
  OPTIONS (
        format = 'ICEBERG',
        uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
  )

  2. Referensikan tabel Iceberg Anda dengan spesifikasi partisi berikut:

    "partition-specs" : [ {
   "spec-id" : 0,
   "fields" : [ {
    "name" : "birth_month",
    "transform" : "month",
    "source-id" : 3,
    "field-id" : 1000
} ]

  3. Buat tampilan terwujud yang selaras dengan partisi:

    CREATE MATERIALIZED VIEW mydataset.myicebergmv
  PARTITION BY DATE_TRUNC(birth_month, MONTH)
AS
  SELECT * FROM mydataset.myicebergtable;

Batasan

Selain batasan tabel Iceberg standar, tampilan terwujud atas tabel Iceberg memiliki batasan berikut:

  • Anda dapat membuat tampilan terwujud yang partisinya selaras dengan tabel dasar. Namun, tampilan terwujud hanya mendukung transformasi partisi berbasis waktu, misalnya, YEAR, MONTH, DAY, dan HOUR.
  • Perincian partisi tampilan terwujud tidak boleh lebih halus daripada perincian partisi tabel dasar. Misalnya, jika Anda mempartisi tabel dasar setiap tahun menggunakan kolom birth_date, membuat tampilan terwujud dengan PARTITION BY DATE_TRUNC(birth_date, MONTH) tidak akan berfungsi.
  • Setiap perubahan skema akan membatalkan tampilan terwujud.
  • Evolusi partisi didukung. Namun, mengubah kolom partisi tabel dasar tanpa membuat ulang tampilan terwujud dapat menyebabkan pembatalan validasi penuh yang tidak dapat diperbaiki dengan pemuatan ulang.
  • Harus ada minimal satu snapshot di tabel dasar.
  • Tabel Iceberg harus berupa tabel BigLake, misalnya, tabel eksternal yang diotorisasi.
  • Jika Kontrol Layanan VPC diaktifkan, akun layanan tabel eksternal yang diotorisasi harus ditambahkan ke aturan masuk Anda. Jika tidak, Kontrol Layanan VPC akan memblokir pembaruan latar belakang otomatis untuk tampilan yang diwujudkan.

File metadata.json tabel Iceberg Anda harus memiliki spesifikasi berikut. Tanpa spesifikasi ini, kueri Anda akan memindai tabel dasar, sehingga gagal menggunakan hasil yang terwujud.

  • Di metadata tabel:

    • current-snapshot-id
    • current-schema-id
    • snapshots
    • snapshot-log

  • Di snapshot:

    • parent-snapshot-id (jika tersedia)
    • schema-id
    • operation (di kolom summary)

  • Partisi (untuk tampilan terwujud yang dipartisi)

Tampilan terwujud yang dipartisi

Tampilan terwujud pada tabel yang dipartisi dapat dipartisi. Mempartisi tampilan terwujud mirip dengan mempartisi tabel normal, karena memberikan manfaat saat kueri sering mengakses subset partisi. Selain itu, mempartisi tampilan terwujud dapat meningkatkan perilaku tampilan saat data dalam tabel dasar atau tabel diubah atau dihapus. Untuk mengetahui informasi selengkapnya, lihat Perataan partisi.

Jika tabel dasar dipartisi, Anda dapat mempartisi tampilan terwujud pada kolom partisi yang sama. Untuk partisi berbasis waktu, perincian harus cocok (per jam, harian, bulanan, atau tahunan). Untuk partisi rentang bilangan bulat, spesifikasi rentang harus sama persis. Anda tidak dapat mempartisi tampilan terwujud melalui tabel dasar yang tidak dipartisi.

Jika tabel dasar dipartisi menurut waktu penyerapan, tampilan terwujud dapat dikelompokkan menurut kolom _PARTITIONDATE tabel dasar, dan juga dipartisi berdasarkan kolom tersebut. Jika Anda tidak secara eksplisit menetapkan partisi saat membuat tampilan terwujud, tampilan terwujud akan tidak dipartisi.

Jika tabel dasar dipartisi, pertimbangkan untuk mempartisi tampilan terwujud Anda juga untuk mengurangi biaya pemeliharaan tugas refresh dan biaya kueri.

Akhir masa berlaku partisi

Akhir masa berlaku partisi tidak dapat disetel pada tampilan terwujud. Tampilan terwujud secara implisit mewarisi waktu habis masa berlaku partisi dari tabel dasar. Partisi tampilan terwujud disejajarkan dengan partisi tabel dasar, sehingga akan berakhir secara sinkron.

Contoh 1

Dalam contoh ini, tabel dasar dipartisi pada kolom transaction_time dengan partisi harian. Tampilan terwujud dipartisi pada kolom yang sama dan dikelompokkan pada kolom employee_id.

CREATE TABLE my_project.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time)
  OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_time)
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    transaction_time,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id, transaction_time
);

Contoh 2

Dalam contoh ini, tabel dasar dipartisi menurut waktu penyerapan dengan partisi harian. Tampilan terwujud memilih waktu penyerapan sebagai kolom bernama date. Tampilan terwujud dikelompokkan menurut kolom date dan dipartisi oleh kolom yang sama.

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY date
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    _PARTITIONDATE AS date,
    COUNT(1) AS count
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    date
);

Contoh 3

Dalam contoh ini, tabel dasar dipartisi pada kolom TIMESTAMP bernama transaction_time, dengan partisi harian. Tampilan terwujud menentukan kolom bernama transaction_hour, menggunakan fungsi TIMESTAMP_TRUNC untuk memotong nilai ke jam terdekat. Tampilan terwujud dikelompokkan menurut transaction_hour dan juga dipartisi olehnya.

Perhatikan hal-hal berikut:

  • Fungsi pemotongan yang diterapkan ke kolom partisi harus setidaknya sedetail partisi tabel dasar. Misalnya, jika tabel dasar menggunakan partisi harian, fungsi pemotongan tidak dapat menggunakan perincian MONTH atau YEAR.

  • Dalam spesifikasi partisi tampilan terwujud, perincian harus cocok dengan tabel dasar.

CREATE TABLE my_project.my_dataset.my_base_table (
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_hour)
AS (
  SELECT
    employee_id,
    TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    transaction_hour
);

Tampilan terwujud cluster

Anda dapat mengelompokkan tampilan terwujud berdasarkan kolom output-nya, sesuai dengan batasan tabel yang dikelompokkan BigQuery. Kolom output agregat tidak dapat digunakan sebagai kolom pengelompokan. Menambahkan pengelompokan kolom ke tampilan terwujud dapat meningkatkan performa kueri yang menyertakan filter pada kolom tersebut.

Mereferensikan tampilan logis

Untuk meminta masukan atau dukungan terkait fitur ini, kirim email ke bq-mv-help@google.com.

Kueri tampilan terwujud dapat mereferensikan tampilan logis, tetapi tunduk pada batasan berikut:

Pertimbangan saat membuat tampilan terwujud

Tampilan terwujud mana yang akan dibuat

Saat membuat tampilan terwujud, pastikan definisi tampilan terwujud Anda mencerminkan pola kueri terhadap tabel dasar. Tampilan terwujud lebih efektif jika menayangkan kumpulan kueri yang luas, bukan hanya satu pola kueri tertentu.

Misalnya, pertimbangkan kueri pada tabel yang sering digunakan pengguna untuk memfilter menurut kolom user_id atau department. Anda dapat mengelompokkan berdasarkan kolom ini dan secara opsional mengelompokkan berdasarkan kolom tersebut, bukan menambahkan filter seperti user_id = 123 ke dalam tampilan terwujud.

Sebagai contoh lainnya, pengguna sering menggunakan filter tanggal, baik menurut tanggal tertentu, seperti WHERE order_date = CURRENT_DATE(), atau rentang tanggal, seperti WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. Tambahkan filter rentang tanggal dalam tampilan terwujud yang mencakup rentang tanggal yang diharapkan dalam kueri:

CREATE MATERIALIZED VIEW ...
  ...
  WHERE date > '2019-01-01'
  GROUP BY date

Gabungan

Rekomendasi berikut berlaku untuk tampilan terwujud dengan JOIN.

Menempatkan tabel yang paling sering berubah terlebih dahulu

Pastikan tabel terbesar atau yang paling sering berubah adalah tabel pertama/paling kiri yang direferensikan dalam kueri tampilan. Tampilan terwujud dengan join mendukung kueri inkremental dan memuat ulang saat tabel pertama atau paling kiri dalam kueri ditambahkan, tetapi perubahan pada tabel lain sepenuhnya membatalkan cache tampilan. Dalam skema bintang atau kepingan salju, tabel pertama atau paling kiri umumnya harus berupa tabel fakta.

Menghindari bergabung pada kunci pengelompokan

Tampilan terwujud dengan join berfungsi paling baik jika data digabungkan banyak atau kueri join asli mahal. Untuk kueri selektif, BigQuery sering kali sudah dapat melakukan penggabungan secara efisien dan tidak perlu tampilan terwujud. Misalnya, pertimbangkan definisi tampilan terwujud berikut.

CREATE MATERIALIZED VIEW dataset.mv
  CLUSTER BY s_market_id
AS (
  SELECT
    s_market_id,
    s_country,
    SUM(ss_net_paid) AS sum_sales,
    COUNT(*) AS cnt_sales
  FROM dataset.store_sales
  INNER JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY s_market_id, s_country
);

Misalnya store_sales dikelompokkan di ss_store_sk dan Anda sering menjalankan kueri seperti berikut:

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany';

Tampilan terwujud mungkin tidak seefisien kueri asli. Untuk mendapatkan hasil terbaik, lakukan eksperimen dengan kumpulan kueri yang representatif, dengan dan tanpa tampilan terwujud.

Menggunakan tampilan terwujud dengan opsi max_staleness

Opsi tampilan terwujud max_staleness membantu Anda mencapai performa kueri tinggi secara konsisten dengan biaya yang terkontrol saat memproses set data yang besar dan sering berubah. Dengan parameter max_staleness, Anda dapat mengurangi biaya dan latensi pada kueri dengan menetapkan interval waktu saat keusangan data hasil kueri dapat diterima. Perilaku ini dapat berguna untuk dasbor dan laporan yang tidak terlalu mementingkan keaktualan hasil kueri.

Data yang tidak berlaku

Saat Anda membuat kueri tampilan terwujud dengan kumpulan opsi max_staleness, BigQuery akan menampilkan hasil berdasarkan nilai max_staleness dan waktu saat pembaruan terakhir terjadi.

Jika refresh terakhir terjadi dalam interval max_staleness, BigQuery akan menampilkan data langsung dari tampilan terwujud tanpa membaca tabel dasar. Misalnya, hal ini berlaku jika interval max_staleness Anda adalah 4 jam, dan pembaruan terakhir terjadi 2 jam yang lalu.

Jika refresh terakhir terjadi di luar interval max_staleness, BigQuery akan membaca data dari tampilan terwujud, menggabungkannya dengan perubahan pada tabel dasar sejak refresh terakhir, dan menampilkan hasil gabungan. Hasil gabungan ini mungkin masih tidak berlaku, hingga interval max_staleness Anda. Misalnya, hal ini berlaku jika interval max_staleness Anda adalah 4 jam, dan pembaruan terakhir terjadi 7 jam yang lalu.

Membuat dengan opsi max_staleness

Pilih salah satu opsi berikut:

SQL

Untuk membuat tampilan terwujud dengan opsi max_staleness, tambahkan klausa OPTIONS ke pernyataan DDL saat Anda membuat tampilan terwujud:

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
  OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
    max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;

    Ganti kode berikut:

    • project-id adalah project ID Anda.
    • my_dataset adalah ID set data dalam project Anda.
    • my_mv_table adalah ID tampilan terwujud yang Anda buat.
    • my_base_table adalah ID tabel dalam set data Anda yang berfungsi sebagai tabel dasar untuk tampilan terwujud Anda.

    • Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

API

Panggil metode tables.insert dengan resource materializedView yang ditentukan sebagai bagian dari permintaan API Anda. Resource materializedView berisi kolom query. Misalnya:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from project-id.my_dataset.my_base_table
                group by 1"
  }
  "maxStaleness": "4:0:0"
}

Ganti kode berikut:

  • project-id adalah project ID Anda.
  • my_dataset adalah ID set data dalam project Anda.
  • my_mv_table adalah ID tampilan terwujud yang Anda buat.
  • my_base_table adalah ID tabel dalam set data Anda yang berfungsi sebagai tabel dasar untuk tampilan terwujud Anda.
  • product_id adalah kolom dari tabel dasar.
  • clicks adalah kolom dari tabel dasar.
  • sum_clicks adalah kolom dalam tampilan terwujud yang Anda buat.

Menerapkan opsi max_staleness

Anda dapat menerapkan parameter ini ke tampilan terwujud yang sudah ada dengan menggunakan pernyataan ALTER MATERIALIZED VIEW. Contoh:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120,
  max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);

Membuat kueri dengan max_staleness

Anda dapat membuat kueri tampilan terwujud dengan opsi max_staleness seperti halnya kueri tampilan terwujud, tampilan logis, atau tabel lainnya.

Contoh:

SELECT * FROM  project-id.my_dataset.my_mv_table

Kueri ini menampilkan data dari pemuatan ulang terakhir jika data tersebut belum lebih lama dari parameter max_staleness. Jika tampilan terwujud belum dimuat ulang dalam interval max_staleness, BigQuery akan menggabungkan hasil refresh terbaru yang tersedia dengan perubahan tabel dasar untuk menampilkan hasil dalam interval max_staleness.

Streaming data dan hasil max_staleness

Jika Anda mengalirkan data ke tabel dasar tampilan terwujud dengan max_staleness, kueri tampilan terwujud mungkin mengecualikan record yang di-streaming ke dalam tabelnya sebelum awal interval ketidak-berlakuan. Akibatnya, tampilan terwujud yang mencakup data dari beberapa tabel dan opsi max_staleness mungkin tidak merepresentasikan snapshot titik waktu tabel tersebut.

Penyesuaian smart dan opsi max_staleness

Smart tuning akan otomatis menulis ulang kueri untuk menggunakan tampilan terwujud jika memungkinkan, terlepas dari opsi max_staleness, meskipun kueri tidak mereferensikan tampilan terwujud. Opsi max_staleness pada tampilan terwujud tidak memengaruhi hasil kueri yang ditulis ulang. Opsi max_staleness hanya memengaruhi kueri yang secara langsung mengkueri tampilan terwujud.

Mengelola data yang tidak berlaku dan frekuensi refresh

Anda harus menetapkan max_staleness berdasarkan persyaratan Anda. Untuk menghindari pembacaan data dari tabel dasar, konfigurasikan interval refresh sehingga refresh terjadi dalam interval keusangan. Anda dapat memperhitungkan rata-rata runtime refresh plus margin untuk pertumbuhan.

Misalnya, jika satu jam diperlukan untuk memuat ulang tampilan terwujud dan Anda ingin buffering pertumbuhan satu jam, Anda harus menetapkan interval refresh ke dua jam. Konfigurasi ini memastikan bahwa refresh terjadi dalam waktu maksimum empat jam laporan Anda untuk status tidak berlaku.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness =
INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Tampilan terwujud non-inkremental

Tampilan terwujud non-inkremental mendukung sebagian besar kueri SQL, termasuk klausa OUTER JOIN, UNION, dan HAVING, serta fungsi analisis. Untuk menentukan apakah tampilan terwujud digunakan dalam kueri Anda, periksa perkiraan biaya menggunakan uji coba. Dalam skenario ketika ketidak-berlakuan data dapat diterima, misalnya untuk pelaporan atau pemrosesan data batch, tampilan terwujud non-inkremental dapat meningkatkan performa kueri dan mengurangi biaya. Dengan menggunakan opsi max_staleness, Anda dapat membuat tampilan terwujud yang arbitrer dan kompleks yang dikelola secara otomatis dan memiliki jaminan ketidak-berlakuan bawaan.

Menggunakan tampilan terwujud non-inkremental

Anda dapat membuat tampilan terwujud non-inkremental menggunakan opsi allow_non_incremental_definition. Opsi ini harus disertai dengan opsi max_staleness. Untuk memastikan pembaruan tampilan terwujud secara berkala, Anda juga harus mengonfigurasi kebijakan refresh. Tanpa kebijakan refresh, Anda harus memuat ulang tampilan terwujud secara manual.

Tampilan terwujud selalu mewakili status tabel dasar dalam interval max_staleness. Jika refresh terakhir terlalu usang dan tidak mewakili tabel dasar dalam interval max_staleness, kueri akan membaca tabel dasar. Untuk mempelajari kemungkinan implikasi performa lebih lanjut, lihat Data yang tidak berlaku.

Membuat dengan allow_non_incremental_definition

Untuk membuat tampilan terwujud dengan opsi allow_non_incremental_definition, ikuti langkah-langkah berikut. Setelah membuat tampilan terwujud, Anda tidak dapat mengubah opsi allow_non_incremental_definition. Misalnya, Anda tidak dapat mengubah nilai true menjadi false, atau menghapus opsi allow_non_incremental_definition dari tampilan terwujud.

SQL

Tambahkan klausa OPTIONS ke pernyataan DDL saat Anda membuat tampilan terwujud:

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
OPTIONS (
  enable_refresh = true, refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
    allow_non_incremental_definition = true)
AS SELECT
  s_store_sk,
  SUM(ss_net_paid) AS sum_sales,
  APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
FROM my_project.my_dataset.store
LEFT OUTER JOIN my_project.my_dataset.store_sales
  ON ss_store_sk = s_store_sk
GROUP BY s_store_sk
HAVING median < 40 OR median is NULL ;

    Ganti kode berikut:

    • my_project adalah project ID Anda.
    • my_dataset adalah ID set data dalam project Anda.
    • my_mv_table adalah ID tampilan terwujud yang Anda buat.
    • my_dataset.store dan my_dataset.store_sales adalah ID tabel-tabel dalam set data Anda yang berfungsi sebagai tabel dasar untuk tampilan terwujud Anda.

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

API

Panggil metode tables.insert dengan resource materializedView yang ditentukan sebagai bagian dari permintaan API Anda. Resource materializedView berisi kolom query. Misalnya:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "my_project",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "`SELECT`
        s_store_sk,
        SUM(ss_net_paid) AS sum_sales,
        APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
      FROM my_project.my_dataset.store
      LEFT OUTER JOIN my_project.my_dataset.store_sales
        ON ss_store_sk = s_store_sk
      GROUP BY s_store_sk
      HAVING median < 40 OR median is NULL`",
    "allowNonIncrementalDefinition": true
  }
  "maxStaleness": "4:0:0"
}

Ganti kode berikut:

  • my_project adalah project ID Anda.
  • my_dataset adalah ID set data dalam project Anda.
  • my_mv_table adalah ID tampilan terwujud yang Anda buat.
  • my_dataset.store dan my_dataset.store_sales adalah ID tabel dalam set data Anda yang berfungsi sebagai tabel dasar untuk tampilan terwujud Anda.

Membuat kueri dengan allow_non_incremental_definition

Anda dapat membuat kueri tampilan terwujud non-inkremental seperti saat membuat kueri tampilan terwujud, tampilan logis, atau tabel lainnya.

Contoh:

SELECT * FROM  my_project.my_dataset.my_mv_table

Jika data tidak lebih lama dari parameter max_staleness, kueri ini akan menampilkan data dari pemuatan ulang terakhir. Untuk mengetahui detail tentang ketidak-berlakuan dan keaktualan data, lihat Data yang tidak berlaku.

Batasan khusus untuk tampilan terwujud non-inkremental

Batasan berikut hanya berlaku untuk tampilan terwujud dengan opsi allow_non_incremental_definition. Dengan pengecualian batasan pada sintaksis kueri yang didukung, semua batasan tampilan terwujud masih berlaku.

  • Smart-tuning tidak diterapkan pada tampilan terwujud yang menyertakan opsi allow_non_incremental_definition. Satu-satunya cara untuk mendapatkan manfaat dari tampilan terwujud dengan opsi allow_non_incremental_definition adalah dengan membuat kuerinya secara langsung.
  • Tampilan terwujud tanpa opsi allow_non_incremental_definition dapat memperbarui subset datanya secara bertahap. Tampilan terwujud dengan opsi allow_non_incremental_definition harus dimuat ulang secara seluruhnya.
  • Tampilan terwujud dengan opsi max_staleness memvalidasi keberadaan batasan keamanan tingkat kolom selama eksekusi kueri. Lihat detail selengkapnya tentang hal ini di kontrol akses tingkat kolom

Langkah berikutnya