SELECT OLARAK TABLO OLUŞTURMA (CTAS)
Bu makalede, çözüm geliştirmek için ayrılmış SQL havuzundaki (eski adı SQL DW) CREATE TABLE AS SELECT (CTAS) T-SQL deyimi açıklanmaktadır. Makalede kod örnekleri de sağlanır.
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT (CTAS) deyimi, kullanılabilir en önemli T-SQL özelliklerinden biridir. CTAS, SELECT deyiminin çıktısını temel alan yeni bir tablo oluşturan paralel bir işlemdir. CTAS, tek bir komutla tabloya veri oluşturmanın ve eklemenin en basit ve en hızlı yoludur.
SEÇİN... INTO ve CTAS karşılaştırması
CTAS, SELECT'in daha özelleştirilebilir bir sürümüdür ... INTO deyimi.
Aşağıda basit bir SELECT örneği verilmiştir... INTO:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SEÇİN... INTO, işlemin bir parçası olarak dağıtım yöntemini veya dizin türünü değiştirmenize izin vermez. ROUND_ROBIN varsayılan dağıtım türünü ve CLUSTERED COLUMNSTORE INDEX'in varsayılan tablo yapısını kullanarak oluşturursunuz [dbo].[FactInternetSales_new] .
Öte yandan CTAS ile hem tablo verilerinin dağılımını hem de tablo yapısı türünü belirtebilirsiniz. Önceki örneği CTAS'ye dönüştürmek için:
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Not
Yalnızca CTAS işleminizdeki dizini değiştirmeye çalışıyorsanız ve kaynak tablo karma dağıtılmışsa, aynı dağıtım sütununu ve veri türünü koruyun. Bu, işlem sırasında çapraz dağıtım verisi hareketini önler ve bu daha verimlidir.
Tabloyu kopyalamak için CTAS kullanma
CTAS'nin en yaygın kullanımlarından biri, DDL'yi değiştirmek için bir tablonun kopyasını oluşturmak olabilir. Başlangıçta tablonuzu olarak ROUND_ROBINoluşturduğunuzu ve şimdi bunu sütuna dağıtılmış bir tabloyla değiştirmek istediğinizi varsayalım. CTAS, dağıtım sütununu nasıl değiştirdiğinizdir. Bölümleme, dizin oluşturma veya sütun türlerini değiştirmek için de CTAS kullanabilirsiniz.
Bu tabloyu HEAP belirterek ve varsayılan dağıtım türünü kullanarak oluşturduğunuzu ROUND_ROBINvarsayalım.
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
);
Şimdi kümelenmiş Columnstore tablolarının performansından yararlanabilmek için ile bu tablonun yeni bir Clustered Columnstore Indexkopyasını oluşturmak istiyorsunuz. Ayrıca, bu sütunda ProductKeybirleştirmeler olmasını ve üzerinde birleştirmeler sırasında veri taşımasını önlemek istediğiniz için bu tabloyu üzerinde ProductKeydağıtmak istiyorsunuz. Son olarak, eski bölümleri bırakarak eski verileri hızla silebilmeniz için bölümüne OrderDateKeybölümleme de eklemek istiyorsunuz. Eski tablonuzu yeni bir tabloya kopyalayan CTAS deyimi aşağıdadır.
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;
Son olarak, yeni tablonuzda değiştirmek için tablolarınızı yeniden adlandırabilir ve ardından eski tablonuzu bırakabilirsiniz.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Çıktının veri türünü ve boş değer atanabilirliğini açıkça belirtin
Kodu geçirirken, bu tür bir kodlama düzeninde çalıştırdığınızı fark edebilirsiniz:
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;
Bu kodu CTAS'ye geçirmeniz gerektiğini düşünebilirsiniz ve doğru olursunuz. Ancak burada gizli bir sorun var.
Aşağıdaki kod aynı sonucu vermez:
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;
"result" sütununun ifadenin veri türünü ve null atanabilirlik değerlerini ilettiğine dikkat edin. Veri türünü ileri taşımak, dikkatli değilseniz değerlerde küçük farklılıklara yol açabilir.
Şu örneği deneyin:
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
Sonuç için depolanan değer farklıdır. Sonuç sütunundaki kalıcı değer diğer ifadelerde kullanıldığından hata daha da önemli hale gelir.
Bu, veri geçişleri için önemlidir. İkinci sorgu muhtemelen daha doğru olsa da bir sorun vardır. Veriler kaynak sistemle karşılaştırıldığında farklı olabilir ve bu da geçişte bütünlük sorularına yol açar. Bu, "yanlış" yanıtın aslında doğru olduğu nadir durumlardan biridir!
İki sonuç arasında ayrım görmenin nedeni örtük tür atamasıdır. İlk örnekte, tablo sütun tanımını tanımlar. Satır eklendiğinde örtük tür dönüştürmesi gerçekleşir. İkinci örnekte, ifade sütunun veri türünü tanımladığı için örtük tür dönüştürmesi yoktur.
İkinci örnekteki sütunun NULLable sütunu olarak tanımlandığına, ancak ilk örnekte tanımlanmadığını da görebilirsiniz. tablo ilk örnekte oluşturulduğunda, sütun null atanabilirliği açıkça tanımlanmıştır. İkinci örnekte, ifadeye bırakılmıştı ve varsayılan olarak NULL tanımına neden olacaktı.
Bu sorunları çözmek için CTAS deyiminin SELECT bölümünde tür dönüştürmesini ve null atanabilirliği açıkça ayarlamanız gerekir. Bu özellikleri 'CREATE TABLE' içinde ayarlayamazsınız. Aşağıdaki örnekte kodun nasıl düzeltileceğini gösterilmektedir:
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
Şunlara dikkat edin:
- CAST veya CONVERT kullanabilirsiniz.
- NULLability'yi zorlamak için COALESCE değil ISNULL kullanın. Aşağıdaki nota bakın.
- ISNULL en dıştaki işlevdir.
- ISNULL'ın ikinci bölümü 0 sabitidir.
Not
Null atanabilirliğin doğru ayarlanması için COALESCE yerine ISNULL kullanılması çok önemlidir. COALESCE belirleyici bir işlev değildir ve bu nedenle ifadenin sonucu her zaman NULLable olacaktır. ISNULL farklıdır. Bu belirleneci. Bu nedenle, ISNULL işlevinin ikinci bölümü sabit veya değişmez değer olduğunda, sonuçta elde edilen değer NOT NULL olur.
Tablo bölümü değiştirme için hesaplamalarınızın bütünlüğünün sağlanması da önemlidir. Bu tablonun olgu tablosu olarak tanımlandığını düşünün:
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
)
)
);
Ancak, tutar alanı hesaplanan bir ifadedir. Bu, kaynak verilerin bir parçası değildir.
Bölümlenmiş veri kümenizi oluşturmak için aşağıdaki kodu kullanmak isteyebilirsiniz:
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');
Sorgu mükemmel bir şekilde çalışır. Sorun, bölüm anahtarını gerçekleştirmeye çalıştığınızda ortaya çıkar. Tablo tanımları eşleşmiyor. Tablo tanımlarının eşleşmesini sağlamak için CTAS'yi değiştirerek sütunun null atanabilirlik özniteliğini koruyacak bir ISNULL işlev ekleyin.
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');
CTAS'de tür tutarlılığının ve null atanabilirlik özelliklerinin korunmasının en iyi mühendislik uygulaması olduğunu görebilirsiniz. Hesaplamalarınızda bütünlüğü korumaya yardımcı olur ve bölüm değiştirmenin mümkün olmasını sağlar.
CTAS, Synapse SQL'deki en önemli deyimlerden biridir. Bunu iyice anladığınızdan emin olun. CTAS belgelerine bakın.
Sonraki adımlar
Daha fazla geliştirme ipucu için bkz. geliştirmeye genel bakış.