Trabalhar com consultas de várias instruções

Neste documento, descrevemos como usar consultas de várias instruções no BigQuery. Por exemplo, como criar consultas de várias instruções, usar tabelas temporárias em consultas de várias instruções, variáveis de referência em consultas de várias instruções e depurar consultas de várias instruções.

Uma consulta de várias instruções é um conjunto de instruções SQL que podem ser executadas em uma solicitação. Com as consultas de várias instruções, é possível executar várias instruções em uma sequência, com estado compartilhado. Consultas de várias instruções podem ter efeitos colaterais, como adicionar ou modificar dados da tabela.

As consultas de várias instruções geralmente são usadas em procedimentos armazenados e são compatíveis com instruções de linguagem processual, que permitem fazer coisas como definir variáveis e implementar o fluxo de controle.

Gravar, executar e salvar consultas de várias instruções

Uma consulta de várias instruções consiste em uma ou mais instruções SQL separadas por ponto e vírgula. Qualquer instrução SQL válida pode ser usada em uma consulta de várias instruções. Consultas de várias instruções também podem incluir instruções de linguagem processual, que permitem usar variáveis ou implementar o fluxo de controle com suas instruções SQL.

Criar uma consulta de várias instruções

É possível escrever uma consulta de várias instruções no BigQuery. A consulta de instrução de várias consultas a seguir declara uma variável e usa a variável dentro de uma instrução 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

O BigQuery interpreta qualquer solicitação com várias instruções como uma consulta de várias instruções, a menos que as instruções sejam inteiramente compostas de instruções CREATE TEMP FUNCTION seguidas por uma única instrução SELECT. Por exemplo, a consulta a seguir não é considerada de várias instruções:

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

Executar uma consulta de várias instruções

É possível executar uma consulta de várias instruções da mesma maneira que qualquer consulta, por exemplo, no console do Google Cloud ou usando a ferramenta da linha de comando.

Simular uma consulta de várias instruções

Para estimar o número de bytes lidos por uma consulta de várias instruções, faça uma simulação. Uma simulação de uma consulta de várias instruções é mais precisa para consultas que contêm apenas instruções SELECT.

As simulações têm processamento especial para os seguintes tipos de consulta e instrução:

• Instruções CALL: a simulação confirma que o procedimento chamado existe e tem uma assinatura correspondente aos argumentos fornecidos. O conteúdo do procedimento chamado e todas as instruções após a instrução CALL não são validados.
• Instruções DDL: a simulação valida a primeira instrução DDL e, em seguida, para. Todas as instruções subsequentes serão ignoradas.
• Instruções DML: a simulação valida a instrução DML e, em seguida, continua a validar as instruções subsequentes. Nesse caso, as estimativas de bytes são baseadas nos tamanhos originais da tabela e não consideram o resultado da instrução DML.
• Instruções EXECUTE IMMEDIATE: a simulação valida a expressão de consulta, mas não avalia a consulta dinâmica em si. Todas as instruções após a instrução EXECUTE IMMEDIATE serão ignoradas.
• Consultas que usam variáveis em um filtro de partição: a simulação valida a consulta inicial e as instruções subsequentes. No entanto, a simulação não consegue calcular o valor do ambiente de execução de variáveis em um filtro de partição. Isso afeta a estimativa de leitura de bytes.
• Consultas que usam variáveis na expressão de carimbo de data/hora de uma cláusula FOR SYSTEM TIME AS OF: a simulação usa o conteúdo atual da tabela e ignora a cláusula FOR SYSTEM TIME AS OF. Isso afetará a estimativa de leitura de bytes se houver diferenças de tamanho entre a tabela atual e a iteração anterior da tabela.
• Instruções de controle FOR, IF e WHILE: a simulação é interrompida imediatamente. As expressões de condição, os corpos da instrução de controle e todas as instruções subsequentes não são validados.

As simulações operam com base no melhor esforço e o processo subjacente está sujeito a alterações. As simulações estão sujeitas às seguintes condições:

• Uma consulta que conclui uma simulação com sucesso pode não ser executada com êxito. Por exemplo, as consultas podem falhar no momento da execução por motivos que não são detectados pelas simulações.
• Uma consulta executada com sucesso pode não concluir uma simulação. Por exemplo, as consultas podem falhar em simulações devido a motivos capturados na execução.
• Não há garantias de que as execuções de teste realizadas hoje sejam realizadas no futuro. Por exemplo, alterações na implementação da simulação podem detectar erros em uma consulta que não foram detectados anteriormente.

Salvar uma consulta de várias instruções

