서버리스 SQL 풀을 사용하여 파일 쿼리

  • 10분

서버리스 SQL 풀을 사용하여 다음과 같은 다양한 일반적인 파일 형식으로 데이터 파일을 쿼리할 수 있습니다.

  • CSV(쉼표로 구분된 값) 파일과 같은 구분된 텍스트.
  • JSON(Javascript Object Notation) 파일.
  • Parquet 파일.

쿼리에 대한 기본 구문은 이러한 모든 형식의 파일에 대해 동일하며 OPENROWSET SQL 함수를 기반으로 빌드됩니다. 이 함수는 하나 이상의 파일에 있는 데이터에서 테이블 형식 행 집합을 생성합니다. 예를 들어 다음 쿼리를 사용하여 CSV 파일에서 데이터를 추출할 수 있습니다.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

OPENROWSET 함수에는 다음과 같은 요소를 결정하는 더 많은 매개 변수가 포함됩니다.

  • 결과 행 집합의 스키마
  • 구분된 텍스트 파일에 대한 추가 서식 옵션입니다.

팁

OPENROWSET 함수에 대한 전체 구문은 Azure Synapse Analytics 설명서에서 찾을 수 있습니다.

OPENROWSET의 출력은 별칭을 할당해야 하는 행 집합입니다. 이전 예제에서 별칭 행은 결과 행 집합의 이름을 지정하는 데 사용됩니다.

BULK 매개 변수에는 데이터 파일이 포함된 데이터 레이크의 위치에 대한 전체 URL이 포함됩니다. 개별 파일이거나 포함해야 하는 파일 형식을 필터링하기 위한 와일드카드 식이 있는 폴더일 수 있습니다. FORMAT 매개 변수는 쿼리되는 데이터의 형식을 지정합니다. 위의 예제에서는 files 폴더의 모든 .csv 파일에서 구분된 텍스트를 읽습니다.

참고

이 예제에서는 사용자가 기본 저장소의 파일에 액세스할 수 있다고 가정합니다. 파일이 SAS 키 또는 사용자 지정 ID로 보호되는 경우 서버 범위 자격 증명을 만들어야 합니다.

이전 예제와 같이 BULK 매개 변수의 와일드카드를 사용하여 쿼리에 파일을 포함하거나 제외할 수 있습니다. 다음 목록에서는 이를 사용하는 방법에 대한 몇 가지 예를 보여 줍니다.

  • https://mydatalake.blob.core.windows.net/data/files/file1.csv: files 폴더의 file1.csv만 포함합니다.
  • https://mydatalake.blob.core.windows.net/data/files/file*.csv: “file”로 시작하는 이름의 files 폴더에 있는 모든 .csv 파일입니다.
  • https://mydatalake.blob.core.windows.net/data/files/*: files 폴더의 모든 파일입니다.
  • https://mydatalake.blob.core.windows.net/data/files/**: files 폴더와 재귀적으로 그 하위 폴더에 있는 모든 파일입니다.

BULK 매개 변수에 여러 파일 경로를 지정하여 각 경로를 쉼표로 구분할 수도 있습니다.

구분된 텍스트 파일 쿼리

구분된 텍스트 파일은 많은 비즈니스 내에서 일반적인 파일 형식입니다. 구분된 파일에 사용되는 특정 서식은 다음 예제와 같이 다를 수 있습니다.

  • 헤더 행 포함 또는 제외
  • 쉼표 및 탭으로 구분된 값
  • Windows 및 Unix 스타일 줄 끝
  • 따옴표가 붙지 않은 값 및 따옴표가 붙은 값과 문자 이스케이프

