Agrupamento de dados no Fabric Data Warehouse

Aplica-se a:✅Armazém de dados no Microsoft Fabric

O clustering de dados é uma técnica usada para organizar e armazenar dados com base na similaridade. O clustering de dados melhora o desempenho da consulta e reduz os custos de computação e de acesso de armazenamento para consultas agrupando registros semelhantes.

Como funciona

O clustering de dados funciona armazenando linhas com valores semelhantes em locais adjacentes no armazenamento durante o processo de ingestão. O clustering de dados usa uma curva de preenchimento de espaço para organizar dados de uma maneira que preserva a localidade em várias dimensões, o que significa que linhas com valores semelhantes entre colunas de clustering são armazenadas fisicamente próximas. Essa abordagem melhora drasticamente o desempenho da consulta ao realizar a omissão de arquivos e reduzir o número de arquivos que são verificados.

Ao contrário da ordenação lexicográfica convencional, o agrupamento de dados usa um algoritmo sofisticado para processar, mantendo próximas as linhas que possuem valores de coluna semelhantes, mesmo quando uma tabela é classificada por várias colunas. Isso torna o clustering de dados ideal para consultas de intervalo, filtros de alta cardinalidade e tabelas grandes com distribuições distorcidas, resultando em leituras mais rápidas, E/S reduzida e uso mais eficiente de recursos.

Aqui está uma ilustração conceitual simplificada do clustering de dados:

Neste diagrama, uma tabela rotulada Source data mostra linhas misturadas e realçadas em cores diferentes para representar agrupamentos com base no destino. Uma tabela ordenada é dividida em três segmentos de arquivo, cada uma agrupando linhas por cores semelhantes, demonstrando como o clustering organiza dados em segmentos de armazenamento otimizados com base em valores de coluna.

Os metadados de clustering de dados são inseridos no manifesto durante a ingestão, permitindo que o motor de armazém de dados tome decisões inteligentes sobre quais arquivos acessar durante as consultas dos usuários. Esses metadados, combinados com a forma como linhas com valores semelhantes são armazenadas juntas, garante que consultas com predicados de filtro possam ignorar arquivos inteiros e grupos de linhas que estão fora do escopo do predicado. Por exemplo: se uma consulta destina-se a apenas 10% de dados de uma tabela, o clustering garante que apenas os arquivos que contêm os dados dentro do intervalo do filtro sejam verificados, reduzindo o consumo de E/S e de computação. Tabelas maiores se beneficiam mais do clustering de dados, pois os benefícios de ignorar arquivos aumentam com o volume de dados.

Quando usar o agrupamento de dados

Quando decidir se o clustering de dados pode ser benéfico, investigue os padrões de consulta e as características da tabela no armazém de dados. O agrupamento de dados é mais eficaz quando as consultas filtram repetidamente colunas específicas e quando as tabelas subjacentes são grandes e contêm dados de cardinalidade média a alta. Os cenários de recuperação incluem:

• Consultas repetidas com WHERE filtros: se a carga de trabalho incluir consultas frequentes filtrando colunas específicas, o clustering de dados garantirá que apenas arquivos relevantes sejam verificados durante consultas de leitura. Isso também se aplica quando os filtros são usados repetidamente em dashboards, relatórios ou trabalhos agendados e transferidos para o mecanismo de armazém de dados como instruções SQL.
• Tabelas maiores: o clustering de dados é mais eficaz quando aplicado a tabelas grandes em que a varredura do dataset completo é dispendiosa. Ao organizar linhas com agrupamento de dados, o motor de banco de dados pode ignorar arquivos inteiros e grupos de linhas que não correspondem ao filtro de consulta, o que pode reduzir o uso de entrada/saída e de recursos de computação.
• Colunas de cardinalidade média a alta: colunas com maior cardinalidade (por exemplo: colunas que têm muitos valores distintos, como uma ID ou uma data) se beneficiam mais do clustering de dados porque permitem que o mecanismo isole e coloque valores semelhantes. Isso permite um salto eficiente de arquivos, especialmente para consultas seletivas. Colunas com baixa cardinalidade (por exemplo: gênero, região) por natureza têm seus valores distribuídos entre vários arquivos, o que oferece, portanto, oportunidades limitadas para ignorar arquivos.
• Consultas seletivas com escopo estreito: quando as consultas normalmente se destinam a um pequeno subconjunto de dados e são combinadas com um filtro WHERE, o clustering de dados garante que somente os arquivos que contêm as linhas relevantes sejam lidos.

