Particionando tabelas no SQL Data WarehousePartitioning tables in SQL Data Warehouse

Recomendações e exemplos para usar partições de tabelas no SQL Data Warehouse do Azure.Recommendations and examples for using table partitions in Azure SQL Data Warehouse.

O que são as partições de tabela?What are table partitions?

Partições de tabela permitem dividir seus dados em grupos menores de dados.Table partitions enable you to divide your data into smaller groups of data. Na maioria dos casos, as partições de tabela são criadas em uma coluna de data.In most cases, table partitions are created on a date column. O particionamento tem suporte em todos os tipos de tabela do SQL Data Warehouse, incluindo columnstore clusterizado, índice clusterizado e heap.Partitioning is supported on all SQL Data Warehouse table types; including clustered columnstore, clustered index, and heap. O particionamento também tem suporte em todos os tipos de distribuição, incluindo hash ou round robin.Partitioning is also supported on all distribution types, including both hash or round robin distributed.

O particionamento pode melhorar o desempenho da consulta e a manutenção de dados.Partitioning can benefit data maintenance and query performance. Se ele beneficia ambos ou apenas um depende de como os dados são carregados e se a mesma coluna pode ser usada para ambas as finalidades, já que o particionamento só pode ser feito em uma coluna.Whether it benefits both or just one is dependent on how data is loaded and whether the same column can be used for both purposes, since partitioning can only be done on one column.

Benefícios para cargasBenefits to loads

A principal vantagem do particionamento no SQL Data Warehouse é melhorar a eficiência e o desempenho de carregamento de dados pelo uso de exclusão, troca e mesclagem de partição.The primary benefit of partitioning in SQL Data Warehouse is to improve the efficiency and performance of loading data by use of partition deletion, switching and merging. Na maioria dos casos, os dados são particionados em uma coluna de data que está intimamente ligada à ordem em que os dados são carregados no banco de dados.In most cases data is partitioned on a date column that is closely tied to the order in which the data is loaded into the database. Uma das maiores vantagens de usar partições para manter dados é evitar o registro de transações em log.One of the greatest benefits of using partitions to maintain data it the avoidance of transaction logging. Embora a simples inserção, atualização ou exclusão de dados possa ser a abordagem mais simples, com um pouco de empenho, o uso de particionamento durante o processo de carregamento pode melhorar consideravelmente o desempenho.While simply inserting, updating, or deleting data can be the most straightforward approach, with a little thought and effort, using partitioning during your load process can substantially improve performance.

A alternância de partição pode ser usada para remover ou substituir uma seção de uma tabela rapidamente.Partition switching can be used to quickly remove or replace a section of a table. Por exemplo, uma tabela de fatos de vendas pode conter apenas dados dos últimos 36 meses.For example, a sales fact table might contain just data for the past 36 months. No final de cada mês, o mês de dados de vendas mais antigo é excluído da tabela.At the end of every month, the oldest month of sales data is deleted from the table. Esses dados poderiam ser excluídos usando uma instrução delete para excluir os dados do mês mais antigo.This data could be deleted by using a delete statement to delete the data for the oldest month. No entanto, a exclusão de uma grande quantidade de dados linha por linha com uma declaração DELETE pode demorar muito tempo, bem como criar o risco de transações grandes, o que pode demorar muito para reverter se algo der errado.However, deleting a large amount of data row-by-row with a delete statement can take too much time, as well as create the risk of large transactions that take a long time to rollback if something goes wrong. Uma abordagem ideal é remover a partição dos de dados mais antiga.A more optimal approach is to drop the oldest partition of data. A exclusão de linhas individuais pode levar horas. A exclusão de uma partição inteira pode demorar segundos.Where deleting the individual rows could take hours, deleting an entire partition could take seconds.

Vantagens para consultasBenefits to queries

O particionamento também pode ser usado para melhorar o desempenho da consulta.Partitioning can also be used to improve query performance. Uma consulta que aplica um filtro a dados particionados pode limitar a verificação apenas para as partições qualificadas.A query that applies a filter to partitioned data can limit the scan to only the qualifying partitions. Este método de filtragem pode evitar uma verificação de tabela completa e apenas examinar um subconjunto de dados menor.This method of filtering can avoid a full table scan and only scan a smaller subset of data. Com a introdução de índices columnstore clusterizados, os benefícios de desempenho de eliminação de predicado são menores, mas em alguns casos pode haver vantagem para as consultas.With the introduction of clustered columnstore indexes, the predicate elimination performance benefits are less beneficial, but in some cases there can be a benefit to queries. Por exemplo, se a tabela de fatos de vendas for particionada em 36 meses usando o campo de data das vendas, as consultas que forem filtradas pela data da venda podem pular a pesquisa de partições que não correspondam ao filtro.For example, if the sales fact table is partitioned into 36 months using the sales date field, then queries that filter on the sale date can skip searching in partitions that don’t match the filter.

