Cloud SQL 連携クエリ

このページでは、連携クエリを使用して BigQuery から Cloud SQL のデータにクエリを実行する方法について説明します。

概要

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

最初に 1 回だけ設定を行う必要がありますが、その後は、SQL 関数の EXTERNAL_QUERY でクエリを作成できます。

始める前に

BigQuery 接続サービスを有効にする

  1. API ライブラリの BigQuery Connection API ページを開きます。
  2. プルダウン メニューから、外部データソースを含むプロジェクトを選択します。
  3. [有効にする] ボタンをクリックします。

    BigQuery Connection API

サービス アカウント

BigQuery Connection API を有効にすると、サービス アカウントが自動的に作成されます。Cloud SQL データソースを含むプロジェクトで BigQuery Connection API を有効にすると、次のロールが適用されます。

ロール 説明
cloudsql.client Cloud SQL インスタンスに接続する
logging.logWriter cloud-logging に書き込む
metrics.metricWriter cloud-monitoring に書き込む

サービス アカウントの詳細については、サービス エージェントをご覧ください。

パブリック IP

BigQuery Cloud SQL 連携機能は、パブリック IP で接続可能な Cloud SQL インスタンスでのみサポートされます。Cloud SQL インスタンスのパブリック IP 接続を構成してください。

Cloud SQL データベース接続の設定

BigQuery Connection API が有効になったら、Cloud SQL データベースとの接続を作成します。

Console

  1. 接続リソースを作成するには、Cloud Console の [BigQuery] ページに移動します。

    [BigQuery] ページに移動

  2. [データを追加] メニューで [外部データソース] を選択します。

    接続リソースを作成する

  3. [外部データソース] ペインで、次の情報を入力します。

    • [接続タイプ] で、ソースのタイプ(MySQL、Postgres など)を選択します。
    • [接続 ID] に、接続リソースの識別子を入力します。文字、数字、アンダースコアを使用できます。
    • [接続ロケーション] で、外部データソースのリージョンと互換性のある BigQuery ロケーション(またはリージョン)を選択します。
    • (省略可): [わかりやすい名前] に、わかりやすい接続名を入力します(例: My connection resource)。後で修正が必要になった場合、わかりやすい名前だと接続リソースを簡単に識別できます。
    • (省略可): [説明] に、この接続リソースの説明を入力します。
    • 接続タイプに Cloud SQL の MySQL または Postgres を選択した場合、[Cloud SQL インスタンス ID] には完全な Cloud SQL インスタンスの名前を入力します(通常は project-id:location-id:instance-id の形式)。インスタンス ID は、クエリを実行する Cloud SQL インスタンスの詳細ページで見つけることができます。
    • [データベース名] に、データベースの名前を入力します。
    • [ユーザー名] に、データベースのユーザー名を入力します。
    • [パスワード] に、データベースのパスワードを入力します。

      • (省略可)パスワードを表示するには、[パスワードを表示する] をオンにします。

      新しい接続リソース

  4. [接続の作成] をクリックします。

bq

bq mk コマンドを入力して、接続フラグ --connection を指定します。次のフラグも必要です。

  • --connection_type
  • --properties
  • --connection_credential
  • --project_id
  • --location

次のフラグは省略可能です。

  • --display_name 接続のわかりやすい名前。
  • --description 接続の説明。

connection-id は、コマンドの最後の引数として追加できるオプションのパラメータで、内部的な保存に使用されます。接続 ID を指定しない場合は、一意の ID が自動的に生成されます。接続 ID には、文字、数字、アンダースコアを使用できます。

    bq mk --connection --display_name='friendly name' --connection_type=TYPE \
      --properties=PROPERTIES --connection_credential=CREDENTIALS \
      --project_id=PROJECT_ID --location=LOCATION \
      CONNECTION_ID

次のように置き換えます。

  • TYPE: 外部データソースのタイプ。
  • PROPERTIES: JSON 形式で作成された接続のパラメータ。例: --properties='{"param":"param_value"}'。接続リソースを作成するには、instanceIDdatabasetype パラメータを指定する必要があります。
  • CREDENTIALS: パラメータ usernamepassword です。
  • PROJECT_ID: プロジェクト ID。
  • LOCATION: Cloud SQL インスタンスが配置されているリージョンです。
  • CONNECTION_ID: 接続 ID。

たとえば、次のコマンドを実行すると、ID federation-test の付いたプロジェクトに my_new_connection(わかりやすい名前: 「My new connection」)という名前の接続リソースが新規に作成されます。

