通过跨云操作加载数据

作为 BigQuery 管理员或分析师，您可以将 Amazon Simple Storage Service (Amazon S3) 存储桶或 Azure Blob Storage 中的数据加载到 BigQuery 表。您可以将转移的数据与Google Cloud 区域中存在的数据联接，也可以利用 BigQuery ML 等 BigQuery 功能。 您还可以创建某些外部来源的物化视图副本，以便在 BigQuery 中使用相应数据。

您可以采用以下方法将数据传输到 BigQuery：

  • 使用 LOAD DATA 语句将数据从 Amazon S3 和 Azure Blob Storage 中的文件传输到 BigQuery 表。

  • 在将结果传输到 BigQuery 表之前，请使用 CREATE TABLE AS SELECT 语句过滤 Amazon S3 或 Blob Storage 中的文件中的数据。 如需将数据附加到目标表，请使用 INSERT INTO SELECT 语句。 数据操纵应用于引用来自 Amazon S3 或 Blob Storage 数据的外部表。

  • 在 BigQuery 数据集中创建外部 Amazon S3、Apache Iceberg 或 Salesforce Data Cloud 数据的物化视图副本，以便在 BigQuery 中本地提供数据。

配额和限制

如需了解配额和限制，请参阅查询作业配额和限制。

准备工作

如需向 Google Cloud 提供加载或过滤其他云中的数据所需的读取权限，请要求您的管理员创建一个连接并与您共享。如需了解如何创建连接，请参阅连接到 Amazon S3 或 Blob Storage。

所需角色

如需获得使用跨云转移作业加载数据所需的权限，请让您的管理员为您授予数据集的 BigQuery Data Editor (roles/bigquery.dataEditor) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

此预定义角色可提供使用跨云转移作业加载数据所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

如需使用跨云转移作业加载数据，您需要拥有以下权限：

  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create
  • bigquery.connections.use

您也可以使用自定义角色或其他预定义角色来获取这些权限。

如需详细了解 BigQuery 中的 IAM 角色，请参阅 BigQuery IAM 角色和权限。

价格

您需要为使用 LOAD 语句跨云传输的字节付费。如需了解价格信息，请参阅 BigQuery Omni 价格中的 Omni Cross Cloud Data Transfer 部分。

您需要为使用 CREATE TABLE AS SELECT 语句或INSERT INTO SELECT 语句跨云转移的字节以及计算容量计费。

LOAD 和 CREATE TABLE AS SELECT 语句都需要 BigQuery Omni 区域中的槽来扫描 Amazon S3 和 Blob Storage 文件以加载它们。 如需了解详情，请参阅 BigQuery Omni 价格。

对于外部数据源的物化视图副本，费用还可能包括物化视图价格。

加载和过滤选项的最佳实践

加载数据

您可以使用 LOAD DATA [INTO|OVERWRITE] 语句将数据加载到 BigQuery。

限制

  • 连接和目标数据集必须属于同一项目。不支持跨项目加载数据。
  • 只有在您将数据从 Amazon Simple Storage Service (Amazon S3) 或 Azure Blob Storage 转移到共置 BigQuery 区域时，系统才支持 LOAD DATA。如需了解详情，请参阅位置。
    • 您可以将数据从任何 US 区域转移到 US 多区域。还可以从任何 EU 区域转移到 EU 多区域。

示例

示例 1

以下示例将名为 sample.parquet 的 parquet 文件从 Amazon S3 存储桶加载到具有自动检测架构的 test_parquet 表中：

LOAD DATA INTO mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

示例 2

以下示例将前缀为 sampled* 的 CSV 文件从 Blob Storage 加载到具有预定义列分区（按时间划分）的 test_csv 表中：

LOAD DATA INTO mydataset.test_csv (Number INT64, Name STRING, Time DATE)
  PARTITION BY Time
  FROM FILES (
    format = 'CSV', uris = ['azure://test.blob.core.windows.net/container/sampled*'],
    skip_leading_rows=1
  )
  WITH CONNECTION `azure-eastus2.test-connection`

示例 3

以下示例会使用自动检测架构的名为 sample.parquet 的文件中的数据覆盖现有表 test_parquet：

LOAD DATA OVERWRITE mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

过滤数据

