创建和使用表
本文档介绍了如何在 BigQuery 中创建和使用标准(内置)表。如需了解如何创建其他表类型,请参阅以下内容:
创建表后,您可执行以下操作:
- 控制对表数据的访问权限
- 获取有关表的信息
- 列出数据集中的表
- 获取表元数据
如需详细了解如何管理表(包括更新表属性、复制表和删除表),请参阅管理表。
准备工作
在 BigQuery 中创建表之前,请先进行以下操作:
- 按照 BigQuery 入门指南来设置项目。
- 创建 BigQuery 数据集。
- (可选)参阅表简介,了解表限制、配额和价格。
表命名
在 BigQuery 中创建表时,数据集中每个表的名称都必须具有唯一性。表名称可:
- 包含最多 1024 个字符。
- 包含类别 L(字母)、M(符号)、N(数字)、Pc(连接符,包括下划线)、Pd(短划线)、Zs(空格)中的 Unicode 字符。如需了解详情,请参阅常规类别。
例如,以下都是有效的表名称:table 01
、ग्राहक
、00_お客様
、étudiant-01
。
某些表名称和表名称前缀已被预留。如果您收到错误,表示您的表名称或前缀已被预留,请选择其他名称并重试。
创建表
您可以通过以下方式在 BigQuery 中创建表:
- 手动使用 Cloud Console 或
bq
命令行工具bq mk
命令。 - 调用
tables.insert
API 方法以编程方式创建。 - 使用客户端库。
- 使用查询结果。
- 通过定义引用外部数据源的表。
- 在加载数据时。
- 使用
CREATE TABLE
数据定义语言 (DDL) 语句。
所需权限
如需创建表,您需要拥有以下 IAM 权限:
bigquery.tables.create
bigquery.tables.updateData
bigquery.jobs.create
此外,您可能需要拥有 bigquery.tables.getData
权限才能访问您写入表中的数据。
以下每个预定义 IAM 角色都包含创建表所需的权限:
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(包括bigquery.jobs.create
权限)roles/bigquery.user
(包括bigquery.jobs.create
权限)roles/bigquery.jobUser
(包括bigquery.jobs.create
权限)
此外,如果您拥有 bigquery.datasets.create
权限,则可以在您创建的数据集中创建和更新表。
如需详细了解 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_1:value_1 \ --label key_2:value_2 \ project_id:dataset.table \ schema
请替换以下内容:
- integer 是表的默认生命周期(以秒为单位)。最小值为 3600 秒(一小时)。到期时间以当前世界协调时间 (UTC) 加上这个整数值为准。如果您在创建表时设置了该表的到期时间,则系统会忽略数据集的默认表到期时间设置。
- description 是加引号的表说明。
- key_1:value_1 和 key_2:value_2 是指定标签的键值对。
- 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 查询。
点击更多,然后选择查询设置。
选择为查询结果设置目标表选项。
在目标部分,选择要在其中创建表的数据集,然后选择表 ID。
在目标表的写入设置部分,选择以下选项之一:
- 只写入空白表 - 仅在表为空时才将查询结果写入表。
- 附加到表 - 将查询结果附加到现有表。
- 覆盖表 - 使用查询结果覆盖名称相同的现有表。
可选:对于数据位置,请选择您的位置。
要更新查询设置,请点击保存。
点击运行。这会创建一个查询作业,并将查询结果写入您指定的表中。
或者,如果您在运行查询之前忘记指定目标表,可以点击编辑器上方的保存结果按钮,将缓存结果表复制到永久表。
SQL
通过数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表。
例如,以下语句根据公共 bikeshare_trips
表中的数据创建名为 trips
的新表:
CREATE TABLE mydataset.trips AS SELECT bikeid, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips;
如需了解详情,请参阅 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 存储中也是如此。
BigQuery 支持以下外部数据源:
如需了解详情,请参阅外部数据源简介。
在加载数据时创建表
将数据加载到 BigQuery 时,可以将数据加载到新的表或分区中,也可以覆盖或附加到现有的表或分区。无需在加载数据前创建空表。您可以同时创建新表和加载数据。
将数据加载到 BigQuery 时,可以提供表或分区架构;对于支持的数据格式,可以使用架构自动检测。
要详细了解如何加载数据,请参阅将数据加载到 BigQuery 简介。
控制对表的访问权限
如需配置对表和视图的访问权限,您可以在以下级层为实体授予 IAM 角色,这些级层按照允许的资源范围从大到小依次排列:
- Google Cloud 资源层次结构中的较高级层,例如项目、文件夹或组织级层
- 数据集级层
- 表或视图级
您还可以使用以下方法限制表中的数据访问权限:
对受 IAM 保护的任何资源的访问权限具有附加性。例如,如果某个实体没有项目等较高级层的访问权限,您可以在数据集级层向该实体授予访问权限,然后该实体便将有权访问该数据集中的表和视图。同样,如果实体没有较高级层或数据集级层的访问权限,您可以在表或视图级层向该实体授予访问权限。
在 Google Cloud 资源层次结构中授予更高级层(例如项目、文件夹或组织级层)的 IAM 角色,使实体可访问一组广泛的资源。例如,在项目级为实体授予角色会为该实体提供整个项目中所有数据集的访问权限。
在数据集级层授予角色可指定允许实体对该特定数据集中的表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需详细了解如何配置数据集级层的访问权限控制,请参阅控制对数据集的访问权限。
在表或视图级层授予角色可指定允许实体对特定表和视图执行的操作,即使实体没有更高级层的访问权限也可执行这些操作。如需了解如何配置表级层的访问权限控制,请参阅控制对表和视图的访问权限。
您还可以创建 IAM 自定义角色。如果创建自定义角色,则您授予的权限取决于您希望实体能够执行的具体操作。
您无法对受 IAM 保护的任何资源设置“拒绝”权限。
如需详细了解角色和权限,请参阅 IAM 文档中的了解角色以及 BigQuery IAM 角色和权限。
获取表的相关信息
您可以通过以下方式获取表的相关信息或元数据:
- 使用 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
获取表信息
INFORMATION_SCHEMA
是一系列视图,可让您访问数据集、例程、表、视图、作业、预留、流式数据的相关元数据。
您可以查询以下视图以获取表信息:
- 使用
INFORMATION_SCHEMA.TABLES
和INFORMATION_SCHEMA.TABLE_OPTIONS
视图检索关于项目中的表和视图的元数据。 - 使用
INFORMATION_SCHEMA.COLUMNS
和INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
视图检索关于表中的列(字段)的元数据。 - 使用
INFORMATION_SCHEMA.TABLE_STORAGE
和INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_*
视图检索关于表当前和历史存储空间用量的元数据。
TABLES
和 TABLE_OPTIONS
视图还包含关于视图的概要信息。如需查看详细信息,请改为查询 INFORMATION_SCHEMA.VIEWS
视图。
TABLES
视图
查询 INFORMATION_SCHEMA.TABLES
视图时,查询结果为数据集中的每个表或视图返回一行。 如需详细了解视图,请改为查询 INFORMATION_SCHEMA.VIEWS
视图。
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 |
表的创建时间 |
ddl |
STRING |
可用于重新创建表的 DDL 语句,例如 CREATE TABLE 或 CREATE VIEW |
clone_time |
TIMESTAMP |
对于表克隆(预览版),此字段为克隆基表以创建该表的时间。如果使用时间旅行,则此字段包含时间旅行时间戳。否则,clone_time 字段与 creation_time 字段相同。仅适用于 table_type 设置为 CLONE 的表。 |
base_table_catalog |
STRING |
对于表克隆(预览版),此字段为基表的项目。仅适用于 table_type 设置为 CLONE 的表。 |
base_table_schema |
STRING |
对于表克隆(预览版),此字段为基表的数据集。仅适用于 table_type 设置为 CLONE 的表。 |
base_table_name |
STRING |
对于表克隆(预览版),此字段为基表的名称。仅适用于 table_type 设置为 CLONE 的表。 |
示例
示例 1:
以下示例会检索名为 mydataset
的数据集中所有表的表元数据。该查询会选择 INFORMATION_SCHEMA.TABLES
视图中除下面两列以外的所有列:is_typed
(该列留待将来使用)和 ddl
(从 SELECT *
查询中隐藏)。系统会返回默认项目中 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
使用 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 | ddl | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | CREATE TABLE `myproject.mydataset.mytable1` | | | | | | | | ( | | | | | | | | id INT64 | | | | | | | | ); | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | CREATE VIEW `myproject.mydataset.myview1` | | | | | | | | AS SELECT 100 as id; | +----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
示例 2:
以下示例从 INFORMATION_SCHEMA.TABLES
视图中检索类型为 BASE TABLE
的所有表。不包括 is_typed
列,并隐藏 ddl
列。系统会返回默认项目中 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
使用 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 | ddl |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
| myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-31 22:40:05 | CREATE TABLE myproject.mydataset.mytable1
|
| | | | | | | ( |
| | | | | | | id INT64 |
| | | | | | | ); |
+----------------+---------------+----------------+------------+--------------------+---------------------+---------------------------------------------+
示例 3:
以下示例从 census_bureau_usa
数据集中 population_by_zip_2010
表的 INFORMATION_SCHEMA.TABLES
视图中检索 table_name
和 ddl
列。此数据集是 BigQuery 公共数据集计划的一部分。
由于您查询的表属于其他项目,因此您应按以下格式将相应项目 ID 添加到数据集:`project_id`.dataset.INFORMATION_SCHEMA.view
。在此示例中,该值为 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_name="population_by_zip_2010"
点击运行。
bq
使用 bq query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT table_name, ddl FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES WHERE table_name="population_by_zip_2010"'
结果应如下所示:
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table_name | ddl | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010` | | | ( | | | geo_id STRING OPTIONS(description="Geo code"), | | | zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"), | | | population INT64 OPTIONS(description="The total count of the population for this segment."), | | | minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."), | | | maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), | | | gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.") | | | ) | | | OPTIONS( | | | labels=[("freebqcovid", "")] | | | ); | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
TABLE_OPTIONS
视图
当您查询 INFORMATION_SCHEMA.TABLE_OPTIONS
视图时,对于数据集中的每个表或视图,查询结果都会为每个选项返回一行结果。 如需详细了解视图,请改为查询 INFORMATION_SCHEMA.VIEWS
视图。
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 |
对于表执行的查询是否需要分区过滤条件 |
enable_refresh |
BOOL |
此标志表示是否为具体化视图启用自动刷新 |
refresh_interval_minutes |
FLOAT64 |
具体化视图的刷新频率 |
对于外部表,还可以使用以下选项:
选项 | |
---|---|
allow_jagged_rows |
如果为 适用于 CSV 数据。 |
allow_quoted_newlines |
如果为 适用于 CSV 数据。 |
compression |
数据源的压缩类型。支持的值包括: 适用于 CSV 和 JSON 数据。 |
enable_logical_types |
如果为 适用于 Avro 数据。 |
encoding |
数据的字符编码。支持的值包括: 适用于 CSV 数据。 |
field_delimiter |
CSV 文件中的字段的分隔符。 适用于 CSV 数据。 |
format |
外部数据的格式。支持的值包括: 值 |
decimal_target_types |
确定如何转换 示例: |
json_extension |
对于 JSON 数据,指示特定的 JSON 交换格式。如果未指定,BigQuery 会以通用 JSON 记录的形式读取数据。 支持的值包括: |
hive_partition_uri_prefix |
分区键编码开始之前所有源 URI 的通用前缀。仅适用于 Hive 分区的外部表。 适用于 Avro、CSV、JSON、Parquet、ORC 数据。 示例: |
ignore_unknown_values |
如果为 适用于 CSV 和 JSON 数据。 |
max_bad_records |
读取数据时要忽略的错误记录数上限。 适用于 CSV、JSON、表格数据。 |
null_marker |
表示 CSV 文件中 适用于 CSV 数据。 |
projection_fields |
要加载的实体属性的列表。 适用于 Datastore 数据。 |
quote |
用于括起 CSV 文件中数据部分的字符串。如果您的数据包含括起的换行符,另请将 适用于 CSV 数据。 |
require_hive_partition_filter |
如果为 适用于 Avro、CSV、JSON、Parquet、ORC 数据。 |
sheet_range |
要查询的表格的电子表格范围。 适用于表格数据。 示例: |
skip_leading_rows |
读取数据时要跳过的文件顶部行数。 适用于 CSV 和表格数据。 |
uris |
外部数据位置的完全限定 URI 数组。 示例: |
示例
示例 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
使用 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
使用 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
使用 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
使用 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 | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
TABLE_STORAGE
视图
INFORMATION_SCHEMA.TABLE_STORAGE
视图提供表和具体化视图的存储空间用量的当前快照。查询 INFORMATION_SCHEMA.TABLE_STORAGE
视图时,每个表或具体化视图都会有一行对应的查询结果。此表中的数据不会实时保留,并且可能会延迟几秒钟到几分钟。
视图数据是区域化的,因此您必须在针对视图的查询中使用区域限定符。如果您未指定项目,则使用默认项目。
以下示例展示了如何从项目或区域返回数据。
返回指定项目中的表的存储信息:
SELECT * FROM myProject.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;
返回指定区域中的表的存储信息:
SELECT * FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;
INFORMATION_SCHEMA.TABLE_STORAGE
视图具有如下架构:
列名 | 数据类型 | 值 |
---|---|---|
PROJECT_ID |
STRING |
该数据集所属项目的项目 ID |
PROJECT_NAME |
INT64 |
该数据集所属项目的项目编号 |
TABLE_SCHEMA |
STRING |
包含表或具体化视图的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或具体化视图的名称,也称为 tableId |
CREATION_TIME |
TIMESTAMP |
表的创建时间 |
TOTAL_ROWS |
INT64 |
表或具体化视图中的总行数 |
TOTAL_PARTITIONS |
INT64 |
表或具体化视图中存在的分区数量。未分区表会返回 0。 |
TOTAL_LOGICAL_BYTES |
INT64 |
表或具体化视图中的逻辑字节总数 |
ACTIVE_LOGICAL_BYTES |
INT64 |
存在时间不超过 90 天的逻辑字节数 |
LONG_TERM_LOGICAL_BYTES |
INT64 |
存在时间超过 90 天的逻辑字节数 |
TOTAL_PHYSICAL_BYTES |
INT64 |
用于存储的物理字节总数,包括活跃、长期和时间旅行(用于已删除的表)字节 |
ACTIVE_PHYSICAL_BYTES |
INT64 |
存在时间少于 90 天的物理字节数 |
LONG_TERM_PHYSICAL_BYTES |
INT64 |
存在时间超过 90 天的物理字节数 |
TIME_TRAVEL_PHYSICAL_BYTES |
INT64 |
时间旅行存储(删除或更改的数据)使用的物理字节数 |
示例
以下示例展示了组织中哪些项目目前使用的存储空间最多。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT project_id, SUM(total_logical_bytes) AS total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY project_id ORDER BY total_logical_bytes DESC;
点击运行。
bq
使用 bq query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT project_id, SUM(total_logical_bytes) AS total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY project_id ORDER BY total_logical_bytes DESC;'
结果应如下所示:
+---------------------+---------------------+ | project_id | total_logical_bytes | +---------------------+---------------------+ | projecta | 971329178274633 | +---------------------+---------------------+ | projectb | 834638211024843 | +---------------------+---------------------+ | projectc | 562910385625126 | +---------------------+---------------------+
TABLE_STORAGE_TIMELINE_BY_
* 视图
对于触发表的存储更改的每个事件(例如写入、更新或删除行),表存储时间轴视图会返回一行。也就是说,一个表在一天内可以有多个行。查询某个时间范围的视图时,请使用感兴趣的日期的最新时间戳。
以下是可用的表存储时间轴视图:
INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT
返回当前或指定项目中所有表的信息。INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
返回当前或指定项目的父文件夹中所有表的信息,包括其下的子文件夹中的项目。
视图数据是区域化的,因此您必须在针对视图的查询中使用区域限定符。
表存储时间轴视图具有以下架构:
列名 | 数据类型 | 值 |
---|---|---|
TIMESTAMP |
TIMESTAMP |
上次重新计算存储空间的时间的时间戳。对表中的数据进行更改会触发重新计算。 |
DELETED |
BOOLEAN |
指示表是否已删除 |
PROJECT_ID |
STRING |
该数据集所属项目的项目 ID |
PROJECT_NAME |
INT64 |
该数据集所属项目的项目编号 |
TABLE_SCHEMA |
STRING |
包含表或具体化视图的数据集的名称,也称为 datasetId |
TABLE_NAME |
STRING |
表或具体化视图的名称,也称为 tableId |
CREATION_TIME |
TIMESTAMP |
表的创建时间 |
TOTAL_ROWS |
INT64 |
表或具体化视图中的总行数 |
TOTAL_PARTITIONS |
INT64 |
表或具体化视图的分区数。未分区表将返回 0。 |
TOTAL_LOGICAL_BYTES |
INT64 |
表或具体化视图中的逻辑字节总数 |
ACTIVE_LOGICAL_BYTES |
INT64 |
存在时间不超过 90 天的逻辑字节数 |
LONG_TERM_LOGICAL_BYTES |
INT64 |
存在时间超过 90 天的逻辑字节数 |
TOTAL_PHYSICAL_BYTES |
INT64 |
用于存储的物理字节总数,包括活跃、长期和时间旅行(用于已删除的表)字节 |
ACTIVE_PHYSICAL_BYTES |
INT64 |
存在时间少于 90 天的物理字节数 |
LONG_TERM_PHYSICAL_BYTES |
INT64 |
存在时间超过 90 天的物理字节数 |
TIME_TRAVEL_PHYSICAL_BYTES |
INT64 |
时间旅行存储(删除或更改的数据)使用的物理字节数 |
示例
示例 1:
以下示例显示了特定数据集中哪些表使用的存储空间最多。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。SELECT timestamp AS start_time, table_name, total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT WHERE table_schema = "TABLE_SCHEMA" AND table_name = "TABLE_NAME" ORDER BY start_time DESC;
点击运行。
bq
使用 bq query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'SELECT timestamp AS start_time, table_name, total_logical_bytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_PROJECT WHERE table_schema = "TABLE_SCHEMA" AND table_name = "TABLE_NAME" ORDER BY start_time DESC;'
结果应如下所示:
------------------------+---------------------+----------------------+ | start_time | table_name | total_logical_bytes | +-----------------------+---------------------+----------------------+ | 2022-03-30 17:39:54 | table1 | 322 | | 2022-03-30 17:39:54 | table2 | 1657 | | 2022-03-30 17:39:53 | table1 | 320 | | 2022-03-30 17:39:53 | table2 | 1655 | +-----------------------+---------------------+----------------------+
示例 2:
以下示例显示了在给定时间点组织中每个项目使用的物理存储空间的总和。
要运行查询,请执行以下操作:
控制台
在 Cloud Console 中打开 BigQuery 页面。
在查询编辑器框中输入以下标准 SQL 查询。
INFORMATION_SCHEMA
要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。WITH most_recent_records as ( SELECT project_id, table_schema, table_name, MAX(timestamp) as max_timestamp FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION WHERE timestamp <= "TIMESTAMP" GROUP BY project_id, table_schema, table_name ) SELECT i_s.project_id, SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s JOIN most_recent_records ON i_s.project_id=most_recent_records.project_id AND i_s.table_schema=most_recent_records.table_schema AND i_s.table_name=most_recent_records.table_name AND i_s.timestamp = most_recent_records.max_timestamp GROUP BY project_id;
点击运行。
bq
使用 bq query
命令并通过 --nouse_legacy_sql
或 --use_legacy_sql=false
标志指定标准 SQL 语法。INFORMATION_SCHEMA
查询要求使用标准 SQL 语法。
如需运行查询,请输入以下命令:
bq query --nouse_legacy_sql \ 'WITH most_recent_records as ( SELECT project_id, table_schema, table_name, MAX(timestamp) as max_timestamp FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION WHERE timestamp <= "TIMESTAMP" GROUP BY project_id, table_schema, table_name ) SELECT i_s.project_id, SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s JOIN most_recent_records ON i_s.project_id=most_recent_records.project_id AND i_s.table_schema=most_recent_records.table_schema AND i_s.table_name=most_recent_records.table_name AND i_s.timestamp = most_recent_records.max_timestamp GROUP BY project_id;'
结果应如下所示:
-----------------+------------------------+ | project_id | TotalPhysicalBytes | +----------------+------------------------+ | projecta | 3844 | | projectb | 16022778 | | projectc | 8934009 | +----------------+------------------------+
列出数据集中的表
您可以通过以下方式列出数据集中的表:
- 使用 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 参考文档。
表安全性
如需控制对 BigQuery 中表的访问权限,请参阅表访问权限控制简介。
后续步骤
- 如需详细了解数据集,请参阅数据集简介。
- 如需详细了解如何处理表数据,请参阅管理表数据。
- 如需详细了解如何指定表架构,请参阅指定架构。
- 如需详细了解如何修改表架构,请参阅修改表架构。
- 如需详细了解如何管理表,请参阅管理表。
- 如需查看
INFORMATION_SCHEMA
的概览,请转到 BigQueryINFORMATION_SCHEMA
简介。
自行试用
如果您是 Google Cloud 新手,请创建一个帐号来评估 BigQuery 在实际场景中的表现。新客户还可获享 $300 赠金,用于运行、测试和部署工作负载。
免费试用 BigQuery