Diretrizes para criar tabelas distribuídas usando o pool de SQL dedicado no Azure Synapse Analytics

  • Artigo

Este artigo contém recomendações para criar tabelas distribuídas por hash e por rodízio nos pools de SQL dedicados.

Este artigo pressupõe que você esteja familiarizado com os conceitos de movimentação e distribuição de dados no pool de SQL dedicado. Para obter mais informações, confira Arquitetura do Azure Synapse Analytics.

O que é uma tabela distribuída?

Uma tabela distribuída é exibida como uma única tabela, mas as linhas são armazenadas em 60 distribuições. As linhas são distribuídas com um algoritmo round-robin ou hash.

Distribuição por hash melhora o desempenho de consulta em grandes tabelas de fatos e é o foco deste artigo. Distribuição por rodízio é útil para melhorar a velocidade do carregamento. Essas opções de design têm um impacto significativo em melhorar o desempenho de carregamento e consulta.

Outra opção de armazenamento de tabela é replicar uma pequena tabela em todos os nós de computação. Para obter mais informações, confira Criação de diretrizes para tabelas replicadas. Para escolher rapidamente entre as três opções, consulte tabelas distribuídas na visão geral de tabelas.

Como parte do design de tabela, compreenda seus dados o tanto quanto possível e a maneira como eles são consultados.    Por exemplo, considere estas perguntas:

  • Qual é o tamanho da tabela?
  • Com que frequência a tabela é atualizada?
  • Tenho tabelas de fatos e dimensões em um pool de SQL dedicado?

Tabelas distribuídas por hash

Uma tabela distribuída por hash distribui linhas da tabela em todos os nós de computação usando uma função de hash determinística para atribuir cada linha a uma distribuição.

Tabela distribuída

Como valores idênticos sempre hash para a mesma distribuição, o SQL Analytics tem conhecimento interno dos locais de linha. No pool de SQL dedicado, esse conhecimento é usado para minimizar a movimentação de dados durante as consultas, o que melhora o desempenho da consulta.

Tabelas distribuídas por hash funcionam bem para grandes tabelas de fatos em um esquema em estrela. Podem ter um grande número de linhas e ainda obter um alto desempenho. Há algumas considerações de design que ajudam você a obter o desempenho que o sistema distribuído foi desenvolvido para fornecer. A escolha de uma boa coluna de distribuição é uma dessas considerações descritas neste artigo.

Considere o uso de uma tabela distribuída por hash quando:

  • O tamanho da tabela no disco é maior de 2 GB.
  • A tabela tiver operações frequentes de inserção, atualização e exclusão.

Distribuição round robin

Uma tabela round robin distribui linhas de tabela uniformemente em todas as distribuições. A atribuição de linhas para distribuições é aleatória. Ao contrário das tabelas distribuídas por hash, não há garantia de que as linhas com valores iguais sejam atribuídas à mesma distribuição.

Como resultado, o sistema às vezes precisa chamar uma operação de movimentação de dados para organizar melhor seus dados antes de poder resolver uma consulta. Essa etapa extra pode causar lentidão em suas consultas. Por exemplo, adicionar uma tabela de round-robin geralmente requer embaralhar linhas, que é uma queda no desempenho.

Considere usar a distribuição round robin para a sua tabela nos seguintes cenários:

  • Ao começar, como um simples ponto de partida já que é padrão
  • Se não houver uma chave de junção óbvia
  • Se não houver colunas como boas candidatas para distribuir por hash a tabela
  • Se a tabela não compartilhar uma chave de junção comum com outras tabelas
  • Se a junção for menos significativa do que outras junções na consulta
  • Quando a tabela é uma tabela temporária de preparo

O tutorial Carregar dados de táxis de Nova York fornece um exemplo de carregamento de dados em uma tabela de preparo por rodízio.

Escolher uma coluna de distribuição

Uma tabela distribuída por hash tem uma coluna ou um conjunto de colunas de distribuição que é a chave de hash. Por exemplo, o código a seguir cria uma tabela distribuída por hash com ProductKey como a coluna de distribuição.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

