CREAR TABLA COMO SELECT (CTAS)

Tip

Microsoft Fabric Data Warehouse es un almacenamiento relacional de escala empresarial en una base de lago de datos, con una arquitectura lista para el futuro, inteligencia artificial integrada y nuevas características. Si no está familiarizado con el almacenamiento de datos, comience con Fabric Data Warehouse. Las cargas de trabajo del grupo de SQL dedicadas pueden actualizarse a Fabric para acceder a nuevas funcionalidades en ciencia de datos, análisis en tiempo real e informes.

• Iniciar una prueba gratuita de Fabric.
• Asistente de migración para almacén de datos en tejido.

En este artículo se explica la instrucción T-SQL CREATE TABLE AS SELECT (CTAS) en el grupo de SQL dedicado (anteriormente SQL DW) para desarrollar soluciones. En el artículo también se proporcionan ejemplos de código.

CREAR TABLA COMO SELECCIONAR

La instrucción CREATE TABLE AS SELECT (CTAS) es una de las características más importantes de T-SQL disponibles. CTAS es una operación paralela que crea una nueva tabla basada en la salida de una instrucción SELECT. CTAS es la forma más sencilla y rápida de crear e insertar datos en una tabla con un solo comando.

SELECT...INTO frente a CTAS

CTAS es una versión personalizable de la instrucción SELECT...INTO.

El siguiente es un ejemplo de una instrucción simple SELECT...INTO:

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

Sin embargo, SELECT...INTO no permite cambiar el método de distribución o el tipo de índice como parte de la operación. Debe crear [dbo].[FactInternetSales_new] usando el tipo de distribución predeterminado de ROUND_ROBIN y la estructura de tabla predeterminada de CLUSTERED COLUMNSTORE INDEX.

Por otro lado, con CTAS puede especificar tanto la distribución de los datos de la tabla como el tipo de estructura de la misma. Para convertir el ejemplo anterior a CTAS:

CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
 DISTRIBUTION = ROUND_ROBIN
 ,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales];

Nota

Si solo quiere cambiar el índice en su operación de CTAS y la tabla de origen tiene una distribución de hash, mantenga la misma columna de distribución y el mismo tipo de datos. Esto evita el movimiento de datos entre distribuciones cruzadas durante la operación, lo que es más eficiente.

Uso de CTAS para copiar una tabla

Quizás uno de los usos más comunes de CTAS es crear la copia de una tabla para cambiar el DDL. Digamos que originalmente creó su tabla como ROUND_ROBIN, y que ahora quiere cambiarla a una tabla distribuida en una columna. CTAS es cómo se cambiaría la columna de distribución. También se puede usar CTAS para cambiar las particiones, el indexado o los tipos de columnas.

Supongamos que especificó HEAP y usó el tipo de distribución predeterminado de ROUND_ROBIN para crear esta tabla.

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 
);

Ahora quiere crear una copia nueva de esta tabla con un Clustered Columnstore Index, para poder aprovechar el rendimiento de las tablas columnstore agrupadas. También quiere distribuir esta tabla en ProductKey, ya que prevé uniones en esta columna y quiere evitar el movimiento de datos durante esas uniones en ProductKey. Por último, también debería agregar particiones en OrderDateKey, para poder eliminar rápidamente datos antiguos eliminando particiones antiguas. Esta es la instrucción CTAS que copiará tu tabla antigua en una nueva tabla.

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;

Finalmente, puede cambiar el nombre de sus tablas, sustituir por la nueva tabla y luego eliminar la tabla anterior.

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

establezca explícitamente el tipo de datos y la nulabilidad de salida

Al migrar código, podría encontrarse con este tipo de patrón de codificación:

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;

Es posible que piense que debe migrar este código a CTAS, y estaría en lo correcto. Sin embargo, existe un problema oculto aquí.

El siguiente código no produce el mismo resultado:

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;

Observe que la columna "result" traslada el tipo de datos y los valores de nulabilidad de la expresión. Mantener el tipo de datos puede llevar a variaciones sutiles en los valores si no se tiene cuidado.

Pruebe este ejemplo:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

El valor almacenado para el resultado es diferente. Como el valor persistente en la columna de resultados se usa en otras expresiones, el error se vuelve más importante incluso.

Esto es importante para las migraciones de datos. Aunque se puede decir que la segunda consulta es más precisa, hay un problema. Los datos serán diferentes en comparación con el sistema de origen y eso conduce a preguntas sobre la integridad de la migración. Este es uno de los pocos casos en los que la respuesta "incorrecta" es en realidad la correcta.

El motivo de que veamos esta disparidad entre los dos resultados se debe a la conversión implícita de tipos. En el primer ejemplo, la tabla define la definición de columna. Cuando se inserta la fila, se produce una conversión implícita de tipo. En el segundo ejemplo, no hay ninguna conversión de tipos implícita ya que la expresión define el tipo de datos de la columna.

Observe también que la columna del segundo ejemplo se ha definido como una columna que admite un valor NULL mientras que en el primer ejemplo no. Cuando se creó la tabla en el primer ejemplo, la nulabilidad se definió explícitamente. En el segundo ejemplo, simplemente se dejó con la expresión y, de forma predeterminada, esto dará lugar a una definición de NULL.

Para resolver estos problemas, debe definir explícitamente la conversión de tipos y la propiedad de nulabilidad en la parte SELECT de la instrucción CTAS. No se pueden establecer estas propiedades en "CREATE TABLE". En el siguiente ejemplo se muestra cómo corregir el código:

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

Tenga en cuenta lo siguiente:

• Puede usar CAST() o CONVERT().
• Use ISNULL, en lugar de COALESCE, para forzar la anulabilidad. Consulte la nota siguiente.
• ISNULL es la función más externa.
• La segunda parte de ISNULL es una constante, es decir, 0.

Nota

Para que la nulabilidad se establezca correctamente, es fundamental usar ISNULL y no COLAESCE. COALESCE no es una función determinista y, por lo tanto, el resultado de la expresión será siempre NULLable. ISNULL es diferente. Es determinista. Por lo tanto, cuando la segunda parte de la función ISNULL es una constante o un literal, el valor resultante no será nulo.

Asegurar la integridad de sus cálculos es también importante para el cambio de particiones en una tabla. Imagine que tiene esta tabla definida como una tabla de hechos:

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
                    )
                )
);

Sin embargo, el campo de cantidad es una expresión calculada. No es parte de los datos de origen.

Para crear su conjunto de datos particionado, es posible que quiera usar el siguiente código:

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');

La consulta se ejecutaría perfectamente. El problema se produce al intentar realizar el cambio de partición. Las definiciones de la tabla no coinciden. Para hacer coincidir las definiciones de la tabla, modifique el CTAS para agregar una función ISNULL y así preservar el atributo de nulabilidad de la columna.

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');

Puede observar que mantener la coherencia de tipos y las propiedades de nulabilidad en un CTAS es una práctica recomendada en ingeniería. Esto le permitirá mantener la integridad de los cálculos y también garantiza que la modificación de particiones sea posible.

CTAS es una de las instrucciones más importantes de SQL de Synapse. Asegúrese de que la comprende perfectamente. Consulte la documentación de CTAS.

Contenido relacionado

Para obtener más sugerencias sobre desarrollo, consulte la información general sobre desarrollo.