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 之前应用。您必须提供有效的时间戳。
| 参数 | 说明 |
|---|---|
as_of |
Timestamp。返回时间戳小于或等于所提供时间戳的最新值。 |
with_history |
Boolean。用于控制是将最新值作为标量返回，还是将带时间戳的值作为 STRUCT 返回。 |
after_or_equal |
Timestamp。值的后缀为时间戳，时间戳包括在内。需要 with_history => TRUE |
before |
Timestamp。时间戳早于输入时间戳（不含）的值。需要 with_history => TRUE |
latest_n |
整数。每个列限定符（映射键）返回带时间戳的值的数量。必须大于或等于 1。需要指定 with_history => TRUE。 |
如需查看更多示例，请参阅高级查询模式。
基础查询
本部分介绍了基本 Bigtable SQL 查询及其运作方式，并提供了示例。如需查看其他示例查询，请参阅 GoogleSQL for Bigtable 查询模式示例。
检索最新版本
虽然 Bigtable 允许您在每个列中存储多个版本的数据，但 Bigtable 专用 GoogleSQL 默认会返回每行数据的最新版本（即最新的单元格）。
请考虑以下示例数据集，其中显示 user1 在纽约州搬迁了两次，在布鲁克林市搬迁了一次。在此示例中，address 是列族，列限定符分别为 street、city 和 state。列中的单元格用空行分隔。
| 地址 | |||
|---|---|---|---|
| _key | street | 城市 | 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 * 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 过滤器，您可以检索某个时间点的行状态。例如，如果您想知道 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 更新中的州。
| 地址 | ||
|---|---|---|
| {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, 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'
结果如下：
| location | CO_sensor |
|---|---|
| building1#section1 | 'on' |
查询时序数据
Bigtable 的一个常见用例是存储时序数据。请考虑以下示例数据集，其中显示了天气传感器的温度和湿度读数。列族 ID 为 metrics，列限定符为 temperature 和 humidity。列中的单元格以空行分隔，每个单元格都表示带时间戳的传感器读数。
指标 |
||
|---|---|---|
| _key | temperature | 湿度 |
| 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(after => TIMESTAMP('2023/01/04-23:00:00'),
before => TIMESTAMP('2023/01/05-01:00:00'))
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} |
查询 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 查询数据。以下示例展示了如何发出查询并访问数据：
Java
如需使用此功能，您必须使用 java-bigtable 2.41.0 或更高版本。如需详细了解用法，请参阅 Javadoc 中的 executeQuery、Statement 和 ResultSet。
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
}
}
}
Python asyncio
如需使用此功能，您必须使用 python-bigtable 2.26.0 或更高版本。
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"])