CREATE TABLE AS SELECT (CTAS)
Artikel ini menjelaskan pernyataan BUAT TABEL SEBAGAI PILIH (CTAS) SQL di Synapse untuk mengembangkan solusi. Artikel ini juga menyediakan contoh kode.
CREATE TABLE AS SELECT
Pernyataan CREATE TABLE AS SELECT (CTAS) adalah salah satu fitur T-SQL terpenting yang tersedia. CTAS adalah operasi paralel yang membuat tabel baru berdasarkan output pernyataan PILIH. CTAS adalah cara paling sederhana dan tercepat untuk membuat dan menyisipkan data ke dalam tabel dengan satu perintah.
PILIH... KE DALAM vs. CTAS
CTAS adalah versi pernyataan PILIH...KE DALAM yang lebih dapat disesuaikan.
Berikut ini adalah contoh sederhana PILIH...KE DALAM:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
Pilih... KE DALAM tidak mengizinkan Anda mengubah metode distribusi atau jenis indeks sebagai bagian dari operasi. Anda membuat [dbo].[FactInternetSales_new] dengan menggunakan jenis distribusi default ROUND_ROBIN, dan struktur tabel default INDEKS PENYIMPANAN KOLOM TERKLUSTER.
Dengan CTAS, di sisi lain, Anda dapat menentukan distribusi data tabel serta jenis struktur tabel. Untuk mengonversi contoh sebelumnya ke CTAS:
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Catatan
Jika Anda hanya mencoba mengubah indeks dalam operasi CTAS Anda, dan tabel sumber didistribusikan hash, pertahankan kolom distribusi dan jenis data yang sama. Ini menghindari perpindahan data lintas distribusi selama operasi, yang lebih efisien.
Menggunakan CTAS untuk menyalin tabel
Mungkin salah satu penggunaan CTAS yang paling umum adalah membuat salinan tabel untuk mengubah DDL. Katakanlah Anda awalnya membuat tabel Anda sebagai ROUND_ROBIN, dan sekarang ingin mengubahnya menjadi tabel yang didistribusikan pada kolom. CTAS adalah bagaimana Anda akan mengubah kolom distribusi. Anda juga dapat menggunakan CTAS untuk mengubah jenis partisi, pengindeksan, atau kolom.
Katakanlah Anda membuat tabel ini dengan menggunakan jenis distribusi default ROUND_ROBIN, tidak menentukan kolom distribusi di CREATE TABLE.
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));
Sekarang Anda ingin membuat salinan baru dari tabel ini, dengan Clustered Columnstore Index, sehingga Anda dapat memanfaatkan kinerja tabel Penyimpanan Kolom Terkluster. Anda juga ingin mendistribusikan tabel ini di ProductKey, karena Anda mengantisipasi bergabung di kolom ini dan ingin menghindari perpindahan data selama bergabung di ProductKey. Terakhir, Anda juga ingin menambahkan partisi pada OrderDateKey, sehingga Anda dapat menghapus data lama dengan cepat dengan menghapus partisi lama. Berikut adalah pernyataan CTAS, yang 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 Anda, untuk menukar tabel baru lalu menghapus tabel lama Anda.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Gunakan CTAS untuk mengatasi fitur yang tidak didukung
Anda juga dapat menggunakan CTAS untuk mengatasi sejumlah fitur yang tidak didukung yang tercantum di bawah ini. Metode ini sering terbukti membantu, karena kode Anda tidak hanya akan sesuai, tetapi juga akan sering berjalan lebih cepat di Synapse SQL. Performa ini adalah hasil dari desainnya yang diparalelkan sepenuhnya. Skenario meliputi:
- GABUNGAN ANSI pada PEMBARUAN
- GABUNGAN ANSI pada PENGHAPUSAN
- Pernyataan PENGGABUNGAN
Tip
Cobalah untuk berpikir "CTAS terlebih dahulu." Memecahkan masalah dengan menggunakan CTAS umumnya merupakan pendekatan yang baik, bahkan jika Anda menulis lebih banyak data sebagai hasilnya.
Penggantian gabungan ANSI untuk pernyataan pembaruan
Anda mungkin menemukan bahwa Anda memiliki pembaruan yang kompleks. Pembaruan menggabungkan lebih dari dua tabel bersama-sama dengan menggunakan sintaksis gabungan ANSI untuk melakukan PEMBARUAN atau PENGHAPUSAN.
Bayangkan Anda harus memperbarui tabel ini:
CREATE TABLE [dbo].[AnnualCategorySales]
( [EnglishProductCategoryName] NVARCHAR(50) NOT NULL
, [CalendarYear] SMALLINT NOT NULL
, [TotalSalesAmount] MONEY NOT NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN
);
Kueri asli mungkin terlihat seperti contoh ini:
UPDATE acs
SET [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM [dbo].[AnnualCategorySales] AS acs
JOIN (
SELECT [EnglishProductCategoryName]
, [CalendarYear]
, SUM([SalesAmount]) AS [TotalSalesAmount]
FROM [dbo].[FactInternetSales] AS s
JOIN [dbo].[DimDate] AS d ON s.[OrderDateKey] = d.[DateKey]
JOIN [dbo].[DimProduct] AS p ON s.[ProductKey] = p.[ProductKey]
JOIN [dbo].[DimProductSubCategory] AS u ON p.[ProductSubcategoryKey] = u.[ProductSubcategoryKey]
JOIN [dbo].[DimProductCategory] AS c ON u.[ProductCategoryKey] = c.[ProductCategoryKey]
WHERE [CalendarYear] = 2004
GROUP BY
[EnglishProductCategoryName]
, [CalendarYear]
) AS fis
ON [acs].[EnglishProductCategoryName] = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear] = [fis].[CalendarYear];
Synapse SQL tidak mendukung gabungan ANSI dalam klausul FROM dari pernyataan UPDATE, jadi Anda tidak dapat menggunakan contoh sebelumnya tanpa memodifikasinya.
Anda dapat menggunakan kombinasi CTAS dan gabungan implisit untuk menggantikan contoh sebelumnya:
-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0) AS [EnglishProductCategoryName]
, ISNULL(CAST([CalendarYear] AS SMALLINT),0) AS [CalendarYear]
, ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0) AS [TotalSalesAmount]
FROM [dbo].[FactInternetSales] AS s
JOIN [dbo].[DimDate] AS d ON s.[OrderDateKey] = d.[DateKey]
JOIN [dbo].[DimProduct] AS p ON s.[ProductKey] = p.[ProductKey]
JOIN [dbo].[DimProductSubCategory] AS u ON p.[ProductSubcategoryKey] = u.[ProductSubcategoryKey]
JOIN [dbo].[DimProductCategory] AS c ON u.[ProductCategoryKey] = c.[ProductCategoryKey]
WHERE [CalendarYear] = 2004
GROUP BY [EnglishProductCategoryName]
, [CalendarYear];
-- Use an implicit join to perform the update
UPDATE AnnualCategorySales
SET AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM CTAS_acs
WHERE CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND CTAS_acs.[CalendarYear] = AnnualCategorySales.[CalendarYear] ;
--Drop the interim table
DROP TABLE CTAS_acs;
Penggantian gabungan ANSI untuk PENGGABUNGAN
Di Azure Synapse Analytics, PENGGABUNGAN (pratinjau) dengan TIDAK SESUAI DENGAN TARGET memerlukan target berupa tabel terdistribusi HASH. Pengguna dapat menggunakan GABUNGAN ANSI dengan PEMBARUAN atau PENGHAPUSAN sebagai solusi untuk mengubah data tabel target berdasarkan hasil bergabung dengan tabel lain. Berikut contohnya.
CREATE TABLE dbo.Table1
(ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA INT NOT NULL, ColB DECIMAL(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
Secara eksplisit menyatakan jenis data dan output yang dapat diubah ke null
Saat memigrasi kode, Anda mungkin menemukan 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;
Anda mungkin berpikir Anda harus memigrasikan kode ini ke CTAS, dan Anda 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. Membawa jenis data ke depan dapat menyebabkan perbedaan nilai yang tipis jika Anda tidak berhati-hati.
Cobalah contoh ini:
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
Nilai disimpan untuk hasil berbeda. Karena nilai dipertahankan di kolom hasil digunakan dalam ekspresi lain, kesalahan menjadi lebih signifikan.
Ini penting untuk migrasi data. Meskipun kueri kedua bisa dibilang lebih akurat, tetap ada masalah. Data akan berbeda dibandingkan dengan sistem sumber, dan itu mengarah pada pertanyaan tentang integritas dalam migrasi. Ini adalah salah satu kasus langka di mana jawaban "salah" sebenarnya adalah yang benar!
Alasan kita melihat perbedaan antara kedua hasil adalah karena pentransmisian jenis implisit. Dalam contoh pertama, tabel menentukan definisi kolom. Saat baris disisipkan, konversi jenis implisit terjadi. Dalam contoh kedua, tidak ada konversi jenis implisit karena ekspresi mendefinisikan jenis data kolom.
Perhatikan juga bahwa kolom dalam contoh kedua telah didefinisikan sebagai kolom dapat diubah ke NULL, sedangkan dalam contoh pertama belum. Ketika tabel dibuat dalam contoh pertama, kolom yang dapat diubah ke null secara eksplisit didefinisikan. Pada contoh kedua, ekspresi dibiarkan, dan secara default akan menghasilkan definisi NULL.
Untuk mengatasi masalah ini, Anda harus secara eksplisit mengatur konversi jenis dan dapat diubah ke null di bagian PILIH dari pernyataan CTAS. Anda tidak dapat mengatur properti ini di 'BUAT TABEL'. Contoh berikut 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-hal berikut:
- Anda dapat menggunakan CAST atau CONVERT.
- Gunakan ISNULL, bukan COALESCE, untuk memaksa kemampuan untuk diubah ke NULL. Lihat catatan berikut.
- ISNULL adalah fungsi terluar.
- Bagian kedua dari ISNULL adalah konstan, 0.
Catatan
Agar kemampuan untuk diubah ke null diatur dengan benar, sangat penting untuk menggunakan ISNULL dan bukan COALESCE. COALESCE bukan fungsi deterministik, sehingga hasil ekspresi akan selalu yang dapat diubah ke NULL. ISNULL berbeda. Ini deterministik. Oleh karena itu, ketika bagian kedua dari fungsi ISNULL adalah konstanta atau literal, nilai yang dihasilkan TIDAK AKAN NULL.
Memastikan integritas perhitungan Anda juga penting untuk pengalihan partisi tabel. Bayangkan Anda memiliki tabel ini didefinisikan sebagai tabel fakta:
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 jumlah adalah ekspresi terhitung. Ini bukan bagian dari data sumber.
Untuk membuat set data yang dipartisi, Anda mungkin ingin menggunakan kode 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 muncul ketika Anda mencoba untuk melakukan mengalihkan partisi. Definisi tabel tidak cocok. Agar definisi tabel cocok, ubah CTAS untuk menambahkan fungsi ISNULL untuk mempertahankan atribut kolom berkemampuan diubah ke null.
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');
Anda dapat melihat bahwa konsistensi jenis dan mempertahankan properti kemampuan diubah ke null pada CTAS adalah praktik terbaik teknik. Ini membantu menjaga integritas dalam perhitungan Anda, dan juga memastikan bahwa pengalihan partisi dimungkinkan.
CTAS adalah salah satu pernyataan paling penting dalam Synapse SQL. Pastikan Anda benar-benar memahaminya. Lihat dokumentasi CTAS.
Langkah berikutnya
Untuk tips pengembangan selengkapnya, buka ringkasan pengembangan.