Introduction aux procédures stockées SQL dans Databricks

La gestion des tâches SQL répétitives — comme le nettoyage des données, la mise à jour des règles métier ou l'exécution de logiques batch — peut être fastidieuse et sujette aux erreurs si vous copiez-collez du code. 

Désormais, les procédures stockées SQL dans Databricks (maintenant généralement disponibles) vous permettent de stocker cette logique une seule fois, de l'exécuter quand vous le souhaitez, et de la maintenir sous le contrôle de Unity Catalog. 

Que vous nettoyiez des données avant l'analyse, mettiez à jour des tables en fonction de critères métier, ou déplaciez des charges de travail d'un entrepôt de données d'entreprise hérité, les procédures stockées rendent le processus plus simple, plus cohérent et plus facile à maintenir.

Databricks prend en charge les standards ouverts et l'interopérabilité, en évitant les implémentations propriétaires ou spécifiques à un fournisseur. Les procédures stockées SQL suivent le standard ANSI/PSM SQL et seront contribuées à Apache Spark™ open source.

Les procédures sont largement utilisées dans les tâches administratives, la gestion des données et les flux ETL — en particulier dans les entrepôts de données d'entreprise (EDW). Pour les clients passant des EDW à Databricks, les procédures stockées existantes peuvent être migrées sans réécriture, simplifiant ainsi la transition. Et comme toujours, le meilleur entrepôt de données est un lakehouse.

Pour l'un de nos cas d'utilisation critiques autour de la segmentation client, nous avons utilisé les procédures stockées SQL avec DBSQL pour obtenir de meilleures performances, une meilleure scalabilité et une meilleure efficacité des coûts. Ma familiarité avec SQL nous a aidés à implémenter et déployer la solution en production en très peu de temps. L'utilisation des procédures stockées nous a permis de gérer une logique complexe plus efficacement tout en gardant l'architecture globale rationalisée et maintenable. —SambaSiva Rao, Sr. Data Engineer/Architect, ClicTechnologies

Les procédures stockées SQL sont maintenant généralement disponibles.

Aperçu des procédures stockées SQL

Que sont les procédures stockées ?

Dans les flux de traitement de données, les clients peuvent avoir du mal à maintenir la cohérence et les performances des tâches répétitives et des logiques complexes. Les procédures stockées sont une excellente approche dans ces cas, garantissant que les données sont traitées de manière cohérente et standardisée, et que les performances sont optimales. 

Pour les tâches de nettoyage de données, les procédures peuvent appliquer des transformations telles que la conversion de formats de date incohérents en une structure standardisée, la suppression des espaces blancs en début et fin de champs texte, et le remplacement ou la correction de valeurs erronées. Cela garantit que vos données sont préparées pour l'analyse en aval. Voir l'exemple ETL détaillé ci-dessous.

Du côté de la gestion des données, les procédures stockées peuvent mettre à jour efficacement les valeurs des tables en fonction de règles métier définies — comme le marquage des enregistrements obsolètes, le recalcul des champs, ou la synchronisation des données entre tables liées. En encapsulant ces opérations dans des procédures, les équipes peuvent assurer une exécution cohérente, réduire l'intervention manuelle et améliorer la qualité des données à grande échelle. Voir l'exemple de gestion de données détaillé ci-dessous, utilisant des procédures stockées pour mettre à jour un programme de fidélité/adhésion.

Alors, que sont les procédures ? Ce sont des collections pré-compilées d'instructions SQL qui permettent à un utilisateur de gérer sa logique SQL en une seule unité réutilisable. Les procédures sont stockées dans Unity Catalog, ce qui signifie qu'elles sont gouvernées et encapsulent entièrement les permissions. Lorsqu'une procédure stockée est appelée, la base de données exécute ces opérations prédéfinies, offrant l'avantage d'une sécurité renforcée, d'une maintenance simplifiée des charges de travail complexes et d'un potentiel d'amélioration des performances.

Qu'est-ce qui est pris en charge ?

5 commandes principales prennent en charge les procédures : CREATE, CALL, DESCRIBE, SHOW et DROP.

