CREATE TABLE AS SELECT

Článek
23. 05. 2023

Platí pro:Azure Synapse Analytics Analytics Platform System (PDW)

CREATE TABLE AS SELECT (CTAS) je jednou z nejdůležitějších dostupných funkcí T-SQL. Jde o plně paralelizovanou operaci, která vytvoří novou tabulku na základě výstupu příkazu SELECT. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit kopii tabulky.

Pomocí funkce CTAS můžete například:

Znovu vytvořte tabulku s jiným sloupcem distribuce hodnot hash.
Znovu vytvořte tabulku jako replikovanou.
Vytvořte index columnstore jenom u některých sloupců v tabulce.
Dotazování nebo import externích dat

Poznámka

Vzhledem k tomu, že funkce CTAS přidává možnosti vytváření tabulek, pokusí se toto téma neopakovat téma CREATE TABLE. Místo toho popisuje rozdíly mezi příkazy CTAS a CREATE TABLE. Podrobnosti o příkazu CREATE TABLE najdete v příkazu CREATE TABLE (Azure Synapse Analytics).

Bezserverový fond SQL ve službě Azure Synapse Analytics tuto syntaxi nepodporuje.
CTAS se podporuje ve skladu v Microsoft Fabric.

Konvence syntaxe jazyka Transact-SQL

Syntaxe

syntaxsql

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 
    }

Argumenty

Podrobnosti najdete v části Argumenty v tématu CREATE TABLE.

Možnosti sloupců

column_name [ ,...n ]
Názvy sloupců neumožňují možnosti sloupců uvedené v tématu CREATE TABLE. Místo toho můžete pro novou tabulku zadat volitelný seznam jednoho nebo více názvů sloupců. Sloupce v nové tabulce používají názvy, které zadáte. Když zadáte názvy sloupců, musí se počet sloupců v seznamu sloupců shodovat s počtem sloupců ve výsledcích výběru. Pokud nezadáte žádné názvy sloupců, použije nová cílová tabulka názvy sloupců ve výsledcích příkazu select.

Není možné zadat žádné další možnosti sloupců, jako jsou datové typy, kolace nebo hodnota null. Každý z těchto atributů je odvozen z výsledků SELECT příkazu . Ke změně atributů však můžete použít příkaz SELECT. Příklad najdete v tématu Změna atributů sloupce pomocí CTAS.

Možnosti distribuce tabulky

Podrobnosti a informace o tom, jak zvolit nejlepší sloupec distribuce, najdete v části Možnosti distribuce tabulky v tématu CREATE TABLE. Doporučení, kterou distribuci zvolit pro tabulku na základě skutečného využití nebo ukázkových dotazů, najdete v tématu Poradce pro distribuci v Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLIKACE Příkaz CTAS vyžaduje možnost distribuce a nemá výchozí hodnoty. To se liší od funkce CREATE TABLE, která má výchozí hodnoty.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuuje řádky na základě hodnot hash až osmi sloupců, což umožňuje rovnoměrnější distribuci dat základní tabulky, snižuje nerovnoměrnou distribuci dat v průběhu času a zlepšuje výkon dotazů.

Poznámka

Pokud chcete funkci povolit, změňte pomocí tohoto příkazu úroveň kompatibility databáze na 50. Další informace o nastavení úrovně kompatibility databáze najdete v tématu ALTER DATABASE SCOPED CONFIGURATION. Příklad: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
Pokud chcete zakázat funkci vícesloupcové distribuce (MCD), spusťte tento příkaz a změňte úroveň kompatibility databáze na HODNOTU AUTO. Příklad: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Existující tabulky MCD zůstanou, ale stanou se nečitelnými. Dotazy na tabulky MCD vrátí tuto chybu: 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.
- Pokud chcete znovu získat přístup k tabulkám MCD, znovu tuto funkci povolte.
- Pokud chcete načíst data do tabulky MCD, použijte příkaz CTAS a zdrojem dat musí být tabulky Synapse SQL.
- CTAS u cílových tabulek HALD MCD se nepodporuje. Místo toho jako alternativní řešení pro načtení dat do tabulek HALD MCD použijte INSERT SELECT .
Použití aplikace SSMS ke generování skriptu pro vytváření tabulek MCD je v současné době podporováno nad rámec aplikace SSMS verze 19.

