Instructions de scripts en SQL standard

Les scripts vous permettent d'envoyer plusieurs instructions à BigQuery en une seule requête. Les scripts peuvent utiliser des variables ainsi que des instructions de flux de contrôle, telles que IF et WHILE. Par exemple, vous pouvez déclarer une variable, lui attribuer une valeur, puis la référencer dans une troisième instruction.

Pour en savoir plus sur les règles de variables et de tables temporaires, le débogage et l'écriture de procédures de scripts, consultez la page Scripts et procédures stockées.

DECLARE

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

variable_name doit être un identifiant valide, et variable_type est n'importe quel type BigQuery.

Description

Déclare une variable du type spécifié. Si la clause DEFAULT est spécifiée, la variable est initialisée avec la valeur de l'expression. Si aucune clause DEFAULT n'est présente, la variable est initialisée avec la valeur NULL.

Si [variable_type] est omis, vous devez spécifier une clause DEFAULT. Le type de la variable sera déduit du type d'expression spécifié dans la clause DEFAULT.

Les déclarations de variables doivent apparaître au début d'un script, avant toute autre instruction, ou au début d'un bloc déclaré avec BEGIN. Les noms de variables ne sont pas sensibles à la casse.

Une seule instruction DECLARE peut contenir plusieurs noms de variables, mais un seul variable_type et une seule expression.

Vous ne pouvez pas déclarer une variable portant le même nom qu'une variable déclarée précédemment dans le bloc actuel ou dans un bloc conteneur.

Si la clause DEFAULT est présente, la valeur de l'expression doit pouvoir être convertie par coercition dans le type spécifié. L'expression peut référencer d'autres variables déclarées précédemment dans le même bloc ou dans un bloc conteneur.

La taille maximale d'une variable est de 1 Mo, et la taille maximale de toutes les variables utilisées dans un script est de 10 Mo.

Exemples

L'exemple suivant initialise la variable x en tant que INT64 avec la valeur NULL.

DECLARE x INT64;

L'exemple suivant initialise la variable d en tant que DATE avec la valeur de la date actuelle.

DECLARE d DATE DEFAULT CURRENT_DATE();

L'exemple suivant initialise les variables x, y et z en tant que INT64 avec la valeur 0.

DECLARE x, y, z INT64 DEFAULT 0;

L'exemple suivant déclare une variable nommée item correspondant à un élément arbitraire de la table dataset1.products. Le type de item est déduit du schéma de la table.

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

SET

Syntaxe

SET name = expression;

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

Description

Définit une variable comme ayant la valeur de l'expression fournie, ou définit plusieurs variables en même temps en fonction du résultat de plusieurs expressions.

L'instruction SET peut apparaître n'importe où dans le corps d'un script.

Exemples

L'exemple suivant définit la variable x comme ayant la valeur 5.

SET x = 5;

L'exemple suivant définit la variable a comme ayant la valeur 4, b comme ayant la valeur "foo", et la variable c comme ayant la valeur false.

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

L'exemple suivant attribue le résultat d'une requête à plusieurs variables. Tout d'abord, il déclare deux variables, target_word et corpus_count. Ensuite, il attribue les résultats d'une requête SELECT AS STRUCT aux deux variables. Le résultat de la requête est une seule ligne contenant un type STRUCT avec deux champs. Le premier élément est attribué à la première variable, et le second élément à la seconde variable.

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;

Cette liste d'instructions génère la chaîne suivante :

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

Syntaxe

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

Description

Exécute une instruction SQL dynamique à la volée.

