在 Google BigQuery 上執行同盟查詢

此頁面描述如何設定 Lakehouse 同盟，以在 Azure Databricks 未管理的 BigQuery 數據上執行同盟查詢。 欲了解更多湖屋聯盟資訊，請參閱 「連結外部資料庫與目錄」

要使用 Lakehouse Federation 連接您的 BigQuery 資料庫，您必須在 Azure Databricks Unity 目錄的中繼儲存庫中建立以下內容（2023 年 11 月 9 日後建立的工作空間已自動配置 Unity Catalog 中繼儲存庫）：

  • 與 BigQuery 資料庫的連線。
  • 外部目錄，該目錄將 BigQuery 資料庫鏡像至 Unity Catalog，讓您可以使用 Unity Catalog 的查詢語法和資料治理工具來管理 Azure Databricks 使用者對資料庫的存取權。

開始之前

若要在 BigQuery 上執行聯合查詢，請建立與 BigQuery 的連線，以及一個對應您的 BigQuery 資料庫的外部目錄。 接著你可以用 Azure Databricks 和 Unity Catalog 查詢和管理 BigQuery 資料。 在後續每個以任務為導向的章節中，將會指定額外的權限需求。

工作區需求：

  • 已為 Unity Catalog 啟用了工作區。

計算需求：

  • 從您的運算資源到所需 Google 端點的網路連線能力。 請參閱 「必要端點與連接性檢查」。
  • Azure Databricks 的運算必須使用 Databricks Runtime 16.1 或以上版本，以及標準或專用存取模式（過去為共享與單一使用者）。
  • SQL 倉庫必須是專業版或無伺服器型。

許可要求：

  • 要建立連線，您必須擁有附加在工作區上的 Unity Catalog 中的 CREATE CONNECTION 元資料庫的權限。
  • 若要建立外來目錄，您必須具有中繼存放區的 CREATE CATALOG 許可權，並且必須是連線的擁有者或具有該連線的 CREATE FOREIGN CATALOG 特權。

必要的端點與連線檢查

你的 Azure Databricks 運算會直接透過 HTTPS 連接到 Google API。 BigQuery 是用 Google 服務帳號金鑰進行認證，因此連接器是從計算層取得並刷新存取權杖，而非從 Azure Databricks 控制層。 因此，允許列出計算出口至下方端點即可。 如果你的運算有外站網路限制，請允許這些端點，並在建立連線或目錄前確認運算能到達它們。

所需的端點

允許從你的 Azure Databricks 計算系統向以下 Google 端點輸出 HTTPS（埠號 443）：

  • bigquery.googleapis.com： BigQuery REST API，用於元資料操作及 測試連線 檢查。
  • bigquerystorage.googleapis.com： BigQuery 儲存 API。 連接器會透過 Databricks Runtime 16.1 及以上版本中的 Storage API 讀取資料表資料，因此，若要讓聯邦查詢傳回資料，就必須使用此端點。 參見 物質化。
  • oauth2.googleapis.com 以及 accounts.google.com：用於驗證連線服務帳號的 Google 認證端點。 這些是服務帳號 JSON 中 auth_uri 和 token_uri 欄位中的主機。 你的值可能會不同，因此請允許下載檔案中出現的主機。 請參閱 建立連線中的認證備註。

如果你的運算系統使用自訂 DNS 伺服器，務必確保它能解析這些主機名稱。 在透過受限虛擬 IP 路由 Google 流量的網路上，請確認您的 *.googleapis.com 設定涵蓋 googleapis.com 端點，且 accounts.google.com 也能解析。

注意

測試連線檢查會測試 BigQuery REST API 和驗證端點，但不會透過 Storage API 讀取資料。 測試通過並不能確認 bigquerystorage.googleapis.com 可連線，而在 Databricks Runtime 16.1 及以上版本中，聯邦查詢需要滿足此條件。 請使用下方的連接性檢查來驗證每個端點。

從計算中驗證連通性

在建立連線前，先確認你的運算能覆蓋每個所需的端點。 請從已附加至通用叢集的筆記本執行下列檢查，該叢集使用的網路組態必須與您計畫從中進行聯合查詢的運算資源相同。

