Bigtable 用 GoogleSQL のクエリの例

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

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

このページの例では、サンプル用のデータと同様の 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'

高度な 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-04 23:51:00+00'))
      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

次のステップ