Руководство по проектированию распределенных таблиц с использованием выделенного пула SQL в Azure Synapse Analytics

  • Статья

Эта статья включает рекомендации по проектированию таблиц с хэш-распределением и с распределением методом циклического перебора в выделенных пулах SQL.

В этой статье предполагается, что вы знакомы с основными понятиями, связанными с распределением данных и перемещением данных в выделенном пуле SQL. Дополнительные сведения см. в статье об архитектуре Azure Synapse Analytics.

Что такое распределенная таблица?

Распределенная таблица отображается как отдельная таблица, но ее строки фактически хранятся в 60 распределениях. Строки распределяются с помощью хэш-алгоритма или алгоритма циклического перебора.

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

Другой вариант хранения таблиц — репликация небольшой таблицы на все вычислительные узлы. Дополнительные сведения см. в статье Руководство по проектированию для использования реплицированных таблиц в хранилище данных SQL Azure. Чтобы быстро выбрать один из трех вариантов, ознакомьтесь с разделом "Распределенные таблицы" в обзоре таблиц.

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

  • Каков размер таблицы?
  • Как часто она обновляется?
  • Существуют ли в моем выделенном пуле SQL таблицы фактов и измерений?

Хэш-распределение

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

Распределенная таблица

Так как хэш-функция всегда назначает одинаковые значения в одно и то же распределение, SQL Analytics содержит встроенные сведения о расположении записей. В выделенном пуле SQL эти сведения используются для того, чтобы свести к минимуму перемещение данных во время выполнения запросов, что повышает их производительность.

Хэш-распределенные таблицы подходят для больших таблиц фактов в схеме типа "звезда". Они могут содержать очень большое количество строк и все равно обеспечивать высокую производительность. Существуют некоторые аспекты проектирования, помогающие достичь производительности, которую должна обеспечивать распределенная система. Выбор одного или нескольких хороших столбцов распределения — это первый из таких аспектов, который описан в этой статье.

Рассмотрите возможность использования хэш-распределенных таблиц, если:

  • Размер таблицы на диске превышает 2 ГБ.
  • Таблица содержит частые операции вставки, обновления и удаления.

Распределение методом циклического перебора

Таблица с распределением методом циклического перебора равномерно распределяет строки по всем распределениям. Строки произвольным образом назначаются в распределения. В отличие от хэш-распределенных таблиц, строки с одинаковыми значениями не обязательно назначаются в одно и то же распределение.

Таким образом, чтобы упорядочить данные перед разрешением запроса, системе иногда требуется вызывать операции перемещения данных. Это дополнительное действие может повлиять на производительность запросов. Например, для операции соединения с таблицей с распределением методом циклического перебора, как правило, требуется перегруппировать строки, что снижает производительность.

Рассмотрите возможность использования распределения методом циклического перебора для таблицы в следующих сценариях:

  • если это простая отправная точка для начала работы, так как используется по умолчанию;
  • если отсутствует очевидный ключ соединения;
  • если отсутствует подходящий для хэш-распределения таблицы столбец;
  • если таблица не использует общий ключ соединения с другими таблицами;
  • Если соединение менее важно, чем других соединения в запросе.
  • если таблица является временной.

В руководстве Загрузка набора данных о такси в Нью-Йорке приведен пример загрузки данных в промежуточную таблицу с циклическим распределением.

Выбор столбца распределения

В хэш-распределенной таблице имеется столбец распределения или набор таких столбцов, по которым создается хэш-ключ. Например, приведенный ниже код создает хэш-распределенную таблицу со столбцом распределения ProductKey.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

Хэш-распределение можно применять к нескольким столбцам для более равномерного распределения базовой таблицы. Распределение по нескольким столбцам позволяет выбрать до восьми столбцов для распределения. Это не только снижает неравномерное распределение данных с течением времени, но и повышает производительность запросов. Пример:

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Примечание

Распределение по нескольким столбцам в Azure Synapse Analytics можно включить, изменив уровень совместимости базы данных на 50 с помощью этой команды. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Дополнительные сведения о настройке уровня совместимости базы данных см. в разделе ALTER DATABASE SCOPED CONFIGURATION. Дополнительные сведения о распределениях по нескольким столбцам см. в разделах CREATE MATERIALIZED VIEW, CREATE TABLE или CREATE TABLE AS SELECT.

Данные, хранящиеся в столбцах распределения, можно обновлять. Обновления данных в столбцах распределения может привести к операции перетасовки данных.

Выбор столбца или столбцов распределения — очень важное решение при проектировании, так как значения в этом столбце или в этих столбцах определяют, как распределяются строки. Лучший выбор зависит от нескольких факторов и обычно включает в себя компромиссы. Выбранный столбец распределения или набор столбцов нельзя изменить. Если вы не выберете удачный столбец с первого раза, можно выполнить инструкцию CREATE TABLE AS SELECT (CTAS), чтобы повторно создать таблицу с более подходящим столбцом распределения.

Выбор столбца распределения с данными, которые распределены равномерно

Для достижения наилучшей производительности все распределения должны содержать примерно одинаковое число строк. Если одно или несколько распределений содержат непропорционально большое количество строк, то некоторые распределения будут завершать свою часть параллельного запроса раньше прочих. Так как запрос невозможно выполнить до завершения обработки всех распределений, каждый запрос выполняется со скоростью обработки самого медленного распределения.

  • Неравномерное распределение данных означает, что данные неравномерно распределяются между распределениями.
  • Неравномерная обработка означает, что при выполнении параллельных запросов некоторые распределения обрабатываются дольше, чем другие. Это может произойти, если данные распределены неравномерно.

