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 へのデータの複製をご覧ください。

始める前に

  • BigQuery 管理者が Cloud SQL 接続を作成して、それを共有していることを確認します。

  • Cloud SQL インスタンスをクエリするのに必要な権限を取得するには、プロジェクトに対する BigQuery 接続ユーザー（roles/bigquery.connectionUser）の IAM ロールを付与するように管理者に依頼してください。ロールの付与の詳細については、アクセス権の管理をご覧ください。

    必要な権限は、カスタムロールや他の事前定義ロールから取得することもできます。

データのクエリ

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 文字列 データベース パスワード

トラブルシューティング

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

次のステップ