面向数据仓库专业人员的 BigQuery 介绍

更新时间:2017 年 9 月

本文介绍如何将 BigQuery 用作数据仓库,包括将常见数据仓库概念与 BigQuery 中的概念一一对应,并介绍了如何在 BigQuery 中完成标准数据仓储任务。

服务模型比较

下表将标准数据仓库概念与 BigQuery 中的概念一一对应:

数据仓库 BigQuery
数据仓库 BigQuery 服务取代了传统数据仓库的典型硬件设置。也就是说,它可以充当组织中所有分析数据的集体家园。
数据集市 数据集是可以按业务范围或给定分析领域划分的表的集合。每个数据集都与一个 Google Cloud Platform (GCP) 项目相关联。
数据湖 您的数据湖可能包含 Cloud StorageGoogle 云端硬盘中的文件,或 Cloud Bigtable 中的事务性数据。BigQuery 可以定义架构并直接对作为联合数据源的外部数据发出查询。
表和视图 表和视图在 BigQuery 中的功能与在传统数据仓库中的功能相同。
授权 Cloud Identity and Access Management (Cloud IAM) 用于授予在 BigQuery 中执行特定操作的权限。

数据集

BigQuery 将数据表组织整理成称为数据集的单元。这些数据集的范围限定在您的 GCP 项目中。如需从命令行、SQL 查询或代码中引用表格,可以使用以下构造:

project.dataset.table

这些范围(项目、数据集和表)可以帮助您在逻辑上构建信息。您可以使用多个数据集来分隔与不同分析领域相关的表,并且可以使用项目级范围来根据业务需求隔离数据集。

以下是 BigQuery 的结构概览:

BigQuery 结构概览

预配和系统大小调整

与许多 RDBMS 系统不同,您无需在使用 BigQuery 之前配置资源。BigQuery 会根据您的使用规律动态分配存储资源和查询资源。

  • 存储资源在您使用它们时进行分配,并在您移除数据或删除表时释放。
  • 查询资源根据查询类型和复杂度进行分配。每个查询使用一定数量的,槽是包含一定数量 CPU 和 RAM 的计算单位。

使用 BigQuery 时您不必做出最低使用承诺。该服务根据您的实际使用情况分配资源并收取费用。默认情况下,所有 BigQuery 客户均有权访问 2000 个槽以执行查询操作。另外,您还可以为项目预留固定数量的槽。如需详细了解应使用哪种方法,请参阅费用部分。

存储管理

在内部,BigQuery 以名为 Capacitor 的专有列式格式存储数据,这在数据仓库工作负载方面有诸多优势。使用专有格式的原因是它可以与查询引擎同时改进,而后者可利用对数据布局的深入了解来优化查询执行。最后,BigQuery 使用查询访问模式来确定实际分片的最佳数量以及它们的编码方式。

这些数据实际存储在称为 Colossus 的 Google 分布式文件系统上,该系统通过使用擦除编码将数据的冗余块存储在多个物理磁盘上来确保持久性。此外,数据会被复制到多个数据中心。

另外,您还可以使用联合数据源对不在 BigQuery 存储内的数据运行 BigQuery 查询,例如存储在 Cloud Storage、Google 云端硬盘或 Cloud Bigtable 中的数据。不过,这些源并未针对 BigQuery 操作进行优化,因此它们的性能可能不如存储在 BigQuery 存储空间中的数据。

维护

BigQuery 是一项全托管式服务,这意味着 BigQuery 工程团队会为您处理更新和维护。升级时无需停机,也不会影响系统性能。

许多传统系统需要每隔一段时间就运行一次资源密集型清空流程,以将数据块重新配置和分类并恢复空间。但是,BigQuery 没有等效的清空流程,因为存储引擎会持续管理和优化数据的存储和复制方式。此外,由于 BigQuery 不使用表上的索引,因此您无需重建索引。

备份与恢复

BigQuery 解决了服务级层的备份和灾难恢复问题。此外,通过维护为期 7 天的完整表格更改记录,BigQuery 可让您查询数据的时间点快照。您可以轻松还原更改,而无需从备份请求恢复。(当明确删除表时,其历史记录将在 2 天后清空。在撰写本文时,仅旧版 SQL 支持快照功能。)

