Cloud SQL 聯合式查詢

資料分析師可以使用聯合查詢，透過 BigQuery 查詢 Cloud SQL 中的資料。

BigQuery Cloud SQL 連結可讓 BigQuery 即時查詢儲存於 Cloud SQL 中的資料，而且無須複製或移動資料。在 Cloud SQL 中，查詢聯合功能支援 MySQL (第 2 代) 和 PostgreSQL 執行個體。

如要將資料複製到 BigQuery，您也可以使用 Cloud Data Fusion 或 Datastream。如要進一步瞭解如何使用 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 連線屬性：

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

追蹤 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. 在「Query」分頁中，輸入下列查詢：

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

3. 點選「執行查詢」。

   如果有可供 BigQuery 聯合查詢使用的記錄，查詢結果中會顯示類似下列的記錄清單：

   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) 角色。服務帳戶的格式如下：service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com。如需詳細操作說明，請參閱「授予服務帳戶存取權」。

後續步驟

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