CREATE TABLE AS SELECT

  • Artikel

Van toepassing op:Azure Synapse AnalyticsAnalytics Platform System (PDW)

CREATE TABLE AS SELECT (CTAS) is een van de belangrijkste T-SQL-functies die beschikbaar zijn. Dit is een volledig geparallelliseerde bewerking waarmee u een nieuwe tabel maakt op basis van de uitvoer van een SELECT-instructie. CTAS is de eenvoudigste en snelste manier om een kopie van een tabel te maken.

Gebruik CTAS bijvoorbeeld om het volgende te doen:

  • Maak een tabel opnieuw met een andere hash-distributiekolom.
  • Maak een tabel opnieuw als gerepliceerd.
  • Maak een columnstore-index voor slechts enkele kolommen in de tabel.
  • Externe gegevens opvragen of importeren.

Notitie

Omdat CTAS de mogelijkheden voor het maken van een tabel toevoegt, wordt in dit onderwerp niet geprobeerd het onderwerp CREATE TABLE te herhalen. In plaats daarvan worden de verschillen tussen de CTAS- en CREATE TABLE-instructies beschreven. Zie de instructie CREATE TABLE (Azure Synapse Analytics) voor de details van CREATE TABLE.

  • Deze syntaxis wordt niet ondersteund door een serverloze SQL-pool in Azure Synapse Analytics.
  • CTAS wordt ondersteund in het magazijn in Microsoft Fabric.

Transact-SQL-syntaxisconventies

Syntaxis

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 
    }

Argumenten

Zie de sectie Argumenten in CREATE TABLE voor meer informatie.

Kolomopties

column_name [ ,...n ]
Kolomnamen staan de kolomopties die worden vermeld in CREATE TABLE niet toe. In plaats daarvan kunt u een optionele lijst met een of meer kolomnamen opgeven voor de nieuwe tabel. De kolommen in de nieuwe tabel gebruiken de namen die u opgeeft. Wanneer u kolomnamen opgeeft, moet het aantal kolommen in de kolomlijst overeenkomen met het aantal kolommen in de selectieresultaten. Als u geen kolomnamen opgeeft, gebruikt de nieuwe doeltabel de kolomnamen in de resultaten van de select-instructie.

U kunt geen andere kolomopties opgeven, zoals gegevenstypen, sortering of null-mogelijkheden. Elk van deze kenmerken is afgeleid van de resultaten van de SELECT instructie. U kunt echter de SELECT-instructie gebruiken om de kenmerken te wijzigen. Zie CTAS gebruiken om kolomkenmerken te wijzigen voor een voorbeeld.

Opties voor tabeldistributie

Zie de sectie Tabeldistributieopties in CREATE TABLE voor meer informatie en om te begrijpen hoe u de beste distributiekolom kiest. Zie Distribution Advisor in Azure Synapse SQL voor aanbevelingen over de distributie die u moet kiezen voor een tabel op basis van het werkelijke gebruik of voorbeeldquery's.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLICEREN De CTAS-instructie vereist een distributieoptie en heeft geen standaardwaarden. Dit verschilt van CREATE TABLE, dat standaardinstellingen heeft.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Hiermee worden de rijen verdeeld op basis van de hash-waarden van maximaal acht kolommen, waardoor de basistabelgegevens gelijkmatiger kunnen worden verdeeld, waardoor de gegevensscheefheid in de loop van de tijd wordt verminderd en de queryprestaties worden verbeterd.

Notitie

  • Als u de functie wilt inschakelen, wijzigt u het compatibiliteitsniveau van de database in 50 met deze opdracht. Zie ALTER DATABASE SCOPED CONFIGURATION (ALTER DATABASE SCOPED CONFIGURATION) voor meer informatie over het instellen van het databasecompatibiliteitsniveau. Bijvoorbeeld: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Als u de functie voor distributie met meerdere kolommen (MCD) wilt uitschakelen, voert u deze opdracht uit om het compatibiliteitsniveau van de database te wijzigen in AUTO. Bijvoorbeeld: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; bestaande MCD-tabellen blijven behouden, maar worden onleesbaar. Query's over MCD-tabellen retourneren deze fout: 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.
    • Als u weer toegang wilt krijgen tot MCD-tabellen, schakelt u de functie opnieuw in.
    • Als u gegevens in een MCD-tabel wilt laden, gebruikt u de CTAS-instructie. De gegevensbron moet Synapse SQL-tabellen zijn.
    • CTAS op MCD HEAP-doeltabellen wordt niet ondersteund. Gebruik in plaats daarvan INSERT SELECT als tijdelijke oplossing om gegevens in MCD HEAP-tabellen te laden.
  • Het gebruik van SSMS voor het genereren van een script voor het maken van MCD-tabellen wordt momenteel ondersteund buiten SSMS versie 19.

