Scripts no SQL padrão

Beta

Este recurso está em estado de pré-lançamento, podendo ser alterado ou ter suporte limitado. Para mais informações, consulte as etapas de lançamento do produto.

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

O BigQuery interpreta qualquer solicitação com várias instruções como um script, a menos que as instruções consistam em CREATE TEMP FUNCTION instrução(ões), 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 e 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.

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;

CONFIGURAR:

Sintaxe

SET name = expression;

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

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, o valor "foo" para a variável b e o valor false para a variável 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;

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

Found 151 occurrences of "methinks" across 38 Shakespeare works

INICIAR

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

O exemplo a seguir declara uma variável x com o valor padrão 10; em seguida, ele inicia um bloco, no qual uma variável y recebe o valor de , que é 10, e retorna esse valor; em seguida, a instrução END encerra o bloco, encerrando o escopo da variável y; por fim, ele retorna o valor de .

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

ENCERRAR

Encerra um bloco iniciado por BEGIN.

SE

Sintaxe

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

Descrição

Se condition for verdadeiro, executa if_statement_list. Caso contrário, executa else_statement_list, se a cláusula ELSE opcional for fornecida.

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 ; 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));
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 seja maior ou igual a 10; após o encerramento do loop, o exemplo gera o valor de .

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

O exemplo a seguir declara duas variáveis, heads e heads_count; em seguida, inicia um loop, que atribui um valor booleano aleatório a e verifica se é verdadeiro; em caso positivo, ele gerará "Cara!" e incrementará heads_count; em caso negativo, ele gerará "Coroa!" e sairá do loop; 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');

SAIR

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

O exemplo a seguir declara duas variáveis, heads e heads_count; em seguida, inicia um loop, que atribui um valor booleano aleatório a e verifica se é verdadeiro; em caso positivo, ele gerará "Cara!", incrementará heads_count e reiniciará o loop, pulando as instruções restantes; em caso negativo, ele gerará "Coroa!" e sairá do loop; 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');

ITERAR

Sinônimo de CONTINUE.

RETORNAR

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. Finalmente, ele retorna o valor de retCode.

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