大网数据广告活动
数据库与应用安全

CREATE TABLE AS SELECT(Azure SQL 数据仓库)

· 52CE 我爱测

CREATE TABLE AS SELECT (CTAS) 是提供最重要的 T-SQL 功能之一。 它是一个完全并行化的操作,基于 SELECT 语句的输出创建新表。 CTAS 是创建表副本最便捷的方法。

适用对象:noSQL ServernoAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库

CREATE TABLE AS SELECT (CTAS) 是提供最重要的 T-SQL 功能之一。 它是一个完全并行化的操作,基于 SELECT 语句的输出创建新表。 CTAS 是创建表副本最便捷的方法。

例如,使用 CTAS:

  • 重新创建具有不同哈希分布列的表。
  • 重新创建一个表作为复制表。
  • 只在表的某些列上创建列存储索引。
  • 查询或导入外部数据。

Note:
由于 CTAS 增加了创建表的功能,因此本主题尽量不重复 CREATE TABLE 主题。 而是描述 CTAS 和 CREATE TABLE 语句之间的差异。 有关 CREATE TABLE 的详细信息,请参阅 CREATE TABLE(Azure SQL 数据仓库)语句。

主题链接图标 TRANSACT-SQL 语法约定

语法

CREATE TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>   
[;]  

<distribution_option> ::=
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = ROUND_ROBIN 
      | DISTRIBUTION = REPLICATE
    }   

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for SQL Data Warehouse 
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 
  
<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

参数

有关详细信息,请参阅 CREATE TABLE 中的参数部分。

列选项

column_name [ ,...n ]
列名不允许 CREATE TABLE 中提及的列选项。 相反,可以为新表提供包含一个或多个列名的可选列表。 新表中的列将使用指定的名称。 指定列名时,列表中的列数必须与所选结果中的列数相匹配。 如果未指定任何列名,新的目标表将使用 select 语句结果中的列名。

不能指定任何其他列选项,例如数据类型、排序规则或为 Null 性。 每个属性都派生自 SELECT 语句的结果。 但可以使用 SELECT 语句来更改属性。 有关示例,请参阅使用 CTAS 更改列属性。

表分发选项

DISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATE
CTAS 语句需要分布选项,并且没有默认值。 这就不同于具有默认值的 CREATE TABLE。

有关详细信息以及如何选择最佳分布列的信息,请参阅 CREATE TABLE 中的表分发选项部分。

表分区选项

即使已对源表进行分区,CTAS 语句也会默认创建一个非分区表。 若要使用 CTAS 语句创建已分区表,必须指定分区选项。

有关详细信息,请参阅 CREATE TABLE 中的表分区选项部分。

选择选项

select 语句是 CTAS 和 CREATE TABLE 之间的根本区别。

WITH common_table_expression
指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。 有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)。

SELECT select_criteria
使用 SELECT 语句的结果填充新表。 select_criteria 是 SELECT 语句的主体,用于确定将哪些数据复制到新表中。 有关 SELECT 语句的信息,请参阅 SELECT (Transact-SQL)。

Permissions

CTAS 需要 select_criteria 中引用的任何对象的 SELECT 权限。

有关创建表的权限,请参阅 CREATE TABLE 中的权限。

一般备注

有关详细信息,请参阅 CREATE TABLE 中的一般备注。

限制和局限

Azure SQL 数据仓库尚不支持自动创建或自动更新统计信息。 为从查询中获取最佳性能,运行 CTAS 以及数据发生任何实质性更改之后,请务必创建关于所有表中所有列的统计信息。 有关详细信息,请参阅 CREATE STATISTICS (Transact-SQL)。

SET ROWCOUNT (Transact-SQL) 对 CTAS 没有影响。 要实现类似的行为,请使用 TOP (Transact-SQL)。

有关详细信息,请参阅 CREATE TABLE 中的限制和局限。

锁定行为

有关详细信息,请参阅 CREATE TABLE 中的锁定行为。

“性能”

对于哈希分布式表,可使用 CTAS 选择其他分布列,提高联接和聚合性能。 如果目标不是选择其他分布列,那么指定相同的分布列可获得最佳 CTAS 性能,因为这样可以避免重新分布行。

如果使用 CTAS 创建表,并且不考虑性能,则可以指定 ROUND_ROBIN 来避免选定分布列。

若要避免数据在后续查询中发生移动,可指定 REPLICATE,但代价是增加了在每个计算节点上加载表的完整副本所用的存储。

复制表的示例

A. 使用 CTAS 复制表

适用范围:Azure SQL 数据仓库和并行数据仓库

