Bigtable 用の GoogleSQL の概要

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

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

クエリは Bigtable Studio で作成して実行できます。 Google Cloud コンソールまたは、Bigtable クライアントライブラリ（ Java、 Python、Go）を使用してプログラムで実行することもできます。詳細については、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、CTEs のクエリ構文

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

ビュー

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

継続的なマテリアライズドビュー - 集計データを含む、継続的に実行される 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 つの行に 1 つの列があり、次の行に 100 個の列がある場合があります。一方、リレーショナルデータベーステーブルでは、すべての行にすべての列が含まれ、その列のデータがない行の列には通常 NULL 値が保存されます。

ただし、GoogleSQL で Bigtable テーブルにクエリを実行すると、未使用の列は空のマップで表され、NULL 値として返されます。これらの NULL 値は、クエリ述語として使用できます。たとえば、a 述語 like 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`	Timestamp。入力後のタイムスタンプを持つ値（この値を含まない）。 `with_history => TRUE` が必須です。
`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-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 の更新の都道府県を組み合わせたものです。

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, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['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(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   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}]

時系列データを `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%'
  ));

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

temp_versioned	_timestamp
55	1672898400
55	1672894800
56	1672891200

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

Go

この機能を使用するには、cloud.google.com/go/bigtable バージョン 1.36.0 以降を使用する必要があります。使用方法の詳細については、PrepareStatement、 Bind、 Execute、および ResultRow のドキュメントをご覧ください。

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

Java

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

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

Python asyncio

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

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

ユースケース

ビュー

主なコンセプト

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

Map データ型

スパーステーブル

バイト

時間フィルタ

基礎的なクエリ

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

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

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

時系列データのクエリ

時系列データを `UNPACK` する

JSON をクエリする

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

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

Go

Java

Python asyncio

`SELECT *` の使用

次のステップ

Bigtable 用の GoogleSQL の概要

ユースケース

ビュー

主なコンセプト

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

Map データ型

スパース テーブル

バイト

時間フィルタ

基礎的なクエリ

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

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

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

時系列データのクエリ

時系列データを UNPACK する

JSON をクエリする

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

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

Go

Java

Python asyncio

SELECT * の使用

次のステップ

スパーステーブル

時系列データを `UNPACK` する

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

`SELECT *` の使用