Чтобы сбалансировать параллельную обработку, столбец распределения должен соответствовать следующим условиям.

  • Содержит много уникальных значений. Может содержать дублирующиеся значения. Все строки с одинаковым значением назначаются в одно и то же распределение. Поскольку дистрибутивов 60, некоторые из них могут иметь больше одного уникального значения, а другие могут не содержать ни одного.
  • Не содержит значений NULL или содержит всего несколько значений NULL. Яркий пример: если в столбце или столбцах распределения есть только значения NULL, то все строки назначаются в одно и то же распределение. В результате обработка запросов будет использовать только одно распределение, и преимущества параллельной обработки не будут реализованы.
  • Не является столбцом даты. Все данные за один день помещаются в одно распределение, то есть записи будут кластеризованы по датам. Если несколько пользователей использует фильтр по одной дате (например, по текущему дню), то всю обработку выполняет только одно из 60-ти распределений.

Выбор столбца распределения, который сводит к минимуму перемещение данных

Для получения корректных результатов запросы могут перемещать данные с одного вычислительного узла на другой. Перемещение данных обычно происходит в том случае, когда запросы содержат операции соединения и агрегирования с распределенными таблицами. Выбор столбца распределения или набора столбцов, которые минимизируют перемещение данных, — одна из самых важных стратегий для оптимизации производительности выделенного пула SQL.

Чтобы минимизировать перемещение данных, выберите столбец распределения или набор столбцов со следующими характеристиками.

  • Используется в предложениях JOIN, GROUP BY, DISTINCT, OVER и HAVING. Когда с двумя большими таблицами фактов часто выполняются операции соединения, производительность запросов повышается при распределении этих таблицы по одному из столбцов соединения. Если таблица не используется в операциях соединения, рассмотрите возможность распределения таблицы по одному или нескольким столбцам, которые часто используются в предложении GROUP BY.
  • Не используется в предложениях WHERE. Если предложение запроса WHERE и столбцы распределения таблицы находятся в одном столбце, запрос может столкнуться с большим отклонением данных, что приводит к тому, что нагрузка на обработку падает только на несколько распределений. Это влияет на производительность запросов, в идеале многие распределения совместно используют нагрузку обработки.
  • Не является столбцом даты. В предложениях WHERE часто используется фильтрация по дате. В этом случае вся обработка может выполняться только в нескольких дистрибутивах, влияющих на производительность запросов. В идеале многие дистрибутивы совместно используют нагрузку обработки.

После завершения проектирования хэш-распределенной таблицы следующим шагом является загрузка данных в эту таблицу. Руководство по загрузке представлено в обзоре загрузки.

Как определить, удачно ли выбран вариант для распределения

После загрузки данных в хэш-распределенную таблицу проверьте, насколько равномерно распределены строки между 60 распределениями. Отличие числа строк на распределение может достигать 10 % без заметного влияния на производительность. Рассмотрим следующие темы, чтобы оценить столбцы распределения.

Как выявить неравномерное распределение данных в таблице

Быстро проверить наличие неравномерного распределения данных можно с помощью DBCC PDW_SHOWSPACEUSED. Приведенный ниже код SQL возвращает число строк таблицы, которые хранятся в каждом из 60 распределений. Для обеспечения сбалансированной производительности строки в распределенной таблице должны размещаться равномерно по всем распределениям.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Вот как можно определить, в каких таблицах неравномерное распределение данных не превышает 10 %.

  1. Создайте представление dbo.vTableSizes, которое показано в статье Общие сведения о таблицах.
  2. Выполните следующий запрос:
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

Проверка планов запроса для перемещения данных

Удачно выбранный набор столбцов распределения обеспечивает минимальное перемещение данных при операциях соединения и агрегирования. Это влияет на способ программирования соединений. Чтобы обеспечить минимальное перемещение данных при операции соединения между двумя хэш-распределенными таблицами, один из используемых для соединения столбцов должен быть столбцом распределения (или набором столбцов распределения). Когда выполняется операция соединения с двумя хэш-распределенными таблицами по столбцу распределения того же типа данных, соединение не требует перемещения данных. Операции соединения могут использовать дополнительные столбцы без необходимости перемещения данных.

Вот как можно избежать перемещения данных во время соединения.

  • Таблицы, входящие в соединение, должны быть распределены по методу хэш-распределения по одному из столбцов соединения.
  • Типы данных столбцов соединения в обеих таблицах должны совпадать.
  • Объединение столбцов необходимо выполнять с помощью оператора equals.
  • Типом соединения не может быть CROSS JOIN.

Чтобы узнать, вызывают ли запросы перемещение данных, можно просмотреть план запроса.

Устранение проблемы со столбцом распределения

Устранять все случаи неравномерного распределения данных не обязательно. В основе распределения данных лежит поиск баланса между уменьшением неравномерности распределения данных и перемещения данных. Не всегда можно уменьшить и неравномерность распределения данных, и перемещение данных. Иногда преимущество минимального перемещения данных может компенсировать последствия неравномерного распределения данных.

Чтобы решить, следует ли устранять смещение данных в таблице, нужно узнать как можно больше об объемах данных и запросах в рамках рабочей нагрузки. Можно использовать инструкции в статье Мониторинг рабочей нагрузки с помощью динамических административных представлений, чтобы отслеживать влияние неравномерного распределения на производительность запросов. В частности, определите время, затрачиваемое на выполнение больших запросов для отдельных распределений.

Так как столбец распределения невозможно изменить для существующей таблицы, для устранения проблем с неравномерным распределением данных обычно таблица создается заново, с другим столбцом распределения.

Повторное создание таблицы с новым набором столбцов распределения

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

Сначала выполните CREATE TABLE AS SELECT (CTAS) для новой таблицы с новым ключом. Затем повторно создайте статистику и, наконец, поменяйте имена у таблиц, чтобы новая заняла место старой.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

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

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