使用排程查詢建立資料表快照

本文件說明如何使用執行排定 DDL 查詢的服務帳戶，每月建立資料表快照。本文件將逐步說明以下範例：

  1. 在 PROJECT 專案中，建立名為 snapshot-bot 的服務帳戶。
  2. 授予 snapshot-bot 服務帳戶所需的權限，讓該帳戶可擷取 DATASET 資料集中 TABLE 資料表的資料表快照，並將資料表快照儲存在 BACKUP 資料集中。
  3. 編寫查詢，為 TABLE 資料表建立每月快照，並將這些快照放入 BACKUP 資料集。由於您無法覆寫現有的資料表快照，因此資料表快照的名稱不得重複。為達到這項目的，查詢會將目前日期附加到資料表快照名稱中，例如 TABLE_20220521。表格快照會在 40 天後失效。
  4. 安排 snapshot-bot 服務帳戶，在每個月的第一天執行查詢。

本文件適用於熟悉 BigQuery 和 BigQuery 資料表快照的使用者。

權限與角色

本節說明建立服務帳戶和排定查詢時所需的身分與存取權管理 (IAM) 權限，以及授予這些權限的預先定義 IAM 角色。

權限

如要使用服務帳戶，您必須具備下列權限：

權限 資源 資源類型
iam.serviceAccounts.* PROJECT 專案

如要排定查詢，您必須具備下列權限：

權限 資源 資源類型
bigquery.jobs.create PROJECT 專案

角色

下列預先定義的角色可提供服務帳戶所需的權限：

角色 資源 資源類型
下列任一項：

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT 專案

下列是預先定義的 BigQuery 角色，可提供排定查詢所需的權限：

