Declaraciones de secuencias de comandos

Las secuencias de comandos te permiten enviar varias declaraciones a BigQuery en una sola solicitud. Las secuencias de comandos pueden usar variables y declaraciones de flujo de control, como IF y WHILE. Por ejemplo, puedes declarar una variable, asignarle un valor y, luego, hacer referencia a ella en una tercera declaración.

Para obtener más información sobre las reglas de tablas variables y temporales, la depuración y los procedimientos de escritura de secuencias de comandos, consulta Secuencias de comandos y procedimientos almacenados.

DECLARE

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name debe ser un identificador válido, y variable_type es cualquier tipo de BigQuery.

Descripción

Declara una variable del tipo especificado. Si se especifica la cláusula DEFAULT, la variable se inicializa con el valor de la expresión; si no hay una cláusula DEFAULT, la variable se inicializa con el valor NULL.

Si se omite [variable_type], se debe especificar una cláusula DEFAULT. El tipo de la variable se inferirá según el tipo de la expresión en la cláusula DEFAULT.

Las declaraciones de variables deben aparecer al comienzo de una secuencia de comandos, antes de cualquier otra declaración, o al comienzo de un bloque declarado con BEGIN. Los nombres de las variables no distinguen entre mayúsculas y minúsculas.

Pueden aparecer varios nombres de variables en una sola declaración DECLARE, pero solo una variable_type y expression.

Es un error declarar una variable con el mismo nombre que una variable declarada anteriormente en el bloque actual o en un bloque contenedor.

Si la cláusula DEFAULT está presente, el valor de la expresión debe ser coercible para el tipo especificado. La expresión puede hacer referencia a otras variables declaradas previamente dentro del mismo bloque o un bloque contenedor.

El tamaño máximo de una variable es de 1 MB, y el tamaño máximo de todas las variables utilizadas en una secuencia de comandos es de 10 MB.

Ejemplos

En el siguiente ejemplo, se inicializa la variable x como un INT64 con el valor NULL.

DECLARE x INT64;

En el siguiente ejemplo, se inicializa la variable d como DATE con el valor de la fecha actual.

DECLARE d DATE DEFAULT CURRENT_DATE();

En el siguiente ejemplo, se inicializan las variables x, y y z como INT64 con el valor 0.

DECLARE x, y, z INT64 DEFAULT 0;

En el siguiente ejemplo, se declara una variable llamada item que corresponde a un elemento arbitrario en la tabla dataset1.products. Se infiere el tipo de item del esquema de la tabla.

DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);

SET

Sintaxis

SET name = expression;

SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

Descripción

Establece una variable para tener el valor de la expresión proporcionada, o establece muchas variables al mismo tiempo en función del resultado de varias expresiones.

La declaración SET puede aparecer en cualquier parte del cuerpo de una secuencia de comandos.

Ejemplos

En el siguiente ejemplo, se establece que la variable x tenga el valor 5.

SET x = 5;

En el siguiente ejemplo, se establece que la variable a tenga el valor 4, que la variable b tenga el valor ‘foo’ y que la variable c tenga el valor false.

SET (a, b, c) = (1 + 3, 'foo', false);

En el siguiente ejemplo, se asigna el resultado de una consulta a muchas variables. Primero, declara dos variables, target_word y corpus_count; a continuación, asigna los resultados de una consulta SELECT AS STRUCT a las dos variables. El resultado de la consulta es una sola fila que contiene un STRUCT con dos campos; el primer elemento se asigna a la primera variable y el segundo elemento se asigna a la segunda variable.

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;

Con esta lista de declaraciones se genera la siguiente string:

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

Sintaxis

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

Descripción

Ejecuta una declaración de SQL dinámica sobre la marcha.

• sql_expression: Representa una declaración de consulta, una expresión que puedes usar en una declaración de consulta, una única declaración DDL o una única declaración DML. No puede ser una declaración de control como IF.
• expression: Puede ser una función, una expresión condicional o una subconsulta de expresión.
• query_statement: Representa una declaración de SQL independiente válida para ejecutar. Si esto muestra un valor, la cláusula INTO debe contener valores del mismo tipo. Puedes acceder a las variables del sistema y los valores presentes en la cláusula USING. Todas las demás variables locales y parámetros de consulta no están expuestos a la declaración de consulta.
• Cláusula INTO: Después de ejecutar la expresión de SQL, puedes almacenar los resultados en una o más variables mediante la cláusula INTO.
• Cláusula USING: Antes de ejecutar la expresión de SQL, puedes pasar uno o más identificadores de la cláusula USING a la expresión de SQL. Estos identificadores funcionan de manera similar a los parámetros de consulta y exponen valores a la declaración de consulta. Un identificador puede ser una variable o un valor.

