マテリアライズド ビューの作成と使用

このドキュメントでは、BigQuery マテリアライズド ビューを使用して、マテリアライズド ビューを作成および使用する方法について説明します。このドキュメントを読む前に、マテリアライズド ビューの概要を理解してください。集計のないマテリアライズド ビュー結合を使用したマテリアライズド ビューのプレビュー版が利用可能になりました。

必要な権限

マテリアライズド ビューを操作するには、マテリアライズド ビューに対する権限が必要です。次の表に、マテリアライズド ビューに対して実行できるオペレーションの種類、各オペレーションに使用可能なコマンドとメソッド、各オペレーションに必要な権限、そのような権限を含むデフォルトのロールを示します。

BigQuery Identity and Access Management(IAM)の詳細については、事前定義ロールと権限をご覧ください。

オペレーションのタイプ コマンドまたはメソッド 必要な権限 デフォルトのロール
作成 CREATE MATERIALIZED VIEW bq mk --materialized view tables.insert bigquery.tables.create bigquery.dataEditor bigquery.dataOwner bigquery.admin
クエリ 標準 SQL クエリ bq query tables.getData bigquery.dataViewer bigquery.dataEditor bigquery.dataOwner bigquery.admin
更新 ALTER MATERIALIZED VIEW bq query bq update tables.patch tables.update bigquery.tables.get bigquery.tables.update bigquery.dataEditor bigquery.dataOwner bigquery.admin
削除 DROP MATERIALIZED VIEW bq query bq rm tables.delete bigquery.tables.get bigquery.tables.delete bigquery.dataEditor bigquery.dataOwner bigquery.admin
手動更新 CALL BQ.REFRESH_MATERIALIZED_VIEW bigquery.tables.getData bigquery.tables.update bigquery.tables.updateData bigquery.dataEditor bigquery.dataOwner bigquery.admin

マテリアライズド ビューの作成

BigQuery のマテリアライズド ビューは、Google Cloud Console、bq コマンドライン ツール、または BigQuery API を使用して作成できます。

この例では、ベーステーブルの名前が my_base_table で、次のスキーマが存在するとします。

列名
product_id 整数
clicks 整数

また、product_id あたりのクリック数をまとめた実体化されたビューが必要だとします。次の手順に従って、次のスキーマを持つ my_mv_table という実体化されたビューを作成します。

列名
product_id 整数
sum_clicks 整数

実体化されたビューを作成するには、次の手順を行います。

Console

データ定義言語(DDL)ステートメントを使用すると、標準 SQL クエリ構文を使用してテーブルとビューの作成と変更ができます。

データ定義言語ステートメントの使用をご覧ください。

DDL ステートメントを使用して Cloud Console にマテリアライズド ビューを作成するには:

  1. Cloud Console で、[BigQuery] ページに移動します。

    [BigQuery] に移動

  2. [クエリを新規作成] をクリックします。

  3. [クエリエディタ] テキスト領域に CREATE MATERIALIZED VIEW DDL ステートメントを入力します。

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
    AS SELECT product_id, SUM(clicks) AS sum_clicks
    FROM  project-id.my_dataset.my_base_table
    GROUP BY 1
    

    ここで

    • project-id は、プロジェクト ID です。
    • my_dataset は、プロジェクト内のデータセットの ID です。
    • my_mv_table は、作成する実体化されたビューの ID です。
    • my_base_table は、実体化されたビューの基本テーブルとして機能するデータセット内のテーブルの ID です。
    • product_id は、ベーステーブルの列です。
    • clicks は、ベーステーブルの列です。
    • sum_clicks は、作成するマテリアライズド ビューの列です。
  4. [実行] をクリックします。

自動更新を無効にしない限り、BigQuery はマテリアライズド ビューの非同期全体更新を開始します。クエリはすぐに成功を返しますが、最初の更新は実行が続く場合があります。正常に作成されたマテリアライズド ビューは、[データセット] ペインに表示されます。

bq

DDL ステートメントをクエリ パラメータとして指定して、bq query コマンドを使用します。

bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM  project-id.my_dataset.my_base_table
GROUP BY 1'

ここで

  • project-id は、プロジェクト ID です。
  • my_dataset は、プロジェクト内のデータセットの ID です。
  • my_mv_table は、作成する実体化されたビューの ID です。
  • my_base_table は、実体化されたビューの基本テーブルとして機能するデータセット内のテーブルの ID です。
  • product_id は、ベーステーブルの列です。
  • clicks は、ベーステーブルの列です。
  • sum_clicks は、作成するマテリアライズド ビューの列です。

