数据加载简介

本文档简要介绍了如何将数据加载到 BigQuery 中。

概览

您可以通过多种方式将数据提取到 BigQuery 中：

  • 批量加载一组数据记录。
  • 流式传输个别记录或批量记录。
  • 使用查询生成新数据，并将结果附加到表中或覆盖表中的数据。
  • 使用第三方应用或服务。

批量加载

使用批量加载时，可通过单个批量操作将源数据加载到 BigQuery 表中。例如，数据源可以是 CSV 文件、外部数据库或一组日志文件。传统的提取、转换和加载 (ETL) 作业属于此类别。

在 BigQuery 中进行批量加载的选项包括：

  • 加载作业。通过创建加载作业，从 Cloud Storage 或本地文件加载数据。记录可以是 Avro、CSV、JSON、ORC 或 Parquet 格式。
  • SQL。LOAD DATA SQL 语句会将数据从一个或多个文件加载到新表或现有表中。您可以使用 LOAD DATA 语句加载 Avro、CSV、JSON、ORC 或 Parquet 文件。
  • BigQuery Data Transfer Service。使用 BigQuery Data Transfer Service 自动从 Google 软件即服务 (SaaS) 应用或第三方应用和服务加载数据。
  • BigQuery Storage Write API。使用 Storage Write API，您可以批量处理任意数量的记录，并在单个原子操作中提交这些记录。如果提交操作失败，您可以安全地重试操作。与 BigQuery 加载作业不同，Storage Write API 不需要将数据暂存到中间存储（如 Cloud Storage）。
  • 其他代管式服务。使用其他代管式服务从外部数据存储区中导出数据并将数据导入到 BigQuery 中。例如，您可以从 Firestore 导出文件中加载数据。

选择批量加载方法时，大多数基于文件的格式都应使用加载作业或 LOAD DATA SQL 语句来批量加载数据。其他服务通常应使用 BigQuery Data Transfer Service 或从 Google 服务导出数据。

批量加载可以作为一次性操作执行，也可以按周期性计划执行。例如，您可以执行以下操作：

  • 您可以按计划运行 BigQuery Data Transfer Service 传输作业。
  • 您可以使用 Cloud Composer 等编排服务来安排加载作业。
  • 您可以使用 Cron 作业来按计划加载数据。

流式插入

通过流式插入，您可以持续实时发送小批量数据，以便在数据到达时可以进行查询。用于将数据流式插入到 BigQuery 的选项包括：

  • Storage Write API。Storage Write API 支持具有正好一次传送语义的高吞吐量流式注入。
  • Dataflow。将 Dataflow 与 Apache Beam SDK 搭配使用来设置写入 BigQuery 的流式流水线。 如需了解详情，请参阅 Apache Beam 文档中的 BigQuery I/O 连接器以及“从 Pub/Sub 流式插入到 BigQuery”教程。
  • Datastream。 Datastream 使用 BigQuery 变更数据捕获功能和 Storage Write API 将数据和架构更新从运营数据库直接复制到 BigQuery。 请查看此快速入门，了解从 Cloud SQL for PostgreSQL 数据库复制到 BigQuery 的示例。
  • BigQuery Connector for SAP。借助 BigQuery Connector for SAP，您可以近乎实时地将 SAP 数据直接复制到 BigQuery 中。如需了解详情，请参阅 BigQuery Connector for SAP 计划指南。
  • Pub/Sub。 Pub/Sub 是一种消息传递服务，可用于协调流式分析和数据集成流水线。 您可以使用 BigQuery 订阅将消息直接写入现有 BigQuery 表。

生成的数据

您可以使用 SQL 生成数据，并将结果存储在 BigQuery 中用于生成数据的选项包括：

  • 使用数据操纵语言 (DML) 语句执行批量插入现有表中或将查询结果存储在新表中。

  • 使用 CREATE TABLE ... AS 语句，根据查询结果创建一个新表。

  • 运行查询并将结果保存到表中。您可以将结果附加到现有表，也可以写入新表。如需了解详情，请参阅写入查询结果。

第三方应用

一些第三方应用和服务提供可将数据提取到 BigQuery 中的连接器。如何配置和管理提取流水线的详细信息取决于应用。 例如，要将数据从外部来源加载到 BigQuery 的存储空间，您可以使用 Informatica Data Loader 或 Fivetran Data Pipelines。如需了解详情，请参阅使用第三方应用加载数据。

选择数据提取方法

选择数据提取方法时，需要考虑以下注意事项。

数据源。数据来源或数据格式可决定批量加载或流式传输是否更易于实现和维护。请考虑以下几点：

  • 如果 BigQuery Data Transfer Service 支持数据源，则将数据直接传输到 BigQuery 可能是最容易实现的解决方案。

  • 对于 BigQuery Data Transfer Service 不支持的第三方来源的数据，请将数据转换为批量加载支持的格式，并改用该方法。

  • 如果您的数据来自 Spark 或 Hadoop，请考虑使用 BigQuery 连接器来简化数据提取。

  • 对于本地文件，请考虑批量加载作业，特别是当 BigQuery 支持这种文件格式时，无需执行转换或数据清理步骤。

  • 对于应用事件或日志流等应用数据，实时流式传输数据可能更容易，而不是实现批量加载。