管理工作流

本部分介绍管理任务,例如在 BigQuery 中组织数据集、授予权限和培训新手用户。此外,本部分还介绍了如何管理并发工作负载、监视数据仓库的运行状况以及审核用户访问权限。

组织数据集

您可以根据数据分类或业务部门将数据集划分为单独的项目,或者为了简单起见将它们合并到常见项目中。

您可以邀请数据分析师以您定义的任何受限角色对现有数据集进行协作处理。当数据分析师登录 BigQuery 网页界面时,他们只会看到与其跨项目共享的数据集。他们可以对数据集执行的操作因人而异,具体取决于其在每个数据集中所属的角色。

授予权限

在传统的 RDBMS 系统中,您可以通过创建 SQL 授权并将其应用于数据库系统中的给定用户来授予查看或修改表的权限。此外,某些 RDBMS 系统允许您向外部目录(如 LDAP)中的用户授予权限。用于管理用户和权限的 BigQuery 模型类似于后一种模型。

BigQuery 提供用于控制资源访问权限的预定义角色。此外,您也可以创建由您定义的权限集组成的自定义 Cloud IAM 角色,然后将这些角色分配给用户或组。或者,您可以将角色分配给 Google 电子邮件地址或 G Suite 群组

操作数据仓库的一个重要方面是允许不同用户组对相同数据进行共享但受控制的访问。例如,财务、人力资源和市场营销部门均有权访问相同的表,但访问权限级别不同。传统的数据仓储工具通过强制实现行级安全性来实现这一点。您可以通过定义已获授权的视图行级权限在 BigQuery 中实现相同的结果。

培训新手用户

按照传统,新入职的数据分析师需要大量的准备时间。要使分析师能够运行简单查询,您必须向他们展示数据源所在的位置,并设置 ODBC 连接和工具以及访问权限。使用 GCP,您可以大幅缩短分析师进入工作状态需要的时间。

为帮助分析师快速掌握 GCP,您可以为其授予对相关项目的访问权限,介绍 Google Cloud Platform Console 和 BigQuery 网页界面,并共享一些查询以帮助他们熟悉数据:

  • GCP Console 提供 GCP 环境中所有资源的集中视图。对数据分析师而言,相关性最强的资源可能是 Cloud Storage 存储分区,他们可以在这些存储分区上协作处理文件。
  • BigQuery 网页界面显示分析师有权访问的数据集列表。分析师可以通过您授予的角色在 GCP Console 中执行任务,例如查看元数据、预览数据、执行查询、保存查询及共享查询。

管理工作负载和并发

BigQuery 会限制传入请求的最大速率,并按项目强制执行相应的配额政策。具体政策取决于资源可用性、用户个人资料、服务使用记录和其他因素。如需了解详情,请参阅 BigQuery 配额政策

BigQuery 提供两种类型的查询优先级,即交互式查询和批量查询。默认情况下,BigQuery 运行交互式查询,也就是说查询会尽快执行。请注意,交互式查询会计入查询配额。批量查询会先行排队,一旦有闲置资源可用就会执行,通常在几分钟之内。

BigQuery 不支持对交互式查询或批量查询进行精细的优先排列。鉴于 BigQuery 的运行速度和规模,许多传统的工作负载问题都不适用。如果需要明确安排查询的优先级,您可以将敏感工作负载归入一个单独的项目,并为该项目预留一定数量的槽。请与您的 Google 代表联系,以帮助您成为固定费率客户。

监控和审核

您可以使用 Stackdriver 监控 BigQuery,前者会根据 BigQuery 指标定义各种图表和提醒。例如,您可以使用“查询时间”指标监控系统吞吐量,也可以根据“已分配的槽”指标直观呈现查询需求的趋势。如果需要提前规划要求严苛的查询,您可以使用“可用槽”指标。要主动掌握系统运行状况,您可以根据您定义的阈值创建提醒。Stackdriver 提供基于网页的自助服务门户。您可以通过 Stackdriver 帐号控制对门户的访问权限。

BigQuery 会自动创建用户操作的审核日志。您可以以批量或数据流的方式将审核日志导出到另一个 BigQuery 数据集,并使用首选分析工具直观呈现日志。如需了解详情,请参阅使用 BigQuery 分析审核日志