Puedes incluir estos marcadores de posición en query_statement para los identificadores a los que se hace referencia en la cláusula USING:

• ?: El valor de este marcador de posición se vincula por índice a un identificador en la cláusula USING.

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
  

• @identifier: El valor de este marcador de posición se vincula por nombre a un identificador en la cláusula USING. Esta sintaxis es idéntica a la del parámetro de consulta.

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
  

Estas son algunas notas adicionales sobre el comportamiento de la declaración EXECUTE IMMEDIATE:

• EXECUTE IMMEDIATE no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que EXECUTE IMMEDIATE no se puede anidar en otra declaración EXECUTE IMMEDIATE.
• Si una declaración EXECUTE IMMEDIATE muestra resultados, esos resultados se convierten en el resultado de toda la declaración y se actualizan las variables de sistema adecuadas.
• La misma variable puede aparecer en las cláusulas INTO y USING.
• query_statement puede contener una sola declaración analizada que contenga otras declaraciones (por ejemplo, BEGIN…END).
• Si query_statement no muestra ninguna fila, incluso las tablas de valores sin filas, todas las variables en la cláusula INTO se establecen como NULL.
• Si query_statement muestra una fila, incluso las tablas de valores sin filas, los valores se asignan por posición, no por nombre de variable.
• Si hay una cláusula INTO, se genera un error si intentas mostrar más de una fila de query_statement.

Ejemplos

En este ejemplo, creamos una tabla de libros y la propagamos con datos. Ten en cuenta las diferentes formas en las que puedes hacer referencia a las variables, guardar valores en variables y usar expresiones.

-- 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…END

Sintaxis

BEGIN
  sql_statement_list
END;

Descripción

BEGIN inicia un bloque de declaraciones donde las variables declaradas existen solo hasta el END correspondiente. sql_statement_list es una lista de cero o más instrucciones de SQL que terminan en punto y coma.

Las declaraciones de variables deben aparecer al comienzo del bloque, antes de otros tipos de declaraciones. Las variables declaradas dentro de un bloque solo pueden referenciarse dentro de ese bloque y en cualquier bloque anidado. Es un error declarar una variable con el mismo nombre que una variable declarada en el mismo bloque o en un bloque externo.

Hay un nivel máximo de anidación de 50 para bloques y declaraciones condicionales como BEGIN/END, IF/ELSE/END IF y WHILE/END WHILE.

BEGIN/END no se puede ejecutar de forma dinámica como un elemento anidado.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

En el siguiente ejemplo, se declara una variable x con el valor predeterminado 10. Luego, se inicia un bloque, en el que a una variable y se le asigna el valor de x, que es 10, y muestra este valor. Luego, la declaración END finaliza el bloque, y finaliza el alcance de la variable y. Por último, muestra el valor de x.

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

BEGIN…EXCEPTION…END

Sintaxis

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Descripción

BEGIN...EXCEPTION ejecuta un bloque de declaraciones. Si alguna de las declaraciones experimenta un error, la secuencia de comandos omite el resto del bloque y ejecuta las declaraciones en la cláusula EXCEPTION.

En la cláusula EXCEPTION, puedes acceder a los detalles del error mediante las siguientes variables de sistema EXCEPTION:

