Fonctionnalité de scripts de BigQuery
La fonctionnalité de scripts de BigQuery permet d'envoyer plusieurs instructions à BigQuery dans une seule requête, d'utiliser des variables et d'utiliser 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.
Dans BigQuery, un script est une liste d'instructions SQL qui doivent être exécutées dans l'ordre. Une liste d'instructions SQL est une liste d'instructions BigQuery valides, séparées par un point-virgule.
Exemple :
-- 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 );
Dans BigQuery, les scripts sont exécutés à l'aide de jobs.insert
, comme n'importe quelle autre requête, en spécifiant le script à plusieurs instructions comme texte de requête. Lorsqu'un script s'exécute, des tâches supplémentaires, appelées tâches enfants, sont créées pour chaque instruction du script. Vous pouvez répertorier les tâches enfants d'un script en appelant la méthode jobs.list
et en transmettant l'ID de tâche du script en tant que paramètre parentJobId
.
L'appel de la fonction jobs.getQueryResults
dans un script renvoie les résultats de la dernière instruction SELECT, LMD ou LDD exécutée dans le script. Aucun résultat n'est renvoyé si aucune des instructions ci-dessus n'a été exécutée. Pour obtenir les résultats de toutes les instructions du script, répertoriez les tâches enfants et appelez jobs.getQueryResults
pour chacune d'elles.
BigQuery interprète toute requête comportant plusieurs instructions comme un script, sauf si ces instructions consistent en des instructions CREATE TEMP FUNCTION
, avec une seule instruction de requête finale. Par exemple, la requête suivante n'est pas considérée comme un script :
CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y); SELECT Add(3, 4);
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.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 clauseINTO
doit contenir des valeurs du même type. Vous pouvez accéder aux variables système et aux valeurs présentes dans la clauseUSING
. 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 clauseINTO
. - Clause
USING
: avant d'exécuter votre expression SQL, vous pouvez transmettre un ou plusieurs identifiants de la clauseUSING
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 clauseUSING
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 clauseUSING
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é.- 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
etUSING
. 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 clauseINTO
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 dequery_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
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
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 :
|
@@error.statement_text |
STRING |
Spécifie le texte de l'instruction à l'origine de l'erreur. |
1 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]" |
END
Arrête un bloc démarré par BEGIN
BEGIN
/END
ne peut pas être exécuté de manière dynamique en tant qu'élément imbriqué.
IF
Syntaxe
IF condition THEN [sql_statement_list] [ELSEIF 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é.
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é.
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é.
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
.
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.
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
.
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înemessage
fournie (pouvant êtreNULL
simessage
estNULL
).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é.
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 |
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. |
@@last_job_id |
STRING |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Nombre d'emplacements de millisecondes utilisés 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. Contrairement aux autres variables système, @@time_zone peut être modifiée 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". |
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.
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
etEXECUTE 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