管理数据

本部分介绍架构设计的注意事项、反规范化、分区的工作原理以及将数据加载到 BigQuery 的方法。本部分最后介绍如何在保持零分析停机时间的同时处理仓库中的更改。

设计架构

请遵循以下一般准则为 BigQuery 设计最佳架构:

  • 对大于 10GB 的维度表进行反规范化,除非您有强有力的证据表明优化查询的优势被数据操纵(UPDATEDELETE 操作)的费用抵消了。
  • 对小于 10GB 的维度表保持规范化,除非该表很少进行 UPDATEDELETE 操作。
  • 充分利用反规范化表中的嵌套和重复字段。

反规范化

传统的数据反规范化方法涉及将事实及其所有维度写入平面表结构中。例如,对于销售交易,您可以将每项事实及其维度写入记录,例如订单和客户信息。

与此相对,反规范化数据的首选方法利用了 BigQuery 对 JSON 或 Avro 输入数据中的嵌套和重复结构的原生支持。使用嵌套和重复结构表示记录可以为底层数据提供更自然的表示形式。以销售订单为例,JSON 结构的外部部分包含订单和客户信息,内部部分包含订单的各个订单项,这些订单项表示为嵌套的重复元素。

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

使用嵌套和重复字段表示记录可以简化使用 JSON 或 Avro 文件的数据加载。创建此架构后,可以使用点表示法对任何单个字段执行 SELECTINSERTUPDATEDELETE 操作,例如 Order.Item.SKU。如需查看示例,请参阅 BigQuery 文档

反规范化的优势

BigQuery 本质上是一个分析引擎。它支持 DML 操作,但不应用作联机事务处理 (OLTP) 存储系统。关于更改数据的介绍提供了在处理更改的同时保持零分析停机时间,并提供最佳联机分析处理 (OLAP) 性能的指南。虽然规范化或部分规范化的数据结构(如星型架构或雪花型架构)适用于更新/删除操作,但它们并非 OLAP 工作负载的最佳选择。在规范化表上执行 OLAP 操作时,必须联接多个表以执行所需的聚合。 可以使用 BigQuery 进行联接,有时也推荐使用小表。然而,它们的性能通常不如反规范化结构。

下图比较了使用联接的查询性能和与表大小相关的简单过滤条件的查询性能。查询性能在存在联接时下降得更快。

使用联接与过滤器的查询时间对比

反规范化的缺点

反规范化架构不是最佳存储架构,但 BigQuery 的低费用存储空间解决了存储效率低下的问题。您可以将费用与查询速度的提升进行对比,以了解为何无需过于关注存储空间。

使用反规范化架构的一个挑战在于维护数据的完整性。根据变化的频率和范围,维护数据完整性可能需要更多的机器时间,有时还需要用于测试和验证的人工时间。

分区表

BigQuery 支持按日期对表分区。创建表时,您需要启用分区功能。BigQuery 会自动创建基于日期的新分区,无需额外的维护。此外,您可以为分区中的数据指定过期时间。

插入到分区表中的新数据会在插入时写入原始分区。如需明确控制数据加载的分区,可以使加载作业指定一个特定日期分区。

加载数据

在将数据加载到 BigQuery 以用于分析工作负载之前,它通常存储在 Cloud Storage 产品中,并且采用原始格式。在迁移到 GCP 的早期阶段,常见的模式是使用现有的提取、转换和加载 (ETL) 工具将数据转换为 BigQuery 的理想架构。转换数据后,它将作为 CSV、JSON 或 Avro 文件传输到 Cloud Storage,然后通过加载作业流式传输加载到 BigQuery 中。或者,您可以使用现有本地数据存储空间的原生架构将文件传输到 Cloud Storage、加载到 BigQuery 中的一组暂存表,然后使用 BigQuery SQL 命令转换为 BigQuery 的理想架构。这两种方法如下所示:

第一种加载数据的方法

第二种加载数据的方法

随着您深入使用 GCP,您可能会直接在 Cloud BigtableCloud DatastoreCloud Spanner 中捕获源数据,并使用 Cloud Dataflow 通过批量或流式方式将数据 ETL 到 BigQuery。

直接捕获源代码