CTAS 最常见的用途之一也许就是创建表的副本,以便用户更改 DDL。 例如,如果最初将表创建为 ROUND_ROBIN,现在希望将其更改为分布在列上的表,则可以使用 CTAS 更改分布列。 CTAS 还可用于更改分区、索引或列类型。

假设你使用默认分布类型 ROUND_ROBIN 创建了此表,因为 CREATE TABLE 中没有指定分布列。

CREATE TABLE FactInternetSales
(
    ProductKey int NOT NULL,
    OrderDateKey int NOT NULL,
    DueDateKey int NOT NULL,
    ShipDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    PromotionKey int NOT NULL,
    CurrencyKey int NOT NULL,
    SalesTerritoryKey int NOT NULL,
    SalesOrderNumber nvarchar(20) NOT NULL,
    SalesOrderLineNumber tinyint NOT NULL,
    RevisionNumber tinyint NOT NULL,
    OrderQuantity smallint NOT NULL,
    UnitPrice money NOT NULL,
    ExtendedAmount money NOT NULL,
    UnitPriceDiscountPct float NOT NULL,
    DiscountAmount float NOT NULL,
    ProductStandardCost money NOT NULL,
    TotalProductCost money NOT NULL,
    SalesAmount money NOT NULL,
    TaxAmt money NOT NULL,
    Freight money NOT NULL,
    CarrierTrackingNumber nvarchar(25),
    CustomerPONumber nvarchar(25)
);

现在,你想使用聚集列存储索引创建此表的新副本,以便可以利用聚集列存储表的性能。 因为希望联接此列,避免在联接 ProductKey 期间数据发生移动,你还想在 ProductKey 上分布此表。 最后,你还想在 OrderDateKey 上添加分区,以便通过删除旧分区来快速删除旧数据。 下面是将旧表复制到新表的 CTAS 声明。

CREATE TABLE FactInternetSales_new
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(ProductKey),
    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 FactInternetSales;

最后,可通过重新命名表来交换新表,然后删除旧表。

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

列选项的示例

B. 使用 CTAS 更改列属性

适用范围:Azure SQL 数据仓库和并行数据仓库

本示例使用 CTAS 更改 DimCustomer2 表中多个列的数据类型、为 Null 性和排序规则。

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] int NOT NULL,  
    [GeographyKey] int NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  
  
-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  
  
