Bigtable 用の GoogleSQL の概要

GoogleSQL ステートメントを使用して Bigtable データに対してクエリを実行できます。GoogleSQL は、BigQuery や Spanner などの他の Google Cloudサービスにも実装されている ANSI 準拠の構造化クエリ言語（SQL）です。

このドキュメントでは、Bigtable 用の GoogleSQL の概要について説明します。Bigtable で使用できる SQL クエリの例を示し、それらが Bigtable テーブル スキーマとどのように関連しているかについて説明します。このドキュメントをお読みになる前に、Bigtable ストレージ モデルとスキーマ設計のコンセプトを理解しておく必要があります。

Python

SQL クエリは、NoSQL データ リクエストと同じ方法でクラスタノードによって処理されます。したがって、Bigtable データに対して実行する SQL クエリを作成する場合も、全テーブル スキャンや複雑なフィルタを避けるなど、同じベスト プラクティスが適用されます。詳細については、読み取りとパフォーマンスをご覧ください。

GoogleSQL for Bigtable で Data Boost を使用することはできません。

ユースケース

Bigtable 用の GoogleSQL は、低レイテンシのアプリケーション開発に最適です。また、Google Cloud コンソールで SQL クエリを実行すると、テーブルのスキーマをすばやく視覚的に把握する、特定のデータが書き込まれたことを確認する、またはデータに関して発生する可能性がある問題をデバッグすることができます。

Bigtable 用の GoogleSQL の現在のリリースでは、次のような一般的な SQL 構造（ただし、これらに限定されません）はサポートされていません。

• SELECT 以外のデータ操作言語（DML）ステートメント（INSERT、UPDATE、DELETE など）
• CREATE、ALTER、DROP などのデータ定義言語（DDL）ステートメント
• データアクセス制御ステートメント
• サブクエリ JOIN、UNION、UNNEST、CTEs のクエリ構文

サポートされている関数、演算子、データ型、クエリ構文などの詳細については、Bigtable 用の GoogleSQL リファレンス ドキュメントをご覧ください。

ビュー

GoogleSQL for Bigtable を使用して、次のリソースを作成できます。

• マテリアライズド ビュー - 継続的に実行される SQL クエリの事前計算された結果（集計データを含む）。増分更新でソーステーブルと同期します。
• 論理ビュー - テーブルのようにクエリできる、保存された名前付きクエリ。

これらのタイプのビューと承認済みビューの比較については、テーブルとビューをご覧ください。

主なコンセプト

このセクションでは、GoogleSQL を使用して Bigtable データをクエリする際に知っておくべき重要なコンセプトについて説明します。

SQL レスポンスの列ファミリー

Bigtable では、テーブルに 1 つ以上の列ファミリーが含まれています。列ファミリーは、列をグループ化するために使用されます。GoogleSQL で Bigtable テーブルをクエリする場合、テーブルのスキーマの構成要素は次のとおりです。

• クエリ対象のテーブルの行キーに対応する _key という名前の特別な列
• 対象の行の列ファミリーのデータを格納するテーブル内の Bigtable 列ファミリーごとに 1 つの列

Map データ型

Bigtable 用の GoogleSQL には、列ファミリーに対応するように特別に設計されたデータ型 MAP<key, value> が含まれています。

デフォルトでは、マップ列の各行には Key-Value ペアが含まれます。ここで、キーはクエリ対象のテーブルの Bigtable 列修飾子であり、値はその列の最新の値です。

次の例は、SQL クエリが columnFamily という名前のマップから行キー値と修飾子の最新の値を含むテーブルを返す例です。

  SELECT _key, columnFamily['qualifier'] FROM myTable

Bigtable スキーマで複数のセル（またはデータのバージョン）を列に保存する場合は、SQL ステートメントに with_history などの時間フィルタを追加できます。

この場合、列ファミリーを表すマップがネストされ、配列として返されます。配列内の各値は、キーとしてのタイムスタンプ、値としてのセルデータで構成されるマップです。形式は MAP<key, ARRAY<STRUCT<timestamp, value>>> です。

次の例では、単一行の「info」列ファミリー内のすべてのセルを返します。

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

返されるマップは次のようになります。クエリ対象のテーブルでは、info は列ファミリー、user_123 は行キー、city と state は列修飾子です。配列内の各タイムスタンプと値のペア（STRUCT）は、対象行のそれらの列内に存在するセルを表し、タイムスタンプの降順で並べ替えられます。

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

スパース テーブル

Bigtable の重要な機能は、柔軟なデータモデルです。Bigtable テーブルでは、行で使用されていない列にデータは保存されません。ある行に 1 つの列があり、次の行に 100 個の列がある場合もあります。一方、リレーショナル データベース テーブルでは、すべての行にすべての列が含まれ、通常、NULL 値は、その列のデータがない行の列に格納されます。