使用加载作业

本部分假设您的数据在 Cloud Storage 中作为受支持文件格式的文件集合。如需详细了解每种数据格式,以及选择格式时要考虑的特定要求和功能,请参阅 BigQuery 数据格式

除了 CSV 之外,还可以通过 --field_delimiter 标志使用带有除逗号外的分隔符的数据文件。如需了解详情,请参阅 bq 加载标志

BigQuery 支持加载 gzip 压缩文件。但是,加载压缩文件的速度低于加载未压缩文件的速度。对于时间敏感的使用场景,或者将未压缩文件传输到 Cloud Storage 受带宽或时间限制的使用场景,请执行快速的加载测试以确定最佳方案。

由于加载作业是异步进行的,因此在执行作业时无需维护客户端连接。更重要的是,加载作业不会影响您的其他 BigQuery 资源。

如果目标表尚不存在,则加载作业时将创建目标表。

BigQuery 按如下方式确定数据架构:

  • 如果您的数据采用自描述的 Avro 格式,BigQuery 可以直接确定架构。
  • 如果数据采用 JSON 或 CSV 格式,BigQuery 可以自动检测架构,但建议手动验证

您可以通过将架构作为参数传递给加载作业来明确指定架构。正在进行的加载作业可以使用与初始加载相同的过程附加到同一个表,但不要求每个作业都传递架构。

如果 CSV 文件始终包含在初始加载和表创建后需要忽略的标题行,您可以使用 --skip_leading_rows 标志忽略该行。如需了解详情,请参阅 bq 加载标志

BigQuery 会为每个项目和每个表可以执行的加载作业的数量和大小设置每日限额。此外,BigQuery 还对各个加载文件和记录的大小设置了限额。如需了解详情,请参阅配额政策

您可以通过 BigQuery 网页界面启动加载作业。要自动执行此过程,您可以设置 Cloud Function 以侦听与到达给定存储分区中的新文件相关联的 Cloud Storage 事件,并启动 BigQuery 加载作业。

使用流式插入

作为替代和补充方法,您还可以将数据直接流式插入到 BigQuery。流式插入的数据可以立即使用,并且可以与现有表数据一起实时查询。

对于可以从实时信息中受益的情况,例如欺诈检测或监控系统指标,流式插入可能会是一个显著的区别。但是,与 BigQuery 中免费的加载作业不同,流式插入数据需要付费。因此,只有在收益大于费用的情况下,再选择使用流式传输。

将数据流式插入到 BigQuery 表时,可以使用 BigQuery API 将记录直接发送到 BigQuery。如果使用 GCP 的日志记录服务 Logging,您还可以将 GCP 项目的日志直接流式传输到 BigQuery,包括来自 App Engine 的请求日志和发送到 Logging 的自定义日志信息。

处理变更

许多数据仓库都必须遵守严格的服务等级协议 (SLA),需要的停机时间非常少,甚至为零。由 Google 负责 BigQuery 的正常运行时,您可以使用反映数据更改的方法来控制数据集的可用性和响应性。

BigQuery 中的所有表修改均符合 ACID 标准。这适用于 DML 操作、针对目标表的查询以及加载作业。在提供用户查询时进行插入、更新和删除的表可以妥善处理并发,并以原子方式从一个状态转换到下一个状态。因此,修改表不需要停机时间。但是,在最近刷新的数据可用于分析之前,您的内部流程可能需要经过测试和验证。此外,由于 DML 操作与槽上的分析工作负载竞争,因此您可能更希望对其进行隔离。出于这些原因,您可能会引入停机时间。本文使用术语“分析停机时间”来避免与 BigQuery 服务停机时间混淆。

您可以应用大多数旧的和经过验证的技术来处理分析停机时间。本部分对一些已知的挑战和补救措施进行了详细讲解。

滑动时间窗口

与数据湖不同,传统数据仓库仅在固定的时间段内保留数据,例如过去 5 年。在每个更新周期中,新数据将添加到仓库中,最旧的数据将被移出,从而保持固定的时长。在大多数情况下,这个概念被用来解决旧技术的局限性。