Partições de dimensionamentoSizing partitions

Embora o particionamento possa ser usado para melhorar o desempenho de alguns cenários, a criação de uma tabela com muitas partições pode prejudicar o desempenho em algumas circunstâncias.While partitioning can be used to improve performance some scenarios, creating a table with too many partitions can hurt performance under some circumstances. Esses problemas são especialmente verdadeiros para tabelas columnstore clusterizadas.These concerns are especially true for clustered columnstore tables. Para que o particionamento seja útil, é importante entender quando usar o particionamento e o número de partições a serem criadas.For partitioning to be helpful, it is important to understand when to use partitioning and the number of partitions to create. Não há uma regra rígida quanto à quantidade de partições que são muitas, isso depende dos seus dados e de quantas partições você carrega simultaneamente.There is no hard fast rule as to how many partitions are too many, it depends on your data and how many partitions you loading simultaneously. Um esquema de particionamento bem sucedido geralmente tem dezenas a centenas de partições, não milhares.A successful partitioning scheme usually has tens to hundreds of partitions, not thousands.

Ao criar partições em tabelas columnstore clusterizadas, é importante considerar quantas linhas pertencem a cada partição.When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. Para compactação e desempenho ideais de tabelas columnstore clusterizadas, é necessário um mínimo de um milhão de linhas por distribuição, e também é necessário haver partição.For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per distribution and partition is needed. Antes das partições serem criadas, o SQL Data Warehouse já divide cada tabela em 60 bancos de dados distribuídos.Before partitions are created, SQL Data Warehouse already divides each table into 60 distributed databases. O particionamento adicionado a uma tabela é além das distribuições criadas nos bastidores.Any partitioning added to a table is in addition to the distributions created behind the scenes. Usando esse exemplo, se a tabela de fatos de vendas contiver 36 partições mensais, e uma vez que o SQL Data Warehouse tem 60 distribuições, a tabela de fatos de vendas deverá conter 60 milhões de linhas por mês, ou 2.1 bilhões de linhas quando todos os meses forem populados.Using this example, if the sales fact table contained 36 monthly partitions, and given that SQL Data Warehouse has 60 distributions, then the sales fact table should contain 60 million rows per month, or 2.1 billion rows when all months are populated. Se uma tabela possuir menos linhas do que o mínimo recomendado, considere usar menos partições para aumentar o número de linhas por partição.If a table contains fewer than the recommended minimum number of rows per partition, consider using fewer partitions in order to increase the number of rows per partition. Para obter mais informações, consulte o artigo Indexação, que inclui consultas que podem avaliar a qualidade dos índices columnstore do cluster.For more information, see the Indexing article, which includes queries that can assess the quality of cluster columnstore indexes.

Diferenças de sintaxe do SQL ServerSyntax differences from SQL Server

O SQL Data Warehouse introduz uma maneira de definir partições que é mais simples que o SQL Server.SQL Data Warehouse introduces a way to define partitions that is simpler than SQL Server. As funções e esquemas de particionamento não são usados no SQL Data Warehouse como são no SQL Server.Partitioning functions and schemes are not used in SQL Data Warehouse as they are in SQL Server. Em vez disso,tudo o que você precisa fazer é identificar a coluna particionada e os pontos delimitadores.Instead, all you need to do is identify partitioned column and the boundary points. Embora a sintaxe de particionamento possa ser ligeiramente diferente do SQL Server, os conceitos básicos são os mesmos.While the syntax of partitioning may be slightly different from SQL Server, the basic concepts are the same. O SQL Server e o SQL Data Warehouse dão suporte a uma coluna de partição por tabela, que pode ser partição intervalada.SQL Server and SQL Data Warehouse support one partition column per table, which can be ranged partition. Para saber mais sobre particionamento, confira Tabelas e índices particionados.To learn more about partitioning, see Partitioned Tables and Indexes.

