使用多语句查询

本文档介绍了如何在 BigQuery 中使用多语句查询，例如如何编写多语句查询、在多语句查询中使用临时表、在多语句查询中引用变量以及调试多语句查询。

多语句查询是可以在一个请求中执行的一组 SQL 语句。使用多语句查询，您可以按顺序运行多个具有共享状态的语句。多语句查询可能会产生副作用，例如添加或修改表数据。

多语句查询通常在存储过程中使用，并且支持过程语言语句，可让您执行定义变量和实现控制流等操作。

编写、运行和保存多语句查询

多语句查询由一个或多个以分号分隔的 SQL 语句组成。任何有效的 SQL 语句都可以在多语句查询中使用。多语句查询还可以包括过程语言语句，这允许您在 SQL 语句中使用变量或实现控制流。

编写多语句查询

您可以在 BigQuery 中编写多语句查询。以下多查询语句查询声明一个变量并在 IF 语句中使用该变量：

DECLARE day INT64;
SET day = (SELECT EXTRACT(DAYOFWEEK from CURRENT_DATE));
if day = 1 or day = 7 THEN
  SELECT 'Weekend';
ELSE
  SELECT 'Weekday';
END IF

BigQuery 会将任何具有多个语句的请求解释为多语句查询，除非这些语句完全由 CREATE TEMP FUNCTION 语句组成，并且最后是一个 SELECT 语句。例如，以下内容不会被视为多语句查询：

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);
SELECT Add(3, 4);

运行多语句查询

您可以按照与任何其他查询相同的方式运行多语句查询，例如，在 Google Cloud 控制台中或使用 bq 命令行工具。

试运行多语句查询

如需估算多语句查询读取的字节数，请考虑试运行。多语句查询的试运行最适合仅包含 SELECT 语句的查询。

试运行会对以下查询和语句类型进行特殊处理：

• CALL 语句：试运行会验证被调用的过程是否存在，并且具有与提供的参数匹配的签名。调用的过程的内容和 CALL 语句之后的所有语句都不会予以验证。
• DDL 语句：试运行会验证第一个 DDL 语句，然后停止。所有后续语句都会被跳过。
• DML 语句：试运行会验证 DML 语句，然后继续验证后续语句。在这种情况下，字节估算值会以原始表大小为基础，而不考虑 DML 语句的结果。
• EXECUTE IMMEDIATE 语句：试运行会验证查询表达式，但不会评估动态查询本身。系统会跳过 EXECUTE IMMEDIATE 语句之后的所有语句。
• 在分区过滤条件中使用变量的查询：试运行会验证初始查询和后续语句。但是，试运行无法计算分区过滤条件中变量的运行时值。这会影响读取字节数的估算值。
• 在 FOR SYSTEM TIME AS OF 子句的时间戳表达式中使用变量的查询：试运行会使用表的当前内容，并忽略 FOR SYSTEM TIME AS OF 子句。如果当前表与表的先前迭代之间存在大小差异，则会影响读取字节数的估算值。
• FOR、IF 和 WHILE 控制语句：试运行会立即停止。条件表达式、控制语句的正文以及所有后续语句均未经验证。

我们会尽力进行试运行，而且底层流程可能会更改。试运行须受以下规定约束：

• 成功完成试运行的查询可能无法成功执行。例如，由于试运行未检测到的原因，查询可能会在运行时失败。
• 成功执行的查询可能无法成功完成试运行。例如，由于执行时发现的原因，查询可能会无法完成试运行。
• 今天成功完成的试运行并不能保证未来始终能够进行试运行。例如，对试运行实施的更改可能会检测到之前未检测到的查询中的错误。

保存多语句查询

如需保存多语句查询，请参阅使用已保存的查询。

在多语句查询中使用变量

多语句查询可以包含用户创建的变量和系统变量。

• 您可以声明用户创建的变量、为其赋值以及在查询中引用它们。

• 您可以在查询中引用系统变量以及为部分变量赋值，但与用户定义的变量不同，您可以不声明这些变量。系统变量内置于 BigQuery 中。

声明用户创建的变量

您必须在多语句查询的开头或 BEGIN 块的开头声明用户创建的变量。在多语句查询开头声明的变量的范围是整个查询。在 BEGIN 块内声明的变量的范围是该块。它们的范围在相应的 END 语句之后结束。变量的大小上限为 1 MB，一个多语句查询中使用的所有变量的大小上限为 10 MB。