Nombre	Tipo	Descripción
@@error.formatted_stack_trace	STRING	El contenido de @@error.stack_trace se expresa como una string legible para el ser humano. Este valor está destinado a fines de visualización y está sujeto a cambios sin previo aviso. En su lugar, el acceso programático al seguimiento de pila de un error debería usar @@error.stack_trace.
@@error.message	STRING	Especifica un mensaje de error legible para el ser humano.
@@error.stack_trace	Consulta 1.	Cada elemento del arreglo corresponde a una declaración o llamada a procedimiento que se ejecuta en el momento del error, y el primer marco de pila que está en ejecución aparece primero. El significado de cada campo se define de la siguiente manera: línea/columna: especifica la línea y el número de columna del marco de pila y comienza por 1. Si el marco se produce dentro del cuerpo de un procedimiento, line 1 column 1 corresponde a la palabra clave BEGIN al comienzo del cuerpo del procedimiento. ubicación: si el marco se produce dentro del cuerpo de un procedimiento, especifica el nombre completo del procedimiento en el formato [project_name].[dataset_name].[procedure_name]. Si el marco hace referencia a una ubicación en una secuencia de comandos de nivel superior, este campo es NULL. nombre de archivo: reservado para uso futuro. Siempre NULL.
@@error.statement_text	STRING	Especifica el texto de la declaración que causó el error.

¹ El tipo para @@error.stack_trace es ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

Como BigQuery se reserva el derecho de revisar los mensajes de error en cualquier momento, los consumidores de @@error.message no deben confiar en que los mensajes de error permanezcan iguales o sigan un patrón en particular. No obtengas información sobre la ubicación del error mediante la extracción de texto del mensaje de error; en su lugar, usa @@error.stack_trace y @@error.statement_text.

Para controlar las excepciones que arroja (sin control) un controlador de excepciones, debes unir el bloque a otro externo con un controlador independiente.

A continuación, se muestra cómo usar un bloque externo con un controlador de excepciones independiente:

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;

Los bloques BEGIN...EXCEPTION también admiten instrucciones DECLARE, como cualquier otro bloque BEGIN. Las variables declaradas en un bloque BEGIN son válidas solo en la sección BEGIN y no se pueden usar en el controlador de excepciones del bloque.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

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;

En este ejemplo, cuando ocurra el error de división por cero, en lugar de finalizar la secuencia de comandos completa, BigQuery finalizará dataset1.proc1() y dataset1.proc2(), y ejecutará la declaración SELECT en el controlador de excepciones. Cuando se ejecute el controlador de excepciones, las variables tendrán los siguientes valores:

Variable	Valor
@@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]"

CASE

Sintaxis

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Descripción

Ejecuta la primera sql_statement_list en la que la expresión booleana es verdadera o la ELSE sql_statement_list opcional si las condiciones no coinciden.

CASE puede tener un máximo de 50 niveles de anidación.

CASE no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que CASE no se puede anidar en una declaración EXECUTE IMMEDIATE.

Ejemplos

La siguiente secuencia de comandos busca target_product_ID en la tabla products_a. Si el ID no se encuentra allí, la secuencia de comandos lo busca en la tabla products_b. Si el ID no se encuentra allí, se ejecuta la declaración en el bloque ELSE.

DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_a WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_b WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_b table';
  ELSE
    SELECT 'did not find product';
END CASE;

CASE search_expression

Sintaxis

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Descripción

Ejecuta la primera sql_statement_list en la que la expresión de búsqueda coincide con una expresión WHEN. El search_expression se evalúa una vez y, luego, se prueba con cada expresión WHEN para determinar la igualdad hasta que se encuentre una coincidencia. Si no se encuentran coincidencias, se ejecuta el ELSE sql_statement_list opcional.

CASE puede tener un máximo de 50 niveles de anidación.

CASE no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que CASE no se puede anidar en una declaración EXECUTE IMMEDIATE.

Ejemplos

En el siguiente ejemplo, se usa el ID del producto como expresión de búsqueda. Si el ID es 1, se muestra 'Product one'. Si el ID es 2, se muestra 'Product two'. Si el ID es distinto, se muestra Invalid product.

DECLARE product_id INT64 DEFAULT 1;
CASE product_id
  WHEN 1 THEN
    SELECT CONCAT('Product one');
  WHEN 2 THEN
    SELECT CONCAT('Product two');
  ELSE
    SELECT CONCAT('Invalid product');
END CASE;

IF

Sintaxis

IF condition THEN [sql_statement_list]
  [ELSEIF condition THEN sql_statement_list]
  [...]
  [ELSE sql_statement_list]
END IF;

Descripción

Ejecuta la primera sql_statement_list en la que la condición es verdadera o la ELSE sql_statement_list opcional si las condiciones no coinciden.

Hay un nivel máximo de anidación de 50 para bloques y declaraciones condicionales como BEGIN/END, IF/ELSE/END IF y WHILE/END WHILE.

