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 クエリを作成するときにも、全テーブルスキャンや複雑なフィルタの回避など、同じベストプラクティスが適用されます。詳細については、読み取りとパフォーマンスをご覧ください。

ユースケース

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 列修飾子であり、値はその列の最新の値です。

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

  SELECT _key, columnFamily['qualifier'] FROM myTable

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

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

次の例では、1 つの行に対する「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 より前に適用されます。有効なタイムスタンプを指定する必要があります。

引数	説明
`as_of`	Timestamp。指定されたタイムスタンプ以下のタイムスタンプを持つ最新の値を返します。
`with_history`	Boolean。最新の値をスカラーとして返すか、タイムスタンプ付きの値を `STRUCT` として返すかを制御します。
`after_or_equal`	Timestamp。入力後のタイムスタンプを持つ値（この値を含む）。`with_history => TRUE` が必須です
`before`	Timestamp。入力より前のタイムスタンプがある値（この値は含まれない）。`with_history => TRUE` が必須です
`latest_n`	整数。列修飾子（マップキー）ごとに返されるタイムスタンプ付き値の数。1 以上にする必要があります。`with_history => TRUE` が必須です。

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

基礎的なクエリ

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

		address
_key	street	city	state
user1	2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: '123 Abc Street'	2021/12/20-09:44:31.010: 'Brooklyn' 2005/03/01-11:12:15.112: 'Queens'	2005/03/01-11:12:15.112: 'NY'

_key	address
user1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

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

データの古いバージョン（セル）を取得するには、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>>> として返されます。配列の各項目は、指定された行、列ファミリー、列のタイムスタンプ付き値です。タイムスタンプは日付の新しい順に並べられるため、最新のデータが常に返される最初のアイテムになります。

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

moved_to			moved_from	moved_on
113 Xyz Street			76 Xyz Street	2023/01/10

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

  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 の更新の都道府県を組み合わせたものです。

address
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

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

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

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

	alarmType
_key	smoke	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: 'off' 2023/04/01-08:41:40.000: 'on' 2020/07/03-06:25:31.000: 'off' 2020/07/03-06:02:04.000: 'on'	2023/04/01-09:22:08.000: 'off' 2023/04/01-08:53:12.000: 'on'


building1#section2	2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on'

次のクエリを使用して、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'

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

location	CO_sensor
building1#section1	'on'

時系列データのクエリ

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

	metrics
_key	温度	湿度
sensorA#20230105	2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55	2023/01/05-02:00:00.000: 0.89 2023/01/05-01:00:00.000: 0.9 2023/01/05-00:00:00.000: 0.91


sensorA#20230104	2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57	2023/01/04-23:00:00.000: 0.9 2023/01/04-22:00:00.000: 0.91

時間フィルタ 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%'

クエリでは、次の形式でデータが返されます。

temp_versioned
{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}


{timestamp: '2023/01/04-23:00:00.000', value:56}

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 によるデータのクエリがサポートされています。次の例は、クエリを発行してデータにアクセスする方法を示しています。

Java

この機能を使用するには、java-bigtable バージョン 2.41.0 以降を使用する必要があります。使用方法の詳細については、Javadoc の executeQuery、Statement、ResultSet をご覧ください。

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

Python asyncio

この機能を使用するには、python-bigtable バージョン 2.26.0 以降を使用する必要があります。

  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 の概要

ユースケース

主なコンセプト

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

Map データ型

スパーステーブル

バイト

時間フィルタ

基礎的なクエリ

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

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

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

時系列データのクエリ

JSON をクエリする

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

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

Java

Python asyncio

次のステップ

Bigtable 用の GoogleSQL の概要

ユースケース

主なコンセプト

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

Map データ型

スパース テーブル

バイト

時間フィルタ

基礎的なクエリ

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

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

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

時系列データのクエリ

JSON をクエリする

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

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

Java

Python asyncio

次のステップ

スパーステーブル

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