CREATE TABLE AS SELECT

적용 대상: Azure Synapse Analytics Analytics Platform System(PDW)

CREATE TABLE AS SELECT(CTAS)는 사용할 수 있는 매우 중요한 T-SQL 기능 중 하나이며 SELECT 문의 출력을 기반으로 하여 새 테이블을 만드는 완전 병렬화된 작업입니다. CTAS는 테이블의 복사본을 만드는 가장 간단하고 빠른 방법입니다.

예를 들어 CTAS를 사용하여 다음을 수행할 수 있습니다.

• 서로 다른 해시 배포 열로 테이블을 다시 만듭니다.
• 테이블을 복제본으로 다시 만듭니다.
• 테이블의 몇몇 열에 대해서만 columnstore 인덱스를 만듭니다.
• 외부 데이터를 쿼리하거나 가져옵니다.

참고

CTAS는 테이블을 만드는 기능에 추가되므로 이 토픽에서는 CREATE TABLE 토픽을 반복하지 않으려고 합니다. 그 대신, CTAS와 CREATE TABLE 문의 차이점을 설명합니다. CREATE TABLE 세부 정보는 CREATE TABLE(Azure Synapse Analytics) 문을 참조하세요.

• 이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.
• CTAS는 Microsoft Fabric의 웨어하우스에서 지원됩니다.

Transact-SQL 구문 표기 규칙

구문

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  

<distribution_option> ::=
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )
      | DISTRIBUTION = ROUND_ROBIN 
      | DISTRIBUTION = REPLICATE
    }   

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column[,...n])
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
      | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

<query_hint> ::=
    {
        MAXDOP 
    }

인수

자세한 내용은 CREATE TABLE의 인수 섹션을 참조하세요.

열 옵션

column_name [ ,...n ]
열 이름은 CREATE TABLE에 언급된 열 옵션을 허용하지 않습니다. 그 대신, 새 테이블에 대해 하나 이상의 열 이름으로 이루어진 선택적 목록을 제공할 수 있습니다. 새 테이블의 열은 지정한 이름을 사용합니다. 열 이름을 지정하는 경우 열 목록의 열 수가 선택 결과의 열 수와 일치해야 합니다. 열 이름을 지정하지 않으면 새 대상 테이블에서 select 문 결과의 열 이름을 사용합니다.

데이터 형식, 데이터 정렬 또는 Null 허용 여부 등의 다른 열 옵션을 지정할 수 없습니다. 이러한 각각의 특성은 SELECT 문의 결과에서 파생됩니다. 그러나 SELECT 문을 사용하여 특성을 변경할 수 있습니다. 예제는 CTAS를 사용하여 열 특성 변경을 참조하세요.

테이블 분포 옵션

세부 정보 및 최선의 분포 열을 선택하는 방법을 이해하려면 CREATE TABLE의 테이블 분포 옵션 섹션을 참조하세요. 실제 사용량 또는 샘플 쿼리를 기반으로 테이블에 대해 선택할 배포에 대한 권장 사항은 Azure Synapse SQL의 배포 관리자를 참조하세요.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLICATE CTAS 문에는 배포 옵션이 필요하며 기본값이 없습니다. 이 점이 기본값을 갖는 CREATE TABLE과 다릅니다.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 최대 8개 열의 해시 값에 따라 행을 배포하여 기본 테이블 데이터를 더 균일하게 배포할 수 있으므로 시간이 지남에 따라 데이터 기울이기가 줄어들고 쿼리 성능이 향상됩니다.

참고 항목