自動更新を無効にしない限り、BigQuery はマテリアライズド ビューの非同期全体更新を開始します。クエリはすぐに成功を返しますが、最初の更新は実行が続く場合があります。正常に作成されたマテリアライズド ビューは、[データセット] ペインに表示されます。

あるいは、--materialized_view 引数付きで bq mk コマンドを使用して、マテリアライズド ビューを作成します。以下の引数は、--materialized_view 引数とともに使用できます。

  • --enable_refresh: 自動更新を有効にするかどうか指定します。
  • --refresh_interval_ms: 更新の間隔をミリ秒単位で指定します。

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

ここで

  • project-id は、プロジェクト ID です。
  • my_dataset は、プロジェクト内のデータセットの ID です。
  • my_mv_table は、作成する実体化されたビューの ID です。
  • my_base_table は、実体化されたビューの基本テーブルとして機能するデータセット内のテーブルの ID です。
  • product_id は、ベーステーブルの列です。
  • clicks は、ベーステーブルの列です。
  • sum_clicks は、作成するマテリアライズド ビューの列です。

自動更新を無効にしない限り、BigQuery はマテリアライズド ビューの非同期全体更新を開始します。クエリはすぐに成功を返しますが、最初の更新は実行が続く場合があります。正常に作成されたマテリアライズド ビューは、[データセット] ペインに表示されます。

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

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

アクセス制御

マテリアライズド ビューへのアクセス権は、データセット レベルビューレベル、または列レベルで付与できます。IAM リソース階層のより高いレベルでアクセス権を設定することもできます。

マテリアライズド ビューに対するクエリ

通常のテーブルまたは標準ビューに対してクエリを行うのと同じ方法で直接、マテリアライズド ビューに対してクエリを実行します。

マテリアライズド ビューを持つベーステーブルにクエリを実行すると、マテリアライズド ビューに格納されたキャッシュ結果を使用するように、クエリ オプティマイザーによって自動的にクエリが書き換えられる場合があります。この書き換え処理は、ベーステーブルとマテリアライズド ビューが同じデータセットにある場合にのみ実行できます。クエリプランには、マテリアライズド ビューを使用するようにクエリが書き換えられたことが示されます。

クエリの書き換えに複数のマテリアライズド ビューを使用できる場合、スキャンする行の推定数が最も少ないマテリアライズド ビューが使用されます。

最新でないマテリアライズド ビューのクエリを実行すると、マテリアライズド ビューのデータがベーステーブルの差分と結合され、最新の結果が計算されます。マテリアライズド ビューが最新ではない場合がある理由には、以下のようなものがあります。

  • ユーザーが手動で更新を制御することを選択したため、マテリアライズド ビューの自動更新が設定されていない。

  • ベーステーブルが頻繁に更新されるため、更新のフリークエンシー キャップが適用されている。

  • ベーステーブルが数秒前に変更されたため、マテリアライズド ビューの更新がベーステーブルに間に合っていない。

これらの理由が原因でマテリアライズド ビューが最新でない場合でも、マテリアライズド ビューのクエリは引き続き最新です。クエリの結果に、ベーステーブルからの変更も含まれているためです。

クエリの実行方法については、BigQuery データのクエリの概要をご覧ください。

マテリアライズド ビューの変更

マテリアライズド ビューは、Cloud Console または bq コマンドライン ツールから、ALTER MATERIALIZED VIEWSET OPTIONS を付けて DDL を使用することで変更できます。

次の例では、enable_refreshtrue に設定しています。必要に応じて、ユースケースに合わせて調整してください。

Console

Cloud Console で DDL ステートメントを使用してマテリアライズド ビューを変更するには:

  1. Cloud Console で、[BigQuery] ページを開きます。

[BigQuery] に移動

  1. [クエリを新規作成] をクリックします。

  2. [クエリエディタ] テキスト領域に ALTER MATERIALIZED VIEW DDL ステートメントを入力します。

    ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    SET OPTIONS (enable_refresh=true)
    

    ここで

    • project-id は、プロジェクト ID です。
    • my_dataset は、プロジェクト内のデータセットの ID です。
    • my_mv_table は、変更する実体化されたビューの ID です。
  3. [実行] をクリックします。

bq

DDL ステートメントをクエリ パラメータとして指定して、bq query コマンドを使用します。

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)

ここで

  • project-id は、プロジェクト ID です。
  • my_dataset は、プロジェクト内のデータセットの ID です。
  • my_mv_table は、変更する実体化されたビューの ID です。

または、bq update コマンドを実行します。

