bigquery

允许在 Google BigQuery 的表上执行 SELECT 和 INSERT 查询，包括公共数据集。

支持对 Google BigQuery 中的表 (包括公开数据集) 执行 SELECT 和 INSERT 查询。表结构会根据 BigQuery 表 schema 自动推断。

读取操作使用 BigQuery REST API (tabledata.list)，因此只能读取原生表 (不支持视图、materialized view 和外部表) 。写入操作使用流式插入 (tabledata.insertAll)，要求项目已启用计费。

​

语法

bigquery(project, dataset, table[, access_token][, key = value, ...])
bigquery(named_collection[, key = value, ...])

​

参数

参数	描述
project	拥有该数据集的 Google Cloud 项目。对于公开数据集，指该数据集所属的项目，例如 bigquery-public-data。
dataset	数据集名称。
table	表名称。
access_token	OAuth 2.0 访问令牌 (可选的位置参数，参见 身份验证) 。

project、dataset、table 和 access_token 参数也可以通过 key = value 形式提供；位置参数会按此顺序填充这些参数。同时以位置参数和键的形式指定同一参数 (或重复指定同一个键) 会导致错误。

以下参数可通过 key = value 形式指定 (或作为命名集合中的键) ：

键	描述
access_token	OAuth 2.0 访问令牌。
service_account_key	JSON 格式的 Google 服务账号密钥文件内容。
client_id	OAuth 2.0 客户端 ID (与 client_secret 和 refresh_token 一起使用) 。
client_secret	OAuth 2.0 客户端密钥。
refresh_token	OAuth 2.0 刷新令牌。
billing_project	用于归属配额和计费的可选项目 (作为 X-Goog-User-Project 请求头发送) 。
base_url	API 端点，默认为 https://bigquery.googleapis.com。可针对测试和模拟器进行更改。
token_url	用于测试和模拟器的 OAuth 令牌端点替代值。默认使用服务账号密钥中的 token_uri 或 https://oauth2.googleapis.com/token。

​

身份验证

必须且只能提供一种身份验证方法。BigQuery 不允许匿名访问，因此即使是公开数据集也需要提供凭据。

1. 访问令牌。任何有效的 OAuth 2.0 访问令牌，例如通过 gcloud auth print-access-token 获取的令牌。令牌会很快过期 (通常在一小时后) ，因此此方法最适合交互式使用。
2. 服务账号密钥 (推荐用于服务器) 。通过 service_account_key 参数传入在 Google Cloud IAM 中创建的密钥文件内容。ClickHouse 使用该密钥签署 JWT，并将其兑换为访问令牌，且会自动刷新。
3. 刷新令牌。传入 client_id、client_secret 和 refresh_token，例如在执行 gcloud auth application-default login 后从 ~/.config/gcloud/application_default_credentials.json 获取。

将凭据存储在命名集合中，避免在每个查询中重复指定。通过命名集合创建的永久表 (使用 BigQuery 表引擎或 CREATE TABLE ... AS bigquery(...)) 会被注册为该集合的依赖项，因此只要该表仍存在，DROP NAMED COLLECTION 就会被阻止。

​

数据类型映射

BigQuery 类型	ClickHouse 类型
STRING	String
BYTES	String (原始字节)
INTEGER / INT64	Int64
FLOAT / FLOAT64	Float64
BOOLEAN / BOOL	Bool
TIMESTAMP	DateTime64(6, ‘UTC’)
DATE	Date32
TIME	Time64(6)
DATETIME	DateTime64(6, ‘UTC’)
NUMERIC / DECIMAL	Decimal(38, 9)，或在参数化时使用 Decimal(P, S)
BIGNUMERIC	Decimal(76, 38)，或在参数化时使用 Decimal(P, S)
GEOGRAPHY	Geometry (由 WKT 解析)
JSON	String
INTERVAL	String
RANGE	String (只读)
RECORD / STRUCT	Tuple，或在 NULLABLE 模式下使用 Nullable(Tuple)
REPEATED 模式	元素类型的 Array，其元素不可为 Nullable (RECORD 元素使用 Array(Tuple(...))) ，因为 BigQuery 数组不能包含 NULL 元素
NULLABLE 模式	Nullable (GEOGRAPHY 除外，其 Geometry 类型本身可包含 NULL)

注释：