• 기능을 사용하도록 설정하려면 이 명령을 사용하여 데이터베이스의 호환성 수준을 50으로 변경합니다. 데이터베이스 호환성 수준을 설정하는 방법에 대한 자세한 내용은 ALTER DATABASE SCOPED CONFIGURATION을 참조 하세요. 예: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• MCD(다중 열 배포) 기능을 사용하지 않도록 설정하려면 이 명령을 실행하여 데이터베이스의 호환성 수준을 AUTO로 변경합니다. 예를 들어 ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; 기존 MCD 테이블은 유지되지만 읽을 수 없게 됩니다. MCD 테이블에 대한 쿼리는 다음 오류를 반환합니다. Related table/view is not readable because it distributes data on multiple columns and multi-column distribution is not supported by this product version or this feature is disabled.
  • MCD 테이블에 다시 액세스하려면 기능을 다시 사용하도록 설정합니다.
  • MCD 테이블에 데이터를 로드하려면 CTAS 문을 사용하고 데이터 원본은 Synapse SQL 테이블이어야 합니다.
  • MCD HEAP 대상 테이블의 CTAS는 지원되지 않습니다. 대신 INSERT SELECT를 해결 방법으로 사용하여 MCD HEAP 테이블에 데이터를 로드합니다.
• 스크립트를 생성하여 MCD 테이블을 만드는 데 SSMS를 사용하는 것은 현재 SSMS 버전 19 이상에서 지원됩니다.

세부 정보 및 최선의 분포 열을 선택하는 방법을 이해하려면 CREATE TABLE의 테이블 분포 옵션 섹션을 참조하세요.

워크로드에 따라 사용하는 최상의 배포 방법에 대한 권장 사항은 Synapse SQL 배포 관리자(미리 보기)를 참조하세요.

테이블 파티션 옵션

CTAS 문은 원본 테이블이 분할된 경우에도 기본적으로 분할되지 않은 테이블을 만듭니다. CTAS 문으로 분할된 테이블을 만들려면 파티션 옵션을 지정해야 합니다.

자세한 내용은 CREATE TABLE의 테이블 파티션 옵션을 참조하세요.

SELECT 문

SELECT 문은 CTAS와 CREATE TABLE 간의 기본적인 차이점입니다.

WITH common_table_expression

CTE(공통 테이블 식)라고도 하는 임시로 이름이 지정된 결과 집합을 지정합니다. 자세한 내용은 WITH common_table_expression (Transact-SQL)을 참조하세요.

SELECTselect_criteria

새 테이블을 SELECT 문의 결과로 채웁니다. select_criteria는새 테이블에 복사할 데이터를 결정하는 SELECT 문의 본문입니다. SELECT 문에 대한 자세한 내용은 SELECT (Transact-SQL)을 참조하세요.

쿼리 힌트

사용자는 MAXDOP를 정수 값으로 설정하여 최대 병렬 처리 수준을 제어할 수 있습니다. MAXDOP를 1로 설정하면 단일 스레드에서 쿼리가 실행됩니다.

사용 권한

CTAS를 사용하려면 select_criteria에 참조된 임의의 개체에 대한 SELECT 권한이 필요합니다.

테이블을 만들기 위한 권한은 CREATE TABLE의 권한을 참조하세요.

설명

자세한 내용은 CREATE TABLE의 일반적인 참고사항을 참조하세요.

제한 사항

제한 사항 및 제한 사항에 대한 자세한 내용은 CREATE TABLE의 제한 사항 및 제한을 참조하세요.

• 문자열 열을 제외하고 Azure Synapse Analytics에서 지원되는 모든 데이터 형식의 열에 순서가 지정된 클러스터형 columnstore 인덱스를 만들 수 있습니다.

• SET ROWCOUNT (Transact-SQL)은 CTAS에 아무런 영향도 주지 않습니다. 비슷한 동작을 얻으려면 TOP(Transact-SQL)을 사용합니다.

• CTAS는 문의 일부로 함수를 SELECT 지원하지 OPENJSON 않습니다. 또는 .를 사용합니다 INSERT INTO ... SELECT. 예시:

  DECLARE @json NVARCHAR(MAX) = N'
  [
      {
          "id": 1,
          "name": "Alice",
          "age": 30,
          "address": {
              "street": "123 Main St",
              "city": "Wonderland"
          }
      },
      {
          "id": 2,
          "name": "Bob",
          "age": 25,
          "address": {
              "street": "456 Elm St",
              "city": "Gotham"
          }
      }
  ]';
  
  INSERT INTO Users (id, name, age, street, city)
  SELECT 
      id,
      name,
      age,
      JSON_VALUE(address, '$.street') AS street,
      JSON_VALUE(address, '$.city') AS city
  FROM OPENJSON(@json)
  WITH (
      id INT,
      name NVARCHAR(50),
      age INT,
      address NVARCHAR(MAX) AS JSON
  );
  