A distribuição de hash pode ser aplicada em várias colunas para uma distribuição mais uniforme da tabela base. A distribuição de várias colunas permitirá que você escolha até oito colunas para distribuição. Isso não só reduz a distorção de dados ao longo do tempo, mas também aprimora o desempenho da consulta. Por exemplo:

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Observação

A distribuição de várias colunas no Azure Synapse Analytics pode ser habilitada alterando o nível de compatibilidade do banco de dados para 50 com esse comando. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;Para obter mais informações sobre como definir o nível de compatibilidade do banco de dados, confira ALTERAR A CONFIGURAÇÃO DO ESCOPO DO BANCO DE DADOS. Para obter mais informações sobre distribuições de várias colunas, consulte CRIAR EXIBIÇÃO MATERIALIZADA, CRIAR TABELA ou SELECIONAR CRIAR TABELA COMO.

Os dados armazenados nas colunas de distribuição podem ser atualizados. As atualizações dos dados nas colunas de distribuição podem resultar em uma operação de dados embaralhados.

A escolha de uma coluna de distribuição é uma decisão de design importante, pois os valores nas colunas de hash determinam como as linhas são distribuídas. A melhor escolha depende de vários fatores e geralmente envolve as vantagens e desvantagens. Assim que uma coluna ou um conjunto de colunas de distribuição é escolhido, não é possível alterá-lo. Se você não escolheu a melhor coluna na primeira vez, use CREATE TABLE AS SELECT (CTAS) para recriar a tabela com a chave de hash de distribuição desejada.

Escolha uma coluna de distribuição com dados que distribui uniformemente

Para melhor desempenho, todas as distribuições devem ter aproximadamente o mesmo número de linhas. Quando uma ou mais distribuições tem um número desproporcional de linhas, algumas distribuições concluem sua parte de uma consulta paralela antes de outros. Uma vez que a consulta não pode concluir até que todas as distribuições concluam o processamento, cada consulta é somente tão rápida quanto a distribuição mais lenta.

  • Distorção de dados significa que os dados não são distribuídos uniformemente entre as distribuições
  • Processar distorção significa que algumas distribuições demoram mais do que outras ao executar consultas em paralelo. Isso pode acontecer quando os dados estão distorcidos.

Para equilibrar o processamento paralelo, selecione uma coluna ou um conjunto de colunas de distribuição que:

  • Tem muitos valores exclusivos. As colunas de distribuição podem ter valores duplicados. Todas as linhas com o mesmo valor são atribuídas para a mesma distribuição. Como há 60 distribuições, algumas distribuições podem ter > 1 valores exclusivos, enquanto outras podem terminar com zero valores.
  • Não tem valores nulos ou tem apenas alguns valores nulos. Para obter um exemplo extremo, se todos os valores na coluna de distribuição forem NULL, todas as linhas serão atribuídas à mesma distribuição. Como resultado, o processamento de consulta é afetado por uma distribuição e não se beneficia com processamento paralelo.
  • Não é uma coluna de dados. Todos os dados da mesma data ficam na mesma distribuição ou agrupam registros por data. Se vários usuários são filtrados na mesma data (como a data de hoje), apenas 1 das 60 distribuições faz todo o trabalho de processamento.

Escolha uma coluna de distribuição que minimiza a movimentação de dados

Para obter a consulta correta os resultados de consultas podem mover dados de um nó de computação para outro. Movimentação de dados geralmente acontece quando as consultas em tabelas distribuídas contêm junções e agregações. A escolha de uma coluna ou um conjunto de colunas de distribuição que ajuda a minimizar a movimentação de dados é uma das estratégias mais importantes para otimizar o desempenho do pool de SQL dedicado.