相反,BigQuery 可以随着仓库规模的增长而扩容,因此无需删除旧数据。通过保留完整历史记录,您可以更好地了解您的业务。如果您担心存储费用过高,则可以充分利用 BigQuery 的长期存储价格,归档旧数据并在需要时使用这些数据进行特定分析。如果您仍有充分的理由删除旧数据,则可以使用 BigQuery 对日期分区表分区有效期的原生支持。换句话说,BigQuery 可以自动删除旧数据。

更改架构

在数据仓库设计和开发期间,通常需要添加、更新或删除列甚至添加或删除整个表来调整表架构。除非更改采用添加列或表的形式,否则它可能会破坏那些引用要删除的表、重命名的列等的已保存的查询和报告。

数据仓库用于生产环境后,此类更改将受到严格的更改控制。您可能会决定在分析停机期间处理细微架构更改,但在大多数情况下,架构更改会被处理为版本升级。您可以同时设计、开发和测试此升级,而上一版本的数据仓库会为分析工作负载提供服务。在将架构更改应用于 BigQuery 数据仓库时,您需要遵循相同的方法。

缓慢变化维度

规范化数据架构通过隔离维度表中的更改使缓慢变化维度 (SCD) 的影响降到最低。它通常优于反规范化架构,其中 SCD 可能引起对扁平事实表的大范围更新。然而,如架构设计部分所述,请谨慎对 BigQuery 使用规范化。

对于 SCD 而言,并不存在适用于所有情况的解决方案。了解更改的性质并应用最相关的解决方案或解决方案组合非常重要。本部分的其余内容概述了一些解决方案以及如何将它们应用于 SCD 类型。

技术 1:视图切换

此技术基于两种数据视图,包括“主”视图与“影子”视图。技巧在于隐藏实际表并向用户公开“主”视图。在更新周期中,系统创建/更新“影子”视图,并在用户针对“主”视图操作时进行数据正确性测试。在切换时,“主”视图与“影子”视图交换。旧的“主”视图(即当前“影子”视图)可以被清理,直到下一个更新周期到来,或者根据组织定义的规则和流程予以保留,以用于某些工作流。

这两个视图可以以共同的表为基础,并通过列进行区分,例如“view_type”,或以不同的表为基础。不推荐使用前一种方法,因为针对表的“影子”视图的 DML 操作可能会减慢用户针对“主”视图的查询速度,而不会带来任何实际的好处。

虽然视图切换提供零分析停机时间,但它具有更高的用费,因为更新周期期间存在两个数据副本。更重要的是,如果更新周期少于 90 天,此方法可能会妨碍您的组织充分利用长期存储价格。90 天是根据撰写本文时的价格政策而定的天数,如需了解最新天数,请务必查看最新政策。

有时,不同的数据细分会按照自己的速度进行更改。例如,北美的销售数据每天更新,而亚太地区的数据每两周更新一次。在这种情况下,最好根据更改的驱动因子对表进行分区,本示例中为“国家/地区”。然后,将视图切换应用于受影响的分区,而非整个数据仓库。在撰写本文时,您只能明确将数据拆分为多个表,根据自定义数据属性(例如国家/地区)进行分区。

技术 2:就地分区加载

当数据更改可以通过分区进行隔离,并且允许短暂的分析停机时间时,视图切换可能显得大材小用。这种情况下,受影响分区的数据可以在其他 BigQuery 表中暂存,也可以导出到 Cloud Storage 中的文件,以便在分析停机时间期间替换它们。

如需使用来自另一个表的查询的数据替换目标分区中的数据,请执行以下代码:

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

如需通过从 Cloud Storage 加载来替换目标分区中的数据,请执行以下代码:

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
技术 3:更新数据遮盖

一个经常变化且较小的维度是进行规范化的主要候选者。在此技术中,对这类维度的更新将在与其余数据有条件地联接的隔离表或视图中暂存:

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
SCD 类型 1:覆盖属性值

SCD 类型 1 使用新数据覆盖属性值,而不保留历史记录。例如,如果产品“超赞的保湿霜”属于“美容养生”类别,而现在被归类为“化妆品”,则变化如下:

之前:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC 超赞的保湿霜 - 100 盎司 美容养生

之后:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC 超赞的保湿霜 - 100 盎司 美容养生
化妆品

