CREATE TABLE AS SELECT (CTAS)

Este artigo explica a instrução CREATE TABLE AS SELECT (CTAS) do T-SQL no SQL do Synapse para desenvolvimento de soluções. O artigo também fornece exemplos de códigos.

CREATE TABLE AS SELECT

A instruçãoCREATE TABLE AS SELECT (CTAS) é um dos recursos do T-SQL mais importantes disponíveis. CTAS é uma operação paralela que cria uma nova tabela com base na saída de uma instrução SELECT. CTAS é a maneira mais simples e rápida de criar e inserir dados em uma tabela com um único comando.

SELECT...INTO versus CTAS

CTAS é uma versão mais personalizável da instrução SELECT...INTO.

Veja a seguir um exemplo de uma instrução SELECT...INTO simples:

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

Essa instrução não permite alterar o método de distribuição ou o tipo de índice como parte da operação. Você cria [dbo].[FactInternetSales_new] usando o tipo de distribuição padrão ROUND_ROBIN e a estrutura de tabela padrão CLUSTERED COLUMNSTORE INDEX.

Com o CTAS, por outro lado, você pode especificar tanto a distribuição dos dados da tabela quanto o tipo de estrutura da tabela. Para converter o exemplo anterior em CTAS:

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

Observação

Se você estiver tentando alterar apenas o índice em sua operação CTAS e a tabela de origem for distribuída por hash, mantenha a mesma coluna de distribuição e o mesmo tipo de dados. Isso evitará a movimentação de dados de distribuição cruzada durante a operação, o que é mais eficiente.

Usar CTAS para copiar uma tabela

Talvez um dos tipos de uso mais comuns de CTAS é criar uma cópia de uma tabela para que você possa alterar a DDL. Vamos supor que você originalmente criou a tabela como ROUND_ROBIN, e agora você quer mudá-la para uma tabela distribuída em colunas. Com CTAS, você alteraria a coluna de distribuição. Também é possível usar CTAS para alterar os tipos de particionamento, de indexação ou de coluna.

Vamos supor que você criou esta tabela usando o tipo de distribuição padrão de ROUND_ROBIN distribuído porque nenhuma coluna de distribuição foi especificada no 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 umClustered Columnstore Index para poder usufruir do desempenho das tabelas Columnstore Clusterizadas. Você também deseja distribuir essa tabela em ProductKey, pois prevê que haverá junções nessa coluna e deseja evitar a movimentação de dados durante a junções em ProductKey. Por fim, você também deseja adicionar o particionamento em OrderDateKey para que seja possível excluir rapidamente os dados antigos descartando as partições antigas. Aqui está a instrução CTAS, que copiaria a tabela antiga em uma nova tabela.

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 as tabelas para fazer a troca pela nova tabela e, em seguida, remover a tabela antiga.

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

DROP TABLE FactInternetSales_old;

Usando CTAS para resolver os recursos sem suporte

A CTAS também pode ser usada como solução alternativa dos diversos recursos incompatíveis listados abaixo. Este método pode muitas vezes revelar-se útil, porque não só o seu código será compatível, como também será executado mais rapidamente em SQL do Synapse. Este é um resultado de seu design totalmente em paralelo. Os cenários incluem:

  • ANSI JOINS em instruções UPDATE
  • ANSI JOINs em instruções DELETE
  • Instrução MERGE

Dica

Tente imaginar "CTAS primeiro". Resolver um problema usando CTAS geralmente é uma boa abordagem, mesmo se você estiver escrevendo mais dados como resultado.

Substituição de junção ANSI para instruções de atualização

Você pode achar que tem uma atualização complexa. A atualização une mais de duas tabelas usando a sintaxe de junção ANSI para executar UPDATE ou DELETE.

Imagine que você precisasse atualizar esta tabela:

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

A consulta original seria semelhante a esta:

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 do Synapse não dá suporte a junções ANSI na FROMcláusula de uma UPDATE instrução, portanto, você não pode usar o exemplo anterior sem modificá-lo.

É possível usar uma combinação de um CTAS e uma junção implícita para substituir este código:

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

No Azure Synapse Analytics, MERGE (versão prévia) com NOT MATCHED BY TARGET requer que o destino seja uma tabela distribuída por HASH. Os usuários podem usar o ANSI JOIN com UPDATE ou DELETE como uma solução alternativa para modificar os dados de tabela de destino com base no resultado da junção com outra tabela. Veja um exemplo.