bq mk --connection --display_name='friendly name' --connection_type='CLOUD_SQL' \
  --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' \
  --connection_credential='{"username":"myusername", "password":"mypassword"}' \
  --project_id=federation-test --location=us my_connection_id

API

BigQuery Connection API では、ConnectionService 内で CreateConnection を呼び出して接続をインスタンス化できます。詳しくは、クライアント ライブラリのページをご覧ください。

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用にある Java の設定手順を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  public static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

接続リソースの表示、一覧表示、共有、更新、削除については、接続の操作をご覧ください。

たとえば、販売テーブルを 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. 顧客情報と最初の注文日を選択します。

サポートされるリージョン

次の表に、BigQuery と Cloud SQL でサポートされているリージョンを示します。

リージョン ロケーション

リージョンの説明 Cloud SQL リージョン 互換性のある BigQuery リージョン 互換性のある BigQuery マルチリージョン
南北アメリカ
アイオワ us-central 非対応: Cloud SQL インスタンスのこのリージョンは V1 です。
連携クエリでは、Cloud SQL の V2 インスタンスのみがサポートされます。
アイオワ us-central1 us-central1 US
ラスベガス us-west4 us-west4 US
ロサンゼルス us-west2 us-west2 US
モントリオール northamerica-northeast1 northamerica-northeast1 US
北バージニア us-east4 us-east4 US
オレゴン us-west1 us-west1 US
ソルトレイクシティ us-west3 us-west3 US
サンパウロ southamerica-east1 southamerica-east1
サウスカロライナ us-east1 us-east1 US
トロント northamerica-northeast2 northamerica-northeast2 US
ヨーロッパ
ベルギー europe-west1 europe-west1 EU
フィンランド europe-north1 europe-north1 EU
フランクフルト europe-west3 europe-west3 EU
ロンドン europe-west2 europe-west2 EU
オランダ europe-west4 europe-west4 EU
ワルシャワ europe-central2 europe-central2 EU
チューリッヒ europe-west6 europe-west6 EU
アジア太平洋
デリー asia-south2 asia-south2
香港 asia-east2 asia-east2
ジャカルタ asia-southeast2 asia-southeast2
メルボルン australia-southeast2 australia-southeast2
ムンバイ asia-south1 asia-south1
大阪 asia-northeast2 asia-northeast2
ソウル asia-northeast3 asia-northeast3
シンガポール asia-southeast1 asia-southeast1
シドニー australia-southeast1 australia-southeast1
台湾 asia-east1 asia-east1
東京 asia-northeast1 asia-northeast1

マルチリージョンのロケーション

Cloud SQL インスタンスではマルチリージョンのロケーションは使用できません。Cloud SQL マルチリージョンは連携クエリには使用できません。

EU マルチリージョン内のデータは europe-west2(ロンドン)や europe-west6(チューリッヒ)のデータセンターには保存されません。

制限事項

Cloud SQL 連携クエリには、次の制限があります。

  • パフォーマンス。連携クエリは、BigQuery ストレージのみをクエリするほど高速ではない可能性があります。BigQuery の場合、外部クエリを実行し、外部データソースから BigQuery にデータを一時的に移動するまで、ソース データベースが待機状態になります。また、ソース データベースが複雑な分析クエリ用に最適化されていない可能性があります。

  • 連携クエリは読み取り専用です。ソース データベースで実行される外部クエリは読み取り専用である必要があります。したがって、DML ステートメントと DDL ステートメントはサポートされていません。

  • サポートされていないデータ型。BigQuery でサポートされていないデータ型が外部クエリに含まれている場合、クエリはすぐに失敗します。サポートされていないデータ型は、サポートされている別のデータ型にキャストできます。

  • Cloud SQL インスタンスの上限。連携クエリは、(プライベート IP ではなく)パブリック IP を使用する Cloud SQL V2 インスタンスでのみサポートされます。

割り当てと上限

一般的な連携クエリの割り当てと上限に加えて、Cloud SQL データベースには次の制限があります。

  • クロスリージョンの連携クエリ: BigQuery のクエリ処理ロケーションと外部データソースのロケーションが異なる場合、クロスリージョンのクエリになります。プロジェクトごとに 1 日あたり 1 TB までクロス リージョンのクエリを実行できます。クロスリージョンのクエリの例を次に示します。
    • Cloud SQL インスタンスは us-west1 にありますが、BigQuery 接続は US マルチリージョンを基点にしています。BigQuery のクエリ処理ロケーションは US です。
  • 割り当て: ユーザーは外部データソース(Cloud SQL)のクエリ割り当てを制御する必要があります。連携クエリに追加の割り当て設定はありません。ワークロードの分離を実現するには、データベースのリードレプリカに対してのみクエリを実行することをおすすめします。
  • Cloud SQL の MySQLPostgreSQL の割り当てと上限が適用されます。

リファレンス

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';");

接続リソースの詳細

プロパティ名 説明
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 データベース。

接続認証情報リソースの詳細

プロパティ名 説明
username 文字列 データベース ユーザー名
password 文字列 データベース パスワード

データ型マッピング

Cloud SQL の連携クエリを実行すると、Cloud SQL のデータ(MySQL または PostgreSQL のデータ型)は BigQuery の標準 SQL 型に変換されます。以下は MySQL から BigQuery へと PostgreSQL から BigQuery へのデータ型のマッピングになります。

マッピングについて、次の点に注意してください。

  • decimaltimestamptime などのいくつかの例外を除き、ほとんどの MySQL データ型は同じ BigQuery データ型に対応します。
  • PostgreSQL は、moneypathuuidboxer など、BigQuery でサポートされていない非標準のデータ型を多数サポートしています。
  • MySQL と PostgreSQL の数値データ型は、デフォルトで BigQuery の NUMERIC 値にマッピングされます。BigQuery の NUMERIC 値の範囲は、MySQL や PostgreSQL よりも小さくなります。また、EXTERNAL_QUERY オプションの default_type_for_decimal_columns を使用した BIGNUMERICFLOAT64STRING にもマッピングできます。

エラー処理

BigQuery でサポートされていないデータ型が外部クエリに含まれている場合、クエリはすぐに失敗します。サポートされていないデータ型は、サポートされている別の MySQL / PostgreSQL データ型にキャストできます。

サポートされていないデータ型は、サポートされている別の MySQL や PostgreSQL データ型にキャストできます。

  • サポートされていない MySQL データ型

    • エラー メッセージ: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery at [1:15]
    • サポートされていない型: GEOMETRYBIT
    • 解決策: サポートされていないデータ型を STRING 型にキャストする。
    • 例: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); このコマンドを実行すると、サポートされていないデータ型 GEOMETRYSTRING にキャストされます。
  • サポートされていない PostgreSQL データ型

    • エラー メッセージ: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • サポートされていない型: moneytime with time zoneinetpathpg_lsnpointpolygontsquerytsvectortxid_snapshotuuidboxcidrcircleintervaljsonblinelsegmacaddrmacaddr8
    • 解決策: サポートされていないデータ型を STRING 型にキャストする。
    • 例: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); このコマンドを実行すると、サポートされていないデータ型 moneySTRING にキャストされます。

MySQL から BigQuery への型マッピング

MySQL タイプ MySQL の説明 BigQuery の型 型の違い
整数
INT 4 バイト、2^32 - 1 INT64
TINYINT 1 バイト、2^8 - 1 INT64
SMALLINT 2 バイト、2^16 - 1 INT64
MEDIUMINT 3 バイト、2^24 - 1 INT64
BIGINT 8 バイト、2^64 - 1 INT64
UNSIGNED BIGINT 8 バイト、2^64 - 1 NUMERIC
正確な数値
DECIMAL (M,D) 小数は(M,D)で表します。M は桁数の合計、D は小数点以下の桁数です。
M <= 65
NUMERIC、BIGNUMERIC、FLOAT64、STRING