-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] int NOT NULL, 
    [CustomerKeyChangeNullable] int NULL, 
    [CustomerKeyChangeDataTypeNullable] decimal(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] decimal(10, 2) NOT NULL, 
    [GeographyKeyNoChange] int NULL, 
    [GeographyKeyChangeNotNullable] int NOT NULL, 
    [CustomerAlternateKeyNoChange] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] nvarchar(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

最后,可使用 RENAME (Transact-SQL) 切换表名称。 这样就使得 DimCustomer2 成为新表。

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

表分发的示例

C. 使用 CTAS 更改表的分布方法

适用范围:Azure SQL 数据仓库和并行数据仓库

此简单示例介绍如何改变表的分布方法。 为显示执行此操作的机制,它将哈希分布式表更改为轮循机制表,然后将轮循机制表更改回哈希分布式表。 最终的表与原始表相匹配。

大多数情况下,无需将哈希分布式表更改为轮循机制表。 更常见的情况是,可能需要将轮循机制表更改为哈希分布式表。 例如,可能最初将新表加载为轮循机制表,然后将其移到哈希分布式表以提高联接性能。

本示例使用 AdventureWorksDW 示例数据库。 若要加载 SQL 数据仓库版本,请参阅将示例数据加载到 SQL 数据仓库

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

接下来,将其更改回哈希分布式表。

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D. 使用 CTAS 将表转换为复制表

适用范围:Azure SQL 数据仓库和并行数据仓库

此示例适用于将轮循机制表或哈希分布式表转换为复制表。 此特殊示例采用先前进一步改变分布类型的方法。 由于 DimSalesTerritory 是一个维度,而且可能是较小的表,因此可以选择重新创建该表作为复制表,从而避免在联接到其他表时数据发生移动。

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E. 使用 CTAS 创建列数较少的表

适用范围:Azure SQL 数据仓库和并行数据仓库

以下示例创建一个名为 myTable (c, ln) 的轮循机制分布式表。 新表仅包含两列。 它使用 SELECT 语句中的列别名作为列名。

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer;  
  

查询提示的示例

F. 在 CREATE TABLE AS SELECT (CTAS) 中使用查询提示

适用范围:Azure SQL 数据仓库和并行数据仓库

此查询显示在 CTAS 语句中使用查询联接提示的基本语法。 提交查询后,SQL 数据仓库 在为每个单独的分布生成查询计划时应用哈希联接策略。 有关哈希联接查询提示的详细信息,请参阅 OPTION 子句 (Transact-SQL).

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

外部表的示例

G. 使用 CTAS 从 Azure Blob 存储导入数据

适用范围:Azure SQL 数据仓库和并行数据仓库

若要从外部表导入数据,只需使用 CREATE TABLE AS SELECT 从外部表进行选择。 从外部表选择数据到 SQL 数据仓库 中的语法与从常规表中选择数据的语法相同。

以下示例针对 Azure Blob 存储帐户中的数据定义外部表。 然后,它使用 CREATE TABLE AS SELECT 从外部表进行选择。 这样会从 Azure blob 存储文本分隔文件导入数据,并将数据存储到新的 SQL 数据仓库 表中。

--Use your own processes to create the text-delimited files on Azure blob storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url varchar(50),  
    event_date date,  
    user_IP varchar(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  
  
--Use CREATE TABLE AS SELECT to import the Azure blob storage data into a new   
--SQL Data Warehouse table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;  

H. 使用 CTAS 从外部表导入 Hadoop 数据

适用范围:并行数据仓库

若要从外部表导入数据,只需使用 CREATE TABLE AS SELECT 从外部表进行选择。 从外部表选择数据到 并行数据仓库 中的语法与从常规表中选择数据的语法相同。

以下示例针对 Hadoop 群集定义外部表。 然后,它使用 CREATE TABLE AS SELECT 从外部表进行选择。 这样会从 Hadoop 文本分隔文件导入数据,并将数据存储到新的 并行数据仓库 表中。

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url varchar(50),  
    event_date date,  
    user_IP varchar(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  
  
-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  
  
-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;   

使用 CTAS 替换 SQL Server 代码的示例

使用 CTAS 解决某些不支持的功能。 除了能够在数据仓库上运行代码之外,通过重写现有代码来使用 CTAS 通常还可以提高性能。 这是因为采用了完全并行化设计。

Note: 尽量考虑“CTAS 优先”。 如果你认为可以使用 CTAS 解决问题,那么这通常是解决问题的最佳方法(即使你会因此编写更多数据)。

I. 使用 CTAS 而不是 SELECT..INTO

适用范围:Azure SQL 数据仓库和并行数据仓库

SQL Server 代码通常借助 SELECT..INTO 来使用 SELECT 语句的结果填充表。 这是 SQL Server SELECT..INTO 语句的一个例子。

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

SQL 数据仓库和并行数据仓库不支持此语法。 本示例显示如何将之前的 SELECT..INTO 语句重写为 CTAS 语句。 你可以选择 CTAS 语法中描述的任何 DISTRIBUTION 选项。 本示例使用 ROUND_ROBIN 分布方法。

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J. 使用 CTAS 和隐式联接替换 UPDATE 语句中 FROM 子句的 ANSI 联接

适用范围:Azure SQL 数据仓库和并行数据仓库

可能会发现更新很复杂,它使用 ANSI 联接语法将两个以上的表联接在一起,以执行 UPDATE 或 DELETE 操作。

假设必须更新此表:

CREATE TABLE [dbo].[AnnualCategorySales]
(   [EnglishProductCategoryName]    NVARCHAR(50)    NOT NULL
,   [CalendarYear]                  SMALLINT        NOT NULL
,   [TotalSalesAmount]              MONEY           NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;

原始查询看起来可能如下:

UPDATE  acs
SET     [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM    [dbo].[AnnualCategorySales]     AS acs
JOIN    (
        SELECT  [EnglishProductCategoryName]
        ,       [CalendarYear]
        ,       SUM([SalesAmount])              AS [TotalSalesAmount]
        FROM    [dbo].[FactInternetSales]       AS s
        JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
        JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
        JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
        JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
        WHERE   [CalendarYear] = 2004
        GROUP BY
                [EnglishProductCategoryName]
        ,       [CalendarYear]
        ) AS fis
ON  [acs].[EnglishProductCategoryName]  = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear]                = [fis].[CalendarYear]
;

由于 SQL 数据仓库不支持 UPDATE 语句中 FROM 子句的 ANSI 联接,因此需要稍作更改才能使用此 SQL Server 代码。

可以将 CTAS 和隐式联接结合使用来替换此代码:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

K. 使用 CTAS 指定要保留的数据而不是使用 DELETE 语句中 FROM 子句的 ANSI 联接

适用范围:Azure SQL 数据仓库和并行数据仓库

有时,删除数据的最佳方法是使用 CTAS。 只需选择希望保留的数据,而不是删除数据。 这尤其适用于使用 ansi 联接语法的 DELETE 语句,因为 SQL 数据仓库不支持 DELETE 语句中 FROM 子句中的 ANSI 联接。

转换后的 DELETE 语句示例如下:

CREATE TABLE dbo.DimProduct_upsert
WITH
(   Distribution=HASH(ProductKey)
,   CLUSTERED INDEX (ProductKey)
)
AS -- Select Data you wish to keep
SELECT     p.ProductKey
,          p.EnglishProductName
,          p.Color
FROM       dbo.DimProduct p
RIGHT JOIN dbo.stg_DimProduct s
ON         p.ProductKey = s.ProductKey
;

RENAME OBJECT dbo.DimProduct        TO DimProduct_old;
RENAME OBJECT dbo.DimProduct_upsert TO DimProduct;

L. 使用 CTAS 简化 merge 语句

适用范围:Azure SQL 数据仓库和并行数据仓库

使用 CTAS 可以替换(至少部分替换)Merge 语句。 可以将 INSERT 和 UPDATE 合并为一个语句。 在第二个语句中需要关闭任何已删除的记录。

UPSERT 示例如下:

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimpProduct_upsert]  TO [DimProduct];

M. 显式声明数据类型和输出的为 Null 性

适用范围:Azure SQL 数据仓库和并行数据仓库

将 SQL Server 代码迁移到 SQL 数据仓库时,可能会遇到这种类型的编码模式:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455

CREATE TABLE result
(result DECIMAL(7,2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN)

INSERT INTO result
SELECT @d*@f
;

你可能会本能地认为应将此代码迁移到 CTAS,这无疑是正确的。 但是,这里存在一个隐藏的问题。

以下代码不会生成相同的结果:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455
;

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT @d*@f as result
;

注意,“结果”列会沿用表达式的数据类型和为 Null 性值。 稍有不慎,可能就会导致值存在细微差异。

请尝试以下示例:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

为结果存储的值不相同。 由于在其他表达式中使用了结果列中保留的值,因此差异会更大。

CREATE TABLE AS SELECT 结果

这一点对数据迁移来说尤其重要。 尽管第二个查询可能更为准确,但仍存在问题。 该数据与源系统相比有所不同,会导致迁移过程出现完整性问题。 这是一种“错误”答案实际上为正确答案的罕见情况!

我们发现这两种结果之间出现这种差异的原因是隐式类型转换。 在第一个示例中,表定义了列的定义。 插入行时会发生隐式类型转换。 在第二个示例中未发生隐式类型转换,因为表达式定义列的数据类型。 另请注意,第二个示例中的列已定义为可为 Null 的列,而第一个示例中并未如此。 在第一个示例中创建表时,显式定义了列的为 Null 性。 在第二个示例中,仅将它留给表达式,在默认情况下会产生 NULL 定义。

若要解决这些问题,则必须在 CTAS 语句的 SELECT 部分中显式设置类型转换和为 Null 性。 在创建表的部分无法设置这些属性。

以下示例演示如何修复代码:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

请注意以下事项:

  • 可能使用了 CAST 或 CONVERT
  • 使用了 ISNULL 而不是 COALESCE 强制为 Null 性
  • ISNULL 是最外面的函数
  • ISNULL 的第二部分是一个常数(即 0)

Note: 为正确设置为 Null 性,请务必使用 ISNULL 而不是 COALESCE。 COALESCE 不是确定性函数,因此表达式的结果将始终可以为 NULL。 ISNULL 则不同。 它是确定性的。 因此,当 ISNULL 函数的第二部分是常数或文本时,结果值将为 NOT NULL。

该提示不仅有助于确保计算的完整性。 它对于表格分区切换也很重要。 试想根据实际情况定义此表:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     MONEY   NOT NULL
,   [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

但值字段是一个计算表达式,不是源数据的一部分。

若要创建分区数据集,可能需要执行此操作:

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   [quantity]*[price]  AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create')
;

该查询将会顺利运行。 尝试执行分区切换时,将出现问题。 表定义不匹配。 若要使表定义匹配,则需对 CTAS 进行修改。

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

因此,你会发现类型一致性和维持 CTAS 上的为 Null 性属性是优秀工程设计的最佳做法。 它有助于保持计算的完整性,而且还可确保实现分区切换。

另请参阅

CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE(Azure SQL 数据仓库) DROP TABLE (Transact-SQL)
DROP EXTERNAL TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)
ALTER EXTERNAL TABLE (Transact-SQL)

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 CarlRabeler)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 10/07/2016
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。