在 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 啟用了工作區。

計算需求：

  • 您的計算資源與目標資料庫系統之間的網路連接能力。 請參閱 Lakehouse 同盟的網路建議。
  • Azure Databricks 的運算必須使用 Databricks Runtime 16.1 或以上版本，以及標準或專用存取模式（過去為共享與單一使用者）。
  • SQL 倉庫必須是專業版或無伺服器型。

許可要求：

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

建立連線

連接會指定用來存取外部資料庫系統的路徑和認證。 若要建立連線，您可以在 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。

  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. 您必須重新執行查詢。
  • Last updated on