将数据仓库迁移到 BigQuery:性能优化

本文档是系列文章中的一篇,可帮助您从本地数据仓库转换到 Google Cloud 上的 BigQuery。本文档介绍了在由 BigQuery 驱动的数据仓库中优化查询性能的基本技术。本文假设您已将架构和数据从旧版数据仓库迁移到 BigQuery,并且已将旧版作业的查询转换为 BigQuery 标准 SQL

在阅读本文时,请考虑在迁移过程中进行优化的原因。本文档的目的并不是详尽列出所有可用的优化技术,如需查看所有优化技术,请参阅 BigQuery 最佳做法的官方文档。 本文旨在帮助您思考是否以及何时应考虑使用这些技术。

该系列文章包含以下部分:

一般性能考虑因素

BigQuery 可同样有效地处理小型和 PB 级数据集的数据。借助 BigQuery,您的数据分析作业应该可以在新迁移的数据仓库中顺利运行,而无需任何修改。 如果您发现在某些情况下查询性能不符合您的预期,请参考本文中介绍的关键原则,了解各种因素如何影响性能。

优化利弊权衡

与任何系统一样,优化性能有时需要权衡利弊。 例如,使用高级 SQL 语法有时会增加复杂性并降低查询对非 SQL 专家的可理解性。 花时间对非关键工作负载进行微优化还可能分散资源,无法将其用于为应用构建新功能,或者进行更有影响的其他优化。因此,为了帮助您获得尽可能高的投资回报率,我们建议您将优化重心放在对数据分析流水线最重要的工作负载上。

如需评估特定查询是否存在问题,您可以使用 Cloud Monitoring 来监控 BigQuery 作业使用资源的情况。如果发现速度缓慢或占用大量资源的查询,则可以深入分析并将性能优化的重心放在该查询上。

容量和并发

BigQuery 将执行 SQL 查询所需的计算能力划分为称为的单位。 然后,BigQuery 会根据查询的大小和复杂程度自动计算每个查询所需的槽数。

BigQuery 会根据客户的历史记录、使用情况和支出,自动管理运行中的查询所共享的槽配额。 对大多数用户而言,每个项目的默认槽容量绰绰有余。 访问更多的槽并不能保证每次查询的速度更快。但更大的槽池有可能提高大规模或复杂查询的性能,还有可能提升高并发工作负载的性能。为了进一步提高查询性能,除了使用合适的数据模型和进行查询优化外,请考虑在适当的情况下购买更多预留槽所带来的好处。

对于查询,BigQuery 提供两种价格模式:按需价格固定价格。按需价格模式根据您运行的每个查询处理的数据量计费。如果您需要保持每月一致的分析支出,则固定价格更合适。当您注册固定价格方案时,您将以 BigQuery 槽为单位购买专用的查询处理能力。处理的所有字节的费用都包含在每月固定价格中。如果您的查询超出了固定价格容量,那么查询会被排入队列,直至固定价格资源可用。

查询计划和时间轴

借助 BigQuery 网页界面,您可以直观地查看查询计划和时间轴。您可以使用 jobs.get API 方法检索查询计划和时间轴信息。您可能还想试用一下 BigQuery Visualiser,这是一款开放源代码工具,可直观呈现 BigQuery 作业中的执行阶段流程。

BigQuery 在执行查询作业时,会将声明式 SQL 语句转换为执行图表。此图表会分解为一系列查询阶段,而这些阶段本身由更细化的多组执行步骤构成。BigQuery 使用高度分布式的并行架构来运行这些查询,而 BigQuery 阶段则模拟很多潜在工作器可能并行执行的工作单元。各阶段通过快速分布式重排架构相互通信。

查询执行计划。

除了查询计划之外,查询作业还会显示执行时间轴, 该时间轴用于提供查询工作器内处于已完成、待处理和活跃状态的工作单元的计量信息。 一个查询可能会同时经历多个具有活跃工作器的阶段,因此时间轴用于显示查询的整体进度。

时间轴统计信息。

如需估算查询的计算成本,您可以查看查询使用的总槽秒数。槽秒数越少越好,这意味着同一项目中同时运行的其他查询可以使用更多资源。

查询计划和时间轴统计信息可以帮助您了解 BigQuery 如何执行查询,以及某些阶段是否耗用太多资源。例如,生成的输出行远超输入行的 JOIN 阶段可能表示有必要在查询的早期阶段进行过滤。 不过,服务的托管特性限制了某些详细信息是否可直接操作。 如需改善查询的执行和性能,建议您遵循查询优化策略部分和 BigQuery 最佳做法文档中介绍的做法。

外部数据源