• sql_expression : Représente une instruction de requête, une expression que vous pouvez utiliser sur une instruction de requête, une instruction LDD, ou une seule instruction LMD. Il ne peut pas s'agir d'une instruction de contrôle telle que IF.
• expression : peut être une fonction, une expression conditionnelle ou une sous-requête d'expression.
• query_statement : représente une instruction SQL autonome valide à exécuter. Si cela renvoie une valeur, la clause INTO doit contenir des valeurs du même type. Vous pouvez accéder aux variables système et aux valeurs présentes dans la clause USING. Toutes les autres variables locales et tous les paramètres de requête ne sont pas exposés à l'instruction de requête.
• Clause INTO : après l'exécution de l'expression SQL, vous pouvez stocker les résultats dans une ou plusieurs variables à l'aide de la clause INTO.
• Clause USING : avant d'exécuter votre expression SQL, vous pouvez transmettre un ou plusieurs identifiants de la clause USING dans l'expression SQL. Ces identifiants fonctionnent de la même manière que les paramètres de requête, en exposant des valeurs à l'instruction de requête. Un identifiant peut être une variable ou une valeur.

Vous pouvez inclure ces espaces réservés dans query_statement pour les identifiants référencés dans la clause USING :

• ? : la valeur de cet espace réservé est liée à un identifiant dans la clause USING par index.

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
  

• @identifier : la valeur de cet espace réservé est liée à un identifiant dans la clause USING par son nom. Cette syntaxe est identique à la syntaxe du paramètre de requête.

  -- y = 1 * (3 + 2) = 5
  EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
  

Voici quelques remarques supplémentaires sur le comportement de l'instruction EXECUTE IMMEDIATE :

• EXECUTE IMMEDIATE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que EXECUTE IMMEDIATE ne peut pas être imbriqué dans une autre instruction EXECUTE IMMEDIATE.
• Si une instruction EXECUTE IMMEDIATE renvoie des résultats, ceux-ci deviennent le résultat de l'instruction complète et toutes les variables système appropriées sont mises à jour.
• La même variable peut apparaître dans les clauses INTO et USING.
• query_statement peut contenir une seule instruction analysée contenant d'autres instructions (par exemple, BEGIN… END).
• Si aucune ligne n'est renvoyée à partir de query_statement, y compris à partir des tables de valeurs à zéro ligne, toutes les variables de la clause INTO sont définies sur NULL.
• Si une ligne est renvoyée à partir de query_statement, y compris à partir de tables de valeurs à zéro ligne, les valeurs sont attribuées par position, et non par nom de variable.
• Si une clause INTO est présente, une erreur est générée si vous essayez de renvoyer plusieurs lignes de query_statement.

Exemples

Dans cet exemple, nous créons une table de livres et y ajoutons des données. Notez les différentes façons de référencer des variables, d'enregistrer des valeurs dans des variables et d'utiliser des expressions.

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared at the top of this
-- script and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT("INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)");

-- save the publish date of the first book, Hamlet, to a variable called first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

BEGIN...END

Syntaxe

BEGIN
  sql_statement_list
END;

Description

BEGIN démarre un bloc d'instructions où les variables déclarées n'existent que jusqu'à l'instruction END correspondante. sql_statement_list correspond à une liste de zéro, une ou plusieurs instructions SQL se terminant par un point-virgule.

Les déclarations de variables doivent apparaître au début du bloc, avant tout autre type d'instructions. Les variables déclarées dans un bloc ne peuvent être référencées que dans ce bloc et dans les blocs imbriqués. Vous ne pouvez pas déclarer une variable portant le même nom qu'une variable déclarée dans le même bloc ou dans un bloc externe.

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

BEGIN/END ne peut pas être exécuté dynamiquement en tant qu'élément imbriqué.

Exemples

L'exemple suivant déclare une variable x avec une valeur par défaut de 10, puis déclenche un bloc dans lequel la valeur x (10) est attribuée à la variable y, puis renvoie cette valeur. Ensuite, l'instruction END met fin au bloc, ce qui met fin au champ d'application de la variable y ; enfin, la valeur x est renvoyée.

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

BEGIN...EXCEPTION...END

Syntaxe

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Description

BEGIN...EXCEPTION exécute un bloc d'instructions. Si l'une des instructions rencontre une erreur, le script ignore le reste du bloc et exécute les instructions de la clause EXCEPTION.

Dans la clause EXCEPTION, vous pouvez accéder aux détails de l'erreur à l'aide des variables système EXCEPTION suivantes :

