連携クエリ関数

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詳細については、接続リソースの作成をご覧ください。

  注意: EXTERNAL_QUERY を使用する複数のプロジェクトで共有されているビューがある場合、常に完全修飾接続 ID（projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID ）を使用してください。そうでないと、誤ったプロジェクトが使用される可能性があります。

+ 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 クエリの例は MySQL と PostgreSQL の両方で機能します。

-- 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 へのデータ型のマッピングについて説明します。

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

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

エラー処理

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]
  • サポートされていない型: GEOMETRY、BIT
  • 解決策: サポートされていないデータ型を STRING 型にキャストする。
  • 例: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); このコマンドを実行すると、サポートされていないデータ型 GEOMETRY が STRING にキャストされます。
• サポートされていない 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)); このコマンドを実行すると、サポートされていないデータ型 money が string にキャストされます。

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 エラーになります。

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