Introduction au scripting SQL dans Databricks, partie 2

Dans la deuxième partie de la série d'articles sur l'annonce de SQL Scripting, nous examinerons la tâche administrative dont nous avons discuté dans la première partie : comment appliquer une règle insensible à la casse à chaque colonne STRING d'une table.

Nous allons parcourir cet exemple étape par étape, expliquer les fonctionnalités utilisées et l'étendre au-delà d'une seule table pour couvrir un schéma entier.

Vous pouvez également suivre dans ce notebook. 

Modification de la collation de tous les champs texte de toutes les tables d'un schéma

Databricks prend en charge un large éventail de collations, conscientes de la langue, insensibles à la casse et aux accents. Il est facile d'utiliser cette fonctionnalité pour les nouvelles tables et colonnes. Mais que faire si vous avez un système existant qui utilise upper() ou lower() dans les prédicats partout et que vous souhaitez bénéficier des améliorations de performance associées à une collation native insensible à la casse tout en simplifiant vos requêtes ? Cela nécessitera une certaine programmation ; maintenant, vous pouvez tout faire en SQL. 

Utilisons le schéma de test suivant : 

L'ordre est basé sur les points de code ASCII, où toutes les lettres majuscules précèdent toutes les lettres minuscules. Pouvez-vous corriger cela sans ajouter upper() ou lower() ?

Instructions SQL dynamiques et définition de variables

Notre première étape consiste à indiquer à la table de modifier sa collation par défaut pour les colonnes nouvellement ajoutées. Vous pouvez alimenter vos variables locales avec des marqueurs de paramètres, que le notebook détectera automatiquement et ajoutera des widgets. Vous pouvez également utiliser EXECUTE IMMEDIATE pour exécuter une instruction ALTER TABLE composée dynamiquement.

Chaque script SQL se compose d'une instruction BEGIN .. END (composée). Les variables locales sont définies d'abord dans une instruction composée, suivies de la logique.

Il s'agit uniquement d'un ensemble d'instructions linéaires. Jusqu'à présent, vous pouviez écrire tout cela avec des variables de session SQL sans l'instruction composée. Vous n'avez pas non plus obtenu grand-chose. Après tout, vous vouliez changer la collation des colonnes existantes. Pour ce faire, vous devez :

• Découvrir toutes les colonnes de chaîne existantes dans la table
• Modifier la collation de chaque colonne

En bref, vous devez boucler sur la table INFORMATION_SCHEMA.COLUMNS.

Boucles

SQL Scripting offre quatre types de boucles et des moyens de contrôler les itérations de boucle.

1. LOOP … END LOOP ;
   C'est une boucle "infinie".
   Cette boucle se poursuivra jusqu'à ce qu'une exception ou une commande ITERATE ou LEAVE explicite ne sorte de la boucle.
   Nous discuterons de la gestion des exceptions plus tard et nous vous renverrons à la documentation ITERATE et LEAVE expliquant comment contrôler les boucles.
2. WHILE predicate DO … END WHILE;
   Cette boucle sera entrée et ré-entrée tant que l'expression prédicat sera vraie ou que la boucle sera interrompue par une exception, ITERATE ou LEAVE.
3. REPEAT … UNTIL predicate END REPEAT;
   Contrairement à WHILE, cette boucle est entrée au moins une fois et ré-exécutée jusqu'à ce que l'expression prédicat soit fausse ou que la boucle soit interrompue par une exception, une commande LEAVE ou ITERATE.
4. FOR query DO …. END FOR;
   Cette boucle s'exécute une fois par ligne renvoyée par la requête, sauf si elle est interrompue prématurément par une exception, une instruction LEAVE ou ITERATE.

Appliquons maintenant la boucle FOR à notre script de collation. La requête récupère les noms de colonnes de toutes les colonnes de chaîne de la table. Le corps de la boucle modifie la collation de chaque colonne à tour de rôle :

Vérifions que la table a été correctement mise à jour :

