Diretrizes de design para tabelas distribuídas no SQL Data Warehouse do AzureGuidance for designing distributed tables in Azure SQL Data Warehouse

Recomendações para a criação de tabelas distribuídas por hash e round-robin no SQL Data Warehouse do Azure.Recommendations for designing hash-distributed and round-robin distributed tables in Azure SQL Data Warehouse.

Este artigo pressupõe que você esteja familiarizado com os conceitos de movimentação e distribuição de dados no SQL Data Warehouse.This article assumes you are familiar with data distribution and data movement concepts in SQL Data Warehouse.  Para obter mais informações, consulte arquitetura de processamento paralelo maciço (MPP) do Azure SQL data warehouse.  For more information, see Azure SQL Data Warehouse - Massively Parallel Processing (MPP) architecture.

O que é uma tabela distribuída?What is a distributed table?

Uma tabela distribuída é exibida como uma única tabela, mas as linhas são armazenadas em 60 distribuições.A distributed table appears as a single table, but the rows are actually stored across 60 distributions. As linhas são distribuídas com um algoritmo round-robin ou hash.The rows are distributed with a hash or round-robin algorithm.

Tabelas distribuídas por hash melhoram o desempenho de consulta em grandes tabelas de fatos e são o foco deste artigo.Hash-distributed tables improve query performance on large fact tables, and are the focus of this article. Tabelas de round-robin são úteis para melhorar a velocidade do carregamento.Round-robin tables are useful for improving loading speed. Essas opções de design têm um impacto significativo em melhorar o desempenho de carregamento e consulta.These design choices have a significant impact on improving query and loading performance.

Outra opção de armazenamento de tabela é replicar uma pequena tabela em todos os nós de computação.Another table storage option is to replicate a small table across all the Compute nodes. Para obter mais informações, confira Criação de diretrizes para tabelas replicadas.For more information, see Design guidance for replicated tables. Para escolher rapidamente entre as três opções, consulte tabelas distribuídas na visão geral de tabelas.To quickly choose among the three options, see Distributed tables in the tables overview.

Como parte do design de tabela, compreenda seus dados o tanto quanto possível e a maneira como eles são consultados.As part of table design, understand as much as possible about your data and how the data is queried.  Por exemplo, considere estas perguntas:  For example, consider these questions:

  • Qual é o tamanho da tabela?How large is the table?  
  • Com que frequência a tabela é atualizada?How often is the table refreshed?  
  • Há tabelas de dimensões e fatos no data warehouse?Do I have fact and dimension tables in a data warehouse?  

Tabelas distribuídas por hashHash distributed

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.A hash-distributed table distributes table rows across the Compute nodes by using a deterministic hash function to assign each row to one distribution.

Tabela distribuídaDistributed table

Como valores idênticos sempre hash para a mesma distribuição, o data warehouse tem conhecimento interno dos locais de linha.Since identical values always hash to the same distribution, the data warehouse has built-in knowledge of the row locations. SQL Data Warehouse usa esse conhecimento para minimizar a movimentação de dados durante as consultas, o que melhora o desempenho da consulta.SQL Data Warehouse uses this knowledge to minimize data movement during queries, which improves query performance.

Tabelas distribuídas por hash funcionam bem para grandes tabelas de fatos em um esquema em estrela.Hash-distributed tables work well for large fact tables in a star schema. Podem ter um grande número de linhas e ainda obter um alto desempenho.They can have very large numbers of rows and still achieve high performance. É claro, há algumas considerações de design que ajudam você a obter o desempenho que o sistema distribuído foi desenvolvido para fornecer.There are, of course, some design considerations that help you to get the performance the distributed system is designed to provide. Escolher uma boa coluna de distribuição é uma consideração que é descrita neste artigo.Choosing a good distribution column is one such consideration that is described in this article.

Considere o uso de uma tabela distribuída por hash quando:Consider using a hash-distributed table when:

  • O tamanho da tabela no disco é maior de 2 GB.The table size on disk is more than 2 GB.
  • A tabela tiver operações frequentes de inserção, atualização e exclusão.The table has frequent insert, update, and delete operations.