Zie de sectie Tabeldistributieopties in CREATE TABLE voor meer informatie en om te begrijpen hoe u de beste distributiekolom kiest.

Opties voor tabelpartitie

Met de CTAS-instructie wordt standaard een niet-gepartitioneerde tabel gemaakt, zelfs als de brontabel is gepartitioneerd. Als u een gepartitioneerde tabel wilt maken met de CTAS-instructie, moet u de partitieoptie opgeven.

Zie de sectie Tabelpartitieopties in CREATE TABLE voor meer informatie.

SELECT-instructie

De SELECT-instructie is het fundamentele verschil tussen CTAS en CREATE TABLE.

WITHcommon_table_expression

Hiermee geeft u een tijdelijke benoemde resultatenset op, ook wel een algemene tabelexpressie (Common Table Expression of CTE) genoemd. Zie WITH common_table_expression (Transact-SQL) voor meer informatie.

SELECTselect_criteria

Hiermee wordt de nieuwe tabel gevuld met de resultaten van een SELECT-instructie. select_criteria is de hoofdtekst van de SELECT-instructie die bepaalt welke gegevens naar de nieuwe tabel moeten worden gekopieerd. Zie SELECT (Transact-SQL) voor informatie over SELECT-instructies.

Queryhint

Gebruikers kunnen MAXDOP instellen op een geheel getal om de maximale mate van parallelle uitvoering te bepalen. Wanneer MAXDOP is ingesteld op 1, wordt de query uitgevoerd door één thread.

Machtigingen

CTAS vereist SELECT machtigingen voor alle objecten waarnaar wordt verwezen in de select_criteria.

Zie Machtigingen in CREATE TABLE voor machtigingen voor het maken van een tabel.

Opmerkingen

Zie Algemene opmerkingen in CREATE TABLE voor meer informatie.

Beperkingen en limieten

Een geordende geclusterde columnstore-index kan worden gemaakt voor kolommen van alle gegevenstypen die worden ondersteund in Azure Synapse Analytics, met uitzondering van tekenreekskolommen.

SET ROWCOUNT (Transact-SQL) heeft geen invloed op CTAS. Gebruik TOP (Transact-SQL) om een vergelijkbaar gedrag te bereiken.

Zie Beperkingen en beperkingen in CREATE TABLE voor meer informatie.

Vergrendelingsgedrag

Zie Vergrendelingsgedrag in CREATE TABLE voor meer informatie.

Prestaties

Voor een met hash gedistribueerde tabel kunt u CTAS gebruiken om een andere distributiekolom te kiezen om betere prestaties voor joins en aggregaties te bereiken. Als het kiezen van een andere distributiekolom niet uw doel is, beschikt u over de beste CTAS-prestaties als u dezelfde distributiekolom opgeeft, omdat hierdoor wordt voorkomen dat de rijen opnieuw worden verdeeld.

Als u CTAS gebruikt om een tabel te maken en de prestaties geen factor zijn, kunt u opgeven ROUND_ROBIN om te voorkomen dat u een distributiekolom moet kiezen.

Om gegevensverplaatsing in volgende query's te voorkomen, kunt u opgeven REPLICATE ten koste van meer opslag voor het laden van een volledige kopie van de tabel op elk rekenknooppunt.

Voorbeelden voor het kopiëren van een tabel

A. CTAS gebruiken om een tabel te kopiëren

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Misschien is een van de meest voorkomende toepassingen van het maken van CTAS een kopie van een tabel, zodat u de DDL kunt wijzigen. Als u bijvoorbeeld de tabel oorspronkelijk hebt gemaakt als ROUND_ROBIN en deze nu wilt wijzigen in een tabel die is gedistribueerd op een kolom, CTAS wijzigt u de distributiekolom. CTAS kan ook worden gebruikt om partitionering, indexering of kolomtypen te wijzigen.