잠금 동작

자세한 내용은 CREATE TABLE의 잠금 동작을 참조하세요.

성능

해시 분포 테이블의 경우 조인 및 집계의 성능을 향상시키기 위해 CTAS를 사용하여 서로 다른 분포 열을 선택할 수 있습니다. 다른 배포 열을 선택하는 것이 목표가 아닌 경우 동일한 배포 열을 지정하면 행 재배포가 방지되므로 최상의 CTAS 성능이 제공됩니다.

CTAS를 사용하여 테이블을 만들고 성능이 요인이 아닌 경우 배포 열을 결정할 필요가 없도록 지정할 ROUND_ROBIN 수 있습니다.

이후 쿼리에서 데이터 이동을 방지하려면 REPLICATE를 지정할 수 있지만 각 컴퓨팅 노드에 대해 전체 테이블 복사본을 로드하기 위해 스토리지가 증가합니다.

테이블을 복사하는 예제

A. CTAS를 사용하여 테이블 복사

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

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 테이블의 성능을 이용할 수 있도록 클러스터형 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;

열 옵션에 대한 예제

B. CTAS를 사용하여 열 특성 변경

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

이 예제에서는 CTAS를 사용하여 DimCustomer2 테이블의 여러 열에 대해 데이터 형식, NULL 허용 여부 및 데이터 정렬을 변경합니다.

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  

-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  