Nom	Type	Description
@@error.formatted_stack_trace	STRING	Contenu de @@error.stack_trace exprimé sous forme de chaîne lisible. Cette valeur est destinée à être affichée et peut être modifiée sans préavis. L'accès automatique à la trace de la pile d'une erreur doit utiliser @@error.stack_trace à la place.
@@error.message	STRING	Indique un message d'erreur lisible par l'utilisateur.
@@error.stack_trace	Voir 1.	Chaque élément du tableau correspond à un appel d'instruction ou de procédure exécuté au moment de l'erreur, la trace de la pile en cours d'exécution apparaissant en premier. La signification de chaque champ est définie comme suit : line/column : spécifie la ligne et le numéro de colonne de la trace de la pile, en commençant par 1. Si la trace se trouve dans un corps de procédure, alors line 1 column 1 correspond au mot clé BEGIN au début du corps de la procédure. location : si la trace se trouve dans un corps de procédure, ce champ spécifie le nom complet de la procédure, au format [project_name].[dataset_name].[procedure_name]. Si la trace fait référence à un emplacement dans un script de premier niveau, ce champ est NULL. filename : réservé pour une utilisation ultérieure. Toujours NULL.
@@error.statement_text	STRING	Spécifie le texte de l'instruction à l'origine de l'erreur.

¹ Le type de @@error.stack_trace est ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

BigQuery se réservant le droit de réviser les messages d'erreur à tout moment, les utilisateurs de @@error.message ne doivent pas s'appuyer sur des messages d'erreur identiques ou suivant un schéma particulier. Pour obtenir des informations sur l'emplacement des erreurs, évitez d'extraire du texte à partir du message d'erreur. Utilisez plutôt @@error.stack_trace et @@error.statement_text.

Pour gérer les exceptions qui sont générées (mais non traitées) par un gestionnaire d'exceptions, vous devez encapsuler le bloc dans un bloc externe avec un gestionnaire d'exceptions distinct.

L'exemple suivant montre comment utiliser un bloc externe avec un gestionnaire d'exceptions distinct :

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;

Les blocs BEGIN...EXCEPTION acceptent également les instructions DECLARE, comme tous les autres blocs BEGIN. Les variables déclarées dans un bloc BEGIN ne sont valides que dans la section BEGIN, mais ne peuvent pas être utilisées dans le gestionnaire d'exceptions de ce bloc.

Exemples

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;

Dans cet exemple, lorsque l'erreur de division par zéro se produit, au lieu d'arrêter l'ensemble du script, BigQuery arrête dataset1.proc1() et dataset1.proc2(), puis exécute l'instruction SELECT dans le gestionnaire d'exceptions. Lorsque le gestionnaire d'exceptions s'exécute, les variables ont les valeurs suivantes :

Variable	Valeur
@@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]"

IF

Syntaxe

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

Description

Exécute le premier bloc sql_statement_list où la condition est "true", ou le bloc ELSE sql_statement_list facultatif si aucune condition ne correspond.

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

IF ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que IF ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant déclare une variable INT64 target_product_id avec une valeur par défaut de 103. Ensuite, il vérifie si la table dataset.products contient une ligne où la colonne product_id correspond à la valeur de target_product_id. Si c'est le cas, il génère une chaîne indiquant que le produit a été trouvé, ainsi que la valeur de default_product_id. Si ce n'est pas le cas, il génère une chaîne indiquant que le produit n'a pas été trouvé, ainsi que la valeur 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;

Boucles

LOOP

Syntaxe

LOOP
  sql_statement_list
END LOOP;

Description

Exécute sql_statement_list jusqu'à ce qu'une instruction BREAK ou LEAVE quitte la boucle. sql_statement_list correspond à une liste de zéro, une ou plusieurs instructions SQL se terminant par un point-virgule.

LOOP ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que LOOP ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant déclare une variable x avec une valeur par défaut de 0. Il utilise ensuite l'instruction LOOP pour créer une boucle qui s'exécute jusqu'à ce que la variable x soit supérieure ou égale à 10. Après la sortie de la boucle, l'exemple génère la valeur de x.

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

Cet exemple génère ce qui suit :

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

WHILE

