Разработка таблиц с помощью выделенного пула SQL в Azure Synapse Analytics
В этой статье приводятся основные ключевые понятия, связанные с разработкой таблиц в выделенном пуле SQL.
Определение категории таблицы
При схеме типа "звезда" данные упорядочиваются в таблицы фактов и измерений. Некоторые таблицы используются для интеграции или хранения данных до того, как они перейдут в таблицу фактов или измерений. При конструировании таблицы решите, к какой таблице относятся данные: фактов, измерений или интеграции. Это решение влияет на соответствующую структуру и распределение таблицы.
Таблицы фактов содержат количественные данные, которые обычно создаются в транзакционной системе, а затем загружаются в выделенный пул SQL. Например, розничное предприятие ежедневно создает транзакции продаж, а затем загружает данные в таблицу фактов в выделенном пуле SQL для анализа.
Таблицы измерений содержат данные атрибутов, которые могут измениться, хотя обычно это происходит редко. Например, имя и адрес клиента хранятся в таблице измерений и обновляются только при изменении профиля клиента. Чтобы минимизировать размер большой таблицы фактов, необязательно включать имя и адрес клиента в каждой строке. Вместо этого в таблице фактов и таблице измерений может совместно использоваться идентификатор клиента. Запрос может объединить две таблицы, чтобы связать профиль и транзакции клиента.
Таблицы интеграции служат для интеграции или промежуточного размещения данных. Можно создать таблицу интеграции в виде обычной таблицы, внешней таблицы или временной таблицы. Например, вы можете загружать данные в промежуточную таблицу, выполнять преобразования данных в режиме промежуточного размещения, а затем вставлять их в рабочую таблицу.
Имена схем и таблиц
Схемы удобно применять группировки таблиц, используемых схожим образом. При переносе нескольких баз данных из локального решения в выделенный пул SQL рекомендуется перенести все таблицы фактов, измерений и интеграции в одну схему в выделенном пуле SQL.
Например, можно сохранить все таблицы в примере выделенного пула SQL WideWorldImportersDW в рамках одной схемы, которая называется wwi. В следующем коде создается пользовательская схема с именем wwi.
CREATE SCHEMA wwi;
Чтобы показать организацию таблиц в выделенном пуле SQL, можно использовать в качестве префиксов имен таблиц значения fact, dim и int. В следующей таблице показаны некоторые имена схем и таблиц для WideWorldImportersDW.
| Таблица WideWorldImportersDW | Тип таблицы | Выделенный пул SQL |
|---|---|---|
| City | Измерение | wwi.DimCity |
| Заказ | Факты | wwi.FactOrder |
Сохраняемость таблицы
Данные таблиц хранятся на постоянной основе в службе хранилища Azure, временно — в службе хранилища Azure или в хранилище данных, являющемся внешним по отношению к выделенному пулу SQL.
Обычная таблица
Данные обычной таблицы хранятся в службе хранилища Azure в составе выделенного пула SQL. Таблица и данные сохраняются независимо от того, открыт ли сеанс. В следующем примере создается обычная таблица с двумя столбцами.
CREATE TABLE MyTable (col1 int, col2 int );
Временная таблица
Временная таблица существует, только пока отрыт сеанс. Если вы используете такие таблицы, другие пользователи не смогут видеть ваши временные результаты. Кроме того, уменьшается потребность в очистке.
Для обеспечения высокой производительности для временных таблиц используются локальное хранилище. Дополнительные сведения см. в статье Временные таблицы.
Внешняя таблица
Внешняя таблица указывает на данные, расположенные в большом двоичном объекте службы хранилища Azure или Azure Data Lake Store. При использовании с инструкцией CREATE TABLE AS SELECT данные, выбранные из внешней таблицы, импортируются в выделенный пул SQL.
Сами внешние таблицы можно использовать для загрузки данных. Руководство по загрузке см. в статье Загрузка данных из хранилища BLOB-объектов Azure в хранилище данных SQL Azure с помощью PolyBase.
Типы данных
Выделенный пул SQL поддерживает самые распространенные типы данных. Список поддерживаемых типов данных представлен в справочнике по типах данных в инструкции CREATE TABLE. Рекомендации по использованию типов данных см. в статье Руководство по определению типов данных для таблиц в хранилище данных SQL.
Распределенные таблицы
Основополагающая функция выделенного пула SQL — это хранение и обработка таблиц с помощью распределений. Выделенный пул SQL поддерживает три метода распространения данных: циклический перебор (по умолчанию), хэширование и репликацию.
Таблицы с хэш-распределением
При использовании таблицы с хэш-распределением строки распределяются по значению в столбце распределения. Таблица с хэш-распределением обеспечивает высокую производительность при выполнении запросов к больших таблицам. При выборе столбца распределения необходимо учитывать несколько факторов.
Дополнительные сведения см. в статье Руководство по проектированию распределенных таблиц в хранилище данных SQL Azure.
Реплицированные таблицы
Копия реплицированной таблицы хранится на каждом вычислительном узле. Запросы в реплицированных таблицах выполняются быстро, поскольку объединения в них не требуют перемещения данных. Тем не менее репликация требует дополнительного места и она не целесообразна для больших таблиц.
Дополнительные сведения см. в статье Руководство по проектированию для использования реплицированных таблиц в хранилище данных SQL Azure.
Таблицы с распределением методом циклического перебора
Строки таблицы с распределением методом циклического перебора распределяются равномерно по всем распределениям. Строки распределяются случайным образом. Загрузка данных в таблицу с распределением методом циклического перебора происходит быстро. Обратите внимание, запросы могут требовать больше перемещений данных, чем при других методах распределения.
Дополнительные сведения см. в статье Руководство по проектированию распределенных таблиц в хранилище данных SQL Azure.
Общие методы распределения для таблиц
Вариант распределения таблицы часто зависит от категории таблицы.
| Категории таблицы | Рекомендуемый вариант распределения |
|---|---|
| Факты | Используйте хэш-распределение с кластеризованным индексом columnstore. Производительность улучшается, когда две хэш-таблицы объединены по одному столбцу распределения. |
| Измерение | Используйте репликацию для небольших таблиц. Если таблицы слишком велики для хранения на каждом вычислительном узле, используйте хэш-распределение. |
| Промежуточная | Используйте циклический перебор для промежуточных таблиц. Загрузка с помощью инструкции CTAS выполняется быстро. Когда данные окажутся в промежуточной таблице, используйте INSERT...SELECT, чтобы переместить данные в рабочие таблицы. |
Примечание
Рекомендации по оптимальной стратегии распределения таблиц для использования на основе рабочих нагрузок см. в разделе Помощнике по распространению Azure Synapse SQL.
Разделы таблицы
Секционированная таблица хранит данные и выполняет операции со строками таблицы в соответствии с диапазонами данных. Например, таблицу можно разделить по дню, месяцу или году. Вы можете улучшить производительность запросов путем исключения секций, что ограничивает проверку запросов к данным в секции. Вы также можете управлять данными путем переключения разделов. Так как данные в пуле SQL уже распределены, слишком большое количество разделов может замедлить производительность запросов. Дополнительные сведения см. в статье Секционирование таблиц в хранилище данных SQL. При переключении секций в секции таблицы, которые не являются пустыми, рекомендуется использовать параметр TRUNCATE_TARGET в инструкции ALTER TABLE, если существующие данные должны быть усечены. Приведенный ниже код выполняет переключение преобразованных ежедневных данных на SalesFact, перезаписывая все имеющиеся данные.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Индексы columnstore
По умолчанию в выделенном пуле SQL таблицы хранятся в виде кластеризованных индексов columnstore. Такая форма хранения данных обеспечивает высокое сжатие данных и производительность запросов в больших таблицах.
Кластеризованный индекс columnstore обычно является лучшим выбором, но в некоторых случаях подходящей структурой хранения являются кластеризованный индекс или куча.
Совет
Таблицы-кучи особенно удобны для загрузки временных данных, таких как промежуточные таблицы, которые преобразуются в окончательные.
Список функций индексов columnstore см. в статье Новые возможности индексов columnstore. Сведения о повышении производительности индекса columnstore см. в статье Максимальное повышение качества группы строк для индекса columnstore.
Статистика
Оптимизатор запросов использует статистику уровня столбца при создании плана выполнения запроса.
Чтобы повысить производительность запросов, важно получить статистику по отдельным столбцам, особенно столбцам, используемым в объединениях запросов. Статистика создается автоматически.
Обновление статистики не происходит автоматически. Обновите статистику после добавления или изменения значительного числа строк. Например, обновите статистику после загрузки. Дополнительные сведения см. в статье Управление статистикой таблиц в хранилище данных SQL.
Первичный ключ и уникальный ключ
PRIMARY KEY поддерживается только при одновременном использовании NONCLUSTERED и NOT ENFORCED. Ограничение UNIQUE поддерживается только при использовании NOT ENFORCED. Проверьте ограничения для таблиц выделенного пула SQL.
Команды для создания таблиц
Вы можете создать пустую таблицу. Вы также можете создать и заполнить таблицу результатами инструкции Select. Ниже приведены команды T-SQL для создания таблицы.
| Инструкция Т-SQL | Описание |
|---|---|
| CREATE TABLE | Создает пустую таблицу, определив все столбцы и параметры таблицы. |
| CREATE EXTERNAL TABLE | Создает внешнюю таблицу. Определение таблицы хранится в выделенном пуле SQL. Данные таблицы хранятся в хранилище BLOB-объектов Azure или в Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Задает новую таблицу с результатами инструкции Select. Столбцы и типы данных таблицы основаны на результатах инструкции Select. Чтобы импортировать данные, эта инструкция может выбрать данные из внешней таблицы. |
| CREATE EXTERNAL TABLE AS SELECT | Создает новую внешнюю таблицу, экспортируя результаты инструкции Select во внешнее расположение. Расположением является либо хранилище BLOB-объектов Azure, либо Azure Data Lake Store. |
Согласование исходных данных с выделенным пулом SQL
Таблицы выделенного пула SQL заполняются путем загрузки данных из другого источника данных. Для успешной загрузки число и типы данных столбцов исходных данных должны совпадать с определением таблицы в выделенном пуле SQL. Согласование данных может оказаться самой сложной частью разработки таблиц.
Если данные поступают из нескольких хранилищ данных, вы можете загрузить их в выделенный пул SQL и сохранить их в таблице интеграции. Когда данные находятся в таблице интеграции, вы можете использовать возможности выделенного пула SQL для выполнения операций преобразования. После подготовки данных их можно вставить в рабочие таблицы.
Неподдерживаемые функции таблиц
Выделенный пул SQL поддерживает многие, но не все функции таблицы, предлагаемые другими базами данных. В следующем списке содержатся некоторые функции таблицы, которые не поддерживаются в выделенном пуле SQL.
- Внешний ключ, проверка ограничений таблицы
- Вычисляемые столбцы
- Индексированные представления
- Последовательность
- Разреженные столбцы
- Суррогатные ключи (реализация посредством удостоверения);
- Синонимы
- Триггеры
- Уникальные индексы
- Определяемые пользователем типы
Запросы размера таблицы
Примечание
Для точного подсчета запросов в этом разделе убедитесь, что обслуживание индекса выполняется регулярно и после изменения больших объемов данных.
Простой способ определить пространство и строки, используемые таблицей в каждом из 60 распределений, — применить инструкцию DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Однако возможности команд DBCC достаточно ограничены. Динамические административные представления (DMV) отображают больше сведений, чем команды DBCC. Начните с создания этого представления.
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
GETDATE() AS [execution_time]
, DB_NAME() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]
, nt.[name] AS [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]
, tp.[distribution_policy_desc] AS [distribution_policy_name]
, c.[name] AS [distribution_column]
, nt.[distribution_id] AS [distribution_id]
, i.[type] AS [index_type]
, i.[type_desc] AS [index_type_desc]
, nt.[pdw_node_id] AS [pdw_node_id]
, pn.[type] AS [pdw_node_type]
, pn.[name] AS [pdw_node_name]
, di.name AS [dist_name]
, di.position AS [dist_position]
, nps.[partition_number] AS [partition_nmbr]
, nps.[reserved_page_count] AS [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]
, nps.[in_row_data_page_count]
+ nps.[row_overflow_used_page_count]
+ nps.[lob_used_page_count] AS [data_space_page_count]
, nps.[reserved_page_count]
- (nps.[reserved_page_count] - nps.[used_page_count])
- ([in_row_data_page_count]
+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]
, nps.[row_count] AS [row_count]
from
sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
ON nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
ON nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
ON nt.[object_id] = nps.[object_id]
AND nt.[pdw_node_id] = nps.[pdw_node_id]
AND nt.[distribution_id] = nps.[distribution_id]
AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
ON cdp.[object_id] = c.[object_id]
AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
[execution_time]
, [database_name]
, [schema_name]
, [table_name]
, [two_part_name]
, [node_table_name]
, [node_table_name_seq]
, [distribution_policy_name]
, [distribution_column]
, [distribution_id]
, [index_type]
, [index_type_desc]
, [pdw_node_id]
, [pdw_node_type]
, [pdw_node_name]
, [dist_name]
, [dist_position]
, [partition_nmbr]
, [reserved_space_page_count]
, [unused_space_page_count]
, [data_space_page_count]
, [index_space_page_count]
, [row_count]
, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]
, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]
, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]
, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]
, ([unused_space_page_count] * 8.0) AS [unused_space_KB]
, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]
, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]
, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]
, ([data_space_page_count] * 8.0) AS [data_space_KB]
, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]
, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]
, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]
, ([index_space_page_count] * 8.0) AS [index_space_KB]
, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]
, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]
, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
Сводка табличного пространства
Этот запрос возвращает количество строк и объем каждой таблицы. Оно позволяет узнать, какие таблицы занимают большего всего места, в каких таблицах применяется циклический перебор, какие таблицы реплицированы, а в каких используется хэш-распределение. Для таблиц с хэш-распределением запрос показывает столбец распределения.
SELECT
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
, COUNT(distinct partition_nmbr) as nbr_partitions
, SUM(row_count) as table_row_count
, SUM(reserved_space_GB) as table_reserved_space_GB
, SUM(data_space_GB) as table_data_space_GB
, SUM(index_space_GB) as table_index_space_GB
, SUM(unused_space_GB) as table_unused_space_GB
FROM
dbo.vTableSizes
GROUP BY
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
ORDER BY
table_reserved_space_GB desc
;
Табличное пространство по типу распределения
SELECT
distribution_policy_name
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;
Табличное пространство по типу индекса
SELECT
index_type_desc
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;
Сводка пространства распределения
SELECT
distribution_id
, SUM(row_count) as total_node_distribution_row_count
, SUM(reserved_space_MB) as total_node_distribution_reserved_space_MB
, SUM(data_space_MB) as total_node_distribution_data_space_MB
, SUM(index_space_MB) as total_node_distribution_index_space_MB
, SUM(unused_space_MB) as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY distribution_id
ORDER BY distribution_id
;
Дальнейшие действия
После создания таблиц для выделенного пула SQL переходите к загрузке данных в таблицу. Руководство по загрузке см. в разделе Загрузка данных в выделенный пул SQL, а также просмотрите стратегии загрузки данных для выделенного пула SQL в Azure Synapse Analytics.