Trabaja con consultas de varias instrucciones

En este documento, se describe cómo usar consultas de varias instrucciones en BigQuery, por ejemplo, cómo escribir consultas de varias instrucciones, usar tablas temporales en consultas de varias instrucciones, variables de referencia en consultas de varias instrucciones y depurar consultas de varias instrucciones

Una consulta de varias instrucciones es una colección de instrucciones de SQL que puedes ejecutar en una solicitud. Con las consultas de varias instrucciones, puedes ejecutar varias instrucciones en una secuencia, con estado compartido. Las consultas de varias instrucciones pueden tener efectos secundarios, como agregar o modificar datos de una tabla.

Las consultas de varias instrucciones a menudo se usan en procedimientos almacenados y admiten instrucciones de lenguaje de procedimiento, que te permiten realizar acciones como definir variables e implementar el flujo de control.

Escribe, ejecuta y guarda consultas de varias instrucciones

Una consulta de varias instrucciones consta de una o más instrucciones de SQL separadas por punto y coma. Cualquier instrucción de SQL válida se puede usar 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 un flujo de control con tus instrucciones de SQL.

Escribe una consulta de varias instrucciones

Puedes escribir una consulta de varias instrucciones en BigQuery. La siguiente consulta de varias instrucciones declara una variable y la usa dentro de 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 todas sean instrucciones CREATE TEMP FUNCTION seguidas de una sola instrucción SELECT. Por ejemplo, la siguiente no se considera como una consulta de varias instrucciones:

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

Ejecuta una consulta de varias instrucciones

Puedes ejecutar una consulta de varias instrucciones de la misma manera que una consulta, por ejemplo, en la Consola de Google Cloud o con la herramienta de línea de comandos

Realizar una prueba de validación de una consulta de varias instrucciones

Para estimar la cantidad de bytes que lee una consulta de varias instrucciones, considera una ejecución de prueba. Una ejecución de prueba de una consulta de varias instrucciones es la más precisa para las consultas que solo contienen instrucciones SELECT.

Las ejecuciones de prueba tienen un control especial para los siguientes tipos de consultas y declaraciones:

  • Declaraciones CALL: la ejecución de prueba valida que el procedimiento específico exista y tenga una firma que coincida con los argumentos proporcionados. El contenido del procedimiento específico y todas las declaraciones posteriores a la declaración CALL no se validan.
  • Declaraciones DDL: La ejecución de prueba valida la primera declaración DDL y, luego, se detiene. Se omiten todas las declaraciones posteriores.
  • Declaraciones DML: La ejecución de prueba valida la declaración DML y, luego, valida las declaraciones posteriores. En este caso, las estimaciones de bytes se basan en tamaños de tabla originales y no tienen en cuenta el resultado de la declaración DML.
  • Declaraciones EXECUTE IMMEDIATE: la ejecución de prueba valida la expresión de la consulta, pero no evalúa la consulta dinámica en sí. Se omiten todas las declaraciones que siguen a la declaración EXECUTE IMMEDIATE.
  • Consultas que usan variables en un filtro de partición: la ejecución de prueba valida la consulta inicial y las declaraciones posteriores. Sin embargo, la ejecución de prueba no puede calcular el valor del entorno de ejecución de las variables en un filtro de partición. Esto afecta la estimación de lectura de bytes.
  • Las consultas que usan variables en la expresión de marca de tiempo de una cláusula FOR SYSTEM TIME AS OF: la ejecución de prueba usa el contenido actual de la tabla y, además, ignora la cláusula FOR SYSTEM TIME AS OF. Esto afecta la estimación de lectura de bytes si hay diferencias de tamaño entre la tabla actual y la iteración anterior de la tabla.
  • Declaraciones de control FOR, IF y WHILE: La ejecución de prueba se detiene de inmediato. Las expresiones de condición, los cuerpos de la declaración de control y todas las declaraciones posteriores no se validan.

Las ejecuciones de prueba se basan en el mejor esfuerzo, y el proceso subyacente está sujeto a cambios. Las ejecuciones de prueba están sujetas a las siguientes estipulaciones:

  • Es posible que una consulta que completa una ejecución de prueba no se ejecute de forma correcta. Por ejemplo, las consultas pueden fallar en el entorno de ejecución debido a motivos que no se detectan mediante ejecuciones de prueba.
  • Es posible que una consulta que se ejecuta de forma correcta no complete una ejecución de prueba de forma correcta. Por ejemplo, las consultas pueden fallar en ejecuciones de prueba debido a los motivos detectados en la ejecución.
  • No se garantiza que las ejecuciones de prueba que se ejecutan con éxito hoy en día siempre se ejecuten en el futuro. Por ejemplo, los cambios en la implementación de ejecución de prueba pueden detectar errores en una consulta que no se detectaron antes.

Guarda una consulta de varias instrucciones

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

