Créer des scripts en langage SQL standard

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
);

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

Syntaxe

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.

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;

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

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.

Exemple

L'exemple suivant déclare une variable x avec une valeur par défaut de 10. Ensuite, il démarre un bloc dans lequel la valeur de (10) est attribuée à une variable y, et renvoie cette valeur. Ensuite, l'instruction END arrête le bloc, mettant fin au champ d'application de la variable y. Enfin, il renvoie la valeur de .

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

END

Arrête un bloc démarré par BEGIN.

IF

Syntaxe

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

Description

Si condition a la valeur "true", exécute if_statement_list. Sinon, exécute else_statement_list si la clause ELSE facultative est fournie.

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.

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 . 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));
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.

Exemple

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

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.

BREAK

Description

Quitte la boucle actuelle.

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

Exemple

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 à , puis il vérifie si a la valeur "true". Si c'est le cas, il affiche "Heads!" et incrémente heads_count. Si ce n'est pas le cas, il affiche "Tails!" 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" (face).

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');

QUITTER

Synonyme de BREAK.

CONTINUER

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.

Exemple

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 à , puis il vérifie si a la valeur "true". Si c'est le cas, il affiche "Heads!", incrémente heads_count et redémarre la boucle en ignorant les instructions restantes. Si ce n'est pas le cas, il affiche "Tails!" 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" (face).

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.

RETURN

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

APPELER

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.

Exemple

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;

Cette page vous a-t-elle été utile ? Évaluez-la :

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Dernière mise à jour : janvier 20, 2020

Créer des scripts en langage SQL standard

Fonctionnalité de scripts de BigQuery

DECLARE

Syntaxe

Description

Exemples

SET

Syntaxe

Description

Exemples

BEGIN

Syntaxe

Description

Exemple

END

IF

Syntaxe

Description

Exemples

Boucles

LOOP

Syntaxe

Description

Exemple

WHILE

Syntaxe

Description

BREAK

Description

Exemple

QUITTER

CONTINUER

Description

Exemple

ITERATE

RETURN

APPELER

Envoyer des commentaires concernant…