Кластеризация данных в хранилище данных Fabric

Применимо к:✅ Хранилище данных в Microsoft Fabric

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

Принцип работы

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

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

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

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

Метаданные кластеризации данных внедряются в манифест во время приема, позволяя подсистеме хранилища принимать интеллектуальные решения о том, какие файлы будут доступны во время запросов пользователей. Эти метаданные в сочетании с тем, как строки с аналогичными значениями хранятся вместе, гарантирует, что запросы с предикатами фильтра могут пропускать все файлы и группы строк, которые находятся за пределами области предиката. Например, если запрос предназначен только для 10% данных таблицы, кластеризация гарантирует, что сканируются только файлы, содержащие данные в диапазоне фильтра, сокращая потребление операций ввода-вывода и вычислений. Более крупные таблицы получают больше преимуществ от кластеризации данных, поскольку выгоды от пропуска файлов возрастают с увеличением объема данных.

Когда следует использовать кластеризацию данных

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

• Повторяющиеся запросы с WHERE фильтрами: если рабочая нагрузка включает частые запросы, фильтрующие определенные столбцы, кластеризация данных гарантирует, что во время чтения сканируются только соответствующие файлы. Это также применяется, если фильтры многократно используются в панелях мониторинга, отчетах или запланированных заданиях и отправляются в подсистему хранилища в виде инструкций SQL.
• Большие таблицы: кластеризация данных наиболее эффективна при применении к большим таблицам, где сканирование полного набора данных является дорогостоящим. Упорядочивание строк с кластеризацией данных позволяет движку хранилища пропускать целые файлы и группы строк, которые не соответствуют фильтру запроса, что может снизить использование операций ввода-вывода и вычислительных ресурсов.
• Столбцы среднего и высокого кратности: столбцы с более высоким кратностью (например, столбцы с большим количеством уникальных значений, например идентификатором или датой), получают больше преимуществ от кластеризации данных, так как они позволяют подсистеме изолировать и колокатировать аналогичные значения. Это позволяет эффективно пропускать файлы, особенно для выборочных запросов. Столбцы с низкой кардинальностью (например, пол, регион) по своей природе их значения распределены по большему количеству файлов, поэтому предлагают ограниченные возможности для пропуска файлов.
• Выборочные запросы с узкой областью: когда запросы обычно предназначены для небольшого подмножества данных и объединяются с фильтром WHERE, кластеризация данных гарантирует, что считываются только файлы, содержащие соответствующие строки.

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

Синтаксис CLUSTER BY

Кластеризация данных определяется во время создания таблицы с помощью CLUSTER BY предложения. Синтаксис выглядит следующим образом:

Синтаксис CREATE TABLE (Transact-SQL):

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
 [ ,... n ] –- Column list
) WITH (CLUSTER BY [ ,... n ]);

Синтаксис CREATE TABLE AS SELECT (Transact-SQL) :

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name }
( { <column_definition> } [ ,... n ] )
WITH (CLUSTER BY [ ,... n ])
AS <select_statement>;

Предложение CLUSTER BY требует, чтобы для кластеризации данных было указано по крайней мере один столбец и не более четырех столбцов.

Создание таблицы с использованием кластеризации данных SELECT INTO не поддерживается.

Поддержка типов данных

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

¹ Для строковых типов (char/varchar) используются только первые 32 символа при создании статистики столбцов. В результате столбцы со значениями, содержащими длинные префиксы, могут иметь ограниченные преимущества при кластеризации данных.

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

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

Рекомендации по кластеризации данных

Кластеризация данных становится более эффективной, если столбцы кластеризации выбираются на основе действительных шаблонов запросов, особенно тех, которые имеют среднюю или высокую кардинальность, и когда в запросах используются диапазонные предикаты.

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

• Кластеризация данных более эффективна в больших таблицах.
• По возможности используйте пакетный прием и обновление для обработки большего количества строк одновременно, вместо того чтобы использовать небольшие задачи. Для оптимальной производительности операции DML должны иметь не менее 1 миллиона строк, чтобы воспользоваться кластеризацией данных. После последовательных вставок, обновлений и удалений сжатие данных может консолидировать строки из небольших файлов в оптимальный размер.
• Выберите столбцы с средней и высокой кратностью для кластеризации данных, так как они дают лучшие результаты из-за их различающегося распределения значений. Столбцы с низкой кардинальностью могут предложить ограниченные возможности для упорядочивания файлов.
• Выберите столбцы на основе частого использования предикатов WHERE в панелях мониторинга, отчетах, запланированных заданиях или запросах пользователей. Условия соединения по равенству не получают преимущества от кластеризации данных. Общие сведения об использовании Query Insights для выбора столбцов для кластеризации данных на основе текущей рабочей нагрузки см. в руководстве. Использование кластеризации данных в хранилище данных Fabric.
• Не используйте кластеризацию данных по более столбцам, чем необходимо. Кластеризация с несколькими столбцами добавляет сложность в хранилище, добавляет затраты и может не предлагать преимущества, если только все столбцы не используются вместе в запросах с предикатами.
• Порядок столбцов, используемый в CLUSTER BY, не имеет значения и не влияет на способ хранения строк.
• При создании таблицы с использованием CREATE TABLE AS SELECT (CTAS) или приеме данных с помощью INSERT INTO ... SELECT, старайтесь сохранять выборку в этих инструкциях как можно проще для обеспечения оптимального качества кластеризации данных.

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