IF no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que IF no se puede anidar en una declaración EXECUTE IMMEDIATE.

Ejemplos

En el siguiente ejemplo, se declara una variable INT64 target_product_id con un valor predeterminado de 103. Luego, se verifica si la tabla dataset.products contiene una fila con la columna product_id que coincide con el valor de target_product_id. Si es así, se da como resultado una string que indica que se ha encontrado el producto, junto con el valor de default_product_id. En caso contrario, se da como resultado una string que indica que no se ha encontrado el producto, también con el valor de 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;

Etiquetas

Sintaxis

label_name: BEGIN
  block_statement_list
END [label_name];

label_name: LOOP
  loop_statement_list
END LOOP [label_name];

label_name: WHILE condition DO
  loop_statement_list
END WHILE [label_name];

label_name: FOR variable IN query DO
  loop_statement_list
END FOR [label_name];

label_name: REPEAT
  loop_statement_list
  UNTIL boolean_condition
END REPEAT [label_name];

block_statement_list:
  { statement | break_statement_with_label }[, ...]

loop_statement_list:
  { statement | break_continue_statement_with_label }[, ...]

break_statement_with_label:
  { BREAK | LEAVE } label_name;

break_continue_statement_with_label:
  { BREAK | LEAVE | CONTINUE | ITERATE } label_name;

Descripción

Una declaración BREAK o CONTINUE con una etiqueta proporciona un salto incondicional al final del bloque o del bucle asociado con esa etiqueta. Para usar una etiqueta con un bloque o un bucle, la etiqueta debe aparecer al comienzo del bloque o del ciclo y, de forma opcional, al final.

• El nombre de una etiqueta puede incluir cualquier identificador de BigQuery, incluido el uso de acentos graves para incluir caracteres reservados o palabras clave.

• Se pueden usar nombres de ruta de acceso multiparte, pero solo como identificadores entrecomillados.

  `foo.bar`: BEGIN ... END -- Works
  foo.bar: BEGIN ... END -- Does not work
  

• Los nombres de etiquetas no distinguen entre mayúsculas y minúsculas.

• Cada procedimiento almacenado tiene un almacenamiento independiente de nombres de etiquetas. Por ejemplo, un procedimiento puede redefinir una etiqueta que ya se utiliza en un procedimiento de llamada.

• Es posible que un bucle o un bloque no repitan el nombre de una etiqueta que se usó en un bloque o bucle delimitador.

• Se permiten nombres de etiquetas repetidos en partes no superpuestas de una secuencia de comandos.

• Se permiten una etiqueta y una variable con el mismo nombre.

• Cuando la declaración BREAK, LEAVE, CONTINUE o ITERATE especifica una etiqueta, sale o continúa el bucle que coincide con el nombre de la etiqueta, en lugar de elegir siempre el más interno.

Ejemplos

Solo puedes hacer referencia a un bloque o bucle mientras estés dentro de él.

label_1: BEGIN
  SELECT 1;
  BREAK label_1;
  SELECT 2; -- Unreached
END;

label_1: LOOP
  BREAK label_1;
END LOOP label_1;

WHILE x < 1 DO
  CONTINUE label_1; -- Error
END WHILE;

Se permiten nombres de etiquetas repetidos en partes no superpuestas de la secuencia de comandos. Esto funciona:

label_1: BEGIN
  BREAK label_1;
END;

label_2: BEGIN
  BREAK label_2;
END;

label_1: BEGIN
  BREAK label_1;
END;

Es posible que un bucle o un bloque no repita el nombre de una etiqueta que se usa en un bloque o bucle delimitador. Se mostrará el siguiente error:

label_1: BEGIN
   label_1: BEGIN -- Error
     BREAK label_1;
   END;
END;

Una etiqueta y una variable pueden tener el mismo nombre. Esto funciona:

label_1: BEGIN
   DECLARE label_1 INT64;
   BREAK label_1;
END;

La palabra clave END que termina un bloque o un bucle puede especificar un nombre de etiqueta, pero esto es opcional. Ambas opciones funcionan:

label_1: BEGIN
  BREAK label_1;
END label_1;

label_1: BEGIN
  BREAK label_1;
END;

No puedes tener una etiqueta al final de un bloque o bucle si no hay una etiqueta al comienzo del bloque o del bucle. Se mostrará el siguiente error:

