Procédures stockées utilisant Synapse SQL dans Azure Synapse Analytics

• Démarrez un essai gratuit Fabric.
• Assistant Migration pour Fabric Data Warehouse.

Les pools provisionnés et sans serveur Synapse SQL vous permettent de placer une logique complexe de traitement des données dans des procédures stockées SQL. Les procédures stockées constituent un excellent moyen d’encapsuler votre code SQL et de les stocker à proximité de vos données dans l’entrepôt de données. Les procédures stockées aident les développeurs à modulariser leurs solutions en encapsulant le code en unités gérables et en facilitant une plus grande réutilisation du code. Chaque procédure stockée peut également accepter des paramètres pour les rendre encore plus flexibles. Dans cet article, vous trouverez quelques conseils pour implémenter des procédures stockées dans le pool SQL Synapse pour développer des solutions.

À quoi s’attendre

Synapse SQL prend en charge la plupart des fonctionnalités T-SQL utilisées dans SQL Server. Plus important encore, il existe des fonctionnalités spécifiques de scale-out que vous pouvez utiliser pour optimiser les performances de votre solution. Dans cet article, vous allez découvrir les fonctionnalités que vous pouvez placer dans les procédures stockées.

Pour maintenir la mise à l’échelle et les performances du pool SQL, il existe également certaines fonctionnalités qui présentent des différences comportementales et d’autres qui ne sont pas prises en charge.

Procédures stockées dans Synapse SQL

Dans l’exemple suivant, vous pouvez voir les procédures qui suppriment des objets externes s’ils existent dans la base de données :

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_tables WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL TABLE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_file_format_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_file_formats WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL FILE FORMAT ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_data_source_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_data_sources WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL DATA SOURCE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END

Ces procédures peuvent être exécutées à l’aide d’une EXEC instruction dans laquelle vous pouvez spécifier le nom et les paramètres de la procédure :

EXEC drop_external_table_if_exists 'mytest';
EXEC drop_external_file_format_if_exists 'mytest';
EXEC drop_external_data_source_if_exists 'mytest';

Synapse SQL fournit une implémentation simplifiée et rationnalisée des procédures stockées. La plus grande différence par rapport à SQL Server est que la procédure stockée n’est pas du code précompilé. Dans les entrepôts de données, le temps de compilation est faible par rapport au temps nécessaire pour exécuter des requêtes sur de grands volumes de données. Il est plus important de s’assurer que le code de procédure stockée est correctement optimisé pour les requêtes volumineuses. L’objectif est d’économiser des heures, des minutes et des secondes, et non pas des millisecondes. Il est donc plus utile de considérer les procédures stockées comme des conteneurs pour la logique SQL.

Lorsque Synapse SQL exécute votre procédure stockée, les instructions SQL sont analysées, traduites et optimisées au moment de l’exécution. Pendant ce processus, chaque instruction est convertie en requêtes distribuées. Le code SQL exécuté sur les données est différent de la requête envoyée.

Encapsuler des règles de validation

Les procédures stockées vous permettent de localiser la logique de validation dans un seul module stocké dans la base de données SQL. Dans l’exemple suivant, vous pouvez voir comment valider les valeurs des paramètres et modifier leurs valeurs par défaut.

CREATE PROCEDURE count_objects_by_date_created 
                            @start_date DATETIME2,
                            @end_date DATETIME2
AS BEGIN 

    IF( @start_date >= GETUTCDATE() )
    BEGIN
        THROW 51000, 'Invalid argument @start_date. Value should be in past.', 1;  
    END

    IF( @end_date IS NULL )
    BEGIN
        SET @end_date = GETUTCDATE();
    END

    IF( @start_date >= @end_date )
    BEGIN
        THROW 51000, 'Invalid argument @end_date. Value should be greater than @start_date.', 2;  
    END

    SELECT
         year = YEAR(create_date),
         month = MONTH(create_date),
         objects_created = COUNT(*)
    FROM
        sys.objects
    WHERE
        create_date BETWEEN @start_date AND @end_date
    GROUP BY
        YEAR(create_date), MONTH(create_date);
END

La logique de la procédure sql valide les paramètres d’entrée lorsque la procédure est appelée.

EXEC count_objects_by_date_created '2020-08-01', '2020-09-01'

EXEC count_objects_by_date_created '2020-08-01', NULL

EXEC count_objects_by_date_created '2020-09-01', '2020-08-01'
-- Error
-- Invalid argument @end_date. Value should be greater than @start_date.

EXEC count_objects_by_date_created '2120-09-01', NULL
-- Error
-- Invalid argument @start_date. Value should be in past.

Imbrication des procédures stockées

Lorsque des procédures stockées appellent d’autres procédures stockées ou exécutent du SQL dynamique, l'appel de la procédure stockée interne ou de code est considéré comme imbriqué. Un exemple de procédure imbriquée est illustré dans le code suivant :

CREATE PROCEDURE clean_up @name SYSNAME
AS BEGIN
    EXEC drop_external_table_if_exists @name;
    EXEC drop_external_file_format_if_exists @name;
    EXEC drop_external_data_source_if_exists @name;
END

Cette procédure accepte un paramètre qui représente un nom, puis appelle d’autres procédures pour supprimer les objets portant ce nom. Le pool SQL Synapse prend en charge un maximum de huit niveaux d’imbrication. Cette fonctionnalité est légèrement différente de SQL Server. Le niveau d’imbrication dans SQL Server est 32.

L’appel de procédure stockée de plus haut niveau équivaut au niveau d’imbrication 1.

EXEC clean_up 'mytest'

Si la procédure stockée effectue également un autre appel EXEC, le niveau d’imbrication augmente de deux.

CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

Si la deuxième procédure exécute ensuite du SQL dynamique, le niveau d’imbrication augmente à trois.

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    /* See full code in the previous example */
    EXEC sp_executesql @tsql = @drop_stmt;  -- This call is nest level 3
END
GO
CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

INSÉRER.. EXÉCUTER

Le pool Synapse SQL provisionné ne vous permet pas d’utiliser le jeu de résultats d’une procédure stockée avec une instruction INSERT. Il existe une autre approche que vous pouvez utiliser. Pour obtenir un exemple, consultez l’article sur les tables temporaires pour le pool Synapse SQL provisionné.

Limitations

Il existe certains aspects de Transact-SQL procédures stockées qui ne sont pas implémentées dans Synapse SQL, comme :

Contenu connexe

Pour obtenir des conseils supplémentaires, consultez la vue d’ensemble du développement.