• CREATE PROCEDURE : Définit et stocke une nouvelle procédure stockée dans Unity Catalog. Elle spécifie le nom de la procédure, les paramètres (le cas échéant) et les instructions SQL à exécuter lorsque la procédure est appelée.
• CALL PROCEDURE : Exécute une procédure stockée précédemment créée ; en passant les paramètres requis.
• DESCRIBE PROCEDURE : Renvoie les métadonnées de base d'une procédure existante, telles que son nom et ses paramètres. Avec EXTENDED, la description inclura des métadonnées supplémentaires, notamment le propriétaire, la date de création, le type de sécurité, etc.
• SHOW PROCEDURES : Liste toutes les procédures stockées disponibles dans le schéma de catalogue actuel.
• DROP PROCEDURE : Supprime une procédure existante du stockage. 

Lors de la création d'une procédure, vous pouvez utiliser plusieurs types de paramètres pour contrôler les entrées et les sorties. 

• Paramètre `IN` : Utilisé pour passer des valeurs à une procédure en entrée. Par exemple, vous pourriez passer un ID client pour récupérer ou traiter uniquement les données de ce client. La procédure peut lire ces valeurs mais pas les modifier.
• Paramètre `OUT` : Utilisé pour renvoyer des valeurs d'une procédure, après qu'elles aient été assignées. Par exemple, vous pourriez passer un ID client et renvoyer son statut de compte ou son total de ventes calculé, pour un traitement ultérieur en dehors de la procédure.
• Paramètre `INOUT` : Sert un double objectif, permettant à une valeur d'être passée à une procédure, modifiée à l'intérieur, et la valeur mise à jour d'être renvoyée. Il fonctionne à la fois comme entrée et comme sortie. 

Ces paramètres peuvent être assignés à :

• Des variables locales, déclarées dans un script/procédure
• Des variables de session, déclarées dans la session, et même en dehors du script/procédure

La logique encapsulée dans une procédure stockée SQL est construite sur le scripting SQL. Une procédure stockée peut être considérée comme un script réutilisable avec des paramètres, gouverné par Unity Catalog. Vous pouvez en savoir plus sur le scripting dans ces deux blogs d'introduction :

• Introduction au scripting SQL Partie 1
• Introduction au scripting SQL Partie 2
• Consultez la documentation sur le scripting SQL

Les appels de procédures imbriquées et récursives sont pris en charge, ce qui signifie que les clients peuvent organiser leurs unités de travail ou leur logique métier de manière pratique en procédures distinctes, rendant l'ensemble du flux d'exécution SQL plus modulaire. Cela améliore la lisibilité et la maintenance.

Quelle est la différence entre les procédures et les fonctions ?

Les procédures sont regroupées avec les fonctions dans Unity Catalog dans l'interface utilisateur. Cependant, les procédures et les fonctions, bien qu'elles permettent de réutiliser la logique SQL, servent des objectifs différents.

Une fonction est utilisée pour retourner une valeur ou une table. Elle doit être utilisée dans une requête SQL et ne peut pas inclure de SQL dynamique ou de logique procédurale. Une procédure, en revanche, est utilisée pour exécuter une séquence d'instructions SQL. Elle peut inclure des flux de contrôle, des variables, des boucles et du SQL dynamique en utilisant IDENTIFIER et EXECUTE IMMEDIATE. Vous appelez une procédure comme une commande autonome, généralement pour effectuer une tâche ou un workflow.

Exemples d'utilisation des procédures stockées SQL

Maintenant que nous avons couvert les capacités des procédures stockées SQL, explorons quelques exemples pour démontrer leur valeur et les problèmes qu'elles aident à résoudre.

Vous pouvez utiliser ce notebook pour suivre — il contient tous les exemples de cet article, ainsi que les commandes de préparation des données.

ETL – Nettoyage des données : Préparation des tables de niveau Silver ou Gold

Si vous suivez l'architecture typique de la médaillon, vous savez que le déplacement des données de Bronze vers Silver (ou de Silver vers Gold) peut nécessiter le nettoyage, la transformation, l'agrégation et la mise en forme des données. Les procédures stockées sont idéales pour gérer les processus répétitifs comme ceux-ci dans un flux de travail ETL. 

Dans ce scénario ETL, une procédure est utilisée pour :

