GoogleSQL for Bigtable 概览

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

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

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

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

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

使用场景

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

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

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

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

主要概念

本部分介绍使用 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>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

稀疏表

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

不过，当您使用 GoogleSQL 查询 Bigtable 表时，未使用的列将用空映射表示，并作为 NULL 值返回。这些 NULL 值可用作查询谓词。例如，只有在行中使用 column1 时，WHERE family['column1'] IS NOT NULL 等谓词才能用于返回行。

字节

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

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

时间过滤器

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

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

基础查询

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

检索最新版本

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

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

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

   SELECT * FROM myTable WHERE _key = 'user1'

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

检索所有版本

如需检索数据的旧版本（单元格），请使用 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>>>。数组中的每个项都是指定行、列族和列的时间戳值。时间戳按时间倒序排列，因此最新的数据始终是返回的第一个项。

查询响应如下所示。

您还可以使用数组函数检索每行中的版本数量，如以下查询所示：

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

检索指定时间的数据

使用 as_of 过滤器，您可以检索某个时间点的行状态。例如，如果您想知道 2022 年 1 月 10 日下午 1:14 的 user 的地址，可以运行以下查询。

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

结果显示了 2022 年 1 月 10 日下午 1:14 的最后已知地址，即 2021/12/20-09:44:31.010 更新中的街道和城市，以及 2005/03/01-11:12:15.112 更新中的州。

您也可以使用 Unix 时间戳来实现相同的结果。

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

请考虑以下数据集，其中显示了烟雾和一氧化碳警报的开启或关闭状态。列族为 alarmType，列限定符为 smoke 和 carbonMonoxide。每列中的单元格由空行分隔。

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

  SELECT _key AS location, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['smoke'] = 'on'

结果如下：

查询时序数据

Bigtable 的一个常见用例是存储时序数据。请考虑以下示例数据集，其中显示了天气传感器的温度和湿度读数。列族 ID 为 metrics，列限定符为 temperature 和 humidity。列中的单元格以空行分隔，每个单元格都表示带时间戳的传感器读数。

您可以使用时间过滤条件 after、before 或 after_or_equal 检索特定范围的时间戳值。以下示例使用 after：

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

该查询会以以下格式返回数据：

查询 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 版 Bigtable 客户端库支持使用 executeQuery API 通过 SQL 查询数据。以下示例展示了如何发出查询并访问数据：

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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
      }
    }
  }

  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"])

后续步骤

• 浏览“适用于 Bigtable 的 GoogleSQL”参考文档。
• 详细了解 Bigtable Studio。