CREATE TABLE como SELECT (CTAS) no Azure SQL Data WarehouseCREATE TABLE AS SELECT (CTAS) in Azure SQL Data Warehouse

Este artigo explica a instrução T-SQL CREATE TABLE como SELECT (CTAS) no Azure SQL Data Warehouse para desenvolver soluções.This article explains the CREATE TABLE AS SELECT (CTAS) T-SQL statement in Azure SQL Data Warehouse for developing solutions. O artigo também fornece exemplos de código.The article also provides code examples.

CREATE TABLE AS SELECTCREATE TABLE AS SELECT

A instrução CREATE TABLE as Select (CTAS) é um dos recursos mais importantes do T-SQL disponíveis.The CREATE TABLE AS SELECT (CTAS) statement is one of the most important T-SQL features available. CTAS é uma operação paralela que cria uma nova tabela com base na saída de uma instrução SELECT.CTAS is a parallel operation that creates a new table based on the output of a SELECT statement. O CTAS é a maneira mais simples e rápida de criar e inserir dados em uma tabela com um único comando.CTAS is the simplest and fastest way to create and insert data into a table with a single command.

SELECIONAR... NO vs. CTASSELECT...INTO vs. CTAS

CTAS é uma versão mais personalizável do Select... Instrução INTO .CTAS is a more customizable version of the SELECT...INTO statement.

Veja a seguir um exemplo de uma simples seleção... PORTAThe following is an example of a simple SELECT...INTO:

SELECT *
INTO    [dbo].[FactInternetSales_new]
FROM    [dbo].[FactInternetSales]

SELECIONAR... INTO não permite que você altere o método de distribuição ou o tipo de índice como parte da operação.SELECT...INTO doesn't allow you to change either the distribution method or the index type as part of the operation. Você cria [dbo].[FactInternetSales_new] usando o tipo de distribuição padrão de ROUND_ROBIN e a estrutura de tabela padrão do índice COLUMNSTORE CLUSTERIZAdo.You create [dbo].[FactInternetSales_new] by using the default distribution type of ROUND_ROBIN, and the default table structure of CLUSTERED COLUMNSTORE INDEX.

Com o CTAS, por outro lado, você pode especificar a distribuição dos dados da tabela, bem como o tipo de estrutura da tabela.With CTAS, on the other hand, you can specify both the distribution of the table data as well as the table structure type. Para converter o exemplo anterior em CTAS:To convert the previous example to CTAS:

CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
 DISTRIBUTION = ROUND_ROBIN
 ,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales];

Observação

Se você estiver apenas tentando alterar o índice em sua operação CTAS e a tabela de origem for de hash distribuído, mantenha a mesma coluna de distribuição e tipo de dados.If you're only trying to change the index in your CTAS operation, and the source table is hash distributed, maintain the same distribution column and data type. Isso evita a movimentação de dados de distribuição cruzada durante a operação, o que é mais eficiente.This avoids cross-distribution data movement during the operation, which is more efficient.

Usar CTAS para copiar uma tabelaUse CTAS to copy a table

Talvez um dos usos mais comuns de CTAS seja a criação de uma cópia de uma tabela para alterar o DDL.Perhaps one of the most common uses of CTAS is creating a copy of a table in order to change the DDL. Digamos que você criou sua tabela originalmente como ROUND_ROBINe agora deseja alterá-la para uma tabela distribuída em uma coluna.Let's say you originally created your table as ROUND_ROBIN, and now want to change it to a table distributed on a column. CTAS é como você alteraria a coluna de distribuição.CTAS is how you would change the distribution column. Você também pode usar CTAS para alterar o particionamento, a indexação ou os tipos de coluna.You can also use CTAS to change partitioning, indexing, or column types.

Digamos que você criou essa tabela usando o tipo de distribuição padrão de ROUND_ROBIN, não especificando uma coluna de distribuição na CREATE TABLE.Let's say you created this table by using the default distribution type of ROUND_ROBIN, not specifying a distribution column in the 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));

Agora você deseja criar uma nova cópia dessa tabela, com um Clustered Columnstore Index, para poder aproveitar o desempenho das tabelas Columnstore clusterizadas.Now you want to create a new copy of this table, with a Clustered Columnstore Index, so you can take advantage of the performance of Clustered Columnstore tables. Você também deseja distribuir essa tabela no ProductKey, pois você está prevendo junções nesta coluna e deseja evitar a movimentação de dados durante junções em ProductKey.You also want to distribute this table on ProductKey, because you're anticipating joins on this column and want to avoid data movement during joins on ProductKey. Por fim, você também deseja adicionar o particionamento em OrderDateKey, para que possa excluir rapidamente dados antigos descartando partições antigas.Lastly, you also want to add partitioning on OrderDateKey, so you can quickly delete old data by dropping old partitions. Aqui está a instrução CTAS, que copia sua tabela antiga em uma nova tabela.Here is the CTAS statement, which copies your old table into a new table.

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;