Stel dat u deze tabel hebt gemaakt door het standaarddistributietype op ROUND_ROBINte HEAP geven en te gebruiken.

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

U wilt nu een nieuwe kopie van deze tabel maken met een geclusterde columnstore-index, zodat u kunt profiteren van de prestaties van geclusterde columnstore-tabellen. U wilt deze tabel ook distribueren op ProductKey omdat u op joins in deze kolom anticipeert en gegevensverplaatsing wilt voorkomen tijdens joins op ProductKey. Ten slotte wilt u ook partitionering toevoegen aan OrderDateKey , zodat u snel oude gegevens kunt verwijderen door oude partities te verwijderen. Dit is de CTAS-instructie waarmee de oude tabel naar een nieuwe tabel wordt gekopieerd:

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;

Ten slotte kunt u de naam van uw tabellen wijzigen om te wisselen in de nieuwe tabel en vervolgens de oude tabel verwijderen.

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

DROP TABLE FactInternetSales_old;

Voorbeelden voor kolomopties

B. CTAS gebruiken om kolomkenmerken te wijzigen

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

In dit voorbeeld wordt CTAS gebruikt om gegevenstypen, null-mogelijkheden en sortering voor verschillende kolommen in de DimCustomer2 tabel te wijzigen.

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

Als laatste stap kunt u RENAME (Transact-SQL) gebruiken om de tabelnamen te wijzigen. Hierdoor is DimCustomer2 de nieuwe tabel.

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

DROP TABLE DimCustomer2_old;

Voorbeelden voor tabeldistributie

C. CTAS gebruiken om de distributiemethode voor een tabel te wijzigen

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

In dit eenvoudige voorbeeld ziet u hoe u de distributiemethode voor een tabel wijzigt. Om de mechanismen te laten zien hoe u dit doet, wordt een met hash gedistribueerde tabel gewijzigd in round robin en wordt de round robin-tabel vervolgens weer gewijzigd in hash gedistribueerd. De uiteindelijke tabel komt overeen met de oorspronkelijke tabel.

In de meeste gevallen hoeft u een met hash gedistribueerde tabel niet te wijzigen in een round robin-tabel. Vaker moet u mogelijk een round robin-tabel wijzigen in een met hash gedistribueerde tabel. U kunt bijvoorbeeld in eerste instantie een nieuwe tabel laden als round robin en deze later verplaatsen naar een met hash gedistribueerde tabel voor betere joinprestaties.

In dit voorbeeld wordt de voorbeelddatabase AdventureWorksDW gebruikt. Als u de Azure Synapse Analytics-versie wilt laden, raadpleegt u Quickstart: een toegewezen SQL-pool (voorheen SQL DW) maken en er query's op uitvoeren in Azure Synapse Analytics met behulp van de Azure Portal.

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

Wijzig deze vervolgens weer in een met hash gedistribueerde tabel.

-- 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. CTAS gebruiken om een tabel te converteren naar een gerepliceerde tabel

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Dit voorbeeld is van toepassing op het converteren van round robin- of hash-gedistribueerde tabellen naar een gerepliceerde tabel. In dit specifieke voorbeeld gaat de vorige methode voor het wijzigen van het distributietype nog een stap verder. Aangezien DimSalesTerritory een dimensie en waarschijnlijk een kleinere tabel is, kunt u ervoor kiezen om de tabel opnieuw te maken zoals gerepliceerd om gegevensverplaatsing te voorkomen bij het toevoegen aan andere tabellen.

-- 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. CTAS gebruiken om een tabel met minder kolommen te maken

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

In het volgende voorbeeld wordt een gedistribueerde round robin-tabel met de naam myTable (c, ln)gemaakt. De nieuwe tabel heeft slechts twee kolommen. De kolomaliassen in de SELECT-instructie worden gebruikt voor de namen van de kolommen.

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

Voorbeelden voor queryhints

