CREATE TABLE AS SELECT (CTAS)
In dit artikel wordt de T-SQL-instructie CREATE TABLE AS SELECT (CTAS) in een toegewezen SQL-pool (voorheen SQL DW) uitgelegd voor het ontwikkelen van oplossingen. Het artikel bevat ook codevoorbeelden.
CREATE TABLE AS SELECT
De instructie CREATE TABLE AS SELECT (CTAS) is een van de belangrijkste T-SQL-functies die beschikbaar zijn. CTAS is een parallelle bewerking waarmee een nieuwe tabel wordt gemaakt op basis van de uitvoer van een SELECT-instructie. CTAS is de eenvoudigste en snelste manier om gegevens te maken en in een tabel in te voegen met één opdracht.
SELECTEER... INTO versus CTAS
CTAS is een meer aanpasbare versie van de SELECT... INTO-instructie .
Hier volgt een voorbeeld van een eenvoudige SELECT... IN:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECTEER... Met INTO kunt u de distributiemethode of het indextype niet wijzigen als onderdeel van de bewerking. U maakt [dbo].[FactInternetSales_new] met behulp van het standaarddistributietype ROUND_ROBIN en de standaardtabelstructuur van CLUSTERED COLUMNSTORE INDEX.
Met CTAS kunt u daarentegen zowel de distributie van de tabelgegevens als het type tabelstructuur opgeven. Het vorige voorbeeld converteren naar CTAS:
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Notitie
Als u alleen de index in uw CTAS-bewerking probeert te wijzigen en de brontabel hash-gedistribueerd is, behoudt u dezelfde distributiekolom en hetzelfde gegevenstype. Dit voorkomt dat gegevens over meerdere distributies worden verplaatst tijdens de bewerking, wat efficiënter is.
CTAS gebruiken om een tabel te kopiëren
Misschien is een van de meest voorkomende toepassingen van CTAS het maken van een kopie van een tabel om de DDL te wijzigen. Stel dat u de tabel oorspronkelijk hebt gemaakt als ROUND_ROBINen deze nu wilt wijzigen in een tabel die is verdeeld over een kolom. CTAS is de wijze waarop u de distributiekolom wijzigt. U kunt CTAS ook gebruiken om partitionering, indexering of kolomtypen te wijzigen.
Stel dat u deze tabel hebt gemaakt door HEAP op te geven en het standaarddistributietype te ROUND_ROBINgebruiken.
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
);
Nu wilt u een nieuwe kopie van deze tabel maken, met een Clustered Columnstore Index, zodat u kunt profiteren van de prestaties van geclusterde columnstore-tabellen. U wilt deze tabel ook distribueren op ProductKey, omdat u op joins in deze kolom anticipeert en gegevensverplaatsing wilt voorkomen tijdens joins op ProductKey. Ten slotte wilt u ook partitionering toevoegen aan OrderDateKey, zodat u snel oude gegevens kunt verwijderen door oude partities te verwijderen. Hier volgt de CTAS-instructie, waarmee de oude tabel naar een nieuwe tabel wordt gekopieerd.
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;
Ten slotte kunt u de naam van uw tabellen wijzigen om de nieuwe tabel te wisselen en vervolgens de oude tabel te verwijderen.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Gegevenstype en nullabiliteit van uitvoer expliciet vermelden
Wanneer u code migreert, ziet u mogelijk dat u dit type coderingspatroon uitvoert:
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;
U denkt misschien dat u deze code naar CTAS moet migreren en u hebt gelijk. Er is hier echter een verborgen probleem.
De volgende code levert niet hetzelfde resultaat op:
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;
U ziet dat de kolom 'result' het gegevenstype en de null-waarden van de expressie bevat. Als u niet voorzichtig bent, kan dit leiden tot subtiele afwijkingen in waarden.
Probeer dit voorbeeld:
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
De waarde die is opgeslagen voor het resultaat is anders. Naarmate de persistente waarde in de resultaatkolom wordt gebruikt in andere expressies, wordt de fout nog belangrijker.
Dit is belangrijk voor gegevensmigraties. Hoewel de tweede query waarschijnlijk nauwkeuriger is, is er een probleem. De gegevens zouden anders zijn in vergelijking met het bronsysteem en dat leidt tot vragen over integriteit in de migratie. Dit is een van die zeldzame gevallen waarin het 'verkeerde' antwoord eigenlijk het juiste is!
De reden dat we een verschil tussen de twee resultaten zien, is vanwege impliciete typecasting. In het eerste voorbeeld definieert de tabel de kolomdefinitie. Wanneer de rij wordt ingevoegd, vindt er een impliciete typeconversie plaats. In het tweede voorbeeld is er geen impliciete typeconversie omdat de expressie het gegevenstype van de kolom definieert.
U ziet ook dat de kolom in het tweede voorbeeld is gedefinieerd als een kolom NULLable, maar in het eerste voorbeeld niet. Toen de tabel in het eerste voorbeeld werd gemaakt, werd de null-waarde van de kolom expliciet gedefinieerd. In het tweede voorbeeld werd dit overgelaten aan de expressie en zou dit standaard resulteren in een NULL-definitie.
Als u deze problemen wilt oplossen, moet u expliciet de typeconversie en null-waarde instellen in het SELECT-gedeelte van de CTAS-instructie. U kunt deze eigenschappen niet instellen in 'CREATE TABLE'. In het volgende voorbeeld ziet u hoe u de code kunt herstellen:
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
Houd rekening met het volgende:
- U kunt CAST of CONVERT gebruiken.
- Gebruik ISNULL, niet COALESCE, om NULLability af te dwingen. Zie de volgende opmerking.
- ISNULL is de buitenste functie.
- Het tweede deel van de ISNULL is een constante, 0.
Notitie
Om de null-functionaliteit correct in te stellen, is het essentieel om ISNULL te gebruiken en niet COALESCE. COALESCE is geen deterministische functie, dus het resultaat van de expressie is altijd NULLable. ISNULL is anders. Het is deterministisch. Dus wanneer het tweede deel van de functie ISNULL een constante of een letterlijke waarde is, is de resulterende waarde NIET NULL.
Het waarborgen van de integriteit van uw berekeningen is ook belangrijk voor het schakelen tussen tabelpartities. Stel dat u deze tabel hebt gedefinieerd als een feitentabel:
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
)
)
);
Het veld hoeveelheid is echter een berekende expressie. Het maakt geen deel uit van de brongegevens.
Als u uw gepartitioneerde gegevensset wilt maken, kunt u de volgende code gebruiken:
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');
De query zou perfect worden uitgevoerd. Het probleem treedt op wanneer u de partitieswitch probeert uit te voeren. De tabeldefinities komen niet overeen. Als u wilt dat de tabeldefinities overeenkomen, wijzigt u de CTAS om een ISNULL functie toe te voegen om het kenmerk nullability van de kolom te behouden.
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');
U kunt zien dat typeconsistentie en het onderhouden van null-eigenschappen op een CTAS een technische best practice is. Het helpt de integriteit van uw berekeningen te behouden en zorgt er ook voor dat schakelen tussen partities mogelijk is.
CTAS is een van de belangrijkste instructies in Synapse SQL. Zorg ervoor dat u het goed begrijpt. Zie de CTAS-documentatie.
Volgende stappen
Zie het ontwikkelingsoverzicht voor meer tips voor ontwikkeling.