CREATE TABLE AS SELECT

Dotyczy:Azure Synapse Analytics AnalyticsPlatform System (PDW)

CREATE TABLE AS SELECT (CTAS) to jedna z najważniejszych dostępnych funkcji języka T-SQL. Jest to w pełni równoległa operacja, która tworzy nową tabelę na podstawie danych wyjściowych instrukcji SELECT. CTAS to najprostszy i najszybszy sposób tworzenia kopii tabeli.

Na przykład użyj funkcji CTAS, aby:

• Utwórz ponownie tabelę z inną kolumną rozkładu skrótów.
• Utwórz ponownie tabelę w postaci replikowanej.
• Utwórz indeks magazynu kolumn dla tylko niektórych kolumn w tabeli.
• Wykonywanie zapytań lub importowanie danych zewnętrznych.

Uwaga

Ponieważ funkcja CTAS dodaje do możliwości tworzenia tabeli, ten temat próbuje nie powtórzyć tematu CREATE TABLE. Zamiast tego opisuje różnice między instrukcjami CTAS i CREATE TABLE. Aby uzyskać szczegółowe informacje o tabeli CREATE, zobacz instrukcję CREATE TABLE (Azure Synapse Analytics).

• Ta składnia nie jest obsługiwana przez bezserwerową pulę SQL w usłudze Azure Synapse Analytics.
• Usługa CTAS jest obsługiwana w magazynie w usłudze Microsoft Fabric.

Konwencje składni języka Transact-SQL

Składnia

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

Aby uzyskać szczegółowe informacje, zobacz sekcję Argumenty w sekcji CREATE TABLE.

Opcje kolumn

column_name [ ,...n ]
Nazwy kolumn nie zezwalają na opcje kolumn wymienione w sekcji CREATE TABLE. Zamiast tego możesz podać opcjonalną listę co najmniej jednej nazwy kolumn dla nowej tabeli. Kolumny w nowej tabeli używają podanych nazw. Po określeniu nazw kolumn liczba kolumn na liście kolumn musi być zgodna z liczbą kolumn w wybranych wynikach. Jeśli nie określisz żadnych nazw kolumn, nowa tabela docelowa używa nazw kolumn w wynikach instrukcji select.

Nie można określić żadnych innych opcji kolumn, takich jak typy danych, sortowanie lub wartość null. Każdy z tych atrybutów pochodzi z wyników instrukcji SELECT . Można jednak użyć instrukcji SELECT, aby zmienić atrybuty. Aby zapoznać się z przykładem, zobacz Zmienianie atrybutów kolumn przy użyciu usługi CTAS.

Opcje dystrybucji tabel

Aby uzyskać szczegółowe informacje i zrozumieć, jak wybrać najlepszą kolumnę dystrybucji, zobacz sekcję Opcje dystrybucji tabel w sekcji CREATE TABLE. Aby uzyskać zalecenia dotyczące wyboru dystrybucji dla tabeli na podstawie rzeczywistego użycia lub przykładowych zapytań, zobacz Doradca dystrybucji w usłudze Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLIKOWANIE instrukcja CTAS wymaga opcji dystrybucji i nie ma wartości domyślnych. Różni się to od metody CREATE TABLE, która ma wartości domyślne.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Dystrybuuje wiersze na podstawie wartości skrótu do ośmiu kolumn, co pozwala na bardziej równomierny rozkład danych tabeli bazowej, zmniejszając niesymetryczność danych w czasie i zwiększając wydajność zapytań.

Uwaga

• Aby włączyć funkcję, zmień poziom zgodności bazy danych na 50 za pomocą tego polecenia. Aby uzyskać więcej informacji na temat ustawiania poziomu zgodności bazy danych, zobacz ALTER DATABASE SCOPED CONFIGURATION (ALTER DATABASE SCOPED CONFIGURATION). Na przykład: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Aby wyłączyć funkcję dystrybucji wielokolumnachowej (MCD), uruchom to polecenie, aby zmienić poziom zgodności bazy danych na AUTO. Na przykład: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Istniejące tabele MCD pozostaną, ale staną się nieczytelne. Zapytania dotyczące tabel MCD zwracają następujący błąd: 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.
  • Aby odzyskać dostęp do tabel MCD, włącz tę funkcję ponownie.
  • Aby załadować dane do tabeli MCD, użyj instrukcji CTAS, a źródło danych musi być tabelami SQL usługi Synapse.
  • Funkcja CTAS w tabelach docelowych MCD HEAP nie jest obsługiwana. Zamiast tego użyj polecenia INSERT SELECT jako obejścia, aby załadować dane do tabel HEAP MCD.
