Scripts no SQL padrão

Scripts do BigQuery

Com os scripts do BigQuery, é possível enviar várias instruções ao BigQuery em uma solicitação, além de usar variáveis e usar instruções de fluxo de controle como IF e WHILE. Por exemplo, é possível declarar uma variável, atribuir um valor a ela e, em seguida, referenciá-la em uma terceira instrução.

No BigQuery, um script é uma lista de instruções SQL que serão executadas em sequência. Uma lista de instruções SQL é uma lista de todas as instruções válidas do BigQuery separadas por ponto e vírgula.

Por exemplo:

-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;
-- Build an array of the top 100 names from the year 2017.
SET top_names = (
  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
  FROM `bigquery-public-data`.usa_names.usa_1910_current
  WHERE year = 2017
);
-- Which names appear as words in Shakespeare's plays?
SELECT
  name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
  SELECT word
  FROM `bigquery-public-data`.samples.shakespeare
);

Os scripts são executados no BigQuery usando jobs.insert, que é semelhante a qualquer outra consulta, com o script de várias instruções especificado como o texto da consulta. Quando um script é executado, são criados nele outros jobs, conhecidos como jobs filhos, para cada instrução. Para enumerar os jobs filhos de um script, chame jobs.list e transmita o código do job como o parâmetro parentJobId.

Quando jobs.getQueryResults for invocado em um script, ele retornará os resultados de consulta da última instrução SELECT, DML ou DDL a ser executada no script. Se nenhuma delas for executada, não haverá resultados de consulta. Para receber os resultados de todas as instruções no script, enumere os jobs filhos e chame jobs.getQueryResults em cada um deles.

O BigQuery interpreta qualquer solicitação com várias instruções como um script, a menos que elas sejam instruções CREATE TEMP FUNCTION com uma única instrução de consulta final. Por exemplo, o caso a seguir não seria considerado um script:

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

SELECT Add(3, 4);

DECLARAR

Sintaxe

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name precisa ser um identificador válido, sendo variable_type qualquer tipo do BigQuery.

Descrição

Declara uma variável do tipo especificado. Se a cláusula DEFAULT for especificada, a variável será inicializada com o valor da expressão. Se nenhuma cláusula DEFAULT estiver presente, a variável será inicializada com o valor NULL.

Se o [variable_type] for omitido, será preciso especificar uma cláusula DEFAULT. O tipo da variável será inferido pelo tipo da expressão na cláusula DEFAULT.

As declarações de variável precisam aparecer no início de um script, antes de qualquer outra instrução ou no início de um bloco declarado com BEGIN. Não há diferenciação entre maiúsculas e minúsculas nos nomes de variáveis.

Vários nomes de variáveis podem aparecer em uma única instrução DECLARE, mas apenas uma variable_type e expression.

É um erro declarar uma variável com o mesmo nome de uma variável declarada anteriormente no bloco atual ou em um bloco que a contém.

Se a cláusula DEFAULT estiver presente, o valor da expressão precisará ser coercível para o tipo especificado. A expressão pode referenciar outras variáveis declaradas anteriormente dentro do mesmo bloco ou em um bloco que a contém.

O tamanho máximo de uma variável é 1 MB, e o tamanho máximo de todas as variáveis usadas em um script é de 10 MB.

Exemplos

O exemplo a seguir inicializa a variável x como um INT64 com o valor NULL.

DECLARE x INT64;

O exemplo a seguir inicializa a variável d como uma DATE com o valor da data atual.

DECLARE d DATE DEFAULT CURRENT_DATE();

O exemplo a seguir inicializa as variáveis x, y e z como INT64 com o valor 0.

DECLARE x, y, z INT64 DEFAULT 0;

No exemplo a seguir, uma variável denominada item é declarada, correspondendo a um item arbitrário na tabela dataset1.products. O tipo de item é inferido do esquema da tabela.

DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);

SET