Por fim, você pode renomear suas tabelas, para alternar em sua nova tabela e, em seguida, descartar a tabela antiga.Finally, you can rename your tables, to swap in your new table and then drop your old table.

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

DROP TABLE FactInternetSales_old;

Use CTAS para contornar recursos sem suporteUse CTAS to work around unsupported features

Você também pode usar o CTAS para contornar uma série de recursos sem suporte listados abaixo.You can also use CTAS to work around a number of the unsupported features listed below. Esse método pode, muitas vezes, provar útil, pois não apenas seu código será compatível, mas será executado com frequência mais rápido no SQL Data Warehouse.This method can often prove helpful, because not only will your code be compliant, but it will often run faster on SQL Data Warehouse. Esse desempenho é resultado de seu design totalmente paralelizado.This performance is a result of its fully parallelized design. Os cenários incluem:Scenarios include:

  • ANSI JOINS em instruções UPDATEANSI JOINS on UPDATEs
  • ANSI JOINs em instruções DELETEANSI JOINs on DELETEs
  • Instrução MERGEMERGE statement

Dica

Tente imaginar "CTAS primeiro".Try to think "CTAS first." Resolver um problema usando CTAS geralmente é uma boa abordagem, mesmo que você esteja escrevendo mais dados como resultado.Solving a problem by using CTAS is generally a good approach, even if you're writing more data as a result.

Substituição da junção ANSI para atualizar instruçõesANSI join replacement for update statements

Você pode achar que tem uma atualização complexa.You might find that you have a complex update. A atualização une mais de duas tabelas usando a sintaxe de junção ANSI para executar a atualização ou exclusão.The update joins more than two tables together by using ANSI join syntax to perform the UPDATE or DELETE.

Imagine que você precisava atualizar esta tabela:Imagine you had to update this table:

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

A consulta original pode ter se visto como neste exemplo:The original query might have looked something like this example:

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 Data Warehouse não dá suporte a junções ANSI na cláusula FROM de uma instrução UPDATE, portanto, você não pode usar o exemplo anterior sem modificá-lo.SQL Data Warehouse doesn't support ANSI joins in the FROM clause of an UPDATE statement, so you can't use the previous example without modifying it.

Você pode usar uma combinação de um CTAS e uma junção implícita para substituir o exemplo anterior:You can use a combination of a CTAS and an implicit join to replace the previous example:

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

Substituição de junção ANSI para instruções deleteANSI join replacement for delete statements

Às vezes, a melhor abordagem para excluir dados é usar o CTAS, especialmente para DELETE instruções que usam a sintaxe de junção ANSI.Sometimes the best approach for deleting data is to use CTAS, especially for DELETE statements that use ANSI join syntax. Isso ocorre porque SQL Data Warehouse não dá suporte a junções ANSI na cláusula FROM de uma instrução DELETE.This is because SQL Data Warehouse doesn't support ANSI joins in the FROM clause of a DELETE statement. Em vez de excluir os dados, selecione os dados que deseja manter.Rather than deleting the data, select the data you want to keep.

Veja a seguir um exemplo de uma instrução de DELETE convertida:The following is an example of a converted DELETE statement:

CREATE TABLE dbo.DimProduct_upsert
WITH
(   Distribution=HASH(ProductKey)
,   CLUSTERED INDEX (ProductKey)
)
AS -- Select Data you want 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;

Substituir instruções mergeReplace merge statements

Você pode substituir instruções MERGE, pelo menos em parte, usando CTAS.You can replace merge statements, at least in part, by using CTAS. Você pode combinar o INSERT e o UPDATE em uma única instrução.You can combine the INSERT and the UPDATE into a single statement. Todos os registros excluídos devem ser restritos da instrução SELECT para omitir dos resultados.Any deleted records should be restricted from the SELECT statement to omit from the results.

O exemplo a seguir é para um UPSERT:The following example is for an 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.[DimProduct_upsert]  TO [DimProduct];

Declarar explicitamente o tipo de dados e a nulidade da saídaExplicitly state data type and nullability of output

Ao migrar o código, você pode achar que é executado por esse tipo de padrão de codificação:When migrating code, you might find you run across this type of coding pattern:

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;

Você pode pensar que deve migrar esse código para CTAS e estaria correto.You might think you should migrate this code to CTAS, and you'd be correct. No entanto, há um problema oculto aqui.However, there's a hidden issue here.

O código a seguir não produz o mesmo resultado:The following code doesn't yield the same result:

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;

Observe que a coluna “resultado” transporta os valores de tipo e a nulidade de dados da expressão.Notice that the column "result" carries forward the data type and nullability values of the expression. A realização do tipo de dados Forward pode levar a variações sutis em valores se você não tiver cuidado.Carrying the data type forward can lead to subtle variances in values if you aren't careful.

Experimente este exemplo:Try this example:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

O valor armazenado para o resultado é diferente.The value stored for result is different. Como o valor persistente na coluna de resultado é usado em outras expressões, o erro se torna ainda mais significativo.As the persisted value in the result column is used in other expressions, the error becomes even more significant.

