在 BigQuery 中查询 Bigtable 变更日志

本页面提供指南和查询示例，可帮助您在 BigQuery 中处理 Bigtable 更新日志。

本页内容面向已完成以下任务的用户：

本指南假定您对 BigQuery 有一定的了解。如需了解详情，您可以按照介绍如何加载和查询数据的快速入门中的说明操作。

打开变更日志表

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

  2. 在探索器面板中，展开您的项目。

  3. 展开您的数据集。

  4. 点击带有以下后缀的表：_changelog。

表格式

整个输出架构包含多个列。本指南重点介绍了如何将行连接到列和值，以及将值解析为可分析的格式。

基本查询

本部分中的示例使用 Bigtable 表来跟踪信用卡销售数据。该表包含一个列族 (cf) 和以下几个列：

  • 行键，格式为 credit card number#transaction timestamp
  • 商家
  • 金额
  • 类别
  • 交易日期

查询一列

使用 WHERE 子句将结果过滤为仅一个列族和一个列。

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

解析值

所有值都存储为字符串或字节字符串。您可以使用转换函数将值类型转换为预期类型。

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

执行汇总

您可以执行更多操作，例如对数值执行聚合。

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

透视数据

如需执行涉及多个 Bigtable 列的查询，您需要对表进行数据透视。每个新的 BigQuery 行都包含变更数据流从 Bigtable 表中的对应行返回的一条数据变更记录。根据您的架构，您可以组合使用行键和时间戳对数据进行分组。

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

使用动态列集进行数据透视

如果您有一个动态列集，则可以执行一些额外的处理来获取所有列，然后以程序化方式将它们放入查询中。

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

JSON 数据

如果您使用 JSON 设置所有值，则需要解析这些值并根据键提取值。从 JSON 对象派生值后，您就可以使用解析函数了。下面的示例使用前面介绍的信用卡销售数据，但会将数据作为 JSON 对象写入单个列，而不是将数据写入多个列。

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

使用 JSON 进行聚合查询

您可以使用 JSON 值执行聚合查询。

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

后续步骤