継続的マテリアライズドビューのクエリ

Bigtable テーブルの継続的マテリアライズドビューを作成するには、継続的マテリアライズドビューを定義する SQL クエリを実行します。

このドキュメントでは、継続的マテリアライズドビューの SQL クエリの準備に役立つコンセプトとパターンについて説明します。このドキュメントを読む前に、継続的マテリアライズドビューと Bigtable 用の GoogleSQL を理解しておく必要があります。

継続的マテリアライズドビューは、制限付き SQL 構文を使用します。次のパターンは、継続的マテリアライズドビューの SQL クエリを作成する方法を示しています。

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

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

継続的マテリアライズドビューの SQL クエリを非同期セカンダリインデックスとして作成する場合は、ORDER BY 句を使用します。

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

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

クエリの制限事項

継続的マテリアライズドビューの作成に使用する SQL クエリには、次のルールが適用されます。

SELECT ステートメントである必要があります。
GROUP BY 句、または非同期セカンダリインデックスクエリの場合は ORDER BY 句が必要です。両方を使用することはできません。
サポートされている集計関数のみを使用する必要があります。
グループごとに複数の集計を行うことができます。

サポートされている集計

継続的マテリアライズドビューを定義する SQL クエリでは、次の集計関数を使用できます。

COUNT
SUM
MIN
MAX
HLL_COUNT.INIT
HLL_COUNT.MERGE
HLL_COUNT.MERGE_PARTIAL
ANY_VALUE
BIT_AND
BIT_OR
BIT_XOR
AVG

SELECT COUNT(*) を使用する場合は、次の例のように行キーを定義する必要があります。

SELECT
  '*' AS _key,
  COUNT(*) AS count
FROM
  foo
GROUP BY
  _key;

サポートされていない SQL 機能

次の SQL 機能は使用できません。

Bigtable 用の GoogleSQL でサポートされていない機能
ARRAY
ARRAY_AGG
ARRAY_CONCAT_AGG
COUNT_IF
CURRENT_TIME などの非確定的関数
DATE、DATETIME を出力列として使用（TIMESTAMP を使用するか、文字列を保存します）
出力での DESC 並べ替え
DISTINCT オプション（SUM(*DISTINCT* value) など）
LIMIT/OFFSET
SELECT *
OVER 句を使用したウィンドウ集計の作成
STRUCT

GROUP BY 句または ORDER BY 句をネストしたり、マップ列を作成したりすることもできません。その他の制限については、制限事項をご覧ください。

除外された行を回避する

入力行は、次の場合に継続的マテリアライズドビューから除外されます。

行から 1 MiB を超えるデータが選択されている。たとえば、クエリが SELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_table GROUP BY apples の場合、 apple 列と banana 列に 1 MiB を超えるデータが含まれている行は、継続的マテリアライズドビューから除外されます。
行から 1 MiB を超えるデータが出力される。これは、SELECT REPEAT(apple, 1000) などのクエリを使用する場合や、大きな定数を使用する場合に発生する可能性があります。
選択されたデータの 10 倍を超えるデータが出力される。
クエリがデータと一致しない。これには、ゼロ除算、整数のオーバーフロー、すべての行キーで使用されていない行キー形式の想定などが含まれます。

除外された行は、最初に処理されるときにユーザーエラー指標を増やします。継続的マテリアライズドビューのモニタリングに役立つ指標の詳細については、指標をご覧ください。

クエリの詳細

このセクションでは、継続的マテリアライズドビューのクエリと、ビューに対してクエリを実行した際の結果について説明します。ソーステーブルのデータは入力、継続的マテリアライズドビューの結果データは出力です。出力データは、集計されるか、集計されない（定義されたキー内）のいずれかです。

SELECT ステートメント

SELECT ステートメントは、継続的マテリアライズドビューで使用される列と集計を構成します。ステートメントでは、GROUP BY 句を使用して行を集計するか、ORDER BY 句を使用して非同期セカンダリインデックスを作成する必要があります。

SELECT * は対象外ですが、SELECT COUNT(*) はサポートされています。

通常の SELECT ステートメントと同様に、グループ化されたデータセットごとに複数の集計を行うことができます。グループ化されていない列は、集計結果である必要があります。

SQL の標準的な GROUP BY 集計クエリの例を次に示します。

SELECT
  myfamily["node"] AS node,
  myfamily["type"] AS type,
  COUNT(clicks) AS clicks_per_key
FROM
  mytable
GROUP BY
  node,
  type

行キーと集計されていないデータ

継続的マテリアライズドビューの行キーとして _key を指定できます。指定しない場合、GROUP BY 句の列がビューのキーを形成します。

`_key` 列で定義された行キー

継続的マテリアライズドビューを定義するときに、_key 列を任意で指定できます。（これは、Bigtable テーブルで SQL クエリを実行したときに取得する _key 列とは異なります）。_key を指定する場合は、次のルールが適用されます。

_key でグループ化する必要があります。_timestamp を除き、他のものでグループ化することはできません。詳細については、タイムスタンプをご覧ください。
_key 列は BYTES 型である必要があります。

_key を指定すると、SQL ではなく ReadRows でビューを読み取る場合に便利です。これは、行キー形式を制御できるためです。一方、定義された _key を持つビューに対する SQL クエリでは、構造化されたキー列を返すだけでなく、_key を明示的にデコードする必要がある場合があります。

`GROUP BY` 句または `ORDER BY` 句で定義された行キー

_key を指定しない場合、SELECT リストの集計されていない列がビューの行キーになります。キー列には、SQL の規則でサポートされている任意の名前を割り当てることができます。ReadRows リクエストではなく SQL を使用してビューにクエリを実行する場合は、この方法を使用します。

SELECT リストの集計されていない出力列は、GROUP BY 句に含める必要があります。GROUP BY 句に列が記述される順序は、継続的マテリアライズドビューの行キーにデータが保存される順序です。たとえば、GROUP BY a, b, c は暗黙的に ORDER BY a ASC, b ASC, c ASC になります。

GROUP BY 句ではなく ORDER BY 句を使用して非同期セカンダリインデックスを作成する場合、ORDER BY 句の一部である SELECT リストの列がビューの行キーになります。ORDER BY 句に列が記述される順序は、継続的マテリアライズドビューの行キーにデータが保存される順序です。たとえば、ORDER BY a, b, c は、a ASC、b ASC、c ASC の順に行キーでデータを保存します。

SQL フィルタでは、エラーの原因となる可能性のある NULL 値やその他の無効な値を排除する必要があります。NULL キー列を含む無効な行は、結果から除外され、materialized_view/user_errors 指標でカウントされます。ユーザーエラーをデバッグするには、継続的マテリアライズドビューの外部で SQL クエリを実行してみてください。

集計データ

クエリの集計列は、継続的マテリアライズドビューのデータを生成する計算を定義します。

集計列のエイリアスは、継続的マテリアライズドビューの列修飾子として扱われます。

たとえば次のようになります。

SELECT
  fam["baz"] AS baz,
  SUM(fam["foo"]) AS sum_foo,
  SUM(fam["bar"]) AS sum_bar
FROM
  TABLE

GROUP BY
  baz;

クエリ出力には次の特徴があります。

baz ごとの出力は、baz ASC 順に別の行に表示されます。
特定の baz に 1 つ以上の foo がある場合、出力行の sum_foo は NULL 以外の値になります。
特定の baz に 1 つ以上の bar がある場合、出力行の sum_bar は NULL 以外の値になります。
特定の baz にいずれの列の値もない場合、結果から除外されます。

SELECT * でビューにクエリを実行すると、結果は次のようになります。

baz	sum_foo	sum_bar
baz1	sum_foo1	sum_bar1
baz2	sum_foo2	sum_bar2

タイムスタンプ

継続的マテリアライズドビューの出力セルのデフォルトのタイムスタンプは 0（1970-01-01 00:00:00Z）です。これは、SQL でクエリを実行したときではなく、ReadRows でビューを読み取るときに表示されます。

出力で別のタイムスタンプを使用するには、TIMESTAMP 型の列をクエリの SELECT リストに追加して、_timestamp という名前を付けます。 ReadRows を使用して継続的マテリアライズドビューにクエリを実行すると、_timestamp が行内の他のセルのタイムスタンプになります。

タイムスタンプは NULL にすることはできません。ゼロ以上で、1,000 の倍数（ミリ秒単位）にする必要があります。Bigtable は、Unix エポック（1970-01-01T00:00:00Z）より前のセルタイムスタンプをサポートしていません。

次の例では、集計データを日単位でリサンプリングします。クエリでは UNPACK 関数を使用します。

SELECT
  _key,
  TIMESTAMP_TRUNC(_timestamp, DAY) AS _timestamp,
  SUM(sum_family["sum_column"]) AS sum_column,
  SUM(sum_family["foo"]) AS second_sum_column
FROM
  UNPACK(
  SELECT
    *
  FROM
    my_table(with_history => TRUE))
GROUP BY
  1,
  2

特定の SUM に特定の日付の空でない入力がある場合、出力行には、切り捨てられた日付と一致するタイムスタンプを持つ集計値が含まれます。

SELECT * でビューにクエリを実行すると、結果は次のようになります。

_key	_timestamp	sum_column	second_sum_column
1	2024-05-01 00:00:00Z	23	99
2	2024-05-02 00:00:00Z	45	201
3	2024-05-03 00:00:00Z	NULL	56
4	2024-05-04 00:00:00Z	8	NULL

エンコード

SQL で継続的マテリアライズドビューにクエリを実行する場合、SQL は結果を型付き列として公開するため、集計値のエンコード方法を意識する必要はありません。

ReadRows を使用してビューから読み取る場合は、読み取りリクエストで集計データをデコードする必要があります。ReadRows リクエストの詳細については、読み取りをご覧ください。

継続的マテリアライズドビューの集計値は、ビュー定義の列の出力タイプに基づいて、次の表に示すエンコードを使用して保存されます。

型	エンコード
BOOL	1 バイト値、1 = true、0 = false
BYTES	エンコードなし
INT64（または INT、SMALLINT、INTEGER、BIGINT、TINYINT、BYTEINT）	64 ビットビッグエンディアン
FLOAT64	64 ビット IEEE 754（NaN と +/-inf を除く）
STRING	UTF-8
TIME/TIMESTAMP	Unix エポックからのマイクロ秒数を表す 64 ビット整数（GoogleSQL と一致）

詳細については、Data API リファレンスのエンコードをご覧ください。