Syntaxe

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Le niveau d'imbrication maximal est de 50 pour les blocs et les instructions conditionnelles telles que BEGIN/END, IF/ELSE/END IF et WHILE/END WHILE.

Description

Si boolean_expression a la valeur "true", exécute sql_statement_list. boolean_expression est évalué pour chaque itération de la boucle.

WHILE ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que WHILE ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

BREAK

Syntaxe

BREAK;

Description

Quitte la boucle actuelle.

Vous ne pouvez pas utiliser BREAK en dehors d'une boucle.

Exemples

L'exemple suivant déclare deux variables, heads et heads_count. Ensuite, il démarre une boucle qui attribue une valeur booléenne aléatoire à heads, puis il vérifie si heads a la valeur "true". Si c'est le cas, il affiche "Heads!" (face) et incrémente heads_count. Si ce n'est pas le cas, il affiche "Tails!" (pile) et quitte la boucle. Enfin, il génère une chaîne indiquant le nombre de fois où le tirage à pile ou face a donné le résultat "heads".

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

Synonyme de BREAK.

CONTINUER

Syntaxe

CONTINUE;

Description

Ignore toutes les instructions suivantes dans la boucle actuelle et revient au début de la boucle.

Vous ne pouvez pas utiliser CONTINUE en dehors d'une boucle.

Vous pouvez utiliser un libellé avec cette instruction. Pour en savoir plus, consultez la section Libellés.

Exemples

L'exemple suivant déclare deux variables, heads et heads_count. Ensuite, il démarre une boucle qui attribue une valeur booléenne aléatoire à heads, puis il vérifie si heads a la valeur "true". Si c'est le cas, il affiche "Heads!" (face), incrémente heads_count et redémarre la boucle en ignorant les instructions restantes. Si ce n'est pas le cas, il affiche "Tails!" (pile) et quitte la boucle. Enfin, il génère une chaîne indiquant le nombre de fois où le tirage à pile ou face a donné le résultat "heads".

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

Synonyme de CONTINUE.

Transactions

Bêta

Ce produit ou cette fonctionnalité est couvert par les Conditions des offres pré-DG des conditions d'utilisation de Google Cloud Platform. Les fonctionnalités et produits pré-DG sont susceptibles de présenter une compatibilité limitée, et les modifications apportées à ces fonctionnalités ou produits peuvent ne pas être compatibles avec d'autres versions pré-DG. Pour en savoir plus, consultez les descriptions des étapes de lancement.

BEGIN TRANSACTION

Syntaxe

BEGIN [TRANSACTION];

Description

Lance une transaction.

La transaction se termine lorsqu'une instruction COMMIT TRANSACTION ou ROLLBACK TRANSACTION est atteinte. Si le script se termine avant d'atteindre l'une de ces instructions, un rollback automatique est effectué.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

L'exemple suivant effectue une transaction qui sélectionne les lignes d'une table existante dans une table temporaire, supprime ces lignes de la table d'origine, puis fusionne la table temporaire dans une autre table.

BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;

COMMIT TRANSACTION

Syntaxe

COMMIT [TRANSACTION];

Description

Effectue un commit d'une transaction ouverte. Si aucune transaction ouverte n'est en cours, l'instruction échoue.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Syntaxe

ROLLBACK [TRANSACTION];

Description

Effectue un rollback d'une transaction ouverte. S'il n'existe aucune transaction ouverte en cours, l'instruction échoue.

Pour en savoir plus sur les transactions dans BigQuery, consultez la section Transactions contenant plusieurs instructions.

Exemple

L'exemple suivant effectue le rollback d'une transaction si une erreur se produit pendant celle-ci. Pour illustrer la logique, l'exemple déclenche une erreur de division par zéro après l'insertion d'une ligne dans une table. Une fois ces instructions exécutées, la table n'est pas affectée.

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

RAISE

Syntaxe

RAISE [USING MESSAGE = message];

Description

Génère une erreur en utilisant éventuellement le message d'erreur spécifié lorsque USING MESSAGE = message est fourni.

Lorsque USING MESSAGE n'est pas fourni

