Azure Synapse Analytics での Synapse SQL を使用したストアドプロシージャ

[アーティクル]
06/01/2023

Synapse SQL のプロビジョニングされたおよびサーバーレスのプールを使用すると、複雑なデータ処理ロジックを SQL ストアドプロシージャに配置できます。ストアドプロシージャは、SQL コードをカプセル化し、データウェアハウスのデータの近くに格納するための優れた方法です。ストアドプロシージャは、開発者がコードを管理しやすい単位にカプセル化して、ソリューションをモジュール化することに役立ち、コードの再利用性が大幅に促進されます。ストアドプロシージャの柔軟性をさらに高めるために、各ストアドプロシージャはパラメーターを受け入れることもできます。この記事では、ソリューション開発のための Synapse SQL プールでのストアドプロシージャの実装に関するヒントをいくつか紹介します。

ウィザードの内容

Synapse SQL では、SQL Server で使用される多数の T-SQL 機能がサポートされています。さらに重要なのは、ソリューションのパフォーマンスを最大限にするために使用できる、スケールアウト専用の機能が用意されていることです。この記事では、ストアドプロシージャに配置できる機能について説明します。

Note

プロシージャ本体では、Synapse SQL のセキュリティでサポートされている機能のみを使用できます。この記事では、ストアドプロシージャで使用できるオブジェクトとステートメントを確認します。これらの記事の例では、サーバーレスおよび専用の両方の領域で使用できる汎用機能が使用されています。この記事の最後にあるプロビジョニングおよびサーバーレスの Synapse SQL プールに関する追加の制限事項を参照してください。

SQL プールのスケールとパフォーマンスを維持するために、動作が異なる機能もあれば、サポートされていない機能もあります。

Synapse SQL でのストアドプロシージャ

次の例では、外部オブジェクトがデータベースに存在する場合に削除するプロシージャを確認できます。

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

これらのプロシージャは、プロシージャ名とパラメーターを指定できる EXEC ステートメントを使用して実行できます。

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 では、簡素化され、合理化されたストアドプロシージャの実装が提供されます。 SQL Server との最大の違いは、ストアドプロシージャがプリコンパイル済みコードではないことです。データウェアハウスでは、大量のデータに対してクエリを実行するのにかかる時間に比べて、短いコンパイル時間で済みます。より重要なことは、ストアドプロシージャのコードが大量のクエリに対して正しく最適化されることを確認することです。目標は、数時間、数分、(数ミリ秒ではなく) 数秒節約することです。したがって、ストアドプロシージャは、SQL ロジックのコンテナーと考えるとよいでしょう。

Synapse SQL でストアドプロシージャが実行されると、実行時に SQL ステートメントが解析され、変換され、最適化されます。このプロセスの中で、各ステートメントが分散クエリに変換されます。データに対して実行される SQL コードは、送信されるクエリとは異なります。

検証規則をカプセル化する

ストアドプロシージャを使用すると、SQL データベースに格納されている単一のモジュールで検証ロジックを見つけることができます。次の例では、パラメーターの値を検証し、それらの既定値を変更する方法を確認できます。

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

プロシージャが呼び出されると、sql プロシージャのロジックによって入力パラメーターが検証されます。


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.

ストアドプロシージャの入れ子

ストアドプロシージャが他のストアドプロシージャを呼び出すか、動的 SQL を実行する場合、内側のストアドプロシージャまたはコードの呼び出しは "入れ子になっている" といわれます。入れ子になったプロシージャの例を次のコードに示します。

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

このプロシージャは、何らかの名前を表すパラメーターを受け取り、他のプロシージャを呼び出して、この名前を持つオブジェクトを削除します。 Synapse SQL プールでは、最大 8 レベルの入れ子がサポートされます。この機能は SQL Server とは少し異なります。 SQL Server の入れ子レベルは 32 です。

最上位のストアドプロシージャの呼び出しは、入れ子レベル 1 になります。

EXEC clean_up 'mytest'

ストアドプロシージャから別の EXEC の呼び出しも行う場合、入れ子レベルは 2 に増えます。

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

次に 2 つ目の手順で何らかの動的 SQL を実行すると、入れ子レベルは 3 に増えます。

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

Note

現在、Synapse SQL では、@@NESTLEVEL はサポートされていません。入れ子レベルを追跡する必要があります。 8 つの入れ子レベルの制限を超過する可能性はほとんどありませんが、もし超過した場合は、入れ子レベルがこの制限内に収まるように、コードを修正する必要があります。

INSERT..EXECUTE

プロビジョニングされた Synapse SQL プールでは、INSERT ステートメントでストアドプロシージャの結果セットを使用することはできません。使用できる別の方法があります。例については、プロビジョニングされた Synapse SQL プールの一時テーブルに関する記事を参照してください。

制限事項

Synapse SQL に実装されていない Transact-SQL ストアドプロシージャには、次のようないくつかの側面があります。

機能/オプション	プロビジョニング済み	サーバーレス
一時ストアドプロシージャ	いいえ	はい
番号付きストアドプロシージャ	いいえ	いいえ
拡張ストアドプロシージャ	いいえ	いいえ
CLR ストアドプロシージャ	いいえ	いいえ
暗号化オプション	いいえ	はい
レプリケーションオプション	いいえ	いいえ
テーブル値パラメーター	いいえ	いいえ
読み取り専用パラメーター	いいえ	いいえ
既定のパラメーター	いいえ	はい
実行コンテキスト	いいえ	いいえ
Return ステートメント	いいえ	はい
INSERT INTO .. EXEC	いいえ	はい

次のステップ

開発についてのその他のヒントは、開発の概要に関するページをご覧ください。

次の方法で共有

Azure Synapse Analytics での Synapse SQL を使用したストアドプロシージャ

ウィザードの内容

Synapse SQL でのストアドプロシージャ

検証規則をカプセル化する

ストアドプロシージャの入れ子

INSERT..EXECUTE

制限事項

次のステップ

フィードバック

その他のリソース

次の方法で共有

Azure Synapse Analytics での Synapse SQL を使用したストアド プロシージャ

ウィザードの内容

Synapse SQL でのストアド プロシージャ

検証規則をカプセル化する

ストアド プロシージャの入れ子

INSERT..EXECUTE

制限事項

次のステップ

フィードバック

その他のリソース

Azure Synapse Analytics での Synapse SQL を使用したストアドプロシージャ

Synapse SQL でのストアドプロシージャ

ストアドプロシージャの入れ子