Usa variables en una consulta de varias instrucciones

Una consulta de varias declaraciones 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.

  • Puedes hacer referencia a las 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 declaras. Las variables del sistema están integradas en BigQuery.

Declara una variable creada por el usuario

Debes declarar variables creadas por el usuario al comienzo de la consulta de varias declaraciones o al comienzo de un bloque BEGIN. Las variables declaradas al comienzo de la consulta de varias instrucciones están dentro del alcance de toda la consulta. Las variables declaradas dentro de un bloque BEGIN tienen alcance para el bloque. Salen del alcance 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 usadas en una consulta de varias instrucciones es de 10 MB.

Puedes declarar una variable con la instrucción de procedimiento DECLARE de la siguiente manera:

DECLARE x INT64;

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

-- Here you can reference x, but not y

Configura 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 como esta:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

Configura una variable del sistema

No creas variables del sistema, pero puedes anular el valor predeterminado para algunas de estas, como lo siguiente:

SET @@dataset_project_id = 'MyProject';

También puedes configurar y usar de forma implícita una variable del sistema en una consulta de varias declaraciones. Por ejemplo, en la siguiente consulta, debes incluir el proyecto cada vez que desees crear una tabla nueva:

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

Si no deseas agregar el proyecto a las rutas de tablas varias veces, puedes asignar el ID del proyecto del conjunto de datos MyProject a la variable de sistema @@dataset_project_id en la consulta de varias declaraciones. Esta asignación hace que MyProject sea 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;

De manera similar, puedes configurar la variable del 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 de sistema como @@dataset_id en muchas partes de una consulta de varias declaraciones. Para obtener más información, consulta Haz referencia a una variable de sistema.

Haz referencia a una variable creada por el usuario

Después de declarar y configurar una variable creada por el usuario, puedes hacer referencia a ella en una consulta de varias declaraciones. Si una variable y una columna comparten el mismo nombre, la columna tiene prioridad.

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

Haz referencia a una variable del sistema

Puedes hacer referencia a una variable del sistema en una consulta de varias declaraciones.

La siguiente consulta muestra la zona horaria predeterminada:

BEGIN
  SELECT @@time_zone AS default_time_zone;
END;

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

Puedes usar variables de sistema con consultas DDL y DML. Por ejemplo, estas son algunas formas de usar la variable del sistema @@time_zone cuando creas y actualizas una tabla:

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;

Existen algunos lugares en los que las variables de sistema no se pueden usar en las consultas DDL y DML. Por ejemplo, no puedes usar una variable del sistema como nombre de proyecto, conjunto de datos o nombre de tabla. Esto produce un error cuando intentas incluir la variable del sistema @@dataset_id en una ruta de tabla:

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

Usa tablas temporales en una consulta de varias instrucciones

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

Puedes crear y hacer referencia a una tabla temporal en una consulta de varias instrucciones. Cuando termines con la tabla temporal, puedes borrarla manualmente para minimizar los costos de almacenamiento o esperar a que BigQuery la borre después de 24 horas.

Cree una tabla temporal

Puedes crear una tabla temporal para una consulta de 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 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.

Haz referencia a una tabla temporal

Puedes hacer referencia a una tabla temporal por nombre mientras dure la consulta actual de varias instrucciones. Esto incluye las tablas temporales que crea un procedimiento dentro de la consulta de varias instrucciones. No puedes compartir tablas temporales. Las tablas temporales residen en conjuntos de datos _script% ocultos con nombres generados de manera aleatoria. En el artículo Haz una lista de los conjuntos de datos, se describe cómo enumerar los conjuntos de datos ocultos.

Borra tablas temporales

Puedes borrar una tabla temporal de forma explícita 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 de varias instrucciones, la tabla temporal existe por hasta 24 horas.

Visualiza datos de la tabla temporal

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

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

    Ir al Explorador

  2. Haz clic en Historial de consultas.

  3. Elige la consulta que creó la tabla temporal.

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

Califica tablas temporales con _SESSION

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 instrucciones 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, considera la siguiente consulta de 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 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 consulta de varias instrucciones muestra un error que indica que la tabla no existe. Por ejemplo, si no hay una tabla temporal llamada t3, la consulta de varias instrucciones muestra 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

Recopila información sobre un trabajo de consulta de varias instrucciones

Un trabajo de consulta de varias instrucciones contiene información sobre una consulta de varias instrucciones ejecutada. Algunas tareas comunes que puedes realizar con los datos de trabajos incluyen mostrar la última instrucción ejecutada con la consulta de varias instrucciones o mostrar todas las instrucciones ejecutadas con la consulta de varias instrucciones.

Muestra la última instrucción ejecutada

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

Muestra todas las instrucciones ejecutadas

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

Enumera los trabajos secundarios