缓慢更改的数据与快速更改的数据。如果您需要近乎实时地提取和分析数据，请考虑流式传输数据。借助流式传输，每条记录一旦到达即可查询数据。应避免使用 DML 语句来提交大量单独的行更新或插入。对于频繁更新的数据，通常最好是流式传输更改日志并使用视图来获取最新结果。另一个选项是使用 Cloud SQL 作为 Online Transaction Processing (OLTP) 数据库，并使用联合查询来联接 BigQuery 中的数据。

如果源数据更改缓慢或您不需要持续更新的结果，请考虑使用加载作业。例如，如果您使用数据运行每日或每小时报告，则加载作业的费用可能较低，并且使用的系统资源数量较少。

另一种情况是到达频率较低或为响应事件而产生的数据。在这种情况下，请考虑使用 Dataflow 流式传输数据，或使用 Cloud Functions 调用流式传输 API 来响应触发器。

解决方案的可靠性。BigQuery 具有服务等级协议 (SLA)。但是，您还需要考虑所实现的特定解决方案的可靠性。请考虑以下几点：

  • 对于 JSON 或 CSV 等弱类型格式，错误数据可能会使整个加载作业失败。考虑是否需要在加载之前执行数据清理步骤，并考虑如何响应错误。另外，请考虑使用强类型格式，例如 Avro、ORC 或 Parquet。
  • 定期加载作业需要使用 Cloud Composer、cron 或其他工具进行安排。安排组件可能会成为解决方案中的故障点。
  • 借助流式传输，您可以检查每条记录是否成功，并快速报告错误。考虑将失败的消息写入未处理的消息队列，以供日后分析和处理。如需详细了解 BigQuery 流式传输错误，请参阅排查流式传输问题。
  • 流式传输和加载作业受配额限制。如需了解如何处理配额错误，请参阅排查 BigQuery 配额错误。
  • 第三方解决方案在配置、可靠性、排序保证及其他因素方面可能存在差异，因此在采用某个解决方案之前，请考虑这些因素。

延迟时间。考虑加载的数据量以及您需要数据可用的快慢。流式传输提供了让数据可用于分析的最低延迟。定期加载作业的延迟时间较长，因为须在每个加载作业都完成后，新数据才可用。

默认情况下，加载作业使用槽的共享池。加载作业可能会以待处理状态等待到槽可用，尤其是在您加载非常大量的数据时。如果这样会导致不可接受的等待时间，您可以购买专用槽，而不是使用共享的槽池。如需了解详情，请参阅预留简介。

外部数据源的查询性能可能低于存储在 BigQuery 中的数据的查询性能。如果最大限度地减少查询延迟时间很重要，则建议将数据加载到 BigQuery 中。

数据注入格式。根据以下因素选择数据提取格式：

  • 架构支持。Avro、ORC、Parquet 和 Firestore 导出文件是自描述格式。BigQuery 会根据源数据自动创建表架构。对于 JSON 和 CSV 数据，您可以提供明确的架构，也可以使用架构自动检测。

  • 平展型数据或嵌套和重复的字段。 Avro、CSV、JSON、ORC 和 Parquet 都支持平展型数据。Avro、JSON、ORC、Parquet 和 Firestore 导出文件还支持包含嵌套重复字段的数据。表示分层数据时，嵌套的重复数据会很有用。 此外，在加载数据时，嵌套和重复字段可以减少数据重复情况。

  • 嵌入式换行符。从 JSON 文件加载数据时，各行内容必须以换行符分隔。BigQuery 要求以换行符分隔的 JSON 文件必须每行包含一条记录。

  • 编码。BigQuery 支持采用 UTF-8 编码的嵌套数据或重复数据以及平展型数据。此外，它还支持采用 ISO-8859-1 编码的平展型数据，但这仅适用于 CSV 文件。

加载嵌套和重复数据

您可以使用以下数据格式将数据加载到嵌套和重复字段中：

  • Avro
  • JSON（以换行符分隔）
  • ORC
  • Parquet
  • Datastore 导出文件
  • Firestore 导出文件

如需了解如何在加载数据时指定架构中的嵌套重复字段，请参阅指定嵌套重复的字段。

从其他 Google 服务加载数据

某些 Google 服务使用计划查询、导出或转移将数据导出到 BigQuery。如需详细了解支持数据导出到 BigQuery 的服务，请参阅从 Google 服务加载数据。

其他 Google 服务支持从 BigQuery Data Transfer Service 启动的数据导出。 如需详细了解支持由 BigQuery Data Transfer Service 启动的导出的服务，请参阅 BigQuery Data Transfer Service。

配额

如需了解配额，请参阅以下部分：

加载数据的替代方案

在以下情况下，您无需在运行查询之前加载数据：