Podrobnosti a informace o tom, jak zvolit nejlepší sloupec distribuce, najdete v části Možnosti distribuce tabulky v tématu CREATE TABLE.

Možnosti oddílů tabulky

Příkaz CTAS ve výchozím nastavení vytvoří nerozdělenou tabulku, a to i v případě, že je zdrojová tabulka rozdělená na oddíly. Pokud chcete vytvořit dělenou tabulku pomocí příkazu CTAS, musíte zadat možnost oddílu.

Podrobnosti najdete v části Možnosti oddílů tabulky v tématu CREATE TABLE.

Příkaz SELECT

Příkaz SELECT představuje základní rozdíl mezi CTAS a CREATE TABLE.

`WITH`common_table_expression

Určuje dočasnou pojmenovanou sadu výsledků dotazu, která se označuje jako výraz společné tabulky (CTE). Další informace najdete v tématu WITH common_table_expression (Transact-SQL).

`SELECT`select_criteria

Naplní novou tabulku výsledky z příkazu SELECT. select_criteria je tělo příkazu SELECT, který určuje, která data se mají zkopírovat do nové tabulky. Informace o příkazech SELECT najdete v tématu SELECT (Transact-SQL).

Nápověda k dotazu

Uživatelé můžou nastavením parametru MAXDOP na celočíselnou hodnotu řídit maximální stupeň paralelismu. Pokud je hodnota MAXDOP nastavená na hodnotu 1, dotaz se spustí v jednom vlákně.

Oprávnění

CTAS vyžaduje SELECT oprávnění ke všem objektům odkazovaným v select_criteria.

Oprávnění k vytvoření tabulky najdete v tématu Oprávnění v tématu CREATE TABLE.

Poznámky

Podrobnosti najdete v tématu Obecné poznámky v tématu CREATE TABLE.

Limity a omezení

Uspořádaný clusterovaný index columnstore je možné vytvořit u sloupců libovolných datových typů podporovaných v Azure Synapse Analytics s výjimkou sloupců řetězců.

SET ROWCOUNT (Transact-SQL) nemá žádný vliv na CTAS. Pokud chcete dosáhnout podobného chování, použijte TOP (Transact-SQL).

Podrobnosti najdete v tématu Omezení a omezení v tématu CREATE TABLE.

Chování při uzamykání

Podrobnosti najdete v tématu Chování zamykání v CREATE TABLE.

Výkon

Pro tabulku distribuovanou hodnotou hash můžete pomocí funkce CTAS zvolit jiný distribuční sloupec, abyste dosáhli lepšího výkonu pro spojení a agregace. Pokud volba jiného distribučního sloupce není vaším cílem, dosáhnete nejlepšího výkonu CTAS, pokud zadáte stejný distribuční sloupec, protože tím zabráníte redistribuci řádků.

Pokud k vytvoření tabulky používáte CTAS a výkon není faktorem, můžete zadat ROUND_ROBIN , abyste nemuseli rozhodovat o distribučním sloupci.

Pokud se chcete vyhnout přesunu dat v následných dotazech, můžete za cenu zvýšeného úložiště zadat REPLICATE načtení úplné kopie tabulky na každém výpočetním uzlu.

Příklady kopírování tabulky

A. Kopírování tabulky pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Možná jedním z nejběžnějších CTAS použití je vytvoření kopie tabulky, abyste mohli změnit DDL. Pokud jste například původně vytvořili tabulku jako ROUND_ROBIN a teď ji chcete změnit na tabulku distribuovanou ve sloupci, je postup, CTAS jak byste změnili distribuční sloupec. CTAS lze také použít ke změně dělení, indexování nebo typů sloupců.

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

SQL

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 clusterovaným indexem columnstore, abyste mohli využívat výkon clusterovaných tabulek columnstore. Tuto tabulku ProductKey také chcete distribuovat, protože očekáváte spojení v tomto sloupci, a chcete se vyhnout přesunu dat během spojení na ProductKey. Nakonec chcete také přidat dělení na OrderDateKey oddíly, abyste mohli rychle odstranit stará data vyřazením starých oddílů. Tady je příkaz CTAS, který by zkopíroval starou tabulku do nové tabulky:

SQL

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 tabulky přejmenovat, aby se v nové tabulce prohodily, a pak starou tabulku vypustit.