Para salvar uma consulta de várias instruções, consulte Trabalhar com consultas salvas.

Usar variáveis em uma consulta de várias instruções

Uma consulta de várias instruções pode conter variáveis criadas pelo usuário e variáveis do sistema.

• Você pode declarar variáveis criadas pelo usuário, atribuir valores a elas e referenciá-las em toda a consulta.

• É possível referenciar variáveis do sistema em uma consulta e atribuir valores a algumas delas, mas, ao contrário das variáveis definidas pelo usuário, você não as declara. O BigQuery inclui variáveis do sistema.

Declarar uma variável criada pelo usuário

É necessário declarar variáveis criadas pelo usuário no início da consulta de várias instruções ou no início de um bloco BEGIN. As variáveis declaradas no início da consulta de várias instruções estão no escopo de toda a consulta. As variáveis declaradas dentro de um bloco BEGIN estão no escopo do bloco. Elas saem do escopo depois da instrução END correspondente. O tamanho máximo de uma variável é 1 MB, e o tamanho máximo de todas as variáveis usadas em uma consulta de várias instruções é de 10 MB.

É possível declarar uma variável com a instrução processual DECLARE da seguinte 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 uma variável criada pelo usuário

Depois de declarar uma variável criada pelo usuário, é possível atribuir um valor a ela com a instrução processual SET da seguinte forma:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

Definir uma variável do sistema

Não é possível criar variáveis do sistema, mas você pode modificar o valor padrão para algumas delas, como em:

SET @@dataset_project_id = 'MyProject';

Também é possível definir e usar implicitamente uma variável do sistema em uma consulta de várias instruções. Por exemplo, na consulta a seguir, é necessário incluir o conjunto de dados sempre que quiser criar uma nova tabela:

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

Se você não quiser adicionar o projeto a caminhos de tabela várias vezes, atribua o ID do projeto do conjunto de dados MyProject à variável de sistema @@dataset_project_id na consulta de várias instruções. Essa atribuição torna MyProject o projeto padrão para o restante da consulta.

SET @@dataset_project_id = 'MyProject';

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

Da mesma forma, é possível definir a variável @@dataset_id do sistema para atribuir um conjunto de dados padrão à consulta. Exemplo:

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

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

Também é possível referenciar explicitamente as variáveis do sistema, como @@dataset_id, em muitas partes de uma consulta de várias instruções. Para saber mais, consulte Referência a uma variável do sistema.

Referenciar uma variável criada pelo usuário

Depois de declarar e definir uma variável criada pelo usuário, é possível referenciá-la em uma consulta de várias instruções. Se uma variável e uma coluna compartilharem o mesmo nome, a coluna terá prioridade.

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

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

Referenciar uma variável do sistema

É possível referenciar uma variável do sistema em uma consulta de várias instruções.

A seguinte consulta devolve o fuso horário padrão:

BEGIN
  SELECT @@time_zone AS default_time_zone;
END;

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

É possível usar variáveis do sistema com consultas em DDL e DML. Por exemplo, confira a seguir algumas maneiras de usar a variável de sistema @@time_zone ao criar e atualizar uma tabela:

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;

Há alguns lugares em que as variáveis do sistema não podem ser usadas em consultas em DDL e DML. Por exemplo, não é possível usar uma variável do sistema como um nome de projeto, conjunto de dados ou nome de tabela. Isso produz um erro quando você tenta incluir a variável de sistema @@dataset_id em um caminho de tabela:

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

Usar tabelas temporárias em uma consulta de várias instruções

Com as tabelas temporárias, você pode salvar os resultados intermediários em uma tabela. As tabelas temporárias são gerenciadas pelo BigQuery, então não é necessário salvá-las ou mantê-las em um conjunto de dados. A cobrança é feita pelo armazenamento de tabelas temporárias.

É possível criar e referenciar uma tabela temporária em uma consulta de várias instruções. Quando terminar de usar a tabela temporária, você poderá excluí-la manualmente para minimizar os custos de armazenamento ou esperar que o BigQuery a exclua após 24 horas.

Criar uma tabela temporária

É possível criar uma tabela temporária para uma consulta de várias instruções com a instrução CREATE TABLE. No exemplo a seguir, criamos uma tabela temporária para armazenar os resultados de uma consulta e a usamos em uma 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
);

Além do uso de TEMP ou TEMPORARY, a sintaxe é idêntica à sintaxe CREATE TABLE.

Ao criar uma tabela temporária, não use um qualificador de projeto ou conjunto de dados no nome da tabela. A tabela é criada automaticamente em um conjunto de dados especial.

Referenciar uma tabela temporária

É possível fazer referência a uma tabela temporária pelo nome durante a consulta de várias instruções atual. Isso inclui tabelas temporárias criadas por um procedimento na consulta de várias instruções. Não é possível compartilhar tabelas temporárias. As tabelas temporárias residem em conjuntos de dados _script% ocultos com nomes gerados aleatoriamente. O artigo Como listar conjuntos de dados descreve como listar conjuntos de dados ocultos.

Excluir tabelas temporárias

É possível excluir uma tabela temporária explicitamente antes da conclusão da consulta de várias instruções usando a instrução DROP TABLE:

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

Após a conclusão de uma consulta de várias instruções, a tabela temporária existirá por até 24 horas.

Ver dados da tabela temporária

Depois de criar uma tabela temporária, é exibida a estrutura da tabela e os dados nela. Para conferir a estrutura da tabela e os dados, siga estas etapas:

1. No console do Google Cloud, acesse a página Explorador do BigQuery.

   Acessar o Explorador

2. Clique em Histórico de consultas.

3. Escolha a consulta que criou a tabela temporária.

4. Na linha Tabela de destino, clique em Tabela temporária.

   Observação: o nome da tabela temporária usada na consulta de várias instruções não é preservado. No console do Google Cloud, ele tem um nome aleatório.

Qualificar tabelas temporárias com _SESSION

Quando tabelas temporárias são usadas com um conjunto de dados padrão, os nomes de tabela não qualificados se referem a uma tabela temporária, se houver, ou uma tabela no conjunto de dados padrão. A exceção é para instruções CREATE TABLE, em que a tabela de destino é considerada uma tabela temporária somente se a palavra-chave TEMP ou TEMPORARY estiver presente.

Por exemplo, considere a consulta de várias instruções a seguir:

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

Para indicar explicitamente que está se referindo a uma tabela temporária, qualifique o nome da tabela com _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;

Caso você use o qualificador _SESSION para uma consulta de uma tabela temporária que não existe, a consulta de múltiplas instruções emite um erro indicando que a tabela não existe. Por exemplo, se não houver uma tabela temporária chamada t3, a consulta de várias instruções gerará um erro mesmo que exista uma tabela chamada t3 no conjunto de dados padrão.

Não é possível usar _SESSION para criar uma tabela não temporária:

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

Coletar informações sobre um job de consulta de várias instruções

Um job de consulta de várias instruções contém informações sobre uma consulta de várias instruções que foi executada. Algumas tarefas comuns que podem ser realizadas com dados de job incluem retornar a última instrução executada com a consulta de várias instruções ou retornar todas as instruções executadas com a consulta de várias instruções.

Retornar a última instrução executada

O método jobs.getQueryResults retorna os resultados da consulta para a última instrução a ser executada na consulta de várias instruções. Se nenhuma instrução foi executada, nenhum resultado é retornado.

Retornar todas as instruções executadas

Para conferir os resultados de todas as instruções na consulta de várias instruções, enumere os jobs filhos e chame jobs.getQueryResults em cada um deles.

Enumerar jobs filhos

As consultas de várias instruções são executadas no BigQuery usando jobs.insert, semelhante a qualquer outra consulta, com as consultas de várias instruções especificadas como o texto de consulta. Quando uma consulta de várias instruções é executada, outros jobs, conhecidos como jobs filhos, são criados para cada instrução na consulta de várias instruções. É possível enumerar os jobs filhos de uma consulta de várias instruções chamando jobs.list, transmitindo o ID do job de consulta de várias instruções como parentJobId.

Depurar uma consulta de várias instruções

Confira algumas dicas para depurar consultas de várias instruções:

• Use a instrução ASSERT para declarar que uma condição booleana é verdadeira.

• Use BEGIN...EXCEPTION...END para capturar erros e exibir a mensagem de erro e o stack trace.

• Use SELECT FORMAT("....") para mostrar resultados intermediários.

• Ao executar uma consulta de várias instruções no console do Google Cloud, é possível conferir a saída de cada instrução na consulta de várias instruções. O comando ferramenta de linha de comando bq também mostra os resultados de cada etapa quando você executa uma consulta de várias instruções.

• No console do Google Cloud, é possível selecionar uma instrução individual dentro do editor de consultas e executá-la.

Permissões

A permissão para acessar uma tabela, modelo ou outro recurso é verificada no momento da execução. Se uma instrução não for executada ou uma expressão não for avaliada, o BigQuery não verificará se o usuário que está executando a consulta de múltiplas instruções tem acesso aos recursos referenciados por ele.

Em uma consulta de várias instruções, as permissões para cada expressão ou instrução são validadas separadamente. Exemplo:

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

