> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

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

# bigquery

支持对 [Google BigQuery](https://cloud.google.com/bigquery) 中的表 (包括公开数据集) 执行 `SELECT` 和 `INSERT` 查询。表结构会根据 BigQuery 表 schema 自动推断。

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

<div id="syntax">
  ## 语法
</div>

```sql theme={null}
bigquery(project, dataset, table[, access_token][, key = value, ...])
bigquery(named_collection[, key = value, ...])
```

<div id="arguments">
  ## 参数
</div>

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

`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`。 |

<div id="authentication">
  ## 身份验证
</div>

必须且只能提供一种身份验证方法。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` 获取。

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

<div id="data-type-mapping">
  ## 数据类型映射
</div>

| BigQuery 类型           | ClickHouse 类型                                                                                                                     |
| --------------------- | --------------------------------------------------------------------------------------------------------------------------------- |
| `STRING`              | [String](/docs/zh/reference/data-types/string)                                                                                         |
| `BYTES`               | [String](/docs/zh/reference/data-types/string) (原始字节)                                                                                  |
| `INTEGER` / `INT64`   | [Int64](/docs/zh/reference/data-types/int-uint)                                                                                        |
| `FLOAT` / `FLOAT64`   | [Float64](/docs/zh/reference/data-types/float)                                                                                         |
| `BOOLEAN` / `BOOL`    | [Bool](/docs/zh/reference/data-types/boolean)                                                                                          |
| `TIMESTAMP`           | [DateTime64(6, 'UTC')](/docs/zh/reference/data-types/datetime64)                                                                       |
| `DATE`                | [Date32](/docs/zh/reference/data-types/date32)                                                                                         |
| `TIME`                | [Time64(6)](/docs/zh/reference/data-types/time64)                                                                                      |
| `DATETIME`            | [DateTime64(6, 'UTC')](/docs/zh/reference/data-types/datetime64)                                                                       |
| `NUMERIC` / `DECIMAL` | [Decimal(38, 9)](/docs/zh/reference/data-types/decimal)，或在参数化时使用 `Decimal(P, S)`                                                       |
| `BIGNUMERIC`          | [Decimal(76, 38)](/docs/zh/reference/data-types/decimal)，或在参数化时使用 `Decimal(P, S)`                                                      |
| `GEOGRAPHY`           | [Geometry](/docs/zh/reference/data-types/geo#geometry) (由 WKT 解析)                                                                      |
| `JSON`                | [String](/docs/zh/reference/data-types/string)                                                                                         |
| `INTERVAL`            | [String](/docs/zh/reference/data-types/string)                                                                                         |
| `RANGE`               | [String](/docs/zh/reference/data-types/string) (只读)                                                                                    |
| `RECORD` / `STRUCT`   | [Tuple](/docs/zh/reference/data-types/tuple)，或在 `NULLABLE` 模式下使用 [Nullable](/docs/zh/reference/data-types/nullable)(`Tuple`)                |
| `REPEATED` 模式         | 元素类型的 [Array](/docs/zh/reference/data-types/array)，其元素不可为 `Nullable` (`RECORD` 元素使用 `Array(Tuple(...))`) ，因为 BigQuery 数组不能包含 `NULL` 元素 |
| `NULLABLE` 模式         | [Nullable](/docs/zh/reference/data-types/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](/docs/zh/reference/data-types/geo#geometry)。BigQuery 将 `GEOGRAPHY` 值作为 [WKT](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry) 文本传输：读取时将其解析为对应的 `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](/docs/zh/reference/data-types/newjson) 数据类型，因为 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]` 范围之外的值。

<div id="examples">
  ## 示例
</div>

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

```sql theme={null}
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;
```

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

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

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

```sql theme={null}
INSERT INTO FUNCTION bigquery('my-project', 'my_dataset', 'my_table', '<access token>')
SELECT number AS id, toString(number) AS name FROM numbers(10);
```

使用命名集合：

```xml theme={null}
<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>
```

```sql theme={null}
SELECT * FROM bigquery(my_bigquery, table = 'my_table');
```

<div id="limitations">
  ## 限制
</div>

* 只能读取原生 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`) 。

<div id="related">
  ## 相关内容
</div>

* [`BigQuery` 表引擎](/docs/zh/reference/engines/table-engines/integrations/bigquery)