L'instruction RAISE ne doit être utilisée que dans une clause EXCEPTION. L'instruction RAISE génère à nouveau l'exception qui a été interceptée et conserve la trace de la pile d'origine.

Lorsque USING MESSAGE est fourni

Si l'instruction RAISE est contenue dans la section BEGIN d'un bloc BEGIN...EXCEPTION :

• Le gestionnaire est appelé.

• La valeur de @@error.message correspond exactement à la chaîne message fournie (pouvant être NULL si message est NULL).

• La trace de la pile sera définie sur l'instruction RAISE.

Si l'instruction RAISE n'est pas contenue dans la section BEGIN d'un bloc BEGIN...EXCEPTION, l'instruction RAISE arrête l'exécution du script et fournit le message d'erreur.

RETURN

Dans un script BigQuery, RETURN met fin à l'exécution du script actuel.

CALL

Syntaxe

CALL procedure_name (procedure_argument[, …])

Description

Appelle une procédure avec une liste d'arguments. procedure_argument peut être une variable ou une expression. Pour les arguments OUT ou INOUT, une variable transmise en tant qu'argument doit posséder le type BigQuery approprié.

La même variable peut ne pas apparaître plusieurs fois sous la forme d'un argument OUT ou INOUT dans la liste d'arguments de la procédure.

La profondeur maximale des appels de procédure est de 50 frames.

CALL ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué. Cela signifie que CALL ne peut pas être imbriqué dans une instruction EXECUTE IMMEDIATE.

Exemples

L'exemple suivant déclare une variable retCode. Ensuite, il appelle la procédure updateSomeTables dans l'ensemble de données myDataset, en transmettant les arguments 'someAccountId' et retCode. Enfin, il renvoie la valeur de retCode.

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

Variables système

Vous pouvez vérifier des informations lors de l'exécution d'un script à l'aide des variables système.

Nom	Type	Description
@@current_job_id	STRING	(Lecture seule) ID de la tâche en cours d'exécution. Dans le contexte d'un script, cette variable renvoie la tâche responsable de l'instruction en cours et non pas l'ensemble du script.
@@dataset_id	STRING	ID de l'ensemble de données par défaut dans le projet actuel. Il est utilisé lorsqu'un ensemble de données n'est pas spécifié pour un projet dans la requête. Vous pouvez utiliser l'instruction SET pour attribuer @@dataset_id à un autre ID d'ensemble de données dans le projet en cours.
@@last_job_id	STRING	(Lecture seule) ID de la tâche la plus récente à exécuter dans le script actuel, à l'exclusion de la tâche en cours. Si le script contient des instructions CALL, cette tâche peut provenir d'une autre procédure.
@@project_id	STRING	(Lecture seule) ID du projet utilisé pour exécuter la requête actuelle. Dans le contexte d'une procédure, @@project_id fait référence au projet qui exécute le script et non au projet propriétaire de la procédure.
@@row_count	INT64	(Lecture seule) Si cette variable est utilisée dans un script et que l'instruction de script précédente est une instruction LMD, alors elle spécifie le nombre de lignes modifiées, insérées ou supprimées suite à cette instruction LMD. Si l'instruction précédente est une instruction MERGE, @@row_count représente le nombre total cumulé de lignes insérées, supprimées et effacées. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.bytes_billed	INT64	(Lecture seule) Nombre total d'octets facturés jusqu'à présent dans la tâche de script en cours d'exécution. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.bytes_processed	INT64	(Lecture seule) Nombre total d'octets traités jusqu'à présent dans la tâche de script en cours d'exécution. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.creation_time	TIMESTAMP	(Lecture seule) Heure de création de la tâche de script en cours d'exécution. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.job_id	STRING	(Lecture seule) ID de la tâche de script en cours d'exécution. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.num_child_jobs	INT64	(Lecture seule) Nombre de tâches enfants actuellement terminées. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@script.slot_ms	INT64	(Lecture seule) Nombre de millisecondes d'emplacement utilisées jusqu'à présent par le script. Cette valeur est NULL si cette variable n'est pas utilisée dans un script.
@@time_zone	STRING	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. @@time_zone peut être modifié en utilisant une instruction SET pour n'importe quel nom de fuseau horaire valide. Au début de chaque script, @@time_zone commence par "UTC".

