Trabajar con consultas de varias instrucciones

Una consulta de varias instrucciones es un conjunto de instrucciones SQL que puedes ejecutar en una secuencia, con un estado compartido.

En este documento se describe cómo usar consultas de varias instrucciones en BigQuery, como escribir consultas de varias instrucciones, usar tablas temporales en consultas de varias instrucciones, hacer referencia a variables en consultas de varias instrucciones y depurar consultas de varias instrucciones.

Las consultas de varias instrucciones se suelen usar en procedimientos almacenados y admiten instrucciones de lenguaje de procedimiento, que te permiten hacer cosas como definir variables e implementar el flujo de control. Las consultas con varias instrucciones pueden contener instrucciones de DDL y DML que tengan efectos secundarios, como crear o modificar tablas o datos de tablas.

Escribir, ejecutar y guardar consultas de varias instrucciones

Una consulta de varias instrucciones consta de una o varias instrucciones SQL separadas por puntos y comas. Se puede usar cualquier instrucción SQL válida en una consulta de varias instrucciones. Las consultas de varias instrucciones también pueden incluir instrucciones de lenguaje de procedimiento, que te permiten usar variables o implementar el flujo de control con tus instrucciones SQL.

Escribir una consulta con varias instrucciones

Puedes escribir una consulta de varias instrucciones en BigQuery. La siguiente consulta de varias instrucciones declara una variable y la usa en una instrucció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

BigQuery interpreta cualquier solicitud con varias instrucciones como una consulta de varias instrucciones, a menos que las instrucciones consistan únicamente en instrucciones CREATE TEMP FUNCTION seguidas de una sola instrucción SELECT. Por ejemplo, la siguiente no se considera una consulta de varias instrucciones:

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

Ejecutar una consulta con varias instrucciones

Puedes ejecutar una consulta de varias instrucciones de la misma forma que cualquier otra consulta, por ejemplo, en la Google Cloud consola o con la herramienta de línea de comandos bq.

Ejecutar una consulta con varias instrucciones de prueba

Para estimar el número de bytes que lee una consulta con varias instrucciones, puedes hacer una prueba. La ejecución de prueba de una consulta de varias instrucciones es más precisa en las consultas que solo contienen instrucciones SELECT.

Las pruebas de funcionamiento tienen un tratamiento especial para los siguientes tipos de consultas y de instrucciones:

• Sentencias CALL: la prueba de funcionamiento valida que el procedimiento llamado existe y tiene una firma que coincide con los argumentos proporcionados. El contenido del procedimiento llamado y todas las instrucciones posteriores a la instrucción CALL no se validan.
• Declaraciones DDL: la prueba de funcionamiento valida la primera declaración DDL y, a continuación, se detiene. Se omiten todas las instrucciones posteriores. No se admiten pruebas de funcionamiento de las instrucciones CREATE TEMP TABLE.
• Instrucciones DML: la prueba de funcionamiento valida la instrucción DML y, a continuación, sigue validando las instrucciones posteriores. En este caso, las estimaciones de bytes se basan en los tamaños de las tablas originales y no tienen en cuenta el resultado de la instrucción DML.
• EXECUTE IMMEDIATE statements: la prueba valida la expresión de la consulta, pero no evalúa la consulta dinámica en sí. Se omiten todas las instrucciones que siguen a la instrucción EXECUTE IMMEDIATE.
• Consultas que usan variables en un filtro de partición: la prueba en seco valida la consulta inicial y las instrucciones posteriores. Sin embargo, la prueba no puede calcular el valor de tiempo de ejecución de las variables en un filtro de partición. Esto afecta a la estimación de bytes leídos.
• Consultas que usan variables en la expresión de marca de tiempo de una cláusula FOR SYSTEM TIME AS OF: la prueba usa el contenido actual de la tabla e ignora la cláusula FOR SYSTEM TIME AS OF. Esto afecta a la estimación de los bytes leídos si hay diferencias de tamaño entre la tabla actual y la iteración anterior de la tabla.
• Sentencias de control FOR, IF y WHILE: la prueba se detiene inmediatamente. Las expresiones de condición, los cuerpos de la instrucción de control y todas las instrucciones posteriores no se validan.

Las pruebas se realizan en la medida de lo posible y el proceso subyacente está sujeto a cambios. Las pruebas de funcionamiento están sujetas a las siguientes estipulaciones:

