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

更新日期:2021 年 6 月

本文档介绍如何将 BigQuery 用作数据仓库。本文将常见数据仓库概念与 BigQuery 中的概念一一对应,并介绍了如何在 BigQuery 中执行标准数据仓储任务。本文档适用于管理数据仓库和大数据系统的人员。

服务模型比较

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

数据仓库 BigQuery
数据仓库 BigQuery 服务取代了传统数据仓库的典型硬件设置。也就是说,它可以充当组织中所有分析数据的集体家园。
数据集市 数据集是可以按业务范围或给定分析领域划分的表的集合。每个数据集都与一个 Google Cloud 项目相关联。
数据湖 您的数据湖可能包含 Cloud StorageGoogle 云端硬盘中的文件或 BigtableCloud SQL 中的事务数据。BigQuery 可以定义架构并直接对作为联合数据源的外部数据发出查询。BigQuery Storage API 提供高带宽并行读取,并与 SparkPandas 等常见处理框架兼容。

BigQuery 资源

BigQuery 具有层次结构。下图展示了其级别:

项目 A:数据集,包括表、视图、脚本/函数。项目 B:作业,包括查询、加载、复制、导出。

项目

您分配和使用的任何 Google Cloud 资源都必须属于一个项目。项目是您使用 Google Cloud 构建的组织。在 BigQuery 环境中,项目是所有 BigQuery 资源的容器。由于 BigQuery 将存储和计算分离,因此存储和查询数据的项目可能会有所不同。

数据集

数据集是用于组织 BigQuery 表和视图的顶层容器。它们经常映射到标准关系型数据库和数据仓库中的架构。

数据集的范围限定在您的 Cloud 项目中。如果您要从命令行、SQL 查询或代码中引用表,请按以下方式引用表:

project.dataset.table

数据集与位置相关联。数据集位置如下所示:

  • 单区域:特定的地理位置,例如伦敦。
  • 多区域:至少包含两个地理位置的大型地理区域,如美国。

您只能在创建数据集时为其设置位置。查询可以包含来自同一位置的不同数据集的表或视图。

使用多个范围(项目、数据集、表和位置)可帮助您在逻辑上和地理上组织信息。

BigQuery 是保存数据的行列结构。每个表由描述列名、数据类型和其他信息的架构进行定义。您可以在创建表时指定表的架构。或者,您也可以创建一个没有架构的表,并在首次使用数据填充表的查询作业或加载作业中指定架构。BigQuery 具有以下类型的表:

  • 原生表:原生 BigQuery 存储支持的表。
  • 外部表:BigQuery 外部存储支持的表。
  • 视图:由 SQL 查询定义的虚拟表。

如需了解详情,请参阅存储管理

作业

作业是 BigQuery 代表您执行的操作,包括加载数据、导出数据、查询数据或复制数据。作业未关联到存储数据的同一项目。但是,作业可以执行的位置会链接到数据集位置。例如,如果将 Cloud Storage 存储桶中的数据加载到位于新加坡的 BigQuery 数据集中,则单区域或多区域 Cloud Storage 存储桶也必须位于新加坡。或者,如果您的数据集位于欧洲区域,则无法从其他区域(如美国)查询。这可确保满足您的数据存放区域要求。

预配和系统大小调整

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

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

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

存储管理

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

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

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

维护

BigQuery 是一种全代管式服务,这意味着 BigQuery 工程团队会为您处理更新和维护。升级通常不需要停机或系统性能。

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

备份与恢复

对于数据库管理员来说,管理备份和可用性一直是一项复杂而昂贵的任务。需要额外的授权和硬件会大幅增加费用。BigQuery 解决了服务级层的备份和灾难恢复问题。通过维护表的完整更改历史记录,BigQuery 允许您通过在 FROM 子句中使用表装饰器SYSTEM_TIME AS OF 来查询数据的时间点快照。您可以轻松还原更改,而无需从备份请求恢复。当明确删除表时,其历史记录将在 7 天后清空。此外,cp 命令还提供即时区域表快照。

BigQuery 数据集可以是单区域数据集,也可以是多区域数据集。对于区域数据集(例如位于 us-central1 区域的数据集),系统不会在该区域外部维护数据集的副本。如果您认为某个区域以外缺少备份,给您的业务带来风险,则可以使用 BigQuery Data Transfer Service 创建和安排跨区域副本。对于位于欧洲等大型地理区域的多区域数据集,副本会自动存储在另一个 Google Cloud 区域中。