F. Een queryhint gebruiken met CREATE TABLE AS SELECT (CTAS)

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Deze query toont de basissyntaxis voor het gebruik van een hint voor querydeelname met de CTAS-instructie. Nadat de query is verzonden, past Azure Synapse Analytics de hash join-strategie toe wanneer het queryplan voor elke afzonderlijke distributie wordt gegenereerd. Zie OPTION-component (Transact-SQL) voor meer informatie over de queryhint voor hash-join.

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

Voorbeelden voor externe tabellen

G. CTAS gebruiken om gegevens te importeren uit Azure Blob Storage

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Als u gegevens uit een externe tabel wilt importeren, gebruikt u CREATE TABLE AS SELECT om een selectie uit de externe tabel te maken. De syntaxis voor het selecteren van gegevens uit een externe tabel in Azure Synapse Analytics is hetzelfde als de syntaxis voor het selecteren van gegevens uit een gewone tabel.

In het volgende voorbeeld wordt een externe tabel gedefinieerd voor gegevens in een Azure Blob Storage-account. Vervolgens wordt CREATE TABLE AS SELECT gebruikt om een selectie uit de externe tabel te maken. Hiermee importeert u de gegevens uit Azure Blob Storage bestanden met scheidingstekens en slaat u de gegevens op in een nieuwe Azure Synapse Analytics-tabel.

--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. CTAS gebruiken om Hadoop-gegevens te importeren uit een externe tabel

Van toepassing op: Analytics Platform System (PDW)

Als u gegevens wilt importeren uit een externe tabel, gebruikt u CREATE TABLE AS SELECT om een selectie uit de externe tabel te maken. De syntaxis voor het selecteren van gegevens uit een externe tabel in Analytics Platform System (PDW) is hetzelfde als de syntaxis voor het selecteren van gegevens uit een gewone tabel.

In het volgende voorbeeld wordt een externe tabel in een Hadoop-cluster gedefinieerd. Vervolgens wordt CREATE TABLE AS SELECT gebruikt om een selectie uit de externe tabel te maken. Hiermee importeert u de gegevens uit hadoop-bestanden met tekstscheidingstekens en slaat u de gegevens op in een nieuwe TABEL van Analytics Platform System (PDW).

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

Voorbeelden waarbij CTAS wordt gebruikt om SQL Server code te vervangen

Gebruik CTAS om een aantal niet-ondersteunde functies te omzeilen. Naast de mogelijkheid om uw code op het datawarehouse uit te voeren, verbetert het herschrijven van bestaande code voor het gebruik van CTAS meestal de prestaties. Dit is het resultaat van het volledig geparallelliseerde ontwerp.

Notitie

Probeer 'CTAS eerst' te denken. Als u denkt dat u een probleem kunt oplossen met behulp van CTAS , is dat over het algemeen de beste manier om het te benaderen, zelfs als u hierdoor meer gegevens schrijft.

I. Gebruik CTAS in plaats van SELECT.. IN

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server code maakt doorgaans gebruik van SELECT.. INTO om een tabel te vullen met de resultaten van een SELECT-instructie. Dit is een voorbeeld van een SQL Server SELECT.. INTO-instructie.

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

Deze syntaxis wordt niet ondersteund in Azure Synapse Analytics en Parallelle Data Warehouse. In dit voorbeeld ziet u hoe u de vorige SELECT.herschrijft. INTO-instructie als een CTAS-instructie. U kunt een van de distributieopties kiezen die worden beschreven in de CTAS-syntaxis. In dit voorbeeld wordt de distributiemethode ROUND_ROBIN gebruikt.

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

J. CTAS gebruiken om samenvoeginstructies te vereenvoudigen

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Samenvoeginstructies kunnen ten minste gedeeltelijk worden vervangen door gebruik te maken van CTAS. U kunt de INSERT en de UPDATE samenvoegen tot één instructie. Verwijderde records moeten worden afgesloten in een tweede instructie.

Een voorbeeld van een UPSERT volgt:

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. Gegevenstype en nullabiliteit van uitvoer expliciet vermelden

Van toepassing op: Azure Synapse Analytics and Analytics Platform System (PDW)

Wanneer u SQL Server code migreert naar Azure Synapse Analytics, ziet u mogelijk dat u dit type coderingspatroon uitvoert:

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
;