成功的測試僅確認可達性適用於共享該網路路徑的經典運算，例如多功能叢集、工作叢集及專業 SQL 倉庫。 無伺服器 SQL 倉庫透過另一條無伺服器出口路徑連接到 Google，因此通過叢集測試並不能確認無伺服器連線。 要控制並驗證無伺服器出口，請參閱 「什麼是無伺服器出口控制？」。

  1. 把筆記型電腦連接到使用你目標網路配置的叢集上。

  2. 執行以下 shell 指令來測試每個端點的可達性：

    %sh
for host in bigquery.googleapis.com bigquerystorage.googleapis.com oauth2.googleapis.com accounts.google.com; do
  nc -zv "$host" 443
done

  3. 確認每個主機都回報連線成功。 任何主機發生失敗或逾時，都表示到該端點的對外流量已遭封鎖，或你的運算環境無法進行 DNS 解析。

建立連線

連接會指定用來存取外部資料庫系統的路徑和認證。 若要建立連線，您可以在 Azure Databricks 筆記本或 Databricks SQL 查詢編輯器中使用目錄總管或 CREATE CONNECTION SQL 命令。

注意

您也可使用 Databricks REST API 或 Databricks CLI 來建立連線。 請參閱 的 POST /api/2.1/unity-catalog/connections，以及 的 Unity Catalog 命令。

需要的權限：具有 CREATE CONNECTION 權限的中繼存放區系統管理員或使用者。

目錄檢視器

  1. 在您的 Azure Databricks 工作區中，按兩下 [資料] 圖示。目錄。

  2. 在「目錄」窗格頂端，按一下「新增」或加號圖示「新增」圖示，然後從功能表中選取「建立連線」。

  3. 在 [連線基本資訊] 頁面的 [設定連線] 精靈中，輸入使用者易記的 [連線名稱]。

  4. 選擇 類型的 Google BigQuery，然後點擊 下一步。

  5. 在 [驗證] 頁面上，輸入 BigQuery 實例的 Google 服務帳戶密鑰 json。

    這是用來指定 BigQuery 專案並提供驗證的原始 JSON 物件。 您可以產生此 JSON 物件，並從 Google Cloud 中 [金鑰] 底下的 [服務帳戶詳細數據] 頁面下載。 服務帳戶必須具有 BigQuery 中授與的適當許可權，包括 BigQuery 使用者 和 BigQuery 數據查看器。 以下是一個範例。

    {
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "PRIVATE_KEY",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL",
  "universe_domain": "googleapis.com"
}

    注意

    Google 會在服務帳號的 JSON 中設定網址值，且可能因帳號而異。 請完全照你下載的 JSON 檔案中顯示的來使用它們。 如果你設定 Azure Databricks 的網路代理規則以存取 Google API，請同時允許 https://accounts.google.com 和 https://oauth2.googleapis.com。 關於完整 Google 端點清單及如何從計算驗證連線，請參見 「必需端點與連線檢查」。

  6. （選擇性）為您的 BigQuery 實例輸入項目識別碼 ：

    這是 BigQuery 專案的名稱，用於針對在此連線下執行的所有查詢計費。 預設為服務帳戶的專案識別碼。 服務帳戶必須在 BigQuery 中為這個專案授與適當的許可權，包括 BigQuery 使用者。 此專案中可能會建立用於儲存 BigQuery 臨時表的其他數據集。

  7. (選擇性) 新增註解。

  8. 點選 「建立連線」。

  9. 在 目錄基礎 頁面上，輸入外文目錄的名稱。 外部目錄會鏡像外部數據系統中的資料庫，讓您可以使用 Azure Databricks 和 Unity 目錄來查詢和管理該資料庫中數據的存取權。

  10. （選擇性）點擊 [測試連線] 以確認它是否正常運作。

  11. 點選 建立目錄。

  12. 在 [Access] 頁面上，選擇工作區以讓使用者能存取您建立的目錄。 您可以選取 [所有工作區都有存取權]，或按一下 [分配至工作區]，選取工作區，然後按一下 [指派]。

  13. 變更 負責人，使其能夠管理目錄中所有物件的存取權。 開始在文字框中輸入主體，然後按一下結果中傳回的主體。

  14. 將目錄中 的許可權授予 。 點擊 授與:

    1. 指定 主體 誰可以存取目錄中的物件。 開始在文字框中輸入主體，然後按一下結果中傳回的主體。
    2. 請選擇 權限預設值，以賦予每個主體。 根據預設，所有帳戶用戶都會被授與 BROWSE。
      • 從下拉功能表中選取 [數據讀取器]，以授予目錄中物件的 read 權限。
      • 從下拉功能表中選取 [數據編輯器]，以授與目錄中物件的 read 和 modify 許可權。
      • 手動選取要授與的許可權。
    3. 按一下 授與。

  15. 點選 [下一步]。

  16. 在 [元數據] 頁面上，指定標籤鍵值對。 如需詳細資訊，請參閱 將標籤應用於 Unity Catalog 的可保護對象。

  17. (選擇性) 新增註解。

  18. 點選 儲存。

