BigQuery 脚本
借助 BigQuery 脚本,您可以在一个请求中向 BigQuery 发送多个语句,可以使用变量以及使用 IF
和 WHILE
等控制流语句。例如,您可以声明变量,为其赋值,然后在第三个语句中对其进行引用。
在 BigQuery 中,脚本是按顺序执行的 SQL 语句列表。SQL 语句列表是以英文分号隔开的任何有效 BigQuery 语句的列表。
例如:
-- Declare a variable to hold names as an array. DECLARE top_names ARRAY<STRING>; -- Build an array of the top 100 names from the year 2017. SET top_names = ( SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100) FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE year = 2017 ); -- Which names appear as words in Shakespeare's plays? SELECT name AS shakespeare_name FROM UNNEST(top_names) AS name WHERE name IN ( SELECT word FROM `bigquery-public-data`.samples.shakespeare );
与任何其他查询类似,在 BigQuery 中,我们使用 jobs.insert
执行脚本,并将多语句脚本指定为查询文本。脚本执行时,系统会为脚本中的每个语句创建额外作业(称为子作业)。您可以通过调用 jobs.list
并将脚本的作业 ID 作为 parentJobId
参数传入,来枚举脚本的子作业。
对某个脚本调用 jobs.getQueryResults
时,它会返回要在该脚本中执行的最后一个 SELECT、DML 或 DDL 语句的查询结果;如果以上语句都未执行,则不会返回任何结果。如需获取脚本中所有语句的结果,请枚举这些子作业,并对每个作业调用 jobs.getQueryResults
。
BigQuery 会将任何具有多个语句的请求解释为脚本,除非这些语句包含 CREATE TEMP FUNCTION
语句,并且最后一个是查询语句。例如,以下内容不会被视为脚本:
CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y); SELECT Add(3, 4);
DECLARE
DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];
variable_name
必须为有效标识符,variable_type
为任何 BigQuery 类型。
说明
声明指定类型的变量。如果指定了 DEFAULT
子句,则使用表达式的值初始化变量;如果不存在 DEFAULT
子句,则使用值 NULL
初始化变量。
如果省略 [variable_type]
,则必须指定 DEFAULT
子句。变量类型将根据 DEFAULT
子句中的表达式类型推断得出。
变量声明必须出现在脚本的开头且在任何其他语句之前,或者出现在使用 BEGIN
声明的块的开头。变量名称不区分大小写。
多个变量名称可以出现在一个 DECLARE
语句中,但只能出现一个 variable_type
和 expression
。
声明与先前在当前块或包含块中声明的变量具有相同名称的变量是错误的。
如果存在 DEFAULT
子句,则表达式的值必须可以强制转换为指定的类型。表达式可以引用先前在同一个块或包含块中声明的其他变量。
变量的大小上限为 1 MB,脚本中使用的所有变量的大小上限为 10 MB。
示例
以下示例使用值NULL
将变量x
初始化为 INT64。
DECLARE x INT64;
以下示例使用当前日期的值将变量d
初始化为 DATE。
DECLARE d DATE DEFAULT CURRENT_DATE();
以下示例将变量 x
、y
和 z
初始化为使用值 0 的 INT64
。
DECLARE x, y, z INT64 DEFAULT 0;
以下示例声明了一个名为 item
的变量,该变量与 dataset1.products
表中的任意一项相对应。item
的类型根据表架构推断得出。
DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);
SET
语法
SET name = expression;
SET (variable_name_1, variable_name_2, …, variable_name_n) = (expression_1, expression_2, …, expression_n);
说明
将变量设置为具有提供的表达式的值,或根据多个表达式的结果同时设置多个变量。
SET
语句可能出现在脚本正文中的任何位置。
示例
以下示例将变量 x
设置为具有值 5。
SET x = 5;
以下示例将变量 a
的值设置为 4,将 b
的值设置为“foo”,并将变量 c
的值设置为 false
。
SET (a, b, c) = (1 + 3, 'foo', false);
以下示例将查询结果分配给多个变量。
首先,它声明两个变量:target_word
和 corpus_count
;然后,它将 SELECT AS STRUCT
查询的结果分配给这两个变量。查询的结果是包含带两个字段的 STRUCT
的单个行;第一个元素分配给第一个变量,第二个元素分配给第二个变量。
DECLARE target_word STRING DEFAULT 'methinks'; DECLARE corpus_count, word_count INT64; SET (corpus_count, word_count) = ( SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count) FROM `bigquery-public-data`.samples.shakespeare WHERE LOWER(word) = target_word ); SELECT FORMAT('Found %d occurrences of "%s" across %d Shakespeare works', word_count, target_word, corpus_count) AS result;
此语句列表输出以下字符串:
Found 151 occurrences of "methinks" across 38 Shakespeare works
EXECUTE IMMEDIATE
语法
EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];
sql_expression:
{ "query_statement" | expression("query_statement") }
identifier:
{ variable | value } [ AS alias ]
说明
即时执行动态 SQL 语句。
sql_expression
:表示查询语句、可对查询语句使用的表达式、一个 DDL 语句或一个 DML 语句。expression
:可以是函数、条件表达式或表达式子查询。query_statement
:表示要执行的有效独立 SQL 语句。如果此语句返回值,则INTO
子句必须包含相同类型的值。您可以同时访问USING
子句中存在的系统变量和值;所有其他局部变量和查询参数都不会提供给查询语句。INTO
子句:在执行 SQL 表达式后,您可以使用INTO
子句将结果存储在一个或多个变量中。USING
子句:在执行 SQL 表达式之前,您可以将USING
子句中的一个或多个标识符传入 SQL 表达式中。这些标识符的功能类似于查询参数,用于将值提供给查询语句。标识符可以是变量或值。
您可以在 USING
子句中引用的标识符的 query_statement
中添加以下占位符:
?
:此占位符的值按索引绑定到USING
子句中的标识符。-- y = 1 * (3 + 2) = 5 EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
@identifier
:此占位符的值按名称绑定到USING
子句中的标识符。此语法与查询参数语法相同。-- y = 1 * (3 + 2) = 5 EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
以下是关于 EXECUTE IMMEDIATE
语句行为的一些其他说明:
EXECUTE IMMEDIATE
被限制不能作为嵌套元素动态执行。- 如果
EXECUTE IMMEDIATE
语句返回结果,则这些结果将成为整个语句的结果,并且所有相应的系统变量会进行更新。 - 同一个变量可以同时出现在
INTO
和USING
子句中。 query_statement
可以包含一个已解析语句,并且该语句包含其他语句(例如 BEGIN...END)- 如果从
query_statement
(包括从零行值表)返回零行,则INTO
子句中的所有变量都会设置为 NULL。 - 如果从
query_statement
(包括从零行值表)返回一行,则系统会按位置(而非变量名称)分配值。 - 如果存在
INTO
子句,则在尝试从query_statement
返回多行时系统会抛出错误。
示例
在此示例中,我们会创建一个图书表,并在其中填充数据。请注意,您可以通过不同的方式来引用变量、将值保存到变量以及使用表达式。
-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;
-- Create a temporary table called Books.
EXECUTE IMMEDIATE
"CREATE TEMP TABLE Books (title STRING, publish_date INT64)";
-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
"INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";
-- add a row for Ulysses, using the variables declared at the top of this
-- script and the ? placeholder
EXECUTE IMMEDIATE
"INSERT INTO Books (title, publish_date) VALUES(?, ?)"
USING book_name, book_year;
-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
"INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
USING 1815 as year, "Emma" as name;
-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
CONCAT("INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)");
-- save the publish date of the first book, Hamlet, to a variable called first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;
+------------------+------------------+
| title | publish_date |
+------------------+------------------+
| Hamlet | 1599 |
| Ulysses | 1922 |
| Emma | 1815 |
| Middlemarch | 1871 |
+------------------+------------------+
BEGIN
语法
BEGIN sql_statement_list END;
说明
BEGIN
启动语句块,其中,声明的变量仅在相应的 END
之前存在。sql_statement_list
列表包含以分号结尾的零个或多个 SQL 语句。
变量声明必须出现在块的开头,并且在其他类型的语句之前。在块中声明的变量只能在该块中和任何嵌套块中引用。声明与在同一个块或外部块中声明的变量具有相同名称的变量是错误的。
块和条件语句(例如 BEGIN
/END
、IF
/ELSE
/END IF
与 WHILE
/END WHILE
)的最大嵌套级别为 50。
BEGIN
/END
被限制不能作为嵌套元素动态执行。
示例
以下示例声明一个使用默认值 10 的变量 x
;然后开始一个语句块,在块中为变量 y
分配 x
的值(即为 10),并返回该值;接着以 END
语句结束该块,结束变量 y
的范围;最后,返回 x
的值。
DECLARE x INT64 DEFAULT 10; BEGIN DECLARE y INT64; SET y = x; SELECT y; END; SELECT x;
BEGIN...EXCEPTION
语法
BEGIN sql_statement_list EXCEPTION WHEN ERROR THEN sql_statement_list END;
说明
BEGIN...EXCEPTION
执行语句块。如果任何语句遇到错误,脚本会跳过块的其余部分并执行 EXCEPTION
子句中的语句。
在 EXCEPTION
子句中,您可使用以下 EXCEPTION
系统变量查看错误的详细信息:
名称 | 类型 | 说明 |
---|---|---|
@@error.formatted_stack_trace |
STRING |
@@error.stack_trace 的内容,以人类可读的字符串表示。该值仅用于显示目的,如有变更,恕不另行通知。如需以编程方式访问错误的堆栈轨迹,请改用 @@error.stack_trace 。 |
@@error.message |
STRING |
指定人类可读的错误消息。 |
@@error.stack_trace |
请参阅 1。 | 数组的每个元素都对应一个在出错时执行的语句或过程调用,当前正在执行的堆栈帧显示在最前面。每个字段的含义定义如下:
|
@@error.statement_text |
STRING |
指定导致错误的语句文本。 |
1 @@error.stack_trace
的类型为 ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>
。
由于 BigQuery 保留随时修订错误消息的权利,因此 @@error.message
的使用者不应指望错误消息保持不变或遵循任何特定模式。请勿通过从错误消息中提取文本来获取错误位置信息,而应改用 @@error.stack_trace
和 @@error.statement_text
。
如需处理由异常处理程序本身抛出(和未处理)的异常,必须使用单独的异常处理程序将块封装在外部块中。
下面演示了如何将外部块与单独的异常处理程序结合使用:
BEGIN BEGIN ... EXCEPTION WHEN ERROR THEN SELECT 1/0; END; EXCEPTION WHEN ERROR THEN -- The exception thrown from the inner exception handler lands here. END;
与任何其他 BEGIN
块一样,BEGIN...EXCEPTION
块也支持 DECLARE
语句。在 BEGIN
块中声明的变量仅在 BEGIN
部分有效,不能在块的异常处理程序中使用。
示例
CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN SELECT 1/0; END; CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN CALL dataset1.proc1(); END; BEGIN CALL dataset1.proc2(); EXCEPTION WHEN ERROR THEN SELECT @@error.message, @@error.stack_trace, @@error.statement_text, @@error.formatted_stack_trace; END;
在此示例中,如果发生除数为 0 的错误,BigQuery 将停止 dataset1.proc1()
和 dataset1.proc2()
,并在异常处理程序中执行 SELECT 语句,而不是停止整个脚本。当异常处理程序运行时,变量将具有以下值:
变量 | 值 |
---|---|
@@error.message |
"Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]" |
@@error.stack_trace |
[ STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location), STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location), STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location), ] |
@@error.statement_text |
"SELECT 1/0" |
@@error.formatted_stack_trace |
"At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]" |
END
结束由 BEGIN
启动的块。BEGIN
/END
被限制不能作为嵌套元素动态执行。
IF
语法
IF condition THEN [sql_statement_list] [ELSEIF condition THEN sql_statement_list] [ELSEIF condition THEN sql_statement_list]... [ELSE sql_statement_list] END IF;
说明
如果条件为 true,则执行第一个 sql_statement_list
;如果没有条件匹配,则执行可选的 ELSE
sql_statement_list
。
块和条件语句(例如 BEGIN
/END
、IF
/ELSE
/END IF
与 WHILE
/END WHILE
)的最大嵌套级别为 50。
IF
被限制不能作为嵌套元素动态执行。
示例
以下示例声明一个包含默认值 103 的 INT64 变量 target_product_id
;然后,它检查表 dataset.products
是否包含 product_id
列与 target_product_id
的值相匹配的行;如果包含,它将输出一个说明产品已找到的字符串以及 default_product_id
的值;如果不包含,则将输出一个说明产品未找到的字符串以及 default_product_id
的值。
DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
WHERE product_id = target_product_id) THEN
SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSEIF EXISTS (SELECT 1 FROM dataset.more_products
WHERE product_id = target_product_id) THEN
SELECT CONCAT('found product from more_products table',
CAST(target_product_id AS STRING));
ELSE
SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;
循环
LOOP
语法
LOOP sql_statement_list END LOOP;
说明
执行 sql_statement_list
,直到 BREAK
或 LEAVE
语句退出循环。sql_statement_list
列表包含以英文分号结尾的零个或零个以上的 SQL 语句。LOOP
被限制不能作为嵌套元素动态执行。
示例
以下示例首先声明一个使用默认值 0 的变量 x
;然后,它使用 LOOP
语句创建一个在变量 x
大于或等于 10 之前一直执行的循环;循环终止后,示例将输出 x
的值。
DECLARE x INT64 DEFAULT 0; LOOP SET x = x + 1; IF x >= 10 THEN LEAVE; END IF; END LOOP; SELECT x;
此示例输出以下内容:
+----+
| x |
+----+
| 10 |
+----+
WHILE
语法
WHILE boolean_expression DO
sql_statement_list
END WHILE;
块和条件语句(例如 BEGIN
/END
、IF
/ELSE
/END IF
与 WHILE
/END WHILE
)的最大嵌套级别为 50。
说明
当 boolean_expression
为 true 时,执行 sql_statement_list
。
系统会针对循环的每次迭代计算 boolean_expression
。WHILE
被限制不能作为嵌套元素动态执行。
BREAK
说明
退出当前循环。
在循环外使用 BREAK
是错误的。
示例
以下示例首先声明 heads
和 heads_count
两个变量;接着,它开始一个向 heads
分配随机布尔值的循环;然后检查 heads
是否为 true(如果是,则输出“Heads!”并将 heads_count
递增;如果不是,则输出“Tails!”并退出循环);最后,它输出一个字符串,说明“扔硬币”的结果为“正面”的次数。
DECLARE heads BOOL; DECLARE heads_count INT64 DEFAULT 0; LOOP SET heads = RAND() < 0.5; IF heads THEN SELECT 'Heads!'; SET heads_count = heads_count + 1; ELSE SELECT 'Tails!'; BREAK; END IF; END LOOP; SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');
退出
相当于 BREAK
。
CONTINUE
说明
跳过当前循环中的任何以下语句,并返回到循环的开头。
在循环外使用 CONTINUE
是错误的。
示例
以下示例声明 heads
和 heads_count
两个变量;接着,它开始一个向 heads
分配随机布尔值的循环;然后检查 heads
是否为 true(如果是,则输出“Heads!”、将 heads_count
递增,然后重启循环并跳过任何剩余语句;如果不是,则输出“Tails!”并退出循环);最后,它输出一个字符串,说明“扔硬币”的结果为“正面”的次数。
DECLARE heads BOOL; DECLARE heads_count INT64 DEFAULT 0; LOOP SET heads = RAND() < 0.5; IF heads THEN SELECT 'Heads!'; SET heads_count = heads_count + 1; CONTINUE; END IF; SELECT 'Tails!'; BREAK; END LOOP; SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');
ITERATE
相当于 CONTINUE
。
RAISE
语法
RAISE [USING MESSAGE = message];
说明
在提供 USING
MESSAGE = message
时,选择性地使用指定的错误消息引发错误。
未提供 USING MESSAGE
时
RAISE
语句只能在 EXCEPTION
子句中使用。RAISE
语句将重新引发被捕获的异常,并保留原始堆栈轨迹。
提供 USING MESSAGE
时
如果 RAISE
语句包含在 BEGIN...EXCEPTION
块的 BEGIN
部分中:
- 则会调用处理程序。
@@error.message
的值将与提供的message
字符串完全匹配(如果message
为NULL
,则该值可能为NULL
)。堆栈轨迹将设置为
RAISE
语句。
如果 RAISE
语句未包含在 BEGIN...EXCEPTION
块的 BEGIN
部分中,则 RAISE
语句将停止脚本的执行,并提供错误消息。
RETURN
在 BigQuery 脚本中,RETURN
会停止当前脚本的执行。
CALL
语法
CALL procedure_name (procedure_argument[, …])
说明
使用参数列表调用过程。
procedure_argument
可能是变量或表达式。对于 OUT
或 INOUT
参数,作为参数传递的变量必须具有正确的 BigQuery 类型。
同一个变量不能在过程的参数列表中作为 OUT
或 INOUT
参数多次出现。
过程调用的最大深度为 50 帧。
CALL
被限制不能作为嵌套元素动态执行。
示例
以下示例声明一个变量 retCode
。然后,它在数据集 myDataset
中调用过程 updateSomeTables
,并传递参数 'someAccountId'
和 retCode
。最后,它返回 retCode
的值。
DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;
系统变量
您可以在脚本执行期间使用系统变量检查信息。
名称 | 类型 | 说明 |
---|---|---|
@@current_job_id |
STRING |
当前正在执行的作业的 ID。在脚本的上下文中,这将返回负责当前语句的作业,而不是整个脚本。 |
@@last_job_id |
STRING |
要在当前脚本中执行的最新作业(不包括当前作业)的 ID。如果脚本包含 CALL 语句,则此作业可能源自另一个过程。 |
@@project_id |
STRING |
用于执行当前查询的项目的 ID。在过程的上下文中,@@project_id 是指运行脚本的项目,而不是拥有该过程的项目。 |
@@row_count |
INT64 |
如果在脚本中使用且前一个脚本语句为 DML,则指定由于该 DML 语句而修改、插入或删除的行数。如果上一个语句是 MERGE 语句,则 @@row_count 表示插入、移除和删除的总行数。如果不在脚本中,则此值为 NULL 。 |
@@script.bytes_billed |
INT64 |
到目前为止,正在执行的脚本作业中结算的总字节数。如果不在脚本中,则此值为 NULL 。 |
@@script.bytes_processed |
INT64 |
到目前为止,正在执行的脚本作业中处理的总字节数。如果不在脚本中,则此值为 NULL 。 |
@@script.creation_time |
TIMESTAMP |
当前正在执行的脚本作业的创建时间。如果不在脚本中,则此值为 NULL 。 |
@@script.job_id |
STRING |
当前正在执行的脚本作业的 ID。如果不在脚本中,则此值为 NULL 。 |
@@script.num_child_jobs |
INT64 |
当前已完成的子作业数。如果不在脚本中,则此值为 NULL 。 |
@@script.slot_ms |
INT64 |
到目前为止,脚本使用的槽毫秒数。如果不在脚本中,则此值为 NULL 。 |
@@time_zone |
STRING |
在依赖时区的 SQL 函数中使用的默认时区(如果未将显式时区指定为参数)。与其他系统变量不同,@@time_zone 可通过对任何有效时区名称使用 SET 语句来进行修改。在每个脚本的开头,@@time_zone 会以“UTC”开头。 |
除了上面显示的系统变量之外,您还可以在脚本执行期间使用 EXCEPTION
系统变量。如果未在异常处理程序中使用 EXCEPTION
系统变量,则此类变量为 NULL
。以下是 EXCEPTION
系统变量。
@@error.formatted_stack_trace
@@error.message
@@error.stack_trace
@@error.statement_text
如需详细了解 EXCEPTION
系统变量,请参阅 BEGIN...EXCEPTION。
权限
在执行语句时,BigQuery 会检查访问表、模型或其他资源的权限。如果未执行语句,或者未计算表达式,则 BigQuery 不会检查执行脚本的用户是否有权访问其引用的任何资源。
在脚本中,针对每个表达式或语句的权限会单独进行验证。例如,请考虑以下脚本:
SELECT * FROM dataset_with_access.table1; SELECT * FROM dataset_without_access.table2;
如果执行脚本的用户有权访问 table1
但无权访问 table2
,则第一个查询将成功,第二个查询将失败。脚本作业本身也将失败。
安全限制条件
虽然动态 SQL 很方便,但可能会为滥用提供新的可能性。例如,执行以下查询会构成潜在的安全威胁,因为表参数可能经过不适当的过滤,允许访问非预期表,以及对非预期表执行操作。
EXECUTE IMMEDIATE CONCAT(‘SELECT * FROM ’, @employee_table);
为避免公开或泄露表中的敏感数据,或避免运行 DROP TABLE
等命令而删除表中的数据,BigQuery 的动态 SQL 脚本支持以下多种安全措施,以减少遭到 SQL 注入攻击的风险:
- 您不能执行在传入到动态 SQL 的参数中嵌入的多个 SQL 语句。
- 以下命令被限制不能动态执行:
BEGIN
/END
、CALL
、IF
、LOOP
、WHILE
、EXECUTE IMMEDIATE
。
配置字段限制
您无法为脚本设置以下查询配置字段:
clustering
create_disposition
destination_table
destination_encryption_configuration
range_partitioning
schema_update_options
time_partitioning
user_defined_function_resources
write_disposition