ストアド プロシージャにデータ変換をカプセル化する

  • 4 分

データを変換する必要がある場合は、いつでもスクリプトで CREATE EXTERNAL TABLE AS SELECT (CETAS) ステートメントを実行できますが、ストアド プロシージャに変換操作をカプセル化することをお勧めします。 この方法を使用すると、1 回のプロシージャ呼び出しで、パラメーターを指定し、出力を取得し、追加のロジックを含めることができるため、データ変換操作が簡単になります。

たとえば、次のコードでは、指定した年の注文データを使用して外部テーブルを再作成する前にその外部テーブルが既に存在する場合には、その外部テーブルを削除するストアド プロシージャが作成されます。

CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN

	-- Drop the table if it already exists
	IF EXISTS (
                SELECT * FROM sys.external_tables
                WHERE name = 'SpecialOrders'
            )
        DROP EXTERNAL TABLE SpecialOrders

	-- Create external table with special orders
	-- from the specified year
	CREATE EXTERNAL TABLE SpecialOrders
		WITH (
			LOCATION = 'special_orders/',
			DATA_SOURCE = files,
			FILE_FORMAT = ParquetFormat
		)
	AS
	SELECT OrderID, CustomerName, OrderTotal
	FROM
		OPENROWSET(
			BULK 'sales_orders/*.csv',
			DATA_SOURCE = 'files',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0',
			HEADER_ROW = TRUE
		) AS source_data
	WHERE OrderType = 'Special Order'
	AND YEAR(OrderDate) = @order_year
END

Note

既に説明したように、既存の外部テーブルを削除しても、そのデータ ファイルを含むフォルダーは削除されません。 ストアド プロシージャを実行する前にターゲット フォルダーが存在する場合、そのターゲット フォルダーを明示的に削除する必要があります。このようにしないと、エラーが発生します。

ストアド プロシージャには、Transact-SQL ロジックのカプセル化に加えて、次のような利点があります。

クライアントからサーバーへのネットワーク トラフィックを削減する

プロシージャ内のコマンドは、コードの 1 つのバッチとして実行されます。これにより、サーバーとクライアントの間のネットワーク トラフィックを大幅に削減できます。これは、プロシージャを実行するための呼び出しのみがネットワークで送信されるためです。

セキュリティ境界を提供する

ユーザーとクライアント プログラムが基になるデータベース オブジェクトの直接アクセス許可を持っていない場合でも、プロシージャによって、複数のユーザーとクライアント プログラムがそれらの基になるデータベース オブジェクトに対して操作を実行できます。 プロシージャによって、実行されるプロセスとアクティビティが制御され、基になるデータベース オブジェクトが保護されます。これにより、個々のオブジェクト レベルでアクセス許可を付与する必要がなくなり、セキュリティ層が簡素化されます。

メンテナンスを容易にする

データ変換に関連するロジックまたはファイル システムの場所の変更を、ストアド プロシージャのみに適用できます。このとき、クライアント アプリケーションやその他の呼び出し元の関数を更新する必要はありません。

パフォーマンスの向上

ストアド プロシージャは初回実行時にコンパイルされます。その結果の実行プランは、キャッシュに保持され、同じストアド プロシージャの後続の実行で再利用されます。 このため、プロシージャの処理には時間がかかりません。