Cloud SQL 連携クエリ

データ アナリストは、連携クエリを使用して、Cloud SQL 内のデータを BigQuery からクエリできます。

BigQuery Cloud SQL 連携機能を使用すると、データのコピーや移動を行わずに、BigQuery から Cloud SQL に存在するデータのクエリをリアルタイムで実行できます。クエリ連携機能は、Cloud SQL で MySQL（第 2 世代）と PostgreSQL インスタンスの両方をサポートします。

または、データを BigQuery に複製するために、Cloud Data Fusion または Datastream を使用することもできます。Cloud Data Fusion の使用の詳細については、MySQL から BigQuery へのデータの複製をご覧ください。

始める前に

データのクエリ

GoogleSQL クエリから Cloud SQL に連携クエリを送信するには、EXTERNAL_QUERY 関数を使用します。

たとえば、販売テーブルを Cloud SQL に保存しながら BigQuery に顧客テーブルを保存し、1 つのクエリで 2 つのテーブルを結合するとします。次の例では、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. 稼働中の PostgreSQL データベースで外部クエリ SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id を実行し、EXTERNAL_QUERY() 関数によって各顧客の最初の注文日を取得します。
  2. 外部クエリの結果テーブルを BigQuery の顧客テーブルと customer_id で結合します。
  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 インスタンスのロケーションと同じです。ただし例外として Cloud SQL us-central1 は BigQuery US にマップし、Cloud SQL europe-west1 は BigQuery EU にマップします。
friendlyName 文字列 接続のわかりやすい表示名
description 文字列 接続の説明
cloudSql.type 文字列 POSTGRES または MYSQL のいずれか。
cloudSql.instanceId 文字列 Cloud SQL インスタンスの名前。通常、次の形式で指定します。

Project-id:location-id:instance-id

インスタンス ID は、Cloud SQL インスタンスの詳細ページにあります。
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. [ログ エクスプローラ] ページに移動します。

    ログ エクスプローラに移動

  2. [クエリ] タブに次のクエリを入力します。

    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 です。詳しい手順については、サービス アカウントへのアクセス権を付与するをご覧ください。

次のステップ