Secuencias de comandos en SQL estándar

Secuencias de comandos de BigQuery

Las secuencias de comandos de BigQuery te permiten enviar varias declaraciones a BigQuery en una sola solicitud, usar variables y usar 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.

En BigQuery, una secuencia de comandos es una lista de instrucciones de SQL que se ejecuta en secuencia. Una lista de instrucciones de SQL es una lista de todas las instrucciones válidas de BigQuery que están separadas por punto y coma.

Por ejemplo:

-- 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 interpreta cualquier solicitud con varias declaraciones como una secuencia de comandos, a menos que las declaraciones consistan en declaraciones CREATE TEMP FUNCTION, con una única declaración de consulta final. Por ejemplo, lo siguiente no se consideraría una secuencia de comandos:

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

SELECT Add(3, 4);

DECLARE

Sintaxis

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.

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;

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;

Esta lista de declaraciones genera la siguiente string:

Found 151 occurrences of "methinks" across 38 Shakespeare works

BEGIN

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.

Ejemplo

En el siguiente ejemplo, se declara una variable x con el valor predeterminado 10. Luego, se inicia un bloque, en el que se le asigna el valor de (10) a una variable y, y esa variable muestra este valor. Después, la declaración END termina el bloque, lo que da fin al alcance de la variable y. Finalmente, devuelve el valor de .

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

END

Termina un bloque iniciado por BEGIN.

IF

Sintaxis

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

Descripción

Si condition es verdadero, ejecuta if_statement_list. De lo contrario, ejecuta else_statement_list si se proporciona la cláusula ELSE opcional.

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.

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 . 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));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

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 instrucciones de SQL que terminan en punto y coma.

Ejemplo

En el siguiente ejemplo, se declara una variable x con el valor predeterminado 0; luego, usa la declaración LOOP para crear un ciclo que se ejecuta hasta que la variable sea mayor o igual que 10; después de que termina el ciclo, en el ejemplo, se genera el valor de .

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

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.

BREAK

Descripción

Sale del bucle actual.

Usar BREAK fuera de un bucle es un error.

Ejemplo

En el siguiente ejemplo, se declaran dos variables, heads y heads_count; luego, inicia un ciclo, que asigna un valor booleano aleatorio a , luego verifica si es verdadero; si es así, da como resultado “Heads!” y se incrementa heads_count; si no, da como resultado “Tails!” y sale del bucle; finalmente, genera una string que indica cuántas veces el “lanzamiento de la moneda” dio como resultado “heads”.

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.

CONTINUE

Descripción

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

Usar CONTINUE fuera de un bucle es un error.

Ejemplo

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

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.

RETURN

En una secuencia de comandos de BigQuery, RETURN termina 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 al procedimiento es de 50 fotogramas.

Ejemplo

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;