Разработка таблиц с помощью Synapse SQL в Azure Synapse Analytics

Этот документ содержит основные сведения о создании таблиц с выделенным пулом SQL и бессерверным пулом SQL Server.

Бессерверный пул SQL — это служба для создания запросов к данным в озере данных. Она не включает в себя локальное хранилище для приема данных. Выделенный пул SQL представляет коллекцию аналитических ресурсов, которые подготавливаются при использовании Synapse SQL. Размер выделенного пула SQL определяется единицами использования хранилища данных (DWU).

В следующей таблице перечислены разделы, относящиеся к выделенному пулу SQL и бессерверному пулу SQL:

Раздел	Выделенный пул SQL	Бессерверный пул SQL
Определение категории таблицы	Да	Нет
имена схем;	Да	Да
Имена таблиц	Да	Нет
Сохраняемость таблицы	Да	Нет
Обычная таблица	Да	Нет
Временная таблица	Да	Да
Внешняя таблица	Да	Да
Типы данных	Да	Да
Распределенные таблицы	Да	Нет
Таблицы с хэш-распределением	Да	Нет
Реплицированные таблицы	Да	Нет
Таблицы с распределением методом циклического перебора	Да	Нет
Общие методы распределения для таблиц	Да	Нет
Секции	Да	Да
Индексы columnstore	Да	Нет
Статистика	Да	Да
Первичный ключ и уникальный ключ	Да	Нет
Команды для создания таблиц	Да	Нет
Согласование исходных данных с хранилищем данных	Да	Нет
Неподдерживаемые функции таблиц	Да	Нет
Запросы размера таблицы	Да	Нет

Определение категории таблицы

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

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

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

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

имена схем;

Схемы отлично подходят для группирования объектов, которые используются аналогичным образом. В следующем коде создается пользовательская схема с именем WWI.

CREATE SCHEMA wwi;

Имена таблиц

При переносе нескольких баз данных из локального решения в выделенный пул SQL рекомендуется перенести все таблицы фактов, измерений и интеграции в одну схему пула SQL. Например, можно сохранить все таблицы в примере хранилища данных WideWorldImportersDW в рамках одной схемы, которая называется WWI.

Чтобы показать организацию таблиц в выделенном пуле SQL, можно использовать в качестве префиксов имен таблиц значения fact, dim и int. В представленной ниже таблице показаны некоторые имена схем и таблиц для WideWorldImportersDW.

Таблица WideWorldImportersDW	Тип таблицы	Выделенный пул SQL
City	Измерение	wwi.DimCity
Заказ	Факты	wwi.FactOrder

Сохраняемость таблицы

Данные таблиц хранятся на постоянной основе в службе хранилища Azure, временно — в Службе хранилища Microsoft Azure или в хранилище данных, являющемся внешним по отношению к хранилищу данных.

Обычная таблица

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

CREATE TABLE MyTable (col1 int, col2 int );  

Временная таблица

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

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

Дополнительные сведения см. в статье Временные таблицы.

Внешняя таблица

Внешняя таблица указывает на данные, расположенные в большом двоичном объекте Службы хранилища Microsoft Azure или Azure Data Lake Storage.

Для импорта данных из внешних таблиц в выделенные пулы SQL используйте инструкцию CREATE TABLE AS SELECT. Руководство по загрузке см. в статье Загрузка данных из хранилища BLOB-объектов Azure в хранилище данных SQL Azure с помощью PolyBase.

Для несерверного пула SQL можно использовать инструкцию CETAS, чтобы сохранить результат запроса во внешней таблице в Службе хранилища Microsoft Azure.

Типы данных

Выделенный пул SQL поддерживает самые распространенные типы данных. Список поддерживаемых типов данных представлен в справочнике по типах данных в инструкции CREATE TABLE. Дополнительные сведения об использовании типов данных см. в разделе Типы данных.

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

Основополагающая функция выделенного пула SQL — это хранение и обработка таблиц с помощью распределений. Выделенный пул SQL поддерживает три метода распределения данных.

• Циклический перебор (по умолчанию)
• Хэш
• Реплицированный

Таблицы с хэш-распределением

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

