CREATE TABLE AS SELECT

Cikk
05/23/2023

A következőkre vonatkozik:Azure Synapse Analytics Analytics platformrendszer (PDW)

A CREATE TABLE AS SELECT (CTAS) az egyik legfontosabb elérhető T-SQL-funkció. Ez egy teljes mértékben párhuzamosított művelet, amely létrehoz egy új táblát egy SELECT utasítás eredményei alapján. A CTAS a legegyszerűbb és leggyorsabb módszer egy tábla másolatának létrehozására.

Használja például a CTAS-t a következő célokra:

Hozzon létre újra egy másik kivonatterjesztési oszlopot tartalmazó táblát.
Hozzon létre újra egy táblát replikáltként.
Hozzon létre egy oszlopcentrikus indexet a tábla néhány oszlopán.
Külső adatok lekérdezése vagy importálása.

Megjegyzés

Mivel a CTAS bővíti a tábla létrehozásának képességeit, ez a témakör nem próbálja megismételni a CREATE TABLE témakört. Ehelyett a CTAS és a CREATE TABLE utasítások közötti különbségeket írja le. A CREATE TABLE részleteiért lásd a CREATE TABLE (Azure Synapse Analytics) utasítást.

Ezt a szintaxist az Azure Synapse Analytics kiszolgáló nélküli SQL-készlete nem támogatja.
A CTAS a Microsoft Fabric raktárában támogatott.

Transact-SQL szintaxis konvenciók

Szintaxis

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  

<distribution_option> ::=
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )
      | DISTRIBUTION = ROUND_ROBIN 
      | DISTRIBUTION = REPLICATE
    }   

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column[,...n])
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
      | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

<query_hint> ::=
    {
        MAXDOP 
    }

Argumentumok

További részletekért lásd a CREATE TABLE argumentumok szakaszát .

Oszlopbeállítások

column_name [ ,...n ]
Az oszlopnevek nem engedélyezik a CREATE TABLE (CREATE TABLE) oszlopbeállításokat . Ehelyett megadhat egy vagy több oszlopnevet az új táblához. Az új tábla oszlopai a megadott neveket használják. Ha oszlopneveket ad meg, az oszloplistában szereplő oszlopok számának meg kell egyeznie a kiválasztási eredmények oszlopainak számával. Ha nem ad meg oszlopneveket, az új céltábla a select utasítás eredményeiben szereplő oszlopneveket használja.

Nem adhat meg más oszlopbeállításokat, például adattípusokat, rendezést vagy nullbilitást. Ezek az attribútumok az utasítás eredményeiből származnak SELECT . A SELECT utasítással azonban módosíthatja az attribútumokat. Példa: Oszlopattribútumok módosítása CTAS használatával.

Táblázatterjesztési beállítások

A részletekért és a legjobb terjesztési oszlop kiválasztásának módjáért tekintse meg a TÁBLÁZAT LÉTREHOZÁSA TÁBLA táblaterjesztési beállítások szakaszát. A tényleges használaton vagy mintalekérdezéseken alapuló táblák kiválasztására vonatkozó javaslatokért lásd: Terjesztési tanácsadó Azure Synapse SQL-ben.

DISTRIBUTION = HASH(distribution_column_name) | ROUND_ROBIN | REPLIKÁLÁS: A CTAS-utasításhoz terjesztési beállítás szükséges, és nem rendelkeznek alapértelmezett értékekkel. Ez eltér a CREATE TABLE függvénytől, amely alapértelmezés szerint rendelkezik.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) A sorokat legfeljebb nyolc oszlop kivonatértékei alapján osztja el, így egyenletesebb eloszlást tesz lehetővé az alaptábla-adatok között, csökkentve az adatok időbeli eltérését, és javítja a lekérdezési teljesítményt.

Megjegyzés