-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] INT NOT NULL, 
    [CustomerKeyChangeNullable] INT NULL, 
    [CustomerKeyChangeDataTypeNullable] DECIMAL(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] DECIMAL(10, 2) NOT NULL, 
    [GeographyKeyNoChange] INT NULL, 
    [GeographyKeyChangeNotNullable] INT NOT NULL, 
    [CustomerAlternateKeyNoChange] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] NVARCHAR(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

마지막 단계로 RENAME(Transact-SQL)을 사용하여 테이블을 전환할 수 있습니다. 이렇게 하면 DimCustomer2가 새 테이블이 됩니다.

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

테이블 배포 예제

C. CTAS를 사용하여 테이블에 대한 배포 방법 변경

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

이 간단한 예제에서는 테이블에 대한 배포 방법을 변경하는 방법을 보여줍니다. 이 작업을 수행하는 방법의 원리를 보여주기 위해 해시 배포 테이블을 라운드 로빈으로 변경한 다음, 라운드 로빈 테이블을 다시 해시 배포로 변경해 보겠습니다. 마지막 테이블은 원본 테이블과 일치합니다.

대부분의 경우 해시 분산 테이블을 라운드 로빈 테이블로 변경할 필요가 없습니다. 오히려 라운드 로빈 테이블을 해시 배포 테이블로 변경해야 할 경우가 더 많습니다. 예를 들어 조인 성능을 개선하기 위해 처음에 새 테이블을 라운드 로빈으로 로드한 다음, 나중에 해당 테이블을 해시 배포 테이블로 변경할 수 있습니다.

이 예에서는 AdventureWorksDW 예제 데이터베이스를 사용합니다. Azure Synapse Analytics 버전을 로드하려면 빠른 시작: Azure Portal을 사용하여 Azure Synapse Analytics에서 전용 SQL 풀(이전의 SQL DW) 만들기 및 쿼리를 참조하세요.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

다음으로 이 테이블을 다시 해시 배포 테이블로 변경합니다.

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D. CTAS를 사용하여 테이블을 복제된 테이블로 변환

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

이 예제는 라운드 로빈 또는 해시 배포 테이블을 복제된 테이블로 변환하는 경우에 적용됩니다. 이 특정 예제에서는 이전의 배포 유형 변경 방법을 선택하고 한 단계를 더 실행합니다. DimSalesTerritory는 하나의 차원이고 더 작은 테이블일 가능성이 있으므로 다른 테이블에 조인할 때 데이터 이동을 방지하기 위해 테이블을 복제본으로 다시 만드는 방법을 선택할 수 있습니다.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E. CTAS를 사용하여 열 수가 적은 테이블 만들기

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

다음 예제에서는 myTable (c, ln)이라는 라운드 로빈 배포 테이블을 만듭니다. 새 테이블은 열을 두 개만 포함하며 SELECT 문에 열 이름으로 열 별칭을 사용합니다.

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer; 

쿼리 힌트 예제

F. CREATE TABLE AS SELECT(CTAS)와 함께 쿼리 힌트 사용

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

이 쿼리는 CTAS 문에 쿼리 조인 힌트를 사용하는 기본 구문을 보여줍니다. 쿼리가 제출된 후 Azure Synapse Analytics는 각 개별 배포에 대한 쿼리 계획을 생성할 때 해시 조인 방법을 적용합니다. 해시 조인 쿼리 힌트에 대한 자세한 내용은 OPTION 절(Transact-SQL)을 참조하세요.

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

외부 테이블 예제

G. CTAS를 사용하여 Azure Blob Storage에서 데이터 가져오기

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

외부 테이블에서 데이터를 가져오려면 CREATE TABLE AS SELECT를 사용하여 외부 테이블에서 선택합니다. 외부 테이블에서 Azure Synapse Analytics로 가져올 데이터를 선택하는 구문은 일반 테이블에서 데이터를 선택하는 구문과 같습니다.

다음 예제에서는 Azure Blob Storage 계정의 데이터에 대한 외부 테이블을 정의합니다. 그런 다음, CREATE TABLE AS SELECT를 사용하여 외부 테이블에서 선택합니다. 그러면 Azure Blob Storage의 텍스트로 분리된 파일의 데이터를 가져와서 새 Azure Synapse Analytics 테이블에 저장합니다.

--Use your own processes to create the text-delimited files on Azure Blob Storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  

--Use CREATE TABLE AS SELECT to import the Azure Blob Storage data into a new   
--Synapse Analytics table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;  

H. CTAS를 사용하여 외부 테이블에서 Hadoop 데이터 가져오기

적용 대상: 분석 플랫폼 시스템(PDW)

외부 테이블에서 데이터를 가져오려면 CREATE TABLE AS SELECT를 사용하여 외부 테이블에서 선택합니다. 외부 테이블에서 분석 플랫폼 시스템(PDW)로 가져올 데이터를 선택하는 구문은 일반 테이블에서 데이터를 선택하는 구문과 같습니다.

다음 예제에서는 Hadoop 클러스터에 외부 테이블을 정의합니다. 그런 다음, CREATE TABLE AS SELECT를 사용하여 외부 테이블에서 선택합니다. 이렇게 해서 Hadoop의 텍스트로 분리된 파일의 데이터를 가져와서 새 분석 플랫폼 시스템(PDW) 테이블에 저장합니다.

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  

-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  

-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;   

CTAS를 사용하여 SQL Server 코드를 바꾸는 예제

CTAS를 사용하여 몇몇 지원되지 않는 기능을 해결합니다. 기존 코드를 CTAS 사용이 가능하도록 다시 작성하면 데이터 웨어하우스에 대해 코드를 실행할 수 있을 뿐만 아니라 대개 성능도 향상됩니다. 이러한 성능 향상은 완전히 병렬화된 디자인의 결과입니다.

참고

"CTAS를 먼저" 생각해 보십시오. CTAS를 사용하여 문제를 해결할 수 있다고 생각한다면 결과적으로 더 많은 데이터를 기록하더라도 그 방법이 최선의 방법입니다.

9\. SELECT..INTO 대신에 CTAS 사용

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

SQL Server 코드는 일반적으로 SELECT..INTO를 사용하여 테이블을 SELECT 문의 결과로 채웁니다. 다음은 SQL Server SELECT..INTO 문의 예입니다.

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

이 구문은 Azure Synapse Analytics 및 병렬 데이터 웨어하우스에서 지원되지 않습니다. 이 예제에서는 이전의 SELECT..INTO 문을 CTAS 문으로 다시 작성하는 방법을 보여줍니다. CTAS 구문에서 설명한 임의의 DISTRIBUTION 옵션을 선택할 수 있습니다. 이 예제에서는 ROUND_ROBIN 배포 방법을 사용합니다.

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J. CTAS를 사용하여 MERGE 문 단순화

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

CTAS을 사용하여 MERGE 문을 적어도 부분적으로 대체할 수 있습니다. INSERT과 UPDATE를 단일 명령문으로 통합할 수 있습니다. 삭제된 레코드는 두 번째 문에서 닫혀야 합니다.

예제는 UPSERT 다음과 같습니다.

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimProduct_upsert]  TO [DimProduct];

