透過 BigQuery Omni 作業載入資料

BigQuery Omni 可讓您將 Amazon Simple Storage Service (Amazon S3) bucket 和 Azure Blob 儲存體中的跨雲端資料載入 BigQuery 資料表。

您可以將外部資料匯入 BigQuery，或使用 materialized view 副本在本機複製資料，以便跨 Google Cloud區域彙整資料集，並使用 BigQuery ML。如要定期從 Amazon S3 或 Blob Storage 移轉大型檔案，請改用 BigQuery 資料移轉服務。

支援的轉移方法

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

配額與限制

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

事前準備

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

必要角色

如要取得使用 BigQuery Omni 移轉作業載入資料所需的權限，請要求管理員授予您資料集的 BigQuery 資料編輯者 (roles/bigquery.dataEditor) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

這個預先定義的角色具備必要權限，可使用 BigQuery Omni 移轉作業載入資料。如要查看確切的必要權限，請展開「Required permissions」(必要權限) 部分：

所需權限

如要使用 BigQuery Omni 移轉作業載入資料，必須具備下列權限：

  • 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 資料移轉」一節。

系統會根據使用 CREATE TABLE AS SELECT 陳述式或 INSERT INTO SELECT 陳述式在雲端之間移轉的位元組，以及運算能力，向您收取費用。

LOAD 和 CREATE TABLE AS SELECT 陳述式都需要 BigQuery Omni 區域中的運算單元，才能掃描 Amazon S3 和 Blob 儲存體檔案並載入。詳情請參閱「BigQuery Omni 定價」。

如果是外部資料來源的 materialized view 副本，費用可能也包含 materialized view 定價。

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

載入資料

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

限制

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

範例

範例 1

下列範例會從 Amazon S3 bucket 載入名為 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 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;

接著，您可以使用新建立的資料表執行 join 作業：

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;

materialized view 副本

materialized view 副本是外部 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data 360 資料在 BigQuery 資料集中的副本，因此資料可在 BigQuery 本機使用。這有助於避免資料輸出費用，並提升查詢效能。BigQuery 可讓您在 Amazon Simple Storage Service (Amazon S3)、Apache Iceberg 或 Salesforce Data 360 資料上，針對已啟用 BigLake 中繼資料快取的資料表建立 materialized view。

您可以使用 materialized view 副本，在查詢中使用 Amazon S3、Iceberg 或 Data 360 materialized view 資料，同時避免資料輸出費用，並提升查詢效能。具體做法是將 Amazon S3、Iceberg 或 Data 360 資料，複製到支援的 BigQuery 區域中的資料集，讓資料可在 BigQuery 本機使用。

事前準備

  1. 登入 Google Cloud 帳戶。如果您未曾使用過 Google Cloud，歡迎 建立帳戶，親自體驗實際使用我們產品的成效。新客戶還能獲得價值 $300 美元的免費抵免額，用於執行、測試及部署工作負載。

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

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    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.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

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

  6. 確認您具備必要的 Identity and Access Management (IAM) 權限，可執行本節中的工作。

必要的角色

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

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

所需權限

您必須具備下列權限，才能執行本節中的工作：

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

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

如要進一步瞭解 BigQuery IAM，請參閱「BigQuery IAM 角色和權限」。

準備 materialized view 副本的資料集

建立 materialized view 副本前，請務必完成下列工作：

  1. 在支援 Amazon S3 的區域建立資料集
  2. 在您於上一個步驟建立的資料集中，建立來源資料表。來源資料表可以是下列任一資料表類型：

建立 materialized view 副本

選取下列選項之一：

