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

Tip

Microsoft Fabric Data Warehouse — это реляционное хранилище корпоративного масштаба на основе озера данных, с архитектурой, готовой к будущему, встроенной ИИ и новыми функциями. Если вы не знакомы с хранилищем данных, начните с Fabric Data Warehouse. Существующие рабочие нагрузки выделенного пула SQL могут обновляться до Fabric для доступа к новым возможностям в области науки о данных, аналитики в реальном времени и отчетности.

В этой статье описываются основные понятия разработки таблиц с выделенным пулом SQL и бессерверным пулом SQL в Azure Synapse Analytics.

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

Следующие разделы относятся к выделенному пулу SQL и бессерверному пулу SQL.

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

Категория таблицы

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

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

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

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

Имена схем

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

CREATE SCHEMA wwi;

Имена таблиц

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

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

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

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

Таблицы хранят данные либо постоянно, либо временно в служба хранилища Azure, либо во внешнем хранилище данных вне хранилища данных.

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

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

CREATE TABLE MyTable (col1 int, col2 int );

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

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

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

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

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

External tables указывают на данные, расположенные в служба хранилища Azure BLOB или Azure Data Lake Storage.

Данные из внешних таблиц можно импортировать в выделенные пулы SQL с помощью инструкции CREATE TABLE AS SELECT (CTAS). Руководство по загрузке см. в разделе "Загрузка набора данных New York Taxicab".

Для бессерверного пула SQL можно использовать CREATE EXTERNAL TABLE AS SELECT (CETAS) для сохранения результата запроса во внешнюю таблицу в служба хранилища Azure.

Типы данных

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

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

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

  • Таблицы циклического распределения (по умолчанию)
  • Хэш-распределенные таблицы
  • Реплицированные таблицы

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

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

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

Хэш-распределенные таблицы

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

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

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

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

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

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

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

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

Разделы

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

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

Tip

При переключении секций в не пустые секции таблиц рекомендуется использовать опцию 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

Индексы колоностора

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

Tip

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

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

Статистика

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

Обновление статистики не происходит автоматически. Статистику можно обновить после добавления или изменения значительного количества строк. Например, есть смысл обновить статистику после загрузки. Дополнительные сведения см. в разделе "Статистика" в Synapse SQL.

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

Для выделенного пула SQL PRIMARY KEY поддерживается только в том случае, если используются и NONCLUSTERED, и NOT ENFORCED. UNIQUE ограничение поддерживается только в том случае, если NOT ENFORCED используется. Дополнительные сведения см. в разделе "Первичный ключ", "Внешний ключ" и уникальный ключ с помощью выделенного пула SQL.

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

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

Инструкция T-SQL Описание
СОЗДАТЬ ТАБЛИЦУ Создает пустую таблицу, определив все столбцы и параметры таблицы.
СОЗДАТЬ ВНЕШНЮЮ ТАБЛИЦУ Создает внешнюю таблицу. Определение таблицы хранится в выделенном пуле SQL. Данные таблицы хранятся в хранилище BLOB-объектов Azure или Azure Data Lake Storage.
CREATE TABLE AS SELECT (Создать таблицу как SELECT) Заполняет новую таблицу результатами запроса SELECT. Столбцы и типы данных таблицы основаны на результатах инструкции Select. Чтобы импортировать данные, эта инструкция может выбрать данные из внешней таблицы.
CREATE EXTERNAL TABLE AS SELECT (Создать внешнюю таблицу как SELECT) Создает внешнюю таблицу, экспортируя результаты инструкции select в внешнее расположение. Расположение — Azure Blob Storage или Azure Data Lake Storage.

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

Выделенные таблицы пула 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]
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
;

Связанный контент

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

  • Last updated on