公共数据集
公共数据集是存储在 BigQuery 中并公开共享的数据集。如需了解详情，请参阅 BigQuery 公共数据集。
共享数据集
您可与他人共享存储在 BigQuery 中的数据集。如果有人与您共享了某个数据集，您可以在不加载数据的情况下对该数据集运行查询。
外部数据源
BigQuery 可以对某些形式的外部数据运行查询，而无需将数据加载到 BigQuery 存储空间中。通过此方法，您可以充分利用 BigQuery 的分析功能，而无需移动存储在其他位置的数据。如需了解这种方法的优点和限制，请参阅外部数据源。
日志文件
Cloud Logging 提供了将日志文件导出到 BigQuery 的选项。如需了解详情，请参阅配置和管理接收器。

监控加载作业的使用情况

您可以通过以下两种方式监控加载作业的使用情况：

  • 使用 Cloud Monitoring。如需了解详情，请参阅 BigQuery 指标。具体而言，您可以监控上传到特定表的数据量和行数。 如果加载作业将数据上传到特定表，则可以使用代理来监控加载作业上传数据量。

  • 使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT。您可以使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT 视图获取每个表每天的加载作业数量。

用例示例

以下示例根据您的使用场景介绍了使用方法，以及如何将这些方法与其他数据分析解决方案结合使用。

使用 Storage Write API 流式传输数据

假设有流水线处理来自端点日志的事件数据。这些事件是连续生成的，并且需要尽快在 BigQuery 中进行查询。由于数据新鲜度对于此用例至关重要，因此 Storage Write API 是将数据注入到 BigQuery 的最佳选择。为使这些端点尽量精简，建议的架构会将这些事件发送到 Pub/Sub，之后由 Dataflow 的流式处理流水线直接将这些事件流式传输到 BigQuery。

此架构存在的主要可靠性问题是如何处理无法将记录插入 BigQuery 的情况。如果每条记录都很重要并且不能丢失，那么您将需要先缓冲数据，然后再尝试插入。在上述建议的架构中，Pub/Sub 可以充当缓冲区的角色并提供消息保留功能。Dataflow 流水线应配置为通过截断的指数退避算法重试 BigQuery 流式插入。Pub/Sub 作为缓冲区的容量耗尽（例如在 BigQuery 长时间不可用或网络故障等情况下）后，客户端就必须保留数据，并且客户端需要一种机制来确保在可用性恢复后能够继续插入保留的记录。如需详细了解如何处理这种情况，请参阅 Google Pub/Sub 可靠性指南博文。

需要处理的另一个失败情况是中毒记录。中毒记录是 BigQuery 拒绝的记录（因为记录无法插入且不可重试），或者是在达到重试次数上限后未成功插入的记录。这两种类型的记录都应由 Dataflow 流水线存储在“死信队列”中，以进一步调查。

如果需要“正好一次”语义，请在已提交类型下创建写入流，并使用客户端提供的记录偏移量。这样可以避免重复，因为只有在偏移量值与下一个附加偏移量匹配时才会执行写入操作。如果未提供偏移量，则表示记录会附加到流的当前末尾处，重试失败的记录可能会导致记录在流中多次出现。

如果不需要“正好一次”保证，写入默认流可实现更高的吞吐量，也不会计入创建写入数据流的配额限制。

估算网络的吞吐量，并提前确保您有足够的配额来满足吞吐量。

如果您的工作负载以非常不均匀的速率生成或处理数据，请尝试消除客户端上的所有负载峰值，并以恒定的吞吐量流式传输到 BigQuery。这样可以简化容量规划。但如果无法做到这一点，请确保准备好应对在短暂高峰期吞吐量超过配额时出现的 429（资源耗尽）错误。

批量数据处理

假设有一个夜间批处理流水线，需要在固定的截止时间之前完成。在此截止时间之前，需要获取数据以供其他批处理进一步处理，从而生成要发送到监管机构的报告。此用例在金融等受监管的行业中很常见。

使用加载作业批量加载数据是此用例的正确方法，因为如果可以满足截止时间要求，就可以不必担心延迟时间。确保 Cloud Storage 存储桶满足将数据加载到 BigQuery 数据集中的位置要求。

BigQuery 加载作业的结果是原子化的；要么已插入所有记录，要么不插入任何记录。最佳实践是在单个加载作业中插入所有数据时，使用 JobConfigurationLoad 资源的 WRITE_TRUNCATE 处置方式创建新表。这一点在重试失败的加载作业时非常重要，因为客户端可能无法区分失败的作业和由于将成功状态传回客户端等原因而导致的失败情况。

假设要注入的数据已成功复制到 Cloud Storage，使用指数退避算法进行重试足以解决注入故障。

建议夜间批量作业不要达到默认配额（每个表每天加载 1,500 次），即使重试也是如此。以增量方式加载数据时，默认配额足以每 5 分钟运行一次加载作业，并且平均每项作业有至少 1 次重试未使用配额。

后续步骤