bigquery

支持对 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 不允许匿名访问，因此即使是公开数据集也需要提供凭据。

访问令牌。任何有效的 OAuth 2.0 访问令牌，例如通过 gcloud auth print-access-token 获取的令牌。令牌会很快过期 (通常在一小时后) ，因此此方法最适合交互式使用。
服务账号密钥 (推荐用于服务器) 。通过 service_account_key 参数传入在 Google Cloud IAM 中创建的密钥文件内容。ClickHouse 使用该密钥签署 JWT，并将其兑换为访问令牌，且会自动刷新。
刷新令牌。传入 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 表引擎

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

语法

参数

身份验证

数据类型映射

示例

限制

​语法

​参数

​身份验证

​数据类型映射

​示例

​限制

​相关内容

语法

参数

身份验证

数据类型映射

示例

限制

相关内容