TÁBLA LÉTREHOZÁSA KIJELÖLÉSKÉNT (CTAS)

Ez a cikk a CREATE TABLE AS SELECT (CTAS) T-SQL utasítást ismerteti a dedikált SQL-készletben (korábbi nevén SQL DW) a megoldások fejlesztéséhez. 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 a legegyszerűbb és leggyorsabb módszer arra, hogy egyetlen paranccsal hozzon létre és szúrjon be adatokat egy táblába.

KIVÁLASZT... INTO és CTAS

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

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

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

KIVÁLASZT... 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áshoz [dbo].[FactInternetSales_new] használja a ROUND_ROBIN alapértelmezett terjesztési típusát és a FÜRTÖZÖTT OSZLOPCENTRIKUS INDEX alapértelmezett táblastruktúráját.

A CTAS-ben 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];

Feljegyzés

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

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

A CTAS talán az 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 táblát a következőképpen ROUND_ROBINhozta létre, és most egy oszlopon elosztott táblára szeretné módosítani. A CTAS segítségével módosíthatja a terjesztési oszlopot. 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 a tábla új példányát szeretné létrehozni egy Clustered Columnstore Index, a fürtözött oszlopcentrikus táblák teljesítményének kihasználásához. Ezt a táblát ProductKeyazért is szeretné elosztani, mert illesztésekre készül ezen az oszlopon, és szeretné elkerülni az adatáthelyezést az illesztések során.ProductKey Végül particionálást OrderDateKeyis szeretne hozzáadni, hogy a régi partíciók elvetésével gyorsan törölhesse a régi adatokat. Itt található 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, felcserélheti az új táblát, majd elvetheti 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 állapot adattípus és a kimenet nullabilitása

A kód migrálása során előfordulhat, hogy az ilyen típusú kódolási mintákat futtatja:

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. Itt azonban van 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 továbbviszi a kifejezés adattípusát és nullitási értékeit. 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 megmaradó értéket más kifejezésekben is használják, a hiba még jelentősebb lesz.

Ez az adatmigrálások esetében fontos. Annak ellenére, hogy a második lekérdezés vitathatatlanul pontosabb, van egy probléma. Az adatok eltérőek lennének a forrásrendszerhez képest, és ez a migrálás integritási kérdéseihez vezetne. Ez egyike azoknak a ritka eseteknek, amikor a "rossz" válasz valójában a megfelelő!

A két eredmény közötti eltérés oka az implicit típusú öntés. Az első példában a tábla határozza meg az oszlopdefiníciót. A sor beszúrása után implicit típusátalakítás történik. A második példában nincs implicit típusátalakítás, 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. Amikor az első példában létrehozta a táblát, az oszlop nullbilitása explicit módon lett definiálva. A második példában a kifejezés maradt, és alapértelmezés szerint NULL definíciót eredményezne.

A problémák megoldásához a CTAS-utasítás SELECT részében explicit módon be kell állítania a típuskonverziót és a nullitást. Ezeket a tulajdonságokat nem állíthatja be a "CREATE TABLE" (TÁBLA LÉTREHOZÁSA) területen. 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 parancsot.
• A NEM ULLability kényszerítéséhez használja az ISNULL-t, és ne a COALESCE-t. Lásd a következő megjegyzést.
• Az ISNULL a legkülső függvény.
• Az ISNULL második része egy állandó, 0.

Feljegyzé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 állandó vagy konstans, 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 számított kifejezés. Nem része a forrásadatoknak.

A 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 jelentkezik, amikor megpróbálja elvégezni a partíciókapcsolót. A tábladefiníciók nem egyeznek. A tábladefiníciók egyezéséhez módosítsa a CTAS-t úgy, hogy egy ISNULL függvényt adjon hozzá az oszlop nullképességi 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, hogy a partícióváltás lehetséges legyen.

A CTAS a Synapse SQL egyik legfontosabb utasítása. Győződjön meg róla, hogy alaposan megérti. 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.