Bigtable 用の GoogleSQL の概要

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

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

クエリは Google Cloud コンソールの Bigtable Studio で作成して実行できます。または、Java 用の Bigtable クライアント ライブラリを使用してプログラムで実行することもできます。詳細については、Bigtable クライアント ライブラリで SQL を使用するをご覧ください。

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

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

ユースケース

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

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

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

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

主なコンセプト

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

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

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

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

Map データ型

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

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

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

  SELECT _key, columnFamily['qualifier'] FROM myTable

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

この場合、列ファミリーを表すマップがネストされ、配列として返されます。配列内の各キーは、キーとしてのタイムスタンプ、値としてのセルデータで構成されるマップです。形式は 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 行に 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 * 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/10-13:14:00'))
  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, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['smoke'] = 'on'

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

時系列データのクエリ

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

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

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   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 用の Bigtable クライアント ライブラリでは、executeQuery API を使用した SQL によるデータのクエリがサポートされています。次の例は、クエリを発行してデータにアクセスする方法を示しています。

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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"])

次のステップ

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