Sintaxe

SET name = expression;

SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

SET @@time_zone = expression;

Descrição

Define o valor da expressão fornecida para uma variável ou define várias variáveis ao mesmo tempo, com base no resultado de várias expressões.

A instrução SET pode aparecer em qualquer parte do corpo de um script.

Exemplos

O exemplo a seguir define o valor 5 para a variável x.

SET x = 5;

O exemplo a seguir define o valor 4 para a variável a, "foo" para b e false para c.

SET (a, b, c) = (1 + 3, 'foo', false);

O exemplo a seguir atribui o resultado de uma consulta a diversas variáveis. Primeiro, ele declara duas variáveis, target_word e corpus_count; em seguida, atribui os resultados de uma consulta SELECT AS STRUCT às duas variáveis. O resultado da consulta é uma única linha contendo um STRUCT com dois campos: o primeiro elemento é atribuído à primeira variável e o segundo elemento é atribuído à segunda variável.

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

Essa lista de instruções gera a seguinte string:

Found 151 occurrences of "methinks" across 38 Shakespeare works

No exemplo a seguir, definimos a variável de sistema @@time_zone, que afeta o resultado da função DATETIME() usada na consulta a seguir. A consulta retorna "2016-12-31T19:00:00" porque o horário padrão de Nova York é cinco horas mais cedo que o UTC. Ao fazer a atribuição a @@time_zone, o valor não pode ser NULL e precisa corresponder a um nome de fuso horário válido.

SET @@time_zone = 'America/New_York';
SELECT DATETIME(TIMESTAMP('2017-01-01', 'UTC'));

BEGIN

Sintaxe

BEGIN
  sql_statement_list
END;

Descrição

BEGIN inicia um bloco de declarações onde as variáveis declaradas existem somente até o END correspondente. sql_statement_list é uma lista de zero ou mais instruções SQL terminadas em ponto e vírgula.

As declarações de variável precisam aparecer no início do bloco, antes de outros tipos de declarações. As variáveis declaradas dentro de um bloco só podem ser referenciadas dentro desse mesmo bloco e em blocos aninhados. É um erro declarar uma variável com o mesmo nome de uma variável declarada no mesmo bloco ou em um bloco externo.

Há um nível máximo de aninhamento de 50 para blocos e declarações condicionais como BEGIN/END, IF/ELSE/END IF e WHILE/END WHILE.

Exemplo

No exemplo a seguir, uma variável x é declarada com o valor padrão 10. Em seguida, ela inicia um bloco, em que uma variável y recebe 10 como o valor de x e o retorna. Depois, esse bloco é encerrado pela instrução END, finalizando o escopo da variável y. Por fim, o valor de x é retornado.

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

BEGIN...EXCEPTION

Sintaxe

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Descrição

BEGIN...EXCEPTION executa um bloco de instruções. Se alguma das instruções encontra um erro, o script ignora o restante do bloco e executa as instruções na cláusula EXCEPTION.

Na cláusula EXCEPTION, é possível acessar os detalhes sobre o erro usando as seguintes variáveis de sistema EXCEPTION:

Nome	Tipo	Descrição
`@@error.formatted_stack_trace`	`STRING`	O conteúdo de `@@error.stack_trace`, expresso como uma string legível. Este valor, destinado a fins de exibição, está sujeito a alterações sem aviso. O acesso programático ao rastreamento de pilha de um erro deve usar `@@error.stack_trace`.
`@@error.message`	`STRING`	Especifica uma mensagem de erro legível.
`@@error.stack_trace`	Consulte 1.	Cada elemento da matriz corresponde a uma instrução ou chamada de procedimento em execução no momento do erro, com o frame de pilha em execução sendo exibido primeiro. O significado de cada campo é definido da seguinte maneira: line/column: especifica o número da linha e da coluna do frame de pilha, começando com 1. Se o frame ocorrer dentro do corpo de um procedimento, `line 1 column 1` corresponderá à palavra-chave `BEGIN` no início do corpo do procedimento. location: se o frame ocorrer no corpo de um procedimento, especifica o nome completo do procedimento, no formato `[project_name].[dataset_name].[procedure_name]`. Se o frame se referir a um local em um script de nível superior, esse campo será `NULL`. filename: reservado para uso futuro. Sempre `NULL`.
`@@error.statement_text`	`STRING`	Especifica o texto da instrução que causou o erro.

