Руководство по проектированию распределенных таблиц с использованием выделенного пула 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 дистрибутивов, некоторые дистрибутивы могут иметь > 1 уникальные значения, а другие могут заканчиваться нулевыми значениями.
- Не содержит значений NULL или содержит всего несколько значений NULL. Например, если все значения в столбцах распределения имеют значение NULL, все строки назначаются одному распределению. В результате обработка запросов будет использовать только одно распределение, и преимущества параллельной обработки не будут реализованы.
- Не является столбцом даты. Все данные за один день помещаются в одно распределение, то есть записи будут кластеризованы по датам. Если несколько пользователей фильтруют по одной дате (например, сегодняшняя дата), то только 1 из 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 %.
- Создайте представление
dbo.vTableSizes, которое показано в статье Общие сведения о таблицах. - Выполните приведенный ниже запрос:
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];
Связанный контент
Чтобы создать распределенную таблицу, воспользуйтесь одной из следующих инструкций: