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 つの部分から構成されている次の連携クエリを使用します。
- 稼働中の PostgreSQL データベースで外部クエリ
SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id
を実行し、EXTERNAL_QUERY()
関数によって各顧客の最初の注文日を取得します。 - 外部クエリの結果テーブルと BigQuery の顧客テーブルを
customer_id
で結合します。 - 顧客情報と最初の注文日を選択します。
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
エラーになります。
連携クエリでサポートされていないデータ型が外部クエリに含まれている場合、クエリはすぐに失敗します。サポートされていないデータ型は、サポートされているデータ型にキャストできます。