如果属性位于规范化维度表中,则更改会非常孤立。您只需更新维度表中受影响的行。对于具有频繁的类型 1 更新的较小维度表,请使用技术 3:更新数据遮盖

如果属性以反规范化方式嵌入事实表中,则更改涉及的范围较广。您必须更新属性重复的所有事实行。在这种情况下,请使用技术 2:就地分区加载技术 1:视图切换

SCD 类型 2:更改属性值并保留历史记录

此方法通过使用单独的代理键为给定自然键创建多个记录来跟踪无限制的历史数据。例如,SCD 类型 1 中说明的相同更改将按以下方式处理:

之前:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC 超赞的保湿霜 - 100 盎司 美容养生 31-Jan-2009 NULL

之后:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC 超赞的保湿霜 - 100 盎司 美容养生 31-Jan-2009 18-JUL-2017
124 ABC 超赞的保湿霜 - 100 盎司 化妆品 19-JUL-2017 NULL

如果属性位于规范化维度表中,则更改会很孤立。您只需更新上一行并在维度表中添加新行。对于具有频繁的类型 1 更新的较小维度表,请使用技术 3:更新数据遮盖

如果属性以反规范化方式嵌入事实表中,则情况可能更有利,只要您无需维护值的明确开始日期和结束日期,而是依赖于事务日期。由于之前的事务发生的日期和时间的值仍然是真实的,因此您无需更改以前的事实表行。事实表应如下所示:

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNITS AMOUNT
18-JUL-2017 123 ABC 超赞的保湿霜 - 100 盎司 美容养生 2 25.16
19-JUL-2017 124 ABC 超赞的保湿霜 - 100 盎司 化妆品 1 13.50

查询数据

BigQuery 支持标准 SQL 查询,并兼容 ANSI SQL 2011。BigQuery 的 SQL 参考提供了对所支持的所有函数、运算符和正则表达式功能的全面说明。

由于 BigQuery 支持嵌套和重复字段作为数据模型的一部分,因此其 SQL 支持也已进行扩展,以便支持这些字段类型。例如,使用 GitHub 公开数据集,您可以发出 UNNEST 命令,该命令允许您迭代重复字段:

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

交互式查询

BigQuery 网页界面允许交互式查询数据集,并为您有权访问的项目的数据集提供汇总视图。此外,控制台还提供了一些有用的功能,例如保存和共享临时查询、调整和修改历史查询、查看表和架构以及收集表元数据。如需了解详情,请参阅 BigQuery 网页界面

bigquery 网页界面屏幕截图

自动查询

根据计划/事件自动执行查询,并缓存结果供以后使用,是一种常见的做法。

如果您使用 Airflow 编排其他自动化操作并且已经熟悉该工具,请使用适用于 BigQuery 的 Apache Airflow API这篇博文将引导您安装 Airflow 并为 BigQuery 创建工作流。

对于更简单的编排,您可以依赖于 Cron 作业。 这篇博文为您展示如何将查询封装为 App Engine 应用并将其作为计划的 Cron 作业来运行。

查询优化

BigQuery 每次运行查询时,都会执行完整的列扫描。BigQuery 不使用、也不支持索引。由于 BigQuery 的性能和查询费用以查询期间扫描的数据量为基础,因此请设计您的查询,以便其仅参考与查询相关的列。使用日期分区表时,请确保仅扫描相关分区。您可以通过使用基于 PARTITIONTIMEPARTITIONDATE 的分区过滤器来实现此目的。

要了解查询执行后的性能特征,请查看详细的查询计划说明。该说明分解了查询处理的阶段、每个阶段处理的输入/输出行的数量,以及每个阶段内的时序配置文件。使用说明中的结果有助于您理解和优化查询。

bigquery 结果屏幕截图

外部来源

您可以使用联合数据源对存在于 BigQuery 之外的数据运行查询,但这种方法会影响性能。因此,仅当必须在外部维护数据时才使用联合数据源。此外,您还可以使用查询联合从外部源执行 ETL 到 BigQuery。此方法允许您使用熟悉的 SQL 语法定义 ETL。

用户定义的函数

BigQuery 还支持用户定义函数 (UDF) 以用于超出 SQL 复杂性的查询。UDF 允许您扩展内置的 SQL 函数;它们接受一个值的列表(可以是数组或结构体),并返回单个值,该值也可以是数组或结构体。UDF 采用 JavaScript 编写,可以包含外部资源,例如加密或其他库。

