指定架构
通过 BigQuery,您可以在将数据加载到表中以及创建空表时指定表架构。对于支持的数据格式,您还可以使用架构自动检测功能。
加载 Avro、Parquet、ORC、Firestore 导出文件或 Datastore 导出文件时,系统会自动从自描述源数据中检索架构。
您可以通过以下方式指定表的架构:
- 使用 Google Cloud 控制台。
- 使用
CREATE TABLE
SQL 语句。 - 使用 bq 命令行工具以内嵌方式提供。
- 创建 JSON 格式的架构文件。
- 调用
jobs.insert
方法并在load
作业配置中配置schema
属性。 - 调用
tables.insert
方法并使用schema
属性在表资源中配置架构。
加载数据或创建空表之后,您可以修改表的架构定义。
架构组件
指定表架构时,必须提供每个列的名称和数据类型。您还可以提供列的说明、模式和默认值。
列名称
列名称可以包含字母(a-z、A-Z)、数字 (0-9) 或下划线 (_),并且必须以字母或下划线开头。如果使用灵活的列名称,则 BigQuery 支持列名称以数字开头。列以数字开头时要格外注意,因为将灵活的列名称与 BigQuery Storage Read API 或 BigQuery Storage Write API 搭配使用需要特殊处理。如需详细了解灵活的列名称支持,请参阅灵活的列名称。
列名称的长度不得超过 300 个字符。列名称不能使用以下任何前缀:
_TABLE_
_FILE_
_PARTITION
_ROW_TIMESTAMP
__ROOT__
_COLIDENTIFIER
列名不可重复,即使其大小写不同也不行。例如,名为 Column1
的列和名为 column1
的列被视作相同。如需详细了解列命名规则,请参阅 GoogleSQL 参考文档中的列名称。
如果表名称(例如 test
)与其一个列名称(例如 test
)相同,则 SELECT
表达式会将 test
列解释为包含所有其他表列的 STRUCT
。为避免此冲突,请使用以下方法之一:
避免表及其列使用相同的名称。
为表分配其他别名。例如,以下查询会为表
project1.dataset.test
分配表别名t
:SELECT test FROM project1.dataset.test AS t;
引用列时添加表名称。例如:
SELECT test.test FROM project1.dataset.test;
灵活的列名称
您可以更灵活地命名列,包括扩展使用非英语字符以及其他符号。
灵活的列名称支持以下字符:
- 任何语言的任何字母,由 Unicode 正则表达式
\p{L}
表示。 - 任何语言的任何数字字符,由 Unicode 正则表达式
\p{N}
表示。 - 任何由 Unicode 正则表达式
\p{Pc}
表示的连接器标点符号(包括下划线)。 - 连字符或短划线(由 Unicode 正则表达式
\p{Pd}
表示)。 - 任何伴随另一个字符的标记,由 Unicode 正则表达式
\p{M}
表示。例如重音符号、变音符号或括起的框。 - 以下特殊字符:
- 与符号 (
&
),由 Unicode 正则表达式\u0026
表示。 - 百分号 (
%
),由 Unicode 正则表达式\u0025
表示。 - 等号 (
=
),由 Unicode 正则表达式\u003D
表示。 - 加号 (
+
),由 Unicode 正则表达式\u002B
表示。 - 冒号 (
:
),由 Unicode 正则表达式\u003A
表示。 - 撇号 (
'
),由 Unicode 正则表达式\u0027
表示。 - 小于符号 (
<
),以 Unicode 正则表达式\u003C
表示。 - 大于符号 (
>
),由 Unicode 正则表达式\u003E
表示。 - 井号 (
#
),由 Unicode 正则表达式\u0023
表示。 - 竖线 (
|
),由 Unicode 正则表达式\u007c
表示。 - 空格。
- 与符号 (
灵活的列名称不支持以下特殊字符:
- 感叹号 (
!
),由 Unicode 正则表达式\u0021
表示。 - 引号 (
"
),由 Unicode 正则表达式\u0022
表示。 - 美元符号 (
$
),由 Unicode 正则表达式\u0024
表示。 - 左括号 (
(
),由 Unicode 正则表达式\u0028
表示。 - 右括号 (
)
),由 Unicode 正则表达式\u0029
表示。 - 星号 (
*
),由 Unicode 正则表达式\u002A
表示。 - 逗号 (
,
),由 Unicode 正则表达式\u002C
表示。 - 由 Unicode 正则表达式
\u002E
表示的英文句号 (.
)。 - 正斜杠 (
/
),由 Unicode 正则表达式\u002F
表示。 - 分号 (
;
),由 Unicode 正则表达式\u003B
表示。 - 问号 (
?
),由 Unicode 正则表达式\u003F
表示。 - 由 Unicode 正则表达式
\u0040
表示的 @ 符号 (@
)。 - 左方括号 (
[
),由 Unicode 正则表达式\u005B
表示。 - 反斜杠 (
\
),由 Unicode 正则表达式\u005C
表示。 - 右方括号 (
]
),由 Unicode 正则表达式\u005D
表示。 - 扬抑符 (
^
),由 Unicode 正则表达式\u005E
表示。 - 重音符号 (
`
),由 Unicode 正则表达式\u0060
表示。 - 左花括号 (
{
),由 Unicode 正则表达式\u007B
表示。 - 右花括号 (
}
),由 Unicode 正则表达式\u007D
表示。 - 波浪号 (
~
),由 Unicode 正则表达式\u007E
表示。
如需了解其他准则,请参阅列名称。
BigQuery Storage Read API 和 BigQuery Storage Write API 都支持扩展的列字符。如需将扩展的 Unicode 字符列表与 BigQuery Storage Read API 搭配使用,您必须设置标志。您可以使用 displayName
属性检索列名称。以下示例展示了如何使用 Python 客户端来设置标志:
from google.cloud.bigquery_storage import types
requested_session = types.ReadSession()
#set avro serialization options for flexible column.
options = types.AvroSerializationOptions()
options.enable_display_name_attribute = True
requested_session.read_options.avro_serialization_options = options
如需将扩展的 Unicode 字符列表与 BigQuery Storage Write API 搭配使用,您必须提供采用 column_name
表示法的架构,除非您使用的是 JsonStreamWriter
写入器对象。以下示例展示了如何提供架构:
syntax = "proto2";
package mypackage;
// Source protos located in github.com/googleapis/googleapis
import "google/cloud/bigquery/storage/v1/annotations.proto";
message FlexibleSchema {
optional string item_name_column = 1
[(.google.cloud.bigquery.storage.v1.column_name) = "name-列"];
optional string item_description_column = 2
[(.google.cloud.bigquery.storage.v1.column_name) = "description-列"];
}
在此示例中,item_name_column
和 item_description_column
是需要遵循协议缓冲区命名惯例的占位名称。请注意,column_name
注解始终优先于占位名称。
- 默认情况下,加载 Parquet 数据不支持灵活的列名称。如需注册此预览版,请填写注册表单。请注意,注册预览版后,任何无效的列名称(例如列名称排序规则)都将返回错误。对于未注册的项目,加载请求会将无效字符替换为下划线,而不是返回错误。
- 默认情况下,使用架构自动检测功能加载 CSV 数据不支持灵活的列名称。如需注册此预览版,请填写注册表单。请注意,注册预览版后,任何无效的列名称(例如列名称排序规则)都将返回错误。对于未注册的项目,加载请求会将无效字符替换为下划线,而不是返回错误。
外部表不支持灵活的列名称。
列说明
每个列均可以选择性地包含说明信息。说明就是一个字符串,长度最大为 1024 个字符。
默认值
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIME
CURRENT_TIMESTAMP
GENERATE_UUID
RAND
SESSION_USER
ST_GEOGPOINT
GoogleSQL 数据类型
GoogleSQL 使您可以在架构中指定以下数据类型。数据类型是必需的。
名称 | 数据类型 | 说明 |
---|---|---|
整数 | INT64 |
不带小数部分的数值 |
浮点 | FLOAT64 |
含有小数部分的近似数值 |
数字 | NUMERIC |
含有小数部分的精确数值 |
BigNumeric | BIGNUMERIC |
含有小数部分的精确数值 |
布尔值 | BOOL |
TRUE 或 FALSE(不区分大小写) |
字符串 | STRING |
长度可变的字符 (Unicode) 数据 |
字节 | BYTES |
长度可变的二进制数据 |
日期 | DATE |
逻辑日历日期 |
日期/时间 | DATETIME |
年、月、日、小时、分钟、秒和亚秒 |
时间 | TIME |
独立于具体日期的时间 |
时间戳 | TIMESTAMP |
绝对时间点,精度为微秒 |
结构体(记录) | STRUCT |
有序字段的容器,其中每个字段都有一个类型(必需)和字段名称(可选) |
地理位置 | GEOGRAPHY |
地球表面上的点集(WGS84 参考球体上的一组点、线和多边形,具有测地线边)。 |
JSON | JSON |
表示 JSON,一种轻量级数据交换格式 |
RANGE(预览版) | RANGE |
某个范围的 DATE 、DATETIME 或 TIMESTAMP 值 |
如需详细了解 GoogleSQL 中的数据类型,请参阅 GoogleSQL 数据类型。
您还可以在查询数据时声明数组类型。如需了解详情,请参阅使用数组。
模式
BigQuery 支持列的以下模式。模式是可选的。如果未指定模式,则列默认为 NULLABLE
。
模式 | 说明 |
---|---|
可以为 Null | 列允许 NULL 值(默认) |
必需 | 不允许使用 NULL 值 |
重复 | 列包含指定类型的值数组 |
如需详细了解模式,请参阅 TableFieldSchema
中的 mode
。
舍入模式
某列是 NUMERIC
或 BIGNUMERIC
类型时,您可以设置 rounding_mode
列选项,用于确定该列中的值在写入表时的舍入方式。您可以在顶级列或 STRUCT
字段上设置 rounding_mode
选项。支持以下舍入模式:
"ROUND_HALF_AWAY_FROM_ZERO"
:此模式(默认)会将中间数向远离 0 的方向舍入。"ROUND_HALF_EVEN"
:此模式会将中间数舍入到最接近的偶数。
您无法为非 NUMERIC
或 BIGNUMERIC
类型的列设置 rounding_mode
选项。如需详细了解这些类型,请参阅小数类型。
以下示例会创建表并插入基于列的舍入模式进行舍入的值:
CREATE TABLE mydataset.mytable ( x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'), y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO') ); INSERT mydataset.mytable (x, y) VALUES (NUMERIC "1.025", NUMERIC "1.025"), (NUMERIC "1.0251", NUMERIC "1.0251"), (NUMERIC "1.035", NUMERIC "1.035"), (NUMERIC "-1.025", NUMERIC "-1.025");
mytable
表如下所示:
+-------+-------+ | x | y | +-------+-------+ | 1.02 | 1.03 | | 1.03 | 1.03 | | 1.04 | 1.04 | | -1.02 | -1.03 | +-------+-------+
如需了解详情,请参阅 TableFieldSchema
中的 roundingMode
。
指定架构
在加载数据或创建空表时,您可以使用 Google Cloud 控制台或 bq 命令行工具指定表架构。加载 CSV 和 JSON(换行符分隔)文件时,系统支持指定架构。加载 Avro、Parquet、ORC、Firestore 导出数据或 Datastore 导出数据时,系统会自动从自描述源数据中检索架构。
如需指定表架构,请执行以下操作:
控制台
在 Google Cloud 控制台中,您可以使用添加字段选项或以文本形式修改选项指定架构。
在 Google Cloud 控制台中,打开 BigQuery 页面。
在浏览器面板中,展开您的项目并选择数据集。
展开
操作选项,然后点击打开。在详情面板中,点击创建表
。在创建表页面的来源部分,选择空白表。
在创建表页面的目标部分,执行以下操作:
在数据集名称部分,选择相应的数据集
在表名称字段中,输入您要创建的表的名称。
确认表类型是否设置为原生表。
在架构部分中,输入架构定义。
- 选项 1:使用添加字段并指定每个字段的名称、类型和模式。
- 选项 2:点击以文本形式修改,并以 JSON 数组的形式粘贴架构。使用 JSON 数组时,您要使用与创建 JSON 架构文件相同的流程生成架构。
点击创建表。
SQL
使用 CREATE TABLE
语句。使用列选项指定架构。以下示例将创建一个名为 newtable
的新表,其中包含 x、y、z 列,类型分别为整数、字符串、布尔值:
在 Google Cloud 控制台中,转到 BigQuery 页面。
在查询编辑器中,输入以下语句:
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL) OPTIONS( description = 'My example table');
点击
运行。
如需详细了解如何运行查询,请参阅运行交互式查询。
bq
使用以下任一命令,以内嵌方式提供采用 field:data_type,field:data_type
格式的架构:
- 如果要加载数据,请使用
bq load
命令。 - 如果要创建空表,请使用
bq mk
命令。
在命令行中指定架构时,您不能添加 RECORD
(STRUCT
) 或 RANGE
类型,不能添加列说明,也不能指定列的模式。所有模式均默认为 NULLABLE
。如需添加说明、模式、RECORD
类型和 RANGE
类型,请改为提供 JSON 架构文件。
如需使用内嵌架构定义将数据加载到表中,请输入 load
命令并使用 --source_format
标志指定数据格式。如果您要将数据加载到非默认项目内的表中,请按以下格式添加项目 ID:project_id:dataset.table_name
。
(可选)提供 --location
标志并将其值设置为您的位置。
bq --location=location load \ --source_format=format \ project_id:dataset.table_name \ path_to_source \ schema
请替换以下内容:
location
:您的位置名称。--location
是可选标志。例如,如果您在东京区域使用 BigQuery,可将该标志的值设置为asia-northeast1
。您可以使用 .bigqueryrc 文件设置位置的默认值。format
:NEWLINE_DELIMITED_JSON
或CSV
。project_id
:您的项目 ID。dataset
:您要向其中加载数据的表所属的数据集。table_name
:要向其中加载数据的表的名称。path_to_source
:本地机器或 Cloud Storage 中 CSV 或 JSON 数据文件的位置。schema
:内嵌架构定义。
例如:
输入以下命令,将名为 myfile.csv
的本地 CSV 文件中的数据加载到默认项目的 mydataset.mytable
中。该架构以内嵌方式指定。
bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING
如需详细了解如何将数据加载到 BigQuery 中,请参阅加载数据简介。
如需在创建空表时指定内嵌架构定义,请输入带 --table
或 -t
标志的 bq mk
命令。如果您要在非默认项目中创建表,请按以下格式将相应项目 ID 添加到命令中:project_id:dataset.table
。
bq mk --table project_id:dataset.table schema
替换以下内容:
project_id
:您的项目 ID。dataset
:项目中的数据集。table
:您要创建的表的名称。schema
:内嵌架构定义。
例如,以下命令在默认项目中创建名为 mytable
的空表。该架构以内嵌方式指定。
bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING
如需详细了解如何创建空表,请参阅创建具有架构定义的空表。
C#
要在将数据加载到表中时指定表架构,请执行以下操作:
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 C# 设置说明进行操作。如需了解详情,请参阅 BigQuery C# API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
要在创建空表时指定架构,请执行以下操作:
Go
要在将数据加载到表中时指定表架构,请执行以下操作:
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
要在创建空表时指定架构,请执行以下操作:
Java
要在将数据加载到表中时指定表架构,请执行以下操作:
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Java 设置说明进行操作。如需了解详情,请参阅 BigQuery Java API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
要在创建空表时指定架构,请执行以下操作:
Python
要在将数据加载到表中时指定表架构,请配置 LoadJobConfig.schema 属性。
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
要在创建空表时指定架构,请配置 Table.schema 属性。
指定 JSON 架构文件
如果愿意,您可以使用 JSON 架构文件而不是使用内嵌架构定义来指定架构。JSON 架构文件由一个 JSON 数组构成,该数组内包含如下内容:
- 列的名称
- 列的数据类型
- 可选:列的模式(如果未指定,模式默认设置为
NULLABLE
) - 可选:列的字段(如果是
STRUCT
类型) - 可选:列的说明
- 可选:列的政策标记,用于字段级访问权限控制
- 可选:列的
STRING
或BYTES
类型的最大长度 - 可选:列的
NUMERIC
或BIGNUMERIC
类型的精度 - 可选:列的
NUMERIC
或BIGNUMERIC
类型的比例 - 可选:列的
STRING
类型的排序规则 - 可选:列的默认值
- 可选:列的舍入模式(如果列是
NUMERIC
或BIGNUMERIC
类型)
创建 JSON 架构文件
如要创建 JSON 架构文件,请为每一列输入 TableFieldSchema
。name
和 type
为必填字段。所有其他字段都是选填字段。
[ { "name": string, "type": string, "mode": string, "fields": [ { object (TableFieldSchema) } ], "description": string, "policyTags": { "names": [ string ] }, "maxLength": string, "precision": string, "scale": string, "collation": string, "defaultValueExpression": string, "roundingMode": string }, { "name": string, "type": string, ... } ]
如果列是 RANGE<T>
类型,请使用 rangeElementType
字段描述 T
,其中 T
必须是 DATE
、DATETIME
或 TIMESTAMP
中的一个。
[ { "name": "duration", "type": "RANGE", "mode": "NULLABLE", "rangeElementType": { "type": "DATE" } } ]
JSON 数组由左括号和右括号(即 []
)表示。每个列条目必须用英文逗号 (},
) 分隔。
如需将现有表架构写入本地文件,请执行以下操作:
bq
bq show \ --schema \ --format=prettyjson \ project_id:dataset.table > path_to_file
请替换以下内容:
project_id
:您的项目 ID。dataset
:项目中的数据集。table
:现有表架构的名称。path_to_file
:您要将表架构写入到的本地文件的位置。
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
如需使用 Python 客户端库从表写入架构 JSON 文件,请调用 Client.schema_to_json 方法。您可以利用输出文件作为基础来创建自己的 JSON 架构文件。如果使用此方法,请确保该文件仅包含表示表架构的 JSON 数组。
例如,以下 JSON 数组表示基本表架构。此架构包含 3 列:qtr
(REQUIRED
STRING
)、rep
(NULLABLE
STRING
) 和 sales
(NULLABLE
FLOAT
)。
[ { "name": "qtr", "type": "STRING", "mode": "REQUIRED", "description": "quarter" }, { "name": "rep", "type": "STRING", "mode": "NULLABLE", "description": "sales representative" }, { "name": "sales", "type": "FLOAT", "mode": "NULLABLE", "defaultValueExpression": "2.55" } ]
使用 JSON 架构文件
创建 JSON 架构文件后,您可以使用 bq 命令行工具指定该文件。您不能将架构文件与 Google Cloud 控制台或 API 一起使用。
提供架构文件:
- 如果要加载数据,请使用
bq load
命令。 - 如果要创建空表,请使用
bq mk
命令。
提供 JSON 架构文件时,它必须存储在本地可读的位置。您不能指定存储在 Cloud Storage 或 Google 云端硬盘中的 JSON 架构文件。
加载数据时指定架构文件
如需使用 JSON 架构定义将数据加载到表中,请执行以下操作:
bq
bq --location=location load \ --source_format=format \ project_id:dataset.table \ path_to_data_file \ path_to_schema_file
请替换以下内容:
location
:您的位置名称。--location
是可选标志。例如,如果您在东京区域使用 BigQuery,可将该标志的值设置为asia-northeast1
。您可以使用 .bigqueryrc 文件设置位置的默认值。format
:NEWLINE_DELIMITED_JSON
或CSV
。project_id
:您的项目 ID。dataset
:您要向其中加载数据的表所属的数据集。table
:要向其中加载数据的表的名称。path_to_data_file
:本地机器或 Cloud Storage 中 CSV 或 JSON 数据文件的位置。path_to_schema_file
:本地机器上架构文件的路径。
例如:
输入以下命令,将名为 myfile.csv
的本地 CSV 文件中的数据加载到默认项目的 mydataset.mytable
中。架构是在当前目录的 myschema.json
中指定的。
bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
如需使用 Python 客户端库从 JSON 文件加载表架构,请调用 schema_from_json 方法。创建表时指定架构文件
如需使用 JSON 架构文件在现有数据集中创建空表,请执行以下操作:
bq
bq mk --table project_id:dataset.table path_to_schema_file
请替换以下内容:
project_id
:您的项目 ID。dataset
:项目中的数据集。table
:您要创建的表的名称。path_to_schema_file
:本地机器上架构文件的路径。
例如,以下命令在默认项目的 mydataset
中创建名为 mytable
的表。架构是在当前目录的 myschema.json
中指定的:
bq mk --table mydataset.mytable ./myschema.json
Python
试用此示例之前,请按照 BigQuery 快速入门:使用客户端库中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需向 BigQuery 进行身份验证,请设置应用默认凭据。 如需了解详情,请参阅为客户端库设置身份验证。
如需使用 Python 客户端库从 JSON 文件加载表架构,请调用 schema_from_json 方法。在 API 中指定架构
如需使用 API 指定表架构,请执行以下操作:
如需在加载数据时指定架构,请调用
jobs.insert
方法并在JobConfigurationLoad
资源中配置schema
属性。如需在创建表时指定架构,请调用
tables.insert
方法并在Table
资源中配置schema
属性。
使用 API 指定架构的过程类似于创建 JSON 架构文件的过程。
表安全性
如需控制对 BigQuery 中表的访问权限,请参阅表访问权限控制简介。