如果某个区域出现故障,部分最新数据可能会丢失。如需了解详情,请参阅有关可用性和耐用性的 BigQuery 文档。

管理工作流

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

组织数据集

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

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

授予权限

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

Cloud Identity 是 Google Cloud 上的内置中央身份提供商,用于启用用户身份验证。它是 Identity and Access Management (IAM) 的一部分。除了身份验证之外,IAM 还为您提供集中控制,以授权具有 BigQuery 及其数据集的特定权限的身份。您可以使用预定义角色创建自定义角色来控制访问权限。对于非人类用户对 BigQuery 资源的访问权限,您可以创建服务帐号并为其分配所需的角色。此方法的一个示例用例是访问计划的数据加载脚本。

操作数据仓库的一个重要方面是允许不同用户组对相同数据进行共享但受控制的访问。例如,财务、人力资源和市场营销部门均有权访问相同的表,但访问权限级别不同。传统的数据仓储工具通过强制实现行级安全性来实现这一点。您可以通过定义已获授权的视图行级权限在 BigQuery 中实现相同的结果。 对于特定列中包含敏感数据的表,您可以使用数据政策标记和 IAM 角色来强制执行列级别安全性。如需详细了解数据治理,请参阅将数据仓库迁移到 BigQuery:数据治理

初始配置

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

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

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

对于企业的新用户和经验丰富的用户来说,数据发现是主要关注点。能够找到所需的数据非常重要。保护敏感数据并授予对数据的访问权限也同样重要。您可以使用 Data Catalog 自动提供元数据搜索和数据泄露防护等功能。如需详细了解数据发现功能,请参阅数据发现

管理工作负载和并发

本部分介绍可用于管理工作负载的控件、您可以执行的并发查询数量以及作业调度。

服务配额

服务配额用于在使用 BigQuery 时保持一致的服务质量,并记录在 BigQuery 配额政策中。每个配额限制对所有使用方都有一个默认值。例如,默认情况下,您可以将并发查询数上限设置为 100。如果您需要增加或减少此数量,可以使用配额替换值。

自定义配额

如果您有多个 BigQuery 项目和用户,可以通过申请自定义配额指定每天处理的查询数据量限额来管理费用。每日配额会在美国太平洋时间零点重置。

查询优先级排序和调度

BigQuery 提供两种类型的查询优先级,即交互式查询和批量查询。默认情况下,BigQuery 运行交互式查询,也就是说查询会尽快执行。请注意,交互式查询会计入并发速率限制配额。批量查询会先行排队,并在有闲置资源时执行,通常在几分钟之内。如果 BigQuery 没有在 24 小时内启动查询,则会将作业优先级更改为交互式。批量查询不会计入并发速率限制配额。

如果并发查询使用的槽数超过项目或预留可用的槽数,则 BigQuery 会实施公平调度算法。鉴于 BigQuery 的运行速度和规模,许多传统的工作负载问题(例如为不同的工作负载维护单独的队列)并不适用。如果需要明确安排查询的优先级,您可以将敏感工作负载归入具有单独预留的项目。

监控和审核

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

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

BigQuery 还提供了 INFORMATION_SCHEMA 只读视图,可用于访问 BigQuery 资源(如数据集、表和作业)的元数据。这些视图可用于各种用途,例如跟踪表的到期日期或者查询的槽利用率

管理数据

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

设计架构

通过 BigQuery,您可以在将数据加载到表中时或创建空表时指定表架构。BigQuery 支持标准 SQL 数据类型,包括整数等简单类型,以及 ARRAYSTRUCT 等更复杂的类型。

BigQuery 支持基于星型架构雪花型架构的传统数据模型。在这些模型中,事实表与维度表相联接。BigQuery 还支持 INNER[FULL|RIGHT|LEFT] OUTERCROSS JOIN 运算。

在某些情况下,您可能需要使用嵌套和重复字段对数据进行反规范化。为此,您可以结合使用 ARRAYSTRUCT 数据类型来定义架构。

分区表

分区表根据分区列的值划分为多个细分。如果查询指定分区列的过滤条件,则只扫描相应的细分。这样做可以加快查询执行速度并降低查询执行费用。BigQuery 表可通过以下方式进行分区:

  • 提取时间:BigQuery 会自动将数据加载到基于日期的每日分区中,这些分区反映了数据的提取或到达时间
  • 基于列的分区:根据指定列的值对表进行分区。您可以对列使用以下类型:
    • 时间单位列分区:可以按 DATEDATETIMETIMESTAMP 列对表进行分区。
      • DATE:允许每天、每月或每年粒度分区。
      • TIMESTAMP DATETIME:允许具有任何时间单位粒度类型的分区,包括 HOUR
    • 整数范围:表按照整数列进行分区。