SQL

在筆記本或 Databricks SQL 查詢編輯器中，執行下列命令。 將 <GoogleServiceAccountKeyJson> 取代為指定 BigQuery 專案並提供驗證的原始 JSON 物件。 您可以產生此 JSON 物件，並從 Google Cloud 中 [金鑰] 底下的 [服務帳戶詳細數據] 頁面下載。 服務帳戶需要具有 BigQuery 中授與的適當權限，包括 BigQuery 使用者和 BigQuery 資料檢視器。 如需範例 JSON 物件，請檢視此頁面上 目錄總管 索引標籤。

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson '<GoogleServiceAccountKeyJson>'
);

Databricks 建議你對像憑證這類敏感值使用 秘密 而非純文字串。 例如：

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson secret ('<secret-scope>','<secret-key-user>')
)

如需設定祕密的相關資訊，請參閱祕密管理。

建立國外目錄

注意

如果您使用 UI 來建立與數據來源的連線，則會包括外來目錄的建立，而且您可以略過此步驟。

外部目錄會鏡像外部數據系統中的資料庫，讓您可以使用 Azure Databricks 和 Unity 目錄來查詢和管理該資料庫中數據的存取權。 若要建立外部目錄，請使用已定義的數據源連線。

若要建立外部目錄，您可以在 Azure Databricks 筆記本或 Databricks SQL 查詢編輯器中使用目錄總管或 CREATE FOREIGN CATALOG。 您也可以使用 Databricks REST API 或 Databricks CLI 來建立目錄。 請參閱 POST /api/2.1/unity-catalog/catalogs 或 Unity Catalog 命令。

必要權限：中繼存放區的CREATE CATALOG權限，還有連線的所有權或對連線的CREATE FOREIGN CATALOG特權。

目錄檢視器

  1. 在您的 Azure Databricks 工作區中，按一下 [資料] 圖示以開啟目錄總管。

  2. 在 [目錄] 窗格頂端，按一下 [新增] 或 [加號] 圖示[新增] 圖示，然後從選單中選取 [新增目錄] 。

    或者，從 [快速存取] 頁面，按一下 [目錄] 按鈕，然後按一下 [建立目錄] 按鈕。

  3. （選擇性）輸入下列目錄屬性：

    數據項目標識碼：BigQuery 專案的名稱，其中包含將對應至此目錄的數據。 默認為連線層級所設定的計費專案標識碼。

  4. 請遵循在 建立目錄中關於建立外國目錄的指示。

  5. （可選）請指定以下目錄選項：

    • Materialization Dataset：一個可選的 BigQuery 資料集名稱，用於實現查詢結果。 若未指定，則會在需要時自動配置實體化資料集。 更多資訊請參見 物質化 。
    • Force materialization：是否要對針對目錄執行的每個查詢都將結果具體化。 預設值為 false。 更多資訊請參見 物質化 。
    • BIGNUMERIC Default Scale：一個可選的縮放值，用於將 BigQuery BIGNUMERIC 映射到 Spark DecimalType。 更多資訊請參見 資料型別映射 。

SQL