查询共享

BigQuery 允许协作者保存查询并在团队成员之间共享查询。此功能在数据探索练习中尤其有用,或者可作为一种提高新数据集或查询模式速度的方法。如需了解详情,请参阅保存和共享查询

分析数据

本部分介绍了连接到 BigQuery 并分析数据的各种方法。要充分利用 BigQuery 作为分析引擎的优势,您应将数据存储在 BigQuery 存储空间中。但是,您的特定使用场景可能会受益于自行分析外部源或与 BigQuery 存储空间中的数据进行联接。

现成的工具

在撰写本文时,测试版 Google Data Studio 以及许多已与 BigQuery 集成的合作伙伴工具可用于从 BigQuery 绘制分析数据并构建复杂的交互式数据可视化。

如果您发现自己必须选择工具,可以在 Gartner 魔力象限报告和 G2 Crowd 的 G2 评分报告中找到全面的供应商比较。请访问我们的许多合作伙伴网站以获取 Gartner 的报告,例如 Tableau

合作伙伴徽标

自定义开发

要在 BigQuery 上构建自定义应用和平台,您可以使用提供了大多数常见编程语言版本的客户端库,也可以直接使用 BigQuery 的 REST API

如需了解具体示例,请参阅这份教程,其中使用 Python 库连接到 BigQuery 并生成自定义交互式信息中心。

第三方连接器

要从未与 BigQuery 在 API 级层原生集成的应用连接到 BigQuery,您可以使用 BigQuery JDBC 和 ODBC 驱动程序。这些驱动程序为旧版应用或无法轻松修改的应用(例如 Microsoft Excel)提供了与 BigQuery 交互的桥梁。尽管 ODBC 和 JDBC 支持使用 SQL 与 BigQuery 交互,但这些驱动程序缺乏与 API 直接交互那样的表达能力。

费用

大多数数据仓库可为组织内的多个业务实体提供服务。常见的挑战在于分析每个业务实体的运营费用。如需了解如何分割帐单以及按实际用量计算费用,请参阅使用 BigQuery 和 Data Studio 直观呈现 GCP 结算信息

BigQuery 具有三个主要费用维度:加载、存储和查询费用。本部分详细介绍每个维度。

存储数据

存储基于每秒每 MB 的价格按比例计算。

如果某个表连续 90 天没有被修改,则其会被归类为长期存储,该表的存储价格会自动下降 50%(降为每 GB 每月 $0.01)。某表被视为长期存储后,不会出现性能、耐用性、可用性方面和任何其他功能的降级。当表中的数据被修改时,BigQuery 会重置该表的计时器,并且表中的所有数据都会恢复为正常存储价格。不直接操控数据的操作(例如查询和创建视图)不会重置计时器。

如需了解详情,请参阅 BigQuery 存储价格

加载数据

您可以使用传统加载作业免费将数据加载到 BigQuery 中。加载数据后,您将按照上述方式为存储空间付费。

流式插入根据插入的数据量收费。如需了解详情,请参阅 BigQuery 存储价格下列出的流式插入的费用。

查询数据

对于查询,BigQuery 提供两种价格模式:按需付费和固定价格。

按需付费

在按需付费模式中,BigQuery 会对查询执行期间访问的数据量进行收费。由于 BigQuery 使用列式存储格式,因此只访问与查询相关的列。如果您仅按周或按月运行报告,并且仅对 1TB 以内的数据执行了查询,则您可能会发现查询费用非常低。如需详细了解查询如何收费,请参阅 BigQuery 查询价格

为确定任何给定查询预先扫描的数据量,您可以在网页界面中使用查询验证程序。在自定义开发的情况下,您可以在 API 请求中设置 dryRun 标志,并阻止 BigQuery 运行该作业。相反,您应返回有关该作业的统计信息,例如将要处理多少字节。如需了解详情,请参阅查询 API

查询 API

固定价格

希望使每月支出保持一致的客户可以选择采用固定价格。如需了解详情,请参阅 BigQuery 固定价格

后续事项

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

发送以下问题的反馈:

此网页
解决方案