• Es posible que una consulta que complete correctamente un simulacro no se ejecute correctamente. Por ejemplo, es posible que las consultas fallen en tiempo de ejecución por motivos que no se detectan en las pruebas de funcionamiento.
• Una consulta que se ejecuta correctamente puede no completar un simulacro correctamente. Por ejemplo, es posible que las consultas no superen las pruebas de funcionamiento por motivos detectados durante la ejecución.
• No se garantiza que las pruebas sin conexión que se ejecuten correctamente hoy se ejecuten siempre en el futuro. Por ejemplo, los cambios en la implementación de la prueba sin errores pueden detectar errores en una consulta que antes no se detectaban.

Guardar una consulta de varias instrucciones

Para guardar una consulta de varias instrucciones, consulta Trabajar con consultas guardadas.

Usar variables en una consulta de varias instrucciones

Una consulta con varias instrucciones puede contener variables creadas por el usuario y variables del sistema.

• Puedes declarar variables creadas por el usuario, asignarles valores y hacer referencia a ellas en toda la consulta.

• Puede hacer referencia a variables del sistema en una consulta y asignar valores a algunas de ellas, pero, a diferencia de las variables definidas por el usuario, no las declara. Las variables de sistema están integradas en BigQuery.

Declarar una variable creada por un usuario

Debes declarar las variables creadas por el usuario al principio de la consulta de varias instrucciones o al principio de un bloque BEGIN. Las variables declaradas al principio de la consulta de varias instrucciones están en el ámbito de toda la consulta. Las variables declaradas dentro de un bloque BEGIN tienen ámbito en el bloque. Dejan de estar en el ámbito después de la instrucció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 consulta de varias instrucciones es de 10 MB.

Puedes declarar una variable con la instrucción de procedimiento DECLARE de esta forma:

DECLARE x INT64;

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

-- Here you can reference x, but not y

Definir una variable creada por el usuario

Después de declarar una variable creada por el usuario, puedes asignarle un valor con la instrucción de procedimiento SET de esta forma:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

Definir una variable del sistema

No puedes crear variables de sistema, pero sí puedes anular el valor predeterminado de algunas de ellas de la siguiente manera:

SET @@dataset_project_id = 'MyProject';

También puede definir y usar implícitamente una variable de sistema en una consulta de varias instrucciones. Por ejemplo, en la siguiente consulta, debe incluir el proyecto cada vez que quiera crear una tabla:

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

Si no quieres añadir el proyecto a las rutas de tabla varias veces, puedes asignar el ID de proyecto del conjunto de datos MyProject a la variable de sistema @@dataset_project_id en la consulta de varias instrucciones. Con esta asignación, MyProject se convierte en el proyecto predeterminado para el resto de la consulta.

SET @@dataset_project_id = 'MyProject';

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

Del mismo modo, puedes definir la variable de sistema @@dataset_id para asignar un conjunto de datos predeterminado a la consulta. Por ejemplo:

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

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

También puedes hacer referencia explícita a variables del sistema, como @@dataset_id, en muchas partes de una consulta de varias instrucciones. Para obtener más información, consulta los ejemplos de variables del sistema.

Hacer referencia a una variable creada por un usuario

Una vez que hayas declarado y definido una variable creada por el usuario, podrás hacer referencia a ella en una consulta de varias instrucciones. Si una variable y una columna tienen el mismo nombre, la columna tiene prioridad.

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

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

Usar tablas temporales en una consulta de varias instrucciones

Las tablas temporales te permiten guardar resultados intermedios en una tabla. BigQuery gestiona las tablas temporales, por lo que no es necesario guardarlas ni mantenerlas en un conjunto de datos. Se te cobrará por el almacenamiento de tablas temporales.

Puedes crear una tabla temporal y hacer referencia a ella en una consulta de varias instrucciones. Cuando hayas terminado de usar la tabla temporal, puedes eliminarla manualmente para minimizar los costes de almacenamiento o esperar a que BigQuery la elimine al cabo de 24 horas.

Crear una tabla temporal

Puedes crear una tabla temporal para una consulta con varias instrucciones con la instrucción CREATE TABLE. En el siguiente ejemplo se crea una tabla temporal para almacenar los resultados de una consulta y se usa esa tabla 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
);

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

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

Hacer referencia a una tabla temporal

Puedes hacer referencia a una tabla temporal por su nombre durante la consulta actual de varias instrucciones. Esto incluye las tablas temporales creadas por un procedimiento en la consulta de varias instrucciones. No puedes compartir tablas temporales. Las tablas temporales se encuentran en conjuntos de datos _script% ocultos con nombres generados aleatoriamente. En el artículo Ver una lista de conjuntos de datos se describe cómo ver una lista de los conjuntos de datos ocultos.