bq update \
--enable_refresh=true \
--refresh_interval_ms= \
project-id.my_dataset.my_mv_table

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryMaterializedView {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query = String.format("SELECT * FROM %s.%s", datasetName, materializedViewName);
    queryMaterializedView(query);
  }

  public static void queryMaterializedView(String query) throws InterruptedException {
    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();

      TableResult results = bigquery.query(QueryJobConfiguration.of(query));
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s\n", val.toString())));

      System.out.println("Query performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query was not performed. \n" + e.toString());
    }
  }
}

マテリアライズド ビューの操作

マテリアライズド ビューの操作に使用できるのは、CREATEDROPALTER ステートメント(およびそれぞれ対応する API)、ならびに手動更新のみです。

実体化されたビューでは、次のオペレーションは使用できません。

  • ソースまたは宛先が実体化されたビューであるコピー、インポート、エクスポートのジョブの実行。

  • クエリ結果のマテリアライズド ビューへの書き込み。

  • tabledata.list の呼び出し。

  • BigQuery Storage Read API の使用。

次に、マテリアル ビューを変更する例を示します。

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

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.Table;
import com.google.cloud.bigquery.TableId;

// Sample to alter materialized view
public class AlterMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    alterMaterializedView(datasetName, materializedViewName);
  }

  public static void alterMaterializedView(String datasetName, String materializedViewName) {
    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);

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Alter materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view altered successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not altered. \n" + e.toString());
    }
  }
}

マテリアライズド ビューの削除

マテリアライズド ビューは、Cloud Console、bq コマンドライン ツール、または API を使用して削除できます。

Console

Cloud Console で DDL ステートメントを使用してマテリアライズド ビューを削除するには:

  1. Cloud Console で、[BigQuery] ページを開きます。

[BigQuery] に移動

  1. [クエリを新規作成] をクリックします。

  2. [クエリエディタ] テキスト領域に DELETE MATERIALIZED VIEW DDL ステートメントを入力します。

    DROP MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    

    ここで

    • project-id は、プロジェクト ID です。
    • my_dataset は、プロジェクト内のデータセットの ID です。
    • my_mv_table は、削除するマテリアライズド ビューの ID です。
  3. [実行] をクリックします。

bq

DDL ステートメントをクエリ パラメータとして指定して、bq query コマンドを使用します。

bq query --use_legacy_sql=false '
DROP MATERIALIZED VIEW
project-id.my_dataset.my_mv_table'

ここで

  • project-id は、プロジェクト ID です。
  • my_dataset は、プロジェクト内のデータセットの ID です。
  • my_mv_table は、削除するマテリアライズド ビューの ID です。

あるいは、bq rm コマンドを使用して、マテリアライズド ビューを作成することもできます。

API

tables.delete メソッドを呼び出し、projectIddatasetIdtableId パラメータの値を指定します。

  • projectId パラメータをプロジェクト ID に割り当てます。
  • datasetId パラメータをデータセット ID に割り当てます。
  • tableId パラメータを、削除するマテリアライズド ビューのテーブル ID に割り当てます。

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

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

// Sample to delete materialized view
public class DeleteMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    deleteMaterializedView(datasetName, materializedViewName);
  }

  public static void deleteMaterializedView(String datasetName, String materializedViewName) {
    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);

      boolean success = bigquery.delete(tableId);
      if (success) {
        System.out.println("Materialized view deleted successfully");
      } else {
        System.out.println("Materialized view was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not found. \n" + e.toString());
    }
  }
}

マテリアライズド ビューのモニタリング

マテリアライズド ビューとマテリアライズド ビュー更新ジョブに関する情報は、BigQuery API を使用して取得できます。

マテリアライズド ビューのモニタリング

マテリアライズド ビューを検出するには、tables.list メソッドを呼び出すか、INFORMATION_SCHEMA.TABLES テーブルに対してクエリを実行します。

マテリアライズド ビューのプロパティを取得するには、tables.get メソッドを呼び出すか、INFORMATION_SCHEMA.TABLE_OPTIONS テーブルにクエリを実行します。

マテリアライズド ビューは、INFORMATION_SCHEMA.VIEWS テーブルには表示されません。

マテリアライズド ビュー更新ジョブのモニタリング

マテリアライズド ビュー更新ジョブの一覧を表示するには、jobs.list メソッドを呼び出します。ジョブの詳細を取得するには、jobs.get メソッドを呼び出します。自動更新ジョブは、ジョブ ID の先頭に materialized_view_refresh という接頭辞が付いており、BigQuery 管理者アカウントによって開始されます。