SQL

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

DROP TABLE FactInternetSales_old;

Příklady možností sloupců

B. Změna atributů sloupce pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento příklad používá CTAS ke změně datových typů, nullability a kolace pro několik sloupců v tabulce DimCustomer2 .

SQL

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

V posledním kroku můžete k přepnutí názvů tabulek použít příkaz RENAME (Transact-SQL ). Díky tomu se dimCustomer2 stává novou tabulkou.

SQL

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

DROP TABLE DimCustomer2_old;

Příklady distribuce tabulek

C. Změna metody distribuce tabulky pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento jednoduchý příklad ukazuje, jak změnit metodu distribuce tabulky. Aby se ukázalo, jak to udělat, změní tabulku distribuovanou hodnotou hash na kruhové dotazování a pak změní tabulku kruhového dotazování zpět na distribuovanou hodnotu hash. Konečná tabulka odpovídá původní tabulce.

Ve většině případů nemusíte měnit tabulku distribuovanou hodnotou hash na tabulku kruhového dotazování. Častěji může být potřeba změnit tabulku kruhového dotazování na distribuovanou tabulku hodnot hash. Můžete například nejprve načíst novou tabulku jako kruhové dotazování a později ji přesunout do distribuované tabulky hash, abyste získali lepší výkon spojení.

V tomto příkladě se používá ukázková databáze AdventureWorksDW. Informace o načtení verze Azure Synapse Analytics najdete v tématu Rychlý start: Vytvoření a dotazování vyhrazeného fondu SQL (dříve SQL DW) v Azure Synapse Analytics pomocí Azure Portal.

SQL

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

Potom ho změňte zpět na distribuovanou tabulku hash.

SQL

-- 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. Použití CTAS k převodu tabulky na replikovanou tabulku

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento příklad platí pro převod distribuovaných tabulek kruhového dotazování nebo hodnoty hash na replikovanou tabulku. Tento konkrétní příklad posouvá předchozí metodu změny typu distribuce o krok dále. Vzhledem k tomu DimSalesTerritory , že se jedná o dimenzi a pravděpodobně menší tabulku, můžete zvolit opětovné vytvoření replikované tabulky, abyste se vyhnuli přesunu dat při připojování k jiným tabulkám.

SQL

-- 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. Použití CTAS k vytvoření tabulky s menším počtem sloupců

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Následující příklad vytvoří distribuovanou tabulku kruhového dotazování s názvem myTable (c, ln). Nová tabulka má jenom dva sloupce. Používá aliasy sloupců v příkazu SELECT pro názvy sloupců.

SQL

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

Příklady nápovědy k dotazům

F. Použití nápovědy k dotazu s funkcí CREATE TABLE AS SELECT (CTAS)

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento dotaz ukazuje základní syntaxi pro použití nápovědy ke spojení dotazů s příkazem CTAS. Po odeslání dotazu použije Azure Synapse Analytics strategii hash join při generování plánu dotazu pro každou jednotlivou distribuci. Další informace o nápovědě dotazu hash join najdete v tématu Option – klauzule (Transact-SQL).

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říklady externích tabulek

G. Použití CTAS k importu dat ze služby Azure Blob Storage

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Pokud chcete importovat data z externí tabulky, použijte příkaz CREATE TABLE AS SELECT k výběru z externí tabulky. Syntaxe výběru dat z externí tabulky do Azure Synapse Analytics je stejná jako syntaxe pro výběr dat z běžné tabulky.

Následující příklad definuje externí tabulku dat v účtu Azure Blob Storage. Pak pomocí příkazu CREATE TABLE AS SELECT vybere z externí tabulky. Importuje data z Azure Blob Storage textem oddělených souborů a uloží je do nové tabulky Azure Synapse Analytics.

SQL

--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. Import dat Hadoopu z externí tabulky pomocí CTAS

Platí pro: PDW (Analytics Platform System)

Pokud chcete importovat data z externí tabulky, jednoduše pomocí příkazu CREATE TABLE AS SELECT vyberte z externí tabulky. Syntaxe výběru dat z externí tabulky do pdw (Analytics Platform System) je stejná jako syntaxe pro výběr dat z běžné tabulky.