Eliminar tablas temporales

Puedes eliminar una tabla temporal explícitamente antes de que se complete la consulta de varias instrucciones mediante la instrucción DROP TABLE:

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

Una vez que finaliza una consulta con varias instrucciones, la tabla temporal existe durante un máximo de 24 horas.

Ver datos de tablas temporales

Después de crear una tabla temporal, puede ver su estructura y los datos que contiene. Para ver la estructura y los datos de la tabla, sigue estos pasos:

1. En la Google Cloud consola, ve a la página Explorador de BigQuery.

   Ir a Explorador

2. Haz clic en Historial de consultas.

3. Elige la consulta que ha creado la tabla temporal.

4. En la fila Tabla de destino, haga clic en Tabla temporal.

Calificar tablas temporales con _SESSION

Cuando se usan tablas temporales junto con un conjunto de datos predeterminado, los nombres de tabla no calificados hacen referencia a una tabla temporal (si existe) o a una tabla del conjunto de datos predeterminado. La excepción son las instrucciones CREATE TABLE, en las que la tabla de destino se considera una tabla temporal si y solo si se incluye la palabra clave TEMP o TEMPORARY.

Por ejemplo, considera la siguiente consulta con varias instrucciones:

-- 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 te refieres a una tabla temporal calificando 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 en una consulta de una tabla temporal que no existe, la consulta de varias instrucciones genera un error que indica que la tabla no existe. Por ejemplo, si no hay ninguna tabla temporal llamada t3, la consulta de varias instrucciones genera un error aunque haya 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

Recoger información sobre una tarea de consulta con varias instrucciones

Un trabajo de consulta con varias instrucciones contiene información sobre una consulta con varias instrucciones que se ha ejecutado. Entre las tareas habituales que puedes realizar con los datos de los trabajos se incluyen devolver la última instrucción ejecutada con la consulta de varias instrucciones o devolver todas las instrucciones ejecutadas con la consulta de varias instrucciones.

Devuelve la última instrucción ejecutada.

El método jobs.getQueryResults devuelve los resultados de la consulta de la última instrucción que se ha ejecutado en la consulta de varias instrucciones. Si no se ha ejecutado ninguna instrucción, no se devuelve ningún resultado.

Devuelve todas las instrucciones ejecutadas.

Para obtener los resultados de todas las instrucciones de una consulta con varias instrucciones, enumera los trabajos secundarios y llama a jobs.getQueryResults en cada uno de ellos.

Enumera las tareas secundarias

Las consultas de varias instrucciones se ejecutan en BigQuery mediante jobs.insert, como cualquier otra consulta, con las consultas de varias instrucciones especificadas como texto de consulta. Cuando se ejecuta una consulta con varias instrucciones, se crean trabajos adicionales, denominados trabajos secundarios, para cada instrucción de la consulta. Puedes enumerar las tareas secundarias de una consulta con varias instrucciones llamando a jobs.list y pasando el ID de la tarea de la consulta con varias instrucciones como parámetro parentJobId.

Depurar una consulta de varias instrucciones

A continuación, te ofrecemos algunos consejos para depurar consultas con varias instrucciones:

• Usa la instrucción ASSERT para afirmar que una condición booleana es verdadera.

• Usa BEGIN...EXCEPTION...END para detectar errores y mostrar el mensaje de error y el rastreo de la pila.

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

• Cuando ejecutas una consulta con varias instrucciones en la consola Google Cloud , puedes ver el resultado de cada instrucción de la consulta. El comando bq query de la herramienta de línea de comandos bq también muestra los resultados de cada paso al ejecutar una consulta de varias instrucciones.

• En la consola Google Cloud , puedes seleccionar una instrucción concreta en el editor de consultas y ejecutarla.

Permisos

El permiso para acceder a una tabla, un modelo u otro recurso se comprueba en el momento de la ejecución. Si no se ejecuta una instrucción o no se evalúa una expresión, BigQuery no comprueba si el usuario que ejecuta la consulta de varias instrucciones tiene acceso a los recursos a los que hace referencia.

En una consulta con varias instrucciones, los permisos de cada expresión o instrucción se validan por separado. Por ejemplo:

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

Si el usuario que ejecuta la consulta tiene acceso a table1, pero no a table2, la primera consulta se realizará correctamente y la segunda no. La propia tarea de consulta con varias instrucciones también falla.

