[DP-203] Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 분석 솔루션 빌드 : Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 레이크의 파일 쿼리

야채곱창의축복 2024. 10. 21. 15:51

2024. 10. 21. 15:51

출처 : https://learn.microsoft.com/ko-kr/training/paths/build-data-analytics-solutions-using-azure-synapse-serverless-sql-pools/

Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 분석 솔루션 빌드 - Training

Azure Synapse 서버리스 SQL 풀을 사용하여 데이터 분석 솔루션 빌드

learn.microsoft.com

소개

- Azure Synapse Analytics에는 데이터 레이크의 데이터 쿼리에 맞게 조정된 서버리스 SQL 풀이 포함되어 있음.

- 서버리스 SQL 풀을 사용하면 데이터베이스 스토리지에 파일 데이터를 로드할 필요 없이 SQL 코드를 사용하여 다양한 일반적인 형식의 파일에서 데이터를 쿼리할 수 있음.

- 이 기능은 데이터 분석가와 데이터 엔지니어가 관계형 데이터베이스 저장소를 만들거나 유지 관리할 필요 없이 친숙한 데이터 처리 언어를 사용하여 데이터 레이크에서 파일 데이터를 분석하고 처리하도록 지원

Azure Synapse 서버리스 SQL 풀 기능 및 사용 사례 이해

- Azure Synapse Analytics는 데이터를 대규모로 처리하고 분석하기 위해 일반적으로 사용되는 다양한 기술을 통합한 통합 분석 서비스

[Azure Synapse Analytics의 서버리스 SQL 풀]

- Azure Synapse SQL은 두 종류의 런타임 환경을 제공하는 Azure Synapse Analytics의 분산 쿼리 시스템

서버리스 SQL 풀 : 주문형 SQL 쿼리 처리, 주로 데이터 레이크의 데이터 작업에 사용됨.
전용 SQL 풀 : 엔터프라이즈 스케일의 관계형 데이터베이스 인스턴스로 데이터가 관계형 테이블로 저장되는 데이터 웨어하우스를 호스트하는데 사용됨.

- 이 모듈에서는 데이터 레이크의 데이터를 쿼리하기 위한 쿼리당 종량제 엔드포인트를 제공하는 서버리스 SQL 풀에 초점을 맞춘다. 서버리스 SQL 풀을 사용할 경우의 이점은 다음과 같다.

데이터를 특수화된 저장소에 복사하거나 로드할 필요 없이 데이터를 적절히 쿼리할 수 있는 친숙한 Transact-SQL 구문
가장 인기 있는 드라이버를 포함하여 광범위한 비즈니스 인텔리전스 및 임시 쿼리 도구에서의 통합 연결
대규모 데이터 및 계산 함수를 위해 빌드된 분산 쿼리 처리로 인해 쿼리 성능이 빨라짐
기본 제공 쿼리 실행 내결함성?을 통해 대규모 데이터 세트와 관련된 장기 실행 쿼리에도 높은 안정성과 성공률을 제공
설정할 인프라나 유지 관리할 클러스터가 없음. 이 서비스에 대한 기본 제공 엔드포인트가 모든 Azure Synapse 작업 영업 내에 제공되므로 작업 영역이 만들어지는 즉시 데이터 쿼리를 시작할 수 있음
예약된 리소스에 대해서는 요금이 부과되지 않으며, 실행하는 쿼리에 의해 처리된 데이터에 대해서만 요금이 부과

[서버리스 SQL 풀을 사용하는 경우]

- 서버리스 SQL풀은 데이터 레이크에 있는 데이터를 쿼리하도록 조정되어 있으므로 관리 부담을 없앨 뿐 아니라 시스템으로 데이터를 수집하는 것에 대해 걱정할 필요가 없음 (...)

- 이미 레이크에 있는 데이터로 쿼리를 지정하고 실행하기만 하면 됨.

- Synapse SQL 서버리스 리소스 모델은 Azure Synapse Analytics 작업 영역에서 Always On 서버리스 SQL 엔드포인트를 사용하여 처리할 수 있는 계획되지 않거나 "한 차례씩 발생하는" 워크로드에 적합

- 서버리스 풀을 사용하면 비용을 모니터링하고 특성을 지정하기 위해 실행되는 각 쿼리의 정확한 비용을 알아야 하는 경우 유용

참고)

서버리스 SQL 풀은 분석 시스템이므로 애플리케이션에서 트랜잭션 데이터를 저장하는데 사용하는 데이터베이스와 같은 OLTP 워크로드에는 권장되지 않는다. 밀리초의 응답 시간이 필요하고 데이터 세트의 단일 행을 정확히 찾아내려는 워크로드는 서버리스 SQL 풀에 적합하지 않는다.