BEGIN
  BREAK label_1;
END label_1;

En este ejemplo, las declaraciones BREAK y CONTINUE se orientan al label_1: LOOP externo, en lugar del bucle WHILE x < 1 DO interno:

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

Una declaración BREAK, LEAVE, CONTINUE o ITERATE que especifica una etiqueta que no existe muestra un error:

WHILE x < 1 DO
  BREAK label_1; -- Error
END WHILE;

Se permite salir de un bloque desde la sección del controlador de excepciones:

label_1: BEGIN
  SELECT 1;
  EXCEPTION WHEN ERROR THEN
    BREAK label_1;
    SELECT 2; -- Unreached
END;

No se puede usar CONTINUE con una etiqueta de bloque. Se mostrará el siguiente error:

label_1: BEGIN
  SELECT 1;
  CONTINUE label_1; -- Error
  SELECT 2;
END;

Bucles

LOOP

Sintaxis

LOOP
  sql_statement_list
END LOOP;

Descripción

Ejecuta sql_statement_list hasta que una declaración BREAK o LEAVE salga del bucle. sql_statement_list es una lista de cero o más declaraciones de SQL que terminan en punto y coma.

LOOP no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que LOOP no se puede anidar en una declaración EXECUTE IMMEDIATE.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

En el siguiente ejemplo, se declara una variable x con el valor predeterminado 0. Luego, usa la declaración LOOP para crear un bucle que se ejecutará hasta que la variable x sea mayor o igual que 10; después de que se cierra el bucle, el ejemplo genera el valor de x.

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

En este ejemplo, se muestra lo siguiente:

+----+
| x  |
+----+
| 10 |
+----+

REPEAT

Sintaxis

REPEAT
  sql_statement_list
  UNTIL boolean_condition
END REPEAT;

Descripción

Ejecuta de forma repetida una lista de cero o más instrucciones de SQL hasta que la condición booleana al final de la lista sea TRUE. La condición booleana debe ser una expresión. Puedes salir de este bucle con anticipación mediante la declaración BREAK o LEAVE.

REPEAT no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que REPEAT no se puede anidar en una declaración EXECUTE IMMEDIATE.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

En el siguiente ejemplo, se declara una variable x con el valor predeterminado 0; luego, usa la declaración REPEAT para crear un bucle que se ejecuta hasta que la variable x sea mayor o igual que 3.

DECLARE x INT64 DEFAULT 0;

REPEAT
  SET x = x + 1;
  SELECT x;
  UNTIL x >= 3
END REPEAT;

En este ejemplo, se muestra lo siguiente:

+---+
| x |
+---+
| 1 |
+---+

+---+
| x |
+---+
| 2 |
+---+

+---+
| x |
+---+
| 3 |
+---+

WHILE

Sintaxis

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Hay un nivel máximo de anidación de 50 para bloques y declaraciones condicionales como BEGIN/END, IF/ELSE/END IF y WHILE/END WHILE.

Descripción

Mientras boolean_expression sea verdadero, ejecuta sql_statement_list. boolean_expression se evalúa para cada iteración del bucle.

WHILE no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que WHILE no se puede anidar en una declaración EXECUTE IMMEDIATE.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

BREAK

Sintaxis

BREAK;

Description

Sale del bucle actual.

Usar BREAK fuera de un bucle es un error.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

En el siguiente ejemplo, se declaran dos variables, heads y heads_count. Luego, se inicia un bucle, que asigna un valor booleano aleatorio a heads. A continuación, verifica si heads es verdadero; si es así, da como resultado “Heads!” (cara) y heads_count aumenta; si no, da como resultado “Tails!” (cruz) y sale del bucle. Finalmente, da como resultado una string que indica cuántas veces el “lanzamiento de la moneda” dio como resultado “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

Sinónimo de BREAK.

CONTINUAR

Sintaxis

CONTINUE;

Description

Omite cualquier declaración siguiente en el bucle actual y regresa al inicio del bucle.

Usar CONTINUE fuera de un bucle es un error.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplos

En el siguiente ejemplo, se declaran dos variables, heads y heads_count. Luego, se inicia un bucle, que asigna un valor booleano aleatorio a heads y verifica si heads es verdadero. Si es así, el resultado es “Cara”, heads_count aumenta y se reinicia el bucle, sin tener en cuenta las declaraciones restantes. En caso contrario, el resultado es “Sello” y sale del bucle. Finalmente, da como resultado una string que indica cuántas veces el “lanzamiento de la moneda” dio como resultado “Cara”.

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

