Écrire des requêtes dans des sessions

Ce document explique comment écrire des requêtes dans une session BigQuery. Il est destiné aux utilisateurs qui ont déjà une connaissance générale des sessions BigQuery et qui savent exécuter des requêtes dans une session.

Une session stocke l'état. L'état créé dans une session est conservé et utilisable pendant toute la durée de la session. Ainsi, si vous créez une table temporaire dans une entrée de requête, vous pouvez utiliser cette table temporaire dans d'autres entrées de requête pour le reste de la session.

Une session est compatible avec les variables de session, les variables du système de session, les requêtes à plusieurs instructions et les transactions à plusieurs instructions.

Avant de suivre ces étapes, assurez-vous de disposer des autorisations nécessaires pour effectuer une session.

Utiliser des variables système dans une session

Vous pouvez définir ou récupérer des données au niveau de la session à l'aide des variables système suivantes:

@@dataset_id : ID de l'ensemble de données par défaut dans le projet actuel. Les variables système @@dataset_project_id et @@dataset_id peuvent être définies et utilisées ensemble.
@@dataset_project_id : ID du projet par défaut pour les ensembles de données utilisés dans la requête. Si cette variable système n'est pas définie ou si elle est définie sur NULL, le projet exécutant la requête est utilisé. Les variables système @@dataset_project_id et @@dataset_id peuvent être définies et utilisées ensemble.
@@query_label : libellé de tâche à attribuer à la session. Le libellé peut être utilisé tout au long de la session, et pas seulement pour une requête donnée de la session.
@@session_id : ID de la session en cours.
@@time_zone : Fuseau horaire par défaut à utiliser dans les fonctions SQL dépendantes du fuseau horaire, lorsqu'un fuseau horaire explicite n'est pas spécifié en tant qu'argument.

Ces variables système peuvent être utilisées à tout moment au cours de la session et sont dans le champ d'application de la session restante. Vous ne définissez pas ces variables, mais une nouvelle valeur peut leur être attribuée avec l'instruction SET.

La taille maximale d'une variable dans une session est de 1 Mo et la taille maximale de toutes les variables d'une session est de 10 Mo.

Attribuer un libellé à une session

Vous pouvez attribuer un libellé de tâche à une session. Dans ce cas, toutes les futures requêtes de la session seront attribuées au libellé. Les libellés peuvent être utilisés à tout moment pendant la session et entrent dans le champ d'application de la session restante. Le libellé de tâche que vous attribuez apparaîtra dans les journaux d'audit.

Utiliser des variables dans une session

Vous pouvez créer, définir et récupérer des données au niveau de la session à l'aide de variables. Les variables peuvent être utilisées à tout moment pendant la session et entrent dans le champ d'application de la session restante.

Pour créer une variable à l'échelle d'une session, utilisez l'instruction DECLARE en dehors d'un bloc BEGIN...END.
Pour définir une variable à l'échelle d'une session après sa création, utilisez l'instruction SET.
Une variable déclarée dans un bloc BEGIN...END n'est pas une variable à l'échelle d'une session.
Une variable à l'échelle d'une session peut être référencée dans un bloc BEGIN...END.
Une variable à l'échelle d'une session peut être définie dans un bloc BEGIN...END.

La taille maximale d'une variable dans une session est de 1 Mo et la taille maximale de toutes les variables d'une session est de 10 Mo.

Utiliser des tables temporaires dans les sessions

Une table temporaire vous permet d'enregistrer les résultats intermédiaires dans une table. Une table temporaire est visible au niveau de la session. Vous n'avez donc pas besoin de l'enregistrer ni de la conserver dans un ensemble de données. Elle est automatiquement supprimée à la fin d'une session. Le stockage de tables temporaires vous est facturé lorsque la session est active. Pour en savoir plus sur les tables temporaires, consultez Utiliser des requêtes à plusieurs instructions.

Utiliser des fonctions temporaires dans les sessions

Une fonction temporaire est visible au niveau de la session. Vous n'avez donc pas besoin de l'enregistrer ni de la conserver dans un ensemble de données. Elle est automatiquement supprimée à la fin d'une session.

Utiliser des requêtes à plusieurs instructions dans des sessions

Vous pouvez utiliser des requêtes à plusieurs instructions GoogleSQL au cours d'une session. Un script peut inclure des tables temporaires et des variables système pour chaque script. Les variables de session et les tables temporaires sont visibles par les scripts. Toutes les variables de premier niveau déclarées dans un script sont également des variables de session.

Exécuter des transactions multi-instructions multi-requêtes dans les sessions

Vous pouvez exécuter des transactions à plusieurs instructions sur plusieurs requêtes au cours d'une session. Exemple :

La requête suivante commence une transaction.

BEGIN TRANSACTION

Dans la transaction, la requête suivante crée une table temporaire appelée Flights, puis renvoie les données de cette table. Deux instructions sont incluses dans la requête.

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

SELECT * FROM Flights;

La requête suivante valide la transaction.

COMMIT

Vous pouvez trouver une transaction active qui affecte la table 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;

Si vous souhaitez annuler une transaction en cours et que vous disposez du rôle bigquery.admin, vous pouvez émettre une instruction de restauration en utilisant l'ID de session associé à la transaction dans Cloud Shell. ou avec un appel d'API. Lorsque vous exécutez la requête, en utilisant l'ID de session associé à la transaction, l'ID de session est affiché dans les résultats.

Session d'exemple

Voici un exemple de workflow de session dans la console Google Cloud :

Dans la console Google Cloud, ouvrez un nouvel onglet d'éditeur et créez une session.

Dans l'onglet de l'éditeur, ajoutez la requête suivante:

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

Exécutez la requête. Une table temporaire appelée Flights est créée et toutes les données sont renvoyées.
```
+-------+
| total |
+-------+
|    55 |
|    23 |
|     3 |
|    14 |
|    10 |
+-------+
```
Supprimez le contenu dans l'onglet de l'éditeur et ajoutez la requête suivante:
```
SELECT * FROM Flights LIMIT 2;
```
Exécutez la requête. Les résultats de deux enregistrements sont renvoyés. Même si vous avez supprimé la requête précédente, les informations de la requête sont stockées dans la session en cours.
```
+-------+
| total |
+-------+
|    55 |
|    23 |
+-------+
```

Supprimez le contenu dans l'onglet de l'éditeur et ajoutez la requête suivante:

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;

Exécutez la requête. La variable définie au niveau de la session x est utilisée pour limiter le nombre de résultats renvoyés pour la table Flights. Examinez attentivement l'incidence de la portée de cette variable lorsqu'elle est déclarée en dehors d'une instruction BEGIN...END, définie dans une instruction BEGIN...END, puis référencée à nouveau en dehors de l'instruction BEGIN...END.
```
+---------+
| total_a |
+---------+
|     550 |
|     230 |
+---------+

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

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