Escrever consultas em sessões

Este documento descreve como escrever consultas numa sessão do BigQuery. Destina-se a utilizadores que já têm uma compreensão geral das sessões do BigQuery e sabem como executar consultas numa sessão.

Uma sessão armazena o estado. O estado criado numa sessão é mantido e utilizável durante toda a sessão. Assim, se criar uma tabela temporária numa entrada de consulta, pode usar essa tabela temporária noutras entradas de consulta durante o resto da sessão.

Uma sessão inclui suporte para variáveis de sessão, variáveis de sistema de sessão, consultas com várias declarações e transações com várias declarações.

Antes de concluir estes passos, certifique-se de que tem as autorizações necessárias para trabalhar numa sessão.

Use variáveis do sistema numa sessão

Pode definir ou obter dados ao nível da sessão com as seguintes variáveis do sistema:

  • @@dataset_id: o ID do conjunto de dados predefinido 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 predefinido para conjuntos de dados que são usados na consulta. Se esta variável do sistema não estiver definida ou estiver definida como NULL, é usado o projeto de execução de consultas. As variáveis do sistema @@dataset_project_id e @@dataset_id podem ser definidas e usadas em conjunto.
  • @@query_label: a etiqueta de tarefa a atribuir à sessão. A etiqueta pode ser usada durante toda a sessão, não apenas para uma consulta específica na sessão.
  • @@session_id: o ID da sessão atual.
  • @@time_zone: o fuso horário predefinido a usar em funções SQL dependentes do fuso horário, quando não é especificado um fuso horário como argumento.

Estas variáveis do sistema podem ser usadas em qualquer altura durante a sessão e estão no âmbito da sessão restante. Não define estas variáveis, mas pode atribuir-lhes um novo valor com a declaração SET.

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

Atribua uma etiqueta a uma sessão

Pode atribuir uma etiqueta de trabalho a uma sessão. Quando o faz, todas as consultas futuras na sessão são atribuídas à etiqueta. As etiquetas podem ser usadas em qualquer altura durante a sessão e estão no âmbito da sessão restante. A etiqueta de tarefa que atribui é apresentada nos registos de auditoria.

Use variáveis numa sessão

Pode criar, definir e obter dados ao nível da sessão com variáveis. As variáveis podem ser usadas em qualquer altura durante a sessão e estão no âmbito da sessão restante.

  • Para criar uma variável ao nível da sessão, use a declaração DECLARE fora de um bloco BEGIN...END.
  • Para definir uma variável ao nível da sessão depois de ter sido criada, use a declaração SET.
  • Uma variável declarada no interior de um bloco BEGIN...END não é uma variável com âmbito de sessão.
  • Pode fazer referência a uma variável ao nível da sessão dentro de um bloco BEGIN...END.
  • Uma variável ao nível da sessão pode ser definida dentro de um bloco BEGIN...END.

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

Use tabelas temporárias em sessões

Uma tabela temporária permite-lhe guardar resultados intermédios numa tabela. Uma tabela temporária é visível ao nível da sessão, pelo que não tem de a guardar nem manter num conjunto de dados. É eliminada automaticamente após o término de uma sessão. É-lhe cobrado o armazenamento de tabelas temporárias enquanto a sessão estiver ativa. Para mais informações, consulte o artigo Use tabelas temporárias numa consulta com várias declarações.

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

Uma função temporária ou uma função de agregação temporária é visível ao nível da sessão, pelo que não precisa de a guardar nem a manter num conjunto de dados. É eliminada automaticamente após o término de uma sessão.

Trabalhe com consultas de várias declarações em sessões

Pode usar consultas com várias declarações GoogleSQL numa sessão. Um script pode incluir tabelas temporárias e variáveis do sistema para cada script. As variáveis de sessão e as tabelas temporárias são visíveis para os scripts. Todas as variáveis de nível superior declaradas num script também são variáveis de sessão.

Execute transações com várias consultas e várias declarações em sessões

Pode executar transações com várias declarações em várias consultas numa sessão. Por exemplo:

A seguinte consulta inicia uma transação.

BEGIN TRANSACTION

Dentro da transação, a seguinte consulta cria uma tabela temporária denominada Flights e, em seguida, devolve os dados nesta tabela. São incluídas duas declarações na consulta.

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

SELECT * FROM Flights;

A seguinte consulta confirma a transação.

COMMIT

Pode 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 quiser cancelar uma transação em curso e tiver a função bigquery.admin, pode emitir uma declaração de reversão, usando o ID da sessão associado à transação no Cloud Shell ou com uma chamada API. Quando executa a consulta, usando o ID da sessão associado à transação, o ID da sessão é apresentado nos resultados.

Exemplo de sessão

Segue-se um exemplo do fluxo de trabalho da sessão na Trusted Cloud consola:

  1. Na Trusted Cloud consola, abra um novo separador do editor e crie uma sessão.

  2. No separador 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;

  3. Execute a consulta. É criada uma tabela temporária denominada Flights e todos os dados são devolvidos.

    +-------+
| total |
+-------+
|    55 |
|    23 |
|     3 |
|    14 |
|    10 |
+-------+

  4. Elimine o conteúdo no separador do editor e adicione a seguinte consulta:

    SELECT * FROM Flights LIMIT 2;

  5. Execute a consulta. São devolvidos os resultados de dois registos. Embora tenha eliminado a consulta anterior, as informações da consulta são armazenadas na sessão atual.

    +-------+
| total |
+-------+
|    55 |
|    23 |
+-------+

  6. Elimine o conteúdo no separador 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;

  7. Execute a consulta. A variável ao nível da sessão x é usada para limitar o número de resultados devolvidos para a tabela Flights. Analise atentamente a forma como o âmbito afeta esta variável quando é declarada fora de uma declaração BEGIN...END, definida numa declaração BEGIN...END e, em seguida, referenciada novamente fora da declaração BEGIN...END.

    +---------+
| total_a |
+---------+
|     550 |
|     230 |
+---------+

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

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

  8. Elimine o conteúdo no separador do editor e adicione a seguinte consulta:

    SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS default_time_zone;

SET @@time_zone = "America/Los_Angeles";

SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS new_time_zone;

  9. Execute a consulta. A variável de sistema ao nível da sessão @@time_zone é usada para atribuir um fuso horário a uma data/hora. A primeira declaração devolve uma data/hora com o fuso horário predefinido (neste exemplo, UTC). A declaração seguinte atribui @@time_zone a um novo valor. A terceira declaração devolve uma data/hora com o novo fuso horário.

    +-------------------------------+
| default_time_zone             |
+-------------------------------+
| 2008-12-20 15:30:00+00        |
+-------------------------------+

+-------------------------------+
| new_time_zone                 |
+-------------------------------+
| 2008-12-20 07:30:00-08        |
+-------------------------------+

O que se segue?