Sinónimo de CONTINUE.

FOR...IN

Sintaxis

FOR loop_variable_name IN (table_expression)
DO
  sql_expression_list
END FOR;

Descripción

Repite de forma indefinida cada fila de table_expression y la asigna a loop_variable_name. Dentro de cada bucle, las instrucciones de SQL en sql_expression_list se ejecutan con el valor actual de loop_variable_name.

El valor de table_expression se evalúa una vez al inicio del bucle. En cada iteración, el valor de loop_variable_name es una STRUCT que contiene las columnas de nivel superior de la expresión de la tabla como campos. El orden en el que se asignan los valores a loop_variable_name no se define, a menos que la expresión de la tabla tenga una cláusula de nivel superior ORDER BY o un operador de arreglo UNNEST.

El alcance de loop_variable_name es el cuerpo del bucle. El nombre de loop_variable_name no puede entrar en conflicto con otras variables dentro del mismo alcance.

Puedes usar una etiqueta con esta declaración. Para obtener más información, consulta Etiquetas.

Ejemplo

FOR record IN
  (SELECT word, word_count
   FROM bigquery-public-data.samples.shakespeare
   LIMIT 5)
DO
  SELECT record.word, record.word_count;
END FOR;

Transacciones

Vista previa

Este producto o función está cubierto por las Condiciones de las ofertas de la fase previa a la DG de las Condiciones del Servicio de Google Cloud Platform. Los productos y las características anteriores a la disponibilidad general pueden tener asistencia limitada, y los cambios en estos productos y características podrían no ser compatibles con otras versiones a la fase previa a la DG. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

COMENZAR TRANSACCIÓN

Sintaxis

BEGIN [TRANSACTION];

Description

Inicia una transacción.

La transacción finaliza cuando se alcanza una declaración COMMIT TRANSACTION o ROLLBACK TRANSACTION. Si la secuencia de comandos finaliza antes de llegar a cualquiera de estas declaraciones, se produce una reversión automática.

Para obtener más información sobre las transacciones en BigQuery, consulta Transacciones de varias declaraciones.

Ejemplo

En el siguiente ejemplo, se realiza una transacción que selecciona filas de una tabla existente en una tabla temporal, borra esas filas de la tabla original y combina la tabla temporal con otra.

BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;

CONFIRMAR TRANSACCIÓN

Sintaxis

COMMIT [TRANSACTION];

Description

Confirma una transacción abierta. Si no hay ninguna transacción abierta en curso, la declaración falla.

Para obtener más información sobre las transacciones en BigQuery, consulta Transacciones de varias declaraciones.

Ejemplo

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

REVERTIR TRANSACCIÓN

Sintaxis

ROLLBACK [TRANSACTION];

Description

Revierte una transacción abierta. Si no hay una transacción abierta en curso, la declaración falla.

Para obtener más información sobre las transacciones en BigQuery, consulta Transacciones de varias declaraciones.

Ejemplo

En el siguiente ejemplo, se revierte una transacción si se produce un error durante ella. Para ilustrar la lógica, en el ejemplo se activa un error de dividir por cero después de insertar una fila en una tabla. Una vez que se ejecutan estas declaraciones, la tabla no se ve afectada.

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

RAISE

Sintaxis

RAISE [USING MESSAGE = message];

Descripción

Genera un error y usa, de manera opcional, el mensaje de error especificado cuando se proporciona USING MESSAGE = message.

Cuando no se proporciona USING MESSAGE

Solo se debe usar la declaración RAISE dentro de una cláusula EXCEPTION. La declaración RAISE generará nuevamente la excepción que se capturó y conservará el seguimiento de pila original.

Cuando se proporciona USING MESSAGE

Si la declaración RAISE se encuentra dentro de la sección BEGIN de un bloque BEGIN...EXCEPTION, ocurrirá lo siguiente:

• Se invocará al controlador.

• El valor de @@error.message coincidirá exactamente con la string message proporcionada (que puede ser NULL si message es NULL).

• Se establecerá el seguimiento de pila en la declaración RAISE.

