Variables SQL

Ce chapitre décrit comment définir et utiliser des variables SQL dans des sessions de Snowflake.

Dans ce chapitre :

Vue d’ensemble

Snowflake prend en charge les variables SQL déclarées par l’utilisateur. Elles ont de nombreuses utilisations, telles que le stockage de paramètres d’environnement spécifiques à l’application.

Identificateurs de variables

Les variables SQL sont identifiées globalement à l’aide de noms insensibles à la casse.

DDL de variables

Snowflake fournit les commandes DDL suivantes pour l’utilisation des variables SQL :

Initialisation des variables

Les variables peuvent être définies en exécutant l’instruction SQL SET ou en définissant les variables dans la chaîne de connexion lorsque vous vous connectez à Snowflake.

La taille des chaînes ou des variables binaires est limitée à 256 octets.

Utiliser SQL pour initialiser des variables dans une session

Les variables peuvent être initialisées dans SQL en utilisant la commande SET. Le type de données de la variable s’appuie sur le type de données du résultat de l’expression évaluée.

SET MY_VARIABLE=10;
SET MY_VARIABLE='example';

Plusieurs variables peuvent être initialisées dans la même instruction, réduisant ainsi le nombre de communications aller-retour avec le serveur.

SET (VAR1, VAR2, VAR3)=(10, 20, 30);
SET (VAR1, VAR2, VAR3)=(SELECT 10, 20, 30);

Réglage des variables à la connexion

En plus d’utiliser SET pour définir des variables dans une session, les variables peuvent être transmises en arguments dans la chaîne de connexion utilisée pour initialiser une session dans Snowflake. Ceci est particulièrement utile lorsque vous utilisez des outils où la spécification de la chaîne de connexion est la seule personnalisation possible.

Par exemple, en utilisant le pilote JDBC de Snowflake, vous pouvez définir des propriétés de connexion supplémentaires qui seront interprétées comme des paramètres. Notez que l’API JDBC nécessite que des variables SQL soient des chaînes.

// build connection properties
Properties properties = new Properties();

// Required connection properties
properties.put("user"    ,  "jsmith"      );
properties.put("password",  "mypassword");
properties.put("account" ,  "myaccount");

// Set some additional variables.
properties.put("$variable_1", "some example");
properties.put("$variable_2", "1"           );

// create a new connection
String connectStr = "jdbc:snowflake://localhost:8080";

// Open a connection under the snowflake account and enable variable support
Connection con = DriverManager.getConnection(connectStr, properties);

Utilisation des variables dans SQL

Des variables peuvent être utilisées dans Snowflake partout où une constante littérale est permise. Pour les distinguer des valeurs de liaison et des noms de colonnes, toutes les variables doivent être préfixées par un signe $.

Par exemple :

SET (MIN, MAX)=(40, 70);

SELECT $MIN;

SELECT AVG(SALARY) FROM EMP WHERE AGE BETWEEN $MIN AND $MAX;

Note

Puisque le signe $ est le préfixe utilisé pour identifier les variables dans des instructions SQL, il est traité comme un caractère spécial lorsqu’il est utilisé dans des identificateurs. Les identificateurs (noms de base de données, noms de tables, noms de colonnes, etc.) ne peuvent pas commencer par des caractères spéciaux à moins que le nom entier soit délimité par des guillemets. Pour plus d’informations, voir Identificateurs d’objet.

Les variables peuvent également contenir des noms d’identificateur, tels que des noms de table. Pour utiliser une variable en tant qu’identificateur, vous devez l’envelopper dans IDENTIFIER(), par exemple IDENTIFIER($MY_VARIABLE). Quelques exemples ci-dessous :

CREATE TABLE IDENTIFIER($MY_TABLE_NAME) (i INTEGER);
INSERT INTO IDENTIFIER($MY_TABLE_NAME) (i) VALUES (42);
SELECT * FROM IDENTIFIER($MY_TABLE_NAME);
DROP TABLE IDENTIFIER($MY_TABLE_NAME);

Dans le contexte d’une clause FROM, vous pouvez envelopper le nom de la variable dans TABLE(), comme indiqué ci-dessous :

SELECT * FROM TABLE($MY_TABLE_NAME);
+----+
|  I |
|----|
| 42 |
+----+

Pour plus d’informations sur IDENTIFIER(), voir Littéraux de chaîne / Variables de session / Variables de liaison comme identificateurs.

Affichage des variables de la session

Pour voir toutes les variables définies dans la session actuelle, utilisez la commande SHOW VARIABLES :

SET (MIN, MAX)=(40, 70);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SHOW VARIABLES;

+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+
|  session_id | created_on                      | updated_on                      | name | value | type  | comment |
|-------------+---------------------------------+---------------------------------+------+-------+-------+---------|
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MAX  | 70    | fixed |         |
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MIN  | 40    | fixed |         |
+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+

Fonctions des variables de session

Les fonctions de commodité suivantes sont fournies pour manipuler des variables de session afin d’assurer la compatibilité avec d’autres systèmes de base de données et d’émettre des commandes SQL au moyen d’outils qui ne prennent pas en charge la syntaxe $ d’accès aux variables. Notez que toutes ces fonctions acceptent et retournent des valeurs de variables de session sous forme de chaînes :

  • SYS_CONTEXT et SET_SYS_CONTEXT

  • SESSION_CONTEXT et SET_SESSION_CONTEXT

  • GETVARIABLE et SETVARIABLE

Voici des exemples d’utilisation de GETVARIABLE() :

set var_artist_name ='Jackson Browne';
-- Variable names are forced to uppercase by default, so output is NULL.
select getvariable('var_artist_name');
-- Output is "Jackson Browne".
select getvariable('VAR_ARTIST_NAME');

-- You can use the variable name in a WHERE clause, for example:
select album_title from albums where artist = $VAR_ARTIST_NAME;

Destruction/Suppression de variables

Les variables SQL sont privées dans une session. Lorsqu’une session Snowflake est fermée, toutes les variables créées pendant la session sont détruites. Cela signifie que personne ne peut accéder aux variables définies par l’utilisateur qui ont été définies dans une autre session, et lorsque la session est fermée, ces variables expirent.

De plus, les variables peuvent toujours être explicitement détruites à l’aide de la commande UNSET.

Par exemple :

UNSET MY_VARIABLE;