Restricciones de seguridad

En las consultas de varias instrucciones, puedes usar SQL dinámico para crear instrucciones SQL en tiempo de ejecución. Es una opción cómoda, pero puede ofrecer nuevas oportunidades de uso inadecuado. Por ejemplo, ejecutar la siguiente consulta supone una posible amenaza de seguridad de inyección de SQL, ya que el parámetro de tabla podría filtrarse de forma incorrecta, lo que permitiría acceder a tablas no deseadas y ejecutarse en ellas.

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

Para evitar que se expongan o se filtren datos sensibles en una tabla o que se ejecuten comandos como DROP TABLE para eliminar datos de una tabla, las instrucciones de procedimiento dinámicas de BigQuery admiten varias medidas de seguridad para reducir la exposición a ataques de inyección de SQL, entre las que se incluyen las siguientes:

• Una instrucción EXECUTE IMMEDIATE no permite que su consulta, ampliada con parámetros de consulta y variables, inserte varias instrucciones SQL.
• Los siguientes comandos no se pueden ejecutar de forma dinámica: BEGIN/END, CALL, CASE, IF, LOOP, WHILE y EXECUTE IMMEDIATE.

Limitaciones de los campos de configuración

Los siguientes campos de consulta de configuración de trabajos no se pueden definir en una consulta de varias instrucciones:

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

Precios

Los precios de las consultas de varias instrucciones incluyen los cargos de las consultas (cuando se usa el modelo de facturación bajo demanda) y el almacenamiento de las tablas temporales. Si usas reservas, el uso de las consultas se cubre con los cargos de tu reserva.

Cálculo del tamaño de las consultas bajo demanda

Si usas la facturación bajo demanda, BigQuery cobra por las consultas de varias instrucciones según el número de bytes procesados durante la ejecución de las consultas de varias instrucciones.

Para obtener una estimación del número de bytes que puede procesar una consulta de varias instrucciones, puedes ejecutar una prueba.

Se aplican los siguientes precios a estas consultas con varias instrucciones:

• DECLARE: la suma de bytes analizados de todas las tablas a las que se hace referencia en la expresión DEFAULT. No te cobraremos las declaraciones DECLARE que no hagan referencia a ninguna tabla.

• SET: la suma de bytes analizados de todas las tablas a las que se hace referencia en la expresión. No te cobraremos las declaraciones SET que no hagan referencia a ninguna tabla.

• IF: la suma de bytes analizados de todas las tablas a las que se hace referencia en la expresión condicional. No te cobraremos las expresiones condicionales IF que no hagan referencia a ninguna tabla. No te cobraremos las declaraciones del bloque IF que no se ejecuten.

• WHILE: la suma de bytes analizados de todas las tablas a las que se hace referencia en la expresión condicional. No te cobraremos las declaraciones WHILE sin referencia a tablas dentro de la expresión condicional. No te cobraremos las declaraciones del bloque WHILE que no se ejecuten.

• CONTINUE o ITERATE: no tiene ningún coste asociado.

• BREAK o LEAVE: no tiene ningún coste asociado.

• BEGIN o END: no tiene ningún coste asociado.

Si una consulta con varias instrucciones falla, se aplicará el coste de las instrucciones que se hayan ejecutado antes del error. No te cobraremos la declaración que produzca el error.

Por ejemplo, el siguiente código de muestra contiene comentarios antes de cada instrucción que explican qué coste se genera (si es que se genera alguno):

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

Para obtener más información, consulta la sección sobre cómo se calcula el tamaño de las consultas.

Precio de almacenamiento

Se te cobrará por las tablas temporales creadas por consultas de varias instrucciones. Puedes usar las vistas TABLE_STORAGE o TABLE_STORAGE_USAGE_TIMELINE para ver el almacenamiento que usan estas tablas temporales. Las tablas temporales se encuentran en conjuntos de datos _script% ocultos con nombres generados aleatoriamente.

Cuotas

Para obtener información sobre las cuotas de consultas con varias instrucciones, consulta Cuotas y límites.

Ver el número de consultas de varias instrucciones

Puede ver el número de consultas de varias instrucciones activas mediante la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT. En el siguiente ejemplo se usa la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT para mostrar el número de consultas con varias instrucciones del día anterior:

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'

Para obtener más información sobre cómo consultar INFORMATION_SCHEMA.JOBS para consultas de varias instrucciones, consulta Trabajo de consulta de varias instrucciones.