O agrupamento de dados ocorre automaticamente durante a ingestão de dados, independentemente da forma como as linhas foram ingeridas. Nenhuma operação de usuário é necessária depois que os dados são ingeridos para aplicar o clustering de dados.

Sintaxe CLUSTER BY

O clustering de dados é definido durante a criação da tabela, usando a CLUSTER BY cláusula. A sintaxe é a seguinte:

Sintaxe 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 ]);

Sintaxe CREATE TABLE AS SELECT (Transact-SQL):

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

A CLUSTER BY cláusula requer que pelo menos uma coluna seja especificada para clustering de dados e um máximo de quatro colunas.

Não é suportado criar uma tabela que usa agrupamento de dados com SELECT INTO.

Suporte do tipo de dados

A tabela a seguir resume os tipos de coluna que podem ser usados na CLUSTER BY cláusula:

¹ Para tipos de cadeia de caracteres (char/varchar), somente os primeiros 32 caracteres são usados quando as estatísticas de coluna são produzidas. Como resultado, colunas com valores que contêm prefixos longos podem ter benefícios limitados com clustering de dados.

² Para tipos decimais com precisão maior que 18, os predicados não são enviados por push para o armazenamento durante a execução da consulta. Se estiver usando tipos decimais com agrupamento de dados, favoreça colunas com menor precisão.

Colunas com tipos de dados sem suporte ainda podem existir em uma tabela que usa agrupamento de dados, mas não podem ser utilizadas com CLUSTER BY.

Práticas recomendadas com agrupamento de dados

O agrupamento de dados é mais eficaz quando as colunas de agrupamento são escolhidas com base em padrões de consulta reais, particularmente aqueles com cardinalidade média a alta e quando predicados de faixa são aplicados nas consultas.

Considere as seguintes práticas recomendadas ao usar o clustering de dados:

• O clustering de dados é mais eficaz em tabelas grandes.
• Sempre que possível, realize a ingestão e atualizações em lote para processar um número maior de linhas ao mesmo tempo, em vez de usar tarefas individuais menores. Para um desempenho ideal, as operações DML devem ter pelo menos 1 milhão de linhas para se beneficiar do clustering de dados. Após inserções, atualizações e exclusões consecutivas, a compactação de dados pode consolidar linhas de arquivos menores em arquivos de tamanho ideal.
• Escolha colunas com cardinalidade média a alta para clustering de dados, pois elas geram melhores resultados devido à distribuição distinta de valores. Colunas com baixa cardinalidade podem oferecer oportunidades limitadas para a eliminação de arquivos.
• Selecione colunas com base no uso frequente de WHERE predicados em dashboards, relatórios, trabalhos agendados ou consultas de usuário. As condições de junção de igualdade não se beneficiam do clustering de dados. Para obter uma visão geral de como usar o Query Insights para ajudar a escolher colunas para clustering de dados com base na carga de trabalho atual, consulte Tutorial: Usando o Clustering de Dados no Fabric Data Warehouse.
• Não use o agrupamento de dados para mais colunas do que o necessário. O clustering de várias colunas adiciona complexidade ao armazenamento, adiciona sobrecarga e pode não oferecer benefícios, a menos que todas as colunas sejam usadas em conjunto em consultas com predicados.
• A ordem de coluna usada CLUSTER BY não é importante e não altera a forma como as linhas são armazenadas.
• Ao criar uma tabela com clustering de dados usando CREATE TABLE AS SELECT (CTAS) ou ingerindo dados com INSERT INTO ... SELECT, mantenha a seleção nessas instruções o mais simples possível para otimizar a qualidade do clustering de dados.

O agrupamento de dados pode reduzir significativamente os custos durante consultas, se estiver bem alinhado com predicados de consulta. No entanto, a ingestão de dados incorre em mais tempo e unidades de capacidade em uma tabela que usa agrupamento de dados, quando comparada a uma tabela equivalente com os mesmos dados sem agrupamento de dados. Isso acontece porque o mecanismo do warehouse precisa solicitar dados durante a ingestão. Como os dados ingeridos são lidos várias vezes, o clustering de dados pode reduzir o consumo geral de computação de uma determinada carga de trabalho.