外部数据源(也称为“联合数据源”)是可从 BigQuery 直接查询的数据源,即使数据未存储在 BigQuery 中也是如此。您不用加载或流式传输数据,只需创建一个引用外部数据源的表即可。BigQuery 支持的外部数据源包括 Cloud BigtableCloud StorageGoogle 云端硬盘

外部数据源的查询性能可能低于原生 BigQuery 表中的数据查询性能。如果需要优先考虑查询速度,建议将数据加载到 BigQuery 中,而不要设置外部数据源。如果查询包含外部数据源,则其性能取决于外部存储类型。例如,查询存储在 Cloud Storage 中的数据比查询存储在 Google 云端硬盘中的数据更快。大体而言,外部数据源的查询性能应等同于直接从外部存储空间读取数据。

查询外部数据源时,系统不会缓存结果。 即使您多次发出相同的查询,也需要为针对外部表发出的每条查询付费。 如果您必须反复针对不经常更改的外部表发出查询,请考虑将查询结果写入永久表,并针对永久表运行查询。

通常,请考虑仅针对以下使用场景使用外部数据源:

  • 加载数据时执行提取、转换和加载 (ETL) 操作。
  • 查询频繁更改的数据。
  • 处理定期加载操作,例如周期性地从 Bigtable 中提取数据。

如需详细了解使用外部数据源的机会、限制和最佳做法,请参阅 BigQuery 官方文档

查询优化策略

在查看上一部分后,如果您发现特定查询的性能与预期不符,请考虑按照本部分介绍的策略优化这些查询。

分区

分区包括将表划分为多个称为“分区”的细分。通过将大型表划分为较小的分区,可以改善查询性能;通过减少查询读取的字节数,可以控制费用。如果表的架构允许,建议对超过 10 GB 的表进行分区。

BigQuery 中有两种类型的表分区:

您可以通过下列方式在 BigQuery 中创建分区表

例如,假设您已将具有以下架构的 orders 表从旧版数据仓库迁移到 BigQuery:

orders
列名 类型
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER

现在,您可以通过 orderDate 列按日期对 orders 表进行分区,方法如下:

CREATE TABLE `your-project.sales.orders_partitioned` (
  salesOrderKey STRING,
  orderDate TIMESTAMP,
  customerKey STRING,
  totalSale FLOAT64,
  currencyKey INT64
)
PARTITION BY DATE(orderDate)
AS
SELECT * FROM `your-project.sales.orders`

以下查询在分区表上的运行速度比在未分区表上的运行速度要快得多,因为 WHERE 子句会提示 BigQuery 它只需要扫描单个分区中的数据:

SELECT
 DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
 currencyKey,
 COUNT(*) AS numberOfOrders,
 SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_partitioned`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-01-01'
GROUP BY DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

如需详细了解何时考虑进行分区以改进架构,请参阅架构和数据转移概览

聚簇

聚簇可以提高某些类型的查询(例如,使用过滤条件子句的查询和聚合数据的查询)的性能。当查询作业或加载作业将数据写入聚簇表时,BigQuery 会使用聚簇列中的值对数据进行排序。这些值用于将数据整理到 BigQuery 存储空间的多个块中。当您提交包含根据聚簇列过滤数据的子句的查询时,BigQuery 会使用已排序的块来避免对不需要的数据进行扫描,从而提高查询速度并降低费用。

目前,BigQuery 仅支持对分区表进行聚簇。提取时间分区表以及按 DATETIMESTAMP分区的表都支持表聚簇。

在以下情况下,您可以对分区表使用聚簇:

  • 您的数据已按日期或时间戳列进行分区。
  • 您在查询中通常针对特定列进行过滤或聚合。

当您结合使用聚簇和分区时,可以按日期或时间戳列对数据进行分区,然后按不同的列组合对该数据进行聚簇。在这种情况下,每个分区中的数据都基于聚簇列的值进行聚簇。通过分区,您可以了解查询作业的准确费用估算值(基于已扫描的分区)。

如需进一步改进上个部分中的示例,您可以根据原本的 orders 表创建一个名为 orders_clustered 的表,该表既进行了分区,又进行了聚簇:

CREATE TABLE
 `your-project.sales.orders_clustered` (
   salesOrderKey STRING,
   orderDate TIMESTAMP,
   customerKey STRING,
   totalSale FLOAT64,
   currencyKey INT64 )
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT * FROM `your-project.sales.orders`

由于结合使用了分区和 WHERE 过滤条件,以下查询在 orders_clustered 表上的运行速度要快于原本的 orders 表。由于结合使用了聚簇和 customerKey IN [...] 以及 GROUP BY customerKey 子句,其运行速度还应该快于 orders_partitioned 表。

SELECT
  customerKey,
  DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
  currencyKey,
  COUNT(*) AS numberOfOrders,
  SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_clustered`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
  AND customerKey IN (
    '1292803200000-402',
    '1298764800000-414',
    '1267401600000-212',
    '1267488000000-209')