Distribuição round robinRound-robin distributed

Uma tabela round robin distribui linhas de tabela uniformemente em todas as distribuições.A round-robin distributed table distributes table rows evenly across all distributions. A atribuição de linhas para distribuições é aleatória.The assignment of rows to distributions is random. 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.Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

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.As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query. Essa etapa extra pode causar lentidão em suas consultas.This extra step can slow down your queries. Por exemplo, adicionar uma tabela de round-robin geralmente requer embaralhar linhas, que é uma queda no desempenho.For example, joining a round-robin table usually requires reshuffling the rows, which is a performance hit.

Considere usar a distribuição round robin para a sua tabela nos seguintes cenários:Consider using the round-robin distribution for your table in the following scenarios:

  • Ao começar, como um simples ponto de partida já que é padrãoWhen getting started as a simple starting point since it is the default
  • Se não houver uma chave de junção óbviaIf there is no obvious joining key
  • Se não houver uma boa coluna candidata para a distribuição de hash da tabelaIf there is no good candidate column for hash distributing the table
  • Se a tabela não compartilhar uma chave de junção comum com outras tabelasIf the table does not share a common join key with other tables
  • Se a junção for menos significativa do que outras junções na consultaIf the join is less significant than other joins in the query
  • Quando a tabela é uma tabela temporária de preparoWhen the table is a temporary staging table

O tutorial Carregar dados do New York taxicab para o SQL Data Warehouse do Azure fornece um exemplo de carregamento de dados em uma tabela de preparo de round-robin.The tutorial Load New York taxicab data to Azure SQL Data Warehouse gives an example of loading data into a round-robin staging table.

Escolher uma coluna de distribuiçãoChoosing a distribution column

Uma tabela distribuída por hash tem uma coluna de distribuição que é a chave de hash.A hash-distributed table has a distribution column that is the hash key. Por exemplo, o código a seguir cria uma tabela distribuída por hash com ProductKey como a coluna de distribuição.For example, the following code creates a hash-distributed table with ProductKey as the distribution column.

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

Escolher uma coluna de distribuição é uma decisão de design importante como os valores nesta coluna determinam como as linhas são distribuídas.Choosing a distribution column is an important design decision since the values in this column determine how the rows are distributed. A melhor escolha depende de vários fatores e geralmente envolve as vantagens e desvantagens.The best choice depends on several factors, and usually involves tradeoffs. No entanto, se você não escolher a melhor coluna na primeira vez, você pode usar Criar tabela como selecionar (CTAS) para recriar a tabela com uma coluna de distribuição diferente.However, if you don't choose the best column the first time, you can use CREATE TABLE AS SELECT (CTAS) to re-create the table with a different distribution column.

Escolha uma coluna de distribuição que não necessita de atualizaçõesChoose a distribution column that does not require updates

Você não pode atualizar uma coluna de distribuição, a menos que você exclua a linha e insira uma nova linha com os valores atualizados.You cannot update a distribution column unless you delete the row and insert a new row with the updated values. Portanto, selecione uma coluna com valores estáticos.Therefore, select a column with static values.

Escolha uma coluna de distribuição com dados que distribui uniformementeChoose a distribution column with data that distributes evenly

Para melhor desempenho, todas as distribuições devem ter aproximadamente o mesmo número de linhas.For best performance, all of the distributions should have approximately the same number of rows. 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.When one or more distributions have a disproportionate number of rows, some distributions finish their portion of a parallel query before others. 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.Since the query can't complete until all distributions have finished processing, each query is only as fast as the slowest distribution.

  • Distorção de dados significa que os dados não são distribuídos uniformemente entre as distribuiçõesData skew means the data is not distributed evenly across the distributions
  • Processar distorção significa que algumas distribuições demoram mais do que outras ao executar consultas em paralelo.Processing skew means that some distributions take longer than others when running parallel queries. Isso pode acontecer quando os dados estão distorcidos.This can happen when the data is skewed.

