다음을 통해 공유

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

서버리스 SQL 풀을 사용하면 데이터 레이크에서 데이터를 쿼리할 수 있습니다. 반구조화 및 비구조화 데이터 쿼리를 수용하는 T-SQL(Transact-SQL) 쿼리 노출 영역을 제공합니다. 쿼리에 지원되는 T-SQL 측면은 다음과 같습니다.

현재 지원되는 사항과 지원되지 않는 사항에 대한 자세한 내용은 서버리스 SQL 풀 개요 또는 다음 문서를 참조합니다.

개요

Azure Storage 파일에 있는 데이터에 대한 현재 위치 쿼리를 원활하게 지원하기 위해 서버리스 SQL 풀은 더 많은 기능을 갖춘 OPENROWSET 함수를 사용합니다.

PARQUET 파일 쿼리

Parquet 원본 데이터를 쿼리하려면 FORMAT = 'PARQUET'를 사용합니다.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

사용 예는 Parquet 파일 쿼리를 참조하세요.

CSV 파일 쿼리

CSV 원본 데이터를 쿼리하려면 FORMAT = 'CSV'를 사용합니다. CSV 파일을 쿼리할 때 OPENROWSET 함수의 일부로 CSV 파일의 스키마를 지정할 수 있습니다.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

구문 분석 규칙을 사용자 지정 CSV 형식으로 조정하는 데 사용할 수 있는 몇 가지 추가 옵션이 있습니다.

  • ESCAPE_CHAR = 'char' 자체 및 파일의 모든 구분 기호 값을 이스케이프하는 데 사용되는 파일의 문자를 지정합니다. 이스케이프 문자 뒤에 자체 또는 구분 기호 값 이외의 값이 있으면 값을 읽을 때 이스케이프 문자가 삭제됩니다. ESCAPE_CHAR 매개 변수는 FIELDQUOTE가 사용하도록 설정되어 있든 아니든 적용됩니다. 따옴표 문자를 이스케이프하는 데 사용되지 않습니다. 따옴표 문자는 다른 따옴표 문자로 이스케이프해야 합니다. 따옴표로 묶은 문자는 값이 따옴표 문자로 캡슐화된 경우에만 열 값 내에 나타날 수 있습니다.
  • FIELDTERMINATOR ='field_terminator' 사용할 필드 종결자를 지정합니다. 기본 필드 종결자는 쉼표(,")입니다.
  • ROWTERMINATOR ='row_terminator' 사용할 행 종결자를 지정합니다. 기본 행 종결자는 줄 바꿈 문자(\r\n)입니다.

DELTA LAKE 형식 쿼리

Delta Lake 원본 데이터를 쿼리하려면 FORMAT = 'DELTA'를 사용하고 Delta Lake 파일이 포함된 루트 폴더를 참조하세요.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

루트 폴더에는 _delta_log라는 하위 폴더가 있어야 합니다. 사용 예는 Delta Lake(v1) 파일 쿼리를 참조하세요.

파일 스키마

Synapse SQL의 SQL 언어를 사용하면 OPENROWSET 함수의 일부로 파일의 스키마를 정의하고 모든 열이나 하위 집합의 열을 읽을 수 있으며, 스키마 유추를 사용하여 파일에서 열 형식을 자동으로 판별하려고 시도합니다.

선택한 열 하위 세트 읽기

읽으려는 열을 지정하려면 OPENROWSET 문 내에 선택적 WITH 절을 제공할 수 있습니다.

  • CSV 데이터 파일이 있는 경우 모든 열을 읽을 수 있도록 열 이름과 데이터 형식을 제공합니다. 열의 하위 세트를 원하는 경우 서수를 사용하여 원본 데이터 파일에서 서수를 기준으로 열을 선택합니다. 열은 순서형 지정에 의해 제한됩니다.
  • Parquet 데이터 파일이 있는 경우 원본 데이터 파일의 열 이름과 일치하는 열 이름을 제공합니다. 열은 이름으로 바인딩됩니다.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

열마다 WITH 절에서 열 이름과 형식을 지정해야 합니다. 샘플은 모든 열을 지정하지 않고 CSV 파일 읽기를 참조하세요.

스키마 유추

OPENROWSET 문에서 WITH 절을 생략하면 서비스가 기본 파일에서 스키마를 자동으로 검색(유추)하도록 지시할 수 있습니다.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

최적의 성능을 위해 적절한 유추된 데이터 형식이 사용되는지 확인합니다.

여러 파일 또는 폴더 쿼리

폴더 또는 폴더 세트 내의 파일 세트를 단일 엔터티 또는 행 세트로 처리하면서 이에 대해 T-SQL 쿼리를 실행하려면 파일 또는 폴더 세트에 대한 폴더 또는 패턴(와일드카드 사용)의 경로를 제공합니다.

다음 규칙이 적용됩니다.

  • 패턴은 디렉터리 경로의 일부 또는 파일 이름에 나타날 수 있습니다.
  • 여러 패턴이 동일한 디렉터리 단계 또는 파일 이름에 나타날 수 있습니다.
  • 와일드카드가 여러 개 있는 경우 일치하는 모든 경로 내의 파일이 결과 파일 집합에 포함됩니다.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

사용 예는 폴더 및 여러 파일 쿼리를 참조하세요.

파일 메타데이터 함수

filename 함수

이 함수는 행이 제공되는 파일 이름을 반환합니다.

특정 파일을 쿼리하려면 특정 파일 쿼리 문서의 filename 섹션을 참조하세요.

반환 데이터 형식은 nvarchar(1024)입니다. 성능을 최적화하려면 항상 filename 함수의 결과를 적절한 데이터 형식으로 캐스팅합니다. 문자 데이터 형식을 사용하는 경우 적절한 길이가 사용되는지 확인합니다.

filepath 함수

이 함수는 전체 경로 또는 경로의 일부를 반환합니다.

  • 매개 변수 없이 호출하면 행이 제공되는 파일의 전체 경로를 반환합니다.
  • 매개 변수를 사용하여 호출하면 매개 변수에 지정된 위치에 있는 와일드카드와 일치하는 경로의 일부를 반환합니다. 예를 들어 1의 매개 변수 값은 첫 번째 와일드카드와 일치하는 경로의 일부를 반환합니다.

자세한 내용은 특정 파일 쿼리 문서의 filepath 섹션을 참조하세요.

반환 데이터 형식은 nvarchar(1024)입니다. 최적의 성능을 위해서는 항상 filepath 함수의 결과를 적절한 데이터 형식으로 변환합니다. 문자 데이터 형식을 사용하는 경우 적절한 길이가 사용되는지 확인합니다.

복합 형식 및 중첩되거나 반복되는 데이터 구조 작업

Parquet 파일과 같이 중첩되거나 반복되는 데이터 형식에 저장된 데이터를 원활하게 사용할 수 있도록 하기 위해 Serverless SQL 풀에 다음 확장 기능이 추가되었습니다.

중첩되거나 반복되는 데이터 프로젝션

데이터를 프로젝션하려면 중첩된 데이터 형식의 열이 포함된 Parquet 파일에 대해 SELECT 문을 실행합니다. 출력 시 중첩된 값은 JSON으로 직렬화되어 varchar(8000) SQL 데이터 형식으로 반환됩니다.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

자세한 내용은 쿼리 Parquet 중첩 형식 문서의 중첩 또는 반복 데이터 프로젝트 섹션을 참조하세요.

중첩된 열의 요소에 액세스

Struct와 같은 중첩된 열의 중첩된 요소에 액세스하려면 점 표기법을 사용하여 필드 이름을 경로에 연결합니다. OPENROWSET 함수의 WITH 절에 column_name 경로를 제공합니다.

구문 조각 예제는 다음과 같습니다.

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

기본적으로 OPENROWSET 함수는 원본 필드 이름과 경로를 WITH 절에 제공된 열 이름과 일치합니다. 동일한 원본 Parquet 파일 내의 서로 다른 중첩 수준에 포함된 요소는 WITH 절을 사용하여 액세스할 수 있습니다.

반환 값

  • 이 함수는 지정된 요소에서 지정된 경로에 대해 중첩 형식 그룹에 없는 모든 Parquet 형식에 대해 int, decimal, varchar와 같은 스칼라 값을 반환합니다.
  • 경로가 중첩 형식의 요소를 가리키는 경우 함수에서 지정된 경로의 최상위 요소에서 시작하는 JSON 조각을 반환합니다. JSON 조각은 varchar(8000) 형식입니다.
  • 지정된 column_name에서 속성을 찾을 수 없는 경우 함수는 오류를 반환합니다.
  • 지정된 column_path에서 속성을 찾을 수 없는 경우, 경로 모드에 따라 함수는 strict 모드에서는 오류를 반환하고 lax 모드에서는 null을 반환합니다.

쿼리 샘플은 쿼리 Parquet 중첩 형식 문서의 중첩된 개체 열에서 속성 읽기 섹션을 참조하세요.

반복된 열의 요소에 액세스

Array 또는 Map의 요소와 같은 반복된 열의 요소에 액세스하려면 프로젝션하여 제공해야 하는 모든 스칼라 요소에 JSON_VALUE 함수를 사용합니다.

  • 첫 번째 매개 변수로 중첩되거나 반복되는 열
  • 두 번째 매개 변수로 액세스할 요소 또는 속성을 지정하는 JSON 경로

반복되는 열에서 비스칼라 요소에 액세스하려면 프로젝션해야 하는 모든 비스칼라 요소에 대해 JSON_QUERY 함수를 사용하고 다음을 제공합니다.

  • 첫 번째 매개 변수로 중첩되거나 반복되는 열
  • 두 번째 매개 변수로 액세스할 요소 또는 속성을 지정하는 JSON 경로

다음 구문 조각을 참조하세요.

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Parquet 중첩 형식 쿼리 문서에서 반복된 열의 요소에 액세스하는 쿼리 샘플을 찾을 수 있습니다.

관련 콘텐츠

다른 파일 형식을 쿼리하고, 보기를 만들고 사용하는 방법에 대한 자세한 내용은 다음 문서를 참조하세요.