本頁面由 Cloud Translation API 翻譯而成。

Bigtable 查詢的 GoogleSQL 範例

本頁的範例會展示常見和進階 Bigtable 查詢的 SQL 查詢模式。您可以在 Bigtable Studio 查詢編輯器中執行 GoogleSQL 查詢。您也可以使用 Bigtable Java 用戶端程式庫執行查詢。

本頁面的範例使用與範例資料類似的 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#%'

擷取資料表中所有資料列的資料列鍵，以及資料欄系列中所有資料欄的最新三個版本。這項查詢需要掃描整個資料表，因此不建議用於低延遲、高輸送量的存取模式。

  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 中，資料欄系列是以對應資料型別表示，其中每個資料欄限定詞和值都會對應為鍵/值組。這個 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，彙整過去一小時內每分鐘的點擊次數，藉此瞭解特定廣告活動的成效。

  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

您可以使用下列查詢擷取資料列鍵，並使用資料表中每個資料列的兩個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 資料欄系列中的鍵/值組合同時包含 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')])

使用下列查詢，您可以擷取天氣感應器的 row key 和 temperature 讀數，條件是過去七次測量期間的溫度超過 70 度。

  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 這類查詢會傳回符合 after 和 before 條件的最新三個值。如果您的用途需要優先使用 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 欄的最新三個版本，以及 state 欄的最新兩個版本。

  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)

如果地址資料欄系列中的鍵值組在任何時間點都包含 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 SQL 查詢模式

進階 Bigtable SQL 查詢模式

後續步驟