在筆記本或 Databricks SQL 編輯器中，執行下列 SQL 命令。 方括號內的項目為可選。 替換占位符值。

  • <catalog-name>：Azure Databricks 中目錄的名稱。
  • <connection-name>：指定數據源、路徑和存取認證的 連接物件。
  • <data-project-id>：一個可選的 BigQuery 專案 ID，用於指定包含要映射到此目錄中的資料的 BigQuery 專案。 若未指定，則使用連線上的專案 ID，接著是服務帳號的專案 ID。
  • <dataset-name>：一個可選的 BigQuery 資料集名稱，用於實現查詢結果。 若未指定，則會在需要時自動配置實體化資料集。 更多資訊請參見 物質化 。
  • <force-materialization>：一個可選的布林值。 若 true，則對目錄的每個查詢都會產生其結果。 預設值為 false。 更多資訊請參見 物質化 。
  • <scale>：一個可選的比例值 [0，38]，用於將 BigQuery BIGNUMERIC 映射到 Spark DecimalType(38, scale)。 預設值為 38。 更多資訊請參見 資料型別映射 。
CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
[OPTIONS (
  dataProjectId '<data-project-id>',
  materializationDataset '<dataset-name>',
  forceMaterialization '<force-materialization>',
  bigNumericDefaultScale '<scale>'
)];

具象化

與其他聯盟連接器不同，BigQuery 連接器使用 BigQuery 儲存 API 而非 JDBC，以提升效能。 Azure Databricks 可以直接從儲存讀取 BigQuery 的資料，或使用實體化的資料集。 直接讀取在大型掃描時效能更佳，並支援濾波與投影下壓。 Materialization 會將額外操作（限制、聚合、加入、排序）推送到 BigQuery 運算，然後再將結果串流到 Azure Databricks。

視圖與外部表格總是具象化。 其他讀取預設使用直接儲存，且不進行實體化。

如果您需要使用進階推壓功能，從大型資料集中讀取小型結果集，或是在跨區域讀取資料，請考慮啟用實體化功能。 實體化會產生額外的 BigQuery 計算費用。

若要針對外部目錄的每個查詢強制物質化，請在目錄總管中選取強制物質化，或將 forceMaterialization 目錄選項設為 true。 你不需要更新每個查詢。

forceMaterialization目錄選項在所需的運算能力下被支援，但叢集必須執行 Databricks Runtime 16.4 LTS 或以上版本。

要啟用單一查詢的物質化，請將選項設 materializationEnabled 於 true BigQuery 資料表名稱後方：

SELECT * FROM <catalog-name>.<schema-name>.<table-name>
WITH ('materializationEnabled' 'true');

預設情況下，物質化資料集會在需要時自動配置。 你可以在建立或修改外國目錄時，使用 materializationDataset 目錄選項指定自訂資料集。 如果服務帳號沒有建立資料集的權限，或你想控制臨時實體化資料表的存放位置，這很有用。 例如：

CREATE FOREIGN CATALOG my_catalog USING CONNECTION my_bq_connection
OPTIONS (materializationDataset 'my_materialization_dataset');

要更新現有目錄，請執行：

ALTER CATALOG my_catalog OPTIONS (materializationDataset 'my_materialization_dataset');

閱讀 BigQuery 外部資料表

你可以直接從工作流程查詢 BigQuery 外部資料表，包括 BigLake 和雲端儲存支援的資料表。 這些資料表會在查詢執行前自動實現，允許在不需額外設定的情況下完整存取其內容。

支援的外部資料表

支援 BigLake 和雲端儲存的外部資料表。

  • BigLake 表格會參考儲存在雲端儲存中的資料，並包含透過 BigQuery 管理的細緻存取控制。
  • 雲端儲存的外部資料表會直接使用 URI 參考檔案。

當你查詢這些資料表時，系統會將資料實體化，讓你的查詢能在內建的 BigQuery 儲存空間上執行，以提供完整的 SQL 功能支援並達到最佳效能。

欲了解更多資訊，請參閱 BigQuery 關於 BigLake 資料表 與 Cloud Storage 外部資料表的文件。

支援的推送執行

下推支援取決於是否啟用了實體化。 有些操作會自動下放到 BigQuery 運算，而有些則需要實體化。

