Azure Synapse Analytics에서 서버리스 SQL 풀을 사용하여 Parquet 파일 쿼리

아티클
10/18/2023

이 문서에서는 Parquet 파일을 읽을 서버리스 SQL 풀을 사용하여 쿼리를 작성하는 방법을 알아봅니다.

빠른 시작 예제

OPENROWSET 함수를 사용하면 파일에 URL을 제공하여 parquet 파일의 내용을 읽을 수 있습니다.

Parquet 파일 읽기

파일의 PARQUET 내용을 확인하는 가장 쉬운 방법은 작동할 파일 URL을 제공하고 parquet을 지정하는 OPENROWSET 것입니다 FORMAT. 파일을 공개적으로 사용할 수 있거나 Microsoft Entra ID가 이 파일에 액세스할 수 있는 경우 다음 예제와 같이 쿼리를 사용하여 파일의 내용을 볼 수 있어야 합니다.

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

이 파일에 액세스할 수 있는지 확인합니다. 파일이 SAS 키 또는 사용자 지정 Azure ID로 보호되는 경우 sql 로그인에 대한 서버 수준 자격 증명을 설정해야 합니다.

Important

PARQUET 파일의 문자열 값은 UTF-8 인코딩을 사용하여 인코딩되므로 UTF-8 데이터베이스 데이터 정렬(예 Latin1_General_100_BIN2_UTF8: )을 사용하고 있는지 확인합니다. PARQUET 파일의 텍스트 인코딩과 데이터 정렬이 일치하지 않을 경우 예기치 않은 변환 오류가 발생할 수 있습니다. 다음 T-SQL 문을 사용하여 현재 데이터베이스의 기본 데이터 정렬을 쉽게 변경할 수 있습니다. ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;데이터 정렬에 대한 자세한 내용은 Synapse SQL에 지원되는 데이터 정렬 형식을 참조하세요.

Latin1_General_100_BIN2_UTF8 데이터 정렬을 사용하면 다른 데이터 정렬에 비해 성능이 추가로 향상됩니다. Latin1_General_100_BIN2_UTF8 데이터 정렬은 parquet 문자열 정렬 규칙과 호환됩니다. SQL 풀은 쿼리에 필요한 데이터를 포함하지 않는 parquet 파일의 일부 부분을 제거할 수 있습니다(파일/열-세그먼트 정리). 다른 데이터 정렬을 사용하는 경우 parquet 파일의 모든 데이터가 Synapse SQL로 로드되고 SQL 프로세스 내에서 필터링이 발생합니다. 데이터 정렬에는 Latin1_General_100_BIN2_UTF8 parquet 및 Cosmos DB에 대해서만 작동하는 추가 성능 최적화가 있습니다. 단점은 대/소문자 구분 안함과 같은 세분화된 비교 규칙을 잃게 된다는 것입니다.

데이터 원본 사용

이전 예제에서는 파일에 전체 경로를 사용했습니다. 또는 스토리지의 루트 폴더를 가리키는 위치를 사용하여 외부 데이터 원본을 만들고 해당 데이터 원본과 함수의 파일에 OPENROWSET 대한 상대 경로를 사용할 수 있습니다.

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

데이터 원본이 SAS 키 또는 사용자 지정 ID를 통해 보호되는 경우, 데이터베이스 범위 자격 증명을 사용하여 데이터 원본을 구성할 수 있습니다.

명시적으로 스키마 지정

OPENROWSET을 사용하면 WITH 절을 사용하여 파일에서 읽을 열을 명시적으로 지정할 수 있습니다.

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Important

WITH 절의 모든 문자열 열에 대해 UTF-8 데이터 정렬(예: Latin1_General_100_BIN2_UTF8)을 명시적으로 지정하거나 데이터베이스 수준에서 UTF-8 데이터 정렬을 설정합니다. 파일의 텍스트 인코딩과 문자열 열 데이터 정렬이 일치하지 않을 경우 예기치 않은 변환 오류가 발생할 수 있습니다. 다음 T-SQL 문을 사용하여 현재 데이터베이스의 기본 데이터 정렬을 쉽게 변경할 수 있습니다. ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; 예를 들어 콜럼 형식에 대한 데이터 정렬을 쉽게 설정할 수 있습니다. geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8데이터 정렬에 대한 자세한 내용은 Synapse SQL에 지원되는 데이터 정렬 형식을 참조하세요.

다음 섹션에서는 다양한 유형의 PARQUET 파일을 쿼리하는 방법을 확인할 수 있습니다.

필수 조건

첫 번째 단계는 NYC Yellow Taxi 스토리지 계정을 참조하는 데이터 원본을 사용하여 데이터베이스를 만드는 것입니다. 그런 다음 해당 데이터베이스에서 설치 스크립트를 실행하여 개체를 초기화합니다. 이 설치 스크립트는 이러한 샘플에서 사용되는 데이터 원본, 데이터베이스 범위 자격 증명 및 외부 파일 형식을 만듭니다.

데이터 세트

NYC Yellow Taxi 데이터 세트는 이 샘플에서 사용됩니다. CSV 파일을 읽는 것과 동일한 방식으로 Parquet 파일을 쿼리할 수 있습니다. 유일한 차이점은 FILEFORMAT 매개 변수를 PARQUET로 설정해야 한다는 점입니다. 이 문서의 예제에서는 Parquet 파일 읽기의 세부 정보를 보여줍니다.

Parquet 파일의 쿼리 집합

Parquet 파일을 쿼리할 때 관심 있는 열만 지정할 수 있습니다.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

자동 스키마 유추

Parquet 파일을 읽을 때 OPENROWSET WITH 절을 사용할 필요가 없습니다. 열 이름 및 데이터 형식은 Parquet 파일에서 자동으로 읽습니다.

한 번에 파일 수를 읽는 경우 스키마, 열 이름 및 데이터 형식은 스토리지에서 가져오는 첫 번째 파일 서비스에서 유추됩니다. 이는 서비스가 스키마를 정의하는 데 사용하는 파일에 이러한 열이 포함되지 않았기 때문에 예상되는 열 중 일부가 생략되었음을 의미할 수 있습니다. 스키마를 명시적으로 지정하려면 OPENROWSET WITH 절을 사용하세요.

다음 샘플에서는 Parquet 파일에 대한 자동 스키마 유추 기능을 보여줍니다. 스키마를 지정하지 않고 2018년 9월의 행 수를 반환합니다.

참고 항목

Parquet 파일을 읽을 때 OPENROWSET WITH 절에 열을 지정할 필요가 없습니다. 이 경우 서버리스 SQL 풀 쿼리 서비스는 Parquet 파일의 메타데이터를 활용하고 이름으로 열을 바인딩합니다.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

분할된 데이터 쿼리

이 샘플에 제공된 데이터 집합은 별도의 하위 폴더로 분할(분할)됩니다. Filepath 함수를 사용하여 특정 파티션을 대상으로 지정할 수 있습니다. 이 예제에서는 2017년 첫 3개월 동안의 년, 월 및 payment_type별 요금을 보여줍니다.

참고

서버리스 SQL 풀 쿼리는 Hive/Hadoop 파티션 구성표와 호환됩니다.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

형식 매핑

Parquet 형식 매핑의 경우 Parquet에 대한 SQL 네이티브 형식 검사 형식 매핑입니다.

다음 단계

Parquet 중첩 형식을 쿼리하는 방법을 알아보려면 다음 문서로 진행합니다.