CREATE TABLE AS SELECT (CTAS)
W tym artykule wyjaśniono instrukcję T-SQL CREATE TABLE AS SELECT (CTAS) w dedykowanej puli SQL (dawniej SQL DW) na potrzeby tworzenia rozwiązań. Artykuł zawiera również przykłady kodu.
CREATE TABLE AS SELECT
Instrukcja CREATE TABLE AS SELECT (CTAS) jest jedną z najważniejszych dostępnych funkcji języka T-SQL. CTAS to operacja równoległa, która tworzy nową tabelę na podstawie danych wyjściowych instrukcji SELECT. CTAS to najprostszy i najszybszy sposób tworzenia i wstawiania danych do tabeli za pomocą jednego polecenia.
WYBIERZ... INTO a CTAS
CTAS to bardziej dostosowywalna wersja funkcji SELECT... INSTRUKCJA INTO .
Poniżej przedstawiono przykład prostej funkcji SELECT... DO:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
WYBIERZ... FUNKCJA INTO nie umożliwia zmiany metody dystrybucji lub typu indeksu w ramach operacji. [dbo].[FactInternetSales_new] Tworzysz przy użyciu domyślnego typu dystrybucji ROUND_ROBIN oraz domyślnej struktury tabeli KLASTROWANEGO INDEKSU MAGAZYNU KOLUMN.
Z drugiej strony za pomocą funkcji CTAS można określić zarówno rozkład danych tabeli, jak i typ struktury tabeli. Aby przekonwertować poprzedni przykład na CTAS:
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Uwaga
Jeśli próbujesz zmienić indeks tylko w operacji CTAS, a tabela źródłowa jest rozproszona, zachowaj tę samą kolumnę dystrybucji i typ danych. Pozwala to uniknąć przenoszenia danych między dystrybucjami podczas operacji, co jest bardziej wydajne.
Kopiowanie tabeli za pomocą funkcji CTAS
Być może jednym z najczęstszych zastosowań CTAS jest utworzenie kopii tabeli w celu zmiany języka DDL. Załóżmy, że pierwotnie utworzono tabelę jako ROUND_ROBIN, a teraz chcesz ją zmienić na tabelę dystrybuowaną w kolumnie. CTAS to sposób zmiany kolumny dystrybucji. Można również użyć CTAS, aby zmienić partycjonowanie, indeksowanie lub typy kolumn.
Załóżmy, że ta tabela została utworzona przez określenie protokołu HEAP i użycie domyślnego typu dystrybucji .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 elementem Clustered Columnstore Index, aby móc korzystać z wydajności tabel klastrowanego magazynu kolumn. Chcesz również rozłożyć tę tabelę na ProductKeyelement , ponieważ przewidujesz sprzężenia w tej kolumnie i chcesz uniknąć przenoszenia danych podczas sprzężeń na .ProductKey Na koniec chcesz również dodać partycjonowanie w systemie OrderDateKey, aby można było szybko usunąć stare dane, upuszczając stare partycje. Oto instrukcja CTAS, która kopiuje 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 tabel, aby zamienić nową tabelę, a następnie usunąć starą tabelę.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Jawny stan typu danych i dopuszczalność wartości null danych wyjściowych
Podczas migrowania kodu można znaleźć przebieg w ramach tego typu wzorca 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;
Można pomyśleć, że należy zmigrować ten kod do CTAS i być poprawny. Jednak w tym miejscu występuje ukryty problem.
Poniższy kod nie daje tego samego wyniku:
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;
Zwróć uwagę, że kolumna "result" przekazuje wartości typu danych i wartości null wyrażenia. Przenoszenie typu danych do przodu może prowadzić do subtelnych wariancji w wartościach, jeśli nie jesteś ostrożny.
Wypróbuj ten przykład:
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
Wartość przechowywana dla wyniku jest inna. Ponieważ utrwalone wartości w kolumnie wynikowej są używane w innych wyrażeniach, błąd staje się jeszcze bardziej znaczący.
Jest to ważne w przypadku migracji danych. Mimo że drugie zapytanie jest prawdopodobnie dokładniejsze, występuje problem. Dane będą się różnić w porównaniu z systemem źródłowym, co prowadzi do pytań o integralność migracji. Jest to jeden z tych rzadkich przypadków, w których "niewłaściwa" odpowiedź jest w rzeczywistości właściwa!
Przyczyna, dla którego widzimy rozbieżność między dwoma wynikami, wynika z niejawnego rzutowania 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 typu, ponieważ wyrażenie definiuje typ danych kolumny.
Zauważ również, że kolumna w drugim przykładzie została zdefiniowana jako kolumna NULLable, natomiast w pierwszym przykładzie nie została zdefiniowana. Podczas tworzenia tabeli w pierwszym przykładzie jawnie zdefiniowano wartość null kolumny. W drugim przykładzie pozostawiono to wyrażenie i domyślnie spowodowałoby to zdefiniowanie wartości NULL.
Aby rozwiązać te problemy, należy jawnie ustawić konwersję typu i wartość null w części SELECT instrukcji CTAS. Nie można ustawić tych właściwości w obszarze "CREATE TABLE". W poniższym przykładzie pokazano, 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
. Weź pod uwagę następujące kwestie:
- Możesz użyć funkcji RZUTUJ lub KONWERTUJ.
- Użyj funkcji ISNULL, a nie COALESCE, aby wymusić NULLability. Zobacz następującą notatkę.
- ISULL jest najbardziej zewnętrzną funkcją.
- Druga część ISULL jest stałą, 0.
Uwaga
Aby można było poprawnie ustawić wartość null, należy użyć funkcji ISNULL, a nie FUNKCJI COALESCE. Funkcja COALESCE nie jest funkcją deterministyczną, więc wynik wyrażenia będzie zawsze możliwy do utworzenia wartości NULL. ISULL jest inny. Jest deterministyczny. W związku z tym, gdy druga część funkcji ISNULL jest stałą lub literałem, wynikowa wartość nie będzie równa NULL.
Zapewnienie integralności obliczeń jest również ważne w przypadku przełączania partycji tabeli. Załóżmy, że ta tabela jest zdefiniowana jako tabela faktów:
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 amount jest wyrażeniem obliczeniowym. Nie jest to część danych źródłowych.
Aby utworzyć partycjonowany zestaw danych, warto użyć następującego kodu:
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 będzie działać doskonale. Problem występuje podczas próby przełączenia partycji. Definicje tabeli nie są zgodne. Aby dopasować definicje tabeli, zmodyfikuj funkcję CTAS, aby ISNULL zachować atrybut nullability kolumny.
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 utrzymywanie właściwości null w obiekcie CTAS jest najlepszym rozwiązaniem inżynieryjnym. Pomaga zachować integralność obliczeń, a także gwarantuje, że przełączanie partycji jest możliwe.
CTAS jest jedną z najważniejszych instrukcji w usłudze Synapse SQL. Upewnij się, że dokładnie to rozumiesz. Zapoznaj się z dokumentacją CTAS.
Następne kroki
Aby uzyskać więcej wskazówek dotyczących programowania, zobacz omówienie programowania.