角色 資源 資源類型
下列任一項：

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin` 		PROJECT 專案

建立 snapshot-bot 服務帳戶

請按照下列步驟建立 snapshot-bot 服務帳戶，並授予該帳戶在 PROJECT 專案中執行查詢所需的權限：

主控台

  1. 前往 Google Cloud 控制台的「Service accounts」(服務帳戶) 頁面：

    前往「Service accounts」(服務帳戶)

  2. 選取 PROJECT 專案。

  3. 建立 snapshot-bot 服務帳戶：

    1. 按一下「建立服務帳戶」。

    2. 在「Service account name」欄位中輸入「snapshot-bot」。

    3. 按一下「建立並繼續」。

  4. 將執行 BigQuery 工作所需的權限，授予服務帳戶：

    1. 在「授予此服務帳戶專案存取權」部分，選取「BigQuery 使用者」角色。

    2. 按一下 [完成]。

BigQuery 會使用電子郵件地址 snapshot-bot@PROJECT.iam.gserviceaccount.com 建立服務帳戶。

如要確認 BigQuery 是否已使用您指定的權限建立服務帳戶，請按照下列步驟操作：

主控台

確認 BigQuery 已建立服務帳戶：

  1. 前往 Google Cloud 控制台的「Service accounts」(服務帳戶) 頁面：

    前往「Service Accounts」(服務帳戶)

  2. 選取 PROJECT 專案。

  3. 按一下「snapshot-bot@PROJECT.iam.gserviceaccount.com」。

  4. 確認「服務帳戶狀態」訊息表示服務帳戶已啟用。

請確認 BigQuery 已授予服務帳戶執行查詢所需的權限：

  1. 在 Google Cloud 控制台中，前往「管理資源」頁面：

    前往「管理資源」

  2. 按一下「PROJECT」。

  3. 按一下「顯示資訊面板」。

  4. 在「Permissions」分頁中，展開「BigQuery User」節點。

  5. 確認「snapshot-bot」服務帳戶是否已列出。

將權限授予服務帳戶

本節說明如何授予 snapshot-bot 服務帳戶所需的權限，讓該帳戶能夠在 BACKUP 資料集中建立 DATASET.TABLE 資料表的快照。

取得基礎資料表快照的權限

如要將 DATASET.TABLE 資料表的快照功能所需的權限，授予 snapshot-bot 服務帳戶，請按照下列步驟操作：

主控台

  1. 在 Google Cloud 控制台開啟「BigQuery」BigQuery頁面。

    前往 BigQuery

  2. 在「Explorer」窗格中，展開 PROJECT 專案節點。

  3. 展開 DATASET 資料集節點。

  4. 選取「TABLE」資料表。

  5. 按一下「分享」。「Share」窗格隨即開啟。

  6. 按一下「新增主體」。「授予存取權」窗格隨即開啟。

  7. 在「新增主體」中，輸入服務帳戶的電子郵件地址：snapshot-bot@PROJECT.iam.gserviceaccount.com。

  8. 在「請選擇角色」下拉式選單中，選取「BigQuery 資料編輯者」角色。

  9. 按一下 [儲存]。

  10. 在「Share」窗格中，展開「BigQuery Data Editor」節點，確認「snapshot-bot@PROJECT.iam.gserviceaccount.com」服務帳戶是否列出。

  11. 按一下 [關閉]。

bq

  1. 在 Google Cloud 控制台中啟用 Cloud Shell：

    啟用 Cloud Shell

  2. 輸入下列 bq add-iam-policy-binding 指令：

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery 確認已新增新的政策繫結。

在目的地資料集中建立資料表的權限

將 snapshot-bot 服務帳戶所需的權限授予 BACKUP 資料集，以便在資料集中建立資料表快照，如下所示：

主控台

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

    前往 BigQuery

  2. 在「Explorer」窗格中，展開 PROJECT 專案節點。

  3. 按一下 BACKUP 資料集節點的選單，然後選取「開啟」。

  4. 按一下「分享資料集」。「Dataset permissions」窗格隨即開啟。

  5. 在「Add members」欄位中輸入服務帳戶的電子郵件地址：snapshot-bot@PROJECT.iam.gserviceaccount.com。

  6. 在「請選擇角色」下拉式選單中，選取「BigQuery 資料擁有者」角色。

  7. 按一下「新增」。

  8. 在「資料集權限」窗格中，確認「snapshot-bot@PROJECT.iam.gserviceaccount.com」服務帳戶是否列於「BigQuery 資料擁有者」節點下方。

  9. 按一下 [完成]。

您的 snapshot-bot 服務帳戶現在具備下列資源的以下 IAM 角色：

角色 資源 資源類型 目的
BigQuery 資料編輯者 PROJECT:DATASET.TABLE 資料表 擷取 TABLE 資料表的快照。
BigQuery 資料擁有者 PROJECT:BACKUP 資料集 在 BACKUP 資料集中建立及刪除資料表快照。
BigQuery 使用者 PROJECT 專案 執行用於建立資料表快照的排定查詢。

這些角色會提供 snapshot-bot 服務帳戶執行查詢所需的權限，以便建立 DATASET.TABLE 資料表的資料表快照，並將資料表快照放入 BACKUP 資料集。

編寫多個陳述式的查詢

本節說明如何編寫多陳述式查詢，藉由使用 CREATE SNAPSHOT TABLE DDL 陳述式建立 DATASET.TABLE 資料表的資料表快照。快照會儲存在 BACKUP 資料集中，並在一天後過期。

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

排定每月查詢

排定查詢要在每個月的第一天上午 5:00 執行，如下所示：

bq

  1. 在 Google Cloud 控制台中啟用 Cloud Shell：

    啟用 Cloud Shell

  2. 輸入下列 bq query 指令：

    bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

  3. BigQuery 會排定查詢的執行時間。

bq 指令列工具指令中的多陳述式查詢與您在 Google Cloud 主控台中執行的查詢有以下差異：

  • bq 指令列工具查詢會使用 @run_date，而非 current_date()。在排程查詢中，@run_date 參數包含目前日期。不過，互動式查詢不支援 @run_date 參數。您可以在排定互動式查詢時，使用 current_date() 而非 @run_date 來測試查詢。
  • 同樣地，bq 指令列工具查詢會使用 @run_time 而非 current_timestamp()，因為互動式查詢不支援 @run_time 參數，但可使用 current_timestamp() 取代 @run_time 來測試互動式查詢。
  • bq 指令列工具查詢會使用斜線和雙引號 \"，而非單引號 '，因為單引號用於括住查詢。

設定服務帳戶以執行排程查詢

系統目前已排定使用您的憑證執行查詢。更新排程查詢，以便使用 snapshot-bot 服務帳戶憑證執行，如下所示：

  1. 執行 bq ls 指令，取得排程查詢工作的身分：

    bq ls --transfer_config=true --transfer_location=us

    輸出看起來類似以下內容：

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. 使用 name 欄位中的 ID，執行下列 bq update 指令：

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell 確認已成功更新排定查詢。

查看工作

本節說明如何確認查詢是否已正確排定、如何查看查詢執行時是否發生任何錯誤，以及如何確認是否已建立每月快照。

查看排程查詢

如要確認 BigQuery 是否已排定每月表格快照查詢，請按照下列步驟操作：

主控台

  1. 在 Google Cloud 控制台中，前往「Scheduled queries」(已排定的查詢) 頁面：

    前往「已排定的查詢」

  2. 按一下「TABLE 資料表的月度快照」。

  3. 按一下「設定」。

  4. 確認「查詢字串」包含查詢，且查詢已排定在每月第一天執行。

查看排程查詢的執行記錄

排定查詢執行完畢後，您可以查看查詢是否順利執行，方法如下：

主控台

  1. 在 Google Cloud 控制台中，前往「Scheduled queries」(已排定的查詢) 頁面：

    前往「已排定的查詢」

  2. 按一下查詢說明「TABLE 資料表的月度快照」。

  3. 按一下「執行記錄」。

您可以查看查詢執行的日期和時間、執行是否成功，以及如果失敗，則會顯示發生的錯誤。如要查看特定執行作業的詳細資料，請在「執行記錄」表格中按一下該作業的資料列。「Run details」窗格會顯示其他詳細資料。

查看資料表快照

如要確認是否已建立資料表快照，請按照下列步驟操作：

主控台

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

    前往 BigQuery

  2. 在「Explorer」窗格中，開啟 BACKUP 資料集，並確認已建立 TABLE_YYYYMMDD 快照，其中 YYYYMMDD 是每個月的第一天。

    例如：

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

後續步驟

  • 如要進一步瞭解資料表快照，請參閱「使用資料表快照」一文。
  • 如要進一步瞭解如何排程查詢，請參閱「排程查詢」。
  • 如要進一步瞭解 Google Cloud 服務帳戶，請參閱「服務帳戶」一文。