次に例を示します。

SELECT job_id, total_slot_ms, total_bytes_processed
FROM region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE "%materialized_view_refresh_%"
LIMIT 10

サポートされているマテリアライズド ビュー

実体化されたビューは、単一のテーブルの集計である必要があります。GROUP BY の使用はオプションです。集計のないマテリアライズド ビュー結合を使用したマテリアライズド ビューのプレビュー版が利用可能になりました。

マテリアライズド ビューは、制限付き SQL 構文を使用します。クエリで次のパターンを使用する必要があります。

SELECT
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

現在サポートされているのは、次の集計関数のみです。

  • 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
  • SUM

集計に計算やフィルタリングを追加しない限り、特定のサブクエリがサポートされます。たとえば、次の SQL クエリがサポートされています。

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
SELECT ts_hour, COUNT(*) as cnt
FROM tmp
GROUP BY ts_hour

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT * FROM tmp

SELECT ts_hour, COUNT(*) as cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
GROUP BY ts_hour

SELECT * FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)

次の SQL クエリは、集計に計算が適用されているため、マテリアライズド ビューでサポートされていません。

-- Not supported for a materialized view
WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT TIMESTAMP_TRUNC(ts_hour, DAY) as ts_day, cnt+1 as cnt FROM tmp

次の SQL クエリは、集計にフィルタリングが適用されているため、マテリアライズド ビューでサポートされていません。

-- Not supported for a materialized view
SELECT ts_hour, cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
WHERE ts_hour != TIMESTAMP(DATE('2020-01-01'))

FROM

FROM 句は 1 つのテーブルを含む必要があり、1 つ以上の配列式をネスト解除できます。

FROM mytable

FROM mytable AS t, t.struct_column.array_field AS x

FROM mytable AS t LEFT JOIN UNNEST(t.array_column) AS x

FROM mytable AS t, t.array_column AS x, x.array_field AS y

FROM mytable AS t, UNNEST(SPLIT(t.string_column)) AS x

WITH OFFSET はサポートされていません。

サポートされているクエリ書き換えパターン

ベーステーブル クエリとマテリアライズド ビューの定義では、以下のクエリ書き換えパターンがサポートされています。

パターン 1

このパターンは、クエリのグループキーとアグリゲータが、実体化されたビューのグループキーとアグリゲータのサブセットであることを示しています。

次のベーステーブル クエリに対して

SELECT
  ss_sold_date_sk,
  SUM(ss_net_profit) AS sum_profit
FROM store_sales
GROUP BY 1

以下は、ベーステーブル クエリのグループキーとアグリゲータを、実体化されたビューのクエリのサブセットとして表示する実体化されたビュークエリです。

SELECT
  ss_store_sk,
  ss_sold_date_sk,
  SUM(ss_net_paid) AS sum_paid,
  SUM(ss_net_profit) AS sum_profit,
  COUNT(*) AS cnt_sales
FROM store_sales
GROUP BY 1, 2

パターン 2

このパターンは、ベーステーブル クエリのグループキーが、実体化されたビューのグループキーから計算できることを示しています。

次のベーステーブル クエリに対して

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

以下は、ベーステーブルのグループキーを計算する実体化されたビュークエリです。

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

パターン 3

このパターンは、クエリのフィルタ式が実体化されたビューのグループキーから得られる、または実体化されたビューと完全に一致することを示します。

次のベーステーブル クエリに対して

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE DATE(l_shipdate) >= DATE(2016, 01, 01) AND l_shipmode = "AIR"
GROUP BY 1

以下は、ベーステーブルからフィルタ式を得る実体化されたビュークエリです。

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE l_shipmode = "AIR"
GROUP BY 1

パターン 4

このパターンは、実体化されたビューのデータのサブセットを選択するベーステーブル クエリの式を示しています。

次のベーステーブル クエリに対して

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount > 20.0
GROUP BY 1

以下は、実行した結果がベーステーブル クエリのスーパーセットになる実体化されたビュークエリです。

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount IS NOT NULL
GROUP BY 1

増分アップデート

ベーステーブルが追加のみの変更を行い続ける場合、マテリアライズド ビューを使用するクエリは、すべてのマテリアライズド ビュー データと、最後の更新以降のベーステーブルの差分をスキャンします。これは、マテリアライズド ビューが、クエリ オプティマイザーによって明示的に参照または選択されている場合に適用されます。これにより、どちらの場合もより高速で低コストのクエリが可能になります。

マテリアライズド ビューを更新した後にベーステーブルが更新または削除された場合、そのマテリアライズド ビューはスキャンされない可能性があります。