Exibições do sistema

Metadados de agrupamento de dados podem ser consultados usando sys.index_columns. Ele mostra todas as colunas usadas no clustering de dados, incluindo a coluna ordinal usada na cláusula CLUSTER BY.

A consulta a seguir lista todas as colunas usadas no agrupamento de dados no armazém de dados atual e as suas tabelas.

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;

Limitações e comentários

• O desempenho da ingestão de dados pode diminuir quando as tabelas contêm colunas varchar grandes com tamanhos de dados altamente variáveis.
  • Por exemplo, considere uma tabela com uma coluna varchar(200 ): se algumas linhas contiverem apenas alguns caracteres enquanto outras se aproximarem do comprimento máximo, a variação significativa no tamanho dos dados poderá afetar negativamente a velocidade de ingestão.
  • Esse problema é conhecido e será resolvido em uma versão futura.
• colunas IDENTITY não podem ser usadas com CLUSTER BY. Tabelas que contêm uma IDENTITY coluna ainda podem ser usadas para agrupamento de dados, desde que usem colunas diferentes com CLUSTER BY.
• O clustering de dados deve ser definido na criação da tabela. Não há suporte para converter uma tabela comum em uma com CLUSTER BY . Da mesma forma, a modificação das colunas de clustering após a criação de uma tabela não é permitida. Se forem necessárias colunas de clustering diferentes, opcionalmente, use CREATE TABLE AS SELECT (CTAS) para criar uma nova tabela com as colunas de clustering desejadas.
• Em alguns casos, o clustering de dados pode ser aplicado de forma assíncrona. Nesses casos, os dados são reorganizados com uma tarefa em segundo plano e a tabela pode não ser totalmente otimizada quando a ingestão é concluída. Isso pode acontecer nas seguintes condições:
  • Ao usar INSERT INTO ... SELECT ou CREATE TABLE AS SELECT (CTAS) e a ordenação das tabelas de origem e de destino são diferentes.
  • Ao ingerir dados externos de um formato CSV compactado.
  • Quando uma instrução de ingestão tem menos de 1 milhão de linhas.
• A ingestão de dados em tabelas de clustering de dados incorre em uma sobrecarga quando comparada com uma tabela com o mesmo esquema que não usa clustering de dados. Isso ocorre devido à computação extra necessária para otimizar o armazenamento. Quando a coluna de agrupamento tem uma ordenação que não diferencia maiúsculas de minúsculas, espera-se mais sobrecarga.
• O clustering de dados pode beneficiar o tempo de resposta à consulta, o consumo da unidade de capacidade (CU) ou ambos.

Exemplos

A. Crie uma tabela clusterizada para dados de vendas

Este exemplo cria uma tabela simples Sales e usa as colunas CustomerID e SaleDate para distribuição de dados em clusters.

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

B. Criar uma tabela clusterizada utilizando o CREATE TABLE AS SELECT

Este exemplo usa CREATE TABLE AS SELECT para criar uma cópia da Sales tabela existente, com CLUSTER BY a SaleDate coluna.

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

C. Exibir as colunas usadas para Clustering de Dados em uma determinada tabela

Este exemplo lista as colunas usadas para clustering de dados na Sales tabela.

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;

Resultados:

D. Verificar a eficácia das opções de coluna para clustering de dados

Query Insights pode ajudar a avaliar o efeito do agrupamento de dados na sua carga de trabalho, comparando o tempo da CPU e os dados verificados entre uma determinada consulta e sua execução equivalente em uma cópia clusterizada da tabela original. O exemplo a seguir ilustra como recuperar o tempo de CPU alocado e o volume de dados verificados em disco, memória e armazenamento remoto para uma consulta específica.

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>'

Onde <Query_Statement_ID> está a ID da instrução distribuída da consulta que você deseja avaliar.

Próxima etapa

Conteúdo relacionado

• Diretrizes de desempenho no Fabric Data Warehouse
• Monitorar o Fabric Data Warehouse