以下下推在不經過物化的情況下支援：

  • 篩選器，下推為 BigQuery Storage API 的資料列限制（僅限簡單述詞——欄位與常值的比較、IN、IS NULL、LIKE，以及使用 AND 或 OR 將這些條件組合）。 參考下列運算元或函數的濾波器需要實體化。
  • 投影

以下額外的推壓動作在啟用實體化時可支援。 透過物質化，過濾器被編譯成 SQL，而非 BigQuery Storage API 的列限制，因此它們還能包含以下運算子與函式：

  • 限制
  • 與限制搭配使用時的偏移
  • 彙總
  • 排序，當與限制數量搭配使用時
  • 聯結 （Databricks Runtime 16.1 或更新版本）
  • 比較運算子、布林運算子、位元運算子及算術運算子（算術運算子僅在啟用 ANSI 模式時向下推）
  • 數學函數（ABS， FLOOR） — 部分支持，僅包含濾波表達式
  • 字串函數（CONCAT， UPPER， LOWER， LENGTHTRIMLTRIM， ） RTRIM— 部分支援，僅有濾波器表達式
  • Contains、Startswith、Endswith
  • 日期、時間與時間戳記函式（DATE_TRUNC以及 EXTRACT 年、季、月、日、時、分鐘）— 部分支援，僅過濾表達式
  • 其他功能（COALESCE、Cast、CASE WHENIF及陣列元素存取）— 部分支援，僅過濾表達式

不支援下列下推：

  • 視窗函數

資料類型對應

下表顯示 BigQuery 與 Spark 資料類型的對應。

BigQuery 類型 Spark 類型
BIGNUMERIC、NUMERIC DecimalType*
INT64 LongType
FLOAT64 DoubleType
ARRAY、、 GEOGRAPHY、 JSON、 STRING、 STRUCT VarcharType
BYTES BinaryType
BOOL BooleanType
DATE DateType
DATETIME TimestampNTZType，Databricks Runtime 16.4 到 17.x 版本中的 StringType 除外
TIME、TIMESTAMP TimestampType/TimestampNTZType
任何具有 REPEATED 模式的類型 ArrayType 對應的 Spark 類型***

* BigQuery BIGNUMERIC 的精確度最高可達 76 位，超過 Spark 的最大 DecimalType 精度 38 位數。 預設情況下， BIGNUMERIC 映射到 DecimalType(38, 38)。 要設定比例，請使用 bigNumericDefaultScale 目錄選項。 允許的值為 [0， 38]。 例如， bigNumericDefaultScale = '10' 映射 BIGNUMERIC 到 DecimalType(38, 10)。 BigQuery NUMERIC 對應其宣稱的精確度與規模。

** 連接器最初在 Databricks 執行環境 16.4 中使用 BigQuery Storage API。 從 Databricks 執行環境 16.4 到 17.x，儲存 API 將 BigQuery DATETIME 映射到 Spark StringType 而非 TimestampNTZType。 Databricks Runtime 18.0 會恢復 TimestampNTZType 對應。

在 BigQuery 中，具有 REPEATED 模式的欄位會對應到一個 Spark ArrayType，其中包含對應的 Spark 類型。 例如，BigQuery REPEATED STRING 欄位對應到 ArrayType(VarcharType)，而 BigQuery REPEATED INT64 欄位對應到 ArrayType(LongType)。

如果 Timestamp (預設值)，當您從 BigQuery 讀取時，BigQuery TimestampType 會對應至 Spark preferTimestampNTZ = false。 如果 Timestamp，BigQuery TimestampNTZType 會對應至 preferTimestampNTZ = true。

注意

目前不支援 BigQuery INTERVAL 欄位。 包含欄位 INTERVAL 的外部資料表在結構載入時會失敗，因此你無法描述、查詢或匯入該資料表。 沒有 INTERVAL 欄位的資料表則不受影響。

故障排除

以下章節將說明使用BigQuery連接器時常見的錯誤及其解決方法。

Error creating destination table using the following query [<query>]

常見原因：連線使用的服務帳戶沒有 BigQuery 使用者 角色。

解決方案：

  1. 將 BigQuery 使用者 角色授與連線所使用的服務帳戶。 此角色需要負責建立暫時儲存查詢結果的具體化資料集。
  2. 您必須重新執行查詢。
  • 上次更新時間：