• Używanie programu SSMS do generowania skryptu do tworzenia tabel MCD jest obecnie obsługiwane poza wersją 19 programu SSMS.

Aby uzyskać szczegółowe informacje i zrozumieć, jak wybrać najlepszą kolumnę dystrybucji, zobacz sekcję Opcje dystrybucji tabel w sekcji CREATE TABLE.

Opcje partycji tabeli

Instrukcja CTAS domyślnie tworzy tabelę niepartycyjną, nawet jeśli tabela źródłowa jest partycjonowana. Aby utworzyć tabelę partycjonowaną za pomocą instrukcji CTAS, należy określić opcję partycji.

Aby uzyskać szczegółowe informacje, zobacz sekcję Opcje partycji tabeli w sekcji CREATE TABLE.

Instrukcja SELECT

Instrukcja SELECT jest podstawową różnicą między usługami CTAS i CREATE TABLE.

WITHcommon_table_expression

Określa tymczasowy nazwany zestaw wyników, znany jako wspólne wyrażenie tabeli (CTE). Aby uzyskać więcej informacji, zobacz WITH common_table_expression (Transact-SQL).

SELECTselect_criteria

Wypełnia nową tabelę wynikami instrukcji SELECT. select_criteria jest treścią instrukcji SELECT, która określa dane do skopiowania do nowej tabeli. Aby uzyskać informacje o instrukcjach SELECT, zobacz SELECT (Transact-SQL).

Wskazówka dotycząca zapytań

Użytkownicy mogą ustawić wartość MAXDOP na wartość całkowitą, aby kontrolować maksymalny stopień równoległości. Po ustawieniu wartości MAXDOP na 1 zapytanie jest wykonywane przez jeden wątek.

Uprawnienia

CTAS wymaga SELECT uprawnień do wszystkich obiektów, do których odwołuje się select_criteria.

Aby uzyskać uprawnienia do tworzenia tabeli, zobacz Uprawnienia w sekcji CREATE TABLE.

Uwagi

Aby uzyskać szczegółowe informacje, zobacz Ogólne uwagi w temacie CREATE TABLE.

Limity i ograniczenia

Uporządkowany indeks magazynu kolumn można utworzyć na kolumnach dowolnego typu danych obsługiwanego w usłudze Azure Synapse Analytics z wyjątkiem kolumn ciągu.

SET ROWCOUNT (Transact-SQL) nie ma wpływu na CTAS. Aby osiągnąć podobne zachowanie, użyj polecenia TOP (Transact-SQL).

Aby uzyskać szczegółowe informacje, zobacz Ograniczenia i ograniczenia w temacie CREATE TABLE.

Zachowanie blokowania

Aby uzyskać szczegółowe informacje, zobacz Blokowanie zachowania w tabeli CREATE TABLE.

Wydajność

W przypadku tabeli rozproszonej skrótów można użyć usługi CTAS, aby wybrać inną kolumnę dystrybucji, aby uzyskać lepszą wydajność sprzężeń i agregacji. Jeśli wybranie innej kolumny dystrybucji nie jest twoim celem, będziesz mieć najlepszą wydajność usługi CTAS, jeśli określisz tę samą kolumnę dystrybucji, ponieważ pozwoli to uniknąć ponownego dystrybuowania wierszy.

Jeśli używasz usługi CTAS do tworzenia tabeli i wydajności nie jest czynnikiem, możesz określić ROUND_ROBIN , aby uniknąć konieczności decydowania o kolumnie dystrybucji.

Aby uniknąć przenoszenia danych w kolejnych zapytaniach, możesz określić REPLICATE koszt zwiększonego magazynu do załadowania pełnej kopii tabeli w każdym węźle obliczeniowym.

Przykłady kopiowania tabeli

A. Kopiowanie tabeli przy użyciu usługi CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Być może jednym z najczęściej używanych CTAS zastosowań jest utworzenie kopii tabeli, aby można było zmienić DDL. Jeśli na przykład utworzono tabelę jako ROUND_ROBIN i teraz chcesz ją zmienić na tabelę dystrybuowaną w kolumnie, CTAS możesz zmienić kolumnę dystrybucji. CTAS Można również użyć do zmiany partycjonowania, indeksowania lub typów kolumn.

Załóżmy, że utworzono tę tabelę, określając HEAP i używając domyślnego typu dystrybucji .ROUND_ROBIN

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

Teraz chcesz utworzyć nową kopię tej tabeli z klastrowanym indeksem magazynu kolumn, aby móc korzystać z wydajności tabel magazynu kolumn klastrowanych. Chcesz również rozpowszechnić tę tabelę, ProductKey ponieważ przewidujesz sprzężenia w tej kolumnie i chcesz uniknąć przenoszenia danych podczas sprzężeń w systemie ProductKey. Na koniec chcesz również dodać partycjonowanie OrderDateKey , aby można było szybko usunąć stare dane, upuszczając stare partycje. Oto instrukcja CTAS, która będzie kopiować starą tabelę do nowej tabeli:

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;

Na koniec możesz zmienić nazwę tabel, aby zamienić nową tabelę, a następnie usunąć starą tabelę.

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

DROP TABLE FactInternetSales_old;

Przykłady opcji kolumn

B. Zmienianie atrybutów kolumny za pomocą funkcji CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

W tym przykładzie użyto funkcji CTAS do zmiany typów danych, wartości null i sortowania dla kilku kolumn w DimCustomer2 tabeli.

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

W ostatnim kroku możesz użyć polecenia RENAME (Transact-SQL), aby przełączyć nazwy tabel. To sprawia, że DimCustomer2 jest nową tabelą.

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

DROP TABLE DimCustomer2_old;

Przykłady dystrybucji tabel

C. Zmienianie metody dystrybucji dla tabeli za pomocą funkcji CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

W tym prostym przykładzie pokazano, jak zmienić metodę dystrybucji dla tabeli. Aby pokazać mechanikę sposobu wykonania tej czynności, zmienia tabelę rozproszoną skrótami na działanie okrężne, a następnie zmienia tabelę działania okrężnego z powrotem na rozproszoną skrót. Końcowa tabela jest zgodna z oryginalną tabelą.

W większości przypadków nie trzeba zmieniać tabeli rozproszonej przy użyciu skrótów na tabelę z działaniem okrężnym. Częściej może być konieczne zmianę tabeli z działaniem okrężnym na tabelę rozproszoną przy użyciu skrótu. Na przykład można początkowo załadować nową tabelę jako działanie okrężne, a następnie przenieść ją do tabeli rozproszonej przy użyciu skrótów, aby uzyskać lepszą wydajność sprzężenia.

W tym przykładzie użyto przykładowej bazy danych AdventureWorksDW. Aby załadować wersję usługi Azure Synapse Analytics, zobacz Szybki start: tworzenie dedykowanej puli SQL (dawniej SQL DW) w usłudze Azure Synapse Analytics przy użyciu 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];

Następnie zmień ją z powrotem na tabelę rozproszoną skrótów.

-- 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. Konwertowanie tabeli na tabelę replikową za pomocą funkcji CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Ten przykład dotyczy konwertowania tabel rozproszonych przy użyciu działania okrężnego lub skrótu do replikowanej tabeli. W tym konkretnym przykładzie poprzednia metoda zmieniania typu dystrybucji o krok dalej. Ponieważ DimSalesTerritory jest to wymiar i prawdopodobnie mniejsza tabela, możesz ponownie utworzyć tabelę jako zreplikowana, aby uniknąć przenoszenia danych podczas łączenia z innymi tabelami.

-- 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. Tworzenie tabeli z mniejszą liczbą kolumn za pomocą funkcji CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Poniższy przykład tworzy tabelę rozproszoną z działaniem okrężnym o nazwie myTable (c, ln). Nowa tabela zawiera tylko dwie kolumny. Używa aliasów kolumn w instrukcji SELECT dla nazw kolumn.

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

Przykłady wskazówek dotyczących zapytań

F. Używanie wskazówki dotyczącej zapytania z funkcją CREATE TABLE AS SELECT (CTAS)

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

To zapytanie pokazuje podstawową składnię użycia wskazówki dotyczącej sprzężenia zapytania z instrukcją CTAS. Po przesłaniu zapytania usługa Azure Synapse Analytics stosuje strategię sprzężenia skrótu podczas generowania planu zapytania dla każdej indywidualnej dystrybucji. Aby uzyskać więcej informacji na temat wskazówek zapytania dotyczących sprzężenia skrótu, zobacz KLAUZULA OPTION (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 );  

Przykłady tabel zewnętrznych

G. Importowanie danych z usługi Azure Blob Storage za pomocą funkcji CTAS

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Aby zaimportować dane z tabeli zewnętrznej, użyj polecenia CREATE TABLE AS SELECT, aby wybrać z tabeli zewnętrznej. Składnia wybierania danych z tabeli zewnętrznej do usługi Azure Synapse Analytics jest taka sama jak składnia wybierania danych z regularnej tabeli.

W poniższym przykładzie zdefiniowano tabelę zewnętrzną na danych na koncie Azure Blob Storage. Następnie używa polecenia CREATE TABLE AS SELECT do wybrania z tabeli zewnętrznej. Spowoduje to zaimportowanie danych z Azure Blob Storage plików rozdzielonych tekstem i zapisanie danych w nowej tabeli Azure Synapse Analytics.

--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. Importowanie danych hadoop z tabeli zewnętrznej za pomocą funkcji CTAS

Dotyczy: Analytics Platform System (PDW)

Aby zaimportować dane z tabeli zewnętrznej, po prostu użyj polecenia CREATE TABLE AS SELECT, aby wybrać z tabeli zewnętrznej. Składnia wybierania danych z tabeli zewnętrznej do systemu platformy analizy (PDW) jest taka sama jak składnia wybierania danych z regularnej tabeli.

W poniższym przykładzie zdefiniowano tabelę zewnętrzną w klastrze hadoop. Następnie używa polecenia CREATE TABLE AS SELECT do wybrania z tabeli zewnętrznej. Spowoduje to zaimportowanie danych z plików tekstowych usługi Hadoop i zapisanie danych w nowej tabeli 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  
;   

Przykłady używania funkcji CTAS do zastępowania kodu SQL Server

Użyj funkcji CTAS, aby obejść niektóre nieobsługiwane funkcje. Oprócz możliwości uruchamiania kodu w magazynie danych ponowne zapisywanie istniejącego kodu w celu używania funkcji CTAS zwykle poprawi wydajność. Jest to wynik w pełni zrównanego projektu.

Uwaga

Spróbuj myśleć "CTAS first". Jeśli uważasz, że możesz rozwiązać problem, to CTAS jest to zazwyczaj najlepszy sposób, aby go podejść — nawet jeśli zapisujesz więcej danych w wyniku.

I. Użyj funkcji CTAS zamiast SELECT.. DO

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server kod zwykle używa funkcji SELECT.. INTO, aby wypełnić tabelę wynikami instrukcji SELECT. Jest to przykład SQL Server SELECT.. INSTRUKCJA INTO.

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

Ta składnia nie jest obsługiwana w usługach Azure Synapse Analytics i Parallel Data Warehouse. W tym przykładzie pokazano, jak ponownie napisać poprzedni element SELECT.. Instrukcja INTO jako instrukcja CTAS. Możesz wybrać jedną z opcji DYSTRYBUCJI opisanych w składni CTAS. W tym przykładzie użyto metody dystrybucji ROUND_ROBIN.

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

J. Używanie funkcji CTAS w celu uproszczenia instrukcji scalania

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Instrukcje scalania można zastąpić co najmniej częściowo przy użyciu polecenia CTAS. Element i UPDATE można skonsolidować INSERT w jedną instrukcję. Wszystkie usunięte rekordy muszą zostać zamknięte w drugiej instrukcji.

Przykładowy przykład:UPSERT

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. Jawny stan typu danych i dopuszczalność wartości null danych wyjściowych

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Podczas migrowania kodu SQL Server do usługi Azure Synapse Analytics można znaleźć przebieg w ramach tego typu wzorca kodowania:

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
;

Instynktownie można pomyśleć, że należy przeprowadzić migrację tego kodu do CTAS i byłoby poprawne. Jednak w tym miejscu występuje ukryty problem.

Poniższy kod nie daje tego samego wyniku:

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
;

Zwróć uwagę, że kolumna "result" przekazuje wartości typu danych i wartości null wyrażenia. Może to prowadzić do subtelnych wariancji w wartościach, jeśli nie jesteś ostrożny.

Spróbuj wykonać następujące czynności jako przykład:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Wartość przechowywana dla wyniku jest inna. Ponieważ utrwalone wartości w kolumnie wynikowej są używane w innych wyrażeniach, błąd staje się jeszcze bardziej znaczący.

Jest to ważne w przypadku migracji danych. Mimo że drugie zapytanie jest prawdopodobnie bardziej dokładne, występuje problem. Dane będą się różnić w porównaniu z systemem źródłowym i prowadzą do pytań o integralność migracji. Jest to jeden z tych rzadkich przypadków, w których "niewłaściwa" odpowiedź jest w rzeczywistości właściwa!

Przyczyną, dla którego widzimy tę rozbieżność między dwoma wynikami, jest niejawne rzutowanie typów. W pierwszym przykładzie tabela definiuje definicję kolumny. Po wstawieniu wiersza następuje niejawna konwersja typu. W drugim przykładzie nie ma niejawnej konwersji typu, ponieważ wyrażenie definiuje typ danych kolumny. Zauważ również, że kolumna w drugim przykładzie została zdefiniowana jako kolumna NULLable, natomiast w pierwszym przykładzie nie została zdefiniowana. Po utworzeniu tabeli w pierwszej przykładowej kolumnie zerowość została jawnie zdefiniowana. W drugim przykładzie pozostawiono to wyrażenie i domyślnie spowodowałoby to definicję NULL .

Aby rozwiązać te problemy, należy jawnie ustawić konwersję typu i wartość null w SELECT części instrukcji CTAS . Nie można ustawić tych właściwości w części tworzenia tabeli.

W tym przykładzie pokazano, jak naprawić kod:

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

Zwróć uwagę na następujące kwestie w przykładzie:

• Można było użyć rzutu lub konwersji.
• FUNKCJA ISULL jest używana do wymuszania NULLability nie COALESCE.
• ISULL jest najbardziej zewnętrzną funkcją.
• Druga część funkcji ISNULL jest stałą , 0.

Uwaga

Aby można było poprawnie ustawić wartość null, należy użyć parametru ISNULL , a nie COALESCE. COALESCE nie jest funkcją deterministyczną, więc wynik wyrażenia zawsze będzie NULLable. ISNULL jest inny. Jest deterministyczny. W związku z tym, gdy druga część ISNULL funkcji jest stałą lub literałem, wynikowa wartość nie będzie równa NULL.

Ta wskazówka nie jest po prostu przydatna do zapewnienia integralności obliczeń. Ważne jest również przełączanie partycji tabeli. Wyobraź sobie, że ta tabela jest zdefiniowana jako fakt:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     MONEY   NOT NULL
,   [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

Jednak pole wartości jest wyrażeniem obliczeniowym, które nie jest częścią danych źródłowych.

Aby utworzyć partycjonowany zestaw danych, rozważmy następujący przykład:

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

Zapytanie będzie działać doskonale. Problem występuje podczas próby wykonania przełącznika partycji. Definicje tabeli nie są zgodne. Aby wprowadzić definicje tabeli, należy zmodyfikować element CTAS.

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

W związku z tym można zobaczyć, że spójność typu i utrzymywanie właściwości null w obiekcie CTAS jest dobrym najlepszym rozwiązaniem inżynieryjnym. Pomaga zachować integralność obliczeń, a także zapewnia możliwość przełączania partycji.

L. Tworzenie uporządkowanego klastrowanego indeksu magazynu kolumn za pomocą polecenia MAXDOP 1

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

Następne kroki

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• DROP EXTERNAL TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• ALTER EXTERNAL TABLE (Transact-SQL)

Dotyczy:Magazyn w usłudze Microsoft Fabric

FUNKCJA CREATE TABLE AS SELECT (CTAS) jest jedną z najważniejszych dostępnych funkcji języka T-SQL. Jest to w pełni równoległa operacja, która tworzy nową tabelę na podstawie danych wyjściowych instrukcji SELECT. CTAS to najprostszy i najszybszy sposób tworzenia kopii tabeli.

Na przykład użyj funkcji CTAS w magazynie w usłudze Microsoft Fabric, aby:

• Utwórz kopię tabeli z niektórymi kolumnami tabeli źródłowej.
• Utwórz tabelę, która jest wynikiem zapytania, które łączy inne tabele.

Aby uzyskać więcej informacji na temat używania funkcji CTAS w magazynie w usłudze Microsoft Fabric, zobacz Pozyskiwanie danych do magazynu przy użyciu języka TSQL.

Uwaga

Ponieważ funkcja CTAS dodaje do możliwości tworzenia tabeli, ten temat próbuje nie powtórzyć tematu CREATE TABLE. Zamiast tego opisuje różnice między instrukcjami CTAS i CREATE TABLE. Aby uzyskać szczegółowe informacje na temat instrukcji CREATE TABLE, zobacz INSTRUKCJĘ CREATE TABLE .

Konwencje składni języka Transact-SQL

Składnia

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

<select_statement> ::=  
    SELECT select_criteria  

Argumenty

Aby uzyskać szczegółowe informacje, zobacz Argumenty w artykule CREATE TABLE for Microsoft Fabric (Argumenty w artykule CREATE TABLE for Microsoft Fabric).

Opcje kolumn

column_name [ ,...n ]
Nazwy kolumn nie zezwalają na opcje kolumn wymienione w sekcji CREATE TABLE. Zamiast tego możesz podać opcjonalną listę co najmniej jednej nazwy kolumn dla nowej tabeli. Kolumny w nowej tabeli używają podanych nazw. Po określeniu nazw kolumn liczba kolumn na liście kolumn musi być zgodna z liczbą kolumn w wybranych wynikach. Jeśli nie określisz nazw kolumn, nowa tabela docelowa używa nazw kolumn w wynikach instrukcji select.

Nie można określić żadnych innych opcji kolumn, takich jak typy danych, sortowanie lub wartość null. Każdy z tych atrybutów pochodzi z wyników instrukcji SELECT . Można jednak użyć instrukcji SELECT, aby zmienić atrybuty.

Instrukcja SELECT

Instrukcja SELECT jest podstawową różnicą między instrukcjami CTAS i CREATE TABLE.

SELECTselect_criteria

Wypełnia nową tabelę wynikami z instrukcji SELECT. select_criteria to treść instrukcji SELECT, która określa dane do skopiowania do nowej tabeli. Aby uzyskać informacje na temat instrukcji SELECT, zobacz SELECT (Transact-SQL).

Uprawnienia

Funkcja CTAS wymaga SELECT uprawnień do wszystkich obiektów, do których odwołuje się select_criteria.

Aby uzyskać uprawnienia do tworzenia tabeli, zobacz Uprawnienia w temacie CREATE TABLE.

Uwagi

Aby uzyskać szczegółowe informacje, zobacz Ogólne uwagi w temacie CREATE TABLE.

Limity i ograniczenia

SET ROWCOUNT (Transact-SQL) nie ma wpływu na CTAS. Aby osiągnąć podobne zachowanie, użyj języka TOP (Transact-SQL).

Aby uzyskać szczegółowe informacje, zobacz Ograniczenia i ograniczenia w artykule CREATE TABLE.

Zachowanie blokujące

Aby uzyskać szczegółowe informacje, zobacz Blokowanie zachowania w instrukcji CREATE TABLE.

Przykłady kopiowania tabeli

Aby uzyskać więcej informacji na temat używania funkcji CTAS w magazynie w usłudze Microsoft Fabric, zobacz Pozyskiwanie danych do magazynu przy użyciu języka TSQL.

A. Zmienianie atrybutów kolumny za pomocą funkcji CTAS

W tym przykładzie użyto funkcji CTAS do zmiany typów danych i wartości null dla kilku kolumn w DimCustomer2 tabeli.

-- 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. Tworzenie tabeli z mniejszą liczbą kolumn za pomocą funkcji CTAS

Poniższy przykład tworzy tabelę o nazwie myTable (c, ln). Nowa tabela zawiera tylko dwie kolumny. Używa aliasów kolumn w instrukcji SELECT dla nazw kolumn.

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

C. Użyj funkcji CTAS zamiast SELECT.. DO

SQL Server kod zwykle używa funkcji SELECT.. INTO, aby wypełnić tabelę wynikami instrukcji SELECT. Jest to przykład SQL Server SELECT.. INSTRUKCJA INTO.

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

W tym przykładzie pokazano, jak ponownie napisać poprzedni element SELECT.. Instrukcja INTO jako instrukcja CTAS.

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

D. Używanie funkcji CTAS w celu uproszczenia instrukcji scalania

Instrukcje scalania można zastąpić co najmniej częściowo przy użyciu polecenia CTAS. Element i UPDATE można skonsolidować INSERT w jedną instrukcję. Wszystkie usunięte rekordy muszą zostać zamknięte w drugiej instrukcji.

Przykładowy przykład: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. Jawny stan typu danych i dopuszczalność wartości null danych wyjściowych

Podczas migrowania kodu SQL Server do magazynu może się okazać, że uruchamiasz ten typ wzorca kodowania:

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
;

Instynktownie można pomyśleć, że należy przeprowadzić migrację tego kodu do CTAS i byłoby poprawne. Jednak w tym miejscu występuje ukryty problem.

Poniższy kod nie daje tego samego wyniku:

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

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

Zwróć uwagę, że kolumna "result" przekazuje wartości typu danych i wartości null wyrażenia. Może to prowadzić do subtelnych wariancji w wartościach, jeśli nie jesteś ostrożny.

Spróbuj wykonać następujące czynności jako przykład:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Wartość przechowywana dla wyniku jest inna. Ponieważ utrwalone wartości w kolumnie wynikowej są używane w innych wyrażeniach, błąd staje się jeszcze bardziej znaczący.

Jest to ważne w przypadku migracji danych. Mimo że drugie zapytanie jest prawdopodobnie bardziej dokładne, występuje problem. Dane będą się różnić w porównaniu z systemem źródłowym i prowadzą do pytań o integralność migracji. Jest to jeden z tych rzadkich przypadków, w których "niewłaściwa" odpowiedź jest w rzeczywistości właściwa!

Przyczyną, dla którego widzimy tę rozbieżność między dwoma wynikami, jest niejawne rzutowanie typów. W pierwszym przykładzie tabela definiuje definicję kolumny. Po wstawieniu wiersza następuje niejawna konwersja typu. W drugim przykładzie nie ma niejawnej konwersji typu, ponieważ wyrażenie definiuje typ danych kolumny. Zauważ również, że kolumna w drugim przykładzie została zdefiniowana jako kolumna NULLable, natomiast w pierwszym przykładzie nie została zdefiniowana. Po utworzeniu tabeli w pierwszej przykładowej kolumnie zerowość została jawnie zdefiniowana. W drugim przykładzie pozostawiono to wyrażenie i domyślnie spowodowałoby to definicję NULL .

Aby rozwiązać te problemy, należy jawnie ustawić konwersję typu i wartość null w SELECT części instrukcji CTAS . Nie można ustawić tych właściwości w części tworzenia tabeli.

W tym przykładzie pokazano, jak naprawić kod:

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

Zwróć uwagę na następujące kwestie w przykładzie:

• Można było użyć rzutu lub konwersji.
• FUNKCJA ISULL jest używana do wymuszania NULLability nie COALESCE.
• ISULL jest najbardziej zewnętrzną funkcją.
• Druga część funkcji ISNULL jest stałą , 0.

Uwaga

Aby można było poprawnie ustawić wartość null, należy użyć parametru ISNULL , a nie COALESCE. COALESCE nie jest funkcją deterministyczną, więc wynik wyrażenia zawsze będzie NULLable. ISNULL jest inny. Jest deterministyczny. W związku z tym, gdy druga część ISNULL funkcji jest stałą lub literałem, wynikowa wartość nie będzie równa NULL.

Ta wskazówka nie jest po prostu przydatna do zapewnienia integralności obliczeń. Ważne jest również przełączanie partycji tabeli. Wyobraź sobie, że ta tabela jest zdefiniowana jako fakt:

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

Jednak pole wartości jest wyrażeniem obliczeniowym, które nie jest częścią danych źródłowych.

W związku z tym można zobaczyć, że spójność typu i utrzymywanie właściwości null w obiekcie CTAS jest dobrym najlepszym rozwiązaniem inżynieryjnym. Pomaga zachować integralność w obliczeniach.

Następne kroki

• Tworzenie tabel w magazynie w usłudze Microsoft Fabric
• Co to jest magazynowanie danych w usłudze Microsoft Fabric?
• Pozyskiwanie danych do magazynu przy użyciu języka TSQL