SQL Server 2022에서 PolyBase 시작

아티클
02/09/2024

적용 대상: SQL Server 2016(13.x) - Windows 이상 버전 SQL Server 2017(14.x) - Linux 이상 버전

이 문서에서는 SQL Server 2022(16.x)에서 PolyBase를 사용하여 여러 폴더 및 파일을 사용하는 자습서를 안내합니다. 이 자습서 쿼리 집합은 PolyBase의 다양한 기능을 보여 줍니다.

SQL Server 에서 PolyBase를 사용한 데이터 가상화를 사용하면 메타데이터 파일 함수를 활용하여 여러 폴더, 파일을 쿼리하거나 폴더 제거를 수행할 수 있습니다. 스키마 검색과 폴더 및 파일 제거의 조합은 SQL이 Azure Storage 계정 또는 S3 호환 개체 스토리지 솔루션에서 필요한 데이터만 가져올 수 있는 강력한 기능입니다.

필수 조건

이 자습서에서 PolyBase를 사용하기 전에 다음을 수행해야 합니다.

Windows 에 PolyBase를 설치하거나 Linux에 PolyBase를 설치합니다.
필요한 경우 sp_configure PolyBase를 사용하도록 설정합니다.
외부 네트워크 액세스를 허용하여 공개적으로 사용 가능한 Azure Blob Storage에 pandemicdatalake.blob.core.windows.net 액세스할 수 있습니다.azureopendatastorage.blob.core.windows.net

샘플 데이터 세트

데이터 가상화에 익숙하지 않고 기능을 빠르게 테스트하려면 먼저 익명 액세스를 허용하는 Bing 코로나19 데이터 세트와 같이 Azure Open Datasets에서 사용할 수 있는 퍼블릭 데이터 세트를 쿼리합니다.

다음 엔드포인트를 사용하여 Bing 코로나19 데이터 세트를 쿼리합니다.

Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

빠른 시작을 위해 이 간단한 T-SQL 쿼리를 실행하여 데이터 집합에 대한 첫 번째 인사이트를 가져옵니다. 이 쿼리는 OPENROWSET을 사용하여 공개적으로 사용 가능한 스토리지 계정에 저장된 파일을 쿼리합니다.

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

첫 번째 쿼리의 결과 집합에 따라 다른 T-SQL 절을 추가하여 WHEREGROUP BY 데이터 집합 탐색을 계속할 수 있습니다.

SQL Server 인스턴스에서 첫 번째 쿼리가 실패하면 공용 Azure Storage 계정에 대한 네트워크 액세스가 차단될 수 있습니다. 쿼리를 진행하기 전에 네트워킹 전문가에게 문의하여 액세스를 사용하도록 설정합니다.

공용 데이터 집합 쿼리에 익숙해지면 자격 증명을 제공하고 액세스 권한을 부여하며 방화벽 규칙을 구성해야 하는 비공개 데이터 집합으로 전환하는 것이 좋습니다. 다수의 실제 시나리오에서는 주로 프라이빗 데이터 세트를 사용하여 작동합니다.

외부 데이터 원본

외부 데이터 원본은 여러 쿼리에서 파일 위치를 쉽게 참조할 수 있는 추상화입니다. 퍼블릭 위치를 쿼리하려면 외부 데이터 원본을 만드는 동안 파일 위치를 지정해야 합니다.

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

참고 항목

오류 메시지 46530 External data sources are not supported with type GENERIC, 이 표시되면 SQL Server 인스턴스의 구성 옵션을 PolyBase Enabled 검사. 값이 1이어야 합니다.

다음을 실행하여 SQL Server 인스턴스에서 PolyBase를 사용하도록 설정합니다.

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

위치와 함께 비퍼블릭 스토리지 계정에 액세스할 때 캡슐화된 인증 매개 변수를 사용하여 데이터베이스 범위 자격 증명을 참조해야 합니다. 다음 스크립트는 파일 경로를 가리키고 데이터베이스 범위 자격 증명을 참조하는 외부 데이터 원본을 만듭니다.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

OPENROWSET를 사용하여 데이터 원본 쿼리

OPENROWSET 구문을 사용하면 필요한 최소 개수의 데이터베이스 개체만 만드는 동시에 즉시 임시 쿼리를 수행할 수 있습니다.

OPENROWSET외부 파일 형식과 외부 테이블 자체가 필요한 외부 테이블 방식과 달리 외부 데이터 원본(및 자격 증명)만 만들어야 합니다.

DATA_SOURCE 매개 변수 값이 BULK 매개 변수 앞에 자동으로 추가되어 파일의 전체 경로를 형성합니다.

OPENROWSET을 사용할 경우 단일 파일을 쿼리하는 다음 예제와 같은 파일 형식을 제공합니다.

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

여러 파일 및 폴더 쿼리

또한 OPENROWSET 명령은 BULK 경로에서 와일드카드를 사용하여 여러 파일 또는 폴더를 쿼리할 수 있습니다.

다음 예제에서는 NYC 노란색 택시 여정 레코드 열기 데이터 세트를 사용합니다.

먼저 외부 데이터 원본을 만듭니다.

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

이제 폴더에서 .parquet 확장자를 사용하여 모든 파일을 쿼리할 수 있습니다. 예를 들어 여기서는 이름 패턴과 일치하는 파일만 쿼리합니다.

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

