Synapse SQL の一時テーブル

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

この記事では、Synapse SQL 内のセッションレベルの一時テーブルの原則を中心に、一時テーブルの基本的な利用方法について説明します。

専用 SQL プールとサーバーレス SQL プールの両方のリソースで、一時テーブルを利用できます。サーバーレス SQL プールにはいくつかの制限事項があり、それについては、この記事の最後で説明しています。

一時テーブル

特に、中間結果が一時的なものである変換中にデータを処理する場合に、一時テーブルが役立ちます。 Synapse SQL では、一時テーブルはセッションレベルで存在します。一時テーブルは、それが作成されたセッションにのみ表示されます。そのため、そのセッションが終了すると自動的に削除されます。

専用 SQL プール内の一時テーブル

専用 SQL プールリソースでは、一時テーブルの結果がリモートストレージではなくローカルストレージに書き込まれるため、パフォーマンス上の利点があります。

一時テーブルを作成する

一時テーブルは、テーブル名にプレフィックス # を付けることで作成できます。次に例を示します。

CREATE TABLE #stats_ddl
(
    [schema_name]        NVARCHAR(128) NOT NULL
,    [table_name]            NVARCHAR(128) NOT NULL
,    [stats_name]            NVARCHAR(128) NOT NULL
,    [stats_is_filtered]     BIT           NOT NULL
,    [seq_nmbr]              BIGINT        NOT NULL
,    [two_part_name]         NVARCHAR(260) NOT NULL
,    [three_part_name]       NVARCHAR(400) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)

一時テーブルは、次のようにまったく同じ手法で CTAS を利用して作成することもできます。

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
;

Note

CTAS は強力なコマンドであり、トランザクションログ領域を効率的に利用するという長所があります。

一時テーブルを削除する

新しいセッションが作成されたとき、一時テーブルは存在しません。ただし、同じ名前で一時テーブルを作成する同じストアドプロシージャを呼び出す場合、CREATE TABLE ステートメントを正常に実行するために、DROP による簡単な既存チェックを使用します。

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

コードの一貫性のために、テーブルと一時テーブルの両方にこのパターンを利用することが推奨されます。また、一時テーブルの利用を終えたら、DROP TABLE を使用してそれを削除することもお勧めします。

ストアドプロシージャの開発では、一般的に、オブジェクトが消去されるように、プロシージャの終わりに削除コマンドがバンドルされています。

DROP TABLE #stats_ddl

コードをモジュール化する

一時テーブルはユーザーセッション内のどこでも使用できます。この機能を利用して、アプリケーションコードをモジュール化することができます。試してみるには、次のストアドプロシージャで、データベース内のすべての統計を統計名で更新する DDL を生成します。

CREATE PROCEDURE    [dbo].[prc_sqldw_update_stats]
(   @update_type    tinyint -- 1 default 2 fullscan 3 sample 4 resample
    ,@sample_pct     tinyint
)
AS

IF @update_type NOT IN (1,2,3,4)
BEGIN;
    THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
SELECT
    CASE @update_type
    WHEN 1
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
    WHEN 2
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
    WHEN 3
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
    WHEN 4
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
    END AS [update_stats_ddl]
,   [seq_nmbr]
FROM    #stats_ddl
;
GO

この段階で、発生した唯一のアクションは、#stats_ddl 一時テーブルを生成するストアドプロシージャの作成です。 #stats_ddl が既に存在する場合は、このストアドプロシージャによって削除されます。この削除により、一時テーブルがセッション内で複数回実行された場合に失敗することがなくなります。

ストアドプロシージャの最後に DROP TABLE がないため、ストアドプロシージャが完了したときに、作成されたテーブルはそのまま残り、ストアドプロシージャの外部で読み取ることができます。

他の SQL Server データベースとは対照的に、Synapse SQL では、一時テーブルを作成したプロシージャの外部でその一時テーブルを使用できます。専用 SQL プールを使用して作成された一時テーブルは、セッション内のどこでも使用できます。その結果、以下のサンプルに示すように、コードのモジュール性が高まり、コードを管理しやすくなります。

EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''

WHILE @i <= @t
BEGIN
    SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

一時テーブルの制限事項

専用 SQL プールには、一時テーブルの実装上の制限事項がいくつかあります。

セッションを範囲とした一時テーブルのみがサポートされています。グローバル一時テーブルはサポートされていません。
一時テーブルでビューを作成することはできません。
一時テーブルは、ハッシュまたはラウンドロビンディストリビューションでのみ作成できます。レプリケートされた一時テーブルのディストリビューションはサポートされていません。

サーバーレス SQL プール内の一時テーブル

サーバーレス SQL プール内の一時テーブルはサポートされていますが、その使用法は制限されています。一時テーブルは、ファイルを対象とするクエリでは使用できません。

たとえば、ストレージ内のファイルからのデータと一時テーブルを結合することはできません。一時テーブルの数は 100 個に制限されており、合計サイズは 100 MB に制限されています。

次のステップ

テーブルの開発について詳しくは、Synapse SQL リソースを使用したテーブルの設計に関する記事をご覧ください。

次の方法で共有