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.
WYBRAĆ... INTO a CTAS
CTAS to bardziej dostosowywalna wersja select... INTO , instrukcja.
Poniżej przedstawiono przykład prostego select... DO:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
WYBRAĆ... FUNKCJA INTO nie zezwala na zmianę metody dystrybucji lub typu indeksu w ramach operacji. [dbo].[FactInternetSales_new] Tworzysz przy użyciu domyślnego typu dystrybucji ROUND_ROBIN i domyślnej struktury tabeli KLASTROWANEGO INDEKSU MAGAZYNU KOLUMN.
Z kolei 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 zmienić ją na tabelę rozłożoną na kolumnę. CTAS to sposób zmiany kolumny dystrybucji. Za pomocą funkcji CTAS można również zmieniać typy partycji, indeksowania lub 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ż rozproszyć tę tabelę w ProductKeyusłudze , ponieważ przewidujesz sprzężenia w tej kolumnie i chcesz uniknąć przenoszenia danych podczas sprzężeń w systemie 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ć je w nowej tabeli, a następnie usunąć starą tabelę.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
Jawnie stan typu danych i dopuszczalność wartości null danych wyjściowych
Podczas migrowania kodu może się okazać, że uruchamiasz w tym typie 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żesz pomyśleć, że należy przeprowadzić migrację tego kodu do usługi CTAS i byłoby poprawne. 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 byłyby różne w porównaniu z systemem źródłowym i prowadzi to do pytań o integralność w migracji. Jest to jeden z tych rzadkich przypadków, w których "niewłaściwa" odpowiedź jest rzeczywiście właściwa!
Przyczyną, dla którego widzimy różnicę między dwoma wynikami, jest niejawne rzutowanie 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.
Zwróć również uwagę, że kolumna w drugim przykładzie została zdefiniowana jako kolumna NULLable, natomiast w pierwszym przykładzie nie została zdefiniowana. Po utworzeniu tabeli w pierwszym przykładzie jawnie zdefiniowano wartość null kolumny. W drugim przykładzie pozostało do wyrażenia 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
Należy zwrócić uwagę na następujące kwestie:
- Można 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 poprawnie ustawić wartość null, należy użyć funkcji ISNULL, a nie FUNKCJI COALESCE. COALESCE nie jest funkcją deterministyczną, więc wynik wyrażenia zawsze będzie NULLable. ISULL jest inny. To deterministyczne. W związku z tym, gdy druga część funkcji ISULL 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, możesz 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');
Można zobaczyć, że spójność typów i utrzymywanie właściwości null w 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.