CREATE TABLE AS SELECT (CTAS)

Ez a cikk a CREATE TABLE AS SELECT (CTAS) T-SQL utasítást ismerteti a megoldások fejlesztéséhez használt dedikált SQL-készletben (korábbi nevén SQL DW). A cikk kód-példákat is tartalmaz.

CREATE TABLE AS SELECT

A CREATE TABLE AS SELECT (CTAS) utasítás az egyik legfontosabb elérhető T-SQL-funkció. A CTAS egy párhuzamos művelet, amely egy SELECT utasítás kimenete alapján hoz létre egy új táblát. A CTAS az adatok egyetlen paranccsal történő létrehozásának és beszúrásának legegyszerűbb és leggyorsabb módja.

VÁLASSZA KI... INTO és CTAS

A CTAS a SELECT... INTO utasítás.

Az alábbi példa egy egyszerű SELECT... INTO:

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

VÁLASSZA KI... Az INTO nem teszi lehetővé sem a terjesztési módszer, sem az indextípus módosítását a művelet részeként. A létrehozása [dbo].[FactInternetSales_new] a ROUND_ROBIN alapértelmezett terjesztési típusával és a FÜRTÖZÖTT OSZLOPCENTRIKUS INDEX alapértelmezett táblaszerkezetével történik.

A CTAS-sel azonban megadhatja a táblaadatok eloszlását és a táblaszerkezet típusát is. Az előző példa konvertálása CTAS-vé:

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

Megjegyzés

Ha csak a CTAS-műveletben próbálja módosítani az indexet, és a forrástábla kivonatként van elosztva, tartsa fenn ugyanazt a terjesztési oszlopot és adattípust. Ez a művelet során elkerüli a keresztelosztási adatok áthelyezését, ami hatékonyabb.

Táblázat másolása a CTAS használatával

A CTAS talán egyik leggyakoribb felhasználási módja egy tábla másolatának létrehozása a DDL módosításához. Tegyük fel, hogy eredetileg a néven hozta létre a táblát ROUND_ROBIN, és most egy oszlopban elosztott táblára szeretné módosítani. A CTAS a terjesztési oszlop módosítása. A CTAS használatával is módosíthatja a particionálást, az indexelést vagy az oszloptípusokat.

Tegyük fel, hogy ezt a táblát a HEAP megadásával és az alapértelmezett terjesztési típus ROUND_ROBINhasználatával hozta létre.

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

Most létre szeretne hozni egy új példányt a táblából egy Clustered Columnstore Indexparanccsal, hogy kihasználhassa a fürtözött oszlopcentrikus táblák teljesítményét. Ezt a táblát is el szeretné osztani a táblán ProductKey, mert illesztésekre készül ezen az oszlopon, és el szeretné kerülni az adatáthelyezést az illesztések során a-on ProductKey. Végül particionálást is szeretne hozzáadni a-on OrderDateKey, hogy a régi partíciók eltávolításával gyorsan törölhesse a régi adatokat. Itt látható a CTAS-utasítás, amely a régi táblát egy új táblába másolja.

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;

Végül átnevezheti a táblákat, hogy felcserélje az új táblát, majd elvetje a régi táblát.

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

DROP TABLE FactInternetSales_old;

Explicit módon állapotadat-típus és a kimenet nullabilitása

A kód migrálásakor előfordulhat, hogy az ilyen típusú kódolási mintán futtatja a következőt:

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;

Elképzelhető, hogy át kell telepítenie ezt a kódot a CTAS-be, és helyes lenne. Van azonban egy rejtett probléma.

A következő kód nem ugyanazt az eredményt adja:

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;

Figyelje meg, hogy az "eredmény" oszlop a kifejezés adattípusát és nullability értékeit továbbítja. Ha az adattípust előre viszi, az értékek apró eltéréseihez vezethet, ha nem körültekintő.

Próbálja ki ezt a példát:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Az eredményhez tárolt érték eltérő. Mivel az eredményoszlopban megőrzött értéket más kifejezésekben is használják, a hiba még jelentősebb lesz.

A CTAS eredményeinek képernyőképe

Ez az adatmigrálások esetében fontos. Bár a második lekérdezés vitathatatlanul pontosabb, probléma merült fel. Az adatok eltérőek lennének a forrásrendszerhez képest, és ez integritási kérdésekhez vezetne a migrálás során. Ez az egyik olyan ritka eset, amikor a "rossz" válasz valójában a helyes!

A két eredmény közötti eltérés oka az implicit típusú casting. Az első példában a tábla határozza meg az oszlopdefiníciót. A sor beszúrásakor implicit típuskonverzió történik. A második példában nincs implicit típuskonverzió, mivel a kifejezés az oszlop adattípusát határozza meg.

Figyelje meg azt is, hogy a második példában szereplő oszlop NULLable oszlopként van definiálva, míg az első példában nem. Az első példában a tábla létrehozásakor explicit módon definiáltuk az oszlop nullability értékét. A második példában a kifejezés maradt, és alapértelmezés szerint NULL definíciót eredményezett.

A problémák megoldásához explicit módon be kell állítania a típuskonvertálást és a nullázhatóságot a CTAS-utasítás SELECT részében. Ezeket a tulajdonságokat nem állíthatja be a "CREATE TABLE" (CREATE TABLE) táblában. Az alábbi példa bemutatja, hogyan lehet kijavítani a kódot:

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

Vegye figyelembe a következőket:

  • Használhatja a CAST vagy a CONVERT függvényt.
  • Az ISNULL-t használja, nem a COALESCE-t a NULLability kényszerítéséhez. Tekintse meg az alábbi megjegyzést.
  • Az ISNULL a legkülső függvény.
  • Az ISNULL második része egy állandó, 0.

Megjegyzés

Ahhoz, hogy a nullability helyesen legyen beállítva, elengedhetetlen az ISNULL használata, és nem a COALESCE használata. A COALESCE nem determinisztikus függvény, ezért a kifejezés eredménye mindig NULLable lesz. AZ ISNULL más. Ez determinisztikus. Ezért ha az ISNULL függvény második része egy állandó vagy egy literál, az eredményül kapott érték NEM NULL lesz.

A számítások integritásának biztosítása a táblapartíciók közötti váltáshoz is fontos. Tegyük fel, hogy ez a tábla ténytáblaként van definiálva:

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

Az összeg mező azonban egy számított kifejezés. Ez nem része a forrásadatoknak.

Particionált adatkészlet létrehozásához érdemes lehet a következő kódot használni:

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 lekérdezés tökéletesen futna. A probléma akkor merül fel, amikor megpróbálja elvégezni a partíciókapcsolót. A tábladefiníciók nem egyeznek. A tábladefiníciók egyeztetéséhez módosítsa a CTAS-t úgy, hogy hozzáadjon egy függvényt ISNULL az oszlop nullability attribútumának megőrzéséhez.

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

Láthatja, hogy a típuskonzisztencia és a CTAS nullability tulajdonságainak fenntartása mérnöki ajánlott eljárás. Segít fenntartani az integritást a számításokban, és biztosítja a partícióváltást is.

A CTAS a Synapse SQL egyik legfontosabb utasítása. Győződjön meg róla, hogy alaposan megértette. Tekintse meg a CTAS dokumentációját.

Következő lépések

További fejlesztési tippekért tekintse meg a fejlesztés áttekintését.