Criar consultas em sessões

Neste documento, você verá como escrever consultas em uma sessão do BigQuery. Ela é destinada a usuários que já têm uma compreensão geral das sessões do BigQueryBigQuery e sabem como executar consultas em uma sessão.

Uma sessão armazena o estado. O estado criado em uma sessão é mantido e pode ser usado durante toda a sessão. Portanto, se você criar uma tabela temporária em uma entrada de consulta, será possível usá-la em outras entradas de consulta no restante da sessão.

Uma sessão inclui compatibilidade com variáveis de sessão, variáveis do sistema de sessão, consultas com várias instruções e transações de vários extratos.

Antes de concluir estas etapas, verifique se você tem as permissões necessárias para trabalhar em uma sessão.

Usar variáveis do sistema em uma sessão

É possível definir ou recuperar dados no nível da sessão com as seguintes variáveis de sistema:

@@dataset_id: ID do conjunto de dados padrão no projeto atual. As variáveis de sistema @@dataset_project_id e @@dataset_id podem ser definidas e usadas em conjunto.
@@dataset_project_id: o ID do projeto padrão para os conjuntos de dados usados na consulta. Se essa variável do sistema não for configurada ou for definida como NULL, o projeto de execução da consulta será usado. As variáveis de sistema @@dataset_project_id e @@dataset_id podem ser definidas e usadas em conjunto.
@@query_label: o rótulo do job a ser atribuído à sessão. O rótulo pode ser usado em toda a sessão, não apenas em uma consulta específica.
@@session_id: o ID da sessão atual.
@@time_zone: o fuso horário padrão a ser usado em funções SQL dependentes de fuso horário, quando um fuso horário não é especificado como um argumento.

Essas variáveis de sistema podem ser usadas a qualquer momento durante a sessão e estão no escopo da sessão restante. Você não define essas variáveis, mas elas podem receber um novo valor com a instrução SET.

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

Atribuir um rótulo a uma sessão

É possível atribuir um rótulo do job a uma sessão. Quando você faz isso, todas as consultas futuras na sessão são atribuídas ao rótulo. Os rótulos podem ser usados a qualquer momento durante a sessão e estão no escopo da sessão restante. O rótulo do job atribuído será exibido nos registros de auditoria.

Usar variáveis em uma sessão

É possível criar, definir e recuperar dados no nível da sessão com variables. As variáveis podem ser usadas a qualquer momento durante a sessão e estão no escopo da sessão restante.

Para criar uma variável com escopo de sessão, use a instrução DECLARE fora de um bloco BEGIN...END.
Para definir uma variável com escopo de sessão depois de criá-la, use a instrução SET.
Uma variável declarada dentro de um bloco BEGIN...END não é uma variável com escopo na sessão.
Uma variável com escopo de sessão pode ser referenciada dentro de um bloco BEGIN...END.
Uma variável com escopo de sessão pode ser definida dentro de um bloco BEGIN...END.

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

Usar tabelas temporárias em sessões

Uma tabela temporária permite salvar resultados intermediários em uma tabela. Uma tabela temporária está visível no nível da sessão. Portanto, não é necessário salvá-la ou mantê-la em um conjunto de dados. Ela é excluída automaticamente após o encerramento de uma sessão. A cobrança será feita pela armazenamento de tabelas temporárias enquanto a sessão estiver ativa. Para mais informações sobre tabelas temporárias, consulte Trabalhar com consultas de várias instruções.

Usar funções temporárias em sessões

Uma função temporária está visível no nível da sessão. Portanto, não é necessário salvá-la ou mantê-la em um conjunto de dados. Ela é excluída automaticamente após o encerramento de uma sessão.

Trabalhar com consultas de várias instruções em sessões

É possível usar consultas de várias instruções do GoogleSQL em uma sessão. Um script pode incluir tabelas temporárias e variáveis do sistema para cada script. Variáveis de sessão e tabelas temporárias são visíveis para scripts. Todas as variáveis de nível superior declaradas em um script também são variáveis de sessão.

Executar transações de várias instruções de várias consultas em sessões

É possível executar transações de várias instruções em várias consultas em uma sessão. Exemplo:

A consulta a seguir inicia uma transação.

BEGIN TRANSACTION

Dentro da transação, a consulta a seguir cria uma tabela temporária chamada Flights e retorna os dados nessa tabela. Duas instruções estão incluídas na consulta.

CREATE TEMP TABLE Flights(total INT64)  AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;

SELECT * FROM Flights;

A consulta a seguir confirma a transação.

COMMIT

É possível encontrar uma transação ativa que afeta a tabela Flights:

WITH running_transactions AS (
  SELECT DISTINCT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    EXCEPT DISTINCT
    SELECT transaction_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
      creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
      AND statement_type = "COMMIT_TRANSACTION"
      OR statement_type = "ROLLBACK_TRANSACTION"
)
SELECT
  jobs.transaction_id AS transaction_id,
  project_id,
  user_email,
  session_info.session_id,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs, running_transactions
  WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND destination_table = ("Flights")
  AND jobs.transaction_id = running_transactions.transaction_id;

Se você quiser cancelar uma transação em andamento e tiver o papel bigquery.admin, emita uma instrução de reversão usando o ID da sessão associado à transação no Cloud Shell. ou com uma chamada de API. Quando você executa a consulta usando o código da sessão associado à transação, ele é exibido nos resultados.

Sessão de exemplo

Este é um exemplo do fluxo de trabalho da sessão no console do Google Cloud:

No console do Google Cloud, abra uma nova guia do editor e crie uma sessão.

Na guia do editor, adicione a seguinte consulta:

CREATE TEMP TABLE Flights(total INT64)  AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;
SELECT * FROM Flights;

Execute a consulta. Uma tabela temporária chamada Flights é criada, e todos os dados são retornados.
```
+-------+
| total |
+-------+
|    55 |
|    23 |
|     3 |
|    14 |
|    10 |
+-------+
```
Exclua o conteúdo dentro da guia do editor e adicione a seguinte consulta:
```
SELECT * FROM Flights LIMIT 2;
```
Execute a consulta. Os resultados de dois registros são retornados. Mesmo que você tenha excluído a consulta anterior, as informações da consulta serão armazenadas na sessão atual.
```
+-------+
| total |
+-------+
|    55 |
|    23 |
+-------+
```

Exclua o conteúdo dentro da guia do editor e adicione a seguinte consulta:

DECLARE x INT64 DEFAULT 10;

SELECT total * x AS total_a FROM Flights LIMIT 2;

BEGIN
  SET x = 100;
  SELECT total * x AS total_b FROM Flights LIMIT 2;
END;

SELECT total * x AS total_c FROM Flights LIMIT 2;

Execute a consulta. A variável com escopo na sessão x é usada para limitar o número de resultados retornados para a tabela Flights. Observe de perto como o escopo afeta essa variável quando ela é declarada fora de uma instrução BEGIN...END, definida dentro de uma instrução BEGIN...END e, em seguida, referenciada fora do BEGIN...END. novamente.
```
+---------+
| total_a |
+---------+
|     550 |
|     230 |
+---------+

+---------+
| total_b |
+---------+
|    5500 |
|    2300 |
+---------+

+---------+
| total_c |
+---------+
|    5500 |
|    2300 |
+---------+
```