A funkció engedélyezéséhez módosítsa az adatbázis kompatibilitási szintjét 50-esre ezzel a paranccsal. Az adatbáziskompatibilitási szint beállításával kapcsolatos további információkért lásd: ALTER DATABASE SCOPED CONFIGURATION .. Például: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
A többoszlopos terjesztési (MCD) funkció letiltásához futtassa ezt a parancsot az adatbázis kompatibilitási szintjének automatikusra való módosításához. Például: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; A meglévő MCD-táblák megmaradnak, de olvashatatlanná válnak. Az MCD-táblákon keresztüli lekérdezések a következő hibát fogják visszaadni: Related table/view is not readable because it distributes data on multiple columns and multi-column distribution is not supported by this product version or this feature is disabled.
- Az MCD-táblákhoz való hozzáférés helyreállításához engedélyezze újra a funkciót.
- Ha adatokat szeretne betölteni egy MCD-táblába, használja a CTAS-utasítást, és az adatforrásnak Synapse SQL-tábláknak kell lennie.
- A CTAS az MCD HEAP-céltáblákon nem támogatott. Ehelyett kerülő megoldásként használja az INSERT SELECT parancsot az ADATOK MCD HEAP-táblákba való betöltéséhez.
Az SSMS használata az MCD-táblák létrehozására szolgáló szkript létrehozásához jelenleg az SSMS 19-es verzióján túl támogatott.

A részletekért és a legjobb terjesztési oszlop kiválasztásának módjáért tekintse meg a TÁBLÁZAT LÉTREHOZÁSA TÁBLA táblaterjesztési beállítások szakaszát.

Táblapartíció beállításai

A CTAS-utasítás alapértelmezés szerint létrehoz egy nem particionált táblát, még akkor is, ha a forrástábla particionált. Ha particionált táblát szeretne létrehozni a CTAS utasítással, meg kell adnia a partíciós beállítást.

További részletekért lásd a CREATE TABLE ( Táblapartíció beállításai ) című szakaszt.

SELECT utasítás

A SELECT utasítás a CTAS és a CREATE TABLE alapvető különbsége.

`WITH`common_table_expression

Egy ideiglenes elnevezett eredményhalmazt ad meg, amelyet közös táblakifejezésnek (CTE) nevezünk. További információ: WITH common_table_expression (Transact-SQL).

`SELECT`select_criteria

Feltölti az új táblát egy SELECT utasítás eredményeivel. select_criteria a SELECT utasítás törzse, amely meghatározza, hogy mely adatokat szeretné az új táblába másolni. A SELECT utasításokról további információt a SELECT (Transact-SQL) című témakörben talál.

Lekérdezési tipp

A felhasználók a MAXDOP értékét egész szám értékre állíthatják a párhuzamosság maximális fokának szabályozásához. Ha a MAXDOP értéke 1, a lekérdezést egyetlen szál hajtja végre.

Engedélyek

A CTAS engedélyre van szüksége SELECT a select_criteria hivatkozott objektumokhoz.

A tábla létrehozásához szükséges engedélyekért lásd: Engedélyek a CREATE TABLE alkalmazásban.

Megjegyzések

További részletekért lásd: Általános megjegyzések a CREATE TABLE-ben.

Korlátozások és megkötések

A rendezett fürtözött oszlopcentrikus indexek az Azure Synapse Analyticsben támogatott adattípusok oszlopaiban hozhatók létre, a sztringoszlopok kivételével.

A SET ROWCOUNT (Transact-SQL) nincs hatással a CTAS-ra. Hasonló viselkedés eléréséhez használja a TOP (Transact-SQL) parancsot.

Részletekért lásd: Korlátozások és korlátozások a CREATE TABLE-ben.

Zárolási viselkedés

Részletekért lásd: Zárolási viselkedés a CREATE TABLE-ben.

Teljesítmény

Kivonatelosztott táblák esetén a CTAS használatával másik terjesztési oszlopot választhat, így jobb teljesítményt érhet el az illesztések és az összesítések esetében. Ha nem egy másik terjesztési oszlopot választ, akkor a legjobb CTAS-teljesítménnyel fog rendelkezni, ha ugyanazt a terjesztési oszlopot adja meg, mivel ezzel elkerülheti a sorok újraelosztását.

