UTWÓRZ TABELĘ JAKO SELECT

Dotyczy:azure Synapse AnalyticsAnalytics Platform System (PDW)

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

Na przykład użyj CTAS do:

• Utwórz na nowo tabelę z inną kolumną rozkładu skrótów.
• Utwórz na nowo tabelę w oryginalnej formie.
• Utwórz indeks columnstore tylko dla niektórych kolumn w tabeli.
• Zapytaj lub importuj dane zewnętrzne.

Uwaga / Notatka

Ponieważ CREATE TABLE AS SELECT (CTAS) zwiększa możliwości tworzenia tabeli, ten temat stara się nie powtarzać tego CREATE TABLE tematu. Zamiast tego opisuje różnice między CTAS a CREATE TABLE.

• CTAS jest obsługiwany w magazynie Microsoft Fabric. Zobacz wersję Fabric artykułu CREATE TABLE AS SELECT.
• Ta składnia nie jest obsługiwana przez bezserwerową pulę SQL w usłudze Azure Synapse Analytics.
• CREATE TABLE AS SELECT (CTAS) jest obsługiwany w magazynie Microsoft Fabric. Więcej informacji można znaleźć w wersji tego artykułu dotyczącej Fabric Data Warehouse.

Transact-SQL konwencje składni

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 
    }

Arguments

Więcej informacji można znaleźć w sekcji Argumenty w CREATE TABLE.

Opcje kolumn

column_name [ ,...n ]
Nazwy kolumn nie pozwalają na opcje kolumn CREATE TABLE wymienione w .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 wynikach wyboru. 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 jest wyprowadzany z wyników stwierdzenia SELECT . Można jednak użyć instrukcji SELECT, aby zmienić atrybuty. Dla przykładu, zobacz: Używaj CTAS do zmiany atrybutów kolumn.

Opcje dystrybucji tabel

Szczegóły i sposób wyboru najlepszej kolumny dystrybucji znajdziesz w sekcji Opcje rozkładu w tabeli w CREATE TABLE. Aby uzyskać rekomendacje, którą dystrybucję wybrać dla tabeli na podstawie rzeczywistego użycia lub przykładowych zapytań, zobacz Distribution Advisor w Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | SYMULOWAĆ

Oświadczenie CTAS wymaga opcji dystrybucji i nie ma wartości domyślnych. To różni się od CREATE TABLE, który ma domyślne ustawienia.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )

Rozkłada wiersze na podstawie wartości skrótu do ośmiu kolumn, co pozwala na bardziej równomierne rozłożenie danych tabeli bazowej, zmniejsza przesunięcie danych w czasie i poprawia wydajność zapytań.

Uwaga / Notatka

• Aby włączyć tę funkcję, zmień poziom kompatybilności bazy danych na 50 za pomocą tego polecenia. Więcej informacji na temat ustawiania poziomu kompatybilności bazy danych można znaleźć w ALTER DATABASE SCOPED CONFIGURATION. Przykład: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Aby wyłączyć funkcję dystrybucji wielokolumnowej (MCD), uruchom to polecenie, aby zmienić poziom kompatybilnoś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 nad tabelami MCD zwracają ten 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, ponownie włącz tę funkcję.
  • Aby załadować dane do tabeli MCD, użyj instrukcji CTAS, a źródłem danych muszą być tabele SQL Synapse.
  • CTAS w tabelach docelowych MCD HEAP nie jest obsługiwany. Zamiast tego użyj INSERT SELECT jako obejścia do ładowania danych do tabel MCD HEAP.
• Obecnie po wersji SSMS SSMS jest obsługiwany do generowania skryptu do tworzenia tabel MCD.

Szczegóły i sposób wyboru najlepszej kolumny dystrybucji znajdziesz w sekcji Opcje rozkładu w tabeli w CREATE TABLE.

Rekomendacje dotyczące najlepszej dystrybucji do wykorzystania w zależności od obciążeń znajdziesz w Synapse SQL Distribution Advisor (Preview).

Opcje partycji tabel

Instrukcja CTAS domyślnie tworzy tabelę niepartycjonowaną, nawet jeśli tabela źródłowa jest podzielona na partycjon. Aby utworzyć tabelę partycjonowaną z inkaucją CTAS, musisz określić opcję partycji.

Szczegóły można znaleźć w sekcji opcji partycji tabeli w CREATE TABLE.

Instrukcja SELECT

Instrukcja SELECT stanowi podstawową różnicę między CTAS a CREATE TABLE.

WITH common_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).

SELECT select_criteria

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

Podpowiedzi zapytania

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

Permissions

CTAS wymaga SELECT zgody na wszystkie obiekty odwoływane w select_criteria.

Uprawnienia do tworzenia tabeli można znaleźć w sekcji Uprawnienia w CREATE TABLE.

Uwagi

Szczegóły można znaleźć w Uwagi Ogólne w .CREATE TABLE

Ograniczenia i ograniczenia

Więcej szczegółów dotyczących ograniczeń i ograniczeń można znaleźć w sekcji Ograniczenia i ograniczenia w CREATE TABLE.

• Uporządkowany klastrowany indeks magazynu kolumn może być utworzony na kolumnach dowolnych typów danych obsługiwanych w Azure Synapse Analytics, z wyjątkiem kolumn łańcuchowych.

• USTAW LICZBĘ WIERSZY (Transact-SQL) nie wpływa na CTAS. Aby osiągnąć podobne zachowanie, użyj TOP (Transact-SQL).

• CTAS nie wspiera OPENJSON tej funkcji jako części oświadczenia SELECT . Alternatywnie używamy INSERT INTO ... SELECT. Przykład:

  DECLARE @json NVARCHAR(MAX) = N'
  [
      {
          "id": 1,
          "name": "Alice",
          "age": 30,
          "address": {
              "street": "123 Main St",
              "city": "Wonderland"
          }
      },
      {
          "id": 2,
          "name": "Bob",
          "age": 25,
          "address": {
              "street": "456 Elm St",
              "city": "Gotham"
          }
      }
  ]';
  
  INSERT INTO Users (id, name, age, street, city)
  SELECT 
      id,
      name,
      age,
      JSON_VALUE(address, '$.street') AS street,
      JSON_VALUE(address, '$.city') AS city
  FROM OPENJSON(@json)
  WITH (
      id INT,
      name NVARCHAR(50),
      age INT,
      address NVARCHAR(MAX) AS JSON
  );
  

Zachowanie blokujące

Szczegóły można znaleźć w artykule Locking Behavior w CREATE TABLE.

Performance

Dla tabeli rozproszonej hash możesz użyć CTAS, aby wybrać inną kolumnę dystrybucji, aby uzyskać lepszą wydajność dla łączenia i agregacji. Jeśli wybór innej kolumny dystrybucji nie jest Twoim celem, najlepszą wydajność CTAS uzyskasz, jeśli określisz tę samą kolumnę dystrybucji, ponieważ pozwoli to uniknąć redystrybucji wierszy.

Jeśli używasz CTAS do tworzenia tabeli i wydajność nie ma znaczenia, możesz to określić ROUND_ROBIN , aby uniknąć wyboru kolumny dystrybucji.

Aby uniknąć ruchu danych w kolejnych zapytaniach, możesz określić REPLICATE , kosztem zwiększonej pamięci na ładowanie pełnej kopii tabeli na każdym węźle Compute.

Przykłady kopiowania tabeli

A. Użyj CTAS, aby skopiować tabelę

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

Być może jednym z najczęstszych zastosowań CTAS jest stworzenie kopii tabeli, aby móc zmienić DDL. Jeśli na przykład pierwotnie utworzyłeś tabelę jako i ROUND_ROBIN teraz chcesz zmienić ją na tabelę rozłożoną w kolumnie, CTAS tak właśnie zmienisz kolumnę dystrybucji. CTAS Można także używać do zmiany partycji, indeksowania lub typów kolumn.

Załóżmy, że stworzyłeś tę tabelę, określając HEAP i używając domyślnego typu rozkładu 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 columnstore, aby móc wykorzystać wydajność klastrowanych tabel columnstore. Chcesz też rozłożyć tę tabelę na , ProductKey ponieważ spodziewasz się łączenia na tej kolumnie i chcesz uniknąć przenoszenia danych podczas łączenia na ProductKey. Na koniec warto też dodać partycjonowanie OrderDateKey , aby móc szybko usuwać stare dane, usuwając stare partycje. Oto oświadczenie CTAS, które skopiowałoby twoją 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ć nazwy swoich stołów, aby zamienić je w nową, a potem usunąć starą.

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

DROP TABLE FactInternetSales_old;

Przykłady opcji kolumnowych

B. Użyj CTAS do zmiany atrybutów kolumn

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

Ten przykład wykorzystuje CTAS do zmiany typów danych, nieważności i sortowania dla kilku kolumn w tabeli DimCustomer2 .

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

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

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

Na koniec możesz użyć RENAME (Transact-SQL ), aby zmienić nazwy tabel. To sprawia, że DimCustomer2 staje się nową tabelą.

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

DROP TABLE DimCustomer2_old;

Przykłady rozkładu tabel

C. Użyj CTAS, aby zmienić metodę dystrybucji dla tabeli

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

Ten prosty przykład pokazuje, jak zmienić metodę dystrybucji dla tabeli. Aby pokazać, jak to zrobić, zmienia się tabelę rozmieszczoną z hashem na system round-robin, a następnie tablicę round-robin z powrotem na hash distributed (rozproszony). Ostateczna tabela odpowiada oryginalnej tabeli.

W większości przypadków nie trzeba zmieniać tabeli rozproszonej po hashu na tabelę round-robin. Częściej może być konieczne zmienienie tabeli round-robin na tabelę rozproszoną hash. Na przykład możesz początkowo załadować nową tabelę w systemie round-robin, a następnie przenieść ją do tabeli rozproszonej przez hash, aby uzyskać lepszą wydajność wyłączeń.

Ten przykład wykorzystuje bazę danych przykładów AdventureWorksDW. Aby załadować wersję Azure Synapse Analytics, zobacz Quickstart: Create and query a dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics using the 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 przywróć ją do tabeli rozproszonej hash.

-- 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. Użyj CTAS, aby przekształcić tabelę w replikowaną

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

Ten przykład dotyczy konwersji tabel w systemie round-robin lub hash-distributed do replikowanej tabeli. Ten konkretny przykład idzie o krok dalej w poprzedniej metodzie zmiany typu rozkładu. Ponieważ DimSalesTerritory jest to wymiar i prawdopodobnie mniejsza tabela, możesz zdecydować się na ponowne utworzenie tabeli w postaci replikacji, 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. Użyj CTAS, aby stworzyć tabelę z mniejszą liczbą kolumn

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

Poniższy przykład tworzy tabelę rozproszoną w systemie round-robin o nazwie myTable (c, ln). Nowa tabela ma tylko dwie kolumny. Używa aliasów kolumn w instrukcji SELECT do określania nazw kolumn.

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

Przykłady podpowiedzi zapytań

F. Użyj podpowiedzi zapytania z CREATE TABLE AS SELECT (CTAS)

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

To zapytanie pokazuje podstawową składnię użycia hint query join wraz z instrukcją CTAS. Po przesłaniu zapytania Azure Synapse Analytics stosuje strategię hash join, generując plan zapytań dla każdej poszczególnej dystrybucji. Więcej informacji na temat wskazówki dotyczącej hash join query znajdziesz w OPTION Clause (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. Użyj CTAS do importu danych z pamięci Azure Blob

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

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

Poniższy przykład definiuje zewnętrzną tabelę danych w kontu Azure Blob Storage. Następnie używa CREATE TABLE AS SELECT, aby wybrać z tabeli zewnętrznej. Importuje to dane z plików tekstowych Azure Blob Storage i przechowuje je 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. Użyj CTAS do importu danych Hadoop z zewnętrznej tabeli

Dotyczy do: Systemu Platformy Analitycznej (PDW)

Aby zaimportować dane z tabeli zewnętrznej, wystarczy użyć CREATE TABLE AS SELECT, aby wybrać z tabeli zewnętrznej. Składnia do wyboru danych z zewnętrznej tabeli do Analytics Platform System (PDW) jest taka sama jak składnia do wyboru danych z regularnej tabeli.

Poniższy przykład definiuje zewnętrzną tabelę na klastrze Hadoop. Następnie używa CREATE TABLE AS SELECT, aby wybrać z tabeli zewnętrznej. Importuje ona dane z plików Hadoop z tekstowym wydzieleniem i przechowuje je 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 wykorzystania CTAS w zastępowaniu kodu SQL Server

Użyj CTAS, aby obejść niektóre nieobsługiwane funkcje. Poza możliwością uruchamiania kodu w hurtowni danych, przepisanie istniejącego kodu na CTAS zwykle poprawia wydajność. Jest to efekt w pełni równoległej konstrukcji.

Uwaga / Notatka

Spróbuj najpierw pomyśleć o "CTAS". Jeśli uważasz, że możesz rozwiązać problem używając, CTAS to zazwyczaj jest to najlepszy sposób – nawet jeśli w efekcie zapisujesz więcej danych.

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

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

Kod SQL Server zazwyczaj używa SELECT... INTO do wypełnienia tabeli wynikami instrukcji SELECT. To jest przykład SQL Server SELECT.. Oświadczenie IN.

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

Ta składnia nie jest obsługiwana w Azure Synapse Analytics ani Parallel Data Warehouse. Ten przykład pokazuje, jak przepisać poprzedni SELECT.. Oświadczenie INTO jako oświadczenie CTAS. Możesz wybrać dowolną z opcji DYSTRYBUCJI opisanych w składni CTAS. Ten przykład wykorzystuje metodę rozkładu ROUND_ROBIN.

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

J. Użyj CTAS do uproszczenia instrukcji scalania

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

Instrukcje merge można częściowo zastąpić za pomocą CTAS. Możesz skonsolidować i INSERTUPDATE the w jedno zdanie. Wszelkie usunięte rekordy musiałyby zostać zamknięte w drugim wyświetleniu.

Przykład oto UPSERT następujący:

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. Wyraźnie podaj typ danych i nieważność wyników

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

Podczas migracji kodu SQL Server do Azure Synapse Analytics możesz natknąć się na taki wzorzec 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żesz pomyśleć, że powinieneś przenieść ten kod do CTAS i masz rację. Jednak jest tu ukryty problem.

Poniższy kod NIE daje tego samego rezultatu:

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
;

Zauważ, że kolumna "result" przenosi dalej wartości typu danych i wartości nieważności wyrażenia. Może to prowadzić do subtelnych różnic w wartościach, jeśli nie będziesz ostrożny.

Spróbuj poniższego przykładu:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Wartość zapisana dla wyniku jest inna. W miarę użycia wartości trwałej w kolumnie wyników w innych wyrażeniach błąd staje się jeszcze większy.

Jest to ważne przy migracjach danych. Mimo że drugie pytanie jest prawdopodobnie trafniejsze, to jednak jest problem. Dane różniłyby się od systemu źródłowego, co rodzi pytania o integralność migracji. To jeden z tych rzadkich przypadków, gdy "zła" odpowiedź jest faktycznie tą właściwą!

Powodem, dla którego widzimy tę rozbieżność między tymi dwoma wynikami, jest ukryte obsadzanie 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 typów, ponieważ wyrażenie definiuje typ danych kolumny. Zwróć też uwagę, że kolumna w drugim przykładzie została zdefiniowana jako kolumna NULLowalna, podczas gdy w pierwszym przykładzie tak nie było. Gdy tabela została utworzona w pierwszym przykładzie, kolumna była wyraźnie definiowana jako nulowalność. W drugim przykładzie pozostawiono to wyrażeniu, co domyślnie skutkowało definicją NULL .

Aby rozwiązać te problemy, musisz wyraźnie ustawić konwersję typu i unieważnienie w SELECT części CTAS instrukcji. Nie możesz ustawić tych właściwości w części tworzenia tabeli.

Ten przykład pokazuje, 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 przykłady:

• Można było użyć CAST lub CONVERT.
• ISNULL służy do wymuszania NULLability, a nie KOALESCE.
• ISNULL to funkcja najbardziej zewnętrzna.
• Druga część ISNULL jest stałą, 0.

Uwaga / Notatka

Aby zerowanie było poprawnie ustawione, konieczne jest użycie ISNULL nieważne, a nie COALESCE. COALESCE nie jest funkcją deterministyczną, więc wynik wyrażenia zawsze będzie możliwy do NULLowania. ISNULL jest inna. Jest deterministyczna. Dlatego gdy druga część ISNULL funkcji jest stałą lub literalem, to otrzymana wartość NIE będzie NULL.

Ta wskazówka nie służy tylko do zapewnienia integralności obliczeń. Jest to również ważne przy przełączaniu partycji tabel. Wyobraź sobie, że masz tę tabelę zdefiniowaną jako swój 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 obliczonym, nie jest częścią danych źródłowych.

Aby utworzyć swój zbiór danych podzielonych, rozważ 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 działało bez zarzutu. Problem pojawia się, gdy próbujesz wykonać zmianę partycji. Definicje tabel się nie zgadzają. Aby stworzyć definicje tabel, dopasowanie CTAS wymaga modyfikacji.

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

Widać, że spójność typów i utrzymanie właściwości nulowalności w CTAS to dobra inżynierska praktyka. Pomaga to zachować integralność obliczeń i zapewnia możliwość przełączania partycji.

L. Utworzenie uporządkowanego klastrowanego indeksu pamięci kolumn z MAXDOP 1

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

Treści powiązane

• UTWÓRZ ZEWNĘTRZNE ŹRÓDŁO DANYCH (Transact-SQL)
• UTWÓRZ ZEWNĘTRZNY FORMAT PLIKU (Transact-SQL)
• UTWÓRZ TABELĘ ZEWNĘTRZNĄ (Transact-SQL)
• UTWÓRZ ZEWNĘTRZNĄ TABELĘ JAKO SELECT (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• TABELA DROP (Transact-SQL)
• ZEWNĘTRZNY STÓŁ DROP (Transact-SQL)
• ZMIEŃ TABELĘ (Transact-SQL)
• ZMIENIJ STÓŁ ZEWNĘTRZNY (Transact-SQL)