マテリアライズド ビューを作成する
このドキュメントでは、BigQuery でマテリアライズド ビューを作成する方法について説明します。このドキュメントを読む前に、マテリアライズド ビューの概要を理解してください。
始める前に
このドキュメントの各タスクを実行するために必要な権限をユーザーに与える Identity and Access Management(IAM)のロールを付与します。
必要な権限
マテリアライズド ビューを作成するには、bigquery.tables.create
IAM 権限が必要です。
次の IAM 事前定義ロールには、マテリアライズド ビューの作成に必要な権限が含まれています。
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
BigQuery Identity and Access Management(IAM)の詳細については、IAM によるアクセス制御をご覧ください。
マテリアライズド ビューの作成
マテリアライズド ビューを作成するには、次のいずれかのオプションを選択します。
SQL
CREATE MATERIALIZED VIEW
ステートメントを使用します。次の例では、各プロダクト ID のクリック数のマテリアライズド ビューを作成します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS ( QUERY_EXPRESSION );
次のように置き換えます。
PROJECT_ID
: マテリアライズド ビューを作成するプロジェクトの名前(例:myproject
)。DATASET
: マテリアライズド ビューを作成する BigQuery データセットの名前(例:mydataset
)。Amazon Simple Storage Service(Amazon S3)BigLake テーブル(プレビュー)に対してマテリアライズド ビューを作成する場合は、データセットがサポートされているリージョンに存在することをご確認ください。MATERIALIZED_VIEW_NAME
: 作成するマテリアライズド ビューの名前(例:my_mv
)。QUERY_EXPRESSION
: マテリアライズド ビューを定義する GoogleSQL クエリ式(例:SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
)。
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。
例
次の例では、各プロダクト ID のクリック数のマテリアライズド ビューを作成します。
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
google_bigquery_table
リソースを使用します。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
次の例では、my_materialized_view
という名前のビューが作成されます。
Google Cloud プロジェクトで Terraform 構成を適用するには、次のセクションの手順を完了します。
Cloud Shell を準備する
- Cloud Shell を起動します。
-
Terraform 構成を適用するデフォルトの Google Cloud プロジェクトを設定します。
このコマンドは、プロジェクトごとに 1 回だけ実行する必要があります。これは任意のディレクトリで実行できます。
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Terraform 構成ファイルに明示的な値を設定すると、環境変数がオーバーライドされます。
ディレクトリを準備する
Terraform 構成ファイルには独自のディレクトリ(ルート モジュールとも呼ばれます)が必要です。
-
Cloud Shell で、ディレクトリを作成し、そのディレクトリ内に新しいファイルを作成します。ファイルの拡張子は
.tf
にする必要があります(例:main.tf
)。このチュートリアルでは、このファイルをmain.tf
とします。mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
チュートリアルを使用している場合は、各セクションまたはステップのサンプルコードをコピーできます。
新しく作成した
main.tf
にサンプルコードをコピーします。必要に応じて、GitHub からコードをコピーします。Terraform スニペットがエンドツーエンドのソリューションの一部である場合は、この方法をおすすめします。
- 環境に適用するサンプル パラメータを確認し、変更します。
- 変更を保存します。
-
Terraform を初期化します。これは、ディレクトリごとに 1 回だけ行います。
terraform init
最新バージョンの Google プロバイダを使用する場合は、
-upgrade
オプションを使用します。terraform init -upgrade
変更を適用する
-
構成を確認して、Terraform が作成または更新するリソースが想定どおりであることを確認します。
terraform plan
必要に応じて構成を修正します。
-
次のコマンドを実行します。プロンプトで「
yes
」と入力して、Terraform 構成を適用します。terraform apply
Terraform に「Apply complete!」というメッセージが表示されるまで待ちます。
- Google Cloud プロジェクトを開いて結果を表示します。Google Cloud コンソールの UI でリソースに移動して、Terraform によって作成または更新されたことを確認します。
API
tables.insert
メソッドを呼び出し、materializedView
フィールドが定義された Table
リソースを渡します。
{ "kind": "bigquery#table", "tableReference": { "projectId": "PROJECT_ID", "datasetId": "DATASET", "tableId": "MATERIALIZED_VIEW_NAME" }, "materializedView": { "query": "QUERY_EXPRESSION" } }
次のように置き換えます。
PROJECT_ID
: マテリアライズド ビューを作成するプロジェクトの名前(例:myproject
)。DATASET
: マテリアライズド ビューを作成する BigQuery データセットの名前(例:mydataset
)。Amazon Simple Storage Service(Amazon S3)BigLake テーブル(プレビュー)に対してマテリアライズド ビューを作成する場合は、データセットがサポートされているリージョンに存在することをご確認ください。MATERIALIZED_VIEW_NAME
: 作成するマテリアライズド ビューの名前(例:my_mv
)。QUERY_EXPRESSION
: マテリアライズド ビューを定義する GoogleSQL クエリ式(例:SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
)。
例
次の例では、各プロダクト ID のクリック数のマテリアライズド ビューを作成します。
{ "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
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Java の設定手順を完了してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
マテリアライズド ビューが正常に作成されると、Google Cloud コンソールの BigQuery の [エクスプローラ] パネルに表示されます。次の例は、マテリアライズド ビューのスキーマを示しています。
自動更新を無効にしない限り、BigQuery はマテリアライズド ビューの非同期全体更新を開始します。クエリは迅速に終了しますが、最初の更新は引き続き実行される場合があります。
アクセス制御
マテリアライズド ビューへのアクセス権は、データセット レベル、ビューレベル、または列レベルで付与できます。IAM リソース階層のより高いレベルでアクセス権を設定することもできます。
マテリアライズド ビューに対してクエリを実行するには、ビューとそのベーステーブルへのアクセス権が必要です。マテリアライズド ビューを共有するには、ベーステーブルに対する権限を付与するか、マテリアライズド ビューを承認済みビューとして構成します。詳細については、承認済みビューをご覧ください。
BigQuery でビューへのアクセスを制御するには、承認済みビューをご覧ください。
マテリアライズド ビュー クエリのサポート
マテリアライズド ビューは、制限付き SQL 構文を使用します。クエリで次のパターンを使用する必要があります。
[ 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
クエリの制限事項
マテリアライズド ビューには次の制限があります。
集計要件
マテリアライズド ビューのクエリの集計は出力である必要があります。集計値に基づく計算、フィルタリング、結合はサポートされていません。たとえば、以下に示すクエリからビューを作成する操作は、集計 COUNT(*) / 10 as cnt
から計算された値を生成するためサポートされません。
SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt FROM mydataset.mytable GROUP BY ts_hour;
現在サポートされているのは、次の集計関数のみです。
ANY_VALUE
(ただしSTRUCT
を超えないこと)APPROX_COUNT_DISTINCT
ARRAY_AGG
(ただしARRAY
またはSTRUCT
を超えないこと)AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
MAX_BY
(ただしSTRUCT
を超えないこと)MIN_BY
(ただしSTRUCT
を超えないこと)SUM
サポートされていない SQL 機能
マテリアライズド ビューでは、次の SQL 機能はサポートされていません。
UNION ALL
() プレビュー版でのサポートLEFT OUTER JOIN
() プレビュー版でのサポートRIGHT/FULL OUTER JOIN
。- 自己結合(同じテーブルで
JOIN
を複数回使用する)。 - ウィンドウ関数。
ARRAY
サブクエリ。RAND()
、CURRENT_DATE()
、SESSION_USER()
、CURRENT_TIME()
などの非確定的関数。- ユーザー定義関数(UDF)。
TABLESAMPLE
。FOR SYSTEM_TIME AS OF
。
LEFT OUTER JOIN
と UNION ALL
のサポート
この機能に関するフィードバックやサポートをご希望の場合は、bq-mv-help @google.com 宛てにメールをお送りください。
増分マテリアライズド ビューは、LEFT OUTER JOIN
と UNION ALL
をサポートしています。LEFT OUTER JOIN
ステートメントと UNION ALL
ステートメントを含むマテリアライズド ビューには、他の増分マテリアライズド ビューと同じ制限があります。また、UNION ALL または LEFT OUTER JOIN を含むマテリアライズド ビューでは、スマートな調整はサポートされていません。
例
次の例では、LEFT JOIN
を使用して集計増分マテリアライズド ビューを作成します。このビューは、左側の表にデータが追加されると段階的に更新されます。
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 );
次の例では、UNION ALL
を使用して集計増分マテリアライズド ビューを作成します。このビューは、データがいずれかのテーブルまたは両方のテーブルに追加されると、段階的に更新されます。増分アップデートの詳細については、増分アップデートをご覧ください。
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 );
アクセス制御の制限
- マテリアライズド ビューのユーザーのクエリに、列レベルのセキュリティのためにアクセスできないベーステーブル列が含まれている場合、クエリは
Access Denied
というメッセージで失敗します。 - ユーザーがマテリアライズド ビューのクエリを行ったが、マテリアライズド ビューのベーステーブルのすべての行に対する完全アクセス権を持っていない場合、BigQuery は、マテリアライズド ビュー データを読み取るのではなく、ベーステーブルに対してクエリを実行します。これにより、クエリがすべてのアクセス制御制約を尊重するようになります。この制限は、データがマスクされた列を含むテーブルをクエリする場合にも適用されます。
WITH
句と共通テーブル式(CTE)
マテリアライズド ビューは、WITH
句と共通テーブル式をサポートしています。それでも、WITH
句を含むマテリアライズド ビューは、WITH
句のない、マテリアライズド ビューのパターンと制限に従う必要があります。
例
次の例は、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;
次の例は、2 つの GROUP BY
句が含まれているため、サポートされていない WITH
句を使用したマテリアライズド ビューを示しています。
WITH tmp AS ( SELECT city, COUNT(*) AS population FROM mydataset.mytable GROUP BY city ) SELECT population, COUNT(*) AS cnt GROUP BY population;
BigLake テーブルのマテリアライズド ビュー
BigLake テーブルからマテリアライズド ビューを作成するには、BigLake テーブルで Cloud Storage データとマテリアライズド ビューに対してメタデータ キャッシュが有効になっている必要があります。max_staleness
オプション値はベーステーブルよりも大きくする必要があります。BigLake テーブルのマテリアライズド ビューは、他のマテリアライズド ビューと同じクエリセットをサポートします。
例
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;
BigLake テーブルのマテリアライズド ビューの制限については、BigLake テーブルのマテリアライズド ビューについてのページをご覧ください。
Apache Iceberg テーブルのマテリアライズド ビュー
この機能に関するフィードバックやサポートをご希望の場合は、bq-mv-help@google.com 宛てにメールをお送りください。
データを BigQuery マネージド ストレージに移行する代わりに、マテリアライズド ビューで大規模な Iceberg テーブルを参照できます。
Iceberg テーブルに対するマテリアライズド ビューを作成する
Iceberg に対するマテリアライズド ビューを作成する手順は次のとおりです。
次のいずれかの方法で Iceberg テーブルを取得します。
- JSON メタデータ ファイルを使用して Iceberg テーブルを作成します。
- BigLake Metastore を使用して Iceberg テーブルを作成します。
- AWS Glue 連携データセットで検出します。
例
CREATE EXTERNAL TABLE mydataset.myicebergtable WITH CONNECTION `myproject.us.myconnection` OPTIONS ( format = 'ICEBERG', uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"] )
次のパーティション仕様で Iceberg テーブルを参照します。
"partition-specs" : [ { "spec-id" : 0, "fields" : [ { "name" : "birth_month", "transform" : "month", "source-id" : 3, "field-id" : 1000 } ]
パーティションと連携したマテリアライズド ビューを作成します。
CREATE MATERIALIZED VIEW mydataset.myicebergmv PARTITION BY DATE_TRUNC(birth_month, MONTH) AS SELECT * FROM mydataset.myicebergtable;
制限事項
Iceberg テーブルのマテリアライズド ビューには、標準の Iceberg テーブルの制限のほかに、次の制限があります。
- ベーステーブルとパーティション分割が一致するマテリアライズド ビューを作成できます。ただし、マテリアライズド ビューは、時間ベースのパーティション変換(
YEAR
、MONTH
、DAY
、HOUR
など)のみをサポートします。 - マテリアライズド ビューのパーティションの粒度をベーステーブルのパーティションの粒度よりも高くすることはできません。たとえば、
birth_date
列を使用してベーステーブルを年単位でパーティション分割する場合、PARTITION BY DATE_TRUNC(birth_date, MONTH)
を使用してマテリアライズド ビューを作成することはできません。 - スキーマを変更すると、マテリアライズド ビューが無効になります。
- パーティションの進化がサポートされています。ただし、マテリアライズド ビューを再作成せずにベーステーブルのパーティショニング列を変更すると、完全に無効になり、更新しても修正できなくなる可能性があります。
- ベーステーブルには少なくとも 1 つのスナップショットが必要です。
- Iceberg テーブルは、BigLake テーブル(承認済みの外部テーブルなど)である必要があります。
- VPC Service Controls が有効になっている場合は、承認済み外部テーブルのサービス アカウントを上り(内向き)ルールに追加する必要があります。追加しなければ、VPC Service Controls によって、マテリアライズド ビューの自動バックグラウンド更新がブロックされます。
Iceberg テーブルの metadata.json
ファイルには、以下の要素を指定する必要があります。これらを指定しないと、クエリはベーステーブルをスキャンするので、具体的な結果を使用できません。
-
current-snapshot-id
current-schema-id
snapshots
snapshot-log
スナップショット内:
parent-snapshot-id
(利用可能な場合)schema-id
operation
(summary
フィールド内)
パーティショニング(パーティション分割されたマテリアライズド ビューの場合)
パーティション分割されたマテリアライズド ビュー
パーティション分割テーブルのマテリアライズド ビューはパーティショニングできます。マテリアライズド ビューのパーティション分割は、クエリがパーティションのサブセットにアクセスすることが多い場合にメリットが得られる点で、通常のテーブルのパーティショニングに似ています。また、マテリアライズド ビューをパーティショニングすることで、ベーステーブルまたはテーブルのデータが変更または削除されたときのビューの動作を改善できます。詳細については、パーティションの配置をご覧ください。
ベーステーブルがパーティション分割されている場合は、マテリアライズド ビューを同じパーティショニング列でパーティション分割できます。時間ベースのパーティションの場合は、粒度(時間単位、日単位、月単位、年単位)が一致する必要があります。整数範囲のパーティションの場合、範囲指定は正確に一致する必要があります。パーティション分割されていないベーステーブルでは、マテリアライズド ビューをパーティション分割できません。
ベーステーブルを取り込み時間でパーティション分割する場合、マテリアライズド ビューのグループ化とパーティション分割は、ベーステーブルの _PARTITIONDATE
列ごとに可能です。マテリアライズド ビューを作成するときにパーティショニングを明示的に指定しない場合、マテリアライズド ビューはパーティション分割されません。
ベーステーブルがパーティション分割されている場合は、マテリアライズド ビューもパーティション分割して、更新ジョブ メンテナンスとクエリの費用を削減することを検討してください。
パーティションの有効期限
パーティションの有効期限をマテリアライズド ビューに設定することはできません。マテリアライズド ビューは、ベーステーブルからパーティションの有効期限を暗黙的に継承します。マテリアライズド ビューのパーティションは、ベーステーブル パーティションと同期されるため、同時に期限切れになります。
例 1
この例では、ベーステーブルは日別パーティションを使用して transaction_time
列でパーティション分割されています。マテリアライズド ビューは同じ列でパーティション分割され、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 );
例 2
この例では、ベーステーブルは日別パーティションを使用して取り込み時間でパーティション分割されています。マテリアライズド ビューでは、date
という名前の列として取り込み時間を選択します。マテリアライズド ビューは date
列でグループ化され、同じ列でパーティション分割されます。
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 );
例 3
この例では、ベーステーブルは日別パーティションを使用して transaction_time
という名前の TIMESTAMP
列でパーティション分割されています。マテリアライズド ビューでは transaction_hour
という名前の列を定義し、TIMESTAMP_TRUNC
関数を使用して値を最も近い時間に切り捨てます。マテリアライズド ビューは transaction_hour
でグループ化およびパーティション分割されます。
次の点にご注意ください。
パーティショニング列に適用される切り捨て関数は、少なくともベーステーブルのパーティショニングと同じ粒度である必要があります。たとえば、ベーステーブルが日別パーティションを使用する場合、切り捨て関数では
MONTH
またはYEAR
の粒度を使用できません。マテリアライズド ビューのパーティションの指定では、粒度はベーステーブルと一致する必要があります。
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 );
クラスタのマテリアライズド ビュー
BigQuery のクラスタ化テーブルの制限を満たす場合に限り、マテリアライズド ビューは出力列でクラスタ化できます。集計出力列は、クラスタリング列として使用できません。マテリアライズド ビューにクラスタリング列を追加すると、その列のフィルタを含むクエリのパフォーマンスが向上します。
論理ビューを参照する
この機能に関するフィードバックやサポートをご希望の場合は、bq-mv-help@google.com 宛てにメールをお送りください。
マテリアライズド ビューのクエリは論理ビューを参照できますが、次の制限があります。
- マテリアライズド ビューの制限事項が適用されます。
- 論理ビューが変更されると、マテリアライズド ビューが無効になり、完全に更新する必要があります。
- スマートな調整はサポートされていません。
マテリアライズド ビューを作成する場合の考慮事項
作成するマテリアライズド ビュー
マテリアライズド ビューを作成する場合は、マテリアライズド ビューの定義にベーステーブルに対するクエリパターンが反映されていることを確認します。マテリアライズド ビューはテーブルごとに最大 20 個であるため、クエリを置換するたびに実体化されたビューを作成しないでください。代わりに、マテリアライズド ビューを作成して、より広範なクエリを処理します。
たとえば、ユーザーが列 user_id
または department
で頻繁にフィルタするテーブルに対するクエリについて考えてみましょう。マテリアライズド ビューに user_id = 123
のようなフィルタを追加する代わりに、これらの列をグループ化できます。またオプションとして、これらのフィルタをクラスタ化することもできます。
別の例として、ユーザーは WHERE order_date = CURRENT_DATE()
などの特定の日付や WHERE order_date
BETWEEN '2019-10-01' AND '2019-10-31'
などの期間で日付フィルタを使用することがよくあります。クエリで想定される期間を含む日付範囲のフィルタをマテリアライズド ビューに追加します。
CREATE MATERIALIZED VIEW ... ... WHERE date > '2019-01-01' GROUP BY date
結合
次の推奨事項は、JOIN を使用したマテリアライズド ビューに適用されます。
最も頻繁に変更するテーブルを最初に配置する
最も大きいまたは最も頻繁に変更するテーブルがビュークエリで参照される最初のテーブル/左端のテーブルであることを確認します。結合を使用したマテリアライズド ビューでは、増分クエリがサポートされ、クエリ内で最初のテーブルまたは左端のテーブルが追加されると更新されますが、他のテーブルを変更すると、ビューのキャッシュは完全に無効になります。スタースキーマまたはスノーフレーク スキーマでは、通常、最初のテーブルまたは左端のテーブルはファクト テーブルになります。
クラスタリング キーでの結合を回避する
結合を使用したマテリアライズド ビューは、データが頻繁に集計される場合や元の結合クエリのコストが高い場合に最も効果的です。選択的なクエリの場合、たいてい、BigQuery はすでに結合を効率的に実行でき、マテリアライズド ビューは必要ありません。たとえば、次のマテリアライズド ビューの定義について考えてみましょう。
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 );
store_sales
が ss_store_sk
でクラスタ化され、次のようなクエリを頻繁に実行するとします。
SELECT SUM(ss_net_paid) FROM dataset.store_sales INNER JOIN dataset.store ON ss_store_sk = s_store_sk WHERE s_country = 'Germany';
マテリアライズド ビューは、元のクエリほど効率的でない可能性があります。マテリアライズド ビューの有無にかかわらず、代表的なクエリセットをテストすることをおすすめします。
max_staleness
オプションを指定してマテリアライズド ビューを使用する
max_staleness
マテリアライズド ビュー オプションを使用すると、頻繁に変更される大規模なデータセットを処理する際のコストを抑えながら、一貫した高いクエリ パフォーマンスを実現できます。max_staleness
パラメータを使用すると、クエリ結果のデータの古さが許容される時間間隔を設定して、クエリの費用とレイテンシを削減できます。この動作は、最新のクエリ結果が必須ではないダッシュボードとレポートで役立ちます。
データ未更新
max_staleness
オプションを設定してマテリアライズド ビューにクエリを実行すると、BigQuery は max_staleness
の値と、最後の更新が発生した時刻に基づいて結果を返します。
最後の更新が max_staleness
間隔内の場合、BigQuery はベーステーブルを読み取らず、マテリアライズド ビューから直接データを返します。たとえば、max_staleness
の間隔が 4 時間で、最後の更新が 2 時間前の場合、この条件が適用されます。
最後の更新が max_staleness
間隔の範囲外で行われた場合、BigQuery はマテリアライズド ビューからデータを読み取り、最後の更新以降のベーステーブルの変更と結合して、結合結果を返します。この結合結果は、max_staleness
間隔まで古い可能性があります。たとえば、max_staleness
の間隔が 4 時間で、最後の更新が 7 時間前の場合、この条件が適用されます。
max_staleness
オプションを使用して作成する
次のオプションのいずれかを選択します。
SQL
max_staleness
オプションを使用してマテリアライズド ビューを作成するには、マテリアライズド ビューの作成時に OPTIONS
句を DDL ステートメントに追加します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
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 FROMmy_dataset.my_base_table
GROUP BY 1, 2;次のように置き換えます。
- project-id は、プロジェクト ID です。
- my_dataset は、プロジェクト内のデータセットの ID です。
- my_mv_table は、作成するマテリアライズドビューの ID です。
- my_base_table は、マテリアライズドビューの基本テーブルとして機能するデータセット内のテーブルの ID です。
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。
API
API リクエストの一部として、定義された materializedView
リソースを指定して tables.insert
メソッドを呼び出します。materializedView
リソースには、query
フィールドが含まれます。次に例を示します。
{ "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" }
次のように置き換えます。
- project-id は、プロジェクト ID です。
- my_dataset は、プロジェクト内のデータセットの ID です。
- my_mv_table は、作成するマテリアライズドビューの ID です。
- my_base_table は、マテリアライズドビューの基本テーブルとして機能するデータセット内のテーブルの ID です。
product_id
は、ベーステーブルの列です。clicks
は、ベーステーブルの列です。sum_clicks
は、作成するマテリアライズド ビューの列です。
max_staleness
オプションの適用
このパラメータを既存のマテリアライズド ビューに適用するには、ALTER
MATERIALIZED VIEW
ステートメントを使用します。次に例を示します。
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);
max_staleness
を使用したクエリ
max_staleness
オプションを使用すると、他のマテリアライズド ビュー、論理ビュー、テーブルをクエリする場合と同様に、マテリアライズド ビューをクエリできます。
次に例を示します。
SELECT * FROM project-id.my_dataset.my_mv_table
このクエリは、データが max_staleness
パラメータより古くない場合、最後に更新したデータを返します。マテリアライズド ビューが max_staleness
間隔で更新されていない場合、BigQuery は、使用可能な最新の更新の結果をベーステーブルの変更と統合して、max_staleness
間隔内の結果を返します。
データ ストリーミングと max_staleness
の結果
マテリアライズド ビューのベーステーブルに max_staleness
オプションでデータをストリーミングすると、未更新間隔の開始前にテーブルにストリーミングされたレコードが、マテリアライズド ビューのクエリから除外される場合があります。その結果、複数のテーブルのデータと max_staleness
オプションを含むマテリアライズド ビューは、それらのテーブルのポイントインタイム スナップショットを表さない場合があります。
スマート チューニングと max_staleness
オプション
スマート チューニングでは、クエリがマテリアライズド ビューを参照していない場合でも、max_staleness
オプションに関係なく、可能な限りマテリアライズド ビューを使用するように自動的にクエリを書き換えます。マテリアライズド ビューの max_staleness
オプションは、書き換えられたクエリの結果に影響しません。max_staleness
オプションは、マテリアライズド ビューに直接クエリを実行するクエリにのみ影響します。
未更新と更新頻度の管理
要件に基づいて max_staleness
を設定する必要があります。ベーステーブルからデータを読み込まないようにするには、未更新期間に更新が行われるように更新間隔を構成します。更新の平均実行時間に増加のマージンを加えたものを考慮できます。
たとえば、マテリアライズド ビューの更新に 1 時間が必要で、増加用に 1 時間のバッファが必要な場合は、更新間隔を 2 時間に設定する必要があります。この構成により、レポートが未更新の場合は最長で 4 時間以内に更新が行われるようになります。
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;
非増分マテリアライズド ビュー
非増分マテリアライズド ビューは、OUTER
JOIN
、UNION
、HAVING
句、分析関数など、ほとんどの SQL クエリをサポートします。マテリアライズド ビューがクエリで使用されたかどうかを確認するには、ドライランを使用してコストの見積もりをチェックします。バッチデータ処理やレポートなど、データの未更新が許容されるシナリオでは、非増分のマテリアライズド ビューを使用すると、クエリのパフォーマンスが向上し、コストを削減できます。max_staleness
オプションを使用すると、自動的にメンテナンスされ、未更新保証が組み込まれた、任意の複雑なマテリアライズド ビューを構築できます。
非増分マテリアライズド ビューを使用する
非増分マテリアライズド ビューを作成するには、allow_non_incremental_definition
オプションを使用します。このオプションは、max_staleness
オプションとともに使用する必要があります。マテリアライズド ビューを定期的に更新するには、更新ポリシーも構成する必要があります。更新ポリシーを使用しない場合は、マテリアライズド ビューを手動で更新する必要があります。
マテリアライズド ビューは、常に max_staleness
期間内のベーステーブルの状態を表します。最後の更新が古く、max_staleness
間隔内のベーステーブルを表していない場合、クエリはベーステーブルを読み取ります。パフォーマンスへの影響について詳しくは、データの未更新をご覧ください。
allow_non_incremental_definition
を使用した作成
allow_non_incremental_definition
オプションを使用してマテリアライズド ビューを作成する手順は次のとおりです。マテリアライズド ビューを作成した後に、allow_non_incremental_definition
オプションを変更することはできません。たとえば、値 true
を false
に変更することはできません。また、マテリアライズド ビューから allow_non_incremental_definition
オプションを削除することもできません。
SQL
マテリアライズド ビューを作成するときに、DDL ステートメントに OPTIONS
句を追加します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
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 ;次のように置き換えます。
- my_project は、プロジェクト ID です。
- my_dataset は、プロジェクト内のデータセットの ID です。
- my_mv_table は、作成するマテリアライズドビューの ID です。
- my_dataset.store と my_dataset.store_sales は、マテリアライズド ビューのベーステーブルとして機能するデータセット内のテーブルの ID です。
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。
API
API リクエストの一部として、定義された materializedView
リソースを指定して tables.insert
メソッドを呼び出します。materializedView
リソースには、query
フィールドが含まれます。次に例を示します。
{ "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" }
次のように置き換えます。
- my_project は、プロジェクト ID です。
- my_dataset は、プロジェクト内のデータセットの ID です。
- my_mv_table は、作成するマテリアライズドビューの ID です。
- my_dataset.store と my_dataset.store_sales は、マテリアライズド ビューのベーステーブルとして機能するデータセット内のテーブルの ID です。
allow_non_incremental_definition
を使用したクエリ
非増分マテリアライズド ビューは、他のマテリアライズド ビュー、論理ビュー、テーブルと同様にクエリできます。
次に例を示します。
SELECT * FROM my_project.my_dataset.my_mv_table
データが max_staleness
パラメータより古くない場合、このクエリは最後の更新からデータを返します。データの未更新と鮮度の詳細については、データの未更新をご覧ください。
非増分マテリアライズド ビューに固有の制限事項
次の制限は、allow_non_incremental_definition
オプションが指定されたマテリアライズド ビューにのみ適用されます。サポートされているクエリ構文の制限を除き、マテリアライズド ビューの制限事項が引き続き適用されます。
- スマート調整は、
allow_non_incremental_definition
オプションを含むマテリアライズド ビューには適用されません。allow_non_incremental_definition
オプションを使用してマテリアライズド ビューを利用する唯一の方法は、直接クエリすることです。 allow_non_incremental_definition
オプションのないマテリアライズド ビューでは、データのサブセットを段階的に更新できます。allow_non_incremental_definition
オプションを含むマテリアライズド ビューは、全体を更新する必要があります。- max_staleness オプションを含むマテリアライズド ビューは、クエリの実行時に列レベルのセキュリティ制約が存在することを検証します。詳しくは、列レベルのアクセス制御をご覧ください。