SELECT OLARAK TABLO OLUŞTURMA (CTAS)

Makale
06/14/2024

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ÇMEK... 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. İÇİNE:

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

SEÇMEK... 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 İNDİS'in varsayılan tablo yapısını kullanarak oluşturursunuz [dbo].[FactInternetSales_new] .

Öte yandan CTAS ile hem tablo verilerinin dağıtı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şleminizde 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 yararlanmak için bu tablonun yeni bir Clustered Columnstore Indexkopyasını ile 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 tablonuzu 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;

Veri türünü ve çıkışın null atanabilirliğini açıkça belirtme

Kodu geçirirken bu tür bir kodlama düzeninde çalıştığı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'a 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.

CTAS sonuçlarının ekran görüntüsü

Bu, veri geçişleri için önemlidir. İkinci sorgu muhtemelen daha doğru olsa da bir sorun vardır. Veriler kaynak sisteme göre 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. İlk örnekte tablo oluşturulduğunda sütun null atanabilirliği açıkça tanımlanmıştır. İkinci örnekte ifadeye bırakıldı ve varsayılan olarak NULL tanımına neden oldu.

Bu sorunları çözmek için, CTAS deyiminin SELECT bölümünde tür dönüştürmeyi 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

Aşağıdakileri dikkate alın:

CAST veya CONVERT kullanabilirsiniz.
NULLability'yi zorlamak için COALESCE yerine 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 olur. ISNULL farklıdır. Bu belirleyici bir şey. Bu nedenle, ISNULL işlevinin ikinci bölümü sabit veya değişmez değer olduğunda, sonuçta elde edilen değer NULL DEĞİl olur.

Hesaplamalarınızın bütünlüğünü sağlamak, tablo bölümü değiştirme için de ö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. 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 ayrıca 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ış.

Aracılığıyla paylaş