创建表时,您需要启用分区功能。此外,您可以为分区中的数据指定过期时间。插入到分区表中的新数据会在插入时写入原始分区。如需控制将数据加载到哪个分区,您可以在加载作业中指定特定分区。

聚簇表

聚簇表根据一个或多个指定列进行整理。BigQuery 支持对分区表和非分区表进行聚簇。聚簇将表细分划分为按照聚簇字段排序的块。对于过滤聚簇列数据的查询,系统会减少扫描的数据量并改进查询性能。由于扫描的数据量只能在运行时确定,因此事先无法知道执行查询的确切费用。

BigQuery 会自动在后台对新插入的数据重新进行聚簇。自动重新聚簇对查询容量或价格没有影响。

以下流程图概述了分区、聚簇和分区以及表中聚簇的最佳使用场景。

下表中重复选项的流程图。

上述流程图概述了以下选项:

使用场景 建议
您使用的是按需价格,并且在运行查询之前需要严格的费用保证。 分区表
对表进行分区后,细分大小小于 1 GB。 聚簇表
超出 BigQuery 限制,您需要大量分区 聚簇表
数据中频繁的变更会修改大量分区。 聚簇表
您经常会运行查询以过滤某些固定列的数据。 分区和聚簇

具体化视图

具体化视图是预计算视图,可定期缓存查询结果以提高性能和效率。在 BigQuery 中,具体化视图始终与基表保持一致,包括 BigQuery 流式表。具体化视图有助于在数据仓库中创建聚合表。

地理空间数据

数据仓库通常包含位置数据。从提供更高效的供应链物流系统到规划风力发电厂提供的 a 风,这些数据有多种用途。借助 BigQuery GIS,您可以使用标准 SQL 地理位置函数分析和直观呈现 BigQuery 中的地理空间数据。BigQuery 提供了 GEOGRAPHY 数据类型,可让您以 GeoJSON已知二进制 (WKB)已知文本 (WKT) 格式加载空间数据。BigQuery 还提供了几个地理函数,可用于解析、转换和操作 GIS 数据。如需详细了解如何使用地理空间数据,请参阅使用 BigQuery GIS

加载数据

BigQuery 提供批量和流式模式来加载数据。它还允许使用 BigQuery Data Transfer Service 直接从某些 SaaS 应用导入数据。批量加载可让您加载大量数据,而不会影响查询性能,且无需额外费用。对于加载用于欺诈检测的更改数据等用例,需要实时提供数据,您可以将数据流式插入到 BigQuery

批量加载

对于批量加载,数据文件会暂存在 Cloud Storage 存储桶中,然后使用加载作业导入您的 BigQuery 表。BigQuery 支持许多开放式格式,例如 CSV、JSON、AvroORCParquet。BigQuery 还内置了对 DatastoreFirestore 的支持。

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

您可以通过 BigQuery 控制台启动加载作业。如需自动执行此过程,您可以设置 Cloud Functions 以侦听与到达给定存储桶中的新文件相关联的 Cloud Storage 事件并启动 BigQuery 加载作业。数据流水线通常用于执行在数据仓库外部运行的提取、转换和加载 (ETL) 过程。下图显示了流水线中的事件流。

BigQuery ETL 过程。

ETL 过程的替代方案是提取、加载和转换 (ELT) 过程。如下图所示,在 ELT 过程中,数据首先加载到数据仓库中,然后使用 SQL 操作转换为所需的架构。

BigQuery ELT 流水线。

您可以使用在 Dataflow 上运行的 ETL 流水线,通过使用 Apache Beam SDK 中提供的 BigQuery I/O 连接器将数据自动加载到 BigQuery。或者,您还可以使用通过 Apache Spark 构建的流水线,通过 Spark BigQuery 连接器将数据自动加载到 BigQuery。

流式插入

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

通过使用 Apache Beam SDK 中提供的 BigQuery I/O 连接器的 STREAMING_INSERTS 方法,还可以使用在 Dataflow 上运行的流水线(例如 Pub/SubApache Kafka)从消息传递系统流式传输事件数据。

随着企业开始使用更多的 Google Cloud 服务,他们通常会选择直接在 BigtableCloud SQLCloud Spanner 中捕获源数据,并使用 Dataflow 以批量或流方式提取、转换和加载数据到 BigQuery。下图显示了如何使用 Dataflow 设置批处理和流式 ETL 流水线。

使用 Dataflow 设置的批量和流式 ETL 流水线。

从 SaaS 应用导入

BigQuery Data Transfer Service 可让您从 Google 应用源(例如 Google Ads、Campaign Manager、Google Ad Manager 和 YouTube)导入数据。它还支持外部数据源(例如 Amazon S3)和数据仓库(例如 TeradataAmazon Redshift)。您还可以使用我们的合作伙伴提供的连接器连接到我们 Google Cloud Marketplace 中的多个其他系统。

处理变更

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

BigQuery 中的所有表修改(包括 DML 操作、针对目标表的查询和加载作业)均符合 ACID。因此,修改表不需要停机时间。但是,在最近刷新的数据可用于分析之前,您的内部流程可能需要经过测试和验证。此外,由于 DML 操作在分析数据库中效率较低,因此您可能更希望对其进行批处理。您可以应用大多数众所周知的技术来处理数据更改。本部分对一些已知的挑战和解决方案进行了扩展。

滑动时间窗口

与数据湖不同,传统数据仓库仅在固定的时间段内保留数据,例如过去五年。在每个更新周期中,新数据将添加到仓库中,最旧的数据将被舍弃,从而保持固定的时长。通常,此概念被用来解决旧技术的局限性。

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

更改架构

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

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

缓慢变化维度

规范化数据架构通过隔离维度表中的更改使缓慢变化维度 (SCD) 的影响降到最低。它通常优于反规范化架构,其中 SCD 可能引起对扁平事实表的大范围更新。

对于缓慢变化维度的所有情况,没有共同的解决方案。了解更改的性质并应用最相关的解决方案或解决方案组合非常重要。本部分的其余内容概述了一些解决方案以及如何将它们应用于 SCD 类型。

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 盎司 美容养生
化妆品

如果属性位于规范化维度表中,则更改会很孤立。您只需更新维度表中受影响的行。

如需很少更改特定行,您可以使用 UPDATE DML 语句。

update mydataset.dimension_table set PRD_CATEGORY="cosmetics" where PRD_SK="123"

在某些情况下,您可能需要定期将维度与操作的主表同步。一种常见模式是定期将操作数据库的转储合并到 BigQuery 维度表。您可以将新数据加载到临时表中,也可以创建一个指向这些数据的外部表

维度表:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC 超赞的保湿霜 - 100 盎司 美容养生
124 PQR 很棒的乳液 - 50 盎司 美容养生

临时表:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC 超赞的保湿霜 - 100 盎司 化妆品
124 PQR 很棒的乳液 - 50 盎司 化妆品
125 XYZ 极致 T 恤 - xl 服饰

现在,您可以运行合并查询以更新维度表,然后删除临时表。

MERGE my-dataset.dimension_table as MAIN using
my-dataset.temporary_table as TEMP
on MAIN.PRD_SK = TEMP.PRD_SK
when matched then
UPDATE SET
MAIN.PRD_CATEGORY = TEMP.PRD_CATEGORY
when not matched then
INSERT VALUES(TEMP.PRD_SK, TEMP. PRD_ID, TEMP. PRD_SK, TEMP.
PRD_CATEGORY)

结果维度表:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC 超赞的保湿霜 - 100 盎司 美容养生
化妆品
124 PQR 很棒的乳液 - 50 盎司 美容养生
化妆品
125 XYZ 极致 T 恤 - xl 服饰

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

您可以在此表上创建视图或具体化视图,并在分析查询中使用它。

create view products_current as
select PRD_SK, PRD_ID, PRD_DESC, PRD_CATEGORY, PRD_START_DATE
from my-dataset.dimension_table
where END_DATE IS NULL

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

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

SCD 类型 3:通过添加列来维护历史记录

此方法使用单独的列来保留有限的历史记录,以跟踪有限的历史数据。由于 BigQuery 支持嵌套和重复字段,因此可以按照 START_DATE 值的升序使用数组类型来维护同一列中的历史记录。与 SCD 类型 2 一样,您可以在表之上创建视图或具体化视图,以便更轻松地进行查询。

基表:

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

创建视图,以选择 PRD_CATEGORY 数组中最后一个商品类别的名称:

create view my-dataset.products_current as
select PRD_SK, PRD_ID, PRD_DESC,
PRD_CATEGORY.ordinal[array_length(PRD_CATEGORY)] as PRD_CAT
from my-dataset.dimension_table;