Instinctief denkt u misschien dat u deze code naar een CTAS moet migreren en dat u gelijk hebt. Er is hier echter een verborgen probleem.

De volgende code levert NIET hetzelfde resultaat op:

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
;

U ziet dat de kolom 'result' het gegevenstype en de null-waarden van de expressie bevat. Dit kan leiden tot subtiele afwijkingen in waarden als u niet voorzichtig bent.

Probeer het volgende als voorbeeld:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

De waarde die is opgeslagen voor het resultaat is anders. Naarmate de persistente waarde in de resultaatkolom wordt gebruikt in andere expressies, wordt de fout nog belangrijker.

Een schermopname van SQL Server Management Studio (SSMS) van de resultaten CREATE TABLE AS SELECT.

Dit is belangrijk voor gegevensmigraties. Hoewel de tweede query waarschijnlijk nauwkeuriger is, is er een probleem. De gegevens zouden anders zijn in vergelijking met het bronsysteem en dat leidt tot vragen over integriteit in de migratie. Dit is een van die zeldzame gevallen waarin het 'verkeerde' antwoord eigenlijk het juiste is!

De reden dat we dit verschil tussen de twee resultaten zien, is te maken met impliciete typecasting. In het eerste voorbeeld definieert de tabel de kolomdefinitie. Wanneer de rij wordt ingevoegd, vindt een impliciete typeconversie plaats. In het tweede voorbeeld is er geen impliciete typeconversie omdat de expressie het gegevenstype van de kolom definieert. U ziet ook dat de kolom in het tweede voorbeeld is gedefinieerd als een kolom NULLable, maar in het eerste voorbeeld niet. Bij het maken van de tabel in de eerste voorbeeldkolom is de null-waarde expliciet gedefinieerd. In het tweede voorbeeld werd dit overgelaten aan de expressie en dit zou standaard resulteren in een NULL definitie.

Als u deze problemen wilt oplossen, moet u expliciet de typeconversie en null-waarde instellen in het SELECT gedeelte van de CTAS instructie. U kunt deze eigenschappen niet instellen in het onderdeel Tabel maken.

In dit voorbeeld ziet u hoe u de code kunt herstellen:

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

Let op het volgende in het voorbeeld:

  • CAST of CONVERT kan zijn gebruikt.
  • ISNULL wordt gebruikt om NULLability niet COALESCE af te dwingen.
  • ISNULL is de buitenste functie.
  • Het tweede deel van de ISNULL is een constante, 0.

Notitie

Om de null-functie correct in te stellen, is het essentieel om te gebruiken ISNULL en niet COALESCE. COALESCE is geen deterministische functie en het resultaat van de expressie is dus altijd NULLable. ISNULL is anders. Het is deterministisch. Dus wanneer het tweede deel van de ISNULL functie een constante of een letterlijke waarde is, is de resulterende waarde NIET NULL.

Deze tip is niet alleen nuttig om de integriteit van uw berekeningen te waarborgen. Het is ook belangrijk voor het schakelen tussen tabelpartities. Stel dat u deze tabel hebt gedefinieerd als uw feit:

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

Het waardeveld is echter een berekende expressie en maakt geen deel uit van de brongegevens.

Bekijk het volgende voorbeeld om uw gepartitioneerde gegevensset te maken:

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

De query zou prima worden uitgevoerd. Het probleem treedt op wanneer u de partitieswitch probeert uit te voeren. De tabeldefinities komen niet overeen. Als u de tabeldefinities wilt maken, moet u overeenkomen met de CTAS die moet worden gewijzigd.

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

U kunt daarom zien dat typeconsistentie en het onderhouden van null-eigenschappen op een CTAS een goede technische best practice is. Het helpt de integriteit van uw berekeningen te behouden en zorgt er ook voor dat partitiewisseling mogelijk is.

L. Een geordende geclusterde columnstore-index maken met MAXDOP 1

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

Volgende stappen

Van toepassing op:Magazijn in Microsoft Fabric

CREATE TABLE AS SELECT (CTAS) is een van de belangrijkste T-SQL-functies die beschikbaar zijn. Dit is een volledig geparallelliseerde bewerking waarmee u een nieuwe tabel maakt op basis van de uitvoer van een SELECT-instructie. CTAS is de eenvoudigste en snelste manier om een kopie van een tabel te maken.

Gebruik bijvoorbeeld CTAS in Warehouse in Microsoft Fabric om het volgende te doen:

  • Maak een kopie van een tabel met enkele kolommen van de brontabel.
  • Maak een tabel die het resultaat is van een query waarmee andere tabellen worden samengevoegd.

Zie Gegevens opnemen in uw magazijn met behulp van TSQL voor meer informatie over het gebruik van CTAS in uw magazijn in Microsoft Fabric.

Notitie

Omdat CTAS bijdraagt aan de mogelijkheden van het maken van een tabel, wordt in dit onderwerp niet geprobeerd het onderwerp CREATE TABLE te herhalen. In plaats daarvan worden de verschillen tussen de CTAS- en CREATE TABLE-instructies beschreven. Zie create table-instructie voor de details van CREATE TABLE .

Transact-SQL-syntaxisconventies

Syntaxis

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

<select_statement> ::=  
    SELECT select_criteria

Argumenten

Zie argumenten in CREATE TABLE voor Microsoft Fabric voor meer informatie.

Kolomopties

column_name [ ,...n ]
Kolomnamen staan de kolomopties die worden vermeld in CREATE TABLE niet toe. In plaats daarvan kunt u een optionele lijst met een of meer kolomnamen voor de nieuwe tabel opgeven. De kolommen in de nieuwe tabel gebruiken de namen die u opgeeft. Wanneer u kolomnamen opgeeft, moet het aantal kolommen in de kolomlijst overeenkomen met het aantal kolommen in de selectieresultaten. Als u geen kolomnamen opgeeft, gebruikt de nieuwe doeltabel de kolomnamen in de resultaten van de select-instructie.

U kunt geen andere kolomopties opgeven, zoals gegevenstypen, sortering of null-mogelijkheden. Elk van deze kenmerken is afgeleid van de resultaten van de SELECT -instructie. U kunt echter de SELECT-instructie gebruiken om de kenmerken te wijzigen.

SELECT-instructie

De SELECT-instructie is het fundamentele verschil tussen CTAS en CREATE TABLE.

SELECTselect_criteria

Hiermee wordt de nieuwe tabel gevuld met de resultaten van een SELECT-instructie. select_criteria is de hoofdtekst van de SELECT-instructie die bepaalt welke gegevens naar de nieuwe tabel moeten worden gekopieerd. Zie SELECT (Transact-SQL) voor informatie over SELECT-instructies.

Machtigingen

CTAS vereist SELECT machtigingen voor alle objecten waarnaar wordt verwezen in de select_criteria.

Zie Machtigingen in CREATE TABLE voor machtigingen voor het maken van een tabel.

Opmerkingen

Zie Algemene opmerkingen in CREATE TABLE voor meer informatie.

Beperkingen en limieten

SET ROWCOUNT (Transact-SQL) heeft geen invloed op CTAS. Gebruik TOP (Transact-SQL) om een vergelijkbaar gedrag te bereiken.

Zie Beperkingen en beperkingen in CREATE TABLE voor meer informatie.

Vergrendelingsgedrag

Zie Vergrendelingsgedrag in CREATE TABLE voor meer informatie.

Voorbeelden voor het kopiëren van een tabel

Zie Gegevens opnemen in uw magazijn met behulp van TSQL voor meer informatie over het gebruik van CTAS in uw magazijn in Microsoft Fabric.

A. CTAS gebruiken om kolomkenmerken te wijzigen

In dit voorbeeld wordt CTAS gebruikt om gegevenstypen en null-mogelijkheden voor verschillende kolommen in de DimCustomer2 tabel te wijzigen.

-- 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. CTAS gebruiken om een tabel met minder kolommen te maken

In het volgende voorbeeld wordt een tabel met de naam gemaakt myTable (c, ln). De nieuwe tabel heeft slechts twee kolommen. De kolomaliassen in de SELECT-instructie worden gebruikt voor de namen van de kolommen.

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

C. Gebruik CTAS in plaats van SELECT.. IN

SQL Server code maakt doorgaans gebruik van SELECT.. INTO om een tabel te vullen met de resultaten van een SELECT-instructie. Dit is een voorbeeld van een SQL Server SELECT. INTO-instructie.

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