• BigQuery DATETIME 不带时区；会映射为 DateTime64(6, 'UTC')，以确保显示的值不受服务器时区影响。
• NULLABLE RECORD 会映射为 Nullable(Tuple(...))，从而将整个记录的 NULL 保留为 NULL，而不会变为由默认值组成的 Tuple。NULL (或空) 数组会变为空数组，因为 ClickHouse 中 Array 不能位于 Nullable 内。BigQuery 数组不能包含 NULL 元素 (ARRAY<T> 等同于 ARRAY<T NOT NULL>) ，因此 REPEATED 字段的元素类型不是 Nullable (RECORD 元素对应 Array(Tuple(...))，其他元素对应 Array(T)) ；tabledata.list 响应中的 NULL 元素会因输入格式错误而被拒绝。
• 通过 bigquery 表函数读取和写入 Nullable(Tuple(...)) 列无需额外设置。创建包含此类列的持久化 BigQuery 引擎表 (无论结构是自动推断还是显式声明) 都需要设置 enable_nullable_tuple_type，与其他所有 Nullable(Tuple) 列相同。显式声明列时，也可将 RECORD 字段声明为普通 Tuple(...) 以避免该设置，但代价是将整个记录的 NULL 强制转换为默认元组；相较于推断类型，唯一允许的差异是在同一记录上移除包裹其 Tuple 的 Nullable——不能将可空性移至其他 (内部或外部) 记录。
• GEOGRAPHY 映射为 Geometry。BigQuery 将 GEOGRAPHY 值作为 WKT 文本传输：读取时将其解析为对应的 Geometry 备选类型 (即由 Point、MultiPoint、Ring、LineString、MultiLineString、Polygon 和 MultiPolygon 组成的 Variant) ，写入时再序列化为 WKT。GEOMETRYCOLLECTION 和空几何图形 (如 POINT EMPTY) 没有对应的 Geometry 类型，因此读取包含此类值的行会引发错误。由于 Variant 本身可容纳 NULL，NULLABLE GEOGRAPHY 字段映射为 Geometry 而非 Nullable(Geometry)，且 NULL 仍可无损往返转换。
• JSON 映射为 String 而非 JSON 数据类型，因为 ClickHouse 的 JSON 类型仅接受顶层对象 ({...}) ，而 BigQuery JSON 值可以是任意 JSON 值——标量、数组或 null——因此包含此类值的表将无法读取。此外，JSON 不能包装在 Nullable 中，因此无法保留 NULLABLE 列中的 SQL NULL。String 映射无损；顶层对象可通过 CAST(value AS JSON) 转换。
• 整数部分超过 38 位的 BIGNUMERIC 值无法存入 Decimal(76, 38)，会引发错误。
• 超出 DateTime64/Date32 范围 (1900 至 2299 年) 的 TIMESTAMP 和 DATE 值不受支持。
• RANGE 列为只读。tabledata.insertAll 要求 RANGE<T> 值为结构化的 {start, end} 对象，无法根据 String 映射重建，因此向 RANGE 列插入数据会引发错误。
• INT64 值会以十进制字符串形式发送到 tabledata.insertAll，因为 API 会将 JSON 数值解析为双精度浮点数，否则会损坏 [-2^53 + 1, 2^53 - 1] 范围之外的值。

​

示例

使用 gcloud 中的令牌读取公开数据集：

SELECT word, sum(word_count) AS c
FROM bigquery('bigquery-public-data', 'samples', 'shakespeare', '<access token>')
GROUP BY word
ORDER BY c DESC
LIMIT 5;

使用服务账号密钥文件读取私有表：

SELECT count()
FROM bigquery('my-project', 'my_dataset', 'my_table',
              service_account_key = '{"type": "service_account", "private_key": "...", "client_email": "...", ...}');

插入数据 (流式插入，需要启用计费) ：

INSERT INTO FUNCTION bigquery('my-project', 'my_dataset', 'my_table', '<access token>')
SELECT number AS id, toString(number) AS name FROM numbers(10);

使用命名集合：

<clickhouse>
    <named_collections>
        <my_bigquery>
            <project>my-project</project>
            <dataset>my_dataset</dataset>
            <service_account_key><![CDATA[{"type": "service_account", ...}]]></service_account_key>
        </my_bigquery>
    </named_collections>
</clickhouse>

SELECT * FROM bigquery(my_bigquery, table = 'my_table');

​

限制

