Documentação de orientação para a conceção de tabelas distribuídas com o conjunto de SQL dedicado no Azure Synapse Analytics

  • Artigo

Este artigo contém recomendações para criar tabelas distribuídas por hash e round-robin em conjuntos de SQL dedicados.

Este artigo pressupõe que está familiarizado com os conceitos de distribuição de dados e movimento de dados no conjunto de SQL dedicado. Para obter mais informações, veja arquitetura do Azure Synapse Analytics.

O que é uma tabela distribuída?

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

A distribuição hash melhora o desempenho das consultas em tabelas de factos grandes e é o foco deste artigo. A distribuição round robin é útil para melhorar a velocidade de carregamento. Estas opções de design têm um impacto significativo na melhoria do desempenho da consulta e do carregamento.

Outra opção de armazenamento de tabelas é replicar uma pequena tabela em todos os nós de Computação. Para obter mais informações, veja Orientações de estrutura para tabelas replicadas. Para escolher rapidamente entre as três opções, veja Descrição geral de Tabelas distribuídas nas tabelas.

Como parte da estrutura da tabela, compreenda o máximo possível sobre os seus dados e como os dados são consultados.  Por exemplo, considere estas perguntas:

  • Qual é o tamanho da tabela?
  • Com que frequência a tabela é atualizada?
  • Tenho tabelas de factos e dimensões num conjunto de SQL dedicado?

Distribuída com hash

Uma tabela distribuída com hash distribui as linhas da tabela pelos Nós de computação com uma função hash determinista para atribuir cada linha a uma distribuição.

Tabela distribuída

Uma vez que os valores idênticos aplicam sempre hash à mesma distribuição, a Análise de SQL tem conhecimento incorporado das localizações das linhas. No conjunto de SQL dedicado, este conhecimento é utilizado para minimizar o movimento de dados durante as consultas, o que melhora o desempenho das consultas.

As tabelas distribuídas com hash funcionam bem com grandes tabelas de factos num esquema de estrela. Podem ter um grande número de linhas e alcançar um elevado desempenho. Existem algumas considerações de design que o ajudam a obter o desempenho que o sistema distribuído foi concebido para fornecer. Escolher uma boa coluna ou colunas de distribuição é uma consideração deste tipo descrita neste artigo.

Considere utilizar uma tabela distribuída com hash quando:

  • O tamanho da tabela no disco é superior a 2 GB.
  • A tabela tem operações frequentes de inserção, atualização e eliminação.

Round robin distribuído

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

Como resultado, por vezes, o sistema precisa de invocar uma operação de movimento de dados para organizar melhor os seus dados antes de poder resolver uma consulta. Este passo adicional pode abrandar as suas consultas. Por exemplo, a associação a uma tabela round robin normalmente requer a reativação das linhas, o que é um impacto no desempenho.

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

  • Ao começar como um ponto de partida simples, uma vez que é a predefinição
  • Se não houver uma chave de associação óbvia
  • Se não existir uma boa coluna de candidatos para a distribuição hash da tabela
  • Se a tabela não partilhar uma chave de associação comum com outras tabelas
  • Se a associação for menos significativa do que outras associações na consulta
  • Quando a tabela é uma tabela de teste temporária

O tutorial Load New York taxicab data (Carregar dados de táxi de Nova Iorque ) dá um exemplo de carregamento de dados para uma tabela de teste round robin.

Escolher uma coluna de distribuição

Uma tabela distribuída por hash tem uma coluna de distribuição ou um conjunto de colunas que é a chave hash. Por exemplo, o código seguinte 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 hash pode ser aplicada em múltiplas colunas para uma distribuição mais uniforme da tabela base. A distribuição de várias colunas permite-lhe escolher até oito colunas para distribuição. Isto não só reduz a distorção de dados ao longo do tempo, como também melhora o desempenho das consultas. 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])
);

Nota

A distribuição de várias colunas no Azure Synapse Analytics pode ser ativada ao alterar o nível de compatibilidade da base de dados para 50 com este comando. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Para obter mais informações sobre como definir o nível de compatibilidade da base de dados, veja ALTER DATABASE SCOPED CONFIGURATION (ALTERAR CONFIGURAÇÃO NO ÂMBITO DA BASE DE DADOS). Para obter mais informações sobre distribuições de várias colunas, consulte CRIAR VISTA MATERIALIZADA, CRIAR TABELA ou CRIAR TABELA COMO SELECIONAR.

Os dados armazenados nas colunas de distribuição podem ser atualizados. Atualizações a dados em colunas de distribuição pode resultar numa operação de aleatorização de dados.

Escolher colunas de distribuição é uma decisão de estrutura importante, uma vez que os valores nas colunas hash determinam a forma como as linhas são distribuídas. A melhor opção depende de vários fatores e, geralmente, envolve compromissos. Assim que for escolhido um conjunto de colunas ou colunas de distribuição, não poderá alterá-lo. Se não tiver escolhido as melhores colunas da primeira vez, pode utilizar CREATE TABLE AS SELECT (CTAS) para recriar a tabela com a chave hash de distribuição pretendida.

Escolher uma coluna de distribuição com dados que distribuam uniformemente

Para um melhor desempenho, todas as distribuições devem ter aproximadamente o mesmo número de linhas. Quando uma ou mais distribuições têm um número desproporcionado de linhas, algumas distribuições terminam a sua parte de uma consulta paralela antes de outras. Uma vez que a consulta não pode ser concluída até que todas as distribuições tenham terminado o processamento, cada consulta é 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 pelas distribuições
  • A distorção do processamento significa que algumas distribuições demoram mais tempo do que outras ao executar consultas paralelas. Isto pode acontecer quando os dados são distorcidos.

