管理输入数据和数据源

在评估您的输入数据时,请考虑所需的 I/O。您的查询要读取多少字节?您是否适当限制了输入数据量?您的数据是位于原生 BigQuery 存储中还是外部数据源中?查询读取的数据量和数据源会影响查询性能和费用。

您可以通过查询计划说明了解查询如何读取输入数据。

以下最佳做法提供了有关控制输入数据和选择数据源方面的指导。

控制投影 - 避免使用 SELECT *

最佳做法:控制投影 - 仅查询您需要的列。

投影是指您的查询所读取的列数。投影过多列会导致额外(浪费)的 I/O 和实体化(写入结果)。

使用 SELECT * 是成本最高的数据查询方式。当您使用 SELECT * 时,BigQuery 会全面扫描表中的每个列。

如果您要使用数据进行实验或浏览数据,请使用一个数据预览选项(而非 SELECT *)。

SELECT * 查询应用 LIMIT 子句不会影响数据读取量。您需要支付读取整个表中的所有字节而产生的费用,并且查询会占用免费层级配额。

请仅查询所需的列。例如,使用 SELECT * EXCEPT 从结果中排除一列或多列。

如果您确实需要查询表中的每个列,但只需要查询部分数据,请考虑使用以下方法:

  • 将目标表中的结果实体化,然后对该表执行查询
  • 按日期将对表分区,然后查询相关分区;例如,WHERE _PARTITIONDATE="2017-01-01" 仅扫描 2017 年 1 月 1 日的分区

查询部分数据或使用 SELECT * EXCEPT 可大幅减少查询所读取的数据量。除了节约费用外,减少数据 I/O 量以及查询结果所需的实体化作业量还可改善性能。

删减分区查询

最佳做法:查询分区表时,请使用 _PARTITIONTIME 伪列来过滤分区。

在查询分区表时,请使用 _PARTITIONTIME 伪列。通过使用 _PARTITIONTIME 过滤数据,您可以指定日期或日期范围。例如,以下 WHERE 子句使用 _PARTITIONTIME 伪列指定 2016 年 1 月 1 日与 2016 年 1 月 31 日之间的分区:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP(“20160101”)
    AND TIMESTAMP(“20160131”)

该查询仅处理日期范围所指示的分区中的数据,从而减少输入数据量。过滤分区可改善查询性能并减少费用。

尽可能对数据进行反规范化

最佳做法:数据在反规范化后,BigQuery 的效果最好。与其保留星型或雪花型架构等关系型架构,不如对数据进行反规范化并利用嵌套和重复的字段。嵌套的重复字段不但可以保持关系,而且也不会因保留关系(规范化)架构对性能产生影响。

通过规范化数据节省存储空间已经不是现代系统所关心的问题。对数据进行反规范化虽然会增加存储费用,但可以提高性能,因此是值得的。联接需要协调数据(通信带宽)。反规范化会将数据本地化到各个,因此可以实现并行执行。

如果您需要在对数据进行反规范化的同时保持关系,请使用嵌套的重复字段,而不是将数据完全展平。将关系型数据完全展平后,网络通信(数据重排)会对查询性能产生负面影响。

例如,如果对顺序架构进行反规范化而不使用嵌套的重复字段,您可能需要按 order_id 这样的字段进行分组(如果存在一对多关系)。由于涉及数据重排,与使用嵌套的重复字段对数据进行反规范化相比,对数据进行分组的性能较差。

在某些情况下,对数据进行反规范化并使用嵌套的重复字段可能并不会提高性能。请避免在以下使用场景中执行反规范化:

  • 您采用的是星型架构且维度更改频繁。
  • BigQuery 通过行级层修改对联机事务处理 (OLTP) 系统进行补充,但无法替代该系统。

使用嵌套和重复字段

BigQuery 不需要完全平展的反规范化操作。您可以使用嵌套和重复字段维持关系。

  • 嵌套数据 (STRUCT)

    • 通过嵌套数据,您可以用内嵌方式表示外部实体。
    • 查询嵌套数据时将使用“dot”语法来引用 leaf 字段,这与使用联接的语法类似。
    • 嵌套数据在标准 SQL 中表示为 STRUCT 类型
  • 重复数据 (ARRAY)

    • 通过创建类型为 RECORD 的字段并将模式设为 REPEATED,您可以用内嵌方式保留一对多关系(只要关系不是高基数)。
    • 使用重复数据后,便不再需要重排。
    • 重复数据表示为 ARRAY。查询重复数据时,您可以在标准 SQL 中使用 ARRAY 函数
  • 嵌套和重复数据(STRUCTARRAY

    • 嵌套和重复互为补充。
    • 例如,在事务记录表中,您可以包含一个具有专列项 STRUCT 的数组。

恰当使用外部数据源

最佳做法:如果查询性能对您来说是最重要的,请不要使用外部数据源。

查询 BigQuery 托管存储中的表通常比查询 Cloud Storage、Google 云端硬盘或 Cloud Bigtable 中的外部表要快得多。

对于以下用例请使用外部数据源:

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

避免过多通配符表

最佳做法:查询通配符表时,尽可能使用最详细的前缀。

借助精简的 SQL 语句使用通配符查询多个表。通配符表是指符合通配符表达式的表集合。如果数据集包含以下内容,则通配符表会非常有用:

  • 多个具备兼容架构且命名类似的表
  • 分片表

查询通配符表时,请在常用的表前缀后面指定通配符 (*)。例如,如果指定 FROM bigquery-public-data.noaa_gsod.gsod194*,系统会查询 20 世纪 40 年代的所有表。

与使用较短前缀相比,使用更详细的前缀的效果要更好。例如,使用 FROM bigquery-public-data.noaa_gsod.gsod194* 的效果比使用 FROM bigquery-public-data.noaa_gsod.* 更好,因为与该通配符匹配的表更少。

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面