GoogleSQL for Bigtable 概览

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

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

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

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

您无法将 Data Boost 与 GoogleSQL for Bigtable 搭配使用。

使用场景

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

当前版本的 Bigtable GoogleSQL 不支持某些常见的 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 列族的单个列，其中包含相应列族在该行中的数据

Map 数据类型

Bigtable 的 GoogleSQL 包含专门用于容纳列族的数据类型 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>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

稀疏表

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

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

字节

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

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

时间过滤条件

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

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

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

基础查询

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

检索最新版本

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

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

		地址
_key	街道	城市	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	地址
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 日上午 9:44:31.010 更新中的街道和城市与 2005 年 3 月 1 日上午 11:12:15.112 更新中的州/省/自治区/直辖市的组合。

地址
{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	烟雾	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: '关闭' 2023/04/01-08:41:40.000: '开启' 2020/07/03-06:25:31.000: '关闭' 2020/07/03-06:02:04.000: '开启'	2023/04/01-09:22:08.000: '关闭' 2023/04/01-08:53:12.000: '开启'
building1#section2	2021/03/11-07:15:04.000： “关闭” 2021/03/11-07:00:25.000： “开启”

您可以使用以下查询找到 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。列中的各个单元格之间以空行分隔，每个单元格表示一个带时间戳的传感器读数。

	指标
_key	温度	湿度
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 查询中，您的表名称可能需要转义，因为其中包含特殊字符或预留字词。

例如，以下查询因表名称中包含句点而无效。

  -- 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 *。

后续步骤

• 浏览 Bigtable 的 GoogleSQL 参考文档。
• 详细了解 Bigtable Studio。