連携クエリ関数

GoogleSQL for BigQuery は、次の連携クエリ関数をサポートしています。

関数リスト

名前 まとめ
EXTERNAL_QUERY 外部データベースに対してクエリを実行し、結果を一時テーブルとして返します。

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

説明

外部データベースに対してクエリを実行し、結果を一時テーブルとして返します。外部データベースのデータ型は、次のデータ型マッピングの一時結果テーブルで GoogleSQL データ型に変換されます。

  • external_database_query: 外部データベースで実行するクエリ。
  • connection_id: 接続リソースの ID。接続リソースには、外部データベースと BigQuery 間の接続の設定が含まれます。デフォルト プロジェクトが構成されていない場合は、次の形式でプロジェクト ID を接続 ID の前に追加します。

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
    

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

    • PROJECT_ID: プロジェクト ID。
    • LOCATION: 接続のロケーション。
    • CONNECTION_ID: 接続 ID。

    例: projects/example-project/locations/us/connections/sql-bq詳細については、接続リソースの作成をご覧ください。

+ options: オプションの名前と値の Key-Value ペアを含む、JSON 形式マップの文字列。共に大文字と小文字が区別されます。

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

追加情報:

  • EXTERNAL_QUERY 関数は通常、FROM 句で使用されます。
  • EXTERNAL_QUERY() 関数を使用して外部データベースに関するメタデータにアクセスできます。
  • 外部クエリに ORDER BY が含まれていても、EXTERNAL_QUERY() は外部クエリの結果で順序を考慮しません。

戻りデータの型

BigQuery テーブル

顧客ごとに最初の注文日をレポートに記載する必要があるとします。このデータは現在 BigQuery にはありませんが、稼働している PostgreSQL データベースにあります。この目的を達成するために、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. 顧客情報と最初の注文日を選択します。
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    '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;

EXTERNAL_QUERY() 関数を使用して information_schema テーブルにクエリを実行すると、データベース内のすべてのテーブルの一覧など、データベースのメタデータを取得できます。また、テーブルのスキーマを表示することもできます。次の information_schema クエリの例は MySQLPostgreSQL の両方で機能します。

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

外部クエリに ORDER BY が含まれていても、EXTERNAL_QUERY() は外部クエリの結果で順序を考慮しません。次のサンプルクエリでは、外部データベースに格納されている顧客 ID で行の並べ替えを行っていますが、BigQuery ではその順序で結果行が出力されていません。

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

データ型マッピング

連携クエリを実行すると、外部データベースのデータは GoogleSQL 型に変換されます。以下では、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 から 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 列を宣言するとき、いくつかの値を事前に定義します。その後、この列に定義済みの値のセットを挿入します STRING
GEOMETRY GEOGRAPHY 現時点では未対応
BIT INT64 現時点では未対応

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

名前 説明 BigQuery の型 型の違い
整数
smallint 2 バイト、-32768~+32767 INT64
smallserial smallint を参照 INT64
integer 4 バイト、-2147483648~+2147483647 INT64
serial integer を参照 INT64
bigint 8 バイト、-9223372036854775808~9223372036854775807 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 桁の尺度、-92233720368547758.08~+92233720368547758.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 形式) 非対応

サポートされていない MySQL と PostgreSQL のデータ型

BigQuery でサポートされていないデータ型が外部クエリに含まれている場合、クエリはすぐに失敗します。サポートされていないデータ型は、サポートされている別の 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]
    • サポートされていない型: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • 解決策: サポートされていないデータ型を STRING 型にキャストする。
    • 例: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); このコマンドを実行すると、サポートされていないデータ型 moneystring にキャストされます。

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

Spanner 連携クエリを実行すると、Spanner のデータは GoogleSQL 型に変換されます。

Spanner GoogleSQL の型 Spanner PostgreSQL の型 BigQuery の型
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Spanner 連携クエリではサポートされていません
TIMESTAMP timestamptz TIMESTAMP(ナノ秒は切り捨て)

* BigQuery がサポートする精度よりも高い精度を持つ PostgreSQL 数値は丸められます。最大値より大きい値は Invalid NUMERIC value エラーになります。

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