Las consultas de varias instrucciones se ejecutan en BigQuery mediante jobs.insert, de modo similar a cualquier otra consulta, y se especifican como el texto de la consulta. Cuando se ejecuta una consulta de varias instrucciones, se crean trabajos adicionales, conocidos como trabajos secundarios, para cada instrucción de la consulta de varias instrucciones. Para enumerar los trabajos secundarios de una consulta de varias instrucciones, llama a jobs.list y pasa el ID del trabajo de consulta de varias instrucciones como el parámetro parentJobId.

Depura una consulta de varias instrucciones

Estas son algunas sugerencias para depurar consultas de varias instrucciones:

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

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

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

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

  • En la consola de Google Cloud, puedes seleccionar una instrucción individual dentro del editor de consultas y ejecutarla.

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 instrucción o no se evalúa una expresión, BigQuery no verifica si el usuario que ejecuta la consulta de varias instrucciones tiene acceso a los recursos a los que hace referencia.

En una consulta de varias instrucciones, los permisos para 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 tiene acceso a table2, la primera consulta tendrá éxito y la segunda fallará. El trabajo de consulta de varias instrucciones también falla.

Restricciones de seguridad

En las consultas de varias instrucciones, puedes usar SQL dinámico para compilar instrucciones de SQL en el entorno de ejecución. Esto es conveniente, pero puede ofrecer nuevas oportunidades para el uso inadecuado. Por ejemplo, la ejecución de la siguiente consulta representa una posible amenaza de seguridad de inyección de SQL, 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.

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

A fin de evitar la exposición o la filtración de datos sensibles en una tabla o la ejecución de comandos como DROP TABLE para borrar datos en una tabla, las instrucciones de procedimiento dinámicas de BigQuery admiten varias medidas de seguridad con el propósito de reducir la exposición a los ataques de inyección de SQL, incluido lo siguiente:

  • Una instrucción EXECUTE IMMEDIATE no permite que su consulta, expandida con parámetros y variables de consulta, incorpore varias instrucciones de SQL.
  • 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 consulta de configuración de trabajo no se pueden configurar para 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

El precio de las consultas de varias declaraciones incluye cargos por consultas (cuando se usa el modelo de facturación a pedido) y el almacenamiento de las tablas temporales. Cuando usas reservas, los cargos de reservas se cubren.

Cálculo del tamaño de la consulta según demanda

Si usas la facturación según demanda, BigQuery cobra por las consultas de varias declaraciones según la cantidad de bytes procesados durante la ejecución estas consultas.

Para obtener una estimación de cuántos bytes puede procesar una consulta de varias instrucciones, puedes ejecutar una ejecución de prueba.

Se aplican los siguientes precios para estas consultas de varias declaraciones:

  • DECLARE: La suma de bytes que se analizaron en cualquier tabla a la que se hizo referencia en la expresión DEFAULT. Las declaraciones DECLARE sin referencias de tablas no generan ningún costo

  • SET: Suma de bytes que se analizaron en cualquier tabla a la que se hizo referencia en la expresión. Las declaraciones SET sin referencias de tablas no generan ningún costo

  • IF: Suma de bytes que se analizaron en cualquier tabla a la que se hizo referencia en la expresión de condición. Las expresiones de condición IF sin referencia de tabla no generan ningún costo. Cualquier declaración en el bloque IF que no se ejecute no generará ningún costo

  • WHILE: Suma de bytes que se analizaron en cualquier tabla a la que se hizo referencia en la expresión de condición. Las declaraciones WHILE sin referencias de tablas en la expresión de condición no generan ningún costo. Cualquier declaración en el bloque WHILE que no se ejecute no generará ningún costo

  • CONTINUE o ITERATE: Sin costos asociados

  • BREAK o LEAVE: Sin costos asociados

  • BEGIN o END: Sin costos asociados

Si una consulta de varias declaraciones falla, se aplica el costo de cualquier declaración hasta el momento del error. La declaración que falló no genera ningún costo.

Por ejemplo, el siguiente código de muestra contiene comentarios arriba de cada declaración con los que se explica el costo (si lo hay) que genera cada declaración:

-- 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 Cálculo del tamaño de la consulta.

Precios de almacenamiento

Se te cobra por las tablas temporales que crean las consultas de varias declaraciones. Puedes usar las vistas TABLE_STORAGE o TABLE_STORAGE_USAGE_TIMELINE para ver el almacenamiento que usan estas tablas temporales. Las tablas temporales residen en conjuntos de datos _script% ocultos con nombres generados de manera aleatoria.

Cuotas

Para obtener información sobre las cuotas de consulta de varias declaraciones, ve Cuotas y límites.

Visualiza la cantidad de consultas de varias instrucciones

Puedes ver la cantidad de consultas de varias declaraciones activas con la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT. En el siguiente ejemplo, se usa la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT para mostrar la cantidad de consultas de varias declaraciones 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'

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