• Charger les données brutes dans une table de faits
• Sélectionner les données en fonction de la plage de dates et de l'origine de la vente (web, mobile, point de vente, fournisseur tiers)
• Nettoyer et formater les données en convertissant les dates dans un format spécifique et en supprimant les espaces blancs
• Une fois nettoyées, charger les données dans une table « propre »
• Ajouter un enregistrement de journal basé sur l'horodatage, les dates de début et de fin, et l'origine de la vente
• En utilisant la procédure, rechercher les ventes d'applications mobiles pour juin 2025

Les procédures comme celle-ci aident à standardiser les produits de données. Tout utilisateur de cette procédure produira des données dans la même structure, quelle que soit la plage de dates ou le point de vente. C'est un avantage principal de la réutilisation du code. La réutilisation du code sera naturellement moins sujette aux erreurs car la même logique est exécutée à chaque fois.

Gestion des données : Mettre à jour une table en fonction de critères commerciaux

La gestion des données est la pratique qui consiste à s'assurer que vos données sont précises, cohérentes et accessibles efficacement — des qualités essentielles pour toute organisation visant à prendre des décisions basées sur les données. Sans une gestion solide des données, même les efforts d'analyse ou de reporting les plus avancés peuvent être compromis par des informations peu fiables ou incohérentes. 

Examinons un exemple courant dans les industries commerciales où une entreprise établit un programme de fidélité pour offrir des avantages aux clients en fonction de leur niveau. Les compagnies aériennes ont des programmes de fidélisation et la plupart des franchises de vente au détail ont des programmes de récompenses, etc. Plus les clients volent avec la même compagnie aérienne ou achètent plus d'articles dans la même franchise, plus ils gagnent d'avantages.

Voici un exemple de la façon dont les procédures stockées peuvent être utilisées pour gérer et mettre à jour un programme de fidélité standard de vente au détail. Il existe deux procédures utilisées pour gérer les niveaux de fidélité des clients : une pour mettre à jour le niveau de fidélité d'un client spécifique pour l'ID client fourni, et l'autre qui met à jour le niveau de fidélité de tous les clients d'un pays donné.

Utilisons maintenant la procédure créée pour mettre à jour les niveaux de fidélité des clients de Serbie, d'Allemagne et du Canada, puis vérifions les enregistrements mis à jour :

La requête précédente produit le résultat suivant :

En encapsulant la logique de mise à jour des niveaux dans des procédures respectives, nous évitons la duplication de code tout en supprimant la complexité pour l'appelant, qui n'a qu'à invoquer la procédure avec les paramètres appropriés.

Et ensuite ?

Avec les procédures stockées SQL désormais disponibles dans DBSQL, les clients peuvent continuer à migrer les charges de travail des entrepôts de données d'entreprise hérités vers le lakehouse. Sur la base des commentaires des clients, plusieurs capacités clés que nous souhaitons aborder au fur et à mesure que nous progressons vers la disponibilité générale (GA) :

• Procédures stockées SQL dans Apache Spark™ : Prise en charge de toutes les procédures SQL dans le logiciel open source
• Prise en charge de SQL SECURITY DEFINER : Permettre aux clients d'exécuter la logique de procédure avec les autorisations du créateur de la procédure (défenseur)

Les clients qui souhaitent partager leurs commentaires ou leurs demandes concernant les scripts et procédures SQL peuvent le faire via ce formulaire.

Deux autres constructions SQL importantes sont nécessaires pour migrer les procédures stockées des systèmes hérités : les Tables Temporaires et les Transactions. Les tables temporaires sont maintenant généralement disponibles sur DBSQL, tandis que la prise en charge des transactions (multi-instructions et multi-tables) sera bientôt disponible. 

Que vous soyez un utilisateur Databricks existant ou que vous migriez depuis un autre entrepôt de données, les procédures stockées SQL sont une fonctionnalité que vous devriez utiliser pour simplifier la gestion des flux de travail SQL complexes. Commencez avec les procédures stockées SQL en lisant la documentation Databricks documentation.

Pour en savoir plus sur Databricks SQL, visitez notre site web ou lisez la documentation. Vous pouvez également consulter la visite guidée du produit pour Databricks SQL. Si vous souhaitez migrer votre entrepôt existant vers un entrepôt de données serveurless haute performance avec une excellente expérience utilisateur et un coût total réduit, alors Databricks SQL est la solution — essayez-le gratuitement.

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