ただし、GoogleSQL で Bigtable テーブルをクエリすると、未使用の列は空のマップで表され、NULL 値として返されます。これらの NULL 値は、クエリ述語として使用できます。たとえば、WHERE family['column1'] IS NOT NULL などの述語を使用して、行で column1 が使用されている場合にのみ行を返すことができます。

バイト

文字列を指定すると、GoogleSQL はデフォルトで STRING 値から BYTES 値に暗黙的にキャストします。つまり、たとえば、バイト シーケンス b'qualifier' ではなく、文字列 'qualifier' を指定できます。

Bigtable はデフォルトですべてのデータをバイトとして扱うため、ほとんどの Bigtable 列には型情報が含まれていません。ただし、GoogleSQL では、CAST 関数を使用して読み取り時にスキーマを定義できます。キャストの詳細については、変換関数をご覧ください。

時間フィルタ

次の表に、テーブルの時間要素にアクセスする際に使用できる引数を示します。引数は、フィルタされる順序で示されています。たとえば、with_history は latest_n より先に適用されます。有効なタイムスタンプを指定する必要があります。

その他の例については、高度なクエリ パターンをご覧ください。

基礎的なクエリ

このセクションでは、基本的な Bigtable SQL クエリの例とそれらの仕組みについて説明します。その他のサンプルクエリについては、Bigtable 用の GoogleSQL のクエリパターンの例をご覧ください。

最新バージョンを取得する

Bigtable では各列に複数のバージョンのデータを格納できますが、Bigtable 用の GoogleSQL はデフォルトで、各行のデータの最新バージョン（最新のセル）を返します。

user1 がニューヨーク州内で 2 回、ブルックリン市内で 1 回転居したことを示している次のサンプル データセットについて考えてみましょう。この例では、address が列ファミリーであり、列修飾子は street、city、state です。列内のセルは空の行で区切られます。

user1 の各列の最新バージョンを取得するには、次のような SELECT ステートメントを使用します。

   SELECT street, city FROM myTable WHERE _key = 'user1'

レスポンスには現在の住所が含まれます。これは、JSON として出力された最新の番地、市町村、都道府県の値（別の時刻に書き込まれた値）を組み合わせたものです。タイムスタンプはレスポンスに含まれません。

すべてのバージョンを取得する

データの古いバージョン（セル）を取得するには、with_history フラグを使用します。次の例に示すように、列と式にエイリアスを設定することもできます。

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

行の現在の状態につながるイベントをより深く理解するには、完全な履歴を取得して、各値のタイムスタンプを取得します。たとえば、user1 がいつ現在の住所に引っ越したのか、そしてどこから引っ越したのかを把握するには、次のクエリを実行します。

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

SQL クエリで with_history フラグを使用すると、レスポンスは MAP<key, ARRAY<STRUCT<timestamp, value>>> として返されます。配列の各アイテムは、指定された行、列ファミリー、列のタイムスタンプ付きの値です。タイムスタンプは日付の新しい順に並べられるため、最新のデータが常に返される最初のアイテムになります。

クエリ レスポンスは次のとおりです。

次のクエリに示すように、配列関数を使用して各行のバージョン数を取得することもできます。

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

指定した時刻のデータを取得する

as_of フィルタを使用すると、特定の時点での行の状態を取得できます。たとえば、2022 年 1 月 10 日午後 1 時 14 分時点の user の住所を知りたい場合は、次のクエリを実行します。

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
  WHERE _key = 'user1'

結果には、2022 年 1 月 10 日午後 1 時 14 分時点で把握されている最後の住所が表示されます。これは、2021 年 12 月 20 日 09:44:31.010 の更新の番地と、2005 年 3 月 1 日 11:12:15.112 の更新の都道府県を組み合わせたものです。

Unix タイムスタンプを使用しても同じ結果が得られます。

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

煙警報と一酸化炭素警報のオン / オフの状態を示す次のデータセットについて考えてみましょう。列ファミリーは alarmType であり、列修飾子は smoke と carbonMonoxide です。各列のセルは空の行で区切られます。

次のクエリを使用すると、2023 年 4 月 1 日午前 9 時に煙警報がオンになっていた building1 のセクションと、その時点の一酸化炭素警報のステータスを確認できます。

  SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['smoke'] = 'on'

結果は次のようになります。

時系列データのクエリ

Bigtable の一般的なユースケースは、時系列データの保存です。気象センサーの気温と湿度の測定値を示す次のサンプル データセットについて考えてみましょう。列ファミリー ID は metrics であり、列修飾子は temperature と humidity です。列内のセルは空の行で区切られ、各セルはタイムスタンプ付きのセンサー測定値を表します。

時間フィルタ after、before、または after_or_equal を使用して、特定の範囲のタイムスタンプ値を取得できます。次の例では、after を使用しています。

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   WHERE _key LIKE 'sensorA%'

クエリは次の形式でデータを返します。

UNPACK 時系列データ

時系列データを分析する場合は、表形式でデータを扱うことが望ましいことがよくあります。Bigtable の UNPACK 関数が役立ちます。