视图:

PRD_SK PRD_ID PRD_DESC PRD_CAT
123 ABC 超赞的保湿霜 - 100 盎司 化妆品

近乎实时的复制

如果您需要从操作数据库中获取近乎实时的更新数据,则可以使用使用变更数据捕获 (CDC) 将数据库复制到 BigQuery

查询数据

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

BigQuery Web 控制台功能。

用户定义的函数

BigQuery 还支持用户定义函数 (UDF),用于在 SQL 语句中表达函数不切实际的查询。通过 UDF,您可以扩展内置的 SQL 函数;它们接受一个值列表(该值可以是 ARRAYSTRUCT 类型),并返回一个值(该值也可以是 ARRAYSTRUCT 类型)。UDF 可以使用标准 SQL 和 JavaScript 编写。在 JavaScript UDF 中,您可以添加外部资源,例如加密或其他库。我们建议您使用标准 SQL UDF,因为它们比 JavaScript UDF 性能更高。如需查看 Google Cloud 专业服务团队构建和维护的一些常用 UDF 的示例,请参阅 bigquery-utils GitHub 页面。

脚本和存储过程

企业用户经常在数据仓库内执行复杂的逻辑。BigQuery 脚本允许您编写标准 SQL 脚本,这些脚本提供变量和控制语句的使用,并在 BigQuery 数据仓库中执行它们。存储过程可让您保存这些脚本,以便将来的使用场景中在 BigQuery 中运行。与视图类似,您还可以在组织中与其他人共享存储过程,同时保留该过程的一个规范版本。您可以在 bigquery-utils GitHub 页面上找到示例脚本和存储过程。

自动查询

根据计划或事件自动执行查询,并缓存结果供以后使用,是一种常见的做法。您可以使用 BigQuery 计划查询定期运行数据定义语言 (DDL)数据操纵语言 (DML)语句。

对于简单的编排(例如从 Cloud Storage 存储桶自动加载作业),您可以使用 Cloud Storage 触发器来运行 Cloud Functions 函数,后者运行 BigQuery 作业。对于计划作业,您可以从 Cloud Scheduler 触发 Cloud Functions 函数。对于更复杂的工作流,您可以使用 Cloud Composer 通过 Airflow BigQuery 运算符编排其他自动化活动。

BigQuery Storage API

企业通常需要从 BigQuery 读取大量数据。借助 BigQuery Storage API,您可以读取序列化结构化数据的并行数据流。此方法可帮助您克服读取分页行和将数据导出到 Cloud Storage 的开销限制。

使用 Apache BeamApache Spark 构建的现有流水线几乎不需要额外设置即可使用 BigQuery Storage API。

查询优化

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

查询输入和输出。

减少数据扫描

BigQuery 不使用、也不支持索引。每次运行查询时,它都会执行整列扫描。由于 BigQuery 的性能和查询费用以查询期间扫描的数据量为基础,因此我们建议您设计查询,使其仅查询与查询相关的列。使用分区表时,请仅扫描相关分区。通过使用基于分区列的过滤条件,您可以避免不必要的扫描。如果您的查询经常针对特定列进行过滤,请考虑聚簇表。如果频繁运行聚合查询以进一步处理,请考虑将查询具体化。此方法可减少计算要求以及要扫描的数据量。

降低计算要求

我们建议您不要使用 JavaScript 用户定义函数。如果可能的话,请改用标准 SQL UDF。加快查询的另一种方法是使用近似聚合,例如 APPROX_COUNT_DISTINCT 而不是 COUNT(DISTINCT)

提升联接性能

企业通常需要联接多个表,尤其是在数据仓库具有星型架构或雪花型架构时。事实表通常大于维度表。在雪花型架构中,由于维度经过规范化,因此维度表可能更小。最佳做法是从左侧的事实表开始,将其与右侧的较小维度表按大小降序连接。如果 JOIN 左侧有一个大型表,而 JOIN 右侧有一个小表,则系统会创建一个广播联接。广播联接将较小表中的所有数据发送到处理较大表的每个槽。

如需了解详情,请参阅将数据仓库迁移到 BigQuery:性能优化

外部来源

如果您希望将经常更改的小型操作表与 BigQuery 表联接,BigQuery 支持外部数据源,例如:Cloud BigtableCloud SQL此方法可确保不需要在每次更新数据时重新加载数据。