Дополнительные сведения см. в статье Руководство по проектированию распределенных таблиц в хранилище данных SQL Azure.

Реплицированные таблицы

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

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

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

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

Дополнительные сведения см. в статье Руководство по проектированию распределенных таблиц в хранилище данных SQL Azure.

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

Категория таблицы часто определяет оптимальный вариант распределения таблиц.

Категории таблицы	Рекомендуемый вариант распределения
Факты	Используйте хэш-распределение с кластеризованным индексом columnstore. Производительность улучшается, когда две хэш-таблицы объединены по одному столбцу распределения.
Измерение	Используйте репликацию для небольших таблиц. Если таблицы слишком велики для хранения на каждом вычислительном узле, используйте хэш-распределение.
Промежуточная	Используйте циклический перебор для промежуточных таблиц. Загрузка с помощью инструкции CTAS выполняется быстро. Когда данные окажутся в промежуточной таблице, используйте INSERT...SELECT, чтобы переместить данные в рабочие таблицы.

Секции

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

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

Совет

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

Приведенный ниже код переключает преобразованные ежедневные данные в секцию SalesFact и перезаписывает существующие данные.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);  

В бессерверном пуле SQL можно ограничить файлы и папки (разделы), которые будут считываться запросом. Секционирование по пути поддерживается с помощью функций FilePath и FileInfo, описанных в разделе Запросы к файлам хранилища. В следующем примере выполняется чтение папки с данными за 2017 год.

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

Индексы columnstore

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

Совет

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

Список функций индексов columnstore см. в статье Новые возможности индексов columnstore. Сведения о повышении производительности индекса columnstore см. в статье Максимальное повышение качества группы строк для индекса columnstore.

Статистика

Оптимизатор запросов использует статистику уровня столбца при создании плана выполнения запроса. Чтобы повысить производительность запросов, важно получить статистику по отдельным столбцам, особенно столбцам, используемым в объединениях запросов. Synapse SQL поддерживает автоматическое создание статистики.

Обновление статистики выполняется вручную. Обновите статистику после добавления или изменения значительного числа строк. Например, есть смысл обновить статистику после загрузки. Дополнительные сведения см. в статье Руководство по статистике.

Первичный ключ и уникальный ключ

Для выделенного пула SQL PRIMARY KEY поддерживается только при одновременном использовании индексов NONCLUSTERED и NOT ENFORCED. Ограничение UNIQUE поддерживается только при использовании NOT ENFORCED. Дополнительные сведения см. в статье Ограничения таблицы выделенного пула SQL.

Команды для создания таблиц

Для выделенного пула SQL можно создать новую пустую таблицу. Вы также можете создать и заполнить таблицу результатами инструкции Select. Ниже приведены команды T-SQL для создания таблицы.

Инструкция Т-SQL	Описание
CREATE TABLE	Создает пустую таблицу, определив все столбцы и параметры таблицы.
CREATE EXTERNAL TABLE	Создает внешнюю таблицу. Определение таблицы хранится в выделенном пуле SQL. Данные таблицы хранятся в хранилище BLOB-объектов Azure или в Azure Data Lake Storage.
CREATE TABLE AS SELECT	Задает новую таблицу с результатами инструкции Select. Столбцы и типы данных таблицы основаны на результатах инструкции Select. Чтобы импортировать данные, эта инструкция может выбрать данные из внешней таблицы.
CREATE EXTERNAL TABLE AS SELECT	Создает новую внешнюю таблицу, экспортируя результаты инструкции Select во внешнее расположение. Расположением является либо хранилище BLOB-объектов Azure, либо Azure Data Lake Storage.

Согласование исходных данных с хранилищем данных

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

Примечание

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

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

Неподдерживаемые функции таблиц

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

• Внешний ключ, проверка ограничений таблицы
• Вычисляемые столбцы
• Индексированные представления
• Последовательность
• Разреженные столбцы
• Суррогатные ключи, реализация посредством Identity
• Синонимы
• Триггеры
• Уникальные индексы
• Определяемые пользователем типы

Запросы размера таблицы

Простой способ определить пространство и строки, используемые таблицей в каждом из 60 распределений выделенного пула SQL, — применить инструкцию 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]
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.