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

次のステップ