Para equilibrar o processamento paralelo, selecione uma coluna de distribuição ou um conjunto de colunas 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 à mesma distribuição. Uma vez que existem 60 distribuições, algumas distribuições podem ter > 1 valores exclusivos, enquanto outras podem terminar com valores zero.
  • Não tem NULLs ou tem apenas alguns NULLs. Para um exemplo extremo, se todos os valores nas colunas de distribuição forem NULL, todas as linhas serão atribuídas à mesma distribuição. Como resultado, o processamento de consultas é distorcido para uma distribuição e não beneficia do processamento paralelo.
  • Não é uma coluna de data. Todos os dados da mesma data são apresentados na mesma distribuição ou serão agrupados por data. Se vários utilizadores estiverem todos a filtrar na mesma data (como a data de hoje), apenas 1 das 60 distribuições faz todo o trabalho de processamento.

Escolher uma coluna de distribuição que minimize o movimento de dados

Para obter as consultas de resultados da consulta corretas, as consultas podem mover dados de um nó de Computação para outro. Normalmente, o movimento de dados ocorre quando as consultas têm associações e agregações em tabelas distribuídas. Escolher um conjunto de colunas ou colunas de distribuição que ajude a minimizar o movimento de dados é uma das estratégias mais importantes para otimizar o desempenho do conjunto de SQL dedicado.

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

  • É utilizado nas JOINcláusulas , GROUP BY, DISTINCT, OVERe HAVING . Quando duas tabelas de factos grandes têm associações frequentes, o desempenho das consultas melhora quando distribui ambas as tabelas numa das colunas de associação. Quando uma tabela não é utilizada em associações, considere distribuir a tabela num conjunto de colunas ou colunas que esteja frequentemente na GROUP BY cláusula .
  • Não é utilizado em WHERE cláusulas. Quando a cláusula de WHERE uma consulta e as colunas de distribuição da tabela estão na mesma coluna, a consulta pode encontrar uma distorção de dados elevada, o que faz com que a carga de processamento caia apenas em poucas distribuições. Isto afeta o desempenho das consultas, idealmente muitas distribuições partilham a carga de processamento.
  • Não é uma coluna de data. WHERE muitas vezes, as cláusulas filtram por data. Quando isto acontece, todo o processamento pode ser executado em apenas algumas distribuições que afetam o desempenho das consultas. Idealmente, muitas distribuições partilham a carga de processamento.

Depois de criar a tabela distribuída com hash, o passo seguinte consistirá em carregar dados para a tabela. Para obter orientações sobre o carregamento, veja Descrição geral do carregamento.

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

Depois de carregar os dados para a tabela distribuída com hash, verifique se as linhas estão distribuídas uniformemente pelas 60 distribuições. As linhas por distribuição podem variar até 10% sem um impacto notável no desempenho. Considere os tópicos seguintes para avaliar as colunas de distribuição.

Determinar se a tabela tem distorção de dados

Uma forma rápida de verificar a distorção de dados é utilizar o DBCC PDW_SHOWSPACEUSED. O seguinte código SQL devolve o número de linhas de tabela armazenadas em cada uma das 60 distribuições. Para um desempenho equilibrado, as linhas na tabela distribuída devem ser distribuídas uniformemente por todas as distribuições.

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

Para identificar que tabelas têm mais de 10% de distorção de dados:

  1. Crie a vista dbo.vTableSizes que é apresentada no artigo Descrição geral das 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;

Verificar os planos de consulta do movimento de dados

Um bom conjunto de colunas de distribuição permite que as associações e agregações tenham um movimento mínimo de dados. Isto afeta a forma como as associações devem ser escritas. Para obter um movimento de dados mínimo para uma associação em duas tabelas distribuídas por hash, uma das colunas de associação tem de estar nas colunas ou colunas de distribuição. Quando duas tabelas distribuídas com hash são associadas numa coluna de distribuição com o mesmo tipo de dados, a associação não necessita do movimento de dados. As associações podem utilizar colunas adicionais sem incorrer em movimento de dados.

Para evitar o movimento de dados durante uma associação:

  • As tabelas envolvidas na associação têm de ser distribuídas com hash numa das colunas incluídas na associação.
  • Os tipos de dados das colunas de associação têm de corresponder entre ambas as tabelas.
  • As colunas têm de ser associadas a um operador de igual.
  • O tipo de associação pode não ser um CROSS JOIN.

Para ver se as consultas estão a ocorrer movimento de dados, pode ver 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 o movimento de dados. Nem sempre é possível minimizar a distorção de dados e o movimento de dados. Por vezes, a vantagem de ter um movimento de dados mínimo pode superar o impacto de distorção de dados.

Para decidir se deve resolver a distorção de dados numa tabela, deve compreender o máximo possível sobre os volumes de dados e as consultas na sua carga de trabalho. Pode utilizar os passos no artigo Monitorização de consultas para monitorizar o impacto da distorção no desempenho das consultas. Especificamente, procure o tempo que as consultas grandes demoram a ser concluídas em distribuições individuais.

Uma vez que não pode alterar as colunas de distribuição numa tabela existente, a forma típica de resolver a distorção de dados é recriar a tabela com uma ou mais colunas de distribuição diferentes.

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

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

Primeiro, utilize CREATE TABLE AS SELECT (CTAS) a nova tabela com a nova chave. Em seguida, recrie as estatísticas e, por fim, troque as tabelas ao atribuir-lhes novamente um nome.

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

Passos seguintes

Para criar uma tabela distribuída, utilize uma das seguintes instruções: