本文档介绍了如何在 BigQuery 中创建和使用标准(内置)表。如需了解如何创建其他表类型,请参阅以下内容:
创建表后,您可执行以下操作:
- 控制对表数据的访问权限
- 获取有关表的信息
- 列出数据集中的表
- 获取表元数据
如需详细了解如何管理表(包括更新表属性、复制表和删除表),请参阅管理表。
准备工作
在 BigQuery 中创建表之前,请先进行以下操作:
- 按照 BigQuery 入门指南来设置项目。
- 创建 BigQuery 数据集。
- (可选)参阅表简介,了解表限制、配额和价格。
表命名
在 BigQuery 中创建表时,数据集中每个表的名称都必须具有唯一性。表名称可:
- 包含最多 1024 个字符。
- 包含类别 L(字母)、M(符号)、N(数字)、Pc(连接符,包括下划线)、Pd(短划线)、Zs(空格)中的 Unicode 字符。如需了解详情,请参阅常规类别。
例如,以下都是有效的表名称:table-01
、ग्राहक
、00_お客様
、étudiant
。
创建表
您可以通过以下方式在 BigQuery 中创建表:
- 使用 Cloud Console 手动创建或使用
bq
命令行工具的bq mk
命令创建。 - 调用
tables.insert
API 方法以编程方式创建。 - 使用客户端库。
- 使用查询结果。
- 通过定义引用外部数据源的表。
- 在加载数据时。
- 使用
CREATE TABLE
DDL 语句创建。
所需权限
如需创建表,您至少必须具有以下权限:
bigquery.tables.create
:创建表的权限bigquery.tables.updateData
:使用加载作业、查询作业或复制作业向表中写入数据bigquery.jobs.create
:用于运行向表中写入数据的查询作业、加载作业或复制作业
如需访问向表中写入的数据,可能还需要其他权限,例如 bigquery.tables.getData
。
以下预定义的 IAM 角色同时包含 bigquery.tables.create
和 bigquery.tables.updateData
权限:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
以下预定义的 IAM 角色包含 bigquery.jobs.create
权限:
bigquery.user
bigquery.jobUser
bigquery.admin
此外,如果用户具有 bigquery.datasets.create
权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner
访问权限。借助 bigquery.dataOwner
访问权限,用户可以在数据集内创建和更新表。
如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅预定义的角色和权限。
创建具有架构定义的空表
您可以通过以下方式创建具有架构定义的空表:
- 使用 Cloud Console 输入架构
- 使用
bq
命令行工具以内嵌方式提供架构。 - 使用
bq
命令行工具提交 JSON 架构文件。 - 调用 API 的
tables.insert
方法时,在表资源中提供架构。
如需详细了解如何指定表架构,请参阅指定架构。
如需创建具有架构定义的空表,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在探索器面板中,展开您的项目并选择数据集。
在创建表页面的来源部分,选择空白表。
在创建表页面的目标部分,执行以下操作:
在数据集名称部分,选择相应数据集。
在表名称字段中,输入您要在 BigQuery 中创建的表的名称。
确认表类型设置为原生表。
在架构部分中,输入架构定义。
通过以下方式,手动输入架构信息:
启用以文本形式修改,并以 JSON 数组格式输入表架构。
使用添加字段手动输入架构。
对于分区和聚簇设置,保留默认值
No partitioning
。对于高级选项部分的加密,保留默认值
Google-managed key
。默认情况下,BigQuery 会对以静态方式存储的客户内容进行加密。点击创建表。
SQL
借助数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表和视图。
详细了解如何使用数据定义语言语句。
如需使用 DDL 语句在 Cloud Console 中创建表,请执行以下操作:
在 Cloud Console 中打开 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中,输入您的
CREATE TABLE
DDL 语句。以下查询会创建一个名为
newtable
的表,该表将于 2023 年 1 月 1 日到期。该表的说明是“a table that expires in 2023”(一个将在 2023 年到期的表),该表的标签是org_unit:development
。CREATE TABLE mydataset.newtable ( x INT64 OPTIONS(description="An optional INTEGER field"), y STRUCT< a ARRAY<STRING> OPTIONS(description="A repeated STRING field"), b BOOL > ) OPTIONS( expiration_timestamp=TIMESTAMP "2023-01-01 00:00:00 UTC", description="a table that expires in 2023", labels=[("org_unit", "development")] )
(可选)点击展开并选择查询设置。
(可选)对于处理位置,点击自动选择并选择数据的位置。如果您将处理位置保留设置为“未指定”,则系统会自动检测处理位置。
点击运行。查询完成后,该表将显示在资源窗格中。
bq
使用带有 --table
或 -t
标志的 bq mk
命令。您可以内嵌方式或通过 JSON 架构文件提供表的架构信息。可选参数包括:
--expiration
--description
--time_partitioning_type
--destination_kms_key
--label
。
此处未演示 --time_partitioning_type
和 --destination_kms_key
。如需详细了解 --time_partitioning_type
,请参阅提取时间分区表或分区表。如需详细了解 --destination_kms_key
,请参阅客户管理的加密密钥。
如果您要在非默认项目中创建表,请按以下格式将相应项目 ID 添加到数据集中:project_id:dataset
。
如需在具有架构定义的现有数据集中创建空表,请输入以下命令:
bq mk \ --table \ --expiration integer \ --description description \ --label key:value, key:value \ project_id:dataset.table \ schema
请替换以下内容:
- integer 是表的默认生命周期(以秒为单位)。最小值为 3600 秒(一小时)。到期时间以当前世界协调时间 (UTC) 加上这个整数值为准。如果您在创建表时设置了该表的到期时间,则系统会忽略数据集的默认表到期时间设置。
- description 是加引号的表说明。
- key:value 是代表标签的键值对。您可以使用英文逗号分隔列表输入多个标签。
- project_id 是项目 ID。
- dataset 是您的项目中的数据集。
- table 是您要创建的表的名称。
- schema 是采用 field:data_type,field:data_type 格式的内嵌架构定义,或者是本地机器上 JSON 架构文件的路径。
使用命令行指定架构时,您不能添加 RECORD
(STRUCT
) 类型和列说明,也不能指定列的模式。所有模式均默认为 NULLABLE
。如需添加说明、模式和 RECORD
类型,请改为提供 JSON 架构文件。
示例:
输入以下命令,使用内嵌架构定义创建表。该命令会在默认项目的 mydataset
中创建一个名为 mytable
的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table
,标签设为 organization:development
。该命令使用 -t
快捷键代替 --table
。该架构以内嵌方式指定为:qtr:STRING,sales:FLOAT,year:STRING
。
bq mk \
-t \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
mydataset.mytable \
qtr:STRING,sales:FLOAT,year:STRING
输入以下命令以使用 JSON 架构文件创建表。该命令会在默认项目的 mydataset
中创建一个名为 mytable
的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table
,标签设为 organization:development
。架构文件的路径为 /tmp/myschema.json
。
bq mk \
--table \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
mydataset.mytable \
/tmp/myschema.json
输入以下命令以使用 JSON 架构文件创建表。该命令会在 myotherproject
的 mydataset
中创建一个名为 mytable
的表。表到期时间设为 3600 秒(1 小时),说明设为 This is my table
,标签设为 organization:development
。架构文件的路径为 /tmp/myschema.json
。
bq mk \
--table \
--expiration 3600 \
--description "This is my table" \
--label organization:development \
myotherproject:mydataset.mytable \
/tmp/myschema.json
API
使用已定义的表资源调用 tables.insert
方法。
C#
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 C# 设置说明进行操作。如需了解详情,请参阅 BigQuery C# API 参考文档。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
PHP
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 PHP 设置说明进行操作。如需了解详情,请参阅 BigQuery PHP API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
Ruby
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Ruby 设置说明进行操作。如需了解详情,请参阅 BigQuery Ruby API 参考文档。
创建没有架构定义的空表
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
基于查询结果创建表
如需基于查询结果创建表,请将结果写入一个目标表。
控制台
在 Cloud Console 中打开 BigQuery 页面。
在探索器面板中,展开您的项目并选择数据集。
如果查询编辑器处于隐藏状态,请点击窗口右上角的显示编辑器。
在查询编辑器文本区域中输入有效的 SQL 查询。
点击更多,然后选择查询选项。
勾选为查询结果设置目标表复选框。
在目标位置部分中,选择要在其中创建表的相应项目名称和数据集名称,然后选择表名称。
在目标表的写入设置部分,选择以下选项之一:
- 只写入空白表 - 仅在表为空时才将查询结果写入表。
- 附加到表 - 将查询结果附加到现有表。
- 覆盖表 - 使用查询结果覆盖名称相同的现有表。
(可选)在处理位置部分中,点击自动选择并选择您的位置。
点击运行查询。这会创建一个查询作业,并将查询结果写入您指定的表中。
或者,如果您在运行查询之前忘记指定目标表,可以点击编辑器下方的保存结果按钮,将缓存结果表复制到永久表。
SQL
通过数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表。
如需了解详情,请参阅 CREATE TABLE
语句页面和 CREATE TABLE
示例:从现有表创建新表。
bq
输入 bq query
命令,并指定 --destination_table
标志以根据查询结果创建永久表。指定 use_legacy_sql=false
标志可使用标准 SQL 语法。如需将查询结果写入非默认项目中的某个表,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset
。
(可选)提供 --location
标志并将其值设置为您的位置。
如需控制现有目标表的写入处置方式,请指定以下可选标志之一:
--append_table
:如果目标表存在,指定该标志可将查询结果附加到该表。--replace
:如果目标表存在,指定该标志可使用查询结果覆盖该表。
bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
请替换以下内容:
location
是用于处理查询的位置的名称。--location
标志是可选的。例如,如果您在东京地区使用 BigQuery,可将该标志的值设置为asia-northeast1
。您可以使用.bigqueryrc
文件设置位置的默认值。project_id
是项目 ID。dataset
是数据集名称,该数据集包含您要向其中写入查询结果的表。table
是您要向其中写入查询结果的表的名称。query
是使用标准 SQL 语法的查询。
如果未指定“写入处置方式”标志,则默认行为是仅在表为空时写入结果。如果表已存在且非空,则系统会返回以下错误:`BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1': Already
Exists: Table project_id:dataset.table
。
示例:
输入以下命令可将查询结果写入 mydataset
中名为 mytable
的目标表。该数据集在默认项目中。由于命令中未指定“写入处置方式”标志,因此该表必须为新表或空表。否则,系统将返回 Already exists
错误。该查询从美国名字数据公共数据集中检索数据。
bq query \ --destination_table mydataset.mytable \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
输入以下命令可使用查询结果覆盖 mydataset
中名为 mytable
的目标表。该数据集在默认项目中。该命令使用 --replace
标志覆盖目标表。
bq query \ --destination_table mydataset.mytable \ --replace \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
输入以下命令可将查询结果附加到 mydataset
中名为 mytable
的目标表。该数据集属于 my-other-project
,而非默认项目。该命令使用 --append_table
标志将查询结果附加到目标表。
bq query \ --append_table \ --use_legacy_sql=false \ --destination_table my-other-project:mydataset.mytable \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
各示例的输出如下所示。为了方便阅读,部分输出已被截断。
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
如需将查询结果保存到永久表中,请调用 jobs.insert
方法,配置 query
作业,并添加 destinationTable
属性的值。如需控制现有目标表的写入处置方式,请配置 writeDisposition
属性。
如需控制查询作业的处理位置,请在作业资源的 jobReference
部分中指定 location
属性。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
如需将查询结果保存到永久表中,请在 QueryJobConfiguration 中将目标表设置为所需的 TableId。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
如需将查询结果保存到永久表中,请创建 QueryJobConfig 并将目标设置为所需的 TableReference。然后,将作业配置传递给查询方法。创建引用外部数据源的表
外部数据源(也称为“联合数据源”)是可供直接查询的数据源,即使数据未存储在 BigQuery 中也是如此。您不用加载或流式传输数据,只需创建一个引用外部数据源的表即可。
BigQuery 支持直接查询以下各项中的数据:
支持的格式包括:
- Avro
- CSV
- JSON(仅限换行符分隔格式)
- ORC
- Parquet
您可以在支持的外部数据源中查询数据,方法是创建一个临时或永久表,该表引用外部数据源中存储的数据。如需详细了解如何使用外部数据源,请参阅:
在加载数据时创建表
将数据加载到 BigQuery 时,可以将数据加载到新的表或分区中,也可以覆盖或附加到现有的表或分区。无需在加载数据前创建空表。您可以同时创建新表和加载数据。
将数据加载到 BigQuery 时,可以提供表或分区架构;对于支持的数据格式,可以使用架构自动检测。
要详细了解如何加载数据,请参阅将数据加载到 BigQuery 简介。
控制表的访问权限
如需配置对表和视图的访问权限,您可以在以下级层为实体授予 IAM 角色,这些级层按照允许的资源范围从大到小依次排列:
- Google Cloud 资源层次结构中的较高级层,例如项目、文件夹或组织级层
- 数据集级层
- 表/视图级层
对受 IAM 保护的任何资源的访问权限具有附加性。例如,如果某个实体没有项目等较高级层的访问权限,您可以在数据集级层向该实体授予访问权限,然后该实体便将有权访问该数据集中的表和视图。同样,如果实体没有较高级层或数据集级层的访问权限,您可以在表或视图级层向该实体授予访问权限。
在 Google Cloud 资源层次结构中授予更高级层(例如项目、文件夹或组织级层)的 IAM 角色,使实体可访问一组广泛的资源。例如,在项目级为实体授予角色会为该实体提供整个项目中所有数据集的访问权限。
在数据集级层授予角色可指定允许实体对该特定数据集中的表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需详细了解如何配置数据集级层的访问权限控制,请参阅控制对数据集的访问权限。
在表或视图级层授予角色可指定允许实体对特定表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需了解如何配置表级层的访问权限控制,请参阅控制对表和视图的访问权限。
您还可以创建 IAM 自定义角色。如果创建自定义角色,则您授予的权限取决于您希望实体能够执行的具体操作。
您无法对受 IAM 保护的任何资源设置“拒绝”权限。
如需详细了解角色和权限,请参阅:
- IAM 文档中的了解角色
- BigQuery 预定义的角色和权限
- 控制对数据集的访问
- 控制对表和视图的访问权限
- 通过 BigQuery 列级层安全性限制访问权限
使用表
获取表的相关信息
您可以通过以下方式获取表的相关信息或元数据:
- 使用 Cloud Console。
- 使用
bq
命令行工具的bq show
命令。 - 调用
tables.get
API 方法。 - 使用客户端库。
- 查询
INFORMATION_SCHEMA
视图(Beta 版。
所需权限
如需获取有关表的信息,您至少必须获得 bigquery.tables.get
权限。以下预定义的 IAM 角色包含 bigquery.tables.get
权限:
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
此外,如果用户具有 bigquery.datasets.create
权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner
访问权限。借助 bigquery.dataOwner
访问权限,用户可以检索表元数据。
如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。
获取表信息
如需获取表的相关信息,请执行以下操作:
Console
在导航面板的资源部分中,展开您的项目并选择数据集。点击数据集名称,将其展开。此时会显示数据集中的表和视图。
点击表名称。
在编辑器下方,点击详细信息。此页面会显示表说明和表信息。
点击 Schema 标签页,查看表的架构定义。
bq
发出 bq show
命令可显示所有表信息。使用 --schema
标志可仅显示表的架构信息。--format
标志可用于控制输出。
如果您要获取非默认项目中的表信息,请按以下格式将相应项目 ID 添加到数据集:project_id:dataset
。
bq show \ --schema \ --format=prettyjson \ project_id:dataset.table
其中:
- project_id 是项目 ID。
- dataset 是数据集的名称。
- table 是表的名称。
示例:
输入以下命令可显示 mydataset
中有关 mytable
的所有信息。mydataset
属于默认项目。
bq show --format=prettyjson mydataset.mytable
输入以下命令可显示 mydataset
中有关 mytable
的所有信息。mydataset
在 myotherproject
中,不在默认项目中。
bq show --format=prettyjson myotherproject:mydataset.mytable
输入以下命令可仅显示 mydataset
中有关 mytable
的架构信息。mydataset
属于 myotherproject
,而非默认项目。
bq show --schema --format=prettyjson myotherproject:mydataset.mytable
API
调用 tables.get
方法并提供所有相关参数。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
PHP
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 PHP 设置说明进行操作。如需了解详情,请参阅 BigQuery PHP API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
使用 INFORMATION_SCHEMA
获取表信息(Beta 版)
INFORMATION_SCHEMA
是一系列视图,可让您访问数据集、例程、表、视图、作业、预留和流式数据的相关元数据。
您可以查询 INFORMATION_SCHEMA.TABLES
和 INFORMATION_SCHEMA.TABLE_OPTIONS
视图,检索关于项目中的表和视图的元数据。您还可以查询 INFORMATION_SCHEMA.COLUMNS
和 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
视图,检索关于表中的列(字段)的元数据。
TABLES
和 TABLE_OPTIONS
视图还包含关于视图的概要信息。如需查看详细信息,请改为查询 INFORMATION_SCHEMA.VIEWS
视图。
TABLES
视图
查询 INFORMATION_SCHEMA.TABLES
视图时,查询结果为数据集中的每个表或视图返回一行。
INFORMATION_SCHEMA.TABLES
视图具有如下架构:
列名 | 数据类型 | 值 |
---|---|---|
TABLE_CATALOG |
STRING |
该数据集所属项目的项目 ID |
TABLE_SCHEMA |
STRING |
包含表或视图的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或视图的名称,也称为 tableId |
TABLE_TYPE |
STRING |
表类型: |
IS_INSERTABLE_INTO |
STRING |
YES 或 NO ,具体取决于表是否支持 DML INSERT 语句 |
IS_TYPED |
STRING |
值始终为 NO |
CREATION_TIME |
TIMESTAMP |
表的创建时间 |
示例
示例 1:
以下示例会检索名为 mydataset
的数据集中所有表的表元数据。该查询从 INFORMATION_SCHEMA.TABLES
视图中选择 is_typed
(该列留待将来使用)以外的所有列。系统会返回默认项目 myproject
中 mydataset
的所有表的元数据。
mydataset
包含以下表:
mytable1
:标准 BigQuery 表myview1
:BigQuery 视图
如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'
结果应如下所示:
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | +----------------+---------------+----------------+------------+--------------------+---------------------+
示例 2:
以下示例从 INFORMATION_SCHEMA.TABLES
视图中检索类型为 BASE TABLE
的所有表。不包括 is_typed
列。返回的元数据包括默认项目 myproject
的 mydataset
中的所有表。
如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"'
结果应如下所示:
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | NO | 2018-10-31 22:40:05 | +----------------+---------------+----------------+------------+--------------------+---------------------+
TABLE_OPTIONS
视图
查询 INFORMATION_SCHEMA.TABLE_OPTIONS
视图时,查询结果为数据集中的每个表或视图返回一行。
INFORMATION_SCHEMA.TABLE_OPTIONS
视图具有如下架构:
列名 | 数据类型 | 值 |
---|---|---|
TABLE_CATALOG |
STRING |
该数据集所属项目的项目 ID |
TABLE_SCHEMA |
STRING |
包含表或视图的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或视图的名称,也称为 tableId |
OPTION_NAME |
STRING |
选项表中的一个名称值 |
OPTION_TYPE |
STRING |
选项表中的一个数据类型值 |
OPTION_VALUE |
STRING |
选项表中的一个值选项 |
选项表
OPTION_NAME |
OPTION_TYPE |
OPTION_VALUE |
---|---|---|
partition_expiration_days |
FLOAT64 |
分区表中所有分区的默认生命周期(以天为单位) |
expiration_timestamp |
FLOAT64 |
此表的到期时间 |
kms_key_name |
STRING |
用于加密表的 Cloud KMS 密钥的名称 |
friendly_name |
STRING |
表的描述性名称 |
description |
STRING |
表的说明 |
labels |
ARRAY<STRUCT<STRING, STRING>> |
一组 STRUCT ,表示表的标签 |
require_partition_filter |
BOOL |
对于表执行的查询是否需要分区过滤条件 |
示例
示例 1:
以下示例通过查询 INFORMATION_SCHEMA.TABLE_OPTIONS
视图来检索默认项目 (myproject
) 中的 mydataset
中的所有表的默认表到期时间。
如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"'
结果应如下所示:
+----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | myproject | mydataset | mytable1 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2020-01-16T21:12:28.000Z" | | myproject | mydataset | mytable2 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2021-01-01T21:12:28.000Z" | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
示例 2:
以下示例检索 mydataset
中包含测试数据的所有表的元数据。该查询使用 description
选项中的值查找在说明的任何位置包含“test”的表。mydataset
位于默认项目 myproject
中。
如需对非默认项目运行查询,请按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应的项目 ID 添加到数据集,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"'
结果应如下所示:
+----------------+---------------+------------+-------------+-------------+--------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+-------------+-------------+--------------+ | myproject | mydataset | mytable1 | description | STRING | "test data" | | myproject | mydataset | mytable2 | description | STRING | "test data" | +----------------+---------------+------------+-------------+-------------+--------------+
COLUMNS
视图
查询 INFORMATION_SCHEMA.COLUMNS
视图时,查询为表中的每一列(字段)返回一行结果。
INFORMATION_SCHEMA.COLUMNS
视图具有如下架构:
列名 | 数据类型 | 值 |
---|---|---|
TABLE_CATALOG |
STRING |
该数据集所属项目的项目 ID |
TABLE_SCHEMA |
STRING |
包含表的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或视图的名称,也称为 tableId |
COLUMN_NAME |
STRING |
列的名称 |
ORDINAL_POSITION |
INT64 |
表中列的偏移量,从 1 开始计数;如果列为伪列(例如 _PARTITIONTIME 或 _PARTITIONDATE),则值为 NULL |
IS_NULLABLE |
STRING |
YES 或 NO ,具体取决于列的模式是否允许使用 NULL 值 |
DATA_TYPE |
STRING |
列的标准 SQL 数据类型 |
IS_GENERATED |
STRING |
值始终为 NEVER |
GENERATION_EXPRESSION |
STRING |
值始终为 NULL |
IS_STORED |
STRING |
值始终为 NULL |
IS_HIDDEN |
STRING |
YES 或 NO ,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE |
IS_UPDATABLE |
STRING |
值始终为 NULL |
IS_SYSTEM_DEFINED |
STRING |
YES 或 NO ,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE |
IS_PARTITIONING_COLUMN |
STRING |
YES 或 NO ,具体取决于列是否为分区列 |
CLUSTERING_ORDINAL_POSITION |
INT64 |
表的聚簇列中列的偏移量,从 1 开始计数;如果表不是聚簇表,则值为 NULL |
示例
以下示例从 census_bureau_usa
数据集内 population_by_zip_2010
表的 INFORMATION_SCHEMA.COLUMNS
视图中检索元数据。此数据集是 BigQuery 公共数据集计划的一部分。
由于您查询的表属于 bigquery-public-data
项目,因此您应按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应项目 ID 添加到数据集;例如 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
。
以下列会从查询结果中排除,因为它们目前预留供将来使用:
IS_GENERATED
GENERATION_EXPRESSION
IS_STORED
IS_UPDATABLE
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"'
结果应如下所示。为确保可读性,table_catalog
和 table_schema
会从结果中排除:
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | table_name | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | population_by_zip_2010 | zipcode | 1 | NO | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | geo_id | 2 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | minimum_age | 3 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | maximum_age | 4 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | gender | 5 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | population | 6 | YES | INT64 | NO | NO | NO | NULL | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
COLUMN_FIELD_PATHS
视图
当您查询 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
视图时,查询为嵌套在 RECORD
(或 STRUCT
)列中的每一列返回一行结果。
INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
视图具有如下架构:
列名 | 数据类型 | 值 |
---|---|---|
TABLE_CATALOG |
STRING |
该数据集所属项目的项目 ID |
TABLE_SCHEMA |
STRING |
包含表的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或视图的名称,也称为 tableId |
COLUMN_NAME |
STRING |
列的名称 |
FIELD_PATH |
STRING |
嵌套在 `RECORD` 或 `STRUCT` 列中的列的路径 |
DATA_TYPE |
STRING |
列的标准 SQL 数据类型 |
DESCRIPTION |
STRING |
列的说明 |
示例
以下示例从 github_repos
数据集内 commits
表的 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
视图中检索元数据。此数据集是 BigQuery 公共数据集计划的一部分。
由于您查询的表属于 bigquery-public-data
项目,因此您应按 `project_id`.dataset.INFORMATION_SCHEMA.view
格式将相应项目 ID 添加到数据集;例如 `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
。
commits
表包含以下嵌套列以及嵌套和重复列:
author
:嵌套的RECORD
列committer
:嵌套的RECORD
列trailer
:嵌套且重复的RECORD
列difference
:嵌套且重复的RECORD
列
查询将检索有关 author
和 difference
列的元数据。
如需运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"
点击运行。
bq
使用 query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"'
结果应如下所示。为确保可读性,table_catalog
和 table_schema
会从结果中排除。
+------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | table_name | column_name | field_path | data_type | description | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | commits | author | author | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP> | NULL | | commits | author | author.name | STRING | NULL | | commits | author | author.email | STRING | NULL | | commits | author | author.time_sec | INT64 | NULL | | commits | author | author.tz_offset | INT64 | NULL | | commits | author | author.date | TIMESTAMP | NULL | | commits | difference | difference | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL | | commits | difference | difference.old_mode | INT64 | NULL | | commits | difference | difference.new_mode | INT64 | NULL | | commits | difference | difference.old_path | STRING | NULL | | commits | difference | difference.new_path | STRING | NULL | | commits | difference | difference.old_sha1 | STRING | NULL | | commits | difference | difference.new_sha1 | STRING | NULL | | commits | difference | difference.old_repo | STRING | NULL | | commits | difference | difference.new_repo | STRING | NULL | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
列出数据集中的表
您可以通过以下方式列出数据集中的表:
- 使用 Cloud Console。
- 使用
bq
命令行工具的bq ls
命令。 - 调用
tables.list
API 方法。 - 使用客户端库。
所需权限
如需列出数据集中的表,您至少必须具有 bigquery.tables.list
权限。以下预定义 IAM 角色包含 bigquery.tables.list
权限:
bigquery.user
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。
列出表
如需列出数据集中的表,请执行以下操作:
Console
在 Cloud Console 的导航窗格中,点击您的数据集将其展开。此时会显示数据集中的表和视图。
滚动列表,查看数据集中的表。表和视图由不同的图标进行标识。
bq
发出 bq ls
命令。--format
标志可用于控制输出。如果您要列出非默认项目中的表,请按以下格式将相应项目 ID 添加到数据集中:project_id:dataset
。
您还可以使用其他标志,包括:
--max_results
或-n
:用于表示结果数量上限的整数,默认值为50
。
bq ls \ --format=pretty \ --max_results integer \ project_id:dataset
其中:
- integer 是一个整数,表示要列出的表的数量。
- project_id 是您的项目 ID。
- dataset 是数据集的名称。
运行该命令时,Type
字段会显示 TABLE
或 VIEW
。例如:
+-------------------------+-------+----------------------+-------------------+ | tableId | Type | Labels | Time Partitioning | +-------------------------+-------+----------------------+-------------------+ | mytable | TABLE | department:shipping | | | myview | VIEW | | | +-------------------------+-------+----------------------+-------------------+
示例:
输入以下命令可列出默认项目的数据集 mydataset
中的表。
bq ls --format=pretty mydataset
输入以下命令可从 mydataset
返回超出默认输出数量(50 个)的表。mydataset
位于您的默认项目中。
bq ls --format=pretty --max_results 60 mydataset
输入以下命令可列出 myotherproject
的数据集 mydataset
中的表。
bq ls --format=pretty myotherproject:mydataset
API
如需使用 API 列出表,请调用 tables.list
方法。
C#
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 C# 设置说明进行操作。如需了解详情,请参阅 BigQuery C# API 参考文档。
Go
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档。
Java
试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Java 设置说明进行操作。 如需了解详情,请参阅 BigQuery Java API 参考文档。
Node.js
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Node.js 设置说明进行操作。如需了解详情,请参阅 BigQuery Node.js API 参考文档。
PHP
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 PHP 设置说明进行操作。如需了解详情,请参阅 BigQuery PHP API 参考文档。
Python
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档。
Ruby
在尝试此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Ruby 设置说明进行操作。如需了解详情,请参阅 BigQuery Ruby API 参考文档。
后续步骤
- 如需详细了解数据集,请参阅数据集简介。
- 如需详细了解如何处理表数据,请参阅管理表数据。
- 如需详细了解如何指定表架构,请参阅指定架构。
- 如需详细了解如何修改表架构,请参阅修改表架构。
- 如需详细了解如何管理表,请参阅管理表。
- 如需查看
INFORMATION_SCHEMA
的概览,请转到 BigQueryINFORMATION_SCHEMA
简介。