通过跨云操作加载数据

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

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

配额和限制

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

价格

您需要为使用 LOAD 语句跨云传输的字节付费。如需了解价格信息,请参阅数据传输价格

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

LOADCREATE TABLE AS SELECT 语句都需要 BigQuery Omni 区域中的槽来扫描 Amazon S3 和 Blob Storage 文件以加载它们。

准备工作

如需向 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`

过滤数据

您可以在将数据转移到 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 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;

最佳做法

后续步骤