CREATE TABLE AS SELECT (CTAS)
Este artigo explica a instrução CREATE TABLE AS SELECT (CTAS) do T-SQL no pool SQL dedicado (anteriormente SQL DW) 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 especificando HEAP e usando o tipo de distribuição padrão de ROUND_ROBIN.
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)
)
WITH(
HEAP,
DISTRIBUTION = ROUND_ROBIN
);
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;
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.
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.