更新または削除の原因となるアクションの例を次に示します。

  • DML UPDATE
  • DML MERGE
  • DML DELETE
  • 切り捨て
  • パーティションの有効期限
  • Console、bq コマンドライン、およびこのリストの項目の API に相当するもの

これらのアクションが発生した場合、実体化されたビューのクエリはビューの更新が次に発生するまでいかなるコスト削減も行えない可能性があります。ベーステーブルで更新または削除を行うと、実体化されたビューの状態の一部が無効になります。パーティション分割されていないビューの場合、ビュー全体が無効になります。ほとんどの場合、パーティション分割ビューでは、影響を受けるパーティションのみが無効になります。

ベーステーブルの BigQuery ストリーミング バッファのデータは、マテリアライズド ビューには保存されません。実体化されたビューが使用されているかどうかにかかわらず、ストリーミング バッファは引き続き最後までスキャンされます。

パーティション分割テーブルとクラスタ化テーブルの操作

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

ベーステーブルを取り込み時間でパーティション分割する場合、マテリアライズド ビューのグループ化とパーティション分割は、ベーステーブルの _PARTITIONDATE 列ごとに可能です。

マテリアライズド ビューのクラスタ化は、BigQuery のクラスタ化テーブルの制限に沿って任意の列で行えます。

例 1

この例では、ベーステーブルは日別パーティションを使用して transaction_time 列でパーティション分割されています。マテリアライズド ビューは同じ列でパーティション分割され、employee_id 列でクラスタ化されます。

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

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_time)
CLUSTER BY employee_id
AS SELECT
  employee_id,
  transaction_time,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

例 2

この例では、ベーステーブルは日別パーティションを使用して取り込み時間でパーティション分割されています。マテリアライズド ビューでは、date という名前の列として取り込み時間を選択します。マテリアライズド ビューは date 列でグループ化され、同じ列でパーティション分割されます。

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64)
PARTITION BY _PARTITIONDATE
OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW project-id.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 1, 2;

例 3

この例では、ベーステーブルは日別パーティションを使用して transaction_time という名前の TIMESTAMP 列でパーティション分割されています。マテリアライズド ビューでは transaction_hour という名前の列を定義し、TIMESTAMP_TRUNC 関数を使用して値を最も近い時間に切り捨てます。マテリアライズド ビューは transaction_hour でグループ化およびパーティション分割されます。

次の点にご注意ください。

  • パーティショニング列に適用される切り捨て関数は、少なくともベーステーブルのパーティショニングと同じ粒度である必要があります。たとえば、ベーステーブルが日別パーティションを使用する場合、切り捨て関数では MONTH または YEAR の粒度を使用できません。

  • マテリアライズド ビューのパーティションの指定では、粒度はベーステーブルと一致する必要があります。

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

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_hour)
AS SELECT
  employee_id,
  TIMESTAMP_TRUNC(transaction_time, HOUR) as transaction_hour,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

パーティションの配置

実体化されたビューがパーティション分割されている場合、BigQuery では確実にそのパーティションがベーステーブルのパーティションと一致するように変更されます。たとえば、ベーステーブルのパーティション 2020-01-01 の行が、マテリアライズド ビューのパーティション 2020-01-01 の行に集約されます。

パーティションの配置によって、マテリアライズド ビューを効率的に維持できるようになります。更新の間、マテリアライズド ビューの中で唯一の更新されるパーティションは、ベーステーブルの対応するパーティションに追加、更新、または削除が存在するパーティションです(まれに、ベーステーブルにおいて削除や更新を行うと、ビュー全体が再計算される場合があります)。クエリの際、パーティショニング アライメントを行うと、パーティション フィルタがマテリアライズド ビュー スキャンとベーステーブル スキャンの両方に適用されます。

パーティションの有効期限

パーティションの有効期限をマテリアライズド ビューに設定することはできません。マテリアライズド ビューは、ベーステーブルからパーティションの有効期限を暗黙的に継承します。マテリアライズド ビューのパーティションは、ベーステーブル パーティションと同期されるため、同時に一斉に期限切れになります。

パーティション分割テーブルの上にマテリアライズド ビューが作成された後、テーブルのパーティションの有効期限を変更することはできません。ベーステーブルのパーティションの有効期限を変更するには、まず、そのテーブル上に作成したすべてのマテリアライズド ビューを削除する必要があります。

マテリアライズド ビューの更新

