Trabalhe com consultas de várias declarações

Uma consulta com várias declarações é uma coleção de declarações SQL que pode executar numa sequência, com estado partilhado.

Este documento descreve como usar consultas com várias declarações no BigQuery, como escrever consultas com várias declarações, usar tabelas temporárias em consultas com várias declarações, fazer referência a variáveis em consultas com várias declarações e depurar consultas com várias declarações.

As consultas com várias declarações são frequentemente usadas em procedimentos armazenados e suportam declarações de linguagem processual, que lhe permitem fazer coisas como definir variáveis e implementar o fluxo de controlo. As consultas com várias declarações podem conter declarações DDL e DML que têm efeitos secundários, como a criação ou a modificação de tabelas ou dados de tabelas.

Escreva, execute e guarde consultas com várias declarações

Uma consulta com várias declarações consiste numa ou mais declarações SQL separadas por pontos e vírgulas. Pode usar qualquer declaração SQL válida numa consulta com várias declarações. As consultas com várias declarações também podem incluir declarações de linguagem processual, que lhe permitem usar variáveis ou implementar o fluxo de controlo com as suas declarações SQL.

Escreva uma consulta com várias declarações

Pode escrever uma consulta com várias declarações no BigQuery. A seguinte consulta de declaração de várias consultas declara uma variável e usa a variável numa declaraçã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 pedido com várias declarações como uma consulta com várias declarações, a menos que as declarações consistam inteiramente em declarações CREATE TEMP FUNCTION seguidas de uma única declaração SELECT. Por exemplo, o seguinte não é considerado uma consulta com várias declarações:

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

Execute uma consulta com várias declarações

Pode executar uma consulta com várias declarações da mesma forma que qualquer outra consulta, por exemplo, na consola Trusted Cloud ou através da ferramenta de linha de comandos bq.

Faça um teste de execução de uma consulta com várias declarações

Para estimar o número de bytes lidos por uma consulta com várias declarações, considere uma execução de teste. Uma execução de teste de uma consulta com várias declarações é mais precisa para consultas que contêm apenas declarações SELECT.

As execuções de teste têm um processamento especial para os seguintes tipos de consultas e declarações:

• CALL declarações: o teste de execução valida se o procedimento chamado existe e tem uma assinatura que corresponde aos argumentos fornecidos. O conteúdo do procedimento chamado e todas as declarações após a declaração CALL não são validados.
• Declarações DDL: a execução de teste valida a primeira declaração DDL e, em seguida, para. Todas as declarações subsequentes são ignoradas. Os ensaios de declarações CREATE TEMP TABLE não são suportados.
• Instruções DML: a execução de teste valida a instrução DML e, em seguida, continua a validar as instruções subsequentes. Neste caso, as estimativas de bytes baseiam-se nos tamanhos das tabelas originais e não têm em conta o resultado da declaração DML.
• EXECUTE IMMEDIATE declarações: a execução de teste valida a expressão de consulta, mas não avalia a própria consulta dinâmica. Todas as declarações após a declaração EXECUTE IMMEDIATE são ignoradas.
• Consultas que usam variáveis num filtro de partição: a execução de teste valida a consulta inicial e as declarações subsequentes. No entanto, a execução de teste não consegue calcular o valor de tempo de execução das variáveis num filtro de partição. Isto afeta a estimativa de bytes lidos.
• Consultas que usam variáveis na expressão de data/hora de uma cláusula FOR SYSTEM TIME AS OF: o teste de execução usa o conteúdo atual da tabela e ignora a cláusula FOR SYSTEM TIME AS OF. Isto afeta a estimativa de bytes lidos se existirem diferenças de tamanho entre a tabela atual e a iteração anterior da tabela.
• Declarações de controlo FOR, IF e WHILE: a execução de teste é interrompida imediatamente. As expressões de condição, os corpos da declaração de controlo e todas as declarações subsequentes não são validados.

Os ensaios funcionam com base no melhor esforço, e o processo subjacente está sujeito a alterações. Os ensaios estão sujeitos às seguintes estipulações:

• Uma consulta que conclui com êxito uma execução de teste pode não ser executada com êxito. Por exemplo, as consultas podem falhar no tempo de execução devido a motivos que não são detetados pelas execuções de ensaio.
• Uma consulta que é executada com êxito pode não concluir uma execução de teste com êxito. Por exemplo, as consultas podem falhar os testes de execução simulada por motivos detetados na execução.
• Não é garantido que as execuções de teste que forem executadas com êxito hoje sejam sempre executadas no futuro. Por exemplo, as alterações à implementação do teste de execução podem detetar erros numa consulta que não foram detetados anteriormente.

Guarde uma consulta com várias declarações

Para guardar uma consulta com várias declarações, consulte o artigo Trabalhe com consultas guardadas.

Use variáveis numa consulta com várias declarações

Uma consulta com várias declarações pode conter variáveis criadas pelo utilizador e variáveis do sistema.

• Pode declarar variáveis criadas pelo utilizador, atribuir-lhes valores e referenciá-las ao longo da consulta.

• Pode fazer referência a variáveis do sistema numa consulta e atribuir valores a algumas delas, mas, ao contrário das variáveis definidas pelo utilizador, não as declara. As variáveis do sistema estão incorporadas no BigQuery.

Declare uma variável criada pelo utilizador

Tem de declarar as variáveis criadas pelo utilizador no início da consulta com várias declarações ou no início de um bloco BEGIN. As variáveis declaradas no início da consulta com várias declarações estão no âmbito de toda a consulta. As variáveis declaradas num bloco BEGIN têm âmbito para o bloco. Deixam de estar no âmbito após a declaração END correspondente. O tamanho máximo de uma variável é de 1 MB e o tamanho máximo de todas as variáveis usadas numa consulta com várias declarações é de 10 MB.

Pode declarar uma variável com a declaraçã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

Defina uma variável criada pelo utilizador

Depois de declarar uma variável criada pelo utilizador, pode atribuir-lhe um valor com a declaração processual SET da seguinte forma:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

Defina uma variável do sistema

Não cria variáveis do sistema, mas pode substituir o valor predefinido de algumas delas da seguinte forma:

SET @@dataset_project_id = 'MyProject';

Também pode definir e usar implicitamente uma variável do sistema numa consulta com várias declarações. Por exemplo, na consulta seguinte, tem de incluir o projeto sempre que quiser criar uma nova tabela:

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

Se não quiser adicionar o projeto aos caminhos das tabelas várias vezes, pode atribuir o ID do projeto do conjunto de dados MyProject à variável do sistema @@dataset_project_id na consulta com várias declarações. Esta atribuição torna MyProject o projeto predefinido para o resto da consulta.

SET @@dataset_project_id = 'MyProject';

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

Da mesma forma, pode definir a variável de sistema @@dataset_id para atribuir um conjunto de dados predefinido à consulta. Por exemplo:

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

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

Também pode fazer referência explícita a variáveis do sistema, como @@dataset_id, em muitas partes de uma consulta com várias declarações. Para saber mais, consulte os exemplos de variáveis do sistema.

Referencie uma variável criada pelo utilizador

Depois de declarar e definir uma variável criada pelo utilizador, pode fazer referência à mesma numa consulta com várias declarações. Se uma variável e uma coluna partilharem o mesmo nome, a coluna tem precedência.

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

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

Use tabelas temporárias numa consulta com várias declarações

As tabelas temporárias permitem-lhe guardar resultados intermédios numa tabela. As tabelas temporárias são geridas pelo BigQuery, pelo que não tem de as guardar nem manter num conjunto de dados. É-lhe cobrado o armazenamento de tabelas temporárias.

Pode criar e referenciar uma tabela temporária numa consulta com várias declarações. Quando terminar de usar a tabela temporária, pode eliminá-la manualmente para minimizar os custos de armazenamento ou aguardar que o BigQuery a elimine após 24 horas.

Crie uma tabela temporária

Pode criar uma tabela temporária para uma consulta com várias declarações com a declaração CREATE TABLE. O exemplo seguinte cria uma tabela temporária para armazenar os resultados de uma consulta e usa a tabela temporária numa 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 da utilização de TEMP ou TEMPORARY, a sintaxe é idêntica à sintaxe CREATE TABLE.

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

Referencie uma tabela temporária

Pode referir-se a uma tabela temporária pelo nome durante a consulta de várias declarações atual. Isto inclui tabelas temporárias criadas por um procedimento na consulta com várias declarações. Não pode partilhar tabelas temporárias. As tabelas temporárias residem em conjuntos de dados _script% ocultos com nomes gerados aleatoriamente. O artigo Listar conjuntos de dados descreve como listar conjuntos de dados ocultos.

Elimine tabelas temporárias

Pode eliminar uma tabela temporária explicitamente antes de a consulta com várias declarações ser concluída através da declaração DROP TABLE:

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

Depois de uma consulta com várias declarações terminar, a tabela temporária existe durante um máximo de 24 horas.

Veja os dados da tabela temporária

Depois de criar uma tabela temporária, pode ver a estrutura da tabela e todos os dados na mesma. Para ver a estrutura e os dados da tabela, siga estes passos:

1. Na Trusted Cloud consola, aceda à página Explorador do BigQuery.

   Aceda ao Explorador

2. Clique em Histórico de consultas.

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

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

Qualifique tabelas temporárias com _SESSION

Quando as tabelas temporárias são usadas em conjunto com um conjunto de dados predefinido, os nomes de tabelas não qualificados referem-se a uma tabela temporária, se existir, ou a uma tabela no conjunto de dados predefinido. A exceção aplica-se às declarações CREATE TABLE, em que a tabela de destino é considerada uma tabela temporária se e apenas se a palavra-chave TEMP ou TEMPORARY estiver presente.

Por exemplo, considere a seguinte consulta com várias declarações:

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

Pode indicar explicitamente que se está a referir a uma tabela temporária qualificando 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;

Se usar o qualificador _SESSION para uma consulta de uma tabela temporária que não exista, a consulta de várias declarações gera um erro a indicar que a tabela não existe. Por exemplo, se não existir uma tabela temporária com o nome t3, a consulta com várias declarações gera um erro, mesmo que exista uma tabela com o nome t3 no conjunto de dados predefinido.

Não pode usar _SESSION para criar uma tabela não temporária:

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

Recolha informações sobre uma tarefa de consulta com várias declarações

Uma tarefa de consulta com várias declarações contém informações sobre uma consulta com várias declarações que foi executada. Algumas tarefas comuns que pode realizar com dados de tarefas incluem devolver a última declaração executada com a consulta de várias declarações ou devolver todas as declarações executadas com a consulta de várias declarações.

Devolve a última declaração executada

O método jobs.getQueryResults devolve os resultados da consulta para a última declaração a ser executada na consulta de várias declarações. Se não tiver sido executada nenhuma declaração, não são devolvidos resultados.

Devolva todas as declarações executadas

Para obter os resultados de todas as declarações numa consulta com várias declarações, enumere as tarefas secundárias e chame jobs.getQueryResults em cada uma delas.

Enumere tarefas secundárias

As consultas com várias declarações são executadas no BigQuery através de jobs.insert, semelhante a qualquer outra consulta, com as consultas com várias declarações especificadas como o texto da consulta. Quando uma consulta com várias declarações é executada, são criadas tarefas adicionais, conhecidas como tarefas secundárias, para cada declaração na consulta com várias declarações. Pode enumerar as tarefas secundárias de uma consulta com várias declarações chamando jobs.list e transmitindo o ID da tarefa de consulta com várias declarações como o parâmetro parentJobId.

Depure uma consulta com várias declarações

Seguem-se algumas sugestões para depurar consultas com várias declarações:

• Use a declaração ASSERT para afirmar que uma condição booleana é verdadeira.

• Use BEGIN...EXCEPTION...END para detetar erros e apresentar a mensagem de erro e o rastreio da pilha.

• Use SELECT FORMAT("....") para mostrar resultados intermédios.

• Quando executa uma consulta com várias declarações na Trusted Cloud consola, pode ver o resultado de cada declaração na consulta com várias declarações. O comando bq query da ferramenta de linha de comandos bq também mostra os resultados de cada passo quando executa uma consulta com várias declarações.

• Na Trusted Cloud consola, pode selecionar uma declaração individual no editor de consultas e executá-la.

Autorizações

A autorização para aceder a uma tabela, a um modelo ou a outro recurso é verificada no momento da execução. Se uma declaração não for executada ou uma expressão não for avaliada, o BigQuery não verifica se o utilizador que executa a consulta com várias declarações tem acesso a quaisquer recursos referenciados pela mesma.

Numa consulta com várias declarações, as autorizações para cada expressão ou declaração são validadas separadamente. Por exemplo:

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

