CREATE EXTERNAL TABLE AS SELECT 문을 사용하여 데이터 파일 변환

SQL 언어에는 데이터를 조작할 수 있는 많은 기능이 포함되어 있습니다. 예를 들어 SQL을 사용하여 다음을 수행할 수 있습니다.

• 데이터 세트의 행과 열을 필터링합니다.
• 데이터 필드의 이름을 바꾸고 데이터 형식 간에 변환합니다.
• 파생 데이터 필드를 계산합니다.
• 문자열 값을 조작합니다.
• 데이터를 그룹화하고 집계합니다.

Azure Synapse 서버리스 SQL 풀을 사용하여 데이터를 변환하고 추가 처리 또는 쿼리를 위해 데이터를 데이터 레이크에 파일로 유지하는 SQL 문을 실행할 수 있습니다. Transact-SQL 구문에 익숙하면 원하는 특정 변환을 적용하는 SELECT 문을 만들고 SQL을 사용하여 쿼리할 수 있는 메타데이터 테이블 스키마로 SELECT 문의 결과를 선택한 파일 형식으로 저장할 수 있습니다.

전용 SQL 풀 또는 서버리스 SQL 풀에서 CETAS(CREATE EXTERNAL TABLE AS SELECT) 문을 사용하여 데이터 레이크의 파일에 데이터를 저장하는 외부 테이블에 쿼리 결과를 유지할 수 있습니다.

CETAS 문에는 유효한 데이터 원본(데이터베이스의 기존 테이블이나 뷰 또는 데이터 레이크에서 파일 기반 데이터를 읽는 OPENROWSET 함수일 수 있음)의 데이터를 쿼리하고 조작하는 SELECT 문이 포함되어 있습니다. SELECT 문의 결과는 파일에 저장된 데이터를 통해 관계형 추상화 기능을 제공하는 데이터베이스의 메타데이터 개체인 외부 테이블에 유지됩니다. 아래 다이어그램은 이 개념을 시각적으로 보여 줍니다.

이 기술을 적용하면 SQL을 사용하여 파일 또는 테이블에서 데이터를 추출 및 변환하고 다운스트림 처리 또는 분석을 위해 변환된 결과를 저장할 수 있습니다. 변환된 데이터의 후속 작업은 SQL 풀 데이터베이스의 관계형 테이블에 대해 수행하거나 기본 데이터 파일에 대해 직접 수행할 수 있습니다.

CETAS를 지원하는 외부 데이터베이스 개체 만들기

CETAS 식을 사용하려면 서버리스 또는 전용 SQL 풀의 데이터베이스에서 다음 유형의 개체를 만들어야 합니다. 서버리스 SQL 풀을 사용하는 경우 CREATE DATABASE 데이터베이스가 아닌 사용자 지정 데이터베이스( 문을 사용하여 만든)에 이러한 개체를 만듭니다.

외부 데이터 원본

외부 데이터 원본은 데이터 레이크의 파일 시스템 위치에 대한 연결을 캡슐화합니다. 그런 다음, 이 연결을 사용하여 CETAS 문에서 만든 외부 테이블의 데이터 파일이 저장되는 상대 경로를 지정할 수 있습니다.

CETAS 문의 원본 데이터가 동일한 데이터 레이크 경로의 파일에 있는 경우 OPENROWSET 함수에서 동일한 외부 데이터 원본을 사용하여 쿼리할 수 있습니다. 또는 원본 파일에 대한 별도의 외부 데이터 원본을 만들거나 OPENROWSET 함수에서 정규화된 파일 경로를 사용할 수 있습니다.

외부 데이터 원본을 만들려면 다음 예제와 같이 CREATE EXTERNAL DATA SOURCE 문을 사용합니다.

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

이전 예제에서는 외부 데이터 원본을 사용하는 쿼리를 실행하는 사용자에게 파일에 액세스할 수 있는 충분한 권한이 있다고 가정합니다. 다른 방법은 모든 사용자에게 직접 읽을 수 있는 권한을 부여하지 않고 파일 데이터에 액세스하는 데 사용할 수 있도록 외부 데이터 원본의 자격 증명을 캡슐화하는 것입니다.

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

외부 파일 형식:

CETAS 문은 파일에 저장된 데이터가 있는 테이블을 만듭니다. 만들려는 파일 형식을 외부 파일 형식으로 지정해야 합니다.

외부 파일 형식을 만들려면 다음 예제와 같이 CREATE EXTERNAL FILE FORMAT 문을 사용합니다.

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

CETAS 문 사용

외부 데이터 원본 및 외부 파일 형식을 만든 후 CETAS 문을 사용하여 데이터를 변환하고 결과를 외부 테이블에 저장할 수 있습니다.

예를 들어 변환하려는 원본 데이터가 데이터 레이크의 폴더에 저장된 쉼표로 구분된 텍스트 파일의 판매 주문으로 구성되어 있다고 가정합니다. “특수 주문”로 표시된 주문만 포함하도록 데이터를 필터링하고 변환된 데이터를 동일한 데이터 레이크의 다른 폴더에 Parquet 파일로 저장하려고 합니다. 다음 예제와 같이 원본 폴더와 대상 폴더 모두에 동일한 외부 데이터 원본을 사용할 수 있습니다.

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

이전 예제의 LOCATION 및 BULK 매개 변수는 각각 결과 및 소스 파일에 대한 상대 경로입니다. 경로는 파일 외부 데이터 원본에서 참조하는 파일 시스템 위치를 기준으로 합니다.

이해해야 할 중요한 점은 외부 데이터 원본을 사용하여 외부 테이블의 변환된 데이터를 저장할 위치를 지정해야 한다는 것입니다. 파일 기반 원본 데이터가 동일한 폴더 계층 구조에 저장되는 경우 동일한 외부 데이터 원본을 사용할 수 있습니다. 그렇지 않으면 다음 예제와 같이 두 번째 데이터 원본을 사용하여 원본 데이터에 대한 연결을 정의하거나 정규화된 경로를 사용할 수 있습니다.

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

외부 테이블 삭제

변환된 데이터를 포함하는 외부 테이블이 더 이상 필요하지 않은 경우 다음과 같이 DROP EXTERNAL TABLE 문을 사용하여 데이터베이스에서 해당 테이블을 삭제할 수 있습니다.

DROP EXTERNAL TABLE SpecialOrders;

그러나 외부 테이블은 실제 데이터가 포함된 파일에 대한 메타데이터 추상화임을 이해하는 것이 중요합니다. 외부 테이블을 삭제해도 기본 데이터는 삭제되지 않습니다.