您可以使用 DECLARE 过程语句声明变量，如下所示：

DECLARE x INT64;

BEGIN
DECLARE y INT64;
-- Here you can reference x and y
END;

-- Here you can reference x, but not y

设置用户创建的变量

声明变量后，您可以使用 SET 过程语句为其分配值，如下所示：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

设置系统变量

您不能创建系统变量，但可以替换其中一些的默认值：

SET @@dataset_project_id = 'MyProject';

您还可以在多语句查询中设置和隐式使用系统变量。例如，在以下查询中，每次您希望创建新表时都必须添加项目：

BEGIN
  CREATE TABLE MyProject.MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyProject.MyDataset.MyTempTableB (id STRING);
END;

如果您不想将项目多次添加到表路径，可以在多语句查询中将数据集项目 ID MyProject 分配给 @@dataset_project_id 系统变量。此分配使 MyProject 成为查询其余部分的默认项目。

SET @@dataset_project_id = 'MyProject';

BEGIN
  CREATE TABLE MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyDataset.MyTempTableB (id STRING);
END;

同样，您可以设置 @@dataset_id 系统变量来为查询分配默认数据集。例如：

SET @@dataset_project_id = 'MyProject';
SET @@dataset_id = 'MyDataset';

BEGIN
  CREATE TABLE MyTempTableA (id STRING);
  CREATE TABLE MyTempTableB (id STRING);
END;

您还可以在多语句查询的许多部分中明确引用 @@dataset_id 等系统变量。如需了解详情，请参阅引用系统变量。

引用用户创建的变量

声明并设置用户创建的变量后，您可以在多语句查询中引用该变量。如果变量和列具有相同的名称，则列优先。