사용 중인 구분된 파일의 형식에 관계없이 csv FORMAT 매개 변수 및 데이터에 대한 특정 서식 세부 정보를 처리하는 데 필요한 다른 매개 변수와 함께 OPENROWSET 함수를 사용하여 데이터를 읽을 수 있습니다. 예를 들면 다음과 같습니다.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION은 쿼리가 파일에 사용되는 텍스트 인코딩을 해석하는 방법을 결정하는 데 사용됩니다. 버전 1.0은 기본값이며 다양한 파일 인코딩을 지원하며, 버전 2.0은 지원하는 인코딩 수는 더 적지만 성능은 더 좋습니다. FIRSTROW 매개 변수는 텍스트 파일의 행을 건너뛰거나, 구조화되지 않은 프리앰블 텍스트를 제거하거나, 열 머리글이 포함된 행을 무시하는 데 사용됩니다.

구분된 텍스트 파일로 작업할 때 필요할 수 있는 추가 매개 변수는 다음과 같습니다.

  • FIELDTERMINATOR - 각 행의 필드 값을 구분하는 데 사용되는 문자입니다. 예를 들어 탭으로 구분된 파일은 TAB(\t) 문자로 필드를 구분합니다. 기본 필드 종결자는 쉼표(,)입니다.
  • ROWTERMINATOR - 데이터 행의 끝을 나타내는 데 사용되는 문자입니다. 예를 들어 표준 Windows 텍스트 파일은 코드 \n로 표시되는 CR(캐리지 리턴) 및 LF(줄 바꿈)의 조합을 사용하는 반면, UNIX 스타일 텍스트 파일은 코드 0x0a를 사용하여 표시할 수 있는 단일 줄 바꿈 문자를 사용합니다.
  • FIELDQUOTE - 따옴표 붙은 문자열 값을 묶는 데 사용되는 문자입니다. 예를 들어 주소 필드 값 126 Main St, apt 2의 쉼표가 필드 구분 기호로 해석되지 않도록 하려면 전체 필드 값을 “126 Main St, apt 2”와 같은 따옴표로 묶을 수 있습니다. 큰따옴표(“)는 기본 필드 따옴표 문자입니다.

팁

구분된 텍스트 파일로 작업할 때 추가 매개 변수에 대한 자세한 내용은 Azure Synapse Analytics 설명서를 참조하세요.

행 집합 스키마 지정

구분된 텍스트 파일에는 열 이름이 첫 번째 행에 포함되는 것이 일반적입니다. OPENROWSET 함수는 이를 사용하여 결과 행 집합에 대한 스키마를 정의하고 포함된 값에 따라 열의 데이터 형식을 자동으로 유추할 수 있습니다. 예를 들어 다음 구분된 텍스트를 생각해보겠습니다.

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

데이터는 다음 세 개의 열로 구성됩니다.

  • product_id (정수)
  • product_name (문자열)
  • list_price (10진수)

다음 쿼리를 사용하여 올바른 열 이름과 적절하게 유추된 SQL Server 데이터 형식(이 경우 INT, NVARCHAR 및 DECIMAL)을 가진 데이터를 추출할 수 있습니다.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

파서 버전 2.0을 사용하는 경우에만 사용할 수 있는 HEADER_ROW 매개 변수는 다음과 같이 각 파일의 첫 번째 데이터 행을 열 이름으로 사용하도록 쿼리 엔진에 지시합니다.

product_id product_name list_price
123 위젯 12.9900
124 가젯 3.9900

이제 다음 데이터를 생각해보겠습니다.

123,Widget,12.99
124,Gadget,3.99

이번에는 파일의 머리글 행에 열 이름이 포함되지 않습니다. 따라서 데이터 형식을 유추할 수 있지만 열 이름은 C1, C2, C3 등으로 설정됩니다.

C1 C2 C3
123 위젯 12.9900
124 가젯 3.9900

명시적 열 이름 및 데이터 형식을 지정하려면 다음과 같이 WITH 절에 스키마 정의를 제공하여 기본 열 이름 및 유추된 데이터 형식을 재정의할 수 있습니다.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

이 쿼리는 예상된 결과를 생성합니다.

product_id product_name list_price
123 위젯 12.99
124 가젯 3.99

팁

텍스트 파일을 사용할 때 서버리스 SQL 풀의 마스터 데이터베이스에서 사용되는 데이터 정렬 및 UTF-8로 인코딩된 데이터와 호환되지 않는 경우가 있습니다. 이를 극복하기 위해 스키마의 개별 VARCHAR 열에 대해 호환되는 데이터 정렬을 지정할 수 있습니다. 자세한 내용은 문제 해결 지침을 참조하세요.

JSON 파일 쿼리

JSON은 REST 인터페이스를 통해 데이터를 교환하거나 Azure Cosmos DB와 같은 NoSQL 데이터 저장소를 사용하는 웹 애플리케이션에 널리 사용되는 형식입니다. 따라서 분석을 위해 데이터 레이크의 파일에 JSON 문서로 데이터를 유지하는 것은 드문 일이 아닙니다.

예를 들어 개별 제품을 정의하는 JSON 파일은 다음과 같을 수 있습니다.

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

이 형식의 여러 JSON 파일이 포함된 폴더에서 제품 데이터를 반환하려면 다음 SQL 쿼리를 사용할 수 있습니다.

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET에는 JSON 파일에 대한 특정 형식이 없으므로 FIELDTERMINATOR, FIELDQUOTE 및 ROWTERMINATOR가 0x0b로 설정된 csv 형식과 단일 NVARCHAR(MAX) 열이 포함된 스키마를 사용해야 합니다. 이 쿼리의 결과는 다음과 같이 JSON 문서의 단일 열을 포함하는 행 집합입니다.

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

JSON에서 개별 값을 추출하려면 다음과 같이 SELECT 문에서 JSON_VALUE 함수를 사용할 수 있습니다.

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

이 쿼리는 다음 결과와 유사한 행 집합을 반환합니다.

product price
위젯 12.99
가젯 3.99

Parquet 파일 쿼리

Parquet는 분산 파일 스토리지에서 빅 데이터 처리에 일반적으로 사용되는 형식이며 압축 및 분석 쿼리에 최적화된 효율적인 데이터 형식입니다.

대부분의 경우 데이터의 스키마는 Parquet 파일 내에 포함되므로 다음과 같이 읽으려는 파일의 경로를 포함한 BULK 매개 변수와 parquet의 FORMAT 매개 변수만 지정하면 됩니다.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

분할된 데이터 쿼리

데이터 레이크에서는 파티셔닝 기준을 반영하는 하위 폴더의 여러 파일 간에 분할하여 데이터를 파티셔닝하는 것이 일반적입니다. 이렇게 하면 분산 처리 시스템이 데이터의 여러 파티션에서 병렬로 작동하거나 필터링 조건에 따라 특정 폴더에서 데이터 읽기를 쉽게 제거할 수 있습니다. 예를 들어 판매 주문 데이터를 효율적으로 처리해야 하며 주문이 발생한 연도 및 월을 기준으로 필터링해야 하는 경우가 많다고 가정해 봅시다. 다음과 같이 폴더를 사용하여 데이터를 파티션할 수 있습니다.

  • /orders
    • /year=2020
      • /month=1
        • /01012020.parquet
        • /02012020.parquet
        • ...
      • /month=2
        • /01022020.parquet
        • /02022020.parquet
        • ...
      • ...
    • /year=2021
      • /month=1
        • /01012021.parquet
        • /02012021.parquet
        • ...
      • ...

2020년 1월과 2월의 주문만 포함하도록 결과를 필터링하는 쿼리를 만들려면 다음 코드를 사용할 수 있습니다.

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

WHERE 절의 번호가 매겨진 filepath 매개 변수는 BULK 경로의 폴더 이름에 있는 와일드카드를 참조하므로 매개 변수 1은 year=* 폴더 이름의 *이고 매개 변수 2는 month=* 폴더 이름의 *입니다.