Se o usuário que estiver executando a consulta tiver acesso a table1, mas não tiver acesso a table2, a primeira consulta será bem-sucedida e a segunda falhará. O próprio job de consulta de várias instruções também falha.

Restrições de segurança

Em consultas de várias instruções, use o SQL dinâmico para criar instruções SQL no ambiente de execução. Isso é conveniente, mas pode oferecer novas oportunidades de uso indevido. Por exemplo, a execução da consulta a seguir representa uma possível ameaça à segurança da injeção de SQL, já que o parâmetro de tabela pode ser filtrado indevidamente, permitir acesso e ser executado em tabelas não intencionais.

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

Para evitar a exposição ou o vazamento de dados confidenciais em uma tabela ou a execução de comandos como DROP TABLE, que excluir dados em uma tabela, suporte a instruções procedurais dinâmicas do BigQuery é compatível com várias medidas de segurança que reduzem a exposição a ataques de injeção de SQL, incluindo:

• Uma instrução EXECUTE IMMEDIATE não permite que a consulta, expandida com parâmetros e variáveis de consulta, incorpore várias instruções SQL.
• Os seguintes comandos estão impedidos de serem executados dinamicamente: BEGIN/END, CALL, CASE, IF, LOOP, WHILE e EXECUTE IMMEDIATE.

Limitações do campo de configuração

Os seguintes campos de consulta de configuração do job não podem ser definidos para uma consulta de várias instruções:

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

Preços

Os preços para consultas de várias instruções incluem cobranças por consultas (ao usar o modelo de faturamento sob demanda) e armazenamento para tabelas temporárias. Quando você usa reservas, o uso da consulta é coberto pelas cobranças de reserva.

Cálculo do tamanho da consulta sob demanda

Se você usa o faturamento sob demanda, o BigQuery cobra por consultas de várias instruções com base no número de bytes processados durante a execução das consultas de várias instruções.

Para ter uma estimativa de quantos bytes uma consulta de várias instruções pode processar, execute uma simulação.

Observação: o número de bytes verificados por uma consulta de várias instruções geralmente não é conhecido antes da execução. Para evitar custos de consulta não intencionais, use os preços baseados em capacidade. Como alternativa, é possível usar o sandbox do BigQuery para executar consultas gratuitas e limitadas.

Os preços a seguir são aplicáveis a estas consultas de várias instruções:

• DECLARE: a soma dos bytes verificados para todas as tabelas referenciadas pela expressão DEFAULT. Instruções DECLARE sem referências de tabela não geram custo.

• SET: a soma dos bytes verificados para todas as tabelas referenciadas pela expressão. Instruções SET sem referências de tabela não geram custo.

• IF: a soma dos bytes verificados para todas as tabelas referenciadas pela expressão condicional. Expressões condicionais IF sem referência de tabela não geram custo. Instruções no bloco IF que não forem executadas não geram custo.

• WHILE: a soma dos bytes verificados para todas as tabelas referenciadas pela expressão condicional. Instruções WHILE sem referências de tabela na expressão condicional não geram custo. Instruções no bloco WHILE que não forem executadas não geram custo.

• CONTINUE ou ITERATE: sem custo associado.

• BREAK ou LEAVE: sem custo associado.

• BEGIN ou END: sem custo associado.

Se uma consulta de várias instruções falhar, o custo das instruções até a falha ainda se aplicará. Instruções falhas não geram cobrança adicional.

Por exemplo, o exemplo de código a seguir contém comentários antes de cada instrução que explicam o custo de cada instrução, se houver:

-- 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 mais informações, consulte Cálculo do tamanho da consulta.

Preços de armazenamento

Você receberá cobranças por tabelas temporárias criadas por consultas com várias instruções. Use as visualizações TABLE_STORAGE ou TABLE_STORAGE_USAGE_TIMELINE para ver o armazenamento usado por essas tabelas temporárias. As tabelas temporárias residem em conjuntos de dados _script% ocultos com nomes gerados aleatoriamente.

Cotas

Para informações sobre cotas de consulta de várias instruções, consulte Cotas e limites.

Ver o número de consultas de várias instruções

É possível conferir o número de consultas de várias instruções ativas usando a visualização INFORMATION_SCHEMA.JOBS_BY_PROJECT. O exemplo a seguir usa a visualização INFORMATION_SCHEMA.JOBS_BY_PROJECT para mostrar o número de consultas de várias instruções do dia 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 mais informações sobre como consultar INFORMATION_SCHEMA.JOBS para consultas de várias instruções, consulte Job de consulta de várias instruções.