• 只能读取原生 BigQuery 表。视图和外部表需要运行 BigQuery 查询作业，而此函数不会这样做。
• 可以读取 RANGE 列 (以 String 类型读取) ，但不能写入：向 RANGE 列插入数据会引发错误。
• 值为 GEOMETRYCOLLECTION 或空几何体的 GEOGRAPHY 无法用 Geometry 类型表示，因此读取包含此类值的行会引发错误。BigQuery 不接受这些位置的 NULL，因此向 REQUIRED GEOGRAPHY 字段写入 NULL Geometry，或将其作为 REPEATED GEOGRAPHY 字段的元素写入，都会被拒绝。
• 谓词不会下推：tabledata.list 只能列出表中的行，完全不提供筛选参数 (仅接受分页、列选择和格式选项) ，而筛选需要运行 BigQuery 查询作业，此函数不会这样做。因此，WHERE 条件会在行下载后由 ClickHouse 应用；请使用列选择来减少传输的数据量。
• 不过，LIMIT 确实会减少读取的数据量。页面会按需请求，maxResults 设为 max_block_size；查询获取到足够的行后，不会再请求更多页面。对于简单的 LIMIT n (没有 WHERE、GROUP BY、ORDER BY，且 n 小于 max_block_size) ，ClickHouse 会将 max_block_size 降至 n，因此恰好发出一次请求并读取恰好 n 行；否则，读取会在超过限制后的第一个页面边界停止，超出限制的行数不足一页。
• 通过向 tabledata.list 传递显式列列表，读取会固定使用查询分析时看到的 schema。对于列列表会超出请求 URL 长度限制的超宽查询 (例如从包含数千列的表中执行 SELECT *) ，查询会被拒绝，而不会在未固定 schema 的情况下读取 (未固定的读取可能因并发 schema 变更而发生错位) ；请选择更少的列，以确保列表能够容纳。每次分页请求之前也会检查相同的 URL 长度限制 (每个页面都带有不透明的 pageToken) ，因此，如果后续页面无法满足该限制，读取会以相同错误被拒绝，而不会在中途失败。
• 如果在读取 BigQuery 表的 schema 后该表发生变更，查询会被拒绝，而不是静默返回或写入不匹配的数据：读取前会重新获取实时 schema，并将其与分析时的 schema 比较；INSERT 流式写入第一行之前也会再次比较。由于 schema 和数据通过不同的 REST 请求获取，仍无法消除检查与后续请求之间发生 schema 变更的窗口期。
• 比较的对象是查询分析时使用的 schema 快照：对于表函数，该快照在解析其结构时获取；对于持久表 (BigQuery engine 表，或使用 CREATE TABLE ... AS bigquery(...) 创建的表，后者也以相同方式持久保存其列) ，则在 CREATE、ATTACH 或 server 重启后的首次读取或写入时获取。表 metadata 持久保存的是映射后的 ClickHouse 列，而不是 BigQuery schema，因此，在表处于 detached 状态 (或 server 停止运行) 期间发生的 schema 变更会由下一次查询采用，而不会被拒绝：声明的列仍会根据实时 schema 进行验证，并使用该 schema 解码行。因此，保留映射后 ClickHouse 类型的变更 (例如从 STRING 到 BYTES) 会在相同列类型下按新类型’s 规则读取。
• 通过流式插入写入的行会进入 BigQuery 流式缓冲区，可能需要一段时间才会在后续读取中可见。
• 大型 INSERT 会分批发送至 tabledata.insertAll：每个请求最多 500 行，并会进一步拆分，以确保每个请求不超过 BigQuery’s 10 MB 请求大小限制 (超过该限制的单行会被拒绝，并返回明确的错误) 。
• 写入不是原子操作，单个 tabledata.insertAll 请求也可能只部分成功：BigQuery 可以提交请求中的部分行，同时通过 insertErrors 拒绝其余行。各请求也是彼此独立提交的，因此即使较早的批次已被接受，较晚的批次仍可能被拒绝。两种情况下，查询都会报告错误，但已提交的行仍会保留在 BigQuery 中。为减少重复，每行都会附带一个稳定的 insertId，该值根据查询 id 和该行在流中的序号生成，BigQuery 会在其流式插入窗口内利用它进行尽力而为的去重。长度超过 BigQuery 的 128 字符 insertId 限制的 query_id 会被哈希为固定长度的前缀，并且对该 query_id 保持稳定。由于 insertId 取决于序号位置，只有在重新运行时以相同顺序生成各行，去重才可靠：对批次进行传输层重试始终是安全的；而使用相同 query_id 重新运行相同的 INSERT，仅当各行以相同顺序提供时才能去重 (例如单线程插入，或采用其他确定性排序；对于分片顺序可能在不同尝试之间发生变化的并行 INSERT ... SELECT，请设置 max_threads = 1 和 max_insert_threads = 1) 。

​

相关内容

• BigQuery 表引擎