通过跨云操作加载数据

作为 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 语句跨云转移的字节以及计算容量计费。

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

准备工作

如需向 Google Cloud 提供对其他云中文件的读取权限,请要求您的管理员创建一个连接并与您共享。如需了解如何创建连接,请参阅连接到 Amazon S3Blob 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`

过滤数据

您可以使用 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 SELECTINSERT 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;

最佳做法

后续步骤