CREATE TABLE AS SELECT (CTAS)
В этой статье описывается применение инструкции T-SQL CREATE TABLE AS SELECT (CTAS) в выделенном пуле SQL (ранее называлось хранилищем данных SQL) для разработки решений. Здесь также приведены примеры кодов.
CREATE TABLE AS SELECT
Инструкция CREATE TABLE AS SELECT (CTAS) — одна из самых важных функций T-SQL. CTAS — это распараллеленная операция, которая создает новую таблицу на основе выходных данных инструкции SELECT. Ее применение — это самый простой и быстрый способ создания и вставки данных в таблицу с помощью одной команды.
Сравнение SELECT...INTO и CTAS
CTAS — это версия инструкции SELECT...INTO с более широкими возможностями настройки.
Ниже показан пример простой инструкции SELECT...INTO.
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECT...INTO не позволяет изменять метод распределения или тип индекса как часть операции. Для создания [dbo].[FactInternetSales_new] используются стандартные значения для типа распределения ROUND_ROBIN и табличной структуры CLUSTERED COLUMNSTORE INDEX.
С другой стороны, с CTAS можно указать как распределение данных таблицы, так и тип структуры таблицы. Для преобразования предыдущего примера в CTAS выполните следующие действия.
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Примечание
Если вы пытаетесь просто изменить индекс в операции CTAS, а для исходной таблицы используется хэш-распределение, сохраните тот же столбец распределения и тип данных. Это позволит избежать перемещения данных для перекрестного распределения во время операции, что повысит ее эффективность.
Использование инструкции CTAS для копирования таблицы
Одно из наиболее частых применений CTAS — создание копии таблицы для изменения DDL. Предположим, например, что изначально вы создали таблицу как ROUND_ROBIN и теперь хотите превратить ее в таблицу с распределением по столбцу. CTAS определяет, как будет изменен столбец распределения. CTAS также может использоваться для изменения секционирования, индексирования и типов столбцов.
Предположим, что вы создали эту таблицу с типом HEAP и стандартным типом распределения 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
);
Теперь вы хотите создать новую копию этой таблицы с Clustered Columnstore Index, чтобы получить преимущества производительности таблиц с кластеризованными индексами Columnstore. Вы также хотите распределить эту таблицу по столбцу ProductKey, так как к этому столбцу будет применяться операция соединения и вы хотите избежать перемещения данных при соединении по столбцу ProductKey. Наконец, вы также хотите добавить секционирование по столбцу OrderDateKey, чтобы быстро удалять старые данные путем удаления старых секций. Ниже приведена инструкция CTAS, которая скопирует старую таблицу в новую.
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;
Наконец, вы можете переименовать таблицы, чтобы поместить старую таблицу на место новой и удалить старую.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
явно указывайте тип данных и допустимость нулевого результата.
При переносе кода вы можете встретить следующую структуру кода:
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;
Возможно, вы решите, такой код следует перенести в CTAS, и будете правы. Однако в этом подходе кроется проблема.
Следующий код не позволяет получить тот же самый результат:
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" переносит тип данных и допустимость значений NULL для выражения. Если проявить осторожность, перенос типа данных может привести к незначительным расхождениям в значениях.
Ознакомьтесь с этим примером.
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
Значение, сохраненное для результата, отличается. Так как сохраненное значение в столбце результатов используется в других выражениях, ошибка становится еще более значительной.
Это важно для переноса данных. Несмотря на то, что второй запрос является более точным, здесь есть проблема. Данные будут отличаться по сравнению с исходной системой, и это приведет к вопросам целостности при миграции. Это один из тех редких случаев, когда "неправильный" ответ на самом деле является правильным!
Причина несоответствия между двумя результатами заключается в неявном приведении типов. В первом примере в таблице приводится определение столбца. При вставке строки происходит неявное преобразование типов. Во втором примере отсутствует неявное преобразование типов, так как выражение определяет тип данных столбца.
Обратите внимание, что во втором примере в столбце допускаются значения NULL, а в первом примере — нет. При создании таблицы в первом примере была явно указана допустимость значений NULL для столбца. Во втором примере допустимость значений NULL определялась с помощью выражения, и по умолчанию это привело бы к определению, равному NULL.
Для устранения этих проблем необходимо явно задать преобразование типа данных и допустимость значений NULL в части SELECT инструкции CTAS. Эти свойства нельзя задать в инструкции CREATE TABLE. В следующем примере показано, как исправить этот код.
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
Следует отметить следующее.
- Можно использовать CAST или CONVERT.
- Чтобы принудительно реализовать допустимость значений NULL, используйте ISNULL, а не COALESCE. Ознакомьтесь со следующим примечанием.
- ISNULL — это внешняя функция.
- Вторая часть функции ISNULL является константой, 0.
Примечание
Для правильного задания допустимости значений NULL необходимо использовать ISNULL, а не COALESCE. COALESCE не является детерминированной функцией, поэтому выражение всегда будет допускать значения NULL. ISNULL работает иначе. Эта функция детерминирована. Поэтому если вторая часть функции ISNULL является константой или литералом, итоговым значением будет NOT NULL.
Проверка целостности вычислений также важна для переключения секций таблиц. Представьте, что в качестве таблицы фактов определена следующая таблица.
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
)
)
);
Однако поле суммы является вычисляемым выражением. Он не является частью исходных данных.
Для создания секционированного набора данных может потребоваться использовать следующий код.
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');
Запрос выполняется без ошибок. Проблема возникает при попытке выполнить переключение секций. Определения таблиц не совпадают. Чтобы определения таблиц совпадали, измените CTAS, добавив функцию ISNULL, чтобы сохранить атрибут допустимости значений 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');
При проектировании рекомендуется использовать инструкцию CTAS для обеспечения согласованности типов и поддержания допустимости значений NULL. Это помогает поддерживать целостность вычислений и обеспечивает возможность переключения секций.
CTAS — это одна из самых важных инструкций в Synapse SQL. Научитесь его применять. Дополнительные сведения см. в документации CTAS.
Дальнейшие действия
Дополнительные советы по разработке см. в статье Проектные решения и методики программирования для хранилища данных SQL.