您可以使用 CREATE TABLE AS SELECT 语句和 INSERT INTO SELECT 语句在将数据转移到 BigQuery 之前过滤数据。

限制

  • 如果 SELECT 查询的结果超过 60 GiB 逻辑字节，则查询会失败。系统不会创建该表，也不会传输数据。如需了解如何减小扫描数据的大小，请参阅减少查询中处理的数据量。

  • 不支持临时表。

  • 不支持传输已知二进制 (WKB) 地理空间数据格式。

  • INSERT INTO SELECT 语句不支持将数据转移到聚簇表。

  • 在 INSERT INTO SELECT 语句中，如果目标表与 SELECT 查询中的源表相同，则 INSERT INTO SELECT 语句不会修改目标表中的任何行。系统不会修改目标表，因为 BigQuery 无法跨区域读取数据。

  • 只有在您将数据从 Amazon S3 或 Blob Storage 转移到共置 BigQuery 区域时，系统才支持 CREATE TABLE AS SELECT 和 INSERT INTO SELECT。如需了解详情，请参阅位置。

    • 您可以将数据从任何 US 区域转移到 US 多区域。还可以从任何 EU 区域转移到 EU 多区域。

示例

示例 1

假设您有一个名为 myawsdataset.orders 的 BigLake 表，该表引用来自 Amazon S3 的数据。您希望将该表中的数据转移到美国多区域中的 BigQuery 表 myotherdataset.shipments。

首先，显示关于 myawsdataset.orders 表的信息：

    bq show myawsdataset.orders;

输出类似于以下内容：

  Last modified             Schema              Type     Total URIs   Expiration
----------------- -------------------------- ---------- ------------ -----------
  31 Oct 17:40:28   |- l_orderkey: integer     EXTERNAL   1
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_linenumber: integer
                    |- l_returnflag: string
                    |- l_linestatus: string
                    |- l_commitdate: date

接下来，显示关于 myotherdataset.shipments 表的信息：

  bq show myotherdataset.shipments

输出类似于以下内容：为简化输出，省略了某些列。

  Last modified             Schema             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Total Logical
 ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ ---------------
  31 Oct 17:34:31   |- l_orderkey: integer      3086653      210767042                                                         210767042
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_commitdate: date
                    |- l_shipdate: date
                    |- l_receiptdate: date
                    |- l_shipinstruct: string
                    |- l_shipmode: string

现在，使用 CREATE TABLE AS SELECT 语句，您可以选择性地将数据加载到位于美国多区域位置的 myotherdataset.orders 表：

CREATE OR REPLACE TABLE
  myotherdataset.orders
  PARTITION BY DATE_TRUNC(l_commitdate, YEAR) AS
SELECT
  *
FROM
  myawsdataset.orders
WHERE
  EXTRACT(YEAR FROM l_commitdate) = 1992;

然后，您可以对新创建的表执行联接操作：

SELECT
  orders.l_orderkey,
  orders.l_orderkey,
  orders.l_suppkey,
  orders.l_commitdate,
  orders.l_returnflag,
  shipments.l_shipmode,
  shipments.l_shipinstruct
FROM
  myotherdataset.shipments
JOIN
  `myotherdataset.orders` as orders
ON
  orders.l_orderkey = shipments.l_orderkey
AND orders.l_partkey = shipments.l_partkey
AND orders.l_suppkey = shipments.l_suppkey
WHERE orders.l_returnflag = 'R'; -- 'R' means refunded.

当有新数据可用时，使用 INSERT INTO SELECT 语句将 1993 年的数据附加到目标表中：

INSERT INTO
   myotherdataset.orders
 SELECT
   *
 FROM
   myawsdataset.orders
 WHERE
   EXTRACT(YEAR FROM l_commitdate) = 1993;

示例 2

以下示例会将数据插入注入时间分区表中：

CREATE TABLE
 mydataset.orders(id String, numeric_id INT64)
PARTITION BY _PARTITIONDATE;

创建分区表后，您可以将数据插入注入时间分区表中：

INSERT INTO
 mydataset.orders(
   _PARTITIONTIME,
   id,
   numeric_id)
SELECT
 TIMESTAMP("2023-01-01"),
 id,
 numeric_id,
FROM
 mydataset.ordersof23
WHERE
 numeric_id > 4000000;

物化视图副本

物化视图副本是 BigQuery 数据集中外部 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data Cloud 数据的复制，以便在 BigQuery 中本地提供数据。这有助于您避免数据传出费用并提高查询性能。BigQuery 可让您基于 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data Cloud 数据在启用了 BigLake 元数据缓存的表上创建物化视图。

物化视图副本可让您在查询中使用 Amazon S3、Iceberg 或 Data Cloud 物化视图数据，同时避免数据出站流量费用并提高查询性能。为此，物化视图副本会将 Amazon S3、Iceberg 或 Data Cloud 数据复制到受支持的 BigQuery 区域中的数据集，以便在 BigQuery 中本地提供数据。

准备工作

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Verify that billing is enabled for your Google Cloud project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Verify that billing is enabled for your Google Cloud project.

  6. 确保您拥有必要的 Identity and Access Management (IAM) 权限，以便执行本部分中的任务。

    7. 所需的角色

    如需获得执行本部分中任务所需的权限，请让您的管理员为您授予 BigQuery Admin (roles/bigquery.admin) IAM 角色。如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

    此预定义角色包含执行本部分中的任务所需的权限。如需查看所需的确切权限，请展开所需权限部分：

    所需权限

    如需执行本部分中的任务，您需要拥有以下权限：

    • bigquery.tables.create
    • bigquery.tables.get
    • bigquery.tables.getData
    • bigquery.tables.replicateData
    • bigquery.jobs.create

    您也可以使用自定义角色或其他预定义角色来获取这些权限。

    如需详细了解 BigQuery IAM，请参阅 BigQuery IAM 角色和权限。

    为物化视图副本准备数据集

    在创建物化视图副本之前，您必须完成以下任务：

    1. 在支持 Amazon S3 的区域中创建数据集
    2. 在您在上一步中创建的数据集中创建一个来源表。该来源表可以是以下任意表类型：

    创建物化视图副本

    从下列选项中选择一项：

    控制台

    1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

      转到 BigQuery

    2. 在探索器窗格中，找到要创建视图副本的项目和数据集，然后依次点击 查看操作 > 创建表。

    3. 在创建表对话框的来源部分中，执行以下操作：

      1. 对于基于以下数据源创建表，选择现有表/视图。
      2. 对于项目，请输入来源表或视图所在的项目。
      3. 在数据集部分，输入来源表或视图所在的数据集。
      4. 对于视图，请输入要复制的来源表或视图。如果您选择视图，则该视图必须是已获授权的视图；如果不是，则用于生成该视图的所有表必须位于该视图的数据集中。

    4. 可选：对于本地物化视图最长过时期限，请为本地物化视图输入 max_staleness 值。

    5. 在创建表对话框的目标部分，执行以下操作：

      1. 对于项目，请输入要在其中创建物化视图副本的项目。
      2. 在数据集部分，输入要在其中创建物化视图副本的数据集。
      3. 对于副本物化视图名称，请为副本输入一个名称。

    6. 可选：为物化视图副本指定标记和高级选项。如果您未为本地物化视图数据集指定数据集，系统会在与来源数据相同的项目和区域中自动创建一个数据集，并将其命名为 bq_auto_generated_local_mv_dataset。如果您未为本地物化视图名称指定名称，系统会在与来源数据相同的项目和区域中自动创建一个名称，并为其指定前缀 bq_auto_generated_local_mv_。

    7. 点击创建表。

    系统会在源数据集中创建新的本地物化视图（如果未指定）并对其进行授权。然后，系统会在目标数据集中创建物化视图副本。

    SQL

    1. 在您创建的数据集中，对基表创建一个物化视图。您还可以在 Amazon S3 区域中的其他数据集中创建物化视图。
    2. 对包含创建物化视图的查询中使用的来源表的数据集授权物化视图。
    3. 如果您为来源表配置了手动元数据缓存刷新，请运行 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系统过程以刷新元数据缓存。
    4. 运行 BQ.REFRESH_MATERIALIZED_VIEW 系统过程以刷新物化视图。

    5. 使用 CREATE MATERIALIZED VIEW AS REPLICA OF 语句创建物化视图副本：

      CREATE MATERIALIZED VIEW PROJECT_ID.BQ_DATASET.REPLICA_NAME