Para minimizar a movimentação de dados selecione uma coluna ou um conjunto de colunas de distribuição que:

  • É usada nas cláusulas JOIN, GROUP BY, DISTINCT, OVER, e HAVING. Quando duas grandes tabelas de fatos têm junções frequentes, o desempenho da consulta melhora quando você distribui ambas as tabelas em uma das colunas de junção. Quando uma tabela não é usada em junções, considere distribuí-la em uma coluna ou em um conjunto de colunas que frequentemente esteja na cláusula GROUP BY.
  • Não é usada em cláusulas WHERE. Quando a cláusula WHERE uma consulta e as colunas de distribuição da tabela estão na mesma coluna, a consulta pode encontrar uma grande distorção de dados, fazendo com que a carga de processamento caia em apenas algumas distribuições. Isso afeta o desempenho da consulta, o ideal é que muitas distribuições compartilhem a carga de processamento.
  • Não é uma coluna de dados. Cláusulas WHERE muitas vezes filtram por data. Quando isso acontece, todo o processamento pode ser executado em apenas algumas distribuições afetando o desempenho da consulta. O ideal é que muitas distribuições compartilhem a carga de processamento.

Quando você cria uma tabela distribuída por hash, a próxima etapa é carregar dados na tabela. Para carregar as diretrizes, consulte Visão geral de carregamento.

Como saber se sua distribuição é uma boa opção

Depois que os dados são carregados em uma tabela distribuída por hash, verifique para ver como as linhas são distribuídas uniformemente entre as distribuições de 60. As linhas por distribuição podem variar até 10% sem um impacto significativo no desempenho. Considere os tópicos a seguir para avaliar suas colunas de distribuição.

Determinar se a tabela tem distorção de dados

Uma maneira rápida de verificar a distorção de dados é usar DBCC PDW_SHOWSPACEUSED. O código SQL a seguir retorna o número de linhas da tabela que são armazenados em cada uma das 60 distribuições. Para um desempenho mais equilibrado, as linhas na tabela distribuída devem ser divididas uniformemente entre todas as distribuições.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Para identificar quais tabelas têm distorção de dados maior de 10%:

  1. Crie a dbo.vTableSizes exibição mostrada no artigo Visão geral de tabelas.
  2. Execute a seguinte consulta:
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

Planos de consulta de seleção para a movimentação de dados

Um bom conjunto de colunas de distribuição permite que as junções e as agregações tenham o mínimo de movimento de dados. Isso afeta a maneira que as junções devem ser gravadas. Para obter o mínimo de movimento de dados em uma junção de duas tabelas distribuídas por hash, uma das colunas de junção precisa estar nas colunas de distribuição. Quando duas tabelas distribuídas por hash são integradas em uma coluna de distribuição do mesmo tipo de dados, a junção não exige a movimentação de dados. Junções podem usar colunas adicionais sem incorrer em movimento de dados.

Para evitar a movimentação de dados durante uma junção:

  • As tabelas envolvidas na junção devem ser distribuídas por hash em uma das colunas que participam da junção.
  • Os tipos de dados das colunas de junção devem ser correspondentes entre as duas tabelas.
  • As colunas devem ser associadas com um operador equals.
  • O tipo de associação pode não ser uma CROSS JOIN.

Para ver se consultas estão com a movimentação de dados, você pode examinar o plano de consulta.

Resolver um problema de coluna de distribuição

Não é necessário resolver todos os casos de distorção de dados. A distribuição de dados é uma questão de encontrar o equilíbrio certo entre minimizar a distorção de dados e minimizar a movimentação de dados. Nem sempre é possível minimizar a distorção de dados e a movimentação de dados. Às vezes, o benefício de ter o mínimo de movimentação de dados pode superar o impacto de ter a distorção de dados.

Para decidir se deve resolver a distorção de dados em uma tabela, você deve compreender o máximo possível sobre os volumes de dados e consultas na carga de trabalho. Você pode usar as etapas no artigo Monitoramento de consulta para monitorar o impacto de distorção no desempenho da consulta. Especificamente, procure quanto tempo grandes consultas demoram para ser concluída em distribuições individuais.

Como você não pode alterar as colunas de distribuição em uma tabela existente, uma forma comum de resolver a distorção de dados é recriar a tabela com colunas de distribuição diferentes.

Recriar a tabela com um novo conjunto de colunas de distribuição

Este exemplo usa CREATE TABLE AS SELECT para recriar uma tabela com colunas de distribuição de hash diferentes.

Primeiro use CREATE TABLE AS SELECT (CTAS) na nova tabela com a nova chave. Depois, recrie as estatísticas e, por fim, troque as tabelas renomeando-as.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Próximas etapas

Para criar uma tabela replicada, use uma dessas instruções: