Secuencias de comandos y procedimientos almacenados

Una secuencia de comandos es una colección de instrucciones de SQL que puedes ejecutar en una solicitud. Las secuencias de comandos pueden usar variables y declaraciones de flujo de control, y pueden tener efectos secundarios. Puedes usar secuencias de comandos para hacer lo siguiente:

  • Ejecutar varias consultas en una secuencia, con estado compartido
  • Automatizar las tareas de administración, como crear o descartar tablas
  • Implementa una lógica más compleja con construcciones de programación como IF y WHILE.

Un procedimiento es una secuencia de comandos persistente que puedes invocar desde el interior de una instrucción de SQL. Un procedimiento puede tomar argumentos de entrada y mostrar valores como resultados.

En el siguiente ejemplo, se muestra una secuencia de comandos que establece una variable, ejecuta una declaración INSERT y muestra el resultado como una string de texto con formato. Puedes ejecutar esta secuencia de comandos de la misma manera que una consulta, por ejemplo, en Google Cloud Console o con la herramienta de línea de comandos de bq. También puedes guardar la secuencia de comandos como una consulta.

DECLARE id STRING;
SET id = GENERATE_UUID();

INSERT INTO mydataset.customers (customer_id)
   VALUES(id);

SELECT FORMAT("Created customer ID %s", id);

A continuación, se muestra la misma secuencia de comandos convertida en un procedimiento:

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

En el ejemplo anterior, el nombre del procedimiento es mydataset.create_customer y el cuerpo del procedimiento aparece entre las declaraciones BEGIN y END.

Para llamar al procedimiento, usa la declaración CALL:

CALL mydataset.create_customer();

Escribe una secuencia de comandos

Una secuencia de comandos consiste en una o más instrucciones de SQL separadas por punto y coma. Cualquier instrucción de SQL válida se puede usar en una secuencia de comandos. Las secuencias de comandos también pueden incluir secuencias de comandos, que te permiten usar variables o implementar el flujo de control con las instrucciones de SQL. En el siguiente ejemplo, se declara una variable y se usa la variable dentro de una declaración 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

Las secuencias de comandos se ejecutan en BigQuery mediante jobs.insert, de modo similar a cualquier otra búsqueda, con la secuencia de comandos de varias declaraciones especificada como el texto de la búsqueda. Cuando se ejecuta una secuencia de comandos, se crean trabajos adicionales, conocidos como trabajos secundarios, para cada declaración de la secuencia de comandos. Para enumerar los trabajos secundarios de una secuencia de comandos, llama a jobs.list y pasa el ID de tarea de la secuencia de comandos como el parámetro parentJobId.

El método jobs.getQueryResults muestra los resultados de la consulta de la última declaración DDL, SELECT o DML que se ejecutará en la secuencia de comandos, sin resultados de consultas si no se ejecutó ninguna de las declaraciones anteriores, no habrá resultados de consulta. Para obtener los resultados de todas las declaraciones en la secuencia de comandos, enumera los trabajos secundarios y llama a jobs.getQueryResults en cada uno de ellos.

BigQuery interpreta cualquier solicitud con varias declaraciones como una secuencia de comandos, a menos que las declaraciones consistan en su totalidad en declaraciones CREATE TEMP FUNCTION seguidas de una sola declaración de consulta. Por ejemplo, lo siguiente no se considera una secuencia de comandos:

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

Variables

Las variables deben declararse al comienzo de la secuencia de comandos o al comienzo de un bloque BEGIN. Las variables declaradas al comienzo de la secuencia de comandos están dentro del alcance de toda la secuencia de comandos. Las variables declaradas dentro de un bloque BEGIN tienen alcance para el bloque. Están fuera del alcance después de la declaración END correspondiente. 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.

DECLARE x INT64;

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

-- Here you can reference x, but not y

Si una variable y una columna comparten el mismo nombre, la columna tiene prioridad.

El resultado es el siguiente: 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    |
+--------+

El resultado es 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     |
+--------+

Tablas temporales

Las tablas temporales te permiten guardar los resultados intermedios en una tabla. Estas tablas temporales existen a nivel de la sesión, por lo que no necesitas guardarlas ni mantenerlas en un conjunto de datos. Se borran de forma automática después de que se completa la secuencia de comandos.

En el siguiente ejemplo, se crea una tabla temporal para almacenar los resultados de una consulta y se usa la tabla temporal en una subconsulta:

-- 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
);

Además del uso de TEMP o TEMPORARY, la sintaxis es idéntica a la sintaxis CREATE TABLE.

Cuando crees una tabla temporal, no uses un calificador de proyecto o conjunto de datos en el nombre de la tabla. La tabla se crea de forma automática en un conjunto de datos especial.

Puedes hacer referencia a una tabla temporal por nombre mientras dure la secuencia de comandos actual. También se incluyen las tablas creadas por un procedimiento dentro de la secuencia de comandos. No puedes compartir tablas temporales y no son visibles con ninguno de los métodos de lista estándar o de manipulación de tablas.

Cuando finaliza una secuencia de comandos, la tabla temporal existe por hasta 24 horas. No se guarda con el nombre que le asignaste, sino que se le asigna uno aleatorio. Para ver los datos y la estructura de la tabla, ve a Consola de BigQuery, haz clic en Historial de consultas y elige la consulta que creó la tabla temporal. Luego, en la fila Tabla de destino, haz clic en Tabla temporal. No se te cobrará por almacenar tablas temporales.

Puedes borrar una tabla temporal de forma explícita antes de que se complete la secuencia de comandos mediante la declaración DROP TABLE:

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

Cuando se usan tablas temporales con un conjunto de datos predeterminado, los nombres de tablas no calificados hacen referencia a una tabla temporal si existe una o a una tabla en el conjunto de datos predeterminado. La excepción es para las declaraciones CREATE TABLE, en las que la tabla de destino se considera una tabla temporal solo si está presente la palabra clave TEMP o TEMPORARY.

Por ejemplo, observa la siguiente secuencia de comandos:

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

Puedes indicar explícitamente que se refiere a una tabla temporal si calificas el nombre de la tabla con _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;

Si usas el calificador _SESSION para una consulta de una tabla temporal que no existe, la secuencia de comandos muestra un error que indica que la tabla no existe. Por ejemplo, si no hay una tabla temporal llamada t3, la secuencia de comandos mostrará un error incluso si existe una tabla llamada t3 en el conjunto de datos predeterminado.

No puedes usar _SESSION para crear una tabla no temporal:

CREATE TABLE _SESSION.t4 (x INT64);  -- Fails

Depura una secuencia de comandos

Estas son algunas sugerencias para depurar secuencias de comandos y procedimientos almacenados:

  • Usa la declaración ASSERT para confirmar que una condición booleana es verdadera.

  • Usa BEGIN...EXCEPTION para detectar errores y mostrar el mensaje de error y el seguimiento de pila.

  • Usa SELECT FORMAT("....") para mostrar resultados intermedios.

  • Cuando ejecutas una secuencia de comandos en Google Cloud Console, puedes ver el resultado de cada declaración en la secuencia de comandos. El comando “bq query” de la herramienta de línea de comandos de bq también muestra los resultados de cada paso cuando ejecutas una secuencia de comandos.

  • En Google Cloud Console, puedes seleccionar una declaración individual dentro del editor de consultas y ejecutarla.

Escribe un procedimiento

Para crear un conjunto de datos, usa la declaración CREATE PROCEDURE. El cuerpo del procedimiento aparece entre las declaraciones BEGIN y END:

CREATE PROCEDURE dataset_name.procedure_name
BEGIN
-- statements here
END

Parámetros

Un procedimiento puede tomar una lista de los parámetros con nombre. Cada parámetro tiene un tipo de datos.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Un procedimiento puede tener parámetros de salida. Un parámetro de salida muestra un valor del procedimiento, pero no permite la entrada del procedimiento. Para crear un parámetro de salida, usa la palabra clave OUT antes del nombre del parámetro.

Por ejemplo, esta versión del procedimiento muestra el ID de cliente nuevo a través del parámetro id:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Si deseas llamar a este procedimiento, debes usar una variable para recibir el valor de salida:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM temp.customers
WHERE customer_id = id;

Un procedimiento también puede tener parámetros de entrada/salida. Un parámetro de entrada/salida muestra un valor del procedimiento y también acepta la entrada para el procedimiento. Para crear un parámetro de entrada o salida, usa la palabra clave INOUT antes del nombre del parámetro. Para obtener más información, consulta Modo de argumentos.

Precios

Si usas la facturación a pedido, BigQuery cobra por las secuencias de comandos según la cantidad de bytes procesados durante su ejecución.

Para obtener más información, consulta Cálculo del tamaño de la consulta.