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
: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 个部分:
- 通过
EXTERNAL_QUERY()
函数,在可用的 PostgreSQL 数据库中运行外部查询SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id
,以获取每位客户的第一个订单日期。 - 通过
customer_id
联接外部查询结果表与 BigQuery 中的客户表。 - 选择客户信息和第一个订单日期。
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 的数据类型映射。
关于映射的注意事项:
- 大多数 MySQL 数据类型可以与相同的 BigQuery 数据类型匹配,但存在一些例外情况,例如
decimal
、timestamp
、time
。 - PostgreSQL 支持 BigQuery 中不支持的许多非标准数据类型,例如
money
、path
、uuid
、boxer
等。 - 默认情况下,MySQL 和 PostgreSQL 中的数字数据类型将映射到 BigQuery
NUMERIC
值。BigQueryNUMERIC
值范围小于 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 | 最大长度为 65535 个字符的字段。 | STRING | |
TINYTEXT | 最大长度为 255 个字符的 TEXT 列。 | STRING | |
MEDIUMTEXT | 最大长度为 16777215 个字符的 TEXT 列。 | STRING | |
LONGTEXT | 最大长度为 4294967295 个字符的 TEXT 列。 | STRING | |
二进制 | |||
BLOB | 最大长度为 65535 个字符的二进制大型对象。 | BYTES | |
MEDIUM_BLOB | 最大长度为 16777215 个字符的 BLOB。 | BYTES | |
LONG_BLOB | 最大长度为 4294967295 个字符的 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 | |
整数 | 4 个字节,-2147483648 到 +2147483647 | INT64 | |
serial | 请参阅整数 | INT64 | |
bigint | 8 个字节,-9223372036854775808 到 9223372036854775807 | INT64 | |
bigserial | 请参阅 bigint | INT64 | |
精确数字 | |||
numeric [ (p, s) ] | 精度高达 1000。 | NUMERIC、BIGNUMERIC、FLOAT64 或 STRING | numeric [ (p, s) ] 将默认映射到 NUMERIC,也可以通过 default_type_for_decimal_columns 映射到 BIGNUMERIC、FLOAT64 或 STRING。 |
Decimal [ (p, s) ] | 请参阅数字 | NUMERIC | 请参阅数字 |
money | 8 个字节,2 位标度,-92233720368547758.08 到 +92233720368547758.07 | 不支持 | |
近似数字 | |||
real | 4 个字节,单精度浮点数 | FLOAT64 | |
双精度 | 8 个字节,双精度浮点数 | FLOAT64 | |
日期和时间 | |||
date | 日历日期(年、月、日) | DATE | |
time [ (p) ] [无时区] | 一天中的时间(无时区) | TIME | |
time [ (p) ],有时区 | 一天中的时间,包含时区 | 不支持 | |
timestamp [ (p) ] [无时区] | 日期和时间(无时区) | DATETIME | |
timestamp [ (p) ],有时区 | 日期和时间,包含时区 | 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(媒体访问控制)地址 | 不支持 | |
macaddr8 | MAC(媒体访问控制)地址(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
- 解决方案:将不支持的数据类型转换为字符串。
- 示例:
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
- 解决方案:将不支持的数据类型转换为字符串。
- 示例:
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
错误。
如果外部查询包含联合查询不支持的数据类型,则查询将立即失败。您可以将不受支持的数据类型转换为受支持的数据类型。