CREATE TABLE AS SELECT

Berlaku untuk:Azure Synapse Analytics AnalyticsPlatform System (PDW)

CREATE TABLE AS SELECT (CTAS) adalah salah satu fitur T-SQL terpenting yang tersedia. Ini adalah operasi yang sepenuhnya paralel yang membuat tabel baru berdasarkan output pernyataan SELECT. CTAS adalah cara paling sederhana dan tercepat untuk membuat salinan tabel.

Misalnya, gunakan CTAS untuk:

• Buat ulang tabel dengan kolom distribusi hash yang berbeda.
• Buat ulang tabel sebagai direplikasi.
• Buat indeks penyimpan kolom hanya pada beberapa kolom dalam tabel.
• Mengkueri atau mengimpor data eksternal.

Catatan

Karena CTAS menambahkan kemampuan membuat tabel, topik ini mencoba untuk tidak mengulangi topik CREATE TABLE. Sebaliknya, ini menjelaskan perbedaan antara pernyataan CTAS dan CREATE TABLE. Untuk detail CREATE TABLE, lihat pernyataan CREATE TABLE (Azure Synapse Analytics ).

• Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.
• CTAS didukung di Gudang di Microsoft Fabric.

Konvensi sintaks transact-SQL

Sintaks

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 
    }

Argumen

Untuk detailnya, lihat bagian Argumen di CREATE TABLE.

Opsi kolom

column_name [ ,...n ]
Nama kolom tidak memperbolehkan opsi kolom yang disebutkan dalam CREATE TABLE. Sebagai gantinya, Anda dapat menyediakan daftar opsional dari satu atau beberapa nama kolom untuk tabel baru. Kolom dalam tabel baru menggunakan nama yang Anda tentukan. Saat Anda menentukan nama kolom, jumlah kolom dalam daftar kolom harus cocok dengan jumlah kolom dalam hasil pemilihan. Jika Anda tidak menentukan nama kolom apa pun, tabel target baru menggunakan nama kolom dalam hasil pernyataan pilih.

Anda tidak dapat menentukan opsi kolom lain seperti jenis data, kolase, atau nullability. Masing-masing atribut ini berasal dari hasil SELECT pernyataan. Namun, Anda dapat menggunakan pernyataan SELECT untuk mengubah atribut. Misalnya, lihat Menggunakan CTAS untuk mengubah atribut kolom.

Opsi distribusi tabel

Untuk detail dan untuk memahami cara memilih kolom distribusi terbaik, lihat bagian Opsi distribusi tabel di CREATE TABLE. Untuk rekomendasi tentang distribusi mana yang akan dipilih untuk tabel berdasarkan penggunaan aktual atau kueri sampel, lihat Distribution Advisor di Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLIKASI pernyataan CTAS memerlukan opsi distribusi dan tidak memiliki nilai default. Ini berbeda dari CREATE TABLE, yang memiliki default.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Mendistribusikan baris berdasarkan nilai hash hingga delapan kolom, memungkinkan distribusi data tabel dasar yang lebih merata, mengurangi ke condong data dari waktu ke waktu dan meningkatkan performa kueri.

Catatan

• Untuk mengaktifkan fitur, ubah tingkat kompatibilitas database menjadi 50 dengan perintah ini. Untuk informasi selengkapnya tentang mengatur tingkat kompatibilitas database, lihat MENGUBAH KONFIGURASI CAKUPAN DATABASE. Misalnya: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Untuk menonaktifkan fitur distribusi multi-kolom (MCD), jalankan perintah ini untuk mengubah tingkat kompatibilitas database menjadi AUTO. Misalnya: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Tabel MCD yang ada akan tetap ada tetapi menjadi tidak dapat dibaca. Kueri melalui tabel MCD akan mengembalikan kesalahan ini: 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.
  • Untuk mendapatkan kembali akses ke tabel MCD, aktifkan fitur lagi.
  • Untuk memuat data ke dalam tabel MCD, gunakan pernyataan CTAS dan sumber data harus berupa tabel Synapse SQL.
  • CTAS pada tabel target MCD HEAP tidak didukung. Sebagai gantinya, gunakan INSERT SELECT sebagai solusi untuk memuat data ke dalam tabel MCD HEAP.
• Menggunakan SSMS untuk menghasilkan skrip untuk membuat tabel MCD saat ini didukung di luar SSMS versi 19.

Untuk detail dan untuk memahami cara memilih kolom distribusi terbaik, lihat bagian Opsi distribusi tabel di CREATE TABLE.

Untuk rekomendasi tentang distribusi terbaik yang dilakukan untuk digunakan berdasarkan beban kerja Anda, lihat Synapse SQL Distribution Advisor (Pratinjau).

Opsi partisi tabel

Pernyataan CTAS membuat tabel yang tidak dipartisi secara default, bahkan jika tabel sumber dipartisi. Untuk membuat tabel yang dipartisi dengan pernyataan CTAS, Anda harus menentukan opsi partisi.

Untuk detailnya, lihat bagian Opsi partisi tabel di CREATE TABLE.

Pernyataan SELECT

Pernyataan SELECT adalah perbedaan mendasar antara CTAS dan CREATE TABLE.

WITHcommon_table_expression

Menentukan kumpulan hasil bernama sementara, yang dikenal sebagai ekspresi tabel umum (CTE). Untuk informasi selengkapnya, lihat WITH common_table_expression (Transact-SQL).

SELECTselect_criteria

Mengisi tabel baru dengan hasil dari pernyataan SELECT. select_criteria adalah isi pernyataan SELECT yang menentukan data yang akan disalin ke tabel baru. Untuk informasi tentang pernyataan SELECT, lihat SELECT (Transact-SQL).

Petunjuk kueri

Pengguna dapat mengatur MAXDOP ke nilai bilangan bulat untuk mengontrol tingkat paralelisme maksimum. Saat MAXDOP diatur ke 1, kueri dijalankan oleh satu utas.

Izin

CTAS memerlukan SELECT izin pada objek apa pun yang dirujuk dalam select_criteria.

Untuk izin membuat tabel, lihat Izin di CREATE TABLE.

Keterangan

Untuk detailnya, lihat Keterangan Umum di CREATE TABLE.

Pembatasan dan batasan

Indeks penyimpan kolom berkluster yang diurutkan dapat dibuat pada kolom dari jenis data apa pun yang didukung di Azure Synapse Analytics kecuali untuk kolom string.

SET ROWCOUNT (Transact-SQL) tidak berpengaruh pada CTAS. Untuk mencapai perilaku serupa, gunakan TOP (Transact-SQL).

Untuk detailnya, lihat Batasan dan Pembatasan di CREATE TABLE.

Perilaku penguncian

Untuk detailnya, lihat Perilaku Penguncian di CREATE TABLE.

Performa

Untuk tabel terdistribusi hash, Anda dapat menggunakan CTAS untuk memilih kolom distribusi yang berbeda guna mencapai performa yang lebih baik untuk gabungan dan agregasi. Jika memilih kolom distribusi yang berbeda bukan tujuan Anda, Anda akan memiliki performa CTAS terbaik jika Anda menentukan kolom distribusi yang sama karena ini akan menghindari distribusi ulang baris.

Jika Anda menggunakan CTAS untuk membuat tabel dan performa bukanlah faktor, Anda dapat menentukan ROUND_ROBIN untuk menghindari harus memutuskan kolom distribusi.

Untuk menghindari pergerakan data dalam kueri berikutnya, Anda dapat menentukan REPLICATE dengan biaya peningkatan penyimpanan untuk memuat salinan lengkap tabel pada setiap simpul Komputasi.

Contoh untuk menyalin tabel

J. Menggunakan CTAS untuk menyalin tabel

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Mungkin salah satu kegunaan CTAS yang paling umum adalah membuat salinan tabel sehingga Anda dapat mengubah DDL. Jika, misalnya, Anda awalnya membuat tabel sebagai ROUND_ROBIN dan sekarang ingin mengubahnya menjadi tabel yang didistribusikan pada kolom, CTAS adalah bagaimana Anda akan mengubah kolom distribusi. CTAS juga dapat digunakan untuk mengubah jenis partisi, pengindeksan, atau kolom.

