マテリアライズド ビューを作成する

このドキュメントでは、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 のクリック数のマテリアライズド ビューを作成します。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    [BigQuery] に移動

  2. クエリエディタで次のステートメントを入力します。

    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)。

  3. [実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。

次の例では、各プロダクト 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 という名前のビューが作成されます。

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"
  }

}

Google Cloud プロジェクトで Terraform 構成を適用するには、次のセクションの手順を完了します。

Cloud Shell を準備する

  1. Cloud Shell を起動します。
  2. Terraform 構成を適用するデフォルトの Google Cloud プロジェクトを設定します。

    このコマンドは、プロジェクトごとに 1 回だけ実行する必要があります。これは任意のディレクトリで実行できます。

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Terraform 構成ファイルに明示的な値を設定すると、環境変数がオーバーライドされます。

ディレクトリを準備する

Terraform 構成ファイルには独自のディレクトリ(ルート モジュールとも呼ばれます)が必要です。

  1. Cloud Shell で、ディレクトリを作成し、そのディレクトリ内に新しいファイルを作成します。ファイルの拡張子は .tf にする必要があります(例: main.tf)。このチュートリアルでは、このファイルを main.tf とします。
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf
  2. チュートリアルを使用している場合は、各セクションまたはステップのサンプルコードをコピーできます。

    新しく作成した main.tf にサンプルコードをコピーします。

    必要に応じて、GitHub からコードをコピーします。Terraform スニペットがエンドツーエンドのソリューションの一部である場合は、この方法をおすすめします。

  3. 環境に適用するサンプル パラメータを確認し、変更します。
  4. 変更を保存します。
  5. Terraform を初期化します。これは、ディレクトリごとに 1 回だけ行います。
    terraform init

    最新バージョンの Google プロバイダを使用する場合は、-upgrade オプションを使用します。

    terraform init -upgrade

変更を適用する

  1. 構成を確認して、Terraform が作成または更新するリソースが想定どおりであることを確認します。
    terraform plan

    必要に応じて構成を修正します。

  2. 次のコマンドを実行します。プロンプトで「yes」と入力して、Terraform 構成を適用します。
    terraform apply

    Terraform に「Apply complete!」というメッセージが表示されるまで待ちます。

  3. 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 に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。

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());
    }
  }
}

マテリアライズド ビューが正常に作成されると、Google Cloud コンソールの BigQuery の [エクスプローラ] パネルに表示されます。次の例は、マテリアライズド ビューのスキーマを示しています。

Google Cloud コンソールのマテリアライズド ビューのスキーマ

自動更新を無効にしない限り、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 機能はサポートされていません。

LEFT OUTER JOINUNION ALL のサポート

この機能に関するフィードバックやサポートをご希望の場合は、bq-mv-help @google.com 宛てにメールをお送りください。

増分マテリアライズド ビューは、LEFT OUTER JOINUNION 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 に対するマテリアライズド ビューを作成する手順は次のとおりです。

  1. 次のいずれかの方法で Iceberg テーブルを取得します。

    CREATE EXTERNAL TABLE mydataset.myicebergtable
      WITH CONNECTION `myproject.us.myconnection`
      OPTIONS (
            format = 'ICEBERG',
            uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
      )
    
  2. 次のパーティション仕様で Iceberg テーブルを参照します。

    "partition-specs" : [ {
       "spec-id" : 0,
       "fields" : [ {
        "name" : "birth_month",
        "transform" : "month",
        "source-id" : 3,
        "field-id" : 1000
    } ]
    
  3. パーティションと連携したマテリアライズド ビューを作成します。

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

制限事項

Iceberg テーブルのマテリアライズド ビューには、標準の Iceberg テーブルの制限のほかに、次の制限があります。

  • ベーステーブルとパーティション分割が一致するマテリアライズド ビューを作成できます。ただし、マテリアライズド ビューは、時間ベースのパーティション変換YEARMONTHDAYHOUR など)のみをサポートします。
  • マテリアライズド ビューのパーティションの粒度をベーステーブルのパーティションの粒度よりも高くすることはできません。たとえば、birth_date 列を使用してベーステーブルを年単位でパーティション分割する場合、PARTITION BY DATE_TRUNC(birth_date, MONTH) を使用してマテリアライズド ビューを作成することはできません。
  • スキーマを変更すると、マテリアライズド ビューが無効になります。
  • パーティションの進化がサポートされています。ただし、マテリアライズド ビューを再作成せずにベーステーブルのパーティショニング列を変更すると、完全に無効になり、更新しても修正できなくなる可能性があります。
  • ベーステーブルには少なくとも 1 つのスナップショットが必要です。
  • Iceberg テーブルは、BigLake テーブル(承認済みの外部テーブルなど)である必要があります。
  • VPC Service Controls が有効になっている場合は、承認済み外部テーブルのサービス アカウントを上り(内向き)ルールに追加する必要があります。追加しなければ、VPC Service Controls によって、マテリアライズド ビューの自動バックグラウンド更新がブロックされます。

Iceberg テーブルの metadata.json ファイルには、以下の要素を指定する必要があります。これらを指定しないと、クエリはベーステーブルをスキャンするので、具体的な結果を使用できません。

パーティション分割されたマテリアライズド ビュー

パーティション分割テーブルのマテリアライズド ビューはパーティショニングできます。マテリアライズド ビューのパーティション分割は、クエリがパーティションのサブセットにアクセスすることが多い場合にメリットが得られる点で、通常のテーブルのパーティショニングに似ています。また、マテリアライズド ビューをパーティショニングすることで、ベーステーブルまたはテーブルのデータが変更または削除されたときのビューの動作を改善できます。詳細については、パーティションの配置をご覧ください。

ベーステーブルがパーティション分割されている場合は、マテリアライズド ビューを同じパーティショニング列でパーティション分割できます。時間ベースのパーティションの場合は、粒度(時間単位、日単位、月単位、年単位)が一致する必要があります。整数範囲のパーティションの場合、範囲指定は正確に一致する必要があります。パーティション分割されていないベーステーブルでは、マテリアライズド ビューをパーティション分割できません。

ベーステーブルを取り込み時間でパーティション分割する場合、マテリアライズド ビューのグループ化とパーティション分割は、ベーステーブルの _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_salesss_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 ステートメントに追加します。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    [BigQuery] に移動

  2. クエリエディタで次のステートメントを入力します。

    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;

    次のように置き換えます。

    • 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 JOINUNIONHAVING 句、分析関数など、ほとんどの 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 オプションを変更することはできません。たとえば、値 truefalse に変更することはできません。また、マテリアライズド ビューから allow_non_incremental_definition オプションを削除することもできません。

SQL

マテリアライズド ビューを作成するときに、DDL ステートメントに OPTIONS 句を追加します。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    [BigQuery] に移動

  2. クエリエディタで次のステートメントを入力します。

    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.storemy_dataset.store_sales は、マテリアライズド ビューのベーステーブルとして機能するデータセット内のテーブルの ID です。

  3. [実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。

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.storemy_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 オプションを含むマテリアライズド ビューは、クエリの実行時に列レベルのセキュリティ制約が存在することを検証します。詳しくは、列レベルのアクセス制御をご覧ください。

次のステップ