Gespeicherte Prozeduren mit Synapse SQL in Azure Synapse Analytics

• Starten Sie eine kostenlose Testversion von Fabric.
• Migration Assistant für Fabric Data Warehouse.

Synapse SQL-bereitgestellte und serverlose Pools ermöglichen es Ihnen, komplexe Datenverarbeitungslogik in gespeicherte SQL-Prozeduren zu platzieren. Gespeicherte Prozeduren eignen sich hervorragend zur Kapselung Ihres SQL-Codes und zur Speicherung nahe den Daten im Datenlager. Gespeicherte Prozeduren helfen Entwicklern, ihre Lösungen zu modularisieren, indem sie den Code in verwaltbare Einheiten kapseln und eine bessere Wiederverwendbarkeit von Code ermöglichen. Jede gespeicherte Prozedur kann auch Parameter akzeptieren, um sie noch flexibler zu gestalten. In diesem Artikel finden Sie einige Tipps für die Implementierung gespeicherter Prozeduren im Synapse SQL-Pool für die Entwicklung von Lösungen.

Was dich erwartet

Synapse SQL unterstützt viele der T-SQL-Features, die in SQL Server verwendet werden. Wichtiger ist, dass es skalierungsspezifische Features gibt, die Sie verwenden können, um die Leistung Ihrer Lösung zu maximieren. In diesem Artikel erfahren Sie mehr über die Features, die Sie in gespeicherten Prozeduren platzieren können.

Um die Skalierung und Leistung des SQL-Pools aufrechtzuerhalten, gibt es auch einige Features und Funktionen, die Verhaltensunterschiede aufweisen und andere, die nicht unterstützt werden.

Gespeicherte Prozeduren in Synapse SQL

Im folgenden Beispiel können Sie die Prozeduren sehen, die externe Objekte ablegen, wenn sie in der Datenbank vorhanden sind:

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

Diese Prozeduren können mithilfe einer EXEC Anweisung ausgeführt werden, in der Sie den Prozedurnamen und parameter angeben können:

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 bietet eine vereinfachte und optimierte Implementierung gespeicherter Prozeduren. Der größte Unterschied im Vergleich zu SQL Server besteht darin, dass die gespeicherte Prozedur kein vorkompilierter Code ist. In Data Warehouses ist die Kompilierungszeit im Vergleich zu der Zeit, die zum Ausführen von Abfragen für große Datenvolumes benötigt wird, gering. Es ist wichtiger, sicherzustellen, dass der gespeicherte Prozedurcode für große Abfragen ordnungsgemäß optimiert ist. Ziel ist es, Stunden, Minuten und Sekunden zu sparen, nicht Millisekunden. Daher ist es hilfreicher, gespeicherte Prozeduren als Container für SQL-Logik zu betrachten.

Wenn Synapse SQL Ihre gespeicherte Prozedur ausführt, werden die SQL-Anweisungen zur Laufzeit analysiert, übersetzt und optimiert. Während dieses Prozesses wird jede Anweisung in verteilte Abfragen konvertiert. Der SQL-Code, der für die Daten ausgeführt wird, unterscheidet sich von der übermittelten Abfrage.

Gültigkeitsregeln kapseln

Mit gespeicherten Prozeduren können Sie die Validierungslogik in einem einzelnen Modul finden, das in der SQL-Datenbank gespeichert ist. Im folgenden Beispiel können Sie sehen, wie Sie die Werte von Parametern überprüfen und deren Standardwerte ändern.

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

Die Logik in der SQL-Prozedur überprüft die Eingabeparameter, wenn die Prozedur aufgerufen wird.

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.

Verschachtelung gespeicherter Prozeduren

Wenn gespeicherte Prozeduren andere gespeicherte Prozeduren aufrufen oder dynamische SQL ausführen, wird die innere gespeicherte Prozedur oder der Codeaufruf als geschachtelt bezeichnet. Ein Beispiel für eine geschachtelte Prozedur ist im folgenden Code dargestellt:

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

Diese Prozedur akzeptiert einen Parameter, der einen Namen darstellt, und ruft dann andere Prozeduren auf, um die Objekte mit diesem Namen abzulegen. Synapse SQL-Pool unterstützt bis zu acht maximale Verschachtelungsebenen. Diese Funktion unterscheidet sich geringfügig von SQL Server. Die Schachtelungsebene in SQL Server ist 32.

Der Aufruf der gespeicherten Prozedur auf oberster Ebene entspricht der Schachtelungsebene 1.

EXEC clean_up 'mytest'

Wenn die gespeicherte Prozedur auch einen weiteren EXEC-Aufruf vornimmt, erhöht sich die Schachtelungsebene auf zwei.

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

Wenn die zweite Prozedur dann einige dynamische SQL-Vorgänge ausführt, erhöht sich die Schachtelungsebene auf drei.

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

EINFÜGEN.. AUSFÜHREN

Der bereitgestellte Synapse SQL-Pool ermöglicht es Ihnen nicht, das Resultset einer gespeicherten Prozedur mit einer INSERT-Anweisung zu nutzen. Es gibt einen alternativen Ansatz, den Sie verwenden können. Ein Beispiel finden Sie im Artikel zu temporären Tabellen für den bereitgestellten Synapse SQL-Pool.

Einschränkungen

Es gibt einige Aspekte von Transact-SQL gespeicherten Prozeduren, die in Synapse SQL nicht implementiert sind, z. B.:

Verwandte Inhalte

Weitere Hinweise zur Entwicklung finden Sie in der Entwicklungsübersicht.