Ha CTAS-t használ a tábla létrehozásához, és a teljesítmény nem számít, megadhatja ROUND_ROBIN , hogy ne kelljen terjesztési oszlopot választania.

A későbbi lekérdezésekben az adatáthelyezési műveletek elkerülése érdekében a megnövekedett tárterület költségén megadhatja REPLICATE a tábla teljes másolatának betöltését az egyes számítási csomópontokon.

Példák táblázat másolására

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

A következőkre vonatkozik: Azure Synapse Elemzési és elemzési platformrendszer (PDW)

Talán az egyik leggyakoribb felhasználási módja CTAS egy tábla másolatának létrehozása, hogy módosíthassa a DDL-t. Ha például eredetileg úgy hozta létre a táblát, hogy ROUND_ROBIN most egy oszlopon elosztott táblára szeretné módosítani, CTAS az a terjesztési oszlop módosítása. CTAS A particionálás, az indexelés vagy az oszloptípusok módosítására is használható.

Tegyük fel, hogy a táblát az alapértelmezett terjesztési típus ROUND_ROBINmegadásával HEAP és haszná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 ennek a táblának egy fürtözött oszlopcentrikus indexével, hogy kihasználhassa a fürtözött oszlopcentrikus táblák teljesítményét. Ezt a táblát ProductKey is el szeretné osztani, mivel illesztésekre készül ezen az oszlopon, és el szeretné kerülni az adatáthelyezést az illesztések során a(z) rendszeren ProductKey. Végül particionálást OrderDateKey is szeretne hozzáadni, hogy a régi partíciók elvetésével gyorsan törölhesse a régi adatokat. Íme 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;

Példák oszlopbeállításokra

B. Oszlopattribútumok módosítása a CTAS használatával

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Ez a példa a CTAS használatával módosítja a tábla több oszlopának adattípusát, nullképességét és rendezését DimCustomer2 .

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  

-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  

-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] INT NOT NULL, 
    [CustomerKeyChangeNullable] INT NULL, 
    [CustomerKeyChangeDataTypeNullable] DECIMAL(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] DECIMAL(10, 2) NOT NULL, 
    [GeographyKeyNoChange] INT NULL, 
    [GeographyKeyChangeNotNullable] INT NOT NULL, 
    [CustomerAlternateKeyNoChange] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] NVARCHAR(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

Utolsó lépésként az ÁTNEVEZÉS (Transact-SQL) használatával válthat a táblanevek között. Így a DimCustomer2 lesz az új tábla.

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

Példák táblaeloszlásra

C. Tábla terjesztési módszerének módosítása a CTAS használatával

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Ez az egyszerű példa bemutatja, hogyan módosíthatja egy tábla terjesztési módszerét. Ennek mechanikája érdekében a kivonatelosztott táblát ciklikus időszeletelésre módosítja, majd a ciklikus időszeleteléses táblát visszaállítja kivonatoltra. Az utolsó táblázat megegyezik az eredeti táblázatéval.

A legtöbb esetben nem kell ciklikus időszeleteléses táblára módosítania a kivonatolt táblát. Gyakrabban előfordulhat, hogy egy ciklikus időszeleteléses táblát kivonatolt táblára kell módosítania. Előfordulhat például, hogy kezdetben ciklikus időszeletelésként tölt be egy új táblát, majd később áthelyezi egy kivonatelosztott táblába a jobb illesztési teljesítmény érdekében.

Ebben a példában az AdventureWorksDW mintaadatbázist használjuk. A Azure Synapse Analytics-verzió betöltéséhez lásd: Rövid útmutató: Dedikált SQL-készlet (korábbi nevén SQL DW) létrehozása és lekérdezése a Azure Synapse Analyticsben a Azure Portal használatával.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Ezután váltson vissza kivonatolt táblára.

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D. Tábla replikált táblává konvertálása a CTAS használatával

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Ez a példa a ciklikus időszeleteléses vagy kivonatolt táblák replikált táblává alakítására vonatkozik. Ez az adott példa egy lépésre továbbviszi a terjesztési típus módosításának előző módszerét. Mivel DimSalesTerritory egy dimenzió és valószínűleg egy kisebb tábla, újra létrehozhatja a táblát replikáltként, hogy elkerülje az adatáthelyezési elemet, amikor más táblákhoz csatlakozik.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E. A CTAS használata kevesebb oszlopot tartalmazó tábla létrehozásához

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Az alábbi példa egy ciklikus időszeleteléses elosztott táblát hoz létre néven myTable (c, ln). Az új táblának csak két oszlopa van. A SELECT utasítás oszlopaliasait használja az oszlopok nevének megadásához.

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer;

Példák lekérdezési tippekre

F. Lekérdezési tipp használata CREATE TABLE AS SELECT (CTAS) használatával

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Ez a lekérdezés bemutatja a CTAS-utasítással való lekérdezésillesztés-tipp használatának alapszintaxisát. A lekérdezés elküldése után Azure Synapse Analytics alkalmazza a kivonat illesztési stratégiáját, amikor minden egyes disztribúcióhoz létrehozza a lekérdezéstervet. További információ a kivonat illesztési lekérdezési tippjéről: OPTION Záradék (Transact-SQL).

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );

Példák külső táblákra

G. Adatok importálása az Azure Blob Storage-ból a CTAS használatával

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Ha külső táblából szeretne adatokat importálni, a CREATE TABLE AS SELECT parancsot használva válasszon a külső táblából. A külső táblából az Azure Synapse Analyticsbe való adatkiválasztás szintaxisa megegyezik a normál táblából történő adatkiválasztás szintaxisával.

Az alábbi példa egy külső táblát határoz meg egy Azure Blob Storage-fiók adatain. Ezután a CREATE TABLE AS SELECT használatával választ a külső táblából. Ez importálja az adatokat Azure Blob Storage szöveghatárolt fájlokból, és az adatokat egy új Azure Synapse Analytics-táblába tárolja.

--Use your own processes to create the text-delimited files on Azure Blob Storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  

--Use CREATE TABLE AS SELECT to import the Azure Blob Storage data into a new   
--Synapse Analytics table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;

H. Hadoop-adatok importálása külső táblából a CTAS használatával

A következőkre vonatkozik: Elemzési platformrendszer (PDW)

Ha külső táblából szeretne adatokat importálni, egyszerűen használja a CREATE TABLE AS SELECT parancsot a külső tábla kiválasztásához. A külső táblákból az Analytics Platform Systembe (PDW) történő adatkiválasztás szintaxisa megegyezik a normál táblákból történő adatkiválasztás szintaxisával.

Az alábbi példa egy külső táblát határoz meg egy Hadoop-fürtön. Ezután a CREATE TABLE AS SELECT használatával választ a külső táblából. Ez importálja az adatokat a Hadoop szöveggel tagolt fájljaiból, és az adatokat egy új Analytics Platform System -táblába (PDW) tárolja.

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  

-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  

-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;

Példák a CTAS-sel SQL Server kód cseréjére

A CTAS használatával megkerülhet néhány nem támogatott funkciót. Amellett, hogy képes futtatni a kódot az adattárházban, a meglévő kód átírása a CTAS használatára általában javítja a teljesítményt. Ez a teljesen párhuzamos kialakítás eredménye.

Megjegyzés

Próbálja meg a "CTAS-t először" gondolni. Ha úgy gondolja, hogy a használatával CTAS meg tudja oldani a problémát, akkor általában ez a legjobb módszer a megközelítésre – még akkor is, ha több adatot ír.

I. A SELECT helyett használja a CTAS-t. INTO

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server kód általában SELECT.-t használ. INTO a SELECT utasítás eredményével rendelkező táblák feltöltéséhez. Ez egy példa a SELECT.SQL Server. INTO utasítás.

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