Remarque : Pour des raisons de rétrocompatibilité, les expressions contenues dans une clause OPTIONS ou FOR SYSTEM TIME AS OF utilisent par défaut le fuseau horaire America/Los_Angeles, tandis que toutes les autres expressions de type date/heure utilisent le fuseau horaire UTC par défaut. Si @@time_zone a été précédemment défini dans le script, le fuseau horaire choisi s'applique à toutes les expressions de type date/heure, y compris les clauses OPTIONS et FOR SYSTEM TIME AS OF.

Outre les variables système présentées ci-dessus, vous pouvez utiliser des variables système EXCEPTION pendant l'exécution d'un script. Les variables système EXCEPTION ont la valeur NULL si elles ne sont pas utilisées dans un gestionnaire d'exceptions. Les variables système suivantes sont des variables système EXCEPTION.

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

Pour en savoir plus sur les variables système EXCEPTION, consultez la section BEGIN...EXCEPTION.

Procédures système

BigQuery accepte les procédures système suivantes :

BQ.JOBS.CANCEL

Syntaxe

CALL BQ.JOBS.CANCEL(job);

Description

Annule une tâche en cours d'exécution.

Spécifiez la tâche en tant que chaîne au format '[project_id.]job_id'. Si vous exécutez cette procédure système à partir d'un projet différent de celui de la tâche, vous devez inclure l'ID du projet. Vous devez exécuter la procédure dans la même région que la tâche.

Pour en savoir plus, consultez la page Annuler une tâche.

BQ.REFRESH_MATERIALIZED_VIEW

Syntaxe

CALL BQ.REFRESH_MATERIALIZED_VIEW(view_name);

Description

Actualise une vue matérialisée.

Spécifiez le nom de la vue matérialisée sous forme de chaîne au format '[project_id.]dataset.table'. Si vous exécutez cette procédure système à partir d'un projet différent de celui de la vue matérialisée, vous devez inclure l'ID du projet.

Pour en savoir plus, consultez la section Actualisation manuelle.

Autorisations

L'autorisation d'accès à une table, un modèle ou une autre ressource est vérifiée au moment de l'exécution. Si une instruction n'est pas exécutée ou qu'une expression n'est pas évaluée, BigQuery ne vérifie pas si l'utilisateur exécutant le script a accès aux ressources référencées.

Dans un script, les autorisations de chaque expression ou instruction sont validées séparément. Prenons l'exemple du script suivant :

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

Si l'utilisateur exécutant le script a accès à table1, mais pas à table2, la première requête aboutit et la deuxième échoue. La tâche de script échoue également.

Contraintes de sécurité

Le langage SQL dynamique est pratique, mais peut offrir de nouvelles opportunités d'utilisation abusive. Par exemple, l'exécution de la requête suivante représente une menace potentielle pour la sécurité, car le paramètre de table peut être mal filtré, autoriser l'accès à et être exécuté sur des tables non souhaitées.

EXECUTE IMMEDIATE CONCAT(‘SELECT * FROM ’, @employee_table);

Pour éviter d'exposer ou de divulguer des données sensibles dans une table ou d'exécuter des commandes telles que DROP TABLE pour supprimer des données dans une table, le script SQL dynamique de BigQuery accepte plusieurs mesures de sécurité pour réduire l'exposition aux attaques d'injection SQL, y compris :

• Vous ne pouvez pas exécuter plusieurs instructions SQL intégrées dans les paramètres transmis en SQL dynamique.
• Les commandes suivantes ne peuvent pas être exécutées dynamiquement : BEGIN/END, CALL, IF, LOOP, WHILE et EXECUTE IMMEDIATE.

Limites du champ de configuration

Les champs de configuration de requête suivants ne peuvent pas être définis pour les scripts :

• clustering
• create_disposition
• destination_table
• destination_encryption_configuration
• range_partitioning
• schema_update_options
• time_partitioning
• user_defined_function_resources
• write_disposition