Системные представления

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

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

SELECT
    t.name AS table_name,
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE ic.data_clustering_ordinal > 0
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

Ограничения и примечания

• Производительность приема данных может снизиться, если таблицы содержат большие столбцы varchar с высокими размерами данных переменных.
  • Например, рассмотрим таблицу с столбцом varchar(200): если некоторые строки содержат только несколько символов, а другие подходили к максимальной длине, то значительное отклонение размера данных может отрицательно повлиять на скорость приема.
  • Эта проблема известна и будет устранена в предстоящем выпуске.
• IDENTITY столбцы нельзя использовать с CLUSTER BY. Таблицы, содержащие IDENTITY столбец, по-прежнему могут использоваться для кластеризации данных, если они используют разные столбцы с CLUSTER BY.
• Кластеризация данных должна быть определена при создании таблицы. Преобразование обычной таблицы в таблицу с CLUSTER BY не поддерживается. Аналогичным образом изменение столбцов кластеризации после создания таблицы не допускается. Если требуются различные столбцы кластеризации, при необходимости используйте CREATE TABLE AS SELECT CTAS для создания новой таблицы с нужными столбцами кластеризации.
• В некоторых случаях кластеризация данных может применяться асинхронно. В таких случаях данные реорганизуются с помощью фоновой задачи, и таблица может быть не полностью оптимизирована по окончании загрузки. Это может произойти в следующих условиях:
  • Когда используются INSERT INTO ... SELECT или CREATE TABLE AS SELECT (CTAS), и сортировки исходных и целевых таблиц отличаются.
  • При загрузке данных из внешнего источника, который использует сжатый формат CSV.
  • Если оператор загрузки данных содержит менее 1 миллиона строк.
• Прием данных в таблицах с кластеризацией данных создает дополнительные затраты по сравнению с таблицей с такой же схемой, которая не использует кластеризацию данных. Это происходит из-за дополнительных вычислений, необходимых для оптимизации хранилища. Если в столбце кластеризации используется регистронезависимая сортировка, также ожидается больше дополнительных нагрузок.
• Кластеризация данных может улучшить время ответа запроса, потребление единицы емкости (CU) или обоих.

Примеры

А. Создайте кластерную таблицу для данных о продажах

В этом примере создается простая Sales таблица и используются CustomerIDSaleDate столбцы для кластеризации данных.

CREATE TABLE Sales (
    SaleID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
) WITH (CLUSTER BY (CustomerID, SaleDate))

В. Создание кластеризованной таблицы с помощью CREATE TABLE AS SELECT

В этом примере используется CREATE TABLE AS SELECT для создания копии существующей Sales таблицы с столбцом CLUSTER BYSaleDate.

CREATE TABLE Sales_CTAS 
WITH (CLUSTER BY (SaleDate)) 
AS SELECT * FROM Sales

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

В этом примере перечислены столбцы, используемые для кластеризации данных в Sales таблице.

SELECT
    c.name AS column_name,
    ic.data_clustering_ordinal AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE 
    ic.data_clustering_ordinal > 0
   AND t.name = 'Sales'
ORDER BY
    t.name,
    ic.data_clustering_ordinal;

Результаты:

D. Проверка эффективности выбора столбцов для кластеризации данных

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

SELECT 
    allocated_cpu_time_ms, 
    data_scanned_disk_mb, 
    data_scanned_memory_mb, 
    data_scanned_remote_storage_mb
FROM 
    queryinsights.exec_requests_history 
WHERE 
     distributed_statement_id = '<Query_Statement_ID>'

Где <Query_Statement_ID> находится идентификатор распределенной инструкции запроса, который требуется оценить.

Следующий шаг

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

• Рекомендации по производительности в хранилище данных Fabric
• Мониторинг хранилища данных Fabric