OPTIONS(replication_interval_seconds=REPLICATION_INTERVAL)
AS REPLICA OF PROJECT_ID.S3_DATASET.MATERIALIZED_VIEW_NAME;

      请替换以下内容：

      • PROJECT_ID：您要在其中创建物化视图副本的项目的名称，例如 myproject。
      • BQ_DATASET：您要在其中创建物化视图副本的 BigQuery 数据集的名称，例如 bq_dataset。该数据集必须位于映射到来源物化视图的区域的 BigQuery 区域中。
      • REPLICA_NAME：您要创建的物化视图副本的名称，例如 my_mv_replica。
      • REPLICATION_INTERVAL：指定将数据从来源物化视图复制到副本的频率（以秒为单位）。必须是一个介于 60 到 3,600 的值（包括 60 和 3,600）。默认值为 300（5 分钟）。
      • S3_DATASET：包含来源物化视图的数据集的名称，例如 s3_dataset。
      • MATERIALIZED_VIEW_NAME：要复制的物化视图的名称，例如 my_mv。

      以下示例会在 bq_dataset 中创建一个名为 mv_replica 的物化视图副本：

      CREATE MATERIALIZED VIEW `myproject.bq_dataset.mv_replica`
OPTIONS(
replication_interval_seconds=600
)
AS REPLICA OF `myproject.s3_dataset.my_s3_mv`

    创建物化视图副本后，复制过程将轮询来源物化视图以查看是否存在更改，并将数据复制到物化视图副本，按照您在 replication_interval_seconds 或 max_staleness 选项中指定的时间间隔刷新数据。如果您在第一次回填完成之前查询副本，则会收到 backfill in progress 错误。您可以在首次复制完成后查询物化视图副本中的数据。

    数据新鲜度

    创建物化视图副本后，复制过程将轮询来源物化视图以查看是否发生更改，并将数据复制到物化视图副本。数据会按照您在 CREATE MATERIALIZED VIEW AS REPLICA OF 语句的 replication_interval_seconds 选项中指定的时间间隔进行复制。

    除了复制间隔时间之外，物化视图副本数据的新鲜度还会受到来源物化视图的刷新频率以及物化视图使用的 Amazon S3、Iceberg 或 Data Cloud 表的元数据缓存的刷新频率影响。

    您可以使用 Google Cloud 控制台查看物化视图副本及其所基于的资源的数据新鲜度：

    • 如需了解物化视图副本的新鲜度，请查看物化视图副本的详细信息窗格中的上次修改时间字段。
    • 如需了解来源物化视图的新鲜度，请查看物化视图的详细信息窗格中的上次修改时间字段。
    • 如需了解来源 Amazon S3、Iceberg 或 Data Cloud 表元数据缓存的新鲜度，请查看物化视图的详细信息窗格中的最长过时期限字段。

    支持的物化视图副本区域

    创建具体化视图副本时，请使用下表中的位置映射：

    来源物化视图的位置 物化视图副本的位置
    aws-us-east-1 US 多区域，或是以下任何区域：
    • northamerica-northeast1
    • northamerica-northeast2
    • us-central1
    • us-east1
    • us-east4
    • us-east5
    • us-south1
    • us-west1
    • us-west2
    • us-west3
    • us-west4
    aws-us-west-2 US 多区域，或是以下任何区域：
    • northamerica-northeast1
    • northamerica-northeast2
    • us-central1
    • us-east1
    • us-east4
    • us-east5
    • us-south1
    • us-west1
    • us-west2
    • us-west3
    • us-west4
    aws-eu-west-1 EU 多区域，或是以下任何区域：
    • europe-central2
    • europe-north1
    • europe-southwest1
    • europe-west1
    • europe-west2
    • europe-west3
    • europe-west4
    • europe-west6
    • europe-west8
    • europe-west9
    • europe-west10
    aws-ap-northeast-2 以下任何区域：
    • asia-east1
    • asia-east2
    • asia-northeast1
    • asia-northeast2
    • asia-northeast3
    • asia-south1
    • asia-south2
    • asia-southeast1
    aws-ap-southeast-2 以下任何区域：
    • australia-southeast1
    • australia-southeast2

    物化视图副本的限制

    物化视图副本价格

    使用物化视图副本会产生计算、出站数据传输和存储费用。

    后续步骤