GoogleSQL for Bigtable 概览

您可以使用 GoogleSQL 语句查询 Bigtable 数据。GoogleSQL 是一种符合 ANSI 标准的 结构化查询语言 (SQL)，也适用于其他 Google Cloud 服务，例如 BigQuery 和 Spanner。

本文档简要介绍了 GoogleSQL for Bigtable。它提供了可与 Bigtable 搭配使用的 SQL 查询示例，并介绍了这些查询与 Bigtable 表架构的关系。在阅读本文档之前，您应该 熟悉 Bigtable 存储 模型 和 架构设计 概念。

您可以在控制台 Google Cloud 的 Bigtable Studio 中创建和运行查询，也可以使用 Bigtable 客户端 库（适用于 Java、 Python 或 Go）以编程方式运行查询。 如需了解详情，请参阅将 SQL 与 Bigtable 客户端库搭配使用。

SQL 查询的处理方式与 NoSQL 数据请求的处理方式相同，都是由集群节点处理。 因此，在创建针对 Bigtable 数据运行的 SQL 查询时，您需要遵循相同的最佳实践，例如避免执行全表扫描或使用复杂的过滤条件。如需了解详情，请参阅读取和 性能。

使用场景

GoogleSQL for Bigtable 非常适合开发低延迟应用。此外，在 Google Cloud 控制台中运行 SQL 查询有助于快速直观地了解表的架构、验证是否写入了特定数据，或调试可能存在的数据问题。

当前版本的 GoogleSQL for Bigtable 不支持某些常见的 SQL 结构，包括但不限于以下结构：

• 除了 SELECT 之外的数据操纵语言 (DML) 语句，例如 INSERT、UPDATE 或 DELETE
• 数据定义语言 (DDL) 语句，例如 CREATE、ALTER 或 DROP
• 数据访问权限控制语句
• 子查询、JOIN、UNION 和 CTEs 的查询语法

如需了解详情，包括支持的函数、运算符、数据类型和 查询语法，请参阅GoogleSQL for Bigtable 参考 文档。

视图

您可以使用 GoogleSQL for Bigtable 创建以下资源：

• 持续物化视图 - 持续运行的 SQL 查询的预计算结果，包括汇总数据，这些数据会通过增量更新与源表同步。
• 逻辑视图 \- 已保存的命名 查询，可以像查询表一样查询。

如需比较这些类型的视图以及授权视图，请参阅表和 视图。

主要概念

本部分讨论了在使用 GoogleSQL 查询 Bigtable 数据时需要了解的主要概念。

SQL 响应中的列族

在 Bigtable 中，一个表包含一个或多个列族，用于对列进行分组。当您使用 GoogleSQL 查询 Bigtable 表时，该表的架构包含以下内容：

• 一个名为 _key 的特殊列，对应于所查询表中的行键
• 表中每个 Bigtable 列族都有一个列，其中包含该行中列族的数据

地图数据类型

GoogleSQL for Bigtable 包含数据类型 MAP<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> timestamp, "Brooklyn" value}, {<t0> timestamp, "New York" value}], "state": [{<t0> timestamp, "NY" value}]}        |
 +----------+------------------------------------------------------------------------------------------------------------------------------------*/

稀疏表

Bigtable 的一个关键特性是其灵活的数据模型。在 Bigtable 表中，如果某行中未使用某个列，则不会为该列存储任何数据。一行可能有一个列，而下一行可能有 100 个列。相比之下，在关系型数据库表中，所有行都包含所有列，并且通常会在没有数据的行的列中存储 NULL 值。

不过，当您使用 GoogleSQL 查询 Bigtable 表时，未使用的列会以空地图表示，并作为 NULL 值返回。这些 NULL 值可用作查询谓词。例如，a 谓词（如 WHERE family['column1'] IS NOT NULL）可用于仅当某行中使用了 column1 时才返回该行。

字节

当您提供字符串时，GoogleSQL 默认会隐式地将 STRING 值转换为 BYTES 值。这意味着，例如，您可以 提供字符串 'qualifier'，而不是字节序列 b'qualifier'。

由于 Bigtable 默认将所有数据视为字节，因此大多数 Bigtable 列都不包含类型信息。不过，借助 GoogleSQL，您可以使用 CAST 函数在读取时定义架构。如需详细了解转换，请参阅转换 函数。

时间过滤条件

下表列出了在访问表的时间元素时可以使用的参数。参数按过滤顺序列出。例如，with_history 会在 latest_n 之前应用。您必须提供有效的时间戳。

参数	说明
as_of	时间戳 。返回时间戳小于或等于所提供时间戳的最新值。
with_history	布尔值 。控制是将最新值作为标量返回，还是将带时间戳的值作为 STRUCT 返回。
after	时间戳 。时间戳晚于输入值（不含输入值）的值。 需要 with_history => TRUE。
after_or_equal	时间戳 。时间戳晚于输入值（含输入值）的值。需要 with_history => TRUE。
before	时间戳 。时间戳早于输入值（不含输入值）的值。需要 with_history => TRUE。
latest_n	整数 。要为每个列 限定符（地图键）返回的带时间戳的值的数量。必须大于或等于 1。需要 with_history => TRUE。

如需查看更多示例，请参阅高级查询 模式。

基础查询

本部分介绍了基本的 Bigtable SQL 查询及其工作原理，并展示了相关示例。如需查看其他示例查询，请参阅 GoogleSQL for Bigtable 查询句式 示例。

检索最新版本

虽然 Bigtable 允许您在每个列中存储多个版本的数据，但 GoogleSQL for Bigtable 默认会为每一行返回数据的最新版本（即最新的单元格）。

请考虑以下示例数据集，该数据集显示 user1 在纽约州搬迁了两次，在布鲁克林市搬迁了一次。在此示例中，address 是列族，列限定符为 street、city 和 state。列中的单元格用空行分隔。

		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'

如需检索 user1 的每个列的最新 版本，您可以使用类似如下的 SELECT 语句。

   SELECT address['street'], address['city'] FROM myTable WHERE _key = 'user1'

响应包含当前地址，该地址是最新街道、城市和州值（在不同时间写入）的组合，以 JSON 格式输出。响应中不包含时间戳。

_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 过滤条件可让您检索行在特定时间点的状态。例如，如果您想知道 user 在 2022 年 1 月 10 日下午 1:14 的地址，可以运行以下查询。

  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/03/01-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'

您可以使用以下查询查找 building1 中在 2023 年 4 月 1 日上午 9 点开启了烟雾警报器的部分，以及当时一氧化碳警报的状态。

  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	temperature	humidity
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 将每个带时间戳的值展开为多行（每个时间戳一行），并将时间戳移到 _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'

将 SQL 与 Bigtable 客户端库搭配使用

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 * 查询可能会遇到暂时性错误。因此，对于生产工作负载，我们建议您在查询中指定所有列族 ID，而不是使用 SELECT *。例如，使用 SELECT cf1, cf2, cf3 而不是 SELECT *。

此外，定义 逻辑 视图 和 持续具体化 视图 的查询无法使用 SELECT *。

后续步骤

• 浏览 GoogleSQL for Bigtable 参考文档。
• 详细了解 Bigtable Studio。
• 了解如何使用 GoogleSQL for Bigtable 开发应用。