Para equilibrar o processamento paralelo, selecione uma coluna de distribuição que:To balance the parallel processing, select a distribution column that:

  • Tem muitos valores exclusivos.Has many unique values. A coluna pode ter alguns valores duplicados.The column can have some duplicate values. No entanto, todas as linhas com o mesmo valor são atribuídas para a mesma distribuição.However, all rows with the same value are assigned to the same distribution. Como há 60 distribuições, a coluna deve ter pelo menos 60 valores exclusivos.Since there are 60 distributions, the column should have at least 60 unique values. Normalmente, o número de valores exclusivos é muito maior.Usually the number of unique values is much greater.
  • Não tem valores nulos ou tem apenas alguns valores nulos.Does not have NULLs, or has only a few NULLs. Para obter um exemplo extremo, se todos os valores na coluna forem NULL, todas as linhas são atribuídas para a mesma distribuição.For an extreme example, if all values in the column are NULL, all the rows are assigned to the same distribution. Como resultado, o processamento de consulta é afetado por uma distribuição e não se beneficia com processamento paralelo.As a result, query processing is skewed to one distribution, and does not benefit from parallel processing.
  • Não é uma coluna de dados.Is not a date column. Todos os dados para a mesma data chegam na mesma distribuição.All data for the same date lands in the same distribution. Se vários usuários são filtrados na mesma data, apenas 1 das 60 distribuições faz todo o trabalho de processamento.If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.

Escolha uma coluna de distribuição que minimiza a movimentação de dadosChoose a distribution column that minimizes data movement

Para obter a consulta correta os resultados de consultas podem mover dados de um nó de computação para outro.To get the correct query result queries might move data from one Compute node to another. Movimentação de dados geralmente acontece quando as consultas em tabelas distribuídas contêm junções e agregações.Data movement commonly happens when queries have joins and aggregations on distributed tables. Escolher uma coluna de distribuição que ajuda a minimizar movimentação de dados é uma das estratégias mais importantes para otimizar o desempenho do SQL Data Warehouse.Choosing a distribution column that helps minimize data movement is one of the most important strategies for optimizing performance of your SQL Data Warehouse.

Para minimizar a movimentação de dados selecione a coluna de distribuição que:To minimize data movement, select a distribution column that:

  • É usada nas cláusulas JOIN, GROUP BY, DISTINCT, OVER, e HAVING.Is used in JOIN, GROUP BY, DISTINCT, OVER, and HAVING clauses. 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.When two large fact tables have frequent joins, query performance improves when you distribute both tables on one of the join columns. Quando uma tabela não é usada em junções, considere distribuir a tabela em uma coluna que é frequentemente na cláusula GROUP BY.When a table is not used in joins, consider distributing the table on a column that is frequently in the GROUP BY clause.
  • Não é usada em cláusulas WHERE.Is not used in WHERE clauses. Isso pode restringir a consulta para não executar em todas as distribuições.This could narrow the query to not run on all the distributions.
  • Não é uma coluna de dados.Is not a date column. Geralmente, cláusulas WHERE filtram por data.WHERE clauses often filter by date. Quando isso acontece, todo o processamento pode ser executado em apenas algumas distribuições.When this happens, all the processing could run on only a few distributions.

O que fazer quando nenhuma das colunas são em uma boa coluna de distribuiçãoWhat to do when none of the columns are a good distribution column

Se nenhuma das suas colunas tiver valores distintos suficientes para uma coluna de distribuição, será possível criar uma nova coluna como uma composição de um ou mais valores.If none of your columns have enough distinct values for a distribution column, you can create a new column as a composite of one or more values. Para evitar a movimentação de dados durante a execução da consulta, use a coluna de distribuição de composição como uma coluna de junção em consultas.To avoid data movement during query execution, use the composite distribution column as a join column in queries.

Quando você cria uma tabela distribuída por hash, a próxima etapa é carregar dados na tabela.Once you design a hash-distributed table, the next step is to load data into the table. Para carregar as diretrizes, consulte Visão geral de carregamento.For loading guidance, see Loading overview.