Se o utilizador que executa a consulta tiver acesso a table1, mas não tiver acesso a table2, a primeira consulta é bem-sucedida e a segunda consulta falha. A tarefa de consulta com várias declarações também falha.

Restrições de segurança

Em consultas com várias declarações, pode usar SQL dinâmico para criar declarações SQL no momento da execução. Isto é conveniente, mas pode oferecer novas oportunidades de utilização indevida. Por exemplo, a execução da seguinte consulta representa uma potencial ameaça de segurança de injeção SQL, uma vez que o parâmetro da tabela pode ser filtrado incorretamente, permitindo o acesso e a execução em tabelas não pretendidas.

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

Para evitar a exposição ou a fuga de dados confidenciais numa tabela ou a execução de comandos como DROP TABLE para eliminar dados numa tabela, as declarações processuais dinâmicas do BigQuery suportam várias medidas de segurança para reduzir a exposição a ataques de injeção SQL, incluindo:

• Uma declaração EXECUTE IMMEDIATE não permite que a respetiva consulta, expandida com parâmetros de consulta e variáveis, incorpore várias declarações SQL.
• Os seguintes comandos estão restritos de execução dinâmica: BEGIN/END, CALL, CASE, IF, LOOP, WHILE e EXECUTE IMMEDIATE.

Limitações dos campos de configuração

Não é possível definir os seguintes campos de consulta de configuração de tarefas para uma consulta com várias declarações:

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

Preços

A definição de preços para consultas com várias declarações inclui encargos para consultas (quando usa o modelo de faturação a pedido) e armazenamento para tabelas temporárias. Quando usa reservas, a utilização de consultas é coberta pelos custos da reserva.

Cálculo do tamanho da consulta a pedido

Se usar a faturação a pedido, o BigQuery cobra pelas consultas com várias declarações com base no número de bytes processados durante a execução das consultas com várias declarações.

Para obter uma estimativa do número de bytes que uma consulta com várias declarações pode processar, pode executar um teste de execução.

Os seguintes preços aplicam-se a estas consultas com várias declarações:

• DECLARE: a soma dos bytes analisados para quaisquer tabelas referenciadas na expressão DEFAULT. As declarações DECLARE sem referências de tabelas não incorrem em custos.

• SET: a soma dos bytes analisados para todas as tabelas referenciadas na expressão. SET As declarações sem referências de tabelas não incorrem em custos.

• IF: a soma dos bytes analisados para quaisquer tabelas referenciadas na expressão de condição. As expressões de condição IF sem referência de tabela não incorrem em custos. Todas as declarações no bloco IF que não forem executadas não incorrem em custos.

• WHILE: a soma dos bytes analisados para quaisquer tabelas referenciadas na expressão de condição. As declarações WHILE sem referências de tabelas na expressão de condição não incorrem em custos. Todas as declarações no bloco WHILE que não forem executadas não incorrem em custos.

• CONTINUE ou ITERATE: sem custo associado.

• BREAK ou LEAVE: sem custo associado.

• BEGIN ou END: sem custo associado.

Se uma consulta com várias declarações falhar, o custo de todas as declarações até à falha continua a aplicar-se. O extrato com falha não incorre em custos.

Por exemplo, o seguinte código de exemplo contém comentários antes de cada declaração que explicam o custo, se existir, incorrido por cada declaração:

-- 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 a secção Cálculo do tamanho da consulta.

Preços de armazenamento

São-lhe cobradas as tabelas temporárias criadas por consultas com várias declarações. Pode usar as vistas TABLE_STORAGE ou TABLE_STORAGE_USAGE_TIMELINE para ver o armazenamento usado por estas tabelas temporárias. As tabelas temporárias residem em conjuntos de dados _script% ocultos com nomes gerados aleatoriamente.

Quotas

Para obter informações sobre as quotas de consultas com várias declarações, consulte o artigo Quotas e limites.

Veja o número de consultas com várias declarações

Pode ver o número de consultas com várias declarações ativas através da vista INFORMATION_SCHEMA.JOBS_BY_PROJECT. O exemplo seguinte usa a vista INFORMATION_SCHEMA.JOBS_BY_PROJECT para mostrar o número de consultas com várias declaraçõ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 a consulta de INFORMATION_SCHEMA.JOBS para consultas com várias declarações, consulte o artigo Tarefa de consulta com várias declarações.