GROUP BY customerKey, DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

如需详细了解何时考虑进行聚簇以改进架构,请参阅架构和数据转移概览

反规范化

反规范化是用于提高之前已规范化的关系型数据集的读取性能的常用策略。在 BigQuery 中对数据进行反规范化的推荐方法是使用嵌套和重复字段。当关系具有分层结构并且经常以父子关系一起被查询时,最好使用此策略。

使用嵌套和重复字段会将数据本地化到各个,因此可以实现并行执行。这种方法可以在不完全展平反规范化关系型数据的情况下维护关系,从而通过在重排阶段减少网络通信来进一步改善性能。

对数据进行反规范化会增加所需的存储量。不过,由于 BigQuery 的低费用存储特性,这种增加在大多数情况下无需担心。 通过将计算资源换为存储资源,反规范化有助于降低具有高读取率的应用的整体费用。 费用降低代表整体速度提升以及计算和网络使用量下降。

嵌套数据在 BigQuery 标准 SQL 中表示为 STRUCT 类型。重复数据表示为 ARRAY 类型,并且可以使用 ARRAY 函数。 嵌套和重复也可以互为补充(由 STRUCT 组成的 ARRAY)。如需了解详情,请参阅 BigQuery 反规范化的最佳做法

例如,请考虑具有以下架构的两个表 orders_clusteredorder_lines_clustered

orders_clustered
列名 类型
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER
order_lines_clustered
列名 类型
salesOrderKey STRING
salesOrderLineKey STRING
productKey STRING
quantity INTEGER
unitPrice FLOAT
orderDate TIMESTAMP
totalSale FLOAT

您可以创建一个反规范化表 (orders_nested),该表将其他两个表中的数据预联接到重复的嵌套字段中:

CREATE TABLE `your-project.sales.orders_denormalized`
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  o.customerKey,
  o.totalSale,
  ARRAY_AGG(
    STRUCT(
      ol.salesOrderLineKey,
      ol.productKey,
      ol.totalSale
  )) AS lineItem
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
GROUP BY 1,2,3,4,5

现在,请考虑以下两个 SELECT 查询:

SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  ol.salesOrderLineKey,
  ol.productKey,
  ol.totalSale
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
WHERE DATE_TRUNC(DATE(o.orderDate), MONTH) = '2015-02-01'
  AND o.customerKey = '1201392000000-325'

SELECT
 salesOrderKey,
 orderDate,
 currencyKey,
 lineItem
FROM `your-project.sales.orders_denormalized`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

第二个查询的运行速度应该比第一个查询快得多,因为它在具有预联接数据的反规范化表上运行。

第二个查询以 ARRAY(STRUCT()) 数据类型返回 lineItem 值:

第二个查询的结果。

您可以使用 UNNEST 运算符展平数组

SELECT
 o.salesOrderKey,
 o.orderDate,
 o.currencyKey,
 ol.*
FROM `your-project.sales.orders_denormalized` AS o
  JOIN UNNEST(lineItem) AS ol
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

查询以展平形式返回结果,如下面的屏幕截图所示:

查询结果以展平形式显示。

如需详细了解何时考虑进行反规范化以改进架构,请参阅架构和数据转移概览

近似函数

近似聚合是对聚合函数输出(例如基数和分位数)进行估算。BigQuery 支持一系列近似聚合函数,这些函数使用 HyperLogLog++ 算法估算大型数据集中不同的值。

近似聚合函数所需的内存少于相应的精确聚合函数,但它们也会引入统计不确定性。您可以将近似聚合函数用于无法使用线性内存的大型数据流以及已经取近似的数据。

例如,请考虑以下两个查询:

SELECT
  COUNT(DISTINCT salesOrderKey)
FROM `your-project.sales.orders`

SELECT
 APPROX_COUNT_DISTINCT(salesOrderKey)
FROM `your-project.sales.orders`

第一个查询返回精确计数值。第二个查询返回一个近似计数值,此值与精确值的偏差可以忽略不计。不过,它的运行速度应该比第一个查询快得多。

反模式

如需进一步提升性能,请参阅有关反模式的 BigQuery 文档以避免常见错误,例如使用输出多于输入的联接、使用特定于点的 DML 语句,或者查询未过滤的偏差数据。

更好的做法

本文档仅重点介绍可用于提高已迁移数据仓库的查询性能的几种最佳做法。如需进一步提高性能以及了解其他最佳做法,请参阅有关优化查询性能的详尽文档。

后续步骤

探索有关 Google Cloud 的参考架构、图表、教程和最佳做法。查看我们的云架构中心