Ezt a szintaxist a Azure Synapse Analytics és a Párhuzamos Data Warehouse nem támogatja. Ez a példa bemutatja, hogyan írhatja át az előző SELECT.. INTO utasítás CTAS-utasításként. A CTAS szintaxisában leírt eloszlási beállítások bármelyikét kiválaszthatja. Ez a példa a ROUND_ROBIN terjesztési módszert használja.

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J. A CTAS használata az egyesítési utasítások egyszerűsítéséhez

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Az egyesítési utasítások legalább részben lecserélhetők a használatával CTAS. A és a INSERTUPDATE egyetlen utasítássá összesíthető. A törölt rekordokat egy második utasításban le kell zárni.

Példa a UPSERT következőkre:

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimProduct_upsert]  TO [DimProduct];

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

A következőkre vonatkozik: Azure Synapse Analytics and Analytics Platform System (PDW)

Amikor SQL Server kódot migrál Azure Synapse Analyticsbe, előfordulhat, hogy az alábbi típusú kódolási mintán fut:

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
;

Ösztönösen úgy gondolhatja, hogy át kell telepítenie ezt a kódot egy 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. Ez az értékek finom varianciáihoz vezethet, ha nem körültekintő.

Próbálja ki például az alábbiakat:

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.

Képernyőkép SQL Server Management Studio (SSMS) a CREATE TABLE AS SELECT eredményekrő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 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ása után 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. Amikor a tábla létrejött az első példaoszlopban, a nullability explicit módon meg lett határozva. A második példában a kifejezés maradt, és alapértelmezés szerint ez egy definíciót NULL 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 az SELECTCTAS utasításban. Ezeket a tulajdonságokat nem lehet beállítani a tábla létrehozása részben.

Ez a 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

Jegyezze fel a következőt a példában:

A CAST vagy a CONVERT használható lett volna.
Az ISNULL a NULLability és a COALESCE kényszerítésére szolgál.
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 a használata, nem COALESCEpedig a használataISNULL. COALESCE nem determinisztikus függvény, így a kifejezés eredménye mindig NULLable lesz. ISNULL más. Ez determinisztikus. Ezért ha a függvény második része ISNULL állandó vagy literál, akkor az eredményül kapott érték NEM NULL lesz.

Ez a tipp nem csak a számítások integritásának biztosításához hasznos. A táblapartíciók közötti váltáshoz is fontos. Tegyük fel, hogy ezt a táblát tényként definiálta:

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 értékmező azonban egy számított kifejezés, amely nem része a forrásadatoknak.

A particionált adatkészlet létrehozásához vegye figyelembe a következő példát:

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 végrehajtani a partíciókapcsolót. A tábladefiníciók nem egyeznek. A tábladefiníciók létrehozásához módosítani kell a CTAS-t.

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 tehát, hogy a típuskonzisztencia és a CTAS nullability tulajdonságainak fenntartása jó 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.

L. Rendezett fürtözött oszlopcentrikus index létrehozása a MAXDOP 1 használatával

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Következő lépések

A következőkre vonatkozik:Raktár a Microsoft Fabricben

Használja például a CTAS-t a Warehouse-ban a Microsoft Fabricben a következő célokra:

Hozzon létre egy másolatot egy táblázatról a forrástábla néhány oszlopával.
Hozzon létre egy táblát, amely egy olyan lekérdezés eredménye, amely más táblákhoz csatlakozik.

A CTAS a Microsoft Fabricben való használatával kapcsolatos további információkért lásd: Adatok betöltése a raktárba a TSQL használatával.

Megjegyzés

Mivel a CTAS bővíti a táblák létrehozásának képességeit, ez a témakör nem próbálja megismételni a CREATE TABLE témakört. Ehelyett a CTAS és a CREATE TABLE utasítások közötti különbségeket írja le. A CREATE TABLE részleteiért lásd a CREATE TABLE utasítást.

Transact-SQL szintaxis konvenciók

