azure-sql-data-warehouse - ALTER TABLE SWITCH 语句失败。表 X 中的分区 1 定义的范围不是表 Y 中的分区 2 定义的范围的子集

Question

我正在尝试根据此处https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data的“使用分区切换进行优化”部分在 Azure SQL DataWarehouse 中创建一个简单的分区交换例程-仓库开发最佳实践交易

我认为我有我试图交换对齐的分区，但收到一个错误，似乎告诉我它们不是（ALTER TABLE SWITCH 语句失败。范围由表 'Distribution_55.dbo.Table_42b5ce68198a4fe1a2c5a597075b93d5_55 中的分区 1 定义'不是表'Distribution_55.dbo.Table_62915da3af53441980fedba6da729c62_55'中分区2定义的范围的子集）

这是我的完整复制品：

--Create a view for us to use to look up the partition numbers later
CREATE VIEW dbo.TablePartitions
AS
    SELECT
        s.name SchemaName
        ,t.name TableName
        ,CAST(r.value as nvarchar(128)) BoundaryValue
        ,p.partition_number PartitionNumber
    FROM        
        sys.schemas s
        JOIN sys.tables t
            ON s.[schema_id] = t.[schema_id]
        JOIN sys.indexes i
            ON t.[object_id] = i.[object_id]
        JOIN sys.partitions p 
            ON i.[object_id] = p.[object_id] 
            AND i.[index_id] = p.[index_id] 
        JOIN sys.partition_schemes h 
            ON i.[data_space_id] = h.[data_space_id]
        JOIN sys.partition_functions f
            ON h.[function_id] = f.[function_id]
        LEFT JOIN sys.partition_range_values r
            ON f.[function_id] = r.[function_id] 
            AND r.[boundary_id] = p.[partition_number]
    WHERE 
        i.[index_id] <= 1;

--Create our main partitioned table
CREATE TABLE [dbo].[PartitionedTable](
    [DistributionField] [nvarchar](30) NOT NULL,
    [PartitionField] [int] NOT NULL,
    [Value] [int] NOT NULL
)
WITH (
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE RIGHT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)

--Create the main table's partition boundaries
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (3)

--Create a staging table for partition swapping
CREATE TABLE [dbo].[PartitionedTableStaging]
WITH
(    
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE RIGHT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM    [dbo].[PartitionedTable]
WHERE 1=2

--Create boundaries that will align the partition that PartitionValue = 2 will fall into
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (3)

--Load the staging table with values where PartitionValue = 2
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('X', 2, 1)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Y', 2, 2)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Z', 2, 3)

--Find the partition numbers that we will swap
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTable' and BoundaryValue = 2
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTableStaging' and BoundaryValue = 2

--Swap the staged partition over to the main table
ALTER TABLE PartitionedTableStaging SWITCH PARTITION 1 TO PartitionedTable PARTITION 2;

保持 PartitionField = 2 的分区的边界不是对齐的吗？

标签： azure-sql-data-warehouse

Answer 1

原来我误解了 RANGE RIGHT 和 RANGE LEFT 的工作原理。一方面，RANGE RIGHT 将值（2 是 repro 关注的值）放入分区 3 而不是分区 2。如果您将 repro 更改为使用 RANGE LEFT，并在暂存表上创建分区 2 的下限（通过为值 1) 创建边界，然后将暂存表和活动表上的分区 2 对齐，并且交换工作。这是修正后的样本：

--Create a view for us to use to look up the partition numbers later
CREATE VIEW dbo.TablePartitions
AS
    SELECT
        s.name SchemaName
        ,t.name TableName
        ,CAST(r.value as nvarchar(128)) BoundaryValue
        ,p.partition_number PartitionNumber
    FROM        
        sys.schemas s
        JOIN sys.tables t
            ON s.[schema_id] = t.[schema_id]
        JOIN sys.indexes i
            ON t.[object_id] = i.[object_id]
        JOIN sys.partitions p 
            ON i.[object_id] = p.[object_id] 
            AND i.[index_id] = p.[index_id] 
        JOIN sys.partition_schemes h 
            ON i.[data_space_id] = h.[data_space_id]
        JOIN sys.partition_functions f
            ON h.[function_id] = f.[function_id]
        LEFT JOIN sys.partition_range_values r
            ON f.[function_id] = r.[function_id] 
            AND r.[boundary_id] = p.[partition_number]
    WHERE 
        i.[index_id] <= 1;

--Create our main partitioned table
CREATE TABLE [dbo].[PartitionedTable](
    [DistributionField] [nvarchar](30) NOT NULL,
    [PartitionField] [int] NOT NULL,
    [Value] [int] NOT NULL
)
WITH (
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE LEFT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)

--Create the main table's partition boundaries
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (2)
ALTER TABLE dbo.[PartitionedTable] SPLIT RANGE (3)

--Create a staging table for partition swapping
CREATE TABLE [dbo].[PartitionedTableStaging]
WITH
(    
    DISTRIBUTION = HASH( [DistributionField] ),
    PARTITION ( [PartitionField] RANGE LEFT FOR VALUES() ),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM    [dbo].[PartitionedTable]
WHERE 1=2

--Create boundaries that will align the partition that PartitionValue = 2 will fall into
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (1)
ALTER TABLE dbo.[PartitionedTableStaging] SPLIT RANGE (2)

--Load the staging table with values where PartitionValue = 2
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('X', 2, 1)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Y', 2, 2)
INSERT INTO PartitionedTableStaging (DistributionField, PartitionField, Value) VALUES ('Z', 2, 3)

--Find the partition numbers that we will swap
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTable' and BoundaryValue = 2
select * from TablePartitions where SchemaName = 'dbo' and TableName = 'PartitionedTableStaging' and BoundaryValue = 2

--Swap the staged partition over to the main table
ALTER TABLE PartitionedTableStaging SWITCH PARTITION 2 TO PartitionedTable PARTITION 2;