Bigtable 用 GoogleSQL のクエリ例

このページの例では、一般的な Bigtable クエリと高度な Bigtable クエリの SQL クエリパターンを示します。Google SQL クエリは、Bigtable Studio クエリエディタで実行できます。Java 用 Bigtable クライアント ライブラリを使用してクエリを実行することもできます。

このページを読む前に、Bigtable 用の GoogleSQL の概要をご覧ください。

注: バージョンとは、特定の列と行のタイムスタンプ値のことです。各バージョンはセルに保存されます。詳細については、Bigtable ストレージ モデルをご覧ください。

このページの例では、サンプル用のデータと同様の ID と値を使用します。

一般的な Bigtable SQL クエリ パターン

Bigtable データの一般的なクエリの例を次に示します。Bigtable Data API を呼び出す同様のクエリの例については、読み取りの例とフィルタを使用するをご覧ください。構造化された行キーに対するクエリの例については、構造化された行キーのクエリをご覧ください。

指定された行キーのすべての列の最新バージョンを取得します。

  SELECT * FROM myTable WHERE _key = 'r1'

指定された行キーのすべての列のすべてのバージョンを取得します。

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

特定の行キーについて、特定の列ファミリーから特定の列の最新バージョンを取得します。

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

指定された行キー範囲の行キーと複数の列の最新バージョンを取得します。

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

複数の行キー範囲のすべての列のすべてのバージョンを最大 10 行まで取得します。

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

複数の行キーのすべての列のすべてのバージョンを取得します。

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

別の方法を使用して、複数の行キーのすべての列のすべてのバージョンを取得します。

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

行キー接頭辞の列ファミリー内のすべての列の最新バージョンを取得します。

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

テーブル内のすべての行について、列ファミリー内のすべての列の行キーと最新の 3 つのバージョンを取得します。このクエリではテーブル全体のスキャンが必要になるため、低レイテンシで高スループットのアクセス パターンにはおすすめできません。

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

指定された正規表現に一致する行キーを持つすべての列の最新バージョンを取得します。このクエリではテーブル全体のスキャンが必要になるため、WHERE 句で行キーの接頭辞または行キー範囲の述語も指定しない限り、低レイテンシで高スループットのアクセス パターンにはおすすめしません。

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

一致する行キーのプレフィックスと 123 より大きいカウンタ値を持つすべての列の最新バージョンを取得します。Bigtable 集計は数値であるため、この比較でキャストする必要はありません。

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

リファラーが特定の値と一致する場合、行キー接頭辞のすべての列の最新バージョンを取得します。

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

指定された列の値に基づいて、指定された行を分類します。このクエリは、Bigtable Data API で条件付き合成フィルタを使用する場合と似ています。

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

指定された行キーの範囲について、特定の列ファミリーの行キーと列修飾子を取得します。SQL では、列ファミリーはマップデータ型で表されます。各列修飾子と値は Key-Value ペアとしてマッピングされます。この SQL クエリは、Bigtable Data API で値削除フィルタを使用する場合と似ています。

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

UNPACK 関数を使用すると、Bigtable データを表形式の時系列形式に変換できます。これは、時系列分析を行う場合に便利です。engagement 列ファミリーに clicks 列がある例について考えてみましょう。次のクエリでは、UNPACK を使用して、過去 1 時間のクリック数を 1 分ごとに集計し、特定のキャンペーンのパフォーマンスを確認します。

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

高度な Bigtable SQL クエリ パターン

次のサンプルは、より高度なパターンを示しています。

次のクエリでは、session 列ファミリーの JSON 属性 abc の行キーと最新の値を取得できます。詳細については、JSON 関数をご覧ください。

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

次のクエリでは、行キーを取得し、テーブル内の各行について 2 つの Bigtable 集計セル（数値）の最新の値を使用して平均セッション長を計算できます。

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

次のクエリでは、session 列ファミリーに列修飾子として referrer、origin、または server が含まれている場合、特定の行キー接頭辞のすべての列の最新バージョンを取得できます。このクエリは、session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL のような個々の比較の連続として記述することもできます。ただし、多数の比較を含むクエリの場合は、次のアプローチをおすすめします。

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

次のクエリでは、session 列ファミリーに列修飾子として referrer、origin、server が含まれている場合に、特定の行キー接頭辞のすべての列の最新バージョンを取得できます。このクエリは、session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL のような個々の比較の連続として記述することもできます。

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

次のクエリでは、session 列ファミリーに値として com.google.search、com.google.maps、または com.google.shopping が含まれている場合、特定の行キー接頭辞のすべての列の最新バージョンを取得できます。

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

次のクエリでは、cell_plan 列ファミリーの Key-Value ペアに data_plan:unlimited と roaming:North America の両方が含まれている場合に、すべての列の最新バージョンを取得できます。

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

次のクエリでは、過去 7 回の測定で温度が 70 度を超えたケースの、気象センサーの row key と temperature の測定値を取得できます。

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

時間のフィルタリング順序では latest_n が最後に来るため、after => X, before => y, latest_n => 3 のようなクエリは、後と前の条件を満たす最新の 3 つの値を返します。ユースケースで latest_n を優先する必要がある場合は、例で示すように、latest_n を唯一の時間フィルタとして指定してから、SELECT ステートメントでクエリ演算子を使用して残りの時間フィルタを適用します。詳細については、時間フィルタをご覧ください。

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

前の例と同様に、クエリ内の各列ファミリーに異なる時間フィルタを適用できます。たとえば、次のクエリは、street 列の最新の 3 つのバージョンと、state 列の最新でない 2 つのバージョンを返します。

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

次のクエリを使用すると、アドレス列ファミリーの Key-Value ペアに city:Savannah または city:Nashville が含まれている場合、すべての列のすべてのバージョンを取得できます。

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

この例ではキャストは不要なので、次のように短く記述することもできます。

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

次のステップ

• Bigtable 用の GoogleSQL のリファレンス ドキュメントを確認する。
• Bigtable Spark コネクタを使用する。
• 行キー スキーマを管理する。