IDENTITY を使用して、Azure Synapse Analytics の専用 SQL プールで代理キーを作成する

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

この記事では、IDENTITY プロパティを使用して専用 SQL プール内のテーブルに代理キーを作成する場合の推奨事項と例を紹介します。

代理キーとは

テーブルの代理キーは、各行の一意の識別子を持つ列です。代理キーはテーブルデータからは生成されません。データモデラーは、データウェアハウスモデルを設計するときに、テーブルに代理キーを作成するのを好みます。 IDENTITY プロパティを使うと、この目的を簡単かつ効果的に達成でき、読み込みのパフォーマンスが影響を受けることもありません。

注意

Azure Synapse Analytics:

IDENTITY 値は各ディストリビューションで自動的に増加し、他のディストリビューションの IDENTITY 値とは重複しません。 Synapse の IDENTITY 値は、ユーザーが明示的に "SET IDENTITY_INSERT ON" と重複する値を挿入するか IDENTITY を再シードする場合は、一意であるとは限りません。詳細については、「CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)」を参照してください。
ディストリビューション列の UPDATE では、IDENTITY 値が一意であることは保証されません。ディストリビューション列の UPDATE 後に DBCC CHECKIDENT (Transact-SQL) を使用して、一意性を確認します。

IDENTITY 列があるテーブルを作成する

IDENTITY プロパティは、読み込みパフォーマンスに影響を与えずに、専用 SQL プール内のすべてのディストリビューションにスケールアウトするように設計されています。そのため、IDENTITY の実装はこれらの目標を達成するようになっています。

次のステートメントのような構文を使って、テーブルを最初に作成するときに、IDENTITY プロパティを持つようにテーブルを定義できます。

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL
,    C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

その後、INSERT..SELECT を使ってテーブルを設定します。

以降、このセクションでは、理解を深めるのに役立つ実装の詳細に注目します。

値の割り当て

IDENTITY プロパティでは、データウェアハウスの分散アーキテクチャにより、サロゲート値が割り当てられる順序は保証されません。 IDENTITY プロパティは、読み込みパフォーマンスに影響を与えずに、専用 SQL プール内のすべてのディストリビューションにスケールアウトするように設計されています。

次にその例を示します。

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1)    NOT NULL
,    C2 VARCHAR(30)                NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

この例では、2 つの行はディストリビューション 1 に格納されます。 1 番目の行の代理値は C1 列の 1 であり、2 番目の行の代理値は 61 です。これらの値はどちらも IDENTITY プロパティによって生成されたものです。ただし、値の割り当ては連続していません。この動作は仕様です。

非対称のデータ

データ型の値の範囲は、ディストリビューション全体に均等に分散されます。分散テーブルが非対称データによって悪影響を受ける場合、データ型に対して使用可能な値の範囲が早く不足する可能性があります。たとえば、すべてのデータが最終的に 1 つのディストリビューションに格納される場合、実質的にテーブルはそのデータ型の値の 60 分の 1 にのみアクセスすることになります。このため、IDENTITY プロパティは INT および BIGINT データ型だけに制限されます。

SELECT..INTO

既存の IDENTITY 列を選択して新しいテーブルにすると、次のいずれかの条件が満たされている場合を除き、新しい列は IDENTITY プロパティを継承します。

SELECT ステートメントに結合が含まれています。
複数の SELECT ステートメントが UNION を使用して結合されている。
IDENTITY 列が SELECT リストに複数回出現する。
IDENTITY 列が式の一部である。

これらの条件が 1 つでも満たされている場合は、列に IDENTITY プロパティは継承されず、代わりに NOT NULL として作成されます。

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) は、SELECT..INTO と同じ SQL Server 動作に従います。ただし、ステートメントの CREATE TABLE 部分の列定義で IDENTITY プロパティを指定することはできません。また、CTAS の SELECT 部分で IDENTITY 関数を使うこともできません。テーブルに値を設定するには、CREATE TABLE を使ってテーブルを定義した後、INSERT..SELECT で値を設定する必要があります。

IDENTITY 列に値を明示的に挿入する

専用 SQL プールでは SET IDENTITY_INSERT <your table> ON|OFF 構文がサポートされています。この構文を使って、IDENTITY 列に値を明示的に挿入できます。

多くのデータモデラーは、ディメンションの特定の行に定義済みの負の値を使うことを好みます。たとえば、-1 や "unknown member" 行です。

次のスクリプトでは、SET IDENTITY_INSERT を使ってこの行を明示的に追加する方法を示します。

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1
,   C2
)
VALUES (-1,'UNKNOWN')
;

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1
;

データの読み込み

IDENTITY プロパティが存在すると、データ読み込みコードに影響があります。ここでは、IDENTITY を使ってテーブルにデータを読み込む場合のいくつかの基本的なパターンを示します。

IDENTITY を使ってテーブルにデータを読み込んで代理キーを生成するには、テーブルを作成した後、INSERT..SELECT または INSERT..VALUES を使って読み込みを実行します。

次の例では基本的なパターンを示します。

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1)
,    C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1
;

SELECT *
FROM   dbo.T1
;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Note

現在は、IDENTITY 列のあるテーブルへのデータの読み込みに、CREATE TABLE AS SELECT を使うことはできません。

データの読み込みの詳細については、専用 SQL プール向けの抽出、読み込み、変換 (ELT) の設計と読み込みのベストプラクティスに関するページを参照してください。

システムビュー

sys.identity_columns カタログビューを使用して、IDENTITY プロパティを持つ列を識別できます。

データベーススキーマを理解しやすいように、次の例では sys.identity_column を他のシステムカタログビューと統合する方法を示します。

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

制限事項

次の場合、IDENTITY プロパティは使用できません。

列のデータ型が INT または BIGINT ではない場合
列が分散キーでもある場合
テーブルが外部テーブルである場合

次の関連する関数は、専用 SQL プールではサポートされません。

一般的なタスク

このセクションでは、IDENTITY 列を操作するときの一般的なタスクを実行するために使うことができるいくつかのサンプルコードを提供します。

以下のすべてのタスクで、列 C1 が IDENTITY です。

テーブルに割り当てられた最も高い値を見つける

分散テーブルに割り当てられた最も高い値を特定するには、MAX() 関数を使います。

SELECT MAX(C1)
FROM dbo.T1

IDENTITY プロパティのシードと増分を調べる

カタログビューで次のクエリを使って、テーブルの ID 増分とシード構成値を調べることができます。

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

次の方法で共有

IDENTITY を使用して、Azure Synapse Analytics の専用 SQL プールで代理キーを作成する

代理キーとは

IDENTITY 列があるテーブルを作成する

値の割り当て

非対称のデータ

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 列に値を明示的に挿入する

データの読み込み

システムビュー

制限事項

一般的なタスク

テーブルに割り当てられた最も高い値を見つける

IDENTITY プロパティのシードと増分を調べる

次のステップ

フィードバック

その他のリソース

次の方法で共有

IDENTITY を使用して、Azure Synapse Analytics の専用 SQL プールで代理キーを作成する

代理キーとは

IDENTITY 列があるテーブルを作成する

値の割り当て

非対称のデータ

SELECT..INTO

CREATE TABLE AS SELECT

IDENTITY 列に値を明示的に挿入する

データの読み込み

システム ビュー

制限事項

一般的なタスク

テーブルに割り当てられた最も高い値を見つける

IDENTITY プロパティのシードと増分を調べる

次のステップ

フィードバック

その他のリソース

システムビュー