O exemplo a seguir usa a instrução CREATE TABLE para particionar a tabela FactInternetSales na coluna OrderDateKey:The following example uses the CREATE TABLE statement to partition the FactInternetSales table on the OrderDateKey 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])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

Migrando o particionamento do SQL ServerMigrating partitioning from SQL Server

Para migrar definições de partição do SQL Server para o SQL Data Warehouse, basta:To migrate SQL Server partition definitions to SQL Data Warehouse simply:

Se você estiver migrando uma tabela particionada de uma Instância do SQL Server, o SQL a seguir poderá ajudá-lo a descobrir o número de linhas que em cada partição.If you are migrating a partitioned table from a SQL Server instance, the following SQL can help you to figure out the number of rows that in each partition. Tenha em mente que se a mesma granularidade de particionamento for utilizada no SQL Data Warehouse, o número de linhas por partição será reduzido por 60.Keep in mind that if the same partitioning granularity is used on SQL Data Warehouse, the number of rows per partition decreases by a factor of 60.

-- Partition information for a SQL Server Database
SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           SUM(a.[used_pages]*8.0)/1048576 AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]
,           p.[data_compression_desc]       AS      [partition_compression_desc]
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]      = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc]
;

Alternância de partiçãoPartition switching

O SQL Data Warehouse dá suporte à divisão, mesclagem e comutação de partição.SQL Data Warehouse supports partition splitting, merging, and switching. Todas essas funções são executadas usando a instrução ALTER TABLE.Each of these functions is executed using the ALTER TABLE statement.

Para alternar as partições entre duas tabelas, você deve garantir que as partições alinhem em seus respectivos limites e que correspondam as definições de tabela.To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match. Como restrições de verificação não estão disponíveis para impor o intervalo de valores em uma tabela, a tabela de origem deve conter os mesmos limites de partição da tabela de destino.As check constraints are not available to enforce the range of values in a table, the source table must contain the same partition boundaries as the target table. Se os limites de partição não forem os mesmos, a alternância de partição falhará, pois os metadados da partição não serão sincronizados.If the partition boundaries are not then same, then the partition switch will fail as the partition metadata will not be synchronized.

Como dividir uma partição que contém dadosHow to split a partition that contains data

O método mais eficiente para dividir uma partição que já contém dados é usar um CTAS instrução.The most efficient method to split a partition that already contains data is to use a CTAS statement. Se a tabela de partição for um columnstore em cluster, então a partição da tabela deverá estar vazia antes que possa ser dividida.If the partitioned table is a clustered columnstore, then the table partition must be empty before it can be split.

O exemplo a seguir cria uma tabela do columnstore particionada.The following example creates a partitioned columnstore table. Ele insere uma linha em cada partição:It inserts one row into each partition:

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])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101
                    )
                )
)
;

INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);

A consulta a seguir localiza a contagem de linhas utilizando a exibição do catálogo sys.partitions:The following query finds the row count by using the sys.partitions catalog view:

SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = 'FactInternetSales'
;

O comando Dividir a seguir recebe uma mensagem de erro:The following split command receives an error message:

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Msg 35346, Nível 15, Estado 1, linha 44 da cláusula SPLIT da instrução ALTER PARTITION falhou porque a partição não está vazia.Msg 35346, Level 15, State 1, Line 44 SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Somente partições vazias podem ser divididas quando existe um índice columnstore na tabela.Only empty partitions can be split in when a columnstore index exists on the table. Considere desabilitar o índice columnstore antes de emitir a instrução ALTER PARTITION, e então recriar o índice columnstore após a instrução ALTER PARTITION estar concluída.Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

No entanto, é possível utilizar CTAS para criar uma nova tabela para reter os dados.However, you can use CTAS to create a new table to hold the data.

CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
            )
AS
SELECT *
FROM    FactInternetSales
WHERE   1=2
;

Como os limites de partição estão alinhados, uma alternância é permitida.As the partition boundaries are aligned, a switch is permitted. Isso deixará a tabela de origem com uma partição vazia que você poderá dividir posteriormente.This will leave the source table with an empty partition that you can subsequently split.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO  FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Tudo o que resta é alinhar os dados aos novos limites de partição utilizando CTAS e, em seguida, alternar os dados de volta para a tabela principal.All that is left is to align the data to the new partition boundaries using CTAS, and then switch the data back into the main table.

CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