UNPACK は、単一のスカラー値ではなく出力テーブル全体を返す Bigtable テーブル値関数（TVF）です。テーブル サブクエリのように FROM 句に表示されます。UNPACK TVF は、タイムスタンプ付きの各値を複数の行（タイムスタンプごとに 1 つ）に展開し、タイムスタンプを _timestamp 列に移動します。

UNPACK の入力は、with_history => true のサブクエリです。

出力は、各行に _timestamp 列を含む拡張テーブルです。

入力列ファミリー MAP<key, ARRAY<STRUCT<timestamp, value>>> は MAP<key, value> に展開され、列修飾子 ARRAY<STRUCT<timestamp, value>>> は value に展開されます。他の入力列の型は変更されません。列をアンパックして選択するには、サブクエリで列を選択する必要があります。タイムスタンプをアンパックするために、新しい _timestamp 列を選択する必要はありません。

時系列データのクエリの時系列の例を拡張し、そのセクションのクエリを入力として使用すると、UNPACK クエリは次のようにフォーマットされます。

  SELECT temp_versioned, _timestamp
  FROM
  UNPACK((
    SELECT metrics['temperature'] AS temperature_versioned
    FROM
    sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
          before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
    WHERE _key LIKE 'sensorA%'
  ));

クエリは次の形式でデータを返します。

JSON をクエリする

JSON 関数を使用すると、運用ワークロード用に Bigtable 値として保存された JSON を操作できます。

たとえば、次のクエリを使用すると、session 列ファミリーの最新のセルから JSON 要素 abc の値を行キーとともに取得できます。

  SELECT _key, JSON_VALUE(session['payload'],'$.abc') AS abc FROM analytics

特殊文字と予約済みの単語をエスケープする

Bigtable では、テーブルと列の命名に高い柔軟性が提供されます。その結果、SQL クエリでは、特殊文字や予約済み単語が原因でテーブル名をエスケープすることが必要な場合があります。

たとえば、次のクエリは、テーブル名にピリオドが含まれているため、有効な SQL ではありません。

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

ただし、項目をバッククォート（`）文字で囲むことで、この問題を解決できます。

  SELECT * FROM `my.table` WHERE _key = 'r1'

SQL 予約済みキーワードが識別子として使用されている場合も、同様にエスケープできます。

  SELECT * FROM `select` WHERE _key = 'r1'

Bigtable クライアント ライブラリで SQL を使用する

Java、Python、Go 用の Bigtable クライアント ライブラリでは、executeQuery API を使用した SQL によるデータのクエリがサポートされています。次の例は、クエリを発行してデータにアクセスする方法を示しています。

  import (
    "cloud.google.com/go/bigtable"
  )

  func query(client *bigtable.Client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    ps, err := client.PrepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      map[string]SQLType{
        "keyParam": BytesSQLType{},
      }
    )
    if err != nil {
      log.Fatalf("Failed to create PreparedStatement: %v", err)
    }

    // For each request, create a BoundStatement with your query parameters set.
    bs, err := ps.Bind(map[string]any{
      "keyParam": []byte("mykey")
    })
    if err != nil {
      log.Fatalf("Failed to bind parameters: %v", err)
    }

    err = bs.Execute(ctx, func(rr ResultRow) bool {
      var byteValue []byte
      err := rr.GetByName("bytesCol", &byteValue)
      if err != nil {
        log.Fatalf("Failed to access bytesCol: %v", err)
      }
      var stringValue string
      err = rr.GetByName("stringCol", &stringValue)
      if err != nil {
        log.Fatalf("Failed to access stringCol: %v", err)
      }
      // Note that column family maps have byte valued keys. Go maps don't support
      // byte[] keys, so the map will have Base64 encoded string keys.
      var cf3 map[string][]byte
      err = rr.GetByName("cf3", &cf3)
      if err != nil {
        log.Fatalf("Failed to access cf3: %v", err)
      }
      // Do something with the data
      // ...
      return true
    })
  }

  static void query(BigtableDataClient client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    PreparedStatement preparedStatement = client.prepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      // For queries with parameters, set the parameter names and types here.
      Map.of("keyParam", SqlType.bytes())
    );

    // For each request, create a BoundStatement with your query parameters set.
    BoundStatement boundStatement = preparedStatement.bind()
      .setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
      .build();

    try (ResultSet resultSet = client.executeQuery(boundStatement)) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data.
      }
    }
  }

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])

SELECT * の使用状況

クエリ対象のテーブルで列ファミリーが追加または削除されると、SELECT * クエリで一時的なエラーが発生することがあります。そのため、本番環境のワークロードでは、SELECT * を使用するのではなく、クエリですべての列ファミリー ID を指定することをおすすめします。たとえば、SELECT * ではなく SELECT cf1, cf2, cf3 を使用します。

次のステップ

• Bigtable 用の GoogleSQL のリファレンス ドキュメントを確認する。
• Bigtable Studio の詳細を確認する。