Si la declaración RAISE no se encuentra dentro de la sección BEGIN de un bloque BEGIN...EXCEPTION, la declaración RAISE detendrá la ejecución de la secuencia de comandos con el mensaje de error proporcionado.

RETURN

En una secuencia de comandos de BigQuery, RETURN detiene la ejecución de la secuencia de comandos actual.

CALL

Sintaxis

CALL procedure_name (procedure_argument[, …])

Descripción

Llama a un procedimiento con una lista de argumentos. procedure_argument puede ser una variable o una expresión. Para los argumentos OUT o INOUT, una variable pasada como argumento debe tener el tipo de BigQuery adecuado.

Es posible que la misma variable no aparezca varias veces como un argumento OUT o INOUT en la lista de argumentos del procedimiento.

La profundidad máxima de las llamadas a procedimiento es de 50 fotogramas.

CALL no puede ejecutarse de forma dinámica como un elemento anidado. Esto significa que CALL no se puede anidar en una declaración EXECUTE IMMEDIATE.

Ejemplos

En el siguiente ejemplo, se declara una variable retCode. Luego, llama al procedimiento updateSomeTables en el conjunto de datos myDataset, pasando los argumentos 'someAccountId' y retCode. Por último, muestra el valor de retCode.

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;

Variables de sistema

Puedes usar variables de sistema para verificar la información durante la ejecución de una secuencia de comandos.

Nombre	Tipo	Descripción
@@current_job_id	STRING	(Solo lectura) ID de trabajo del trabajo en ejecución. En el contexto de una secuencia de comandos, muestra el trabajo responsable de la declaración actual en vez de la secuencia de comandos completa.
@@dataset_id	STRING	ID del conjunto de datos predeterminado en el proyecto actual. Se usa cuando no se especifica un conjunto de datos para un proyecto en la consulta. Puedes usar la declaración SET para asignar @@dataset_id a otro ID de conjunto de datos en el proyecto actual.
@@last_job_id	STRING	(Solo lectura) ID de tarea del trabajo más reciente que se ejecutará en la secuencia de comandos actual, sin incluir el que se encuentra en la actualidad. Si la secuencia de comandos contiene declaraciones CALL, posiblemente, este trabajo se originó en un procedimiento diferente.
@@project_id	STRING	(Solo lectura) ID de solo lectura del proyecto que se usa para ejecutar la consulta actual (solo lectura). En el contexto de un procedimiento, @@project_id se refiere al proyecto que ejecuta la secuencia de comandos, no al proyecto que posee el procedimiento.
@@row_count	INT64	(Solo lectura) Si se usa en una secuencia de comandos y la declaración de la secuencia de comandos anterior es DML, especificará la cantidad de filas modificadas, insertadas o borradas como resultado de esa declaración DML. Si la declaración anterior es MERGE, @@row_count representa la cantidad total de filas insertadas, quitadas y borradas. Este valor es NULL si no está en una secuencia de comandos.
@@script.bytes_billed	INT64	(Solo lectura) Total de bytes facturados hasta el momento en el trabajo de la secuencia de comandos en ejecución actualmente. Este valor es NULL si no está en una secuencia de comandos.
@@script.bytes_processed	INT64	(Solo lectura) Total de bytes procesados hasta el momento en el trabajo de la secuencia de comandos en ejecución actualmente. Este valor es NULL si no está en una secuencia de comandos.
@@script.creation_time	TIMESTAMP	Hora de creación (solo lectura) del trabajo de la secuencia de comandos en ejecución actualmente. Este valor es NULL si no está en una secuencia de comandos.
@@script.job_id	STRING	(Solo lectura) ID de trabajo del trabajo de la secuencia de comandos en ejecución actualmente. Este valor es NULL si no está en una secuencia de comandos.
@@script.num_child_jobs	INT64	(Solo lectura) Cantidad de trabajos secundarios completados en la actualidad. Este valor es NULL si no está en una secuencia de comandos.
@@script.slot_ms	INT64	(Solo lectura) Cantidad de milisegundos de ranura que la secuencia de comandos usó hasta el momento. Este valor es NULL si no está en una secuencia de comandos.
@@time_zone	STRING	La zona horaria predeterminada que se debe usar en funciones de SQL que dependen de esta cuando no se especifica una zona horaria como argumento. @@time_zone se puede modificar mediante una declaración SET a cualquier nombre de zona horaria válido. Al inicio de cada secuencia de comandos, @@time_zone comienza como “UTC”.