CREATE TABLE dbo.Table1   
    (ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
CREATE TABLE dbo.Table2   
    (ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);  
GO  
INSERT INTO dbo.Table1 VALUES(1, 10.0);  
INSERT INTO dbo.Table2 VALUES(1, 0.0);  
GO  
UPDATE dbo.Table2   
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB  
FROM dbo.Table2   
    INNER JOIN dbo.Table1   
    ON (dbo.Table2.ColA = dbo.Table1.ColA);  
GO  
SELECT ColA, ColB   
FROM dbo.Table2;

Declarar explicitamente o tipo de dados e a nulidade da saída

Ao migrar o código, você pode achar executar por esse tipo de padrão de codificação:

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 isso estaria correto. No entanto, há um problema oculto aqui.

O código a seguir não produz o mesmo resultado:

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" transfere os valores de tipo de dados e de nulidade da expressão. Carregar o tipo de dados para frente poderá levar a variações sutis em valores se você não tiver cuidado.

Experimente este exemplo:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

O valor armazenado para o resultado é diferente. Como o valor persistente na coluna de resultado é usado em outras expressões, o erro torna-se ainda mais significativo.

Captura de tela dos resultados CTAS

Isso é importante principalmente para migrações de dados. Embora a segunda consulta seja indiscutivelmente mais precisa, há um problema. Os dados seriam diferentes em comparação com o sistema de origem, o que levantaria dúvidas sobre a integridade da migração. Este é um dos raros casos em que a resposta "errada" é, na verdade, a melhor!

O motivo pelo qual existe essa discrepância entre os dois resultados se deve à conversão de tipo implícito. No primeiro exemplo, a tabela define a definição de coluna. Quando a linha é inserida, ocorre uma conversão implícita de tipo. No segundo exemplo, não há nenhuma conversão implícita de tipo, pois a expressão define o tipo de dados da coluna.

Observe também que a coluna no segundo exemplo foi definida como uma coluna anulável, mas no primeiro exemplo ela não foi. Quando a tabela foi criada no primeiro exemplo, a nulidade da coluna foi definida explicitamente. No segundo exemplo, isso ficou a cargo da expressão, o que, por padrão, resultaria em uma definição nula.

Para resolver esses problemas, é necessário definir explicitamente a conversão de tipo e a nulidade na parte SELECT da instrução CTAS. Você não pode definir essas propriedades em 'CREATE TABLE'. O exemplo a seguir demonstra como fazer isso:

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:

  • Você pode usar CAST ou CONVERT.
  • Use ISNULL, e não COALESCE, para forçar a nulidade. Veja a observação a seguir.
  • ISNULL é a função mais distante.
  • A segunda parte de ISNULL é uma constante, ou seja, 0.

Observação

Para a nulidade ser definida corretamente, é crucial usar ISNULL, e não COALESCE. COALESCE não é uma função determinística e, por isso, o resultado da expressão sempre será anulável. Com ISNULL, é diferente. É determinístico. Portanto, quando a segunda parte da função ISNULL for uma constante ou um literal, o valor resultante será NOT NULL.

Garantir a integridade dos cálculos também é importante para a alternância de partição de tabela. Imagine que você tenha esta tabela definida como tabela de fatos:

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 valor é uma expressão calculada. Ele não faz parte dos dados de origem.

Para criar o conjunto de dados particionado, é possível fazer o seguinte:

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 de forma perfeitamente normal. O problema aparece quando você tenta executar a alternância de partição. As definições de tabela não correspondem. Para que as definições de tabela coincidam, modifique o CTAS para adicionar uma função ISNULL para preservar o atributo de nulidade da coluna.

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

Portanto, veja que manter a consistência de tipo e manter as propriedades de nulidade em um CTAS são uma prática recomendada de engenharia. Isso ajuda a manter a integridade em seus cálculos e também garante que a alternância de partição seja possível.

CTAS é uma das instruções mais importantes no SQL do Synapse. Certifique-se compreendê-la totalmente. Confira a documentação CTAS.

Próximas etapas

Para obter mais dicas de desenvolvimento, confira a visão geral sobre desenvolvimento.