控制台

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往「BigQuery」

  2. 點選左側窗格中的 「Explorer」。

    醒目顯示的「Explorer」窗格按鈕。

    如果沒有看到左側窗格，請按一下 「Expand left pane」(展開左側窗格)，開啟窗格。

  3. 在「Explorer」窗格中，前往要 建立具體化檢視副本的專案和資料集，然後依序點選 「Actions」(動作) >「Create table」(建立資料表)。

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

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

  5. 選用：在「Local materialized view max staleness」(本機 materialized view 過時程度上限)，輸入本機 materialized view 的max_staleness值。

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

    1. 在「Project」(專案) 部分，輸入要建立 materialized view 副本的專案。
    2. 在「Dataset」(資料集) 部分，輸入要建立 materialized view 副本的資料集。
    3. 在「Replica materialized view name」(副本 materialized view 名稱) 部分，輸入副本名稱。

  7. 選用：為 materialized view 副本指定標記和進階選項。如未指定「Local Materialized View Dataset」(本機 materialized view 資料集) 的資料集，系統會在與來源資料相同的專案和區域中自動建立資料集，並命名為 bq_auto_generated_local_mv_dataset。如未指定「Local Materialized View Name」(本機 materialized view 名稱)，系統會自動在來源資料所在的專案和區域中建立一個名稱，並加上前置字串 bq_auto_generated_local_mv_。

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

系統會建立新的本機 materialized view (如未指定)，並在來源資料集中授權。接著，系統會在目的地資料集中建立 materialized view 副本。

SQL

  1. 在您建立的資料集中，對基礎資料表建立 materialized view。您也可以在 Amazon S3 區域中的其他資料集建立 materialized view。
  2. 在包含來源資料表的資料集上授權 materialized view，這些資料表用於建立 materialized view 的查詢。
  3. 如果您為來源資料表設定手動重新整理中繼資料快取，請執行 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系統程序，重新整理中繼資料快取。
  4. 執行 BQ.REFRESH_MATERIALIZED_VIEW 系統程序，重新整理 materialized view。

  5. 使用 CREATE MATERIALIZED VIEW AS REPLICA OF 陳述式建立 materialized view 副本：

    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：要建立 materialized view 副本的專案名稱，例如 myproject。
    • BQ_DATASET：要建立 materialized view 副本的 BigQuery 資料集名稱，例如 bq_dataset。資料集必須位於 BigQuery 區域，該區域會對應至來源 materialized view 的區域。
    • REPLICA_NAME：要建立的 materialized view 副本名稱，例如 my_mv_replica。
    • REPLICATION_INTERVAL：指定將來源 materialized view 資料複製到副本的頻率，以秒為單位。值必須介於 60 至 3,600 之間 (含 60 和 3, 600)。預設值為 300 (5 分鐘)。
    • S3_DATASET：包含來源 materialized view 的資料集名稱，例如 s3_dataset。
    • MATERIALIZED_VIEW_NAME：要複製的 materialized view 名稱，例如 my_mv。

    以下範例會在 bq_dataset 中建立名為 mv_replica 的 materialized view 副本：

    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 是否有變更，並將資料複製到 materialized view 副本。系統會按照您在 CREATE MATERIALIZED VIEW AS REPLICA OF 陳述式的 replication_interval_seconds 選項中指定的間隔複製資料。

除了複製間隔之外，materialized view 副本資料的新鮮度，也會受到來源 materialized view 的重新整理頻率，以及 materialized view 使用的 Amazon S3、Iceberg 或 Data 360 資料表中繼資料快取重新整理頻率的影響。

您可以使用 Google Cloud 控制台，查看 materialized view 副本和其依據資源的資料更新間隔：

  • 如要查看 materialized view 副本的新鮮度，請查看 materialized view 副本「Details」(詳細資料) 窗格中的「Last modified」(上次修改時間) 欄位。
  • 如要查看來源 materialized view 的更新程度，請查看 materialized view「Details」(詳細資料) 窗格中的「Last modified」(上次修改時間) 欄位。
  • 如要查看來源 Amazon S3、Iceberg 或 Data 360 資料表中繼資料快取的更新程度，請查看具體化檢視表「詳細資料」窗格中的「過時程度上限」欄位。

支援的 materialized view 副本區域

建立 materialized view 副本時，請使用下表中的位置對應：

來源 materialized view 的位置 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

materialized view 副本的限制

materialized view 副本定價

使用 materialized view 副本會產生運算、傳出資料移轉和儲存空間費用。

後續步驟