Katakanlah Anda membuat tabel ini dengan menentukan HEAP dan menggunakan jenis ROUND_ROBINdistribusi default .

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

Sekarang Anda ingin membuat salinan baru tabel ini dengan indeks penyimpan kolom berkluster sehingga Anda dapat memanfaatkan performa tabel penyimpan kolom berkluster. Anda juga ingin mendistribusikan tabel ini karena ProductKey Anda mengantisipasi gabungan pada kolom ini dan ingin menghindari pergerakan data selama gabungan pada ProductKey. Terakhir, Anda juga ingin menambahkan partisi OrderDateKey sehingga Anda dapat dengan cepat menghapus data lama dengan menghilangkan partisi lama. Berikut adalah pernyataan CTAS yang akan menyalin tabel lama Anda ke dalam tabel baru:

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;

Terakhir, Anda dapat mengganti nama tabel untuk ditukar di tabel baru Anda lalu menjatuhkan tabel lama Anda.

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

DROP TABLE FactInternetSales_old;

Contoh untuk opsi kolom

B. Menggunakan CTAS untuk mengubah atribut kolom

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh ini menggunakan CTAS untuk mengubah jenis data, nullability, dan collation untuk beberapa kolom dalam DimCustomer2 tabel.

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

Sebagai langkah terakhir, Anda dapat menggunakan RENAME (Transact-SQL) untuk mengalihkan nama tabel. Ini membuat DimCustomer2 menjadi tabel baru.

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

DROP TABLE DimCustomer2_old;

Contoh untuk distribusi tabel

C. Menggunakan CTAS untuk mengubah metode distribusi untuk tabel

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh sederhana ini menunjukkan cara mengubah metode distribusi untuk tabel. Untuk menunjukkan mekanisme cara melakukan ini, ia mengubah tabel yang didistribusikan hash menjadi round-robin dan kemudian mengubah tabel round-robin kembali ke hash yang didistribusikan. Tabel akhir cocok dengan tabel asli.

Dalam kebanyakan kasus, Anda tidak perlu mengubah tabel terdistribusi hash ke tabel round-robin. Lebih sering, Anda mungkin perlu mengubah tabel round-robin ke tabel terdistribusi hash. Misalnya, Anda mungkin awalnya memuat tabel baru sebagai round-robin dan kemudian memindahkannya ke tabel terdistribusi hash untuk mendapatkan performa gabungan yang lebih baik.

Contoh ini menggunakan database sampel AdventureWorksDW. Untuk memuat versi Azure Synapse Analytics, lihat Mulai Cepat: Membuat dan mengkueri kumpulan SQL khusus (sebelumnya SQL DW) di Azure Synapse Analytics menggunakan portal Azure.

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

Selanjutnya, ubah kembali ke tabel terdistribusi 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. Menggunakan CTAS untuk mengonversi tabel ke tabel yang direplikasi

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh ini berlaku untuk mengonversi tabel round-robin atau terdistribusi hash ke tabel yang direplikasi. Contoh khusus ini mengambil metode sebelumnya untuk mengubah jenis distribusi satu langkah lebih jauh. Karena DimSalesTerritory merupakan dimensi dan kemungkinan tabel yang lebih kecil, Anda dapat memilih untuk membuat ulang tabel sebagai direplikasi untuk menghindari pergerakan data saat bergabung ke tabel lain.

-- 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. Menggunakan CTAS untuk membuat tabel dengan lebih sedikit kolom

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Contoh berikut membuat tabel terdistribusi round-robin bernama myTable (c, ln). Tabel baru hanya memiliki dua kolom. Ini menggunakan alias kolom dalam pernyataan SELECT untuk nama kolom.

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

Contoh untuk petunjuk kueri

F. Menggunakan Petunjuk Kueri dengan CREATE TABLE AS SELECT (CTAS)

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Kueri ini memperlihatkan sintaks dasar untuk menggunakan petunjuk gabungan kueri dengan pernyataan CTAS. Setelah kueri dikirimkan, Azure Synapse Analytics menerapkan strategi gabungan hash saat menghasilkan rencana kueri untuk setiap distribusi individu. Untuk informasi selengkapnya tentang petunjuk kueri gabungan hash, lihat Klausul 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 );  

