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

Tip

Microsoft Fabric Data Warehouse egy nagyvállalati szintű relációs raktár egy Data Lake-alaprendszeren, jövőre kész architektúrával, beépített AI-vel és új funkciókkal. Ha még nem ismerkedik adattárházzal, kezdje a Fabric Data Warehouse. A meglévő dedikált SQL-készlet számítási feladatai frissíthetők Fabric az adatelemzés, a valós idejű elemzés és a jelentéskészítés új képességeinek eléréséhez.

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.

TÁBLA LÉTREHOZÁSA KIVÁLASZTÁSSAL

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.

VÁLASSZA KI... INTO és CTAS

A CTAS a SELECT...INTO utasítás testreszabhatóbb változata.

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

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 [dbo].[FactInternetSales_new] létrehozása során a ROUND_ROBIN alapértelmezett elosztási típusát és a FÜRTÖZÖTT OSZLOPCENTRIKUS INDEX alapértelmezett táblaszerkezetét használja.

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

Note

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 Clustered Columnstore Index-el, a fürtözött oszlopcentrikus táblák teljesítményének előnyeinek kihasználása érdekében. Ezt a táblát azért is szeretné elosztani ProductKey, mert azt várja, hogy ezen az oszlopon összekapcsolások történnek, és el szeretné kerülni az adatmozgatást az összekapcsolások során ProductKey. Végül szeretne particionálást is hozzáadni a OrderDateKey-ra, hogy a régi partíciók törlé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;

Kifejezetten adja meg az adattípust és a kimenet nullabilitását.

A kód áthelyezése során előfordulhat, hogy ilyen jellegű kódolási mintákkal találkozik:

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 ezt a kódot át kell költöztetnie a CTAS-be, és ez 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.

Képernyőkép a CTAS eredményeiről

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ípuskonverzió. 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í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 lett 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 NULL-képesség kényszerítéséhez használja az ISNULL-t, 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.

Note

Ahhoz, hogy a nullability helyesen legyen beállítva, elengedhetetlen az ISNULL használata a COALESCE helyett. 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óváltást. A táblázat definíciói 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 nullitási tulajdonságok fenntartása mérnöki legjobb gyakorlat. 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.

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

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2026-05-03