Následující příklad definuje externí tabulku v clusteru Hadoop. Pak pomocí příkazu CREATE TABLE AS SELECT vybere z externí tabulky. Tato možnost importuje data ze souborů Hadoop oddělených textem a uloží je do nové tabulky PDW (Analytics Platform System).

SQL

-- 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říklady použití CTAS k nahrazení kódu SQL Server

Pomocí CTAS můžete obejít některé nepodporované funkce. Kromě toho, že je možné spustit kód v datovém skladu, může přepsání existujícího kódu na použití CTAS obvykle zlepšit výkon. Je to výsledek plně paralelizovaného návrhu.

Poznámka

Zkuste myslet na "CTAS first". Pokud si myslíte, že můžete problém vyřešit pomocí CTAS , pak je to obecně nejlepší způsob, jak k němu přistupovat – i když v důsledku toho píšete více dat.

I. Místo příkazu SELECT použijte CTAS. DO

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server kód obvykle používá příkaz SELECT.. INTO k naplnění tabulky výsledky příkazu SELECT. Toto je příklad SQL Server SELECT. Příkaz INTO.

SQL

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

Tato syntaxe není podporována v Azure Synapse Analytics a Paralelní Data Warehouse. Tento příklad ukazuje, jak přepsat předchozí příkaz SELECT. Příkaz INTO jako příkaz CTAS. Můžete zvolit kteroukoli z možností DISTRIBUCE popsaných v syntaxi CTAS. V tomto příkladu se používá metoda distribuce ROUND_ROBIN.

SQL

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

J. Zjednodušení příkazů sloučení pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Příkazy sloučení lze nahradit, alespoň částečně, pomocí příkazu CTAS. Můžete konsolidovat INSERT a do UPDATE jednoho příkazu. Všechny odstraněné záznamy by bylo potřeba uzavřít v druhém příkazu.

Příklad UPSERT :

SQL

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. Explicitní stavový datový typ a dostupnost výstupu s hodnotou null

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Při migraci kódu SQL Server do Azure Synapse Analytics můžete zjistit, že se setkáte s tímto typem kódu:

SQL

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
;

Instinktivně si můžete myslet, že byste tento kód měli migrovat do CTAS a měli byste pravdu. Je tu ale skrytý problém.

Následující kód NEPŘINÁŠÍ stejný výsledek:

SQL

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áší hodnoty datového typu a hodnoty null výrazu. Pokud nejste opatrní, může to vést k drobným odchylkám v hodnotách.

Vyzkoušejte následující příklad:

SQL

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Hodnota uložená pro výsledek se liší. S tím, jak se trvalá hodnota ve sloupci výsledku používá v jiných výrazech, je chyba ještě významnější.

Snímek obrazovky z SQL Server Management Studio (SSMS) s výsledky CREATE TABLE AS SELECT

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

Důvodem, proč vidíme tento rozdíl mezi dvěma výsledky, je implicitní přetypování typu. V prvním příkladu tabulka definuje definici sloupce. Po vložení řádku dojde k implicitní převodu typu. Ve 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 je definovaný jako sloupec NULLable, zatímco v prvním příkladu ne. Při vytvoření tabulky v prvním příkladu sloupce byla explicitně definována hodnota nullability. Ve druhém příkladu byla ponechána na výrazu a ve výchozím nastavení by výsledkem byla NULL definice.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typu a hodnotu null v SELECT části CTAS příkazu. Tyto vlastnosti nemůžete nastavit v části pro vytvoření tabulky.

Tento příklad ukazuje, jak opravit kód:

SQL

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

V příkladu si všimněte následujícího:

Bylo možné použít funkci CAST nebo CONVERT.
Funkce ISNULL se používá k vynucení, že funkce NULLability není COALESCE.
FUNKCE ISNULL je vnější funkce.
Druhá část knihovny ISNULL je konstanta , 0.

Poznámka

Aby byla hodnota null nastavena správně, je důležité použít , ISNULL a ne COALESCE. COALESCE není deterministická funkce, takže výsledek výrazu bude vždy NULLable. ISNULL se liší. Je deterministický. Pokud je tedy druhá část ISNULL funkce konstantou nebo literálem, výsledná hodnota bude NOT NULL.

Tento tip není užitečný jenom k zajištění integrity výpočtů. Je to také důležité pro přepínání oddílů tabulky. Představte si, že máte tuto tabulku definovanou jako fakt:

SQL

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 hodnoty je ale počítaný výraz, který není součástí zdrojových dat.

Pokud chcete vytvořit dělenou datovou sadu, podívejte se na následující příklad:

SQL

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 běžel naprosto v pořádku. K problému dochází při pokusu o přepnutí oddílů. Definice tabulek se neshodují. Aby bylo možné vytvořit definice tabulky, je potřeba upravit shodu s CTAS.

SQL

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 proto, že dobrým technickým osvědčeným postupem je konzistence typů a udržování vlastností nullability u CTAS. Pomáhá udržovat integritu ve výpočtech a také zajišťuje, že je možné přepínat oddíly.

L. Vytvoření uspořádaného clusterovaného indexu columnstore s využitím MAXDOP 1

SQL

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

Další kroky

Platí pro:Sklad v Microsoft Fabric

Například pomocí CTAS ve skladu v Microsoft Fabric můžete:

Vytvořte kopii tabulky s některými sloupci zdrojové tabulky.
Vytvořte tabulku, která je výsledkem dotazu, který spojuje jiné tabulky.

Další informace o používání CTAS ve skladu v Microsoft Fabric najdete v tématu Ingestování dat do skladu pomocí TSQL.

Poznámka

Konvence syntaxe jazyka Transact-SQL

Syntaxe

syntaxsql

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

<select_statement> ::=  
    SELECT select_criteria

Argumenty

Podrobnosti najdete v tématu Argumenty v tématu CREATE TABLE pro Microsoft Fabric.

Možnosti sloupců

Příkaz SELECT

Příkaz SELECT představuje základní rozdíl mezi CTAS a CREATE TABLE.

`SELECT`select_criteria

Oprávnění

CTAS vyžaduje SELECT oprávnění ke všem objektům odkazovaným v select_criteria.

Oprávnění k vytvoření tabulky najdete v tématu Oprávnění v tématu CREATE TABLE.

Poznámky

Podrobnosti najdete v tématu Obecné poznámky v tématu CREATE TABLE.

Limity a omezení

SET ROWCOUNT (Transact-SQL) nemá žádný vliv na CTAS. Pokud chcete dosáhnout podobného chování, použijte TOP (Transact-SQL).

Podrobnosti najdete v tématu Omezení a omezení v tématu CREATE TABLE.

Chování při uzamykání

Podrobnosti najdete v tématu Chování zamykání v CREATE TABLE.

Příklady kopírování tabulky

Další informace o používání CTAS ve skladu v Microsoft Fabric najdete v tématu Ingestování dat do skladu pomocí TSQL.

A. Změna atributů sloupce pomocí CTAS

Tento příklad používá CTAS ke změně datových typů a nullability pro několik sloupců v tabulce DimCustomer2 .

SQL

-- 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. Použití CTAS k vytvoření tabulky s menším počtem sloupců

Následující příklad vytvoří tabulku s názvem myTable (c, ln). Nová tabulka má jenom dva sloupce. Používá aliasy sloupců v příkazu SELECT pro názvy sloupců.

SQL

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

C. Místo příkazu SELECT použijte CTAS. DO

SQL Server kód obvykle používá příkaz SELECT.. INTO k naplnění tabulky výsledky příkazu SELECT. Toto je příklad SQL Server SELECT. Příkaz INTO.

SQL

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

Tento příklad ukazuje, jak přepsat předchozí příkaz SELECT. Příkaz INTO jako příkaz CTAS.

SQL

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

D. Zjednodušení příkazů sloučení pomocí CTAS

Příklad UPSERT :

SQL

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. Explicitně stavový datový typ a nullability výstupu

Při migraci kódu SQL Server do služby Warehouse můžete zjistit, že používáte tento typ modelu kódování:

SQL

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
;

Instinktivně si můžete myslet, že byste tento kód měli migrovat do CTAS a měli byste pravdu. Tady je ale skrytý problém.

Následující kód NEPŘINÁŠÍ stejný výsledek:

SQL

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

CREATE TABLE ctas_r
AS
SELECT @d*@f as result
;

Všimněte si, že sloupec "result" přenáší datové typy a hodnoty nullability výrazu. To může vést k drobným odchylkám v hodnotách, pokud nejste opatrní.

Jako příklad použijte následující:

