适用于 Bigtable 的 GoogleSQL 查询示例

本页中的示例展示了常规和高级 Bigtable 查询的 SQL 查询模式。您可以在 Bigtable Studio 查询编辑器中运行 GoogleSQL 查询。您还可以使用 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#%'

检索表中所有行的列族中所有列的行键和三个最新版本。此查询需要全表扫描，因此不适用于低延迟、高吞吐量的访问模式。

  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'

高级 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')])

使用以下查询，您可以检索过去 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 等查询会返回满足“之后”和“之前”条件的最新三个值。如果您的用例要求 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 列的三个最新版本，以及 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

后续步骤