CREATE TABLE AS SELECT (CTAS)
이 문서에서는 솔루션 개발을 위한 전용 SQL 풀(이전의 SQL DW)의 CTAS(CREATE TABLE AS SELECT) T-SQL 문을 설명합니다. 코드 예제도 제공합니다.
CREATE TABLE AS SELECT
CTAS(CREATE TABLE AS SELECT) 문은 현재 제공되고 있는 가장 중요한 T-SQL 기능 중 하나로, SELECT 문의 출력을 기반으로 새 테이블을 만드는 병렬 연산입니다. CTAS는 단일 명령을 사용하여 데이터를 만들고 테이블에 삽입하는 가장 간단하고 빠른 방법입니다.
SELECT...INTO 및 CTAS
CTAS는 SELECT...INTO 문의 한 버전으로, 보다 다양한 사용자 지정이 가능합니다.
다음은 간단한 SELECT..INTO 문의 예제입니다.
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECT...INTO를 사용할 때는 작업의 일환으로 배포 방법이나 인덱스 유형을 변경할 수 없습니다. 기본 배포 유형인 ROUND_ROBIN과 기본 테이블 구조인 CLUSTERED COLUMNSTORE INDEX를 사용하여 [dbo].[FactInternetSales_new]를 생성합니다.
반면, 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
);
이제 클러스터형 Columnstore 테이블의 성능을 이용할 수 있도록 Clustered Columnstore Index로 이 테이블의 새 복사본을 만들려고 합니다. 또한 이 열에 대한 조인을 예상하고 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;
참고로 "결과" 열은 식의 데이터 형식 및 NULL 허용 여부 값을 이월합니다. 데이터 형식을 이월할 경우 주의하지 않으면 값에 미묘한 차이가 발생할 수 있습니다.
다음 예제를 실행해 보세요.
SELECT result,result*@d
from result;
SELECT result,result*@d
from ctas_r;
결과에 저장된 값이 다릅니다. 결과 열에 보관된 값이 다른 식에 사용되면 오류가 훨씬 더 심각해집니다.
이는 데이터 마이그레이션의 경우 중요합니다. 두 번째 쿼리는 틀림없이 더 정확하지만 문제가 있습니다. 데이터가 원본 시스템과 다르며 마이그레이션에서 무결성 문제가 발생합니다. 이는 "오답"이 실제로는 정답인, 드문 경우 중 하나입니다!
두 결과 간에 차이가 나타나는 이유는 암시적 형식 캐스팅 때문입니다. 첫 번째 예제에서 테이블은 열 정의를 정의합니다. 행이 삽입되면 암시적 형식 변환이 일어납니다. 두 번째 예제에서는 식이 열의 데이터 형식을 정의하기 때문에 암시적 형식 변환이 없습니다.
또한 두 번째 예제의 열은 NULL을 허용하는 열로 정의되었지만, 첫 번째 예제의 열은 NULL을 허용하는 열로 정의되지 않았다는 점에 주목하세요. 첫 번째 예제에서 테이블이 만들어질 때 열의 NULL 허용 여부를 명시적으로 정의했습니다. 두 번째 예제에서는 NULL 허용 여부가 식에 따라서 결정되기 때문에 기본적으로 이로 인해 NULL 정의가 있는 셈입니다.
이러한 문제를 해결하려면 CTAS 문의 SELECT 부분에서 형식 변환 및 Null 허용 여부를 명시적으로 설정해야 합니다. ‘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 허용 여부를 강제 설정하려면 COALESCE가 아닌 ISNULL을 사용합니다. 다음이 참고를 확인하세요.
- ISNULL은 맨 바깥쪽의 함수입니다.
- ISNULL의 두 번째 부분은 상수(0)입니다.
참고 항목
Null 허용 여부를 올바르게 설정하려면 COALESCE가 아닌 ISNULL을 사용해야 합니다. COALESCE는 결정 함수가 아니므로 식의 결과는 언제나 Null을 허용합니다. ISNULL은 다릅니다. 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
)
)
);
그러나 amount 필드는 계산된 식이며 원본 데이터의 일부가 아닙니다.
분할된 데이터 세트를 만들려면 다음 코드를 사용하는 것이 좋습니다.
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를 수정하여 열의 Null 허용 여부 특성이 유지되도록 ISNULL 함수를 추가합니다.
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 설명서를 참조하세요.
다음 단계
더 많은 개발 팁은 개발 개요를 참조하세요.