ベーステーブルから事前計算された結果に、自動更新と手動更新のどちらを使用するかを BigQuery で指定できます。デフォルト値を使用しない場合、マテリアライズド ビューを作成する際に更新設定を構成できます。更新設定は、マテリアライズド ビューの作成後に変更することもできます。

マテリアライズド ビューはいつでも手動で更新できます。

自動更新

デフォルトでは、マテリアライズド ビューはベーステーブルの変更から 5 分以内に自動的に更新されますが、前の更新から 30 分以内に更新されることはありません。変更の例としては、行の挿入や行の削除があります。

自動更新はいつでも有効または無効にできます。

テーブルを作成するときに自動更新をオフにするには、enable_refreshfalse に設定します。

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY RANGE_BUCKET(column, buckets)
OPTIONS (enable_refresh = false)
AS SELECT ...

既存のマテリアライズド ビューの場合は、ALTER MATERIALIZED VIEW を使用して enable_refresh 値を変更できます。

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true)

自動更新が無効になっている場合も、マテリアライズド ビューの更新はいつでも手動で行えます。

フリークエンシー キャップの設定

自動更新を実行する頻度については、フリークエンシー キャップを構成できます。デフォルトでは、実体化されたビューは 30 分ごとに更新されます。

更新のフリークエンシー キャップはいつでも変更できます。

マテリアライズド ビューの作成時に更新のフリークエンシー キャップを設定するには、DDL で refresh_interval_minutes(または API と bq コマンドライン ツールで refresh_interval_ms)を設定します。

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT ...

同様に、テーブルを変更するときにもフリークエンシー キャップを設定できます。この例では、すでに自動更新を有効にしていて、フリークエンシー キャップの変更だけを行うことを想定しています。

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (refresh_interval_minutes = 60)

更新のフリークエンシー キャップの最小値は、1 分です。更新のフリークエンシー キャップの最大値は、7 日間です。

マテリアライズド ビューは、いつでも手動で更新できます。手動更新のタイミングは、フリークエンシー キャップによらず自由に選択できます。

ベスト エフォート

自動更新はベスト エフォート ベースで行われます。BigQuery では、ベーステーブルの変更後 5 分以内に更新の開始が試行されます(前回の更新が 30 分以上前に行われた場合)。しかし、更新がその時点で開始されることや、いつ更新が完了するかが保証されるわけではありません。(マテリアライズド ビューのクエリはベーステーブルの最新の状態を反映しますが、ビューが最近更新されていない場合は、クエリの費用やレイテンシが予想より大きくなる可能性があります。)

自動更新は、バッチ優先度でのクエリの実行と同様に処理されます。マテリアライズド ビューのプロジェクトに現時点でその容量がない場合、更新が遅延します。プロジェクトに更新の費用が高いビューが多く含まれる場合、個々のビューはベーステーブルと比較してかなり遅くなる可能性があります。

手動更新

マテリアライズド ビューのデータを更新するには、任意のタイミングで BQ.REFRESH_MATERIALIZED_VIEW システム プロシージャを呼び出します。このプロシージャが呼び出されると、ベーステーブルで行われた変更が BigQuery によって識別され、その変更がマテリアライズド ビューに適用されます。更新が完了すると、BQ.REFRESH_MATERIALIZED_VIEW を実行するクエリが終了します。

CALL BQ.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')

集約のないマテリアライズド ビュー(プレビュー)

集計のないマテリアライズド ビュー(射影マテリアライズド ビュー)を使用して、事前にデータの再クラスタ化、フィルタリング、計算を行うことができます。集計のないマテリアライズド ビューはインデックスのように機能します。そのため、BigQuery では、クエリ対象のバイト数がベーステーブルと 1 つ以上の使用可能な修飾マテリアライズド ビューのどちらにあるかを予測することで、クエリを最適化できます。集計のないマテリアライズド ビューにも、今後のクエリのパフォーマンスを最適化するための事前フィルタ済みデータや事前計算データを提供できます。

ユースケース

集計のないマテリアライズド ビューの使用方法を、次の表に示します。

CREATE TABLE dataset.base_table (
  x INT64,
  y INT64,
  string_field STRING)
CLUSTER BY x;

データの再クラスタ化

ベーステーブルと異なるクラスタリング スキームを使用したクエリの多くを発行する場合、マテリアライズド ビューを使用するとクエリのパフォーマンスが向上する可能性があります。

前述の例では、dataset.base_tablex 列でクラスタ化されているため、この列でフィルタリングまたは集計を行うクエリに最適化されています。y 列をフィルタリングするクエリを頻繁に実行し、x 列は実行しない場合、マテリアライズド ビューに従うと、これらのクエリのパフォーマンスが向上する可能性があります。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY y
AS SELECT * FROM dataset.base_table;