Como saber se a coluna de distribuição é uma boa opçãoHow to tell if your distribution column is a good choice

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.After data is loaded into a hash-distributed table, check to see how evenly the rows are distributed across the 60 distributions. As linhas por distribuição podem variar até 10% sem um impacto significativo no desempenho.The rows per distribution can vary up to 10% without a noticeable impact on performance.

Determinar se a tabela tem distorção de dadosDetermine if the table has data skew

Uma maneira rápida de verificar a distorção de dados é usar DBCC PDW_SHOWSPACEUSED.A quick way to check for data skew is to use 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.The following SQL code returns the number of table rows that are stored in each of the 60 distributions. Para um desempenho mais equilibrado, as linhas na tabela distribuída devem ser divididas uniformemente entre todas as distribuições.For balanced performance, the rows in your distributed table should be spread evenly across all the distributions.

-- 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%:To identify which tables have more than 10% data skew:

  1. Criar o modo de exibição dbo.vTableSizes que é mostrado no artigo visão geral de tabelas.Create the view dbo.vTableSizes that is shown in the Tables overview article.
  2. Execute a consulta a seguir:Run the following query:
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 dadosCheck query plans for data movement

Uma boa coluna de distribuição permite junções e agregações para que o movimento de dados seja mínimo.A good distribution column enables joins and aggregations to have minimal data movement. Isso afeta a maneira que as junções devem ser gravadas.This affects the way joins should be written. Para obter o movimento de dados mínimo de uma junção em duas tabelas distribuídas por hash, uma das colunas de junção deve ser a coluna de distribuição.To get minimal data movement for a join on two hash-distributed tables, one of the join columns needs to be the distribution column. 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.When two hash-distributed tables join on a distribution column of the same data type, the join does not require data movement. Junções podem usar colunas adicionais sem incorrer em movimento de dados.Joins can use additional columns without incurring data movement.

Para evitar a movimentação de dados durante uma junção:To avoid data movement during a join:

  • As tabelas envolvidas na junção devem ser distribuídas por hash em uma das colunas que participam da junção.The tables involved in the join must be hash distributed on one of the columns participating in the join.
  • Os tipos de dados das colunas de junção devem ser correspondentes entre as duas tabelas.The data types of the join columns must match between both tables.
  • As colunas devem ser associadas com um operador equals.The columns must be joined with an equals operator.
  • O tipo de associação pode não ser uma CROSS JOIN.The join type may not be a CROSS JOIN.

Para ver se consultas estão com a movimentação de dados, você pode examinar o plano de consulta.To see if queries are experiencing data movement, you can look at the query plan.

Resolver um problema de coluna de distribuiçãoResolve a distribution column problem

Não é necessário resolver todos os casos de distorção de dados.It is not necessary to resolve all cases of data skew. 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.Distributing data is a matter of finding the right balance between minimizing data skew and data movement. Nem sempre é possível minimizar a distorção de dados e a movimentação de dados.It is not always possible to minimize both data skew and data movement. À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.Sometimes the benefit of having the minimal data movement might outweigh the impact of having data skew.

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.To decide if you should resolve data skew in a table, you should understand as much as possible about the data volumes and queries in your workload. Você pode usar as etapas no artigo Monitoramento de consulta para monitorar o impacto de distorção no desempenho da consulta.You can use the steps in the Query monitoring article to monitor the impact of skew on query performance. Especificamente, procure quanto tempo grandes consultas demoram para ser concluída em distribuições individuais.Specifically, look for how long it takes large queries to complete on individual distributions.

Como você não pode alterar a coluna de distribuição em uma tabela existente, uma forma comum de resolver distorção de dados é recriar a tabela com uma coluna de distribuição diferente.Since you cannot change the distribution column on an existing table, the typical way to resolve data skew is to re-create the table with a different distribution column.

Criar novamente a tabela com uma nova coluna de distribuiçãoRe-create the table with a new distribution column

Este exemplo usa CREATE TABLE AS SELECT para recriar uma tabela com uma coluna de distribuição de hash diferente.This example uses CREATE TABLE AS SELECT to re-create a table with a different hash distribution column.

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 etapasNext steps

Para criar uma tabela replicada, use uma dessas instruções:To create a distributed table, use one of these statements: