CREATE TABLE AS SELECT (CTAS) — создание таблицы с помощью запроса SELECT.

Tip

Microsoft Fabric Data Warehouse — это реляционное хранилище корпоративного масштаба на основе озера данных, с архитектурой, готовой к будущему, встроенной ИИ и новыми функциями. Если вы не знакомы с хранилищем данных, начните с Fabric Data Warehouse. Существующие рабочие нагрузки выделенного пула SQL могут обновляться до Fabric для доступа к новым возможностям в области науки о данных, аналитики в реальном времени и отчетности.

• Start a Fabric бесплатная пробная версия.
• Ассистент по миграции для Fabric Data Warehouse.

В этой статье описывается применение инструкции T-SQL CREATE TABLE AS SELECT (CTAS) в выделенном пуле SQL (ранее называлось хранилищем данных SQL) для разработки решений. Здесь также приведены примеры кодов.

СОЗДАТЬ ТАБЛИЦУ КАК SELECT

Инструкция CREATE TABLE AS SELECT (CTAS) — одна из самых важных функций T-SQL. CTAS — это распараллеленная операция, которая создает новую таблицу на основе выходных данных инструкции SELECT. CTAS — это самый простой и быстрый способ создания и вставки данных в таблицу с помощью одной команды.

Сравнение операторов 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;

Определите тип данных и возможность использования null-значений для результата.

При переносе кода вы можете встретить следующую структуру кода:

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.
• Используйте ISNULL, а не COALESCE, чтобы обеспечить принудительную NULL-значимость. Ознакомьтесь со следующим примечанием.
• ISNULL — это самая внешняя функция.
• Вторая часть функции ISNULL является константой, 0.

Примечание.

Чтобы правильно задать значение NULL, необходимо использовать ISNULL, а не COALESCE. COALESCE не является детерминированной функцией, поэтому результат выражения всегда может принимать значение NULL. ISNULL работает иначе. Это детерминировано. Таким образом, если вторая часть функции ISNULL является константой или литералом, результирующее значение будет НЕ 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');

Вы видите, что согласованность типов и сохранение свойств отсутствия значения (nullability) в CTAS являются передовой инженерной практикой. Это помогает поддерживать целостность вычислений и обеспечивает возможность переключения секций.

CTAS — это одна из самых важных инструкций в Synapse SQL. Убедитесь, что вы его тщательно поняли. Дополнительные сведения см. в документации CTAS.

Связанный контент

Для получения дополнительных советов по разработке см. обзор разработки.