架构自动检测功能
在将数据加载到 BigQuery 以及查询外部数据源时,您可以使用架构自动检测功能。
启用自动检测功能后,BigQuery 按以下方式开始推理过程:在数据源中随机选择一个文件,然后扫描多达 100 行数据以用作代表性样本。BigQuery 接着检查每个字段,并尝试根据样本中的值向该字段分配数据类型。
您可以通过下列方式查看检测到的表架构:
- 使用 Cloud Console。
- 使用
bq
命令行工具的bq show
命令。
当 BigQuery 检测架构时,在极少数情况下可能会更改字段名称,使其与 BigQuery SQL 语法兼容。
如需了解数据类型转换,请参阅以下内容:
- 数据类型转换(从 Datastore 加载数据时)
- 数据类型转换(从 Firestore 加载数据时)
- Avro 转换
- Parquet 转换
- ORC 转换
在加载数据时使用架构自动检测功能
如需在加载数据时启用架构自动检测功能,请使用以下方法之一:
- 在 Cloud Console 中,对于架构部分的自动检测,勾选架构和输入参数选项。
- 在
bq
命令行工具中,使用带有--autodetect
参数的bq load
命令。
如果启用了架构自动检测功能,BigQuery 将尽力尝试自动推断出 CSV 和 JSON 文件的架构。
架构自动检测功能不适用于 Avro 文件、Parquet 文件、ORC 文件、Firestore 导出文件或 Datastore 导出文件。在您将这些文件加载到 BigQuery 中时,系统会自动从自描述源数据中检索表架构。
如需在加载 JSON 或 CSV 数据时使用架构自动检测功能,请执行如下操作:
控制台
在 Cloud Console 中,转到 BigQuery 页面。
在导航面板的资源部分中,选择一个数据集。
点击创建表。
在创建表页面的来源部分,执行以下操作:
- 在基于以下数据创建表部分,选择所需的来源类型。
在来源字段中,浏览文件/Cloud Storage 存储分区,或输入 Cloud Storage URI。请注意,Cloud Console 不支持添加多个 URI,但支持使用通配符。Cloud Storage 存储分区必须与您要创建的表所属的数据集位于同一位置。
在文件格式部分,选择 CSV 或 JSON。
在创建表页面的目标位置部分,执行以下操作:
在数据集名称部分,选择相应数据集。
在表名称字段中,输入您要创建的表的名称。
确认表类型是否设置为原生表。
点击创建表。
bq
发出带 --autodetect
参数的 bq load
命令。
(可选)提供 --location
标志并将其值设置为您的位置。
以下命令通过架构自动检测功能加载文件:
bq --location=LOCATION load \ --autodetect \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE
请替换以下内容:
LOCATION
:您的位置名称。--location
是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为asia-northeast1
。您可以使用 .bigqueryrc 文件设置位置的默认值。FORMAT
:NEWLINE_DELIMITED_JSON
或CSV
。DATASET
:要向其中加载数据的表所属的数据集。TABLE
:要向其中加载数据的表的名称。PATH_TO_SOURCE
:CSV 或 JSON 文件的位置。
示例:
输入以下命令可以将 myfile.csv
从本地机器加载到存储在 mydataset
数据集的 mytable
表中。
bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv
输入以下命令可以将 myfile.json
从本地机器加载到存储在 mydataset
数据集的 mytable
表中。
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json
API
创建指向源数据的
load
作业。如需了解如何创建作业,请参阅以编程方式运行 BigQuery 作业。在jobReference
部分的location
属性中指定您的位置。设置
sourceFormat
属性以指定数据格式。如需使用架构自动检测功能,必须将此值设置为NEWLINE_DELIMITED_JSON
或CSV
。使用
autodetect
属性将架构自动检测设置为true
。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
PHP
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 PHP 设置说明进行操作。如需了解详情,请参阅 BigQuery PHP API 参考文档。
Python
如需启用架构自动检测功能,请将 LoadJobConfig.autodetect 属性设置为 True
。
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
Ruby
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Ruby 设置说明进行操作。如需了解详情,请参阅 BigQuery Ruby API 参考文档。
针对外部数据源的架构自动检测
架构自动检测功能可以与 CSV、JSON 和 Google 表格外部数据源搭配使用。启用架构自动检测功能后,BigQuery 将尽力尝试自动从源数据推断出架构。如果您不为这些来源启用架构自动检测功能,则必须提供显式架构。
架构自动检测功能不适用于自描述的外部文件,包括 Avro、Parquet 和 ORC 格式,或 Firestore 导出文件或 Datastore 导出文件。在创建链接到这些文件类型之一的表时,BigQuery 会自动从自描述源数据中检索架构。
使用 Cloud Console,您可以勾选自动检测的架构和输入参数选项来启用架构自动检测功能。
借助 bq
命令行工具,您可以在为 CSV、JSON 或 Google 表格数据创建表定义文件时启用架构自动检测功能。使用 bq
工具创建表定义文件时,将 --autodetect
标志传递给 mkdef
命令以启用架构自动检测功能,或传递 --noautodetect
标志以停用自动检测功能。
使用 --autodetect
标志时,autodetect
设置在表定义文件中设定为 true
。使用 --noautodetect
标志时,autodetect
设置设定为 false
。如果您在创建表定义时未提供外部数据源的架构定义,且未使用 --noautodetect
或 --autodetect
标志,则 autodetect
设置默认为 true
。
使用 API 创建表定义文件时,请将 autodetect
属性的值设置为 true
或 false
。将 autodetect
设置为 true
可启用自动检测功能。将 autodetect
设置为 false
可将其停用。
自动检测详述
除了检测架构详细信息外,自动检测还可识别以下内容:
压缩
打开文件时,BigQuery 会识别与 gzip 兼容的文件压缩。
CSV 分隔符
BigQuery 会检测以下分隔符:
- 逗号 (,)
- 竖线 (|)
- 制表符 (\t)
CSV 标题
BigQuery 通过将文件的第一行与数据集中的其他行进行比较来推断出标题。如果第一行只包含字符串,而其他行包含其他数据类型,则 BigQuery 会假设第一行是标题行。在这种情况下,BigQuery 会根据标题行中的字段名称分配列名称。您可以根据 BigQuery 中列的命名规则修改名称。例如,空格将替换为下划线。
否则,BigQuery 会假设第一行是数据行,并分配常规列名(例如 string_field_1
)。请注意,创建表后,无法在架构中更新列名,但您可以在创建表后手动更改名称。另一种方法是提供显式架构,而不是使用自动检测功能。
您可能拥有一个包含标题行的 CSV 文件,其所有数据字段都是字符串。在这种情况下,BigQuery 不会自动检测第一行是否为标题。使用 -skip_leading_rows
选项跳过标题行。否则,标题将作为数据导入。在这种情况下,请考虑提供显式架构,以便您可以分配列名。
CSV 中括起的新行
BigQuery 可检测 CSV 字段中括起的新行字符,不会将其解释为行边界。
日期和时间值
BigQuery 会根据源数据的格式检测日期和时间值。
DATE
列中的值必须采用以下格式:YYYY-MM-DD
。
TIME
列中的值必须采用以下格式:HH:MM:SS[.SSSSSS]
(小数秒部分是可选的)。
对于 TIMESTAMP
列,BigQuery 会检测各种时间戳格式,包括但不限于:
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSSSSS
YYYY/MM/DD HH:MM
时间戳还可包含世界协调时间 (UTC) 偏移量或世界协调时间 (UTC) 可用区指示符 (Z)。
以下是 BigQuery 将自动检测为时间戳值的一些值示例:
- 2018-08-19 12:11
- 2018-08-19 12:11:35.22
- 2018/08/19 12:11
- 2018-08-19 07:11:35.220 -05:00
如果 BigQuery 无法识别格式,则会以字符串数据类型加载该列。在这种情况下,您可能需要在加载源数据之前进行预处理。例如,如果您要从电子表格导出 CSV 数据,请将日期设置为与此处所示的某个示例相匹配的格式。或者,您可以在将数据加载到 BigQuery 后转换数据。