¹ O tipo de @@error.stack_trace é ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

Como o BigQuery reserva-se o direito de revisar mensagens de erro a qualquer momento, os consumidores de @@error.message não devem pensar que essas mensagens permanecem iguais nem seguir padrões específicos. Não extraia informações do local em que a falha ocorreu do texto da mensagem de erro. Em vez disso, use @@error.stack_trace e @@error.statement_text.

Para processar as exceções que foram geradas, mas não processadas, por um gerenciador de exceções, é necessário agrupar o bloco em outro externo usando um gerenciador diferente.

Veja a seguir como utilizar um bloco externo com um gerenciador de exceções separado:

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

Os blocos BEGIN...EXCEPTION também são compatíveis com instruções DECLARE, como qualquer outro bloco BEGIN. As variáveis declaradas em um bloco BEGIN são válidas apenas na seção BEGIN, não podendo ser usadas no gerenciador de exceções do bloco.

Exemplo

CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN
  CALL dataset1.proc1();
END;

BEGIN
  CALL dataset1.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

Neste exemplo, quando ocorre o erro de divisão por zero, em vez de encerrar todo o script, o BigQuery encerra dataset1.proc1() e dataset1.proc2() e executa a instrução SELECT no gerenciador de exceções. Quando o gerenciador é executado, as variáveis assumem os seguintes valores:

Variável	Valor
`@@error.message`	`"Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]"`
`@@error.stack_trace`	`[` `STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location),` `STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location),` `STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location),` `]`
`@@error.statement_text`	`"SELECT 1/0"`
`@@error.formatted_stack_trace`	`"At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]"`

END

Encerra um bloco iniciado por BEGIN.

SE

Sintaxe

IF condition THEN [sql_statement_list]
[ELSEIF condition THEN sql_statement_list]
[ELSEIF condition THEN sql_statement_list]...
[ELSE  sql_statement_list]
END IF;

Descrição

Executa o primeiro sql_statement_list, em que a condição é verdadeira, ou o ELSE sql_statement_list opcional, se não há condições correspondentes.

Há um nível máximo de aninhamento de 50 para blocos e declarações condicionais como BEGIN/END, IF/ELSE/END IF e WHILE/END WHILE.

Exemplos