SQL

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Hodnota uložená pro výsledek se liší. Vzhledem k tomu, že se v jiných výrazech použije trvalá hodnota ve sloupci výsledků, bude chyba ještě významnější.

Snímek obrazovky z SQL Server Management Studio (SSMS) s výsledky CREATE TABLE AS SELECT

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

Důvodem, proč vidíme tento rozdíl mezi dvěma výsledky, je implicitní přetypování typu. V prvním příkladu tabulka definuje definici sloupce. Po vložení řádku dojde k implicitní převodu typu. Ve 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 je definovaný jako sloupec NULLable, zatímco v prvním příkladu ne. Při vytvoření tabulky v prvním příkladu sloupce byla explicitně definována hodnota null. Ve druhém příkladu byla ponechána na výrazu a ve výchozím nastavení by výsledkem byla NULL definice.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typů a nullability v SELECT části CTAS příkazu. Tyto vlastnosti nemůžete nastavit v části vytvořit tabulku.

Tento příklad ukazuje, jak opravit kód:

SQL

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

V příkladu si všimněte následujícího:

Bylo možné použít přetypování nebo převod.
FUNKCE ISNULL se používá k vynucení možnosti NULLability, nikoli COALESCE.
ISNULL je nejkrajnější funkce.
Druhá část hodnoty ISNULL je konstanta 0.

Poznámka

Aby byla hodnota null správně nastavená, je důležité použít ISNULL a ne COALESCE. COALESCE není deterministická funkce, takže výsledek výrazu bude vždy NULLable. ISNULL je jiný. Je to deterministické. Pokud je tedy druhá část ISNULL funkce konstantou nebo literálem, výsledná hodnota nebude NULL.

Tento tip není užitečný jen pro zajištění integrity výpočtů. Je také důležité pro přepínání oddílů tabulky. Představte si, že máte tuto tabulku definovanou jako fakt:

SQL

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

Pole hodnoty je ale počítaný výraz, který není součástí zdrojových dat.

Vidíte proto, že dobrým technickým osvědčeným postupem je konzistence typů a udržování vlastností nullability na CTAS. Pomáhá udržovat integritu ve výpočtech.

Sdílet prostřednictvím

CREATE TABLE AS SELECT

Syntaxe

Argumenty

Možnosti sloupců

Možnosti distribuce tabulky

Možnosti oddílů tabulky

Příkaz SELECT

WITHcommon_table_expression

SELECTselect_criteria

Nápověda k dotazu

Oprávnění

Poznámky

Limity a omezení

Chování při uzamykání

Výkon

Příklady kopírování tabulky

A. Kopírování tabulky pomocí CTAS

Příklady možností sloupců

B. Změna atributů sloupce pomocí CTAS

Příklady distribuce tabulek

C. Změna metody distribuce tabulky pomocí CTAS

D. Použití CTAS k převodu tabulky na replikovanou tabulku

E. Použití CTAS k vytvoření tabulky s menším počtem sloupců

Příklady nápovědy k dotazům

F. Použití nápovědy k dotazu s funkcí CREATE TABLE AS SELECT (CTAS)

Příklady externích tabulek

G. Použití CTAS k importu dat ze služby Azure Blob Storage

H. Import dat Hadoopu z externí tabulky pomocí CTAS

Příklady použití CTAS k nahrazení kódu SQL Server

I. Místo příkazu SELECT použijte CTAS. DO

J. Zjednodušení příkazů sloučení pomocí CTAS

K. Explicitní stavový datový typ a dostupnost výstupu s hodnotou null

L. Vytvoření uspořádaného clusterovaného indexu columnstore s využitím MAXDOP 1

Další kroky

Syntaxe

Argumenty

Možnosti sloupců

Příkaz SELECT

SELECTselect_criteria

Oprávnění

Poznámky

Limity a omezení

Chování při uzamykání

Příklady kopírování tabulky

A. Změna atributů sloupce pomocí CTAS

B. Použití CTAS k vytvoření tabulky s menším počtem sloupců

C. Místo příkazu SELECT použijte CTAS. DO

D. Zjednodušení příkazů sloučení pomocí CTAS

E. Explicitně stavový datový typ a nullability výstupu

Další kroky

Váš názor

Další materiály

`WITH`common_table_expression

`SELECT`select_criteria

`SELECT`select_criteria