マテリアライズド ビューを使用するように BigQuery のオプティマイザーによってクエリ SELECT COUNT(*) FROM dataset.base_table WHERE y = 123 が書き換えられると、BigQuery がスキャンする必要があるデータ量が減ります。

データの事前フィルタリング

テーブルの特定のサブセットのみを読み取るクエリを頻繁に実行する場合は、マテリアライズド ビューを使用してクエリのパフォーマンスを向上させることができます。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY x
AS SELECT * FROM dataset.base_table
WHERE y < 1000;

多くの場合、y 値が 1,000 より大きいと仮定すると、マテリアライズド ビューを使用するように BigQuery オプティマイザーによってクエリ SELECT COUNT(*) FROM dataset.base_table WHERE x = 123 AND y < 500 が書き換えられる可能性が高くなります。クエリ内の述語は、ビュー内の述語と完全に一致する必要はありません。クエリでは、サポートされるクエリ書き換えパターンで説明されているように、ビュー内のデータのサブセットを選択できます。

データの事前計算

コンピューティング コストが高い関数を頻繁に使用する場合や、大きな列から少量のデータを抽出する場合は、マテリアライズド ビューを使用するとクエリのパフォーマンスが向上します。

string_field に JSON データが含まれていて、特定のサブフィールドの値を取得したいことが多い場合は、次のマテリアライズド ビューを使用すると、それらのクエリのパフォーマンスを向上させることができます。

CREATE MATERIALIZED VIEW dataset.mv
AS SELECT x, JSON_EXTRACT(string_field, "$.subfield1.subfield2") subfield2
FROM dataset.base_table;

BigQuery オプティマイザーがクエリ SELECT JSON_EXTRACT(string_field, "$.subfield1.subfield2") FROM dataset.base_table WHERE x = 123 を書き換えて、マテリアライズド ビューを使用するようになります。BigQuery オプティマイザーでは、最終的な計算コストではなく、クエリの選択時にクエリされたバイト数のみが考慮されます。

内部結合を使用したマテリアライズド ビュー(プレビュー)

結合を使用したマテリアライズド ビューを利用すると、1 つの大きなファクト テーブルが 1 つ以上の小さなディメンション テーブルと頻繁に結合されるスターやスノーフレークのようなスキーマを持つデータセットのパフォーマンスが向上し、コストが削減されます。結合を使用したマテリアライズド ビューを使用すると、データを事前に結合および集約することで、クエリ時にデータを結合する必要がなくなります。小さなデータセットを返す、コンピューティング コストが高くて大規模で複雑なクエリに最適です。

制限事項

既存のマテリアライズド ビューの制限に加えて、結合を使用したマテリアライズド ビューには次の制限があります。

  • 既存のマテリアライズド ビューと同じアグリゲータのセットを使用して集計する必要があります。
  • 内部結合のみがサポートされています。クロス結合、完全結合、左結合、右結合はサポートされていません。
  • パーティション分割ビューは、クエリの最初または左端のテーブルからパーティショニング列を取得する必要があります。
  • 自己結合はサポートされていません。

結合を使用したマテリアライズド ビューを作成する

結合を使用したマテリアライズド ビューは、DDL CREATE MATERIALIZED VIEW ステートメントまたは API を使用して、単一テーブルのマテリアライズド ビューと同様に作成できます。クエリ内のテーブルの順序はパフォーマンスに影響します。最高のパフォーマンスを得るには、最も大きなテーブルを最初に配置します。大きなテーブルが複数ある場合は、クエリ計算の最適化で説明されているように、結合回数が最も多いテーブルを先頭に指定します。詳細については、結合を使用したマテリアライズド ビューのベスト プラクティスをご覧ください。

結合を使用したマテリアライズド ビューのクエリ

結合を使用したマテリアライズド ビューには、単一テーブルのマテリアライズド ビュー、通常のテーブル、または標準ビューのように直接クエリを行うことができます。一致するクエリを発行すると、マテリアライズド ビューに格納されている事前計算された結果を使用するるために、クエリ オプティマイザーでクエリが自動的に書き換えられることがあります。クエリプランには、マテリアライズド ビューを使用するようにクエリが書き換えられたことが示されます。

サポートされているクエリ書き換えパターン

クエリの自動書き換えを可能にするには、クエリで同じ順序でマテリアライズド ビューとまったく同じテーブルセットを使用する必要があります。たとえば、次のマテリアライズド ビューを使用します。

