CREATE TABLE AS SELECT (CTAS)
Cet article explique l’instruction T-SQL CREATE TABLE AS SELECT (CTAS) dans le pool SQL dédié (anciennement SQL DW) pour le développement de solutions. L’article fournit également des exemples de code.
CREATE TABLE AS SELECT
L’instruction CREATE TABLE AS SELECT (CTAS) est l’une des fonctionnalités T-SQL les plus importantes. CTAS désigne une opération parallèle qui crée une table en fonction de la sortie d’une instruction SELECT. C’est le moyen le plus rapide pour créer et insérer des données dans une table avec une seule commande.
SELECT…INTO et CTAS
CTAS est une version plus personnalisable de l’instruction SELECT…INTO.
Voici un exemple simple d’instruction SELECT…INTO :
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECT…INTO ne vous permet pas de modifier la méthode de distribution ou le type d’index dans le cadre de l’opération. Vous créez [dbo].[FactInternetSales_new] en utilisant le type de distribution par défaut de ROUND_ROBIN et la structure de table par défaut de CLUSTERED COLUMNSTORE INDEX.
En revanche, avec CTAS, vous pouvez spécifier la distribution de données de la table, ainsi que le type de structure de table. Pour convertir l’exemple précédent en CTAS :
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Notes
Si vous essayez uniquement de modifier l’index dans votre opération CTAS et si la table source est distribuée par hachage, conservez la même colonne de distribution et le même type de données. Cela évite le déplacement des données entre les distributions pendant l’opération, d’où une efficacité accrue.
Utiliser CTAS pour copier une table
L’une des utilisations les plus courantes de CTAS consiste sans doute à créer une copie de table afin que vous puissiez modifier le DDL. Supposons que vous avez créé votre table ROUND_ROBIN et que vous souhaitez maintenant la remplacer par une table distribuée sur une colonne. CTAS permet de modifier la colonne de distribution. Vous pouvez aussi utiliser CTAS pour modifier le partitionnement, l’indexation ou les types de colonnes.
Supposons que vous avez créé cette table en spécifiant HEAP et en utilisant le type de distribution par défaut 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
);
Maintenant, vous souhaitez créer une copie de cette table avec Clustered Columnstore Index afin de tirer parti des performances de tables Columnstore en cluster. Vous souhaitez également distribuer cette table sur ProductKey dans la mesure où vous anticipez des jointures sur cette colonne et vous souhaitez éviter le déplacement de données au cours des jointures sur ProductKey. Enfin, vous souhaitez ajouter le partitionnement sur OrderDateKey pour supprimer rapidement les anciennes données en supprimant les anciennes partitions. Voici l’instruction CTAS qui copie votre ancienne table dans une nouvelle table.
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;
Pour finir, vous pouvez renommer vos tables pour intégrer votre nouvelle table et supprimer votre ancienne table.
RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;
DROP TABLE FactInternetSales_old;
déclarer explicitement le type de données et la possibilité de valeur NULL de la sortie
Lorsque vous procédez à la migration de votre code, vous pouvez constater que vous exécutez le type de modèle de codage suivant :
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;
Vous pourriez envisager de convertir ce code en instruction CTAS, et vous auriez raison. Toutefois, un problème se dissimule derrière ce scénario.
Le code suivant ne génère pas le même résultat :
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;
Notez que la colonne « result » reprend le type de données et la possibilité de valeur NULL de l’expression. Ce type de données peut entraîner de légers écarts de valeurs si vous n’y prenez pas garde.
Essayez l’exemple suivant :
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
Les valeurs de résultats enregistrées sont différentes. Étant donné que la valeur persistante dans la colonne de résultat est utilisée dans d’autres expressions, l’erreur devient encore plus significative.
C’est important dans le cas des migrations de données. Bien que la seconde requête soit effectivement plus précise, un problème se pose. Les données obtenues diffèrent de celles du système source, ce qui compromet l’intégrité de la migration. Il s’agit de l’un des rares cas où la « mauvaise » réponse est en fait la bonne réponse !
Cet écart entre les deux résultats est dû à la conversion de type (transtypage) implicite. Dans le premier exemple, la table définit la définition de colonne. Lorsque la ligne est insérée, une conversion de type implicite est effectuée. Dans le second exemple, aucune conversion de type implicite ne se produit, car l’expression définit le type de données de la colonne.
La colonne figurant dans le second exemple a été définie comme une colonne Nullable, ce qui n’est pas son cas dans le premier exemple. Lorsque la table a été créée dans le premier exemple, la possibilité de valeur Null de la colonne a été définie explicitement. Dans le second exemple, cet aspect est déterminé par l’expression et entraîne par défaut une définition de valeur Null.
Pour résoudre ces problèmes, vous devez explicitement définir la conversion de type et la possibilité de valeur Null dans la partie SELECT de l’instruction CTAS. Vous ne pouvez pas définir ces propriétés dans la partie CREATE TABLE. L’exemple suivant montre comment réparer le code :
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
Notez les points suivants :
- Vous pouvez utiliser CAST ou CONVERT.
- Utilisez ISNULL, et non COALESCE, pour forcer la possibilité de valeur Null. Reportez-vous à la remarque suivante.
- ISNULL est la fonction située la plus à l’extérieur.
- La seconde partie de l’instruction ISNULL est une constante : 0.
Notes
La possibilité de valeur Null doit être impérativement définie avec ISNULL, et non avec COALESCE. COALESCE n’est pas une fonction déterministe et par conséquent, le résultat de l’expression sera toujours NULLable. ISNULL est différente. Cette fonction est déterministe. Par conséquent, lorsque la seconde partie de la fonction ISNULL est une constante ou un littéral, la valeur résultante est NOT NULL.
Il est également important de vérifier l’intégrité de vos calculs pour le basculement de partitions de table. Imaginons que vous ayez défini la table de faits suivante :
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
)
)
);
Toutefois, le champ Quantité est une expression calculée. Il ne fait pas partie des données sources.
Pour créer votre jeu de données partitionné, vous pouvez utiliser le code suivant :
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');
Cette requête s’exécute parfaitement. Toutefois, un problème se pose lorsque vous essayez de procéder au basculement de partitions. Les définitions de table ne correspondent pas. Pour faire correspondre les définitions de table, modifiez le CTAS afin d’ajouter une fonction ISNULL dans le but de conserver l’attribut de possibilité de valeur null de la colonne.
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');
Vous pouvez constater que la cohérence des types et le maintien des propriétés de possibilité de valeur Null sur une instruction CTAS constituent une meilleure pratique d’ingénierie. Ces opérations contribuent à garantir l’intégrité de vos calculs, ainsi que la possibilité de basculement de partitions.
CTAS est l’une des instructions les plus importantes de SQL Synapse. Vous devez donc faire en sorte d’en comprendre les moindres aspects. Consultez la documentation CTAS.
Étapes suivantes
Pour obtenir des conseils supplémentaires sur le développement, consultez la vue d’ensemble sur le développement.