11. 데이터 형식 및 출력의 null 허용 여부를 명시적으로 지정

적용 대상: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

SQL Server 코드를 Azure Synapse Analytics로 마이그레이션할 때 다음 유형의 코딩 패턴을 발견할 수도 있습니다.

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
;

결과에 저장된 값이 다릅니다. 결과 열에 보관된 값이 다른 식에 사용되면 오류가 훨씬 더 심각해집니다.

이는 데이터 마이그레이션의 경우 중요합니다. 두 번째 쿼리가 더 정확하더라도 문제가 있습니다. 데이터가 원본 시스템과 다르며 마이그레이션에서 무결성 문제가 발생합니다. 이는 "오답"이 실제로는 정답인, 드문 경우 중 하나입니다!

두 결과 간에 차이가 나타나는 이유는 암시적 형 변환 때문입니다. 첫 번째 예제에서 테이블은 열 정의를 정의합니다. 행이 삽입되면 암시적 형 변환이 일어납니다. 두 번째 예제에서는 식이 열의 데이터 형식을 정의하므로 암시적 형식 변환이 없습니다. 또한 두 번째 예제의 열은 NULLable 열로 정의되었지만 첫 번째 예제에서는 정의되지 않았습니다. 첫 번째 예제에서 테이블이 만들어질 때 열의 NULL 허용 여부를 명시적으로 정의했습니다. 두 번째 예제에서는 식에 남아 있으며 기본적으로 정의가 NULL 생성됩니다.

이 문제를 해결하려면 CTAS 문의 SELECT 부분에서 형식 변환과 NULL 허용 여부를 명시적으로 설정해야 합니다. 테이블 만들기 파트에서는 이러한 속성을 설정할 수 없습니다.

이 예제에서는 코드를 수정하는 방법을 보여 줍니다.

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은 NULLability가 COALESCE가 아닌 강제 적용하는 데 사용됩니다.
• ISNULL은 맨 바깥쪽의 함수입니다.
• ISNULL의 두 번째 부분은 상수 0입니다.

참고 항목

NULL 허용 여부를 올바르게 설정하려면 COALESCE를 사용하지 말고 ISNULL을 사용해야 합니다. 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를 수정해야 합니다.

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 허용 여부 속성을 유지하는 것이 좋은 엔지니어링 구현 방식임을 알 수 있습니다. 계산에서 무결성을 유지하고 파티션 전환도 가능하도록 하는 것이 좋습니다.

12. MAXDOP 1을 사용하여 순서가 지정된 클러스터형 columnstore 인덱스 만들기

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

다음 단계

• CREATE EXTERNAL DATA SOURCE(Transact-SQL)
• CREATE EXTERNAL FILE FORMAT(Transact-SQL)
• CREATE EXTERNAL TABLE(Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT(Transact-SQL)
• CREATE TABLE(Azure Synapse Analytics)
• DROP TABLE(Transact-SQL)
• DROP EXTERNAL TABLE(Transact-SQL)
• ALTER TABLE(Transact-SQL)
• ALTER EXTERNAL TABLE(Transact-SQL)