VYTVOŘIT TABULKU JAKO VYBRAT (CTAS)

Tip

Microsoft Fabric Data Warehouse je relační sklad v podnikovém měřítku na základu datového jezera s architekturou připravenou pro budoucnost, integrovanou AI a novými funkcemi. Pokud s datovými sklady začínáte, začněte pracovat s Fabric Data Warehouse. Stávající úlohy fondu dedikované SQL můžou upgradovat na Fabric a získat tak přístup k novým funkcím napříč datovou vědou, analýzou v reálném čase a reportováním.

Tento článek vysvětluje příkaz T-SQL CREATE TABLE AS SELECT (CTAS) ve vyhrazeném fondu SQL (dříve SQL DW) pro vývoj řešení. Článek také uvádí příklady kódu.

VYTVOŘIT TABULKU JAKO VYBERTE

Příkaz CREATE TABLE AS SELECT (CTAS) je jednou z nejdůležitějších dostupných funkcí T-SQL. CTAS je paralelní operace, která vytvoří novou tabulku na základě výstupu příkazu SELECT. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit a vložit data do tabulky jediným příkazem.

VYBERTE... INTO vs. CTAS

CTAS je více přizpůsobitelná verze SELECT...INTO statement.

Následuje příklad jednoduchého příkazu SELECT... INTO:

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

VYBERTE... INTO neumožňuje změnit metodu distribuce nebo typ indexu jako součást operace. Vytvoříte [dbo].[FactInternetSales_new] pomocí výchozího distribučního typu ROUND_ROBIN a výchozí struktury tabulky CLUSTERED COLUMNSTORE INDEX.

U CTAS můžete na druhou stranu určit rozdělení dat tabulky i typ struktury tabulky. Převod předchozího příkladu na CTAS:

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

Note

Pokud se pokoušíte změnit index pouze v operaci CTAS a zdrojová tabulka je distribuovaná hodnotou hash, zachovejte stejný distribuční sloupec a datový typ. Tím se vyhnete přesunu dat mezi distribucemi během operace, což je efektivnější.

Použijte CTAS ke zkopírování tabulky

Možná je jedním z nejběžnějších použití CTAS vytvoření kopie tabulky, aby bylo možné změnit DDL. Řekněme, že jste tabulku původně vytvořili jako ROUND_ROBINa teď ji chcete změnit na tabulku distribuovanou ve sloupci. CTAS je způsob, jakým byste změnili distribuční sloupec. CTAS můžete také použít ke změně typů oddílů, indexování nebo sloupců.

Řekněme, že jste vytvořili tuto tabulku zadáním HEAP a použitím výchozího distribučního ROUND_ROBINtypu .

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

Teď chcete vytvořit novou kopii této tabulky s tabulkou Clustered Columnstore Index, abyste mohli využít výkon clusterovaných tabulek Columnstore. Chcete také distribuovat tuto tabulku na ProductKey, protože očekáváte JOIN operace na tomto sloupci a chcete se vyhnout přesunu dat během těchto JOIN operací na ProductKey. Nakonec chcete také přidat dělení na OrderDateKeyoddíly, abyste mohli rychle odstranit stará data vyřazením starých oddílů. Tady je příkaz CTAS, který zkopíruje starou tabulku do nové tabulky.

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;

Nakonec můžete přejmenovat tabulky, vložit novou tabulku a poté odstranit starou tabulku.

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

DROP TABLE FactInternetSales_old;

Explicitně uveďte typ dat a neplatnost výstupu

Při migraci kódu můžete narazit na tento typ kódu:

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;

Možná si myslíte, že byste měli tento kód migrovat do CTAS, a máte pravdu. Nicméně je tu skrytý problém.

Následující kód nepřináší stejný výsledek:

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;

Všimněte si, že sloupec "result" přenáší další hodnoty datového typu a neplatnosti výrazu. Přenos datového typu vpřed může vést k drobným rozptylům hodnot, pokud nejste opatrní.

Vyzkoušejte tento příklad:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Hodnota uložená pro výsledek je jiná. Vzhledem k tomu, že trvalá hodnota ve výsledném sloupci se používá v jiných výrazech, bude chyba ještě důležitější.

Snímek obrazovky s výsledky CTAS

To je důležité pro migraci dat. I když je druhý dotaz pravděpodobně přesnější, je problém. Data by se v porovnání se zdrojovým systémem liší a to vede k otázkám integrity migrace. Tohle je jeden z těch vzácných případů, kdy je "špatná" odpověď skutečně ta správná!

Důvodem, proč vidíme rozdíl mezi těmito dvěma výsledky, je implicitní přetypování. V prvním příkladu tabulka definuje sloupec. Při vložení řádku dojde k implicitní převodu typu. V druhém příkladu neexistuje žádný implicitní převod typu, protože výraz definuje datový typ sloupce.

Všimněte si také, že sloupec v druhém příkladu byl definován jako sloupec NULLable, zatímco v prvním příkladu ne. Když byla tabulka vytvořena v prvním příkladu, byla explicitně definována možnost null sloupce. V druhém příkladu byl výraz ponechán a ve výchozím nastavení by výsledkem byla definice NULL.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typu a hodnotu null v části SELECT příkazu CTAS. Tyto vlastnosti nelze nastavit v příkazu CREATE TABLE. Následující příklad ukazuje, jak opravit kód:

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

Mějte na paměti následující:

Můžete použít funkci CAST nebo CONVERT.
K vynucení NULLability použijte funkci ISNULL, nikoli COALESCE. Podívejte se na následující poznámku.
ISNULL je nejvzdálenější funkce.
Druhá část isNULL je konstanta, 0.

Note

Aby byla správně nastavená hodnota null, je důležité použít funkci ISNULL a ne COALESCE. COALESCE není deterministická funkce, takže výsledek výrazu bude vždy umožňovat hodnotu NULL. ISNULL se liší. Je to deterministické. Proto, pokud druhá část funkce ISNULL je konstanta nebo literál, výsledná hodnota nebude NULL.

Zajištění integrity výpočtů je také důležité pro přepínání partitionů tabulky. Představte si, že máte tuto tabulku definovanou jako tabulku faktů:

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

Pole množství je však počítaný výraz. Není součástí zdrojových dat.

Pokud chcete vytvořit dělenou datovou sadu, můžete použít následující kód:

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

Dotaz by proběhl dokonale. K problému dochází při pokusu o přepnutí diskového oddílu. Definice tabulek nesedí. Aby se definice tabulky shodovaly, upravte CTAS tak, aby se přidala ISNULL funkce, aby se zachoval atribut nullability sloupce.

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

Vidíte, že konzistence typů a udržování vlastností nulovatelnosti při použití CTAS je osvědčená inženýrská praxe. Pomáhá udržovat integritu výpočtů a také zajišťuje, že přepínání oddílů je možné.

CTAS je jedním z nejdůležitějších tvrzení v Synapse SQL. Ujistěte se, že jste ho důkladně porozuměli. Prohlédněte si dokumentaci k CTAS.

Další tipy pro vývoj najdete v přehledu vývoje.

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-05-03