Após concluir a movimentação dos dados, é recomendável atualizar as estatísticas na tabela de destino.Once you have completed the movement of the data, it is a good idea to refresh the statistics on the target table. A atualização de estatísticas garante que as estatísticas reflitam com precisão a nova distribuição dos dados em suas respectivas partições.Updating statistics ensures the statistics accurately reflect the new distribution of the data in their respective partitions.

UPDATE STATISTICS [dbo].[FactInternetSales];

Carregar novos dados em partições que contêm dados em uma única etapaLoad new data into partitions that contain data in one step

Carregar dados em partições com a alternância de partição é uma maneira conveniente de novos dados em uma tabela que não está visível para os usuários na mudança dos novos dados.Loading data into partitions with partition switching is a convenient way stage new data in a table that is not visible to users the switch in the new data. Pode ser desafiador em sistemas ocupados para lidar com a contenção de bloqueio associada à alternância de partição.It can be challenging on busy systems to deal with the locking contention associated with partition switching. Para limpar os dados existentes em uma partição, um ALTER TABLE costumava ser necessário para desativar os dados.To clear out the existing data in a partition, an ALTER TABLE used to be required to switch out the data. Em seguida, outra ALTER TABLE era necessária para alternar os novos dados.Then another ALTER TABLE was required to switch in the new data. No SQL Data Warehouse, há suporte para a opção TRUNCATE_TARGET no comando ALTER TABLE.In SQL Data Warehouse, the TRUNCATE_TARGET option is supported in the ALTER TABLE command. Com TRUNCATE_TARGET o comando ALTER TABLE substitui os dados existentes na partição por novos dados.With TRUNCATE_TARGET the ALTER TABLE command overwrites existing data in the partition with new data. Abaixo está um exemplo que usa CTAS para criar uma nova tabela com os dados existentes, insere novos dados e, em seguida, alterna todos os dados de volta para a tabela de destino, substituindo os dados existentes.Below is an example which uses CTAS to create a new table with the existing data, inserts new data, then switches all the data back into the target table, overwriting the existing data.

CREATE TABLE [dbo].[FactInternetSales_NewSales]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);  

Controle da origem do particionamento da tabelaTable partitioning source control

Para evitar a definição da tabela de rusting em seu sistema de controle de origem, convém considerar a abordagem a seguir:To avoid your table definition from rusting in your source control system, you may want to consider the following approach:

  1. Criar a tabela como uma tabela particionada, mas sem valores de partiçãoCreate the table as a partitioned table but with no partition values

    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])
    ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES () )
    )
    ;
    
  2. SPLIT a tabela como parte do processo de implantação:SPLIT the table as part of the deployment process:

     -- Create a table containing the partition boundaries
    
    CREATE TABLE #partitions
    WITH
    (
        LOCATION = USER_DB
    ,   DISTRIBUTION = HASH(ptn_no)
    )
    AS
    SELECT  ptn_no
    ,       ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no
    FROM    (
        SELECT CAST(20000101 AS INT) ptn_no
        UNION ALL
        SELECT CAST(20010101 AS INT)
        UNION ALL
        SELECT CAST(20020101 AS INT)
        UNION ALL
        SELECT CAST(20030101 AS INT)
        UNION ALL
        SELECT CAST(20040101 AS INT)
    ) a
    ;
    
     -- Iterate over the partition boundaries and split the table
    
    DECLARE @c INT = (SELECT COUNT(*) FROM #partitions)
    ,       @i INT = 1                                 --iterator for while loop
    ,       @q NVARCHAR(4000)                          --query
    ,       @p NVARCHAR(20)     = N''                  --partition_number
    ,       @s NVARCHAR(128)    = N'dbo'               --schema
    ,       @t NVARCHAR(128)    = N'FactInternetSales' --table
    ;
    
    WHILE @i <= @c
    BEGIN
        SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i);
        SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');');
    
        -- PRINT @q;
        EXECUTE sp_executesql @q;
        SET @i+=1;
    END
    
     -- Code clean-up
    
    DROP TABLE #partitions;
    

Com essa abordagem, o código no controle de origem permanece estático e são permitidos valores de limite de particionamento dinâmicos, evoluindo com o depósito ao longo do tempo.With this approach the code in source control remains static and the partitioning boundary values are allowed to be dynamic; evolving with the warehouse over time.

Próximas etapasNext steps

Para obter mais informações sobre como desenvolver tabelas, consulte Visão geral da tabela.For more information about developing tables, see the articles on Table Overview.