此操作会返回 column x + column x：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS x)
SELECT (x+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 100    |
+--------+

此操作会返回 column y + variable x：

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS y)
SELECT (y+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 60     |
+--------+

引用系统变量

您可以在多语句查询中引用系统变量。

以下查询会返回默认时区：

BEGIN
  SELECT @@time_zone AS default_time_zone;
END;

+-------------------+
| default_time_zone |
+-------------------+
| UTC               |
+-------------------+

您可以将系统变量与 DDL 和 DML 查询搭配使用。例如，以下是在创建和更新表时使用系统变量 @@time_zone 的几种方法：

BEGIN
  CREATE TEMP TABLE MyTempTable
  AS SELECT @@time_zone AS default_time_zone;
END;

BEGIN
  CREATE OR REPLACE TABLE MyDataset.MyTable(default_time_zone STRING)
  OPTIONS (description = @@time_zone);
END;

BEGIN
  UPDATE MyDataset.MyTable
  SET default_time_zone = @@time_zone
  WHERE TRUE;
END;

在一些位置，系统变量不能用于 DDL 和 DML 查询。例如，不能将系统变量用作项目名称、数据集或表名称。当您尝试将 @@dataset_id 系统变量添加到表路径中时，这会生成错误：

BEGIN
  CREATE TEMP TABLE @@dataset_id.MyTempTable (id STRING);
END;

在多语句查询中使用临时表

借助临时表，您可以将中间结果保存到表中。这些临时表存在于会话级层，因此您无需在数据集中保存或维护这些表。

您可以在多语句查询中创建和引用临时表。使用完临时表后，您可以将其手动删除，也可以等待 BigQuery 在 24 小时后将其删除。

创建临时表

您可以使用 CREATE TABLE 语句为多语句查询创建临时表。以下示例创建了一个临时表来存储查询结果，并在子查询中使用该临时表：

-- Find the top 100 names from the year 2017.
CREATE TEMP TABLE top_names(name STRING)
AS
 SELECT name
 FROM `bigquery-public-data`.usa_names.usa_1910_current
 WHERE year = 2017
 ORDER BY number DESC LIMIT 100
;
-- Which names appear as words in Shakespeare's plays?
SELECT
 name AS shakespeare_name
FROM top_names
WHERE name IN (
 SELECT word
 FROM `bigquery-public-data`.samples.shakespeare
);

除了使用 TEMP 或 TEMPORARY 之外，该语法与 CREATE TABLE 语法相同。

创建临时表时，请勿在表名称中使用项目或数据集限定符。该表是在特殊数据集中自动创建的。

引用临时表

在当前多语句查询期间，您可以根据名称引用临时表。这包括由多语句查询中的过程创建的临时表。您无法共享临时表，也无法使用任何标准列表或其他表操作方法显示临时表。

删除临时表

您可以使用 DROP TABLE 语句在多语句查询完成之前显式删除临时表：

CREATE TEMP TABLE table1(x INT64);
SELECT * FROM table1;  -- Succeeds
DROP TABLE table1;
SELECT * FROM table1;  -- Results in an error

多语句查询完成后，临时表最多存在 24 小时。

查看临时表数据

创建临时表后，您可以查看该表的结构以及其中的所有数据。如需查看表的结构和数据，请按照以下步骤操作：

1. 在 Google Cloud 控制台中，转到 BigQuery 浏览器页面。

   前往“探索器”页面

2. 点击查询历史记录。

3. 选择创建了临时表的查询。

4. 在目标表行中，点击临时表。

   注意：系统不会保留多语句查询中使用的临时表的名称。在 Google Cloud 控制台中，它具有一个随机名称。

使用 _SESSION 限定临时表

当临时表与默认数据集一起使用时，非限定表名称会引用临时表（如果存在）或默认数据集中的表。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 selects from the temporary table.
SELECT * FROM t1;

-- Drop the temporary table
DROP TABLE t1;

-- Now that the temporary table is dropped, this statement selects 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

收集有关多语句查询作业的信息

多语句查询作业包含有关已执行的多语句查询的信息。您可以对作业数据执行的一些常见任务包括返回使用多语句查询执行的最后一个语句，以及返回使用多语句查询执行的所有语句。

返回最后执行的语句

jobs.getQueryResults 方法会返回要在多语句查询中执行的最后一个语句的查询结果。如果未执行任何语句，则不会返回任何结果。

返回所有已执行的语句

如需获取多语句查询中所有语句的结果，请枚举这些子作业，并对每个作业调用 jobs.getQueryResults。

枚举子作业

与其他查询类似，在 BigQuery 中，我们使用 jobs.insert 执行多语句查询，并将多语句查询指定为查询文本。多语句查询运行时，系统会为多语句查询中的每个语句创建额外作业（称为子作业）。您可以通过调用 jobs.list 并将多语句查询作业 ID 作为 parentJobId 参数传入，来枚举多语句查询的子作业。

调试多语句查询

以下是有关调试多语句查询的一些提示：

• 使用 ASSERT 语句可声明布尔条件为 true。

• 使用 BEGIN...EXCEPTION...END 可捕获错误并显示错误消息和堆栈轨迹。

• 使用 SELECT FORMAT("....") 可显示中间结果。

• 在 Google Cloud 控制台中运行多语句查询时，您可以查看多语句查询中每个语句的输出。在您运行多语句查询时，bq 命令行工具的“bq query”命令也会显示每个步骤的结果。

• 在 Google Cloud 控制台中，您可以选择查询编辑器内的单个语句并运行它。

权限

在执行语句时，BigQuery 会检查访问表、模型或其他资源的权限。如果未执行语句，或者未计算表达式，则 BigQuery 不会检查执行多语句查询的用户是否有权访问其引用的任何资源。

在多语句查询中，针对每个表达式或语句的权限会单独进行验证。例如：

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

如果执行查询的用户有权访问 table1 但无权访问 table2，则第一个查询会成功，第二个查询会失败。多语句查询作业本身也将失败。

安全限制条件

在多语句查询中，您可以使用动态 SQL 在运行时构建 SQL 语句。这样很方便，但可能会为滥用提供新的可能性。例如，执行以下查询会构成潜在的 SQL 注入安全威胁，因为表参数可能经过不适当的过滤，允许访问非预期表，以及对非预期表执行操作。

-- Risky query vulnerable to SQL injection attack.
EXECUTE IMMEDIATE CONCAT('SELECT * FROM SensitiveTable WHERE id = ', @id);

为避免公开或泄露表中的敏感数据，或避免运行 DROP TABLE 等命令而删除表中的数据，BigQuery 的动态过程语句支持以下多种安全措施，以减少遭到 SQL 注入攻击的风险：

• EXECUTE IMMEDIATE 语句不允许其查询（使用查询参数和变量进行扩展）嵌入多个 SQL 语句。
• 以下命令被限制不能动态执行：BEGIN/END、CALL、CASE、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

价格

如果您使用按需结算，BigQuery 会根据执行多语句查询期间处理的字节数收费。

如需估算多语句查询可能处理的字节数，您可以运行试运行。

注意：执行多语句查询之前，查询扫描的字节数通常未知。为避免产生意料之外的查询费用，请考虑使用基于容量的价格。或者，您也可以通过 BigQuery 沙盒免费享用有限次数的查询执行。

按需查询大小计算

如果您使用按需结算，BigQuery 会根据执行多语句查询期间处理的字节数收取多语句查询费用。

注意：执行多语句查询之前，查询扫描的字节数通常未知。为避免产生意料之外的查询费用，请考虑使用基于容量的价格。或者，您也可以通过 BigQuery 沙盒免费享用有限次数的查询执行。

以下价格适用于这些多语句查询：

• DECLARE：针对 DEFAULT 表达式中引用的所有表扫描的总字节数。未引用表的 DECLARE 语句不会产生费用。

• SET：针对该表达式中引用的所有表扫描的总字节数。未引用表的 SET 语句不会产生费用。

• IF：针对该条件表达式中引用的所有表扫描的总字节数。未引用表的 IF 条件表达式不会产生费用。IF 块中任何未执行的语句都不会产生费用。

• WHILE：针对该条件表达式中引用的所有表扫描的总字节数。该条件表达式中未引用表的 WHILE 语句不会产生费用。WHILE 块中任何未执行的语句都不会产生费用。

• CONTINUE 或 ITERATE：无相关费用。

• BREAK 或 LEAVE：无相关费用。

• BEGIN 或 END：无相关费用。

如果多语句查询失败，失败前执行的所有语句仍会产生相关费用。失败的语句不会产生任何费用。

例如，以下示例代码包含每个语句前面的注释，这些注释说明了每个语句产生的费用（如有）：

-- No cost, since no tables are referenced.
DECLARE x DATE DEFAULT CURRENT_DATE();
-- Incurs the cost of scanning string_col from dataset.table.
DECLARE y STRING DEFAULT (SELECT MAX(string_col) FROM dataset.table);
-- Incurs the cost of copying the data from dataset.big_table.  Once the
-- table is created, you are not charged for storage while the rest of the
-- multi-statement query runs.
CREATE TEMP TABLE t AS SELECT * FROM dataset.big_table;
-- Incurs the cost of scanning column1 from temporary table t.
SELECT column1 FROM t;
-- No cost, since y = 'foo' doesn't reference a table.
IF y = 'foo' THEN
  -- Incurs the cost of scanning all columns from dataset.other_table, if
  -- y was equal to 'foo', or otherwise no cost since it is not executed.
  SELECT * FROM dataset.other_table;
ELSE
  -- Incurs the cost of scanning all columns from dataset.different_table, if
  -- y was not equal to 'foo', or otherwise no cost since it is not executed.
  UPDATE dataset.different_table
  SET col = 10
  WHERE true;
END IF;
-- Incurs the cost of scanning date_col from dataset.table for each
-- iteration of the loop.
WHILE x < (SELECT MIN(date_col) FROM dataset.table) DO
  -- No cost, since the expression does not reference any tables.
  SET x = DATE_ADD(x, INTERVAL 1 DAY);
  -- No cost, since the expression does not reference any tables.
  IF true THEN
    -- LEAVE has no associated cost.
    LEAVE;
  END IF;
  -- Never executed, since the IF branch is always taken, so does not incur
  -- a cost.
  SELECT * FROM dataset.big_table;
END WHILE;

如需了解详情，请参阅查询大小计算。

配额

如需了解多语句查询配额，请参阅配额和限制。

查看多语句查询数量

您可以使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT 视图查看活跃的多语句查询的数量。以下示例使用 INFORMATION_SCHEMA.JOBS_BY_PROJECT 视图显示前一天的多语句查询数量：

SELECT
  COUNT(*)
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "QUERY"
AND state = 'RUNNING'
AND statement_type = 'SCRIPT'

如需详细了解如何查询 INFORMATION_SCHEMA.JOBS 以进行多语句查询，请参阅多语句查询作业。