SELECT
 s_country,
 SUM(ss_net_paid) as sum_sales,
 COUNT(*) AS cnt_sales
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
GROUP BY 1;

次のクエリは、マテリアライズド ビューを使用するように自動的に書き換えられるため、費用と時間を節約できます。

SELECT
 SUM(ss_net_paid),
FROM store_sales
INNER JOIN store
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"
GROUP BY 1;

しかし、次のクエリは storestore_sales を異なる順序で参照するため、書き換えられません。

SELECT
 SUM(ss_net_paid),
FROM store
INNER JOIN store_sales
ON ss_store_sk = s_store_sk
WHERE s_country = "Canada"

結合を使用したマテリアライズド ビューの増分更新

結合を使用したマテリアライズド ビューでは、クエリの最初または左端のテーブルの増分クエリがサポートされます。つまり、マテリアライズド ビューが最後に更新された後で、データが最初または左端のテーブルに追加された場合でも、BigQuery はキャッシュされたビューデータを使用できます。ビュー内の最初または左端のテーブルを更新または削除した場合、またはビュー内の他のテーブルを変更した場合、BigQuery はキャッシュされたビューデータを使用できません。次の例は、結合を使用したマテリアライズド ビューの増分更新の仕組みを示しています。

CREATE MATERIALIZED VIEW dataset.mv AS SELECT
 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 1;

CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

INSERT INTO dataset.store_sales …;

-- This query and refresh will use the cached data, and read new data from
-- store_sales and all data from store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

DELETE FROM dataset.store_sales WHERE …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

INSERT INTO dataset.store …;

-- This query and refresh will not use the cached data and will read all data
-- from store_sales and store.
SELECT * FROM dataset.mv;
CALL BQ.REFRESH_MATERIALIZED_VIEW("dataset.mv");

-- This query will use the cached data for dataset.mv.
SELECT * FROM dataset.mv;

パーティション分割テーブルの場合、最初のテーブルまたは左端のテーブルに対する更新や削除によって影響を受けるパーティションのみが無効になります。他の結合テーブルを変更すると、ビュー全体が無効になります。

マテリアライズド ビューの更新

結合を使用したマテリアライズド ビューは、単一テーブルのマテリアライズド ビューと同様に、自動または手動で更新できます。BigQuery は、必要最小限のデータを結合し、マテリアライズド ビューの結合を段階的に維持しようとします。ただし、マテリアライズド ビューが最後に更新されてから特定のベーステーブルが変更された場合、BigQuery は、他のベーステーブルのすべてのデータをスキャンする必要があります。

結合を使用したマテリアライズド ビューのベスト プラクティス

既存のマテリアライズド ビューのベスト プラクティスに加えて、次のベスト プラクティスは、結合を使用したマテリアライズド ビューに特化したものです。

最も頻繁に変更するテーブルを最初に配置する

最大のテーブルがビュークエリで参照される最初の/左端のテーブルであることを確認します。結合を使用したマテリアライズドビューでは、増分クエリをサポートされ、クエリ内で最初のテーブル/左端のテーブルが変更されると更新されますが、他のテーブルを変更すると、ビューのキャッシュは完全に無効になります。スタースキーマまたはスノーフレーク スキーマでは、これはファクト テーブルである可能性があります。

より予測可能性の高いパフォーマンスのために、マテリアライズド ビューを手動で管理する

最初または左端のテーブルのデータを更新または削除する場合、またはクエリ内の他のテーブルを予測可能な時間に変更する場合は、その直後に手動でビューの更新を実施して、ビューのメンテナンス スケジュールのオーナー権限を取得することを検討してください。

キーをグループ化したクラスタ

キーをグループ化してビューをクラスタ化すると、それらのキーに対するフィルタを含むクエリのパフォーマンスが向上します。

クラスタリング キーでの結合を回避する

結合を使用したマテリアライズド ビューは、データが頻繁に集計される場合や元の結合クエリのコストが高い場合に最も効果的です。選択的なクエリの場合、BigQuery はすでに結合を効率的に実行でき、マテリアライズド ビューは必要ありません。たとえば、次のマテリアライズド ビューの定義について考えてみましょう。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY s_marked_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 1;

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'

マテリアライズド ビューは、元のクエリほど効率的でない可能性があります。マテリアライズド ビューの有無にかかわらず、代表的なクエリセットをテストすることをおすすめします。マテリアライズド ビューを使用せずにクエリを実行する手順については、よくある質問をご覧ください。

セキュリティの表示

BigQuery でビューへのアクセスを制御するには、ビューへのアクセスの制御をご覧ください。

次のステップ