Szintaxis

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    AS <select_statement>  
[;]  

<select_statement> ::=  
    SELECT select_criteria

Argumentumok

További részletekért tekintse meg a Microsoft Fabric CREATE TABLE argumentumait.

Oszlopbeállítások

column_name [ ,...n ]
Az oszlopnevek nem engedélyezik a CREATE TABLE (TÁBLA LÉTREHOZÁSA) oszlopbeállításokat . Ehelyett megadhat egy vagy több oszlopnevet az új táblához. Az új tábla oszlopai a megadott neveket használják. Ha oszlopneveket ad meg, az oszloplistában szereplő oszlopok számának meg kell egyeznie a kiválasztási eredmények oszlopainak számával. Ha nem ad meg oszlopneveket, az új céltábla a select utasítás eredményeiben szereplő oszlopneveket használja.

Nem adhat meg más oszlopbeállításokat, például adattípusokat, rendezést vagy nullázhatóságot. Ezen attribútumok mindegyike a utasítás eredményeiből SELECT származik. A SELECT utasítással azonban módosíthatja az attribútumokat.

SELECT utasítás

A SELECT utasítás a CTAS és a CREATE TABLE közötti alapvető különbség.

`SELECT`select_criteria

Feltölti az új táblát a SELECT utasítás eredményeivel. select_criteria a SELECT utasítás törzse, amely meghatározza, hogy mely adatokat másolja az új táblába. A SELECT utasításokkal kapcsolatos információkért lásd: SELECT (Transact-SQL).

Engedélyek

A CTAS-nek engedélyre van szüksége SELECT a select_criteria hivatkozott objektumokhoz.

A táblák létrehozásához szükséges engedélyekért lásd: Engedélyek a CREATE TABLE-ben.

Megjegyzések

Részletekért lásd: Általános megjegyzések a CREATE TABLE-ben.

Korlátozások és megkötések

A SET ROWCOUNT (Transact-SQL) nincs hatással a CTAS-ra. Hasonló viselkedés eléréséhez használja a TOP (Transact-SQL) függvényt.

Részletekért lásd: Korlátozások és korlátozások a CREATE TABLE-ben.

Zárolási viselkedés

Részletekért lásd: Zárolási viselkedés a CREATE TABLE-ben.

Példák táblázat másolására

A CTAS a Microsoft Fabricben való használatával kapcsolatos további információkért lásd: Adatok betöltése a raktárba a TSQL használatával.

A. Oszlopattribútumok módosítása a CTAS használatával

Ez a példa a CTAS használatával módosítja a tábla több oszlopának adattípusát és nullképességét DimCustomer2 .

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] VARCHAR(15)NOT NULL  
)  

-- CTAS example to change data types and nullability of columns
CREATE TABLE test  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
FROM [dbo].[DimCustomer2]  

-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] INT NOT NULL, 
    [CustomerKeyChangeNullable] INT NULL, 
    [CustomerKeyChangeDataTypeNullable] DECIMAL(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] DECIMAL(10, 2) NOT NULL, 
    [GeographyKeyNoChange] INT NULL, 
    [GeographyKeyChangeNotNullable] INT NOT NULL, 
    [CustomerAlternateKeyNoChange] VARCHAR(15) NOT NULL, 
    [CustomerAlternateKeyNullable] VARCHAR(15) NULL, 
NOT NULL
)

B. A CTAS használata kevesebb oszlopot tartalmazó tábla létrehozásához

Az alábbi példa egy nevű myTable (c, ln)táblát hoz létre. Az új táblának csak két oszlopa van. A SELECT utasítás oszlopaliasait használja az oszlopok nevének megadásához.

CREATE TABLE myTable  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer;

C. A SELECT helyett használja a CTAS-t. INTO

SQL Server kód általában SELECT.-t használ. INTO a SELECT utasítás eredményével rendelkező táblák feltöltéséhez. Ez egy példa a SELECT.SQL Server. INTO utasítás.

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