In dit voorbeeld ziet u hoe u de vorige SELECT herschrijft. INTO-instructie als een CTAS-instructie.

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

D. CTAS gebruiken om samenvoeginstructies te vereenvoudigen

Samenvoeginstructies kunnen, ten minste gedeeltelijk, worden vervangen door gebruik te maken van CTAS. U kunt de INSERT en de UPDATE samenvoegen in één instructie. Verwijderde records moeten worden afgesloten in een tweede instructie.

Een voorbeeld van een UPSERT :

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. Geef het gegevenstype en de null-uitvoerbaarheid expliciet op

Wanneer u SQL Server code naar Warehouse migreert, ziet u mogelijk dat u dit type coderingspatroon uitvoert:

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
;

Instinctief zou u kunnen denken dat u deze code moet migreren naar een CTAS en u zou gelijk hebben. Er is hier echter een verborgen probleem.

De volgende code levert NIET hetzelfde resultaat op:

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

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

U ziet dat de kolom 'result' het gegevenstype en de null-waarden van de expressie doorstuurt. Dit kan leiden tot subtiele afwijkingen in waarden als u niet voorzichtig bent.

Probeer het volgende als voorbeeld:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

De waarde die is opgeslagen voor het resultaat is anders. Naarmate de persistente waarde in de resultaatkolom wordt gebruikt in andere expressies, wordt de fout nog belangrijker.

Een schermopname van SQL Server Management Studio (SSMS) van de resultaten VAN CREATE TABLE AS SELECT.

Dit is belangrijk voor gegevensmigraties. Hoewel de tweede query mogelijk nauwkeuriger is, is er een probleem. De gegevens zouden anders zijn in vergelijking met het bronsysteem en dat leidt tot vragen over integriteit in de migratie. Dit is een van die zeldzame gevallen waarin het 'verkeerde' antwoord eigenlijk het juiste is!

De reden dat we dit verschil tussen de twee resultaten zien, is te maken met impliciete typecasting. In het eerste voorbeeld definieert de tabel de kolomdefinitie. Wanneer de rij wordt ingevoegd, vindt een impliciete typeconversie plaats. In het tweede voorbeeld is er geen impliciete typeconversie omdat de expressie het gegevenstype van de kolom definieert. U ziet ook dat de kolom in het tweede voorbeeld is gedefinieerd als een kolom NULLable, maar in het eerste voorbeeld niet. Bij het maken van de tabel in het eerste voorbeeld is null-bruikbaarheid expliciet gedefinieerd. In het tweede voorbeeld werd dit overgelaten aan de expressie en dit zou standaard resulteren in een NULL definitie.

Als u deze problemen wilt oplossen, moet u expliciet de typeconversie en null-waarde instellen in het SELECT gedeelte van de CTAS instructie. U kunt deze eigenschappen niet instellen in het onderdeel Tabel maken.

In dit voorbeeld ziet u hoe u de code kunt herstellen:

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

Let op het volgende in het voorbeeld:

  • CAST of CONVERT kan zijn gebruikt.
  • ISNULL wordt gebruikt om NULLability niet COALESCE af te dwingen.
  • ISNULL is de buitenste functie.
  • Het tweede deel van de ISNULL is een constante, 0.

Notitie

Om de null-functie correct in te stellen, is het essentieel om te gebruiken ISNULL en niet COALESCE. COALESCE is geen deterministische functie en het resultaat van de expressie is dus altijd NULLable. ISNULL is anders. Het is deterministisch. Dus wanneer het tweede deel van de ISNULL functie een constante of een letterlijke waarde is, is de resulterende waarde NIET NULL.

Deze tip is niet alleen nuttig om de integriteit van uw berekeningen te waarborgen. Het is ook belangrijk voor het schakelen tussen tabelpartities. Stel dat u deze tabel hebt gedefinieerd als uw feit:

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

Het waardeveld is echter een berekende expressie en maakt geen deel uit van de brongegevens.

U kunt daarom zien dat typeconsistentie en het onderhouden van null-eigenschappen op een CTAS een goede technische best practice is. Het helpt om de integriteit van uw berekeningen te behouden.

Volgende stappen