透過跨雲端作業載入資料

BigQuery 管理員或分析師可以將 Amazon Simple Storage Service (Amazon S3) 值區或 Azure Blob 儲存空間中的資料載入 BigQuery 資料表。您可以將移轉的資料與Google Cloud 區域中的資料合併，也可以運用 BigQuery ML 等 BigQuery 功能。您也可以建立特定外部來源的具體化檢視表副本，讓 BigQuery 能夠存取該資料。

您可以透過下列方式將資料移轉至 BigQuery：

配額與限制

如要瞭解配額和限制，請參閱查詢工作配額和限制。

事前準備

如要授予 Google Cloud 讀取權限，以便載入或篩選其他雲端中的資料，請管理員建立連線並與您共用。如要瞭解如何建立連線，請參閱「連結至 Amazon S3 」或「Blob 儲存體」。

必要角色

如要取得使用跨雲端移轉載入資料所需的權限，請要求管理員授予您資料集的 BigQuery 資料編輯者 (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 儲存體檔案並載入。詳情請參閱 BigQuery Omni 定價。

如果是外部資料來源的具體化檢視區塊副本，費用也可能包括具體化檢視區塊定價。

載入和篩選選項的最佳做法

載入資料

您可以使用 LOAD DATA [INTO|OVERWRITE] 陳述式將資料載入 BigQuery。

限制

  • 連線和目的地資料集必須屬於同一個專案。 系統不支援載入跨專案的資料。
  • LOAD DATA 僅在將資料從 Amazon Simple Storage Service (Amazon S3) 或 Azure Blob 儲存體移轉至同區 BigQuery 時支援。詳情請參閱「位置」。
    • 您可以將資料從任何 US 區域轉移至 US 多區域。您也可以從任何EU區域轉移至EU多區域。

範例

範例 1

以下範例會從 Amazon S3 值區將名為 sample.parquet 的 Parquet 檔案載入 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

以下範例會將 Blob 儲存空間中前置字元為 sampled* 的 CSV 檔案，載入至 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 儲存體將資料移轉至共置的 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;

具體化檢視表副本

實體化檢視區塊副本是外部 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data Cloud 資料的副本，位於 BigQuery 資料集中，因此資料可在 BigQuery 中本機使用。這有助於避免資料輸出費用，並提升查詢效能。BigQuery 可讓您在啟用 BigLake 中繼資料快取的資料表上建立具體化檢視表，並透過 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data Cloud 資料建立檢視表。

實體化檢視區塊副本可讓您在查詢中使用 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. 確認您具備必要的身分與存取權管理 (IAM) 權限，可執行本節中的工作。

    7. 必要的角色

    如要取得執行本節工作所需的權限，請要求管理員授予您 BigQuery 管理員 (roles/bigquery.admin) 身分與存取權管理角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

    這個預先定義的角色具備執行本節工作所需的權限。如要查看確切的必要權限，請展開「必要權限」部分：

    所需權限

    如要執行本節中的工作，必須具備下列權限：

    • 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. 在「Explorer」窗格中，前往要建立具體化檢視副本的專案和資料集，然後依序點選 「View actions」(查看動作)>「Create table」(建立資料表)。

    3. 在「Create table」(建立資料表) 對話方塊的「Source」(來源) 區段中，執行下列操作：

      1. 在「Create table from」(使用下列資料建立資料表) 部分，選取「Existing table/view」(現有資料表/檢視區塊)。
      2. 在「Project」(專案) 部分，輸入來源資料表或檢視區塊所在的專案。
      3. 在「Dataset」(資料集) 部分，輸入來源資料表或檢視表所在的資料集。
      4. 在「View」(檢視畫面) 中，輸入要複製的來源資料表或檢視畫面。如果選擇檢視表，則必須是授權檢視表；否則，用於產生該檢視表的所有資料表都必須位於檢視表的資料集中。

    4. 選用：在「本機 materialized view 過時程度上限」中，輸入本機 materialized view 的 max_staleness 值。

    5. 在「Create table」(建立資料表) 對話方塊的「Destination」(目的地) 區段中，執行下列操作：

      1. 在「Project」(專案) 部分，輸入要建立具體化檢視表副本的專案。
      2. 在「Dataset」(資料集) 部分，輸入要建立具體化檢視表副本的資料集。
      3. 在「Replica materialized view name」(備用資源 materialized view 名稱) 部分，輸入備用資源的名稱。

    6. 選用：為具體化檢視副本指定標記和進階選項。如果沒有為「本機具體化檢視資料集」指定資料集，系統會在與來源資料相同的專案和區域中，自動建立名為 bq_auto_generated_local_mv_dataset 的資料集。如未指定本機具體化檢視表名稱，系統會自動在來源資料所在的專案和區域中建立具體化檢視表，並加上 bq_auto_generated_local_mv_ 前置字串。

    7. 點選「建立資料表」。

    系統會建立新的本機具體化檢視區塊 (如果未指定)，並在來源資料集中授權。然後在目的地資料集中建立 materialized view 副本。

    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 之間 (含首尾)。預設值為 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`

    建立 materialized view 副本後，複製程序會輪詢來源 materialized view 的變更，並將資料複製到 materialized view 副本，按照您在 replication_interval_seconds 或 max_staleness 選項中指定的間隔時間重新整理資料。如果在第一個回填作業完成前查詢副本，會收到 backfill in progress 錯誤。第一次複製完成後，您就可以查詢具體化檢視表副本中的資料。

    資料更新間隔

    建立 materialized view 副本後，複製程序會輪詢來源 materialized view 的變更，並將資料複製到 materialized view 副本。資料會按照您在 CREATE MATERIALIZED VIEW AS REPLICA OF 陳述式的 replication_interval_seconds 選項中指定的間隔複製。

    除了複製間隔外，來源具體化檢視區塊的重新整理頻率，以及具體化檢視區塊使用的 Amazon S3、Iceberg 或 Data Cloud 資料表的中繼資料快取重新整理頻率，也會影響具體化檢視區塊副本資料的新鮮度。

    您可以使用 Google Cloud 控制台，檢查 materialized view 副本和所依據資源的資料新鮮度：

    • 如要查看具體化檢視表副本的新鮮度，請查看具體化檢視表副本「詳細資料」窗格中的「上次修改時間」欄位。
    • 如要查看來源實體化檢視區塊的時效，請查看實體化檢視區塊「詳細資料」窗格中的「上次修改時間」欄位。
    • 如要查看來源 Amazon S3、Iceberg 或 Data Cloud 資料表的中繼資料快取更新間隔，請查看具體化檢視表「詳細資料」窗格中的「最大陳舊度」欄位。

    支援的 materialized view 副本區域

    建立具體化檢視副本時，請使用下表中的位置對應：

    來源具體化檢視區塊的位置 具體化檢視表副本的位置
    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

    具體化檢視表副本的限制

    具體化檢視表副本定價

    使用具體化檢視副本會產生運算、傳出資料傳輸和儲存空間費用。

    後續步驟