O exemplo a seguir declara uma variável INT64 target_product_id com um valor padrão de 103; depois, ele verifica se a tabela dataset.products contém uma linha com a coluna product_id correspondente ao valor de target_product_id; em caso afirmativo, ele gera uma string informando que o produto foi encontrado, junto do valor de default_product_id; em caso negativo, ele gera uma string informando que o produto não foi encontrado, bem como o valor de default_product_id.

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
  ELSEIF EXISTS (SELECT 1 FROM dataset.more_products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product from more_products table',
  CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

Loops

LOOP

Sintaxe

LOOP
  sql_statement_list
END LOOP;

Descrição

Executa sql_statement_list até que uma instrução BREAK ou LEAVE saia do loop. sql_statement_list é uma lista de zero ou mais instruções SQL terminadas em ponto e vírgula.

Exemplo

O exemplo a seguir declara uma variável x com o valor padrão 0; então, ele usa a instrução LOOP para criar um loop que é executado até que a variável x seja maior ou igual a 10; após o encerramento do loop, o exemplo gera o valor de x.

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

Este exemplo retorna o seguinte:

+----+
| x  |
+----+
| 10 |
+----+

ENQUANTO

Sintaxe

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Há um nível máximo de aninhamento de 50 para blocos e declarações condicionais como BEGIN/END, IF/ELSE/END IF e WHILE/END WHILE.

Descrição

Enquanto boolean_expression for verdadeiro, executa sql_statement_list. boolean_expression é avaliado para cada iteração do loop.

QUEBRA

Descrição

Sai do loop atual.

É um erro usar BREAK fora de um loop.

Exemplo

No exemplo a seguir, o código declara duas variáveis, heads e heads_count. Depois, ele inicia um loop, que atribui um valor booleano aleatório a heads e verifica se heads é verdadeiro. Se for o caso, o resultado será "Heads!" (cara), e heads_count será adicionado. Em caso negativo, o resultado será "Tails!"(coroa), e o loop será encerrado. Por fim, ele gera uma string que indica quantas vezes o jogo de "cara ou coroa" resultou em "cara".

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

LEAVE

Sinônimo de BREAK.

CONTINUAR

Descrição

Ignora as instruções a seguir no loop atual e retorna ao início do loop.

É um erro usar CONTINUE fora de um loop.

Exemplo

No exemplo a seguir, o código declara duas variáveis, heads e heads_count. Depois, ele inicia um loop, que atribui um valor booleano aleatório a heads e verifica se heads é verdadeiro. Se for o caso, o resultado será "Heads!" (cara), heads_count será adicionado e o loop será reiniciado, ignorando as instruções restantes. Em caso negativo, o resultado será "Tails!" (coroa), e o loop será encerrado. Por fim, ele gera uma string que indica quantas vezes o jogo de "cara ou coroa" resultou em "cara".

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

Sinônimo de CONTINUE.

RAISE

Sintaxe

RAISE [USING MESSAGE = message];

Descrição

Gera um erro, opcionalmente, usando a mensagem de erro especificada quando USING MESSAGE = message é fornecido.

Quando `USING MESSAGE` não é fornecido

A instrução RAISE só precisa ser usada em uma cláusula EXCEPTION. A instrução RAISE gerará novamente a exceção que foi capturada, preservando o rastreamento de pilha original.

Quando `USING MESSAGE` é fornecido

Veja o que acontecerá se a instrução RAISE estiver contida na seção BEGIN de um bloco BEGIN...EXCEPTION:

O gerenciador será invocado.
O valor de @@error.message corresponderá exatamente à string message fornecida (que poderá ser NULL se message for NULL).
O rastreamento de pilha será definido com a instrução RAISE.

Se a instrução RAISE não estiver contida na seção BEGIN de um bloco BEGIN...EXCEPTION, a instrução RAISE encerrará a execução do script com a mensagem de erro fornecida.

RETURN

Em um script do BigQuery, RETURN encerra a execução do script atual.

LIGAR

Sintaxe

CALL procedure_name (procedure_argument[, …])

Descrição

Chama um procedimento com uma lista de argumentos. procedure_argument pode ser uma variável ou uma expressão. Para argumentos OUT ou INOUT, uma variável passada como um argumento precisa ter o tipo apropriado do BigQuery.

A mesma variável pode não aparecer diversas vezes como um argumento OUT ou INOUT na lista de argumentos do procedimento.

A profundidade máxima das chamadas de procedimento é de 50 frames.

Exemplo

O exemplo a seguir declara uma variável retCode. Em seguida, ele chama o procedimento updateSomeTables no conjunto de dados myDataset, passando os argumentos 'someAccountId' e retCode. Por fim, o valor de retCode é retornado.

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;

Variáveis de sistema

É possível usar variáveis de sistema para verificar informações durante a execução de um script.

Nome	Tipo	Descrição
`@@current_job_id`	`STRING`	ID do job em execução no momento. No contexto de um script, retorna o job responsável pela instrução atual, não o script inteiro.
`@@last_job_id`	`STRING`	ID do job mais recente a ser executado no script atual, sem incluir o atual. Se o script contém instruções `CALL`, ele pode ser originário de um procedimento diferente.
`@@project_id`	`STRING`	ID do projeto usado para executar a consulta atual. No contexto de um procedimento, `@@project_id` refere-se ao projeto que executa o script, não ao projeto proprietário do procedimento.
`@@row_count`	`INT64`	Se usado em um script e a instrução de script anterior for DML, especifica o número de linhas modificadas, inseridas ou excluídas, em decorrência dessa instrução DML. Se a instrução anterior é MERGE, `@@row_count` representa o número total combinado de linhas inseridas, removidas e excluídas. Esse valor será `NULL` se não estiver em um script.
`@@script.bytes_billed`	`INT64`	Total de bytes faturados até o momento no job de script em execução no momento. Esse valor será `NULL` se não estiver em um script.
`@@script.bytes_processed`	`INT64`	Total de bytes processados até o momento no job de script em execução no momento. Esse valor será `NULL` se não estiver em um script.
`@@script.creation_time`	`TIMESTAMP`	Hora de criação do job de script em execução no momento. Esse valor será `NULL` se não estiver em um script.
`@@script.job_id`	`STRING`	ID do job de script em execução no momento. Esse valor será `NULL` se não estiver em um script.
`@@script.num_child_jobs`	`INT64`	Número de jobs filhos concluídos no momento. Esse valor será `NULL` se não estiver em um script.
`@@script.slot_ms`	`INT64`	Número de milissegundos de slot usados até o momento pelo script. Esse valor será `NULL` se não estiver em um script.
`@@time_zone`	`STRING`	O fuso horário padrão a ser usado em funções SQL dependentes de fuso horário, quando um fuso horário explícito não é especificado como um argumento. Ao contrário de outras variáveis de sistema, é possível modificar `@@time_zone` usando uma instrução `SET` para qualquer nome de fuso horário válido. No início de cada script, `@@time_zone` começa como "UTC".

Observação: para oferecer compatibilidade com versões anteriores, as expressões usadas em uma cláusula OPTIONS ou FOR SYSTEM TIME AS OF são padronizadas com o fuso horário America/Los_Angeles, enquanto todas as outras expressões de data e hora usam o fuso horário UTC. Se @@time_zone tiver sido definida anteriormente no script, o fuso horário escolhido será aplicado a todas as expressões de data e hora, incluindo as cláusulas OPTIONS e FOR SYSTEM TIME AS OF.

Além das variáveis de sistema mostradas acima, é possível usar EXCEPTION durante a execução de um script. As variáveis de sistema EXCEPTION serão NULL se não forem usadas em um gerenciador de exceções. Veja a seguir as variáveis de sistema EXCEPTION.

@@error.formatted_stack_trace
@@error.message
@@error.stack_trace
@@error.statement_text

Para mais informações sobre as variáveis de sistema EXCEPTION, consulte BEGIN...EXCEPTION.

Scripts no SQL padrão

Scripts do BigQuery

DECLARAR

Sintaxe

Descrição

Exemplos

SET

Sintaxe

Descrição

Exemplos

BEGIN

Sintaxe

Descrição

Exemplo

BEGIN...EXCEPTION

Sintaxe

Descrição

Exemplo

END

SE

Sintaxe

Descrição

Exemplos

Loops

LOOP

Sintaxe

Descrição

Exemplo

ENQUANTO

Sintaxe

Descrição

QUEBRA

Descrição

Exemplo

LEAVE

CONTINUAR

Descrição

Exemplo

ITERATE

RAISE

Sintaxe

Descrição

Quando USING MESSAGE não é fornecido

Quando USING MESSAGE é fornecido

RETURN

LIGAR

Variáveis de sistema

Quando `USING MESSAGE` não é fornecido

Quando `USING MESSAGE` é fornecido