由于 BigQuery 支持查询 Avro、Parquet 和 ORC 等多种格式的数据,因此您可以使用它一次转换数据并将其从 Google 云端硬盘Cloud Storage 加载到 BigQuery。您还可以从 BigQuery 的默认 Hive 分区布局查询 Cloud Storage 中现有数据湖的数据。例如,企业数据湖中的表以 Parquet 格式存储在 Cloud Storage 存储桶中,且具有以下 Hive 分区模式:

gs://my_bucket/my_table/{dt:DATE}/{val:STRING}

为了进行查询,用户可以使用 BigQuery 中的 Hive 分区模式创建外部表。当用户对此表运行查询时,BigQuery 会遵循 Hive 分区架构,并减少扫描的数据。

在分阶段将数据仓库迁移到 BigQuery 时,这非常有用,因为您可以在不移动数据的情况下将所有查询迁移到 BigQuery。

如需详细了解 BigQuery 中的外部数据源,请参阅外部数据源简介

查询共享

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

分析数据

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

现成的工具

Google 数据洞察Looker 以及许多已与合作伙伴集成的合作伙伴工具 BigQuery 可用于从 BigQuery 绘制分析数据并构建复杂的交互式数据可视化。如果您熟悉电子表格界面,则可以使用 Connected SheetsSheets 访问、分析、可视化和共享 BigQuery 中的数据。

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

自定义开发

如需在 BigQuery 上构建自定义应用和平台,您可以使用适用于大多数常见编程语言的客户端库,或者 BigQuery REST API。如需查看示例,请参阅使用 Bokeh 和 BigQuery 创建自定义交互式信息中心,后者使用 Python 库连接到 BigQuery 并生成自定义交互式信息中心。

第三方连接器

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

费用

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

BigQuery 具有三个主要费用维度:加载、存储和查询费用。拥有 BigQuery 数据集的项目按标准每月存储费率计费。启动查询或加载的项目将支付计算费用。本部分详细介绍每个维度。

存储数据

存储定价按 Mbps 比例计算。

如果某个表连续 90 天没有被修改,则其会被归类为长期存储,该表的存储价格会自动下降 50%(降为每 GB 每月 $0.01)。某表被视为长期存储后,不会出现性能、耐用性、可用性和任何其他功能方面的降级。

当表中的数据被修改时,BigQuery 会重置该表的计时器,并且表中的所有数据都会恢复为正常存储价格。不直接操控数据的操作(例如查询和创建视图)不会重置计时器。对于分区表,同一模型应用于各个分区细分。

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

加载数据

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

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

查询数据

对于查询,BigQuery 提供两种价格模式:使用预留方式按需结算和固定费率。

按需价格

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

如需详细了解查询如何收费,请参阅 BigQuery 查询价格

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

查询费用。

BigQuery Reservations 预留功能

如需获得更一致的每月费用,您可以选择通过 BigQuery Reservations 预留功能启用固定价格。使用此选项,您可以为您的组织购买特定数量的 BigQuery 槽的容量承诺,并将其分配给特定项目。

您可以月度或年度承诺。您还可以签订灵活槽承诺,购买至少 60 秒的额外槽。购买槽后,您可以将其分配给不同的存储桶,称为预留。预留创建命名的槽分配。如要使用购买的槽,请将项目、文件夹或组织分配给预留。资源层次结构中的每个层级都会沿用上一层级中的分配,除非您替换此设置。

您可以使用 BigQuery Reservations 预留功能来隔离您针对各种工作负载、团队或部门的承诺容量,具体方法是创建更多预留并将项目分配到这些预留。

如下图所示,在第一个示例场景中,两种工作负载类型(数据科学 (DS) 和商业智能 (BI))都需要 1000 个槽。在第二个示例场景中,需要 1000 个槽才能每小时运行 ELT 作业 15 分钟。

槽预留示例。

在第一种情况下,DS 作业和 BI 作业会使用承诺和预留,如下所示:

  • 创建 1000 个槽的月度或年度承诺。
  • 创建 DS 500 预留,并将所有相关的 Google Cloud 项目分配到 DS 预留。
  • 创建 500 个槽的 BI 预留,并将连接到 BI 工具的项目分配到 BI 预留。

在第二种方案中,对于 ELT 作业,您可以使用承诺和预留,如下所示:

  • 创建 1000 个槽的灵活槽预留。
  • 创建一个具有 1000 个槽的 ELT 预留,并将相关项目分配到 ELT 预留。
  • ELT 作业完成后,您将删除分配、ELT 预留和承诺。

后续步骤