使用标准 SQL 编写脚本

BigQuery 脚本

借助 BigQuery 脚本,您可以在一个请求中向 BigQuery 发送多个语句,可以使用变量以及使用 IFWHILE 等控制流语句。例如,您可以声明变量,为其赋值,然后在第三个语句中对其进行引用。

在 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 会将任何具有多个语句的请求解释为脚本,除非这些语句由 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 初始化变量。

变量声明必须出现在脚本的开头,并且在任何其他语句之前,或者出现在使用 BEGIN 声明的块的开头。变量名称不区分大小写。

多个变量名称可以出现在一个 DECLARE 语句中,但只能出现一个 variable_typeexpression

声明与先前在当前块或包含块中声明的变量具有相同名称的变量是错误的。

如果存在 DEFAULT 子句,则表达式的值必须可以强制转换为指定的类型。表达式可以引用先前在同一个块或包含块中声明的其他变量。

变量的大小上限为 1 MB,脚本中使用的所有变量的大小上限为 10 MB。

示例

以下示例使用值NULL将变量x初始化为 INT64。

DECLARE x INT64;

以下示例使用当前日期的值将变量d初始化为 DATE。

DECLARE d DATE DEFAULT CURRENT_DATE();

以下示例将变量 xyz 初始化为具有值 0 的 INT64

DECLARE x, y, z INT64 DEFAULT 0;

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_wordcorpus_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

BEGIN

语法

BEGIN
  sql_statement_list
END;

说明

BEGIN 启动语句块,其中,声明的变量仅在相应的 END 之前存在。sql_statement_list 列表包含以分号结尾的零个或多个 SQL 语句。

变量声明必须出现在块的开头,并且在其他类型的语句之前。在块中声明的变量只能在该块中和任何嵌套块中引用。声明与在同一个块或外部块中声明的变量具有相同名称的变量是错误的。

块和条件语句(例如 BEGIN/ENDIF/ELSE/END IFWHILE/END WHILE)的最大嵌套级别为 50。

示例

以下示例声明一个包含默认值 10 的变量 x;然后,它启动一个块,块中的变量 y 分配有 的值 10,并返回该值;接着,END 语句终止该块,结束变量 y 的范围;最后,它返回 的值。

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

END

终止由 BEGIN 启动的块。

IF

语法

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

说明

如果 condition 为 true,则执行 if_statement_list。否则,如果提供了可选的 ELSE 子句,则执行 else_statement_list

块和条件语句(例如 BEGIN/ENDIF/ELSE/END IFWHILE/END WHILE)的最大嵌套级别为 50。

示例

以下示例声明一个包含默认值 103 的 INT64 变量 target_product_id;然后,它检查表 dataset.products 是否包含 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));
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,直到 BREAKLEAVE 语句退出循环。sql_statement_list 列表包含以分号结尾的零个或多个 SQL 语句。

示例

以下示例声明一个包含默认值 0 的变量 x;然后,它使用 LOOP 语句创建一个在变量 大于或等于 10 之前一直执行的循环;循环终止后,示例将输出 的值。

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/ENDIF/ELSE/END IFWHILE/END WHILE)的最大嵌套级别为 50。

说明

boolean_expression 为 true 时,执行 sql_statement_list。 为循环的每次迭代计算 boolean_expression

BREAK

说明

退出当前循环。

在循环外使用 BREAK 是错误的。

示例

以下示例声明两个变量 headsheads_count;然后,它启动一个向 分配随机布尔值的循环,然后检查 是否为 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');

LEAVE

相当于 BREAK

CONTINUE

说明

跳过当前循环中的任何以下语句,并返回到循环的开头。

在循环外使用 CONTINUE 是错误的。

示例

以下示例声明两个变量 headsheads_count;然后,它启动一个向 分配随机布尔值的循环,然后检查 是否为 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

RETURN

在 BigQuery 脚本中,RETURN 会终止当前脚本的执行。

CALL

语法

CALL procedure_name (procedure_argument[, …])

说明

使用参数列表调用过程procedure_argument 可能是变量或表达式。对于 OUTINOUT 参数,作为参数传递的变量必须具有正确的 BigQuery 类型

同一个变量不能在过程的参数列表中作为 OUTINOUT 参数多次出现。

过程调用的最大深度为 50 帧。

示例

以下示例声明一个变量 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;