通过数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改 BigQuery 资源。目前,您可以在 BigQuery 中使用 DDL 命令执行以下操作:
运行 DDL 语句
如需运行 DDL 语句,您可以使用 Cloud Console、bq
命令行工具、调用 jobs.query
REST API 或以编程方式使用 BigQuery API 客户端库。
控制台
转到 Cloud Console 中的 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中,输入 DDL 语句。例如:
CREATE TABLE mydataset.newtable ( x INT64 )
点击运行。
bq
输入 bq query
命令并提供 DDL 语句作为查询参数。将 use_legacy_sql
标志设置为 false
。
bq query --use_legacy_sql=false \ 'CREATE TABLE mydataset.newtable ( x INT64 )'
API
调用 jobs.query
方法并在请求正文的 query
属性中提供 DDL 语句。
DDL 功能会扩展作业资源返回的信息。statistics.query.statementType
包含以下其他值以提供 DDL 支持:
CREATE_TABLE
CREATE_TABLE_AS_SELECT
DROP_TABLE
CREATE_VIEW
DROP_VIEW
statistics.query
有 2 个附加字段:
ddlOperationPerformed
:执行的 DDL 操作,可能取决于是否存在 DDL 目标。当前值包括:CREATE
:查询创建了 DDL 目标。SKIP
:无操作发生。示例 - 提交了CREATE TABLE IF NOT EXISTS
语句,而表已存在。或者提交了DROP TABLE IF EXISTS
语句,而表不存在。REPLACE
:查询替换了 DDL 目标。示例 - 提交了CREATE OR REPLACE TABLE
语句,而表已存在。DROP
:查询删除了 DDL 目标。
ddlTargetTable
:当您提交CREATE TABLE/VIEW
语句或DROP TABLE/VIEW
语句时,以包含 3 个字段的对象形式返回目标表:- “projectId”:字符串
- “datasetId”:字符串
- “tableId”:字符串
Java
调用 BigQuery.create()
方法,启动查询作业。调用 Job.waitFor()
方法,等待 DDL 查询完成。
Node.js
Python
调用 Client.query()
方法,启动查询作业。调用 QueryJob.result()
方法,等待 DDL 查询完成。
CREATE TABLE
语句
如需在 BigQuery 中创建表,请使用 CREATE TABLE
DDL 语句。
{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} [[project_name.]dataset_name.]table_name [( column_name column_schema[, ...] )] [PARTITION BY partition_expression] [CLUSTER BY clustering_column_list] [OPTIONS(table_option_list)] [AS query_statement]
其中:
{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
是下述语句之一:
CREATE TABLE
:创建新表。CREATE TABLE IF NOT EXISTS
:仅当指定的数据集中不存在要创建的表时才创建此表。CREATE OR REPLACE TABLE
:在指定的数据集中创建表并替换具有相同名称的现有表。
CREATE TABLE
语句必须符合以下规则:
- 只允许一个
CREATE
语句。 - 必须存在列列表和/或
as query_statement
子句。 - 当列列表和
as query_statement
子句都存在时,BigQuery 会忽略as query_statement
子句中的名称,并按位置匹配列和列列表。 - 存在
as query_statement
子句但不存在列列表时,BigQuery 会根据as query_statement
子句确定列名称和类型。 - 必须通过列列表或
as query_statement
子句指定列名称。 - 禁止使用重复的列名称。
表路径
project_name
是您要在其中创建表的项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是您要在其中创建表的数据集的名称。
默认为请求中的 defaultDataset
。
table_name
是您要创建的表的名称。
在 BigQuery 中创建表时,每个数据集的表名称必须是唯一的。表名称可:
- 包含最多 1024 个字符。
- 包含类别 L(字母)、M(符号)、N(数字)、Pc(连接符,包括下划线)、Pd(短划线)、Zs(空格)中的 Unicode 字符。如需了解详情,请参阅常规类别。
例如,以下都是有效的表名称:table-01
、ग्राहक
、00_お客様
、étudiant
。
某些表名称和表名称前缀已被预留。如果您收到错误,表示您的表名称或前缀已被预留,请选择其他名称并重试。
“column_name
”和“column_schema
”
(column_name column_schema[, ...])
以英文逗号分隔列表的形式提供表的架构信息:
column_name
是列的名称。列名称要求:- 只能包含字母(a-z、A-Z)、数字 (0-9) 或下划线 (_)
- 必须以字母或下划线开头
- 最多包含 128 个字符
column_schema
与数据类型相似,但对于ARRAY
以外的类型,支持视需要施加NOT NULL
限制。column_schema
还支持顶级列与STRUCT
字段的选项。
column_schema := {simple_type [NOT NULL] | STRUCT<field_list> [NOT NULL] | ARRAY<array_element_schema>} [OPTIONS(column_option_list)] field_list := field_name column_schema [, ...] array_element_schema := {simple_type | STRUCT<field_list>} [NOT NULL]
simple_type
是除 STRUCT
和 ARRAY
之外的任何受支持的数据类型。
field_name
是结构体字段的名称。结构体字段名称具有与列名称相同的限制。
当列或字段存在 NOT NULL
限制时,按 REQUIRED
模式创建列或字段。相反,没有 NOT NULL
限制时,按 NULLABLE
模式创建列或字段。
ARRAY
类型的列和字段不支持 NOT NULL
修饰符。例如,ARRAY<INT64> NOT NULL
的 column_schema
无效,因为 ARRAY
列具有 REPEATED
模式并可为空,但不可为 NULL
。无论是否指定 NOT NULL
限制,表中的数组元素都不能为 NULL
。例如,ARRAY<INT64>
等同于 ARRAY<INT64 NOT NULL>
。
表的 column_schema
的 NOT NULL
特性 (Attribute) 不会通过对表的查询来传播。例如,如果表 T
包含声明为 x INT64 NOT NULL
的列,CREATE TABLE dataset.newtable AS SELECT x FROM T
会创建名为 dataset.newtable
的表,其中 x
是 NULLABLE
。
column_schema
只能在 CREATE TABLE
语句的列定义列表中使用,不能在表达式中作为类型使用。例如,CAST(1 AS INT64 NOT NULL)
无效。
partition_expression
PARTITION BY
是控制表分区的可选子句。partition_expression
是确定如何对表进行分区的表达式。分区表达式可包含以下值:
PARTITION BY DATE(_PARTITIONTIME)
:使用_PARTITIONTIME pseudo column
中基于日期的时间戳对表进行分区。此语法只支持用于不带AS query_statement
子句的CREATE TABLE
。PARTITION BY _PARTITIONDATE
:使用_PARTITIONDATE pseudo column
对表进行分区。此语法只支持用于不带AS query_statement
子句的CREATE TABLE
,等同于使用PARTITION BY DATE(_PARTITIONTIME)
。PARTITION BY DATE(<timestamp_column>)
:使用TIMESTAMP
列的日期对表进行分区PARTITION BY RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(start, end[, interval]))
:使用指定的INTEGER
列范围对表进行分区。start
是范围分区的起始值(含边界值),end
是范围分区的结束值(不含边界值),interval
是分区中每个范围的宽度。 如果未设置,则默认为 1。PARTITION BY TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
:使用TIMESTAMP
列的日期/小时/月/年对表进行分区PARTITION BY DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR })
:使用DATETIME
列的日期/小时/月/年对表进行分区PARTITION BY <date_column>
:使用DATE
列对表进行分区
clustering_column_list
CLUSTER BY
是控制表聚簇的可选子句。clustering_column_list
是确定如何对表进行聚簇的英文逗号分隔列表。聚簇列列表最多可包含 4 个聚簇列。
table_option_list
可通过选项列表设置标签和到期时间等表选项。可使用逗号分隔列表包括多个选项。
请按以下格式指定表选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
partition_expiration_days |
|
示例: 此属性等效于 timePartitioning.expirationMs 表资源属性,但单位是天而非毫秒。一天相当于 8640 万毫秒(即 24 小时)。 只能对分区表设置此属性。 |
require_partition_filter |
|
示例: 此属性等效于 timePartitioning.requirePartitionFilter 表资源属性。 只能对分区表设置此属性。 |
kms_key_name |
|
示例: 此属性等效于 encryptionConfiguration.kmsKeyName 表资源属性。 请查看有关使用 Cloud KMS 密钥保护数据的详细信息。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
VALUE
是一个只包含文字、查询参数和标量函数的常数表达式。如果常量表达式的计算结果为 null
,则忽略对应的 NAME
选项。
常量表达式不得包含以下内容:
- 对表的引用
- 子查询和 SQL 语句,如
SELECT
、CREATE
和UPDATE
- 用户定义的函数、聚合函数或分析函数
- 以下标量函数:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
column_option_list
通过 column_schema
中的 column_option_list
,您可以指定可选的列或字段选项。列选项与表选项的语法和要求相同,但包含的 NAME
和 VALUE
列表不同:
NAME |
VALUE |
详细信息 |
---|---|---|
description |
|
示例: 此属性等效于 schema.fields[].description 表资源属性。 |
query_statement
AS query_statement
子句指定应从其中创建表的查询。如需了解受支持的 query_statement
形式,请参阅 SQL 语法参考。
已知限制:
- 无法利用查询结果创建提取时间分区表。请改为使用
CREATE TABLE
DDL 语句来创建表,然后使用INSERT
DML 语句向其中插入数据。 - 无法使用
OR REPLACE
修饰符将表替换为使用不同种类的分区。请先使用DROP
删除表,然后使用CREATE TABLE ... AS SELECT ...
语句重新创建表。
临时表
如需创建临时表,请在使用 CREATE TABLE
语句时使用 TEMP
或 TEMPORARY
关键字。
语法
{ CREATE {TEMP|TEMPORARY} TABLE | CREATE {TEMP|TEMPORARY} TABLE IF NOT EXISTS | CREATE OR REPLACE {TEMP|TEMPORARY} TABLE } ...
除了使用 TEMP
或 TEMPORARY
之外,该语法与 CREATE TABLE
语法相同。
不要限定临时表名称;也就是说,请勿使用项目或数据集限定符。临时表是在特殊数据集中自动创建的。
在当前脚本的持续时间内,您可以根据名称引用临时表;如需了解详情,请参阅使用标准 SQL 编写脚本。这包括在脚本中由过程创建的表。表在其中创建的脚本完成后,您无法查询表。
脚本完成后,临时表最多存在 24 小时。它不会使用您提供的名称进行保存,而是被分配一个随机名称。如需查看表的结构和数据,请转到 BigQuery 控制台,点击查询历史记录,然后选择创建临时表的查询。然后,在目标表 行中,点击临时表。
您无法共享临时表,也无法使用任何标准列表或其他表操作方法显示临时表。您不需要支付临时表的存储费用。
如需创建临时表,请运行以下脚本:
CREATE TEMP TABLE Example
(
x INT64,
y STRING
);
INSERT INTO Example
VALUES (5, 'foo');
INSERT INTO Example
VALUES (6, 'bar');
SELECT *
FROM Example;
此脚本会返回以下输出:
+-----+---+-----+
| Row | x | y |
+-----+---|-----+
| 1 | 5 | foo |
| 2 | 6 | bar |
+-----+---|-----+
您可以通过执行 DROP TABLE
语句,在脚本完成之前明确删除临时表:
CREATE TEMP TABLE foo(x INT64);
SELECT * FROM foo; -- Succeeds
DROP TABLE foo;
SELECT * FROM foo; -- Results in an error
当临时表与默认数据集一起使用时,非限定表名称会引用下面所述的表:
- 临时表(如果存在的话)
- 默认数据集中的表(如果不存在临时表)
CREATE TABLE
语句属于例外情况;当且仅当存在 TEMP
或 TEMPORARY
关键字时,目标表才会被视为临时表。
例如,请考虑以下脚本:
-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);
-- Create temporary table t1
CREATE TEMP TABLE t1 (x INT64);
-- This statement will select from the temporary table
SELECT * FROM t1;
-- Drop the temporary table
DROP TABLE t1;
-- Now that the temporary table is dropped, this statement will select from
-- the table in the default dataset
SELECT * FROM t1;
您可以使用 _SESSION
限定表名称,从而明确表明您要引用临时表:
-- Create a temp table CREATE TEMP TABLE t1 (x INT64); -- Create a temp table using the `_SESSION` qualifier CREATE TEMP TABLE _SESSION.t2 (x INT64); -- Select from a temporary table using the `_SESSION` qualifier SELECT * FROM _SESSION.t1;
如果对某个临时表的查询使用 _SESSION
限定符,而该临时表并不存在,则您会收到一条错误消息,指示该表不存在。例如,如果没有名为 t3
的临时表,即使默认数据集中存在名为 t3
的表,您也会收到该错误消息。
您无法使用 _SESSION
创建非临时表:
CREATE TABLE _SESSION.t4 (x INT64); -- Fails
示例
创建新表
以下示例在 mydataset
中创建名为 newtable
的分区表:
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 > ) PARTITION BY _PARTITIONDATE OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", partition_expiration_days=1, description="a table that expires in 2025, with each partition living for 24 hours", labels=[("org_unit", "development")] )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.newtable`
,而不是 mydataset.newtable
。
如果数据集内已存在该表名称,会返回以下错误:
Already Exists: project_id:dataset.table
该表利用以下 partition_expression
对表进行分区:PARTITION BY _PARTITIONDATE
。此表达式使用 _PARTITIONDATE
伪列中的日期对表进行分区。
表架构中包含两列:
- x:整数,带有“可选的整数字段”描述
y:包含两列的 STRUCT:
- a:一组字符串,带有“重复的字符串字段”描述
- b:布尔值
表选项列表指定了以下内容:
- 表到期时间:世界协调时间 (UTC) 2025 年 1 月 1 日 00:00:00
- 分区到期时间:1 天
- 说明:于 2025 年到期的表
- 标签:org_unit = development
从现有表创建新表
下面的示例基于查询在 mydataset
中创建名为 top_words
的表:
CREATE TABLE mydataset.top_words OPTIONS( description="Top ten words per Shakespeare corpus" ) AS SELECT corpus, ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words FROM bigquery-public-data.samples.shakespeare GROUP BY corpus;
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.top_words`
,而不是 mydataset.top_words
。
如果数据集内已存在该表名称,会返回以下错误:
Already Exists: project_id:dataset.table
表架构中包含两列:
- corpus:莎士比亚全集的名称
top_words:
STRUCT
类型的ARRAY
,包含两个字段:word
(STRING
类型)和word_count
(表示该 word 出现次数的INT64
)
表选项列表指定了以下内容:
- 说明:莎士比亚全集中出现次数最多的十个词
仅当某个表不存在时才创建该表
仅当 mydataset
中不存在名为 newtable
的表时,下述示例才会在 mydataset
中创建名为 newtable
的表。如果数据集中存在该表名称,则不会返回任何错误,也不会执行任何操作。
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.newtable`
,而不是 mydataset.newtable
。
表架构中包含两列:
- x:整数
y:包含 a(字符串数组)和 b(布尔值)的 STRUCT
表选项列表指定了以下内容:
- 到期时间:世界协调时间 (UTC) 2025 年 1 月 1 日 00:00:00
- 说明:于 2025 年到期的表
- 标签:org_unit = development
创建或替换表
以下示例会在 mydataset
中创建名为 newtable
的表,如果 mydataset
中存在 newtable
,则会用空表覆盖该表。
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.newtable`
,而不是 mydataset.newtable
。
表架构中包含两列:
- x:整数
y:包含 a(字符串数组)和 b(布尔值)的 STRUCT
表选项列表指定了以下内容:
- 到期时间:世界协调时间 (UTC) 2025 年 1 月 1 日 00:00:00
- 说明:于 2025 年到期的表
- 标签:org_unit = development
使用 REQUIRED
列创建表
以下示例在 mydataset
中创建名为 newtable
的表。CREATE TABLE
语句的列定义列表中的 NOT
NULL
修饰符指定以 REQUIRED
模式创建列或字段。
CREATE TABLE my_dataset.new_table ( x INT64 NOT NULL, y STRUCT< a ARRAY<STRING>, b BOOL NOT NULL, c FLOAT64 > NOT NULL, z STRING )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.my_dataset.new_table`
,而不是 my_dataset.new_table
。
如果数据集内已存在该表名称,会返回以下错误:
Already Exists: project_id:dataset.table
表架构中包含 3 列:
- x:
REQUIRED
整数 - y:
REQUIRED
STRUCT,包含 a(字符串数组)、b(REQUIRED
布尔值)、c(NULLABLE
浮点) z:
NULLABLE
字符串
创建分区表
以下示例使用一个 DATE
列在 mydataset
中创建名为 newtable
的分区表:
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date OPTIONS( partition_expiration_days=3, description="a table partitioned by transaction_date" )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.newtable`
,而不是 mydataset.newtable
。
表架构中包含两列:
- transaction_id:整数
- transaction_date:日期
表选项列表指定了以下内容:
- 分区有效期限:3 天
- 说明:按
transaction_date
分区的表
从查询结果创建分区表
以下示例使用一个 DATE
列在 mydataset
中创建名为 days_with_rain
的分区表:
CREATE TABLE mydataset.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=365, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations can have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather WHERE prcp != 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.days_with_rain`
,而不是 mydataset.days_with_rain
。
表架构中包含两列:
- date:数据收集的
DATE
- station_name:
STRING
格式的气象站名称 - prcp:
FLOAT64
格式的降雨量(以英寸为单位)
表选项列表指定了以下内容:
- 分区有效期限:一年
- 说明:有降雨量的气象站,按天分区
创建聚簇表
示例 1
以下示例在 mydataset
中创建名为 myclusteredtable
的聚簇表。该表是分区表,按 TIMESTAMP
列分区,并按名为 customer_id
的 STRING
列进行聚簇。
CREATE TABLE mydataset.myclusteredtable ( timestamp TIMESTAMP, customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(timestamp) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.myclusteredtable`
,而不是 mydataset.myclusteredtable
。
表架构中包含 3 列:
- timestamp:
TIMESTAMP
格式的数据收集时间 - customer_id:
STRING
格式的客户 ID - transaction_amount:
NUMERIC
格式的交易金额
表选项列表指定了以下内容:
- 分区有效期限:3 天
- 说明:“按 customer_id 聚簇的表”
示例 2
以下示例在 mydataset
中创建名为 myclusteredtable
的聚簇表。该表为提取时间分区表。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(_PARTITIONTIME) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.myclusteredtable`
,而不是 mydataset.myclusteredtable
。
表架构中包含两列:
- customer_id:
STRING
格式的客户 ID - transaction_amount:
NUMERIC
格式的交易金额
表选项列表指定了以下内容:
- 分区有效期限:3 天
- 说明:“按 customer_id 聚簇的表”
示例 3
以下示例在 mydataset
中创建名为 myclusteredtable
的聚簇表。该表未进行分区。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" )
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.myclusteredtable`
,而不是 mydataset.myclusteredtable
。
表架构中包含两列:
- customer_id:
STRING
格式的客户 ID - transaction_amount:
NUMERIC
格式的交易金额
表选项列表指定了以下内容:
- 说明:“按 customer_id 聚簇的表”
从查询结果创建聚簇表
示例 1
以下示例使用查询结果在 mydataset
中创建名为 myclusteredtable
的聚簇表。该表是按 TIMESTAMP
列分区的分区表。
CREATE TABLE mydataset.myclusteredtable ( timestamp TIMESTAMP, customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(timestamp) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.myclusteredtable`
,而不是 mydataset.myclusteredtable
。
表架构中包含 3 列:
- timestamp:
TIMESTAMP
格式的数据收集时间 - customer_id:
STRING
格式的客户 ID - transaction_amount:
NUMERIC
格式的交易金额
表选项列表指定了以下内容:
- 分区有效期限:3 天
- 说明:“按 customer_id 聚簇的表”
示例 2
以下示例使用查询结果在 mydataset
中创建名为 myclusteredtable
的聚簇表。该表未进行分区。
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
如果您未配置默认项目,请在示例 SQL 中的数据集名称前加上项目 ID,如果 project_id
包含特殊字符,则用反引号将名称括起:`project_id.dataset.table`
。因此,表限定符可能是 `myproject.mydataset.myclusteredtable`
,而不是 mydataset.myclusteredtable
。
表架构中包含两列:
- customer_id:
STRING
格式的客户 ID - transaction_amount:
NUMERIC
格式的交易金额
表选项列表指定了以下内容:
- 说明:“按 customer_id 聚簇的表”
CREATE VIEW
语句
如需在 BigQuery 中创建视图,请使用 CREATE VIEW
DDL 语句。
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} [[project_name.]dataset_name.]view_name [OPTIONS(view_option_list)] AS query_expression
其中:
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
是下述语句之一:
CREATE VIEW
:创建新视图。CREATE VIEW IF NOT EXISTS
:仅当指定的数据集中不存在要创建的视图时才创建此视图。CREATE OR REPLACE VIEW
:在指定的数据集中创建视图并替换具有相同名称的现有视图。
project_name
是您要在其中创建视图的项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是您要在其中创建视图的数据集的名称。
默认为请求中的 defaultDataset
。
view_name
是您要创建的视图的名称。每个数据集的视图名称必须是唯一的。视图名称要求:
- 包含最多 1024 个字符
- 包含字母(大写或小写)、数字和下划线
view_option_list
可让您指定其他视图创建选项,例如标签和到期时间。
CREATE VIEW
语句必须符合以下规则:
- 只允许一个
CREATE
语句。
query_expression
是用于定义视图的标准 SQL 查询表达式。
view_option_list
通过选项列表,您可以设置视图选项,例如标签和到期时间。可使用逗号分隔列表包括多个选项。
请按以下格式指定视图选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
VALUE
是一个只包含文字、查询参数和标量函数的常数表达式。如果常量表达式的计算结果为 null
,则忽略对应的 NAME
选项。
常量表达式不得包含以下内容:
- 对表的引用
- 子查询和 SQL 语句,如
SELECT
、CREATE
和UPDATE
- 用户定义的函数、聚合函数或分析函数
- 以下标量函数:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
视图正文中的默认项目
如果视图是在用于运行 CREATE VIEW
语句的项目中创建的,则视图正文 query_expression
可以在不指定项目的情况下引用实体;默认项目是拥有视图的项目。请考虑以下示例查询。
CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
在项目 myProject
中运行上述 CREATE VIEW
查询后,您可以运行查询 SELECT * FROM myProject.myDataset.myView
。无论选择哪个项目来运行此 SELECT
查询,引用的表 anotherDataset.myTable
始终会针对项目 myProject
进行解析。
如果视图并非是在用于运行 CREATE VIEW
语句的项目中创建的,则视图正文 query_expression
中的所有引用都必须使用项目 ID 进行限定。例如,如果前面的示例 CREATE VIEW
查询在 myProject
之外的其他项目中运行,则该查询无效。
示例
创建新视图
以下示例在 mydataset
中创建名为 newview
的视图:
CREATE VIEW `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
如果数据集中存在该视图名称,会返回以下错误:
Already Exists: project_id:dataset.table
视图使用以下标准 SQL 查询进行定义:
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
视图选项列表指定了以下内容:
- 到期时间:自创建视图后 48 小时
- 易记名称:newview
- 说明:在两天后到期的视图
- 标签:org_unit = development
仅当某个视图不存在时才创建该视图
以下示例只有在 mydataset
中不存在名为 newview
的视图时才会在 mydataset
中创建名为 newview
的视图。如果数据集中存在该视图名称,则不会返回任何错误,也不会执行任何操作。
CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
视图使用以下标准 SQL 查询进行定义:
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
视图选项列表指定了以下内容:
- 到期时间:自创建视图后 48 小时
- 易记名称:newview
- 说明:在两天后到期的视图
- 标签:org_unit = development
创建或替换视图
以下示例会在 mydataset
中创建名为 newview
的视图,如果 mydataset
中存在 newview
,会使用指定的查询表达式覆盖该视图。
CREATE OR REPLACE VIEW `myproject.mydataset.newview` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="newview", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
视图使用以下标准 SQL 查询进行定义:
SELECT column_1, column_2, column_3 FROM
myproject.mydataset.mytable
视图选项列表指定了以下内容:
- 到期时间:自创建视图后 48 小时
- 易记名称:newview
- 说明:在两天后到期的视图
- 标签:org_unit = development
CREATE MATERIALIZED VIEW
语句
如需在 BigQuery 中创建具体化视图,请使用 CREATE
MATERIALIZED VIEW
DDL 语句。
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS } [[project_name.]dataset_name.]materialized_view_name [PARTITION BY partition_expression] [CLUSTER BY clustering_column_list] [OPTIONS(materialized_view_option_list)] AS query_expression
其中:
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS }
是下述语句之一:
CREATE MATERIALIZED VIEW
:创建新的具体化视图。CREATE MATERIALIZED VIEW IF NOT EXISTS
:仅当指定数据集中当前不存在某个具体化视图时才新建该视图。
project_name
是您要在其中创建具体化视图的项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
如果省略 project_name
或它与运行此 DDL 查询的项目相同,则后者还将在 query_expression
中用作对表、函数等引用的默认项目(注意,引用的默认项目是固定的,并非取决于调用新具体化视图的未来查询)。否则,query_expression
中的所有引用都必须使用项目进行限定。
dataset_name
是您要在其中创建具体化视图的数据集的名称。
默认为请求中的 defaultDataset
。
materialized_view_name
是您要创建的具体化视图的名称。
每个数据集的具体化视图名称必须是唯一的。具体化视图名称可以:
- 包含最多 1024 个字符
- 包含字母(大写或小写)、数字和下划线
PARTITION BY
和 CLUSTER BY
子句与在 CREATE TABLE
语句中的使用方式一样。具体化视图只能像 query expression
(基表)中的表一样分区。
materialized_view_option_list
可用于指定其他具体化视图选项,例如是否启用刷新、刷新间隔、标签和到期时间。
CREATE MATERIALIZED VIEW
语句必须符合以下规则:
- 只允许一个
CREATE
语句。
query_expression
是用于定义具体化视图的标准 SQL 查询表达式。
materialized_view_option_list
通过选项列表,您可以设置具体化视图选项,例如是否启用刷新、刷新间隔、标签和到期时间。您可以使用逗号分隔列表添加多个选项。
请按以下格式指定具体化视图选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
enable_refresh |
BOOLEAN |
示例: |
refresh_interval_minutes |
FLOAT64 |
示例: |
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
具体化视图正文中的默认项目
如果具体化视图是在用于运行 CREATE MATERIALIZED VIEW
语句的项目中创建的,则具体化视图正文 query_expression
可以在不指定项目的情况下引用实体;默认项目是拥有具体化视图的项目。请考虑以下示例查询。
CREATE MATERIALIZED VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
在项目 myProject
中运行上述 CREATE MATERIALIZED VIEW
查询后,您可以运行查询 SELECT * FROM myProject.myDataset.myView
。无论选择哪个项目来运行此 SELECT
查询,引用的表 anotherDataset.myTable
始终会针对项目 myProject
进行解析。
如果具体化视图并非是在用于运行 CREATE VIEW
语句的项目中创建的,则具体化视图正文 query_expression
中的所有引用都必须使用项目 ID 进行限定。例如,如果前面的示例 CREATE MATERIALIZED VIEW
查询在 myProject
之外的其他项目中运行,则该查询无效。
示例
创建新的具体化视图
以下示例在 mydataset
中创建了一个名为 new_mv
的具体化视图:
CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a materialized view that expires in 2 days",
labels=[("org_unit", "development")],
enable_refresh=true,
refresh_interval_minutes=20
)
AS SELECT column_1, SUM(column_2) AS sum_2, AVG(column_3) AS avg_3
FROM `myproject.mydataset.mytable`
GROUP BY column_1
如果数据集中已存在此具体化视图名称,会返回以下错误:
Already Exists: project_id:dataset.materialized_view
使用 DDL 语句创建具体化视图时,您必须按以下格式指定项目、数据集和具体化视图:`project_id.dataset.materialized_view`
(如果 project_id
包含特殊字符,则包括反引号);例如 `myproject.mydataset.new_mv`
。
具体化视图使用以下标准 SQL 查询进行定义:
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
具体化视图选项列表指定了以下内容:
- 到期时间:自创建具体化视图后 48 小时
- 易记名称:new_mv
- 说明:在两天后到期的具体化视图
- 标签:org_unit = development
- 已启用刷新:true
- 刷新间隔:20 分钟
仅当某个具体化视图不存在时才创建该视图
以下示例仅在 mydataset
中不存在名为 new_mv
的具体化视图时才在 mydataset
中创建名为 new_mv
的具体化视图。如果数据集中存在该具体化视图名称,则不会返回任何错误,也不会执行任何操作。
CREATE MATERIALIZED VIEW IF NOT EXISTS `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a view that expires in 2 days",
labels=[("org_unit", "development")],
enable_refresh=false
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
具体化视图使用以下标准 SQL 查询进行定义:
SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
具体化视图选项列表指定了以下内容:
- 到期时间:自创建视图后 48 小时
- 易记名称:new_mv
- 说明:在两天后到期的视图
- 标签:org_unit = development
- 已启用刷新:false
使用分区和聚簇创建具体化视图
以下示例在 mydataset
中创建一个名为 new_mv
的具体化视图,按 col_datetime
列进行分区并按 col_int
列进行聚簇:
CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
PARTITION BY DATE(col_datetime)
CLUSTER BY col_int
AS SELECT col_int, col_datetime, COUNT(1) as cnt
FROM `myproject.mydataset.mv_base_table`
GROUP BY col_int, col_datetime
基表 mv_base_table
也必须按 col_datetime
列进行分区。如需了解详情,请参阅使用具体化视图。
CREATE EXTERNAL TABLE
语句
CREATE EXTERNAL TABLE
语句会创建外部表。外部表允许 BigQuery 查询存储在 BigQuery 存储空间以外的数据。如需详细了解外部表,请参阅外部数据源简介。
CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name [( column_name column_schema, ... )] [WITH PARTITION COLUMNS [( partition_column_name partition_column_type, ... )] ] OPTIONS ( external_table_option_list, ... );
其中:
project_name
是您要在其中创建表的项目的名称。默认为运行此 DDL 查询的项目。dataset_name
是您要在其中创建表的数据集的名称。table_name
是外部表的名称。column_name
是表中的列名称。column_schema
指定列的架构。它使用的语法与CREATE TABLE
语句中的column_schema
定义相同。如果您不添加此子句,BigQuery 会自动检测架构。partition_column_name
是分区列的名称。如果外部数据使用 Hive 分区布局,请添加此字段。如需了解详情,请参阅支持的数据布局。partition_column_type
是分区列类型。external_table_option_list
指定用于创建外部表的选项列表。
external_table_option_list
此选项列表指定用于创建外部表的选项。您必须提供 format
和 uris
选项。请按以下格式指定选项列表:NAME=VALUE, ...
选项 | |
---|---|
allow_jagged_rows |
如果为 适用于 CSV 数据。 |
allow_quoted_newlines |
如果为 适用于 CSV 数据。 |
compression |
数据源的压缩类型。支持的值包括: 适用于 CSV 和 JSON 数据。 |
description |
此表的说明。 |
enable_logical_types |
如果为 适用于 Avro 数据。 |
encoding |
数据的字符编码。支持的值包括: 适用于 CSV 数据。 |
expiration_timestamp |
此表的到期时间。如果未指定,则该表不会过期。 示例: |
field_delimiter |
CSV 文件中的字段的分隔符。 适用于 CSV 数据。 |
format |
外部数据的格式。支持的值包括: 值 |
decimal_target_types |
确定如何转换 示例: |
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 数组。 示例: |
CREATE EXTERNAL TABLE
语句不支持创建临时外部表。
如需创建外部分区表,请使用 WITH PARTITION COLUMNS
子句指定分区架构的详细信息。BigQuery 会根据外部数据位置验证列定义。架构声明必须严格遵循外部路径中的字段顺序。如需详细了解外部分区,请参阅查询外部分区数据。
示例
以下示例通过多个 URI 创建外部表。数据格式为 CSV。此示例使用架构自动检测功能。
CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv', 'gs://bucket/path2.csv']
);
下面的示例将根据 CSV 文件创建外部表并明确指定架构。此外,它还会指定字段分隔符 ('|'
) 并设置允许的错误记录数上限。
CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable
(
x INT64,
y STRING
)
OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv'],
field_delimiter = '|',
max_bad_records = 5
);
下面的示例将创建一个外部分区表。它使用架构自动检测功能来检测文件架构和 Hive 分区布局。
例如,如果外部路径是 gs://bucket/path/field_1=first/field_2=1/data.csv
,则分区列将是 field_1
(STRING
) 和 field_2
(INT64
)。
CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
下面的示例将通过明确指定分区列来创建外部分区表。此示例假定外部文件路径的格式为 gs://bucket/path/field_1=first/field_2=1/data.csv
。
CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64
)
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
CREATE FUNCTION
语句
BigQuery 支持用户定义函数 (UDF)。借助 UDF,您可以使用 SQL 表达式或 JavaScript 创建函数。这些函数接受输入列并执行操作,然后以值的形式返回这些操作的结果。
UDF 可以是永久性的,也可以是临时性的。您可以在多个查询间重复使用永久性 UDF,但只能在单个查询中使用临时性 UDF。如需详细了解 UDF,请参阅用户定义函数。
UDF 语法
如需创建永久性 UDF,请使用以下语法:
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] [[project_name.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
如需创建临时性 UDF,请使用以下语法:
CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS] function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
named_parameter: param_name param_type sql_function_definition: AS (sql_expression) javascript_function_definition: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
此语法由以下部分组成:
CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }:创建或更新函数。如需替换名称相同的任何现有函数,请使用
OR REPLACE
关键字。如需在存在同名函数的情况下将查询视为成功,而不执行任何操作,请使用IF NOT EXISTS
子句。project_name 是您要在其中创建函数的项目的名称。默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号
`
将项目名称引起来(示例:`google.com:my_project`
)。dataset_name 是您要在其中创建函数的数据集的名称。默认为请求中的
defaultDataset
。named_parameter。由英文逗号分隔的
param_name
和param_type
对组成。param_type
的值为 BigQuery 数据类型。对于 SQL UDF,param_type
的值也可以是ANY TYPE
。determinism_specifier。仅适用于 JavaScript 的用户定义的函数。向 BigQuery 提供关于是否可缓存查询结果的提示。可以是下列值之一:
DETERMINISTIC
:传递相同的参数时,函数始终返回相同的结果。查询结果可能可缓存。例如,如果函数add_one(i)
始终返回i + 1
,则该函数是确定性函数。NOT DETERMINISTIC
:在传递相同的参数时,函数并不总是返回相同的结果,因此无法缓存。例如,如果add_random(i)
返回i + rand()
,则该函数不是确定性的,并且 BigQuery 不会使用缓存的结果。如果调用的所有函数均为 DETERMINISTIC,则 BigQuery 将尝试缓存结果,除非由于其他原因无法缓存结果。如需了解详情,请参阅使用缓存的查询结果。
[RETURNS data_type]。它指定函数返回的数据类型。
- 如果函数是使用 SQL 定义的,则
RETURNS
为可选子句。如果省略RETURNS
子句,则当查询调用函数时,BigQuery 会通过 SQL 函数体推断出函数的结果类型。 - 如果函数是使用 JavaScript 定义的,则需要
RETURNS
子句。如需详细了解可使用的data_type
值,请参阅支持的 JavaScript UDF 数据类型。
- 如果函数是使用 SQL 定义的,则
AS (sql_expression):它指定用于定义函数的 SQL 表达式。
[OPTIONS (library = library_array)]。对于 JavaScript UDF,请指定一个包含要加入函数定义的 JavaScript 库的数组。
AS javascript_code:指定 JavaScript 函数的定义。
javascript_code
为字符串字面量。- 如果代码包含英文引号和反斜杠,则必须进行转义或表示为原始字符串。例如,代码
return "\n";
可以表示为以下之一:- 带英文引号的字符串
"return \"\\n\";"
。英文引号和反斜杠都需要进行转义。 - 带英文三引号的字符串:
"""return "\\n";"""
。反斜杠需要进行转义,而英文引号不需要。 - 原始字符串:
r"""return "\n";"""
。无需进行转义。
- 带英文引号的字符串
- 如果代码包含英文引号和反斜杠,则必须进行转义或表示为原始字符串。例如,代码
SQL UDF 结构
使用以下语法创建 SQL UDF:
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] AS (sql_expression) named_parameter: param_name param_type
模板化 SQL UDF 参数
在调用函数时,param_type
为 ANY TYPE
的模板化参数可以匹配多个参数类型。
- 如果多个参数的类型为
ANY TYPE
,则 BigQuery 不会在这些参数之间强制执行任何类型关系。 - 函数返回类型不能是
ANY TYPE
。它要么省略(即系统根据sql_expression
自动确定),要么是某个明确的类型。 - 向函数传递与函数定义不兼容类型的参数将导致调用时错误。
SQL UDF 正文中的默认项目
如果 SQL UDF 是在用于运行 CREATE FUNCTION
语句的项目中创建的,则 UDF 正文 sql_expression
可以在不指定项目的情况下引用实体;默认项目是拥有 UDF 的项目。请考虑以下示例查询。
CREATE FUNCTION myProject.myDataset.myFunction() AS (anotherDataset.anotherFunction());
在项目 myProject
中运行上述 CREATE FUNCTION
查询后,您可以运行查询 SELECT myProject.myDataset.myFunction()
。无论选择哪个项目来运行此 SELECT
查询,引用的函数 anotherDataset.anotherFunction
始终会根据项目 myProject
进行解析。
如果 UDF 并非是在用于运行 CREATE FUNCTION
语句的项目中创建的,则 UDF 正文 sql_expression
中的所有引用都必须使用项目 ID 进行限定。例如,如果前面的示例 CREATE FUNCTION
查询在 myProject
之外的其他项目中运行,则该查询无效。
SQL UDF 示例
以下示例会创建一个永久性 SQL UDF。它假设活跃项目中存在名为 mydataset
的数据集。如果不存在使用此名称的数据集,请参阅关于如何创建数据集的文档。
CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
AS (x * y);
执行 CREATE FUNCTION
语句后,您可以在单独的查询中使用新的永久性用户定义函数。将查询编辑器中的内容替换为以下内容,然后运行查询:
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, mydataset.multiplyInputs(x, y) as product
FROM numbers;
上面的示例生成以下输出:
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
以下示例展示了使用模板化参数的永久性 SQL UDF。生成的函数可接受各种类型的参数。
CREATE FUNCTION mydataset.addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
(x + 4) / y
);
执行 CREATE FUNCTION
语句后,您可以在单独的查询中使用新的永久性用户定义函数:
SELECT addFourAndDivideAny(3, 4) AS integer_output,
addFourAndDivideAny(1.59, 3.14) AS floating_point_output;
此查询返回以下输出:
+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75 | 1.7802547770700636 |
+----------------+-----------------------+
以下示例所示的 SQL UDF 使用模板化参数返回任何类型的数组的最后一个元素。
CREATE FUNCTION mydataset.lastArrayElement(arr ANY TYPE) AS (
arr[ORDINAL(ARRAY_LENGTH(arr))]
);
执行 CREATE FUNCTION
语句后,您可以在单独的查询中使用新的永久性用户定义函数:
SELECT
names[OFFSET(0)] AS first_name,
lastArrayElement(names) AS last_name
FROM (
SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
SELECT ['Marie', 'Skłodowska', 'Curie']
);
以上查询返回以下输出:
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred | Rogers |
| Marie | Curie |
+------------+-----------+
JavaScript UDF 结构
使用以下语法创建永久性 JavaScript UDF。
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code
如需详细了解可使用的 data_type
值和参数类型,请参阅支持的 JavaScript UDF 数据类型。
JavaScript UDF 示例
CREATE TEMP FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
return x*y;
""";
执行 CREATE FUNCTION
语句后,您可以在单独的查询中使用新的永久性 JavaScript UDF:
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;
上面的示例返回以下输出:
+-----+-----+--------------+
| x | y | product |
+-----+-----+--------------+
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
+-----+-----+--------------+
您可以将 UDF 的结果作为输入传递给另一个 UDF。例如,使用以下查询创建永久性 UDF:
CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
return x*y;
""";
然后运行另一个查询来创建第二个永久性 UDF:
CREATE FUNCTION mydataset.divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
return x/2;
""";
现在,运行以下查询以在同一查询中使用这两个永久性 UDF:
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x,
y,
mydataset.multiplyInputs(
mydataset.divideByTwo(x), mydataset.divideByTwo(y)) as half_product
FROM numbers;
上面的示例返回以下输出:
+-----+-----+--------------+
| x | y | half_product |
+-----+-----+--------------+
| 1 | 5 | 1.25 |
| 2 | 10 | 5 |
| 3 | 15 | 11.25 |
+-----+-----+--------------+
以下示例对给定 JSON 字符串中名为“foo”的所有字段的值进行求和。
CREATE FUNCTION mydataset.SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
function SumFoo(obj) {
var sum = 0;
for (var field in obj) {
if (obj.hasOwnProperty(field) && obj[field] != null) {
if (typeof obj[field] == "object") {
sum += SumFoo(obj[field]);
} else if (field == "foo") {
sum += obj[field];
}
}
}
return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";
执行 CREATE FUNCTION
语句后,您可以在单独的查询中使用新的永久性用户定义函数:
WITH Input AS (
SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
SELECT NULL, 4 AS foo UNION ALL
SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
TO_JSON_STRING(t) AS json_row,
mydataset.SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
上面的示例返回以下输出:
+---------------------------------------------------------------------+---------+
| json_row | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10} | 14.14 |
| {"s":null,"foo":4} | 4 |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59 |
+---------------------------------------------------------------------+---------+
引用规则
必须将 JavaScript 代码用引号括起来。对于一行简单的代码段,可使用带标准英文引号的字符串:
CREATE FUNCTION mydataset.plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
如果代码段包含英文引号或由多行组成,请使用三引号块:
CREATE FUNCTION mydataset.customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS r"""
var d = new Date();
if (d.getHours() < 12) {
return 'Good Morning, ' + a + '!';
} else {
return 'Good Evening, ' + a + '!';
}
""";
包括 JavaScript 库
可使用 OPTIONS
部分扩展 JavaScript UDF。此部分可让您指定 UDF 的 JavaScript 代码库。
CREATE FUNCTION mydataset.myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
)
AS
r"""
// Assumes 'doInterestingStuff' is defined in one of the library files.
return doInterestingStuff(a, b);
""";
SELECT mydataset.myFunc(3.14, 'foo');
在上述示例中,lib1.js
和 lib2.js
中的代码可用于 UDF 的 javascript_code
部分中的任何代码。请注意,您可以使用单元素或数组语法来指定库文件。
UDF 和 Cloud Console
您可以使用 Cloud Console 创建用户定义的永久性函数。
通过运行查询来创建永久性 UDF
转到 Cloud Console 中的 BigQuery 页面。
点击编写新查询。
在查询编辑器文本区域中,输入 UDF 语句。例如:
CREATE FUNCTION mydataset.timesTwo(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS r""" return x*2; """;
点击运行。
创建永久性用户定义函数后,将编辑器中的内容替换为使用该函数的新查询:
SELECT mydataset.timesTwo(numbers) AS doubles FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
点击运行。
UDF 和 bq 命令行工具
您可以使用 Cloud SDK 中的 bq 命令行工具来创建永久性 UDF。
使用以下语法运行查询,以创建永久性 UDF:
bq query --use_legacy_sql=false '
CREATE FUNCTION mydataset.AddTwo(x INT64) AS (x + 2);
'
CREATE PROCEDURE
语句
创建一个过程,该过程是可以从其他查询调用的语句块。
CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] [[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] ) [OPTIONS(procedure_option_list)] BEGIN statement_list END; procedure_argument: [procedure_argument_mode] argument_name argument_type
procedure_argument_mode: IN | OUT | INOUT
说明
project_name
是您要在其中创建过程的项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是您要在其中创建过程的数据集的名称。
默认为请求中的 defaultDataset
。
statement_list
是 BigQuery 语句列表。一个语句列表是一个语句系列,其中的每条语句都以英文分号结尾。
argument_type
是任何有效的 BigQuery 类型。
procedure_argument_mode
指定参数是输入还是输出,或者既是输入又是输出。
过程可以递归调用自身。
procedure_option_list
procedure_option_list
可让您指定过程选项。过程选项与表选项的语法和要求相同,但包含的 NAME
和 VALUE
列表不同:
NAME |
VALUE |
详细信息 |
---|---|---|
strict_mode |
|
示例: 如果 虽然 如果 默认值为 |
参数模式
IN
指示参数只是过程的输入。您可以为 IN
参数指定变量或值表达式。
OUT
指示参数是过程的输出。过程开始时,OUT
参数会初始化为 NULL
。您必须为 OUT
参数指定变量。
INOUT
指示参数既是过程的输入,也是过程的输出。您必须为 INOUT
参数指定变量。您可以在过程正文中将 INOUT
参数作为变量引用,并且可以为该参数赋予新值。
如果未指定 IN
、OUT
、INOUT
,则参数会被视为 IN
参数。
变量范围
如果变量是在某个过程之外声明的,并且作为 INOUT 或 OUT 参数传递给该过程,而该过程为该变量赋予了新值,则该新值在该过程之外可见。
在某个过程中声明的变量在该过程之外不可见,反之亦然。
您可以使用 SET
为 OUT
或 INOUT
参数赋值,在这种情况下,修改后的值在相应过程之外可见。如果该过程成功退出,则 OUT
或 INOUT
参数的值是赋予该 INOUT
变量的最终值。
由于临时表存在于脚本的生命周期内,因此,如果某个过程创建了临时表,则该过程的调用者也可以引用该临时表。
过程正文中的默认项目
过程正文可以在不指定项目的情况下引用实体;默认项目是拥有该过程的项目,而不一定是用于运行 CREATE PROCEDURE
语句的项目。请考虑以下示例查询。
CREATE PROCEDURE myProject.myDataset.QueryTable()
BEGIN
SELECT * FROM anotherDataset.myTable;
END;
创建上述过程后,您可以运行查询 CALL myProject.myDataset.QueryTable()
。无论选择哪个项目来运行此 CALL
查询,引用的表 anotherDataset.myTable
始终会根据项目 myProject
进行解析。
示例
以下示例会创建一个过程,该过程既接收 x
作为输入参数,又返回 x
作为输出;由于参数 delta
没有参数模式,因此该参数是输入参数。该过程由一个包含单一语句的块组成,该语句将这两个输入参数的总和赋予 x
。
CREATE PROCEDURE mydataset.AddDelta(INOUT x INT64, delta INT64)
BEGIN
SET x = x + delta;
END;
以下示例会调用上述示例中的 AddDelta
过程,并将变量 accumulator
传递给该过程两次;由于 AddDelta
内部对 x
的更改在 AddDelta
之外可见,因此这些过程调用使 accumulator
总共增加了 8。
DECLARE accumulator INT64 DEFAULT 0;
CALL mydataset.AddDelta(accumulator, 5);
CALL mydataset.AddDelta(accumulator, 3);
SELECT accumulator;
此示例会返回以下内容:
+-------------+
| accumulator |
+-------------+
| 8 |
+-------------+
以下示例会创建过程 SelectFromTablesAndAppend
,该过程接收 target_date
作为输入参数,并返回 rows_added
作为输出。该过程会基于查询创建临时表 DataForTargetDate
,然后计算 DataForTargetDate
中的行数并将结果赋予 rows_added
。接下来,该过程会在 TargetTable
中插入一个新行,并将 target_date
的值作为列名之一传递。最后,该过程会删除 DataForTargetDate
表并返回 rows_added
。
CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(
target_date DATE, OUT rows_added INT64)
BEGIN
CREATE TEMP TABLE DataForTargetDate AS
SELECT t1.id, t1.x, t2.y
FROM dataset.partitioned_table1 AS t1
JOIN dataset.partitioned_table2 AS t2
ON t1.id = t2.id
WHERE t1.date = target_date
AND t2.date = target_date;
SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);
SELECT id, x, y, target_date -- note that target_date is a parameter
FROM DataForTargetDate;
DROP TABLE DataForTargetDate;
END;
以下示例会声明变量 rows_added
,然后将其作为参数(连同 CURRENT_DATE
的值)传递给上一示例中的 SelectFromTablesAndAppend
过程,最后,该示例会返回一条说明添加了多少行的消息。
DECLARE rows_added INT64;
CALL mydataset.SelectFromTablesAndAppend(CURRENT_DATE(), rows_added);
SELECT FORMAT('Added %d rows', rows_added);
ALTER TABLE SET OPTIONS
语句
如需在 BigQuery 中设置表中的选项,请使用 ALTER TABLE SET OPTIONS
DDL 语句。
ALTER TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name SET OPTIONS(table_set_options_list)
其中:
IF EXISTS
:如果存在,则指定的表不存在时查询会成功。如果不存在,则指定的表不存在时查询会失败。
project_name
是要修改的表所在项目的名称。默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要修改的表所在数据集的名称。默认为请求中的 defaultDataset
。
table_name
是您要更改的表的名称。
table_set_options_list
可通过选项列表设置标签和到期时间等表选项。可使用逗号分隔列表包括多个选项。
请按以下格式指定表选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
partition_expiration_days |
|
示例: 此属性等效于 timePartitioning.expirationMs 表资源属性,但单位是天而非毫秒。一天相当于 8640 万毫秒(即 24 小时)。 只能对分区表设置此属性。 |
require_partition_filter |
|
示例: 此属性等效于 timePartitioning.requirePartitionFilter 表资源属性。 只能对分区表设置此属性。 |
kms_key_name |
|
示例: 此属性等效于 encryptionConfiguration.kmsKeyName 表资源属性。 请查看有关使用 Cloud KMS 密钥保护数据的详细信息。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
VALUE
是一个只包含文字、查询参数和标量函数的常数表达式。如果常量表达式的计算结果为 null
,则忽略对应的 NAME
选项。
常量表达式不得包含以下内容:
- 对表的引用
- 子查询和 SQL 语句,如
SELECT
、CREATE
和UPDATE
- 用户定义的函数、聚合函数或分析函数
- 以下标量函数:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
设置 VALUE
会替换表的该选项的现有值(如果有)。将 VALUE
设置为 NULL
会清除表中该选项的值。
示例
设置表上的到期时间戳和说明
以下示例将表上的到期时间戳设置为执行 ALTER TABLE
语句后七天,还设置了说明:
ALTER TABLE mydataset.mytable SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="Table that expires seven days from now" )
对分区表设置 require partition filter 特性
以下示例会对分区表设置 timePartitioning.requirePartitionFilter
特性:
ALTER TABLE mydataset.mypartitionedtable SET OPTIONS (require_partition_filter=true)
引用此表的查询必须对分区列使用过滤条件,否则 BigQuery 会返回错误。将此选项设置为 true
有助于避免查询超过预期的数据量。
清除表上的到期时间戳
以下示例清除表上的到期时间戳,使其不会过期:
ALTER TABLE mydataset.mytable SET OPTIONS (expiration_timestamp=NULL)
ALTER TABLE ADD COLUMN
语句
ALTER TABLE ADD COLUMN
语句会向现有表架构添加一个或多个新列。如需详细了解 BigQuery 中的架构修改,请参阅修改表架构。
ALTER TABLE [[project_name.]dataset_name.]table_name
ADD COLUMN [IF NOT EXISTS] column_name column_schema [, ...]
其中:
project_name
是包含该表的项目的名称。默认为运行此 DDL 查询的项目。dataset_name
是包含该表的数据集的名称。table_name
是要更改的表的名称。该表必须已存在且具有架构。column_name
是要添加的列的名称。column_schema
是列的架构。此架构使用的语法与CREATE TABLE
语句的列架构相同。
您无法使用此语句创建以下列:
- 分区列。
- 聚簇列。
- 现有
RECORD
字段中的嵌套列。
不能向现有表架构添加 REQUIRED
列。但是,您可以创建嵌套的 REQUIRED
列以作为新的 RECORD
字段的一部分。
在没有 IF NOT EXISTS
子句的情况下,如果表已包含使用该名称的列,则语句将返回错误。如果包含 IF NOT EXISTS
子句且列名已存在,则系统不会返回任何错误,也不会执行任何操作。
现有行对应的新列的值设置为下列值之一:
- 如果采用
NULLABLE
模式添加了新列,则为NULL
。这是默认模式。 - 如果采用
REPEATED
模式添加了新列,则为空的ARRAY
。
示例
添加列
以下示例会将以下列添加到名为 mytable
的现有表中:
- 类型为
STRING
的列A
。 - 类型为
GEOGRAPHY
的列B
。 - 类型为
NUMERIC
且采用REPEATED
模式的列C
。 - 类型为
DATE
且具有说明的列D
。
ALTER TABLE mydataset.mytable
ADD COLUMN A STRING,
ADD COLUMN IF NOT EXISTS B GEOGRAPHY,
ADD COLUMN C ARRAY<NUMERIC>,
ADD COLUMN D DATE OPTIONS(description="my description")
如果已存在名为 A
、C
或 D
的任意列,则语句将失败。如果已存在列 B
,则由于 IF NOT
EXISTS
子句的原因,语句将会成功。
添加 RECORD
列
以下示例添加了一个名为 A
且类型为 STRUCT
的列,其中包含以下嵌套列:
- 类型为
GEOGRAPHY
的列B
。 - 类型为
INT64
且采用REPEATED
模式的列C
。 - 类型为
INT64
且采用REQUIRED
模式的列D
。 - 类型为
TIMESTAMP
且具有说明的列E
。
ALTER TABLE mydataset.mytable
ADD COLUMN A STRUCT<
B GEOGRAPHY,
C ARRAY<INT64>,
D INT64 NOT NULL,
E TIMESTAMP OPTIONS(description="creation time")
>
如果表已有名为 A
的列,则即使该列不包含任何指定的嵌套列,查询也会失败。
名为 A
的新 STRUCT
可为 null,但 A
的任何 STRUCT
值都需要 A
中的嵌套列 D
。
ALTER VIEW SET OPTIONS
语句
如需在 BigQuery 中设置视图中的选项,请使用 ALTER VIEW SET OPTIONS
DDL 语句。
ALTER VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name SET OPTIONS(view_set_options_list)
其中:
IF EXISTS
:如果存在,则指定的视图不存在时查询会成功。如果不存在,则指定的视图不存在时查询会失败。
project_name
是要修改的视图所在项目的名称。默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要修改的视图所在数据集的名称。默认为请求中的 defaultDataset
。
view_name
是您要更改的视图的名称。
view_set_options_list
通过选项列表,您可以设置视图选项,例如标签和到期时间。可使用逗号分隔列表包括多个选项。
请按以下格式指定视图选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
VALUE
是一个只包含文字、查询参数和标量函数的常数表达式。如果常量表达式的计算结果为 null
,则忽略对应的 NAME
选项。
常量表达式不得包含以下内容:
- 对表的引用
- 子查询和 SQL 语句,如
SELECT
、CREATE
和UPDATE
- 用户定义的函数、聚合函数或分析函数
- 以下标量函数:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
设置 VALUE
会替换视图上该选项的现有值(如果有)。将 VALUE
设置为 NULL
会清除视图上该选项的值。
示例
设置视图上的到期时间戳和说明
以下示例将视图上的到期时间戳设置为执行 ALTER VIEW
语句后七天,还设置了说明:
ALTER VIEW mydataset.myview SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="View that expires seven days from now" )
ALTER MATERIALIZED VIEW SET OPTIONS
语句
如需在 BigQuery 中设置具体化视图选项,请使用 ALTER MATERIALIZED VIEW SET OPTIONS
DDL 语句。
ALTER MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]materialized_view_name SET OPTIONS(materialized_view_set_options_list)
其中:
IF EXISTS
:如果存在,则指定的视图不存在时查询会成功。如果不存在,则指定的视图不存在时查询会失败。
project_name
是要修改的具体化视图所在项目的名称。默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要修改的具体化视图所在数据集的名称。默认为请求中的 defaultDataset
。
materialized_view_name
是您要更改的具体化视图的名称。
materialized_view_set_options_list
通过选项列表,您可以设置具体化视图选项,例如是否启用刷新、刷新间隔、标签和到期时间。您可以使用逗号分隔列表添加多个选项。
请按以下格式指定具体化视图选项列表:
NAME=VALUE, ...
NAME
和 VALUE
必须是下述组合之一:
NAME |
VALUE |
详细信息 |
---|---|---|
enable_refresh |
BOOLEAN |
示例: |
refresh_interval_minutes |
FLOAT64 |
示例: |
expiration_timestamp |
TIMESTAMP |
示例: 此属性等效于 expirationTime 表资源属性。 |
friendly_name |
|
示例: 此属性等效于 friendlyName 表资源属性。 |
description |
|
示例: 此属性等效于 description 表资源属性。 |
labels |
|
示例: 此属性等效于 labels 表资源属性。 |
设置 VALUE
会针对具体化视图替换该选项的现有值(如果有)。将 VALUE
设置为 NULL
会针对具体化视图清除该选项的值。
示例
在具体化视图上设置启用刷新状态和刷新间隔
以下示例在具体化视图上启用了刷新功能并将刷新间隔设置为 20 分钟:
ALTER MATERIALIZED VIEW mydataset.my_mv
SET OPTIONS (
enable_refresh=true,
refresh_interval_minutes=20
)
DROP TABLE
语句
如需在 BigQuery 中删除表,请使用 DROP TABLE
DDL 语句。
DROP TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
其中:
IF EXISTS
:如果存在,则指定的表不存在时查询会成功。如果不存在,则指定的表不存在时查询会失败。
project_name
是要删除的表所在项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要删除的表所在数据集的名称。
默认为请求中的 defaultDataset
。
table_name
:要删除的表的名称。
示例
删除表
以下示例演示了如何删除 mydataset
中名为 mytable
的表:
DROP TABLE mydataset.mytable
如果数据集内不存在表名称,会返回以下错误:
Error: Not found: Table myproject:mydataset.mytable
仅当某个表存在时才删除该表
仅当某个表存在时,以下示例才会删除 mydataset
中名为 mytable
的表。如果数据集内不存在该表名称,则不会返回任何错误,也不会执行任何操作。
DROP TABLE IF EXISTS mydataset.mytable
DROP EXTERNAL TABLE
语句
DROP EXTERNAL TABLE
语句会删除外部表。
DROP EXTERNAL TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
其中:
project_name
是包含该表的项目的名称。默认为运行此 DDL 查询的项目。dataset_name
是包含该表的数据集的名称。table_name
是要删除的表的名称。
在没有 IF EXISTS
子句的情况下,如果外部表不存在,则语句将返回错误。如果包含 IF EXISTS
子句,而表不存在,则系统不会返回任何错误,也不会执行任何操作。
如果 table_name
存在但不是外部表,则语句会返回以下错误:
Cannot drop table_name which has type TYPE. An
external table was expected.
DROP EXTERNAL
语句只会从 BigQuery 中移除外部表定义。存储在外部位置的数据不受影响。
示例
以下示例会丢弃数据集 mydataset
中名为 external_table
的外部表。如果外部表不存在,则该示例会返回错误。
DROP EXTERNAL TABLE mydataset.external_table
以下示例会丢弃数据集 mydataset
中名为 external_table
的外部表。如果外部表不存在,则系统不会返回任何错误。
DROP EXTERNAL TABLE IF EXISTS mydataset.external_table
DROP VIEW
语句
如需在 BigQuery 中删除视图,请使用 DROP VIEW
DDL 语句。
DROP VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name
其中:
IF EXISTS
:如果存在,则指定的视图不存在时查询会成功。如果不存在,则指定的视图不存在时查询会失败。
project_name
是要删除的视图所在项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要删除的视图所在数据集的名称。
默认为请求中的 defaultDataset
。
view_name
是您要删除的视图的名称。
示例
删除视图
以下示例演示如何删除 mydataset
中名为 myview
的视图:
DROP VIEW mydataset.myview
如果数据集内不存在视图名称,会返回以下错误:
Error: Not found: Table myproject:mydataset.myview
仅当某个视图存在时才删除该视图
仅当某个视图存在时,以下示例才会删除 mydataset
中名为 myview
的视图。如果数据集内不存在该视图名称,则不会返回任何错误,也不会执行任何操作。
DROP VIEW IF EXISTS mydataset.myview
DROP MATERIALIZED VIEW
语句
如需在 BigQuery 中删除具体化视图,请使用 DROP
MATERIALIZED VIEW
DDL 语句。
DROP MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]mv_name
其中:
IF EXISTS
:如果存在,则指定的具体化视图不存在时查询会成功。如果不存在,则指定的具体化视图不存在时查询会失败。
project_name
是要删除的具体化视图所在项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要删除的具体化视图所在数据集的名称。
默认为请求中的 defaultDataset
。
mv_name
是您要删除的具体化视图的名称。
示例
删除具体化视图
以下示例演示了如何删除 mydataset
中名为 my_mv
的具体化视图:
DROP MATERIALIZED VIEW mydataset.my_mv
如果数据集中不存在该具体化视图名称,会返回以下错误:
Error: Not found: Table myproject:mydataset.my_mv
如果要删除其他项目中的具体化视图,您必须按以下格式指定项目、数据集和具体化视图:`project_id.dataset.materialized_view`
(如果 project_id
包含特殊字符,则包括反引号);例如 `myproject.mydataset.my_mv`
。
仅当某个具体化视图存在时才删除该视图。
仅当某个具体化视图存在时,以下示例才会删除 mydataset
中名为 my_mv
的具体化视图。如果数据集中不存在该具体化视图名称,则不会返回任何错误,也不会执行任何操作。
DROP MATERIALIZED VIEW IF EXISTS mydataset.my_mv
如果要删除其他项目中的具体化视图,您必须按以下格式指定项目、数据集和具体化视图:`project_id.dataset.materialized_view`,
(如果 project_id
包含特殊字符,则包括反引号);例如 `myproject.mydataset.my_mv`
。
DROP FUNCTION
语句
DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name
其中:
IF EXISTS
:如果存在,则指定的函数不存在时查询会成功。如果不存在,则指定的函数不存在时查询会失败。
project_name
是要删除的函数所在项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要删除的函数所在数据集的名称。
默认为请求中的 defaultDataset
。
function_name
是您要删除的函数的名称。
示例
以下示例语句会删除数据集 mydataset
中包含的函数 parseJsonAsStruct
。
DROP FUNCTION mydataset.parseJsonAsStruct;
以下示例语句会删除项目 other_project
的数据集 sample_dataset
中的函数 parseJsonAsStruct
。
DROP FUNCTION `other_project`.sample_dataset.parseJsonAsStruct;
DROP PROCEDURE
语句
DROP PROCEDURE [IF EXISTS] [[project_name.]dataset_name.]procedure_name
其中:
IF EXISTS
:如果存在,则指定的过程不存在时查询会成功。如果不存在,则指定的过程不存在时查询会失败。
project_name
是要删除的过程所在项目的名称。
默认为运行此 DDL 查询的项目。如果项目名称包含特殊字符(例如英文冒号),则应使用反引号 `
将项目名称引起来(示例:`google.com:my_project`
)。
dataset_name
是要删除的过程所在数据集的名称。
默认为请求中的 defaultDataset
。
procedure_name
是您要删除的过程的名称。
示例
以下示例语句会删除数据集 mydataset
中包含的过程 myprocedure
。
DROP PROCEDURE mydataset.myProcedure;
以下示例语句会删除项目 other_project
的数据集 sample_dataset
中的过程 myProcedure
。
DROP PROCEDURE `other-project`.sample_dataset.myprocedure;