Captura de tela dos resultados do CTAS

Isso é importante para migrações de dados.This is important for data migrations. Embora a segunda consulta seja, de forma alguma, mais precisa, há um problema.Even though the second query is arguably more accurate, there's a problem. Os dados seriam diferentes em comparação com o sistema de origem e isso leva a perguntas de integridade na migração.The data would be different compared to the source system, and that leads to questions of integrity in the migration. Esse é um dos casos raros em que a resposta “incorreta” é realmente a melhor!This is one of those rare cases where the "wrong" answer is actually the right one!

O motivo pelo qual vemos uma diparidade entre os dois resultados é devido à conversão de tipo implícito.The reason we see a disparity between the two results is due to implicit type casting. No primeiro exemplo, a tabela define a definição de coluna.In the first example, the table defines the column definition. Quando a linha é inserida, ocorre uma conversão implícita de tipo.When the row is inserted, an implicit type conversion occurs. No segundo exemplo, não há nenhuma conversão implícita de tipo, pois a expressão define o tipo de dados da coluna.In the second example, there is no implicit type conversion as the expression defines the data type of the column.

Observe também que a coluna no segundo exemplo foi definida como uma coluna anulável, enquanto que no primeiro exemplo não tem.Notice also that the column in the second example has been defined as a NULLable column, whereas in the first example it has not. Quando a tabela foi criada no primeiro exemplo, a nulidade da coluna foi explicitamente definida.When the table was created in the first example, column nullability was explicitly defined. No segundo exemplo, ele foi deixado para a expressão e, por padrão, resultaria em uma definição nula.In the second example, it was left to the expression, and by default would result in a NULL definition.

Para resolver esses problemas, você deve definir explicitamente a conversão de tipo e a nulidade na parte SELECT da instrução CTAS.To resolve these issues, you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. Você não pode definir essas propriedades em ' CREATE TABLE '.You can't set these properties in 'CREATE TABLE'. O exemplo a seguir demonstra como corrigir o código:The following example demonstrates how to fix the code:

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

Observe o seguinte:Note the following:

  • Você pode usar CAST ou CONVERT.You can use CAST or CONVERT.
  • Use ISNULL, not enunidas, para forçar a nulidade.Use ISNULL, not COALESCE, to force NULLability. Consulte a observação a seguir.See the following note.
  • ISNULL é a função mais externa.ISNULL is the outermost function.
  • A segunda parte de ISNULL é uma constante, 0.The second part of the ISNULL is a constant, 0.

Observação

Para que a nulidade seja definida corretamente, é vital usar ISNULL e não adesão.For the nullability to be correctly set, it's vital to use ISNULL and not COALESCE. A adesão não é uma função determinística e, portanto, o resultado da expressão sempre será anulável.COALESCE is not a deterministic function, and so the result of the expression will always be NULLable. Com ISNULL, é diferente.ISNULL is different. É determinístico.It's deterministic. Portanto, quando a segunda parte da função ISNULL for uma constante ou um literal, o valor resultante não será nulo.Therefore, when the second part of the ISNULL function is a constant or a literal, the resulting value will be NOT NULL.

Garantir a integridade dos cálculos também é importante para a alternância de partição de tabela.Ensuring the integrity of your calculations is also important for table partition switching. Imagine que você tenha essa tabela definida como uma tabela de fatos:Imagine you have this table defined as a fact table:

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

No entanto, o campo Amount é uma expressão calculada.However, the amount field is a calculated expression. Ele não faz parte dos dados de origem.It isn't part of the source data.

Para criar seu conjunto de espaço de os, você pode desejar usar o seguinte código:To create your partitioned dataset, you might want to use the following code:

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

A consulta seria executada perfeitamente bem.The query would run perfectly well. O problema surge quando você tenta fazer a alternância de partição.The problem comes when you try to do the partition switch. As definições de tabela não correspondem.The table definitions don't match. Para que as definições de tabela coincidam, modifique o CTAS para adicionar uma função de ISNULL para preservar o atributo de nulidade da coluna.To make the table definitions match, modify the CTAS to add an ISNULL function to preserve the column's nullability attribute.

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

Você pode ver que a consistência de tipo e a manutenção de propriedades de nulidade em um CTAS é uma prática recomendada de engenharia.You can see that type consistency and maintaining nullability properties on a CTAS is an engineering best practice. Ele ajuda a manter a integridade em seus cálculos e também garante que a alternância de partição seja possível.It helps to maintain integrity in your calculations, and also ensures that partition switching is possible.

CTAS é uma das instruções mais importantes em SQL Data Warehouse.CTAS is one of the most important statements in SQL Data Warehouse. Certifique-se compreendê-la totalmente.Make sure you thoroughly understand it. Consulte a documentação do CTAS.See the CTAS documentation.

Próximas etapasNext steps

Para obter mais dicas de desenvolvimento, confira a visão geral sobre desenvolvimento.For more development tips, see the development overview.