CRIAR TABELA COMO SELECT (CTAS)
Este artigo explica a instrução T-SQL CREATE TABLE AS SELECT (CTAS) no conjunto de SQL dedicado (anteriormente SQL DW) para desenvolver soluções. O artigo também fornece exemplos de código.
CREATE TABLE AS SELECT
A instrução CREATE TABLE AS SELECT (CTAS) é uma das funcionalidades T-SQL mais importantes disponíveis. O CTAS é uma operação paralela que cria uma nova tabela com base na saída de uma instrução SELECT. O CTAS é a forma mais simples e rápida de criar e inserir dados numa tabela com um único comando.
SELECIONAR... INTO vs. CTAS
O CTAS é uma versão mais personalizável do SELECT... Instrução INTO .
Segue-se um exemplo de um SELECT simples... INTO:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECIONAR... O INTO não lhe permite alterar o método de distribuição nem o tipo de índice como parte da operação. Pode criar [dbo].[FactInternetSales_new] com o tipo de distribuição predefinido de ROUND_ROBIN e a estrutura de tabela predefinida do ÍNDICE COLUMNSTORE CLUSTERED.
Por outro lado, com o CTAS, pode especificar a distribuição dos dados da tabela, bem como 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];
Nota
Se estiver apenas a tentar alterar o índice na operação CTAS e a tabela de origem for distribuída por hash, mantenha a mesma coluna de distribuição e tipo de dados. Isto evita o movimento de dados de distribuição cruzada durante a operação, o que é mais eficiente.
Utilizar o CTAS para copiar uma tabela
Talvez uma das utilizações mais comuns do CTAS seja criar uma cópia de uma tabela para alterar o DDL. Digamos que criou originalmente a sua tabela como ROUND_ROBINe agora quer alterá-la para uma tabela distribuída numa coluna. O CTAS é como alteraria a coluna de distribuição. Também pode utilizar o CTAS para alterar a criação de partições, a indexação ou os tipos de coluna.
Imaginemos que criou esta tabela ao especificar HEAP e ao utilizar o tipo de distribuição predefinido 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, quer criar uma nova cópia desta tabela, com um Clustered Columnstore Index, para que possa tirar partido do desempenho das tabelas Columnstore agrupadas. Também quer distribuir esta tabela no ProductKey, porque está a antecipar associações nesta coluna e quer evitar o movimento de dados durante as associações no ProductKey. Por fim, também pretende adicionar partições no OrderDateKey, para que possa eliminar rapidamente dados antigos ao remover partições antigas. Segue-se a instrução CTAS, que copia a sua tabela antiga para 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, pode mudar o nome das tabelas para trocar na nova tabela e, em seguida, largar a tabela antiga.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Indicar explicitamente o tipo de dados e a nullabilidade da saída
Ao migrar código, poderá descobrir que executa este 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;
Poderá considerar que deve migrar este código para o CTAS e que estaria correto. No entanto, existe um problema oculto aqui.
O código seguinte 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;
Tenha em atenção que a coluna "resultado" encaminha o tipo de dados e os valores de nullabilidade da expressão. Carregar o tipo de dados para a frente pode levar a variações subtis nos valores se 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. À medida que o valor persistente na coluna de resultados é utilizado noutras expressões, o erro torna-se ainda mais significativo.
Isto é importante para migrações de dados. Embora a segunda consulta seja indiscutivelmente mais precisa, existe um problema. Os dados seriam diferentes em comparação com o sistema de origem e isso levaria a questões de integridade na migração. Este é um daqueles casos raros em que a resposta "errada" é, na verdade, a certa!
A razão pela qual vemos uma disparidade entre os dois resultados deve-se à função de fundição de tipo implícito. No primeiro exemplo, a tabela define a definição da coluna. Quando a linha é inserida, ocorre uma conversão de tipo implícito. No segundo exemplo, não existe nenhuma conversão de tipo implícito, uma vez que a expressão define o tipo de dados da coluna.
Repare também que a coluna no segundo exemplo foi definida como uma coluna NULLável, enquanto no primeiro exemplo não o fez. Quando a tabela foi criada no primeiro exemplo, a nullabilidade da coluna foi explicitamente definida. No segundo exemplo, ficou na expressão e, por predefinição, resultaria numa definição NULL.
Para resolver estes problemas, tem de definir explicitamente o tipo de conversão e nullabilidade na parte SELECT da instrução CTAS. Não pode definir estas propriedades em "CRIAR TABELA". O exemplo seguinte demonstra como corrigir o código:
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
Tenha em atenção o seguinte:
- Pode utilizar CAST ou CONVERTER.
- Utilize ISULL, não COALESCE, para forçar a NULabilidade. Veja a seguinte nota.
- ISNULL é a função mais externa.
- A segunda parte do ISULL é uma constante, 0.
Nota
Para que a nullabilidade seja definida corretamente, é vital utilizar a ISULL e não a COALESCE. COALESCE não é uma função determinista e, por isso, o resultado da expressão será sempre NULLável. ISULL é diferente. É determinista. Por conseguinte, quando a segunda parte da função ISNULL for uma constante ou um literal, o valor resultante não será NULO.
Garantir a integridade dos cálculos também é importante para a mudança de partição de tabelas. Imagine que tem esta tabela definida como uma tabela de factos:
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 de quantidade é uma expressão calculada. Não faz parte dos dados de origem.
Para criar o conjunto de dados particionado, pode utilizar o seguinte código:
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 funcionaria perfeitamente. O problema surge quando tenta fazer o comutador de partição. As definições da tabela não correspondem. Para que as definições da tabela correspondam, modifique o CTAS para adicionar uma função ISNULL para preservar o atributo de nullabilidade 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');
Pode ver que a consistência do tipo e a manutenção de propriedades de nullabilidade num CTAS é uma melhor prática de engenharia. Ajuda a manter a integridade nos seus cálculos e também garante que a mudança de partições é possível.
O CTAS é uma das instruções mais importantes no Synapse SQL. Certifique-se de que compreende completamente. Veja a documentação do CTAS.
Passos seguintes
Para obter mais sugestões de desenvolvimento, veja a descrição geral do desenvolvimento.