PolyBase를 사용하여 CSV 파일 가상화

  • 아티클

적용 대상: SQL Server 2022(16.x) 이상 버전

SQL Server 2022(16.x)는 CSV 파일에서 직접 데이터를 쿼리할 수 있습니다. 일반적으로 데이터 가상화라고 하는 이 개념을 사용하면 데이터가 원래 위치에 유지되지만 다른 테이블과 같은 T-SQL 명령을 사용하여 SQL Server 인스턴스에서 쿼리할 수 있습니다. 이 기능은 PolyBase 커넥터를 사용하며 ETL 프로세스를 통해 데이터를 복사할 필요성을 최소화합니다.

다음 예제에서 CSV 파일은 Azure Blob Storage에 저장되고 OPENROWSET 또는 외부 테이블을 통해 액세스됩니다.

데이터 가상화 에 대한 자세한 내용은 PolyBase를 사용한 데이터 가상화를 소개합니다.

미리 구성

1. 다음에서 PolyBase 사용 sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. 사용자 데이터베이스 만들기

이 연습에서는 기본 설정 및 위치를 사용하여 샘플 데이터베이스를 만듭니다. 이 빈 샘플 데이터베이스를 사용하여 데이터를 사용하고 범위가 지정된 자격 증명을 저장합니다. 이 예제에서는 명명 CSV_Demo 된 새 빈 데이터베이스가 사용됩니다.

CREATE DATABASE [CSV_Demo];

3. 마스터 키 및 데이터베이스 범위 자격 증명 만들기

사용자 데이터베이스의 데이터베이스 마스터 키는 데이터베이스 범위 자격 증명 비밀을 blob_storage암호화하는 데 필요합니다.

USE [CSV_Demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. 외부 데이터 원본 만들기

데이터베이스 범위 자격 증명은 외부 데이터 원본에 사용됩니다. 이 예제에서 CSV 파일은 Azure Blob Storage에 있으므로 접두사 abs 및 ID 메서드를 SHARED ACCESS SIGNATURE 사용합니다. SQL Server 2022(16.x)에 대한 새 설정을 포함하여 커넥터 및 접두사에 대한 자세한 내용은 CREATE EXTERNAL DATA SOURCE를 참조하세요.

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

예를 들어 스토리지 계정의 이름이 s3sampledata이고 컨테이너 이름이 import인 경우 코드는 다음과 같습니다.

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

OPENROWSET을 사용하여 데이터 액세스

이 예제에서는 파일 이름이 지정 call_center.csv되고 데이터가 두 번째 행에서 시작됩니다.

외부 데이터 원본 Blob_CSV 은 컨테이너 수준에 매핑되므로 컨테이너 call_center.csv 의 루트에서 호출 2022 되는 하위 폴더에 있습니다. 폴더 구조에서 파일을 쿼리하려면 외부 데이터 원본의 LOCATION 매개 변수를 기준으로 폴더 매핑을 제공합니다.

SELECT * FROM OPENROWSET
(
    BULK '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

외부 테이블을 사용하여 데이터 쿼리

CREATE EXTERNAL TABLE을 사용하여 SQL Server에서 CSV 데이터를 가상화할 수도 있습니다. 열을 정의하고 강력하게 입력해야 합니다. 외부 테이블을 만드는 데 더 많은 노력이 필요한 반면 OPENROWSET을 사용하여 외부 데이터 원본을 쿼리하는 경우 추가적인 이점도 제공합니다. 마케팅 목록의 구성원을 관리할 수 있습니다.

  • 지정된 열에 대한 데이터 입력 정의 강화
  • Null 허용 여부 정의
  • 데이터 정렬 정의
  • 쿼리 계획의 품질을 최적화하기 위해 열에 대한 통계 만들기
  • 보안 모델을 향상시키기 위해 데이터 액세스를 위해 SQL Server 내에서 보다 세분화된 모델 만들기

자세한 내용은 CREATE EXTERNAL TABLE을 참조 하세요.

다음 예제에서는 동일한 데이터 원본이 사용됩니다.

1. 외부 파일 형식 만들기

파일의 서식을 정의하려면 외부 파일 형식이 필요합니다. 재사용 가능성 때문에 외부 파일 형식도 권장됩니다.

다음 예제에서는 두 번째 행에서 데이터가 시작됩니다.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. 외부 테이블 만들기

LOCATION은 DATA_SOURCE 정의된 외부 데이터 원본의 위치 경로를 기준으로 파일의 폴더 및 파일 경로 call_center.csv 입니다. 이 경우 파일은 라는 2022하위 폴더에 있습니다. FILE_FORMAT 사용하여 SQL Server에서 외부 파일 형식의 csv_ff 경로를 지정합니다.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

관련 콘텐츠