通过跨云操作加载数据

作为 BigQuery 管理员或分析师，您可以将 Amazon Simple Storage Service (Amazon S3) 存储桶或 Azure Blob Storage 中的数据加载到 BigQuery 表。您可以将转移的数据与 Google Cloud 区域中存在的数据联接，也可以利用 BigQuery ML 等 BigQuery 功能。

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

配额和限制

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

价格

您需要为使用 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 价格。

准备工作

如需向 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 角色，请参阅预定义的角色和权限。

加载数据

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

限制

  • 不支持将数据加载到包含注入时间分区的目标表中。
  • LOAD DATA 作业不会在预留上运行。这些作业会使用由 Google Cloud 管理的按需槽。
  • 连接和目标数据集必须属于同一项目。不支持跨项目加载数据。
  • 只有在您将数据从 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`

过滤数据

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

限制

  • 如果 SELECT 查询的结果超过 20 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 INT)
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;

最佳做法

后续步骤