Ez a példa bemutatja, hogyan írhatja át az előző SELECT.. INTO utasítás CTAS-utasításként.

CREATE TABLE NewFactTable
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

D. A CTAS használata az egyesítési utasítások egyszerűsítéséhez

Példa a UPSERT következőkre:

CREATE TABLE dbo.[DimProduct_upsert]
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

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

Ha SQL Server kódot a Warehouse-ba migrálja, előfordulhat, hogy az alábbi típusú kódolási mintán fut:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE result (result DECIMAL(7,2) NOT NULL)

INSERT INTO result
SELECT @d*@f
;

Ösztönösen úgy gondolhatja, hogy át kell telepítenie ezt a kódot egy 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
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. Ez az értékek finom varianciáihoz vezethet, ha nem körültekintő.

Próbálja ki például az alábbiakat:

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.

Képernyőkép SQL Server Management Studio (SSMS) a CREATE TABLE AS SELECT eredményekről.

Ez a 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
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

Jegyezze fel a következőt a példában:

A CAST vagy a CONVERT használható lett volna.
Az ISNULL a NULLability és a COALESCE kényszerítésére szolgál.
Az ISNULL a legkülső függvény.
Az ISNULL második része egy állandó, 0.

Megjegyzés

Ez a tipp nem csak a számítások integritásának biztosításához hasznos. A táblapartíciók közötti váltáshoz is fontos. Tegyük fel, hogy ezt a táblát tényként definiálta:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     DECIMAL(7,2)   NOT NULL
,   [amount]    DECIMAL(7,2)   NOT NULL
)
;

Az értékmező azonban egy számított kifejezés, amely nem része a forrásadatoknak.

Láthatja tehát, hogy a típuskonzisztencia és a CTAS nullability tulajdonságainak fenntartása jó mérnöki ajánlott eljárás. Segít fenntartani az integritást a számításokban.

Megosztás a következőn keresztül:

CREATE TABLE AS SELECT

Szintaxis

Argumentumok

Oszlopbeállítások

Táblázatterjesztési beállítások

Táblapartíció beállításai

SELECT utasítás

WITHcommon_table_expression

SELECTselect_criteria

Lekérdezési tipp

Engedélyek

Megjegyzések

Korlátozások és megkötések

Zárolási viselkedés

Teljesítmény

Példák táblázat másolására

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

Példák oszlopbeállításokra

B. Oszlopattribútumok módosítása a CTAS használatával

Példák táblaeloszlásra

C. Tábla terjesztési módszerének módosítása a CTAS használatával

D. Tábla replikált táblává konvertálása a CTAS használatával

E. A CTAS használata kevesebb oszlopot tartalmazó tábla létrehozásához

Példák lekérdezési tippekre

F. Lekérdezési tipp használata CREATE TABLE AS SELECT (CTAS) használatával

Példák külső táblákra

G. Adatok importálása az Azure Blob Storage-ból a CTAS használatával

H. Hadoop-adatok importálása külső táblából a CTAS használatával

Példák a CTAS-sel SQL Server kód cseréjére

I. A SELECT helyett használja a CTAS-t. INTO

J. A CTAS használata az egyesítési utasítások egyszerűsítéséhez

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

L. Rendezett fürtözött oszlopcentrikus index létrehozása a MAXDOP 1 használatával

Következő lépések

Szintaxis

Argumentumok

Oszlopbeállítások

SELECT utasítás

SELECTselect_criteria

Engedélyek

Megjegyzések

Korlátozások és megkötések

Zárolási viselkedés

Példák táblázat másolására

A. Oszlopattribútumok módosítása a CTAS használatával

B. A CTAS használata kevesebb oszlopot tartalmazó tábla létrehozásához

C. A SELECT helyett használja a CTAS-t. INTO

D. A CTAS használata az egyesítési utasítások egyszerűsítéséhez

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

Következő lépések

Visszajelzés

További források

`WITH`common_table_expression

`SELECT`select_criteria

`SELECT`select_criteria