Contoh untuk tabel eksternal

G. Menggunakan CTAS untuk mengimpor data dari penyimpanan Azure Blob

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Untuk mengimpor data dari tabel eksternal, gunakan CREATE TABLE AS SELECT untuk memilih dari tabel eksternal. Sintaks untuk memilih data dari tabel eksternal ke Azure Synapse Analytics sama dengan sintaks untuk memilih data dari tabel biasa.

Contoh berikut menentukan tabel eksternal pada data di akun Azure Blob Storage. Kemudian menggunakan CREATE TABLE AS SELECT untuk memilih dari tabel eksternal. Ini mengimpor data dari file yang dibatasi teks Azure Blob Storage dan menyimpan data ke dalam tabel Azure Synapse Analytics baru.

--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. Menggunakan CTAS untuk mengimpor data Hadoop dari tabel eksternal

Berlaku untuk: Analytics Platform System (PDW)

Untuk mengimpor data dari tabel eksternal, cukup gunakan CREATE TABLE AS SELECT untuk memilih dari tabel eksternal. Sintaks untuk memilih data dari tabel eksternal ke Analytics Platform System (PDW) sama dengan sintaks untuk memilih data dari tabel biasa.

Contoh berikut mendefinisikan tabel eksternal pada kluster Hadoop. Kemudian menggunakan CREATE TABLE AS SELECT untuk memilih dari tabel eksternal. Ini mengimpor data dari file yang dibatasi teks Hadoop dan menyimpan data ke dalam tabel Analytics Platform System (PDW) baru.

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

Contoh menggunakan CTAS untuk mengganti kode SQL Server

Gunakan CTAS untuk mengatasi beberapa fitur yang tidak didukung. Selain dapat menjalankan kode Anda di gudang data, menulis ulang kode yang ada untuk menggunakan CTAS biasanya akan meningkatkan performa. Ini adalah hasil dari desainnya yang sepenuhnya paralel.

Catatan

Cobalah untuk berpikir "CTAS terlebih dahulu". Jika Anda berpikir Anda dapat menyelesaikan masalah menggunakan CTAS maka itu umumnya cara terbaik untuk mendekatinya - bahkan jika Anda menulis lebih banyak data sebagai hasilnya.

I. Gunakan CTAS alih-alih SELECT.. KE

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Kode SQL Server biasanya menggunakan SELECT.. INTO untuk mengisi tabel dengan hasil pernyataan SELECT. Ini adalah contoh SELECT SQL Server. Pernyataan INTO.

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

Sintaks ini tidak didukung di Azure Synapse Analytics dan Gudang Data Paralel. Contoh ini menunjukkan cara menulis ulang SELECT sebelumnya. Pernyataan INTO sebagai pernyataan CTAS. Anda dapat memilih salah satu opsi DISTRIBUSI yang dijelaskan dalam sintaks CTAS. Contoh ini menggunakan metode distribusi ROUND_ROBIN.

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

j. Menggunakan CTAS untuk menyederhanakan pernyataan penggabungan

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Pernyataan penggabungan dapat diganti, setidaknya sebagian, dengan menggunakan CTAS. Anda dapat mengonsolidasikan INSERT dan UPDATE ke dalam satu pernyataan. Setiap rekaman yang dihapus perlu ditutup dalam pernyataan kedua.

Contoh berikut 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. Secara eksplisit menyatakan jenis data dan output yang dapat diubah ke null

Berlaku untuk: Azure Synapse Analytics and Analytics Platform System (PDW)

Saat memigrasikan kode SQL Server ke Azure Synapse Analytics, Anda mungkin menemukan Anda berjalan di seluruh jenis pola pengodean ini:

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
;

Secara institustif Anda mungkin berpikir Anda harus memigrasikan kode ini ke CTAS dan Anda akan benar. Namun, ada masalah tersembunyi di sini.

Kode berikut TIDAK menghasilkan hasil yang sama:

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
;

Perhatikan bahwa kolom "hasil" meneruskan jenis data dan nilai yang dapat diubah ke null dari ekspresi. Hal ini dapat menyebabkan varians nilai yang halang jika Anda tidak berhati-hati.

Coba yang berikut ini sebagai contoh:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Nilai disimpan untuk hasil berbeda. Karena nilai yang bertahan dalam kolom hasil digunakan dalam ekspresi lain, kesalahan menjadi lebih signifikan.

Ini penting untuk migrasi data. Meskipun kueri kedua bisa dibilang lebih akurat ada masalah. Data akan berbeda dibandingkan dengan sistem sumber dan yang mengarah pada pertanyaan integritas dalam migrasi. Ini adalah salah satu kasus langka di mana jawaban "salah" sebenarnya adalah yang benar!

Alasan kita melihat perbedaan ini antara kedua hasil turun ke pengecoran jenis implisit. Dalam contoh pertama, tabel menentukan definisi kolom. Ketika baris disisipkan, konversi jenis implisit terjadi. Dalam contoh kedua, tidak ada konversi jenis implisit karena ekspresi menentukan jenis data kolom. Perhatikan juga bahwa kolom dalam contoh kedua telah didefinisikan sebagai kolom NULLable sedangkan dalam contoh pertama belum. Ketika tabel dibuat dalam contoh pertama nullability kolom ditentukan secara eksplisit. Dalam contoh kedua, itu dibiarkan ke ekspresi dan secara default ini akan menghasilkan NULL definisi.

Untuk mengatasi masalah ini, Anda harus secara eksplisit mengatur konversi jenis dan nullability di SELECT bagian CTAS pernyataan. Anda tidak dapat mengatur properti ini di bagian buat tabel.

Contoh ini menunjukkan cara memperbaiki kode:

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

Perhatikan hal berikut dalam contoh:

• CAST atau CONVERT bisa saja digunakan.
• ISNULL digunakan untuk memaksa NULLability bukan COALESCE.
• ISNULL adalah fungsi terluar.
• Bagian kedua dari ISNULL adalah konstanta, 0.

Catatan

Agar nullability diatur dengan benar, sangat penting untuk digunakan ISNULL dan bukan COALESCE. COALESCE bukan fungsi deterministik dan sehingga hasil ekspresi akan selalu dapat DIUBAH. ISNULL berbeda. Ini deterministik. Oleh karena itu ketika bagian kedua dari ISNULL fungsi adalah konstanta atau harfiah maka nilai yang dihasilkan akan MENJADI NOT NULL.

Tips ini tidak hanya berguna untuk memastikan integritas perhitungan Anda. Penting juga untuk pengalihan partisi tabel. Bayangkan Anda memiliki tabel ini yang didefinisikan sebagai fakta Anda:

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

Namun, bidang nilai adalah ekspresi terhitung yang bukan bagian dari data sumber.

Untuk membuat himpunan data yang dipartisi, pertimbangkan contoh berikut:

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

Kueri akan berjalan dengan baik. Masalahnya datang ketika Anda mencoba melakukan sakelar partisi. Definisi tabel tidak cocok. Untuk membuat definisi tabel, cocokkan CTAS perlu dimodifikasi.

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

Oleh karena itu, Anda dapat melihat bahwa jenis konsistensi dan mempertahankan properti nullability pada CTAS adalah praktik terbaik teknik yang baik. Ini membantu menjaga integritas dalam perhitungan Anda dan juga memastikan bahwa pengalihan partisi dimungkinkan.

L. Membuat indeks penyimpan kolom berkluster yang diurutkan dengan MAXDOP 1

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

Langkah berikutnya

• BUAT SUMBER DATA EKSTERNAL (TRANSACT-SQL)
• BUAT FORMAT FILE EKSTERNAL (TRANSACT-SQL)
• BUAT TABEL EKSTERNAL (Transact-SQL)
• BUAT TABEL EKSTERNAL SEBAGAI PILIH (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• DROP EXTERNAL TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• UBAH TABEL EKSTERNAL (Transact-SQL)