Nota: Para la retrocompatibilidad, de forma predeterminada, las expresiones que se usan en una cláusula OPTIONS o FOR SYSTEM TIME AS OF se establecen en la zona horaria America/Los_Angeles, mientras que todas las demás expresiones de fecha y hora se establecen de forma predeterminada en la zona horaria UTC. Si se configuró @@time_zone en la secuencia de comandos con anterioridad, la zona horaria elegida se aplicará a todas las expresiones de fecha y hora, incluidas las cláusulas OPTIONS y FOR SYSTEM TIME AS OF.

Además de las variables de sistema que se muestran arriba, puedes usar las variables de sistema EXCEPTION durante la ejecución de una secuencia de comandos. Si no se usan en un controlador de excepciones, las variables de sistema EXCEPTION son NULL. Las siguientes son variables de sistema EXCEPTION.

• @@error.formatted_stack_trace
• @@error.message
• @@error.stack_trace
• @@error.statement_text

Para obtener más información sobre las variables de sistema EXCEPTION, consulta BEGIN…EXCEPTION.

Procedimientos del sistema

BigQuery es compatible con los siguientes procedimientos del sistema.

BQ.JOBS.CANCEL

Sintaxis

CALL BQ.JOBS.CANCEL(job);

Descripción

Cancela un trabajo en ejecución.

Especifica el trabajo como una string con el formato '[project_id.]job_id'. Si ejecutas este procedimiento del sistema desde un proyecto diferente al del trabajo, debes incluir el ID del proyecto. Debes ejecutar el procedimiento en la misma región que el trabajo.

Para obtener más información, consulta Cancela un trabajo.

BQ.REFRESH_MATERIALIZED_VIEW

Sintaxis

CALL BQ.REFRESH_MATERIALIZED_VIEW(view_name);

Descripción

Actualiza una vista materializada.

Especifica el nombre de la vista materializada como una string con el formato '[project_id.]dataset.table'. Si ejecutas este procedimiento del sistema desde un proyecto diferente al de la vista materializada, debes incluir el ID del proyecto.

Para obtener más información, consulta Actualización manual.

Permisos

El permiso para acceder a una tabla, modelo o a otro recurso se verifica en el momento de la ejecución. Si no se ejecuta una declaración o no se evalúa una expresión, BigQuery no verifica si el usuario que ejecuta la secuencia de comandos tiene acceso a los recursos a los que hace referencia.

Dentro de una secuencia de comandos, los permisos para cada expresión o declaración se validan por separado. Por ejemplo, observa la siguiente secuencia de comandos:

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

Si el usuario que ejecuta la secuencia de comandos tiene acceso a table1, pero no tiene acceso a table2, la primera consulta tendrá éxito y la segunda fallará. El trabajo de la secuencia de comandos también fallará.

Restricciones de seguridad

El SQL dinámico es conveniente, pero puede ofrecer nuevas oportunidades para el uso inadecuado. Por ejemplo, la ejecución de la siguiente consulta representa una amenaza de seguridad potencial, ya que el parámetro de la tabla podría filtrarse de forma incorrecta, permitir el acceso a tablas no deseadas y ejecutarse en ellas.

EXECUTE IMMEDIATE CONCAT(‘SELECT * FROM ’, @employee_table);

A fin de evitar la exposición o el filtrado datos sensibles en una tabla o la ejecución de comandos como DROP TABLE para borrar datos en una tabla, la secuencia de comandos de SQL dinámica de BigQuery admite varias medidas de seguridad con el propósito de reducir la exposición a los ataques de inyección de SQL, incluidos los siguientes:

• No puedes ejecutar varias declaraciones de SQL incorporadas en los parámetros que se pasaron a SQL dinámico.
• Los siguientes comandos no pueden ejecutarse de forma dinámica: BEGIN/END, CALL, CASE, IF, LOOP, WHILE y EXECUTE IMMEDIATE.

Limitaciones del campo de configuración

Los siguientes campos de configuración de consulta no se pueden configurar para la secuencia de comandos:

• clustering
• create_disposition
• destination_table
• destination_encryption_configuration
• range_partitioning
• schema_update_options
• time_partitioning
• user_defined_function_resources
• write_disposition