Jusqu'ici, tout va bien. Notre code est fonctionnellement complet, mais vous devriez indiquer à Delta d'analyser les colonnes que vous avez modifiées pour bénéficier du saut de fichier. Vous ne voulez pas faire cela colonne par colonne. Mais rassemblez-les toutes et effectuez le travail uniquement s'il y avait effectivement une colonne de type chaîne pour laquelle la collation a été modifiée. Des décisions, des décisions… 

Logique conditionnelle

SQL Scripting offre trois façons d'exécuter conditionnellement des instructions SQL.

1. Logique If-then-else. La syntaxe est simple :
   IF predicate THEN … ELSEIF predicate THEN … ELSE …. END IF;
   Naturellement, vous pouvez avoir autant de blocs ELSEIF optionnels que vous le souhaitez, et le bloc ELSE final est également facultatif.
2. Une simple instruction CASE
   Cette instruction est la version SQL Scripting de l'expression CASE simple.
   CASE expression WHEN option THEN … ELSE … END CASE;
   Une seule exécution d'une expression est comparée à plusieurs options, et la première correspondance détermine quel ensemble d'instructions SQL doit être exécuté. Si aucune ne correspond, le bloc ELSE facultatif sera exécuté.
3. Une instruction CASE recherchée
   Cette instruction est la version SQL Scripting de l'expression CASE recherchée.
   CASE WHEN predicate THEN …. ELSE … END CASE;
   Le bloc THEN est exécuté pour la première des conditions qui s'évalue à vrai. Si aucune ne correspond, le bloc ELSE facultatif est exécuté.

Pour notre script de collation, un simple IF THEN END IF suffira. Vous devez également collecter l'ensemble des colonnes auxquelles appliquer ANALYZE et utiliser quelques fonctions d'ordre supérieur pour générer la liste des colonnes :

Imbrication

Ce que vous avez écrit jusqu'à présent fonctionne pour des tables individuelles. Qu'en est-il si vous voulez opérer sur toutes les tables d'un schéma ? SQL Scripting est entièrement composable. Vous pouvez imbriquer des instructions composées, des instructions conditionnelles et des boucles dans d'autres instructions SQL Scripting.

Vous allez donc faire deux choses ici :

1. Ajouter une boucle FOR externe pour trouver toutes les tables d'un schéma en utilisant INFORMATION_SCHEMA.TABLES. Dans ce cadre, vous devez remplacer les références à la variable de nom de table par des références aux résultats de la requête de la boucle FOR.
2. Ajouter un bloc composé imbriqué pour déplacer la variable de liste de colonnes dans la boucle FOR externe. Vous ne pouvez pas déclarer une variable directement dans le corps de la boucle FOR ; cela n'ajoute pas de nouvelle portée. C'est principalement une décision liée au style de codage, mais vous aurez une raison plus sérieuse pour une nouvelle portée.

This error makes sense. You have multiple ways to proceed:

1. Filter out unsupported table types, such as views, in the information schema query. The problem is that there are numerous table types, and new ones are occasionally added.
2. Handle views. That's a great idea. Let's call that your homework assignment.
3. Tolerating the error condition

Exception handling

A key capability of SQL Scripting is the ability to intercept and handle exceptions. Condition handlers are defined in the declaration section of a compound statement, and they apply to any statement within that compound, including nested statements. You can handle specific error conditions by name, specific SQLSTATEs handling several error conditions, or all error conditions. Within the body of the condition handler, you can use the GET DIAGNOSTICS statement to retrieve information about the exception being handled and execute any SQL scripting you deem appropriate, such as recording the error in a log or running an alternative logic to the one that failed. You can then SIGNAL a new error condition, RESIGNAL the original condition, or simply exit the compound statement where the handler is defined and continue with the following statement.

In our script, you want to skip any statement for which the ALTER TABLE DEFAULT COLLATION statement did not apply and log the object's name.

Above, you have developed an administrative script purely in SQL. You can also write ELT scripts and turn them into Jobs. SQL Scripting is a truly powerful tool you should exploit.  

What to do next

Whether you are an existing Databricks user or migrating from another product, SQL Scripting is a capability you should use. SQL Scripting follows the ANSI standard and is fully compatible with OSS Apache Spark™. SQL Scripting is described in detail in SQL Scripting | Databricks Documentation. 

You can also use this notebook to see for yourself. 

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original