Cloud SQL 聯合查詢

BigQuery 的聯合查詢功能可讓您即時查詢 Cloud SQL 中的資料，不必複製或移動資料。查詢聯合支援 Cloud SQL 中的 MySQL (第 2 代) 和 PostgreSQL 執行個體。

或者，您也可以使用 Cloud Data Fusion 或 Datastream，將資料複製到 BigQuery。如要進一步瞭解如何使用 Cloud Data Fusion，請參閱「將資料從 MySQL 複製到 BigQuery」。

事前準備

• 確認 BigQuery 管理員已建立 Cloud SQL 連線，並與您共用。

• 如要取得查詢 Cloud SQL 執行個體所需的權限，請要求管理員授予您專案的 BigQuery 連線使用者 (roles/bigquery.connectionUser) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

  您或許也能透過自訂角色或其他預先定義的角色，取得必要權限。

查詢資料

如要透過 GoogleSQL 查詢將聯合查詢傳送至 Cloud SQL，請使用 EXTERNAL_QUERY 函式。

假設您將客戶資料表儲存在 BigQuery，並將銷售資料表儲存在 Cloud SQL，且想在單一查詢中彙整這兩個資料表。下列範例會對名為 orders 的 Cloud SQL 資料表執行聯合查詢，並將結果與名為 mydataset.customers 的 BigQuery 資料表彙整。

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

查詢範例包括 3 個部分：

1. 透過 EXTERNAL_QUERY() 函式在作業 PostgreSQL 資料庫中執行外部查詢 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id，藉此取得每位客戶第一筆訂單的日期。
2. 依 customer_id 將外部查詢結果資料表與 BigQuery 中的客戶資料表彙整。
3. 選取顧客個人資訊和首筆訂單日期。

查看 Cloud SQL 資料表結構定義

您可以使用 EXTERNAL_QUERY() 函式來查詢 information_schema 資料表，藉此存取資料庫中繼資料。舉例來說，您可以列出資料庫中的所有資料表，或查看資料表結構定義。下列 information_schema 查詢範例可以在 MySQL 和 PostgreSQL 中運作。詳情請參閱 MySQL information_schema 資料表和 PostgreSQL information_schema 資料表。

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.columns where table_name='x';");

連線詳細資料

下表列出 Cloud SQL 連線屬性：

屬性名稱	值	說明
name	字串	連線資源名稱，格式為：project_id.location_id.connection_id。
location	字串	連線位置必須與 Cloud SQL 執行個體位置相符，或是相應管轄區的多區域。舉例來說，us-east4 中的 Cloud SQL 執行個體可以使用 US，而 europe-north1 中的 Cloud SQL 執行個體可以使用 EU。只有在這個位置執行的 BigQuery 查詢，才能使用這個連線。
friendlyName	字串	連線的顯示名稱，方便使用者辨識。
description	字串	連線說明。
cloudSql.type	字串	可以是「POSTGRES」或「MYSQL」。
cloudSql.instanceId	字串	Cloud SQL 執行個體名稱，格式通常為： Project-id:location-id:instance-id 您可以在 Cloud SQL 執行個體詳細資料頁面找到執行個體 ID。
cloudSql.database	字串	要連線的 Cloud SQL 資料庫。
cloudSql.serviceAccountId	字串	設定為存取 Cloud SQL 資料庫的服務帳戶。

下表列出 Cloud SQL 執行個體憑證的屬性：

屬性名稱	值	說明
username	字串	資料庫使用者名稱
password	字串	資料庫密碼

追蹤 BigQuery 聯合查詢

對 Cloud SQL 執行聯合查詢時，BigQuery 會在查詢中加入類似下列內容的註解：

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

如果您正在監控 MySQL 或 PostgreSQL 資料庫的查詢用量記錄，下列註解可協助您找出源自 BigQuery 的查詢。

1. 前往「Logs Explorer」頁面。

   前往「Logs Explorer」

2. 在「查詢」分頁中，輸入下列查詢：

   resource.type="cloudsql_database"
   textPayload=~"Federated query from BigQuery"
   

3. 點選「執行查詢」。

   如果有可用於 BigQuery 聯合查詢的記錄，「Query results」(查詢結果) 中會顯示類似下方的記錄清單：

   YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
   db=DATABASE, user=USER_ACCOUNT
   STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
   /* Federated query from BigQuery.
   Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
   */
   
   YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
   db=DATABASE, user=USER_ACCOUNT
   STATEMENT: SELECT "company_id", "company type_id" FROM
   (SELECT FROM company_name_table) t;
   /* Federated query from BigQuery.
   Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
   */
   

疑難排解

本節說明如何排解將聯合查詢傳送至 Cloud SQL 時可能遇到的問題。

問題：無法連線至資料庫伺服器。如果您查詢 MySQL 資料庫，可能會遇到下列錯誤：

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

或者，如果您查詢 PostgreSQL 資料庫，可能會遇到下列錯誤：

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

解決方法：請確認您使用的是有效憑證，並遵循所有必要條件，建立 Cloud SQL 連線。檢查建立 Cloud SQL 連線時自動建立的服務帳戶，是否具備 Cloud SQL 用戶端 (roles/cloudsql.client) 角色。服務帳戶的格式如下：[e-mail]。如需詳細操作說明，請參閱「授予服務帳戶存取權」。

如果 Cloud SQL 執行個體使用私人 IP 位址，請確認您在建立 Cloud SQL 執行個體時，已啟用私人路徑。這樣一來，BigQuery 就能透過私人連線存取 Cloud SQL 中的資料，並對這些資料執行查詢。

問題：無法連線至 MySQL 資料庫。對 Cloud SQL 資料執行聯合查詢時，可能會遇到下列錯誤：

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

解決方法：

如果資料庫使用者採用caching_sha2_password 驗證，就會發生這項錯誤，因為聯合查詢不支援這種驗證方式。

如果您使用的 Cloud SQL 執行個體執行 MySQL 8.0 以下版本，可以變更從 BigQuery 連線的使用者驗證外掛程式。如要變更驗證外掛程式，請在 Cloud SQL 執行個體上執行下列指令：

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

更改下列內容：

• USERNAME：BigQuery 用來驗證及連線至 MySQL 適用的 Cloud SQL 執行個體的資料庫使用者帳戶。
• PASSWORD：資料庫使用者的密碼。

如果使用的 Cloud SQL 執行個體執行 MySQL 8.4 版，則沒有可用的解決方法，因為 mysql_native_password 外掛程式已在 8.4 版中淘汰。不建議將現有資料庫從 8.4 降級至 8.0，做為正式環境工作負載的解決方案。

如果 Cloud SQL 執行個體執行的 MySQL 版本低於 8.4，只要在升級前不變更使用者的驗證外掛程式，將執行個體升級至 8.4 版就不會影響現有的資料庫連線。

只有使用 mysql_native_password 外掛程式建立的使用者 (通常是在升級至 8.4 版之前建立的使用者) 才能用於聯合查詢，因此即使 MySQL 執行個體升級至 8.4 版，您仍應使用 8.4 之前的 MySQL 版本中現有的資料庫使用者，將聯合查詢傳送至 MySQL。

限制

• 聯合查詢不支援 MySQL caching_sha2_password 外掛程式。因此，使用這個外掛程式對 MySQL 8.0 和 8.4 執行聯合查詢會失敗。詳情請參閱「疑難排解」。

後續步驟

• 瞭解聯合查詢。
• 瞭解 MySQL 至 BigQuery 資料類型對應。
• 瞭解從 PostgreSQL 到 BigQuery 的資料類型對應。
• 瞭解不支援的資料類型。