여러 파일 또는 폴더를 쿼리할 때 단일 OPENROWSET로 액세스하는 모든 파일은 동일한 구조(예: 동일한 수의 열 및 데이터 형식)를 가져야 합니다. 폴더는 재귀적으로 트래버스할 수 없습니다.

스키마 유추

자동 스키마 유추는 파일 스키마를 알지 못하는 경우 신속하게 쿼리를 작성하고 데이터를 탐색하는 데 도움을 줍니다. 스키마 유추는 parquet 파일에서만 작동합니다.

원본 파일에 적절한 데이터 형식이 사용되도록 충분한 정보가 있을 수 있으므로 유추된 데이터 형식은 편리하지만 실제 데이터 형식보다 클 수 있습니다. 이로 인해 쿼리 성능이 저하 될 수 있습니다. 예를 들어 parquet 파일에는 최대 문자 열 길이에 대한 메타데이터가 포함되어 있지 않으므로 인스턴스는 이를 varchar(8000)로 유추합니다.

저장 프로시저를 sys.sp_describe_first_results_set 사용하여 다음 예제와 같이 쿼리의 결과 데이터 형식을 검사.

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

데이터 형식을 알고 나면 WITH 절을 사용하여 데이터 형식을 지정하여 성능을 향상시킬 수 있습니다.

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

CSV 파일의 스키마를 자동으로 확인할 수 없으므로 항상 WITH 절을 사용하여 열을 지정해야 합니다.

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

파일 메타데이터 함수

여러 파일 또는 폴더를 쿼리할 때 파일 메타데이터를 읽고 결과 집합의 행이 시작되는 파일의 경로 또는 전체 경로 및 이름의 일부를 가져오는 데 사용할 filepath()filename() 수 있습니다. 다음 예제에서는 각 행에 대한 모든 파일 및 프로젝트 파일 경로 및 파일 이름 정보를 쿼리합니다.

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

매개 변수 없이 호출되면 filepath() 함수는 행이 시작되는 파일 경로를 반환합니다. DATA_SOURCE가 OPENROWSET에 사용되는 경우 DATA_SOURCE에 해당하는 경로를 반환하고, 그렇지 않으면 전체 파일 경로를 반환합니다.
매개 변수를 사용하여 호출되는 경우 함수는 filepath() 매개 변수에 지정된 위치에서 wild카드 일치하는 경로의 일부를 반환합니다. 예를 들어 첫 번째 매개 변수 값은 첫 번째 wild카드 일치하는 경로의 일부를 반환합니다.

filepath() 함수는 행을 필터링하고 집계하는 데도 사용할 수 있습니다.

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

OPENROWSET 위에 보기 만들기

기본 쿼리를 쉽게 다시 사용할 수 있도록 뷰 를 만들어 쿼리를 래핑 OPENROWSET 할 수 있습니다. 또한 뷰를 사용하면 Power BI와 같은 보고 및 분석 도구에서 OPENROWSET의 결과를 사용할 수 있습니다.

예를 들어 명령에 따라 OPENROWSET 다음 보기를 고려합니다.

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

또한 더 쉽고 성능이 좋은 필터링을 위해 filepath() 함수를 사용하여 파일 위치 데이터가 있는 열을 보기에 추가하는 것이 편리합니다. 뷰를 사용하면 파일 수와 뷰 위에 있는 쿼리가 해당 열로 필터링될 때 읽고 처리해야 하는 데이터의 양을 줄일 수 있습니다.

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

외부 테이블

외부 테이블은 사용자 테이블에 저장된 로컬 관계형 데이터를 쿼리하는 것과 거의 동일한 쿼리 환경을 만드는 파일에 대한 액세스를 캡슐화합니다. 외부 테이블을 만들려면 외부 데이터 원본 및 외부 파일 형식 개체가 있어야 합니다.

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

외부 테이블이 만들어지면 다른 테이블과 마찬가지로 쿼리할 수 있습니다.

SELECT TOP 10 * 
FROM tbl_TaxiRides;

OPENROWSET과 마찬가지로 외부 테이블에서는 wild카드를 사용하여 여러 파일 및 폴더를 쿼리할 수 있습니다. 스키마 유추는 외부 테이블에서 지원되지 않습니다.

외부 데이터 원본

다양한 데이터 원본에 외부 데이터 원본 및 외부 테이블을 만드는 방법에 대한 자세한 자습서는 PolyBase Transact-SQL 참조를 참조하세요.

다양한 외부 데이터 원본에 대한 자세한 자습서는 다음을 검토하세요.

SQL Server 2022에서 PolyBase 시작

필수 조건

샘플 데이터 세트

외부 데이터 원본

OPENROWSET를 사용하여 데이터 원본 쿼리

여러 파일 및 폴더 쿼리

스키마 유추

파일 메타데이터 함수

OPENROWSET 위에 보기 만들기

외부 테이블

외부 데이터 원본

피드백

피드백

추가 리소스

SQL Server 2022에서 PolyBase 시작

필수 조건

샘플 데이터 세트

외부 데이터 원본

OPENROWSET를 사용하여 데이터 원본 쿼리

여러 파일 및 폴더 쿼리

스키마 유추

파일 메타데이터 함수

OPENROWSET 위에 보기 만들기

외부 테이블

외부 데이터 원본

관련 콘텐츠

피드백

피드백

추가 리소스