Использование IDENTITY для создания суррогатных ключей с помощью выделенного пула SQL в Azure Synapse Analytics

  • Статья

В этой статье приведены рекомендации и примеры использования свойства IDENTITY для создания суррогатных ключей для таблиц в выделенном пуле SQL.

Что такое суррогатный ключ

Суррогатный ключ таблицы представляет собой столбец с уникальным идентификатором каждой строки. Ключ не генерируется из данных таблицы. Разработчики моделей данных создают суррогатные ключи для таблиц, когда проектируют модели хранилища данных. Чтобы просто и эффективно достичь этой цели, не оказывая влияния на производительность загрузки, можно использовать свойство IDENTITY.

Примечание

В Azure Synapse Analytics:

  • Значение IDENTITY увеличивается отдельно в каждом распределении и не перекрывается со значениями IDENTITY в других дистрибутивах. Значение IDENTITY в Synapse не обязательно будет уникальным, если пользователь явно вставляет повторяющееся значение с помощью конструкции SET IDENTITY_INSERT ON или повторно заполняет свойство IDENTITY. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL) IDENTITY (свойство).
  • UPDATE в столбце распределения не гарантирует уникальность значения IDENTITY. Используйте инструкцию DBCC CHECKIDENT (Transact-SQL) после update в столбце распространения для проверки уникальности.

Создание таблицы со столбцом 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');

В приведенном выше примере две строки попали в распределение 1. У первой строки есть суррогатное значение 1 в столбце C1, а у второй строки есть суррогатное значение 61. Оба эти значения были созданы свойством IDENTITY. Тем не менее распределение этих значений не является связанным. В этом весь замысел.

Неравномерные данные

Диапазон значений определенного типа данных равномерно размещается в распределениях. Если распределенная таблица содержит неравномерные данные, то диапазон значений, доступных для типа данных, может быть преждевременно исчерпан. Например, если все данные попадают в отдельное распределение, фактически таблица имеет доступ к только одной шестидесятой части значений этого типа данных. По этой причине свойство IDENTITY ограничено следующими типами данных: INT и BIGINT.

SELECT..INTO

При выборке существующего столбца IDENTITY в новую таблицу новый столбец наследует свойство IDENTITY, если только не выполняется одно из следующих условий:

  • Инструкция SELECT содержит соединение.
  • несколько инструкций SELECT соединены при помощи UNION;
  • столбец IDENTITY более одного раза указан в списке инструкции SELECT;
  • столбец IDENTITY является частью выражения.

Если любое из этих условий выполняется, столбец создается как NOT NULL и не наследует свойство IDENTITY.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) выполняется SQL Server так же, как и SELECT..INTO. Тем не менее невозможно указать свойство IDENTITY в определении столбца в части CREATE TABLE инструкции. Невозможно также использовать функцию IDENTITY в части SELECT инструкции CTAS. Для заполнения таблицы необходимо использовать CREATE TABLE, чтобы определить таблицу, а затем указать INSERT..SELECT, чтобы ее заполнить.

Вставка значений в столбец IDENTITY явным образом

Выделенный пул SQL поддерживает синтаксис SET IDENTITY_INSERT <your table> ON|OFF. Этот синтаксис позволяет явно вставить значения в столбец IDENTITY.

Многие разработчики моделей данных используют в измерениях предопределенные отрицательные значения для определенных строк. Например, значение -1 или строка "неизвестный элемент".

Приведенный ниже сценарий показывает, как явным образом добавить эту строку с помощью инструкции 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');

Примечание

В настоящее время невозможно использовать CREATE TABLE AS SELECT при загрузке данных в таблицу со столбцом IDENTITY.

Дополнительные сведения о загрузке данных см. в статьях Разработка процесса извлечения, загрузки и преобразования (ELT) для выделенного пула SQL и Рекомендации по загрузке данных.

Системные представления

Можно использовать представление каталога 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

Представления каталога можно использовать для обнаружения значения шага приращения идентификатора и начального значения конфигурации для таблицы. Для этого можно выполнить приведенный ниже запрос.

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'
;

Дальнейшие действия