DECIMAL (M,D) はデフォルトで NUMERIC にマッピングされるか、default_type_for_decimal_columns を使用して BIGNUMERIC、FLOAT64、または STRING にマッピングできます。
近似値
FLOAT (M,D) 4 バイト、M <= 23 FLOAT64
DOUBLE (M,D) 8 バイト、M <= 53 FLOAT64
日時
TIMESTAMP '1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07' UTC TIMESTAMP ユーザーが BigQuery を呼び出した場所に関係なく、MySQL TIMESTAMP は UTC タイムゾーンとして取得されます。
DATETIME '1000-01-01 00:00:00'~'9999-12-31 23:59:59' DATETIME
DATE '1000-01-01'~'9999-12-31'. DATE
TIME 'HH:MM:SS'形式の時間
'-838:59:59'~'838:59:59'。
TIME
BigQuery の TIME 範囲は小さく、00:00:00~23:59:59 の範囲です。
YEAR INT64
文字と文字列
ENUM 使用可能な値のリストから選択された値を持つ文字列オブジェクト。 STRING
CHAR (M) 1~255 文字の固定長の文字列。 STRING
VARCHAR (M) 1~255 文字の可変長の文字列。 STRING
TEXT 最大 65,535 文字までのフィールド。 STRING
TINYTEXT 最大 255 文字までの TEXT 列。 STRING
MEDIUMTEXT 最大 16,777,215 文字までの TEXT 列。 STRING
LONGTEXT 最大 4,294,967,295 文字までの TEXT 列。 STRING
バイナリ
BLOB 最大 65,535 文字までのバイナリ ラージ オブジェクト。 BYTES
MEDIUM_BLOB 最大 16,777,215 文字までの BLOB。 BYTES
LONG_BLOB 最大 4,294,967,295 文字までの BLOB。 BYTES
TINY_BLOB 最大 255 文字までの BLOB。 BYTES
BINARY 1~255 文字の固定長のバイナリ文字列。 BYTES
VARBINARY 1~255 文字の可変長のバイナリ文字列。 BYTES
その他
SET SET 列を宣言するとき、いくつかの値を事前に定義します。その後、INSERT を使用して、この列に定義済みの値のセットを追加します。 STRING
GEOMETRY GEOGRAPHY 現時点では未対応
BIT INT64 現時点では未対応

PostgreSQL と BigQuery の型マッピング

名前 説明 BigQuery の型 型の違い
整数
smallint 2 バイト、-32,768~+32,767。 INT64
smallserial smallint を参照。 INT64
integer 4 バイト、-2,147,483,648~+2,147,483,647。 INT64
serial integer を参照。 INT64
bigint 8 バイト、-9,223,372,036,854,775,808~9,223,372,036,854,775,807。 INT64
bigserial bigint を参照。 INT64
正確な数値
numeric [ (p, s) ] 最大 1,000 までの精度。 NUMERIC、BIGNUMERIC、FLOAT64、STRING numeric [ (p, s) ] は、デフォルトで NUMERIC にマッピングされるか、default_type_for_decimal_columns を使用して BIGNUMERIC、FLOAT64、または STRING にマッピングできます。
decimal [ (p, s) ] numeric を参照。 NUMERIC numeric を参照。
money 8 バイト、2 桁の尺度、-92,233,720,368,547,758.08~+92,233,720,368,547,758.07 非対応
近似値
real 4 バイト、単精度浮動小数点数。 FLOAT64
double precision 8 バイト、倍精度浮動小数点数。 FLOAT64
日時
date カレンダーの日付(年、月、日)。 DATE
time [ (p) ] [ without time zone ] 時刻(タイムゾーンなし)。 TIME
time [ (p) ] with time zone 時刻(タイムゾーンあり)。 非対応
timestamp [ (p) ] [ without time zone ] 日付と時刻(タイムゾーンなし)。 DATETIME
timestamp [ (p) ] with time zone 日付と時刻(タイムゾーンあり)。 TIMESTAMP ユーザーが BigQuery を呼び出す場所に関係なく、PostgreSQL の TIMESTAMP は UTC タイムゾーンとして取得されます。
interval 期間 非対応
文字と文字列
character [ (n) ] 固定長の文字列。 STRING
character varying [ (n) ] 可変長の文字列。 STRING
text 可変長の文字列。 STRING
バイナリ
bytea バイナリデータ(バイト配列)。 BYTES
bit [ (n) ] 固定長のビット列。 BYTES
bit varying [ (n) ] 可変長のビット列。 BYTES
その他
boolean 論理ブール値(true / false)。 BOOL
inet IPv4 または IPv6 ホストアドレス。 非対応
path 平面上の幾何学的なパス。 非対応
pg_lsn PostgreSQL ログシーケンス番号。 非対応
point 平面上の幾何学的な点。 非対応
polygon 平面上の閉じた幾何学的パス。 非対応
tsquery テキスト検索クエリ。 非対応
tsvector テキスト検索文書。 非対応
txid_snapshot ユーザーレベルの取引 ID のスナップショット。 非対応
uuid ユニバーサルに一意な識別子。 非対応
xml XML データ。 STRING
box 平面上の矩形のボックス。 非対応
cidr IPv4 または IPv6 ネットワーク アドレス。 非対応
circle 平面上の円。 非対応
interval [ fields ] [ (p) ] 期間。 非対応
json テキスト JSON データ。 STRING
jsonb バイナリ JSON データ、分解。 非対応
line 平面上の無限の線。 非対応
lseg 平面上の線分。 非対応
macaddr MAC(Media Access Control)アドレス。 非対応
macaddr8 MAC(Media Access Control)アドレス(EUI-64 形式)。 非対応