- 서버리스 SQL 풀의 일반적인 사용 사례는 다음과 같다.

데이터 탐색 : 데이터 탐색에는 데이터 레이크를 찾아보고 데이터에 대한 초기 인사이트를 얻는 작업이 수반되며 Azure Synapse Studio를 통해 쉽게 수행할 수 있다. 연결된 데이터 레이크 스토리지의 파일을 찾아보고 기본 제공 서버리스 SQL 풀을 사용하여 SQL server 테이블에서와 마찬가지로 파일 또는 폴더에서 상위 100개 행을 선택하는 SQL 스크립트를 자동으로 생성할 수 있다. 데이터가 일반 SQL server 테이블에 있는 것처럼 데이터에 대해 프로젝션, 필터링, 그룹화 및 대부분의 작업을 적용할 수 있음
데이터 변환 : Azure Synapse Analytics는 Synapse Spark와 함께 뛰어난 데이터 변환 기능을 제공하지만 일부 데이터 엔지니어는 SQL을 사용하여 데이터 변환을 더 쉽게 수행할 수 있다. 서버리스 SQL 풀을 사용하면 대화형으로 또는 자동화된 데이터 파이프라인의 일부로 SQL 기반 데이터 변환을 수행할 수 있다.
논리 데이터 웨어하우스 : 데이터 레이크에서 데이터를 처음 탐색한 후 서버리스 SQL 데이터베이스에서 테이블 및 뷰와 같은 외부 개체를 정의할 수 있다. 데이터는 데이터 레이크 파일에 저장된 상태로 유지되지만, SQL server에서 호스트되는 관계형 데이터베이스에서와 마찬가지로 클라이언트 애플리케이션 및 분석 도구에서 데이터를 쿼리하는데 사용할 수 있는 관계형 스키마에 의해 추상화된다.

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

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

CSV파일과 같은 구분된 텍스트
JSON 파일
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의 출력은 별칭을 할당해야 하는 행 집합

- BULK 매개 변수에는 데이터 파일이 포함된 데이터 레이크의 위치에 대한 전체 URL이 포함.

- 개별 파일이거나 포함해야 하는 파일형식을 필터링하기 위한 와일드카드 식이 있는 폴더 일 수 있음.

- FORMAT 매개 변수는 쿼리되는 데이터의 형식을 지정함

- 위의 예제에서는 files 폴더의 모든 .csv 파일에서 구분된 텍스트를 읽음.

참고)

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

[구분된 텍스트 파일 쿼리]

- 구분된 텍스트 파일은 많은 비즈니스 내에서 일반적인 파일 형식

헤더 행 포함 또는 제외
쉼표 및 탭으로 구분된 값
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 : 따옴표 붙은 문자열 값을 묶는데 사용되는 문자.

[행 집합 스키마 지정]

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

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

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

[JSON 파일 쿼리]

- 데이터 레이크의 파일에 JSON 문서로 데이터를 유지

[Parquet 파일 쿼리]

- Parquet는 분산 파일 스토리지에서 빅 데이터 처리에 일반적으로 사용되는 형식

- 압축 및 분석 쿼리에 최적화된 효율적인 데이터 형식

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

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

[분할된 데이터 쿼리]

- 데이터 레이크에서는 파티셔닝 기준을 반영하는 하위 폴더의 여러 파일 간에 분할하여 데이터를 파티셔닝하는 것이 일반적

- 분산 처리 시스템이 데이터의 여러 파티션에서 병렬로 작동하거나 필터링 조건에 따라 특정 폴더에서 데이터 읽기를 쉽게 제거할 수 있다.

외부 데이터베이스 개체 만들기

- 기본 제공 서버리스 SQL 풀의 기본 마스터 데이터베이스에서 실행되는 SQL 쿼리에서 OPENROWSET 함수를 사용하여 데이터 레이크의 데이터를 탐색할 수 있다. 그러나 때때로 데이터 레이크에서 자주 쿼리해야 하는 외부 데이터를 더 쉽게 사용할 수 있도록 해주는 일부 개체가 포함된 사용자 지정 데이터베이스를 만들려는 경우가 있다.

[데이터베이스 만들기]

- 그럴 때는 SQL Server 인스턴스에서와 마찬가지로 서버리스 SQL풀에 데이터베이스를 만들 수 있다.

- Synapse Studio에서 그래픽 인터페이스 또는 CREATE DATABASE 문을 사용할 수 있다.

- 데이터베이스의 데이터 정렬을 설정할 때 파일의 텍스트 데이터를 적절한 Transact-SQL 데이터 형식으로 변환할 수 있도록 해야 한다.

- 다음 예제 코드는 UTF-8로 인코딩된 텍스트 데이터를 VARCHAR열로 쉽게 가져올 수 있도록 해주는 데이터 정렬을 사용하여 salesDB라는 데이터베이스를 만든다.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

[외부 데이터 원본 만들기]

- BULK 경로와 함께 OPENROWSET 함수를 사용하여 마스터 데이터베이스에서와 마찬가지로 자체 데이터베이스의 파일 데이터를 쿼리할 수 있다.

- 그러나 동일한 위치에 있는 데이터를 자주 쿼리할 계획이라면 해당 위치를 참조하는 외부 데이터 원본을 정의하는 것이 더 효율적

예시)

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

- 외부 데이터 원본의 한가지 이점은 데이터 원본과 쿼리하려는 폴더 또는 파일에 대한 상대 경로의 조합을 사용하도록 OPENROWSET 쿼리를 간소화할 수 있다는 것

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

- 이 예제에서 BULK 매개 변수는 orders 폴더의 모든 .csv 파일에 대한 상대 경로를 지정하는데 사용되며, 이 경로는 데이터 원본에서 참조하는 files 폴더의 하위 폴더이다.

- 데이터 원본을 사용하는 또 다른 이점은 기본 스토리지에 액세스할 때 사용할 데이터 원본에 대한 자격 증명을 할당하여 사용자가 스토리지 계정에 직접 데이터에 액세스하도록 허용하지 않으면서 SQL을 통해 데이터에 대한 액세스를 제공할 수 있다는 것.

- 예) SAS( 공유 액세스 서명)를 사용하는 자격 증명을 만들어서 데이터 레이크를 호스트하는 기본 Azure Storage 계정에 대한 인증을 한다.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

- Tip

SAS 인증 외에도 관리 ID (Azure Synapse 작업 영역에서 사용하는 Microsoft Entra ID), 특정 Microsoft Entra 보안 주체 또는 쿼리를 실행하는 사용자의 ID 기반의 패스스루 인증(기본 인증 유형)을 사용하는 자격 증명을 정의할 수 있다.

[외부 파일 형식 만들기]

- 외부 데이터 원본은 OPENROWSET 함수를 사용하여 파일에 액세스하는데 필요한 코드를 간소화하지만 액세스 중인 파일에 대한 형식 세부 정보는 여전히 제공해야 하며, 세부 정보에는 구분된 텍스트 파일에 대한 여러 설정이 포함될 수 있다. 다음과 같이 이러한 설정을 외부 파일 형식으로 캡슐화할 수 있다.

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

작업해야 하는 특정 데이터 파일에 대한 파일 형식을 만든 후에는 다음에 설명된 대로 파일 형식을 사용하여 외부 테이블을 만들 수 있다.

[외부 테이블 만들기]

- 데이터 레이크의 파일에서 많은 분석 또는 보고를 수행해야 하는 경우 OPENROWSET 함수를 사용하면 데이터 원본 및 파일 경로를 포함하는 복잡한 코드가 발생할 수 있다.

- 데이터에 대한 액세스를 간소화하기 위해 외부 테이블에 파일을 캡슐화할 수 있고, 다른 데이터베이스 테이블에서와 마찬가지로 사용자와 보고 애플리케이션은 표준 SQL SELECT 문을 사용하여 쿼리할 수 있다.

- 외부 테이블을 만들려면 CREATE EXTERNAL TABLE 문을 사용하여 표준 테이블에 대한 열 스키마를 지정하고 WITH절을 포함하여 데이터에 대한 외부 데이터 원본, 상대 경로 및 외부 파일 형식을 지정한다.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

저작자표시 비영리 변경금지

'Azure' 카테고리의 다른 글

[DP-203] Azure Synapse Analytics 파이프라인을 사용하여 데이터 전송 및 변환 : Azure Synapse Analytics에서 데이터 파이프라인 빌드 (0)	2024.10.25
[Azure Synapse] 서버리스 SQL 풀과 전용 SQL풀 차이점 (0)	2024.10.21
[Azure Synapse Analytics] 통합 런타임 자체 호스팅 에러 (2)	2024.10.16
[DP-203] Azure에서 데이터 엔지니어링 시작하기: Azure의 데이터 엔지니어링 소개 (0)	2024.10.15
[DP-900] 4. Microsoft Azure 데이터 기본 사항: Azure의 데이터 분석 탐색 - 데이터 시각화의 기본 사항 살펴보기 (0)	2024.10.15

야채곱창의 삽질의 기억 저편에