Azure Synapse Analytics의 서버리스 SQL 풀에 대한 모범 사례

아티클
11/16/2023

이 문서에서는 서버리스 SQL 풀 사용에 대한 모범 사례의 컬렉션을 찾을 수 있습니다. 서버리스 SQL 풀은 Azure Synapse Analytics의 리소스입니다. 전용 SQL 풀을 사용하는 경우 특정 지침은 전용 SQL 풀 모범 사례를 참조하세요.

서버리스 SQL 풀을 사용하면 Azure Storage 계정의 파일을 쿼리할 수 있습니다. 로컬 저장소 또는 수집 기능이 없습니다. 쿼리 대상이 되는 모든 파일은 서버리스 SQL 풀 외부에 있습니다. 스토리지에서 파일을 읽는 것과 관련된 모든 것이 쿼리 성능에 영향을 줄 수 있습니다.

몇 가지 일반적인 지침은 다음과 같습니다.

클라이언트 애플리케이션이 서버리스 SQL 풀과 함께 배치되었는지 확인합니다.
- Azure 외부에서 클라이언트 애플리케이션을 사용하는 경우 클라이언트 컴퓨터와 가까운 지역에서 서버리스 SQL 풀을 사용하고 있는지 확인합니다. 클라이언트 애플리케이션의 예로 Power BI Desktop, SQL Server Management Studio 및 Azure Data Studio가 있습니다.
스토리지 및 서버리스 SQL 풀이 동일한 지역에 있는지 확인합니다. 스토리지의 예로 Azure Data Lake Storage 및 Azure Cosmos DB가 있습니다.
분할을 사용하고 파일의 범위를 100MB~10GB로 유지하여 스토리지 레이아웃을 최적화합니다.
많은 수의 결과를 반환하는 경우 Azure Synapse Studio가 아닌 SQL Server Management Studio 또는 Azure Data Studio를 사용하는지 확인합니다. Azure Synapse Studio는 대규모 결과 집합에 맞게 설계되지 않은 웹 도구입니다.
문자열 열을 기준으로 결과를 필터링하는 경우 BIN2_UTF8 데이터 정렬을 사용합니다. 데이터 정렬 변경에 대한 자세한 내용은 Synapse SQL에 지원되는 데이터 정렬 형식을 참조하세요.
Power BI 가져오기 모드 또는 Azure Analysis Services를 사용하여 클라이언트 쪽에서 결과를 캐시하고 주기적으로 새로 고치는 것이 좋습니다. 복잡한 쿼리를 사용하거나 많은 양의 데이터를 처리하는 경우 서버리스 SQL 풀은 Power BI 직접 쿼리 모드에서 대화형 환경을 제공할 수 없습니다.
최대 동시 실행은 제한되지 않으며 쿼리 복잡성 및 검색된 데이터의 양에 따라 달라집니다. 하나의 서버리스 SQL 풀은 간단한 쿼리를 실행하는 1000개의 활성 세션을 동시에 처리할 수 있습니다. 쿼리가 더 복잡하거나 더 많은 양의 데이터를 검색하면 숫자가 감소하므로 이 경우 동시성을 줄이고 가능한 경우 더 오랜 기간 동안 쿼리를 실행하는 것이 좋습니다.

클라이언트 애플리케이션 및 네트워크 연결

클라이언트 애플리케이션이 최적의 연결을 사용하여 가장 가까운 가능한 Azure Synapse 작업 영역에 연결되는지 확인합니다.

클라이언트 애플리케이션을 Azure Synapse 작업 영역과 함께 배치합니다. Power BI 또는 Azure Analysis Service와 같은 애플리케이션을 사용하는 경우 Azure Synapse 작업 영역을 배치한 동일한 지역에 있는지 확인합니다. 필요한 경우 클라이언트 애플리케이션과 쌍을 이루는 별도의 작업 영역을 만듭니다. 클라이언트 애플리케이션과 Azure Synapse 작업 영역을 다른 지역에 배치하면 대기 시간이 길어지고 결과 스트리밍이 느려질 수 있습니다.
온-프레미스 애플리케이션에서 데이터를 읽는 경우 Azure Synapse 작업 영역이 사용자의 위치와 가까운 지역에 있는지 확인합니다.
많은 양의 데이터를 읽는 동안 네트워크 대역폭 문제가 없는지 확인합니다.
Azure Synapse Studio를 사용하여 많은 양의 데이터를 반환하지 않습니다. Azure Synapse Studio는 HTTPS 프로토콜을 사용하여 데이터를 전송하는 웹 도구입니다. Azure Data Studio 또는 SQL Server Management Studio를 사용하여 많은 양의 데이터를 읽습니다.

스토리지 및 콘텐츠 레이아웃

다음은 서버리스 SQL 풀의 스토리지 및 콘텐츠 레이아웃에 대한 모범 사례입니다.

스토리지 및 서버리스 SQL 풀 공동 배치

대기 시간을 최소화하려면 Azure Storage 계정 또는 Azure Cosmos DB 분석 스토리지와 서버리스 SQL 풀 엔드포인트를 함께 배치합니다. 작업 영역을 만드는 동안 프로비저닝된 스토리지 계정과 엔드포인트는 같은 지역에 있습니다.

성능을 최적화하려면 서버리스 SQL 풀에서 다른 스토리지 계정에 액세스할 때 스토리지 계정과 서버리스 SQL 풀이 같은 지역에 있어야 합니다. 서로 다른 지역에 있으면 원격 지역과 엔드포인트의 지역 간에 데이터의 네트워크 전송에 걸리는 대기 시간이 증가합니다.

Azure Storage 제한

여러 애플리케이션과 서비스가 스토리지 계정에 액세스할 수 있습니다. 스토리지 제한은 애플리케이션, 서비스 및 서버리스 SQL 풀 워크로드에서 생성한 결합된 IOPS 또는 처리량이 스토리지 계정의 제한을 초과할 때 발생합니다. 결과적으로 쿼리 성능에 매우 부정적인 영향을 미치게 됩니다.

서버리스 SQL 풀은 제한이 감지되면 이러한 문제를 해결하는 기능을 기본적으로 갖추고 있습니다. 서버리스 SQL 풀은 제한이 해결될 때까지 느린 속도로 스토리지에 요청합니다.

팁

최적의 쿼리 실행을 위해, 쿼리를 실행하는 중에는 스토리지 계정에 다른 워크로드 부하를 추가해서는 안 됩니다.

쿼리할 파일 준비

가능하다면 성능 향상을 위한 파일을 준비해도 됩니다.

대용량 CSV 및 JSON 파일을 Parquet로 변환합니다. Parquet은 칼럼 형식입니다. 또한 압축되므로 동일한 데이터를 포함하는 CSV 또는 JSON 파일보다 파일 크기가 작습니다. Parquet 파일을 읽는 경우 서버리스 SQL 풀은 쿼리에 필요하지 않은 열과 행을 건너뜁니다. 서버리스 SQL 풀에서 읽는 데 필요한 시간과 스토리지 요청이 줄어듭니다.
쿼리 대상이 대형 파일 하나인 경우 여러 개의 작은 파일로 분할하는 것이 유리합니다.
CSV 파일 크기를 100MB에서 10GB로 유지하세요.
단일 OPENROWSET 경로 또는 외부 테이블 LOCATION에 대해 크기가 동일한 파일을 지정하는 것이 좋습니다.
파티션을 다른 폴더 또는 파일 이름에 저장하여 데이터를 분할합니다. 및 filepath 함수를 사용하여 특정 파티션을 대상으로 지정을 참조하세요.

Azure Cosmos DB 분석 스토리지 및 서버리스 SQL 풀 공동 배치

Azure Cosmos DB 분석 스토리지가 Azure Synapse 작업 영역과 동일한 지역에 있는지 확인합니다. 영역 간 쿼리를 수행하면 대기 시간이 길어질 수 있습니다. 연결 문자열의 region 속성을 사용하여 분석 저장소가 배치되는 지역을 명시적으로 지정합니다(서버리스 SQL 풀을 사용하여 Azure Cosmos DB 쿼리 참조). account=<database account name>;database=<database name>;region=<region name>'

CSV 최적화

다음은 서버리스 SQL 풀에서 CSV 파일을 사용하는 모범 사례입니다.

PARSER_VERSION 2.0을 사용하여 CSV 파일 쿼리

CSV 파일을 쿼리할 때 성능 최적화 파서를 사용할 수 있습니다. 자세한 내용은 PARSER_VERSION을 참조하세요.

CSV 파일에 대한 통계 수동 작성

서버리스 SQL 풀은 통계를 기반으로 최적의 쿼리 실행 계획을 생성합니다. 샘플링을 사용하여 열에 대한 통계가 자동으로 만들어지며 대부분의 경우 샘플링 비율은 100% 미만입니다. 이 흐름은 모든 파일 형식에 동일합니다. 파서 버전 1.0으로 CSV를 읽을 때는 샘플링이 지원되지 않으며 샘플링 비율이 100% 미만인 경우 자동 통계 만들기가 발생하지 않는다는 점을 유의해야 합니다. 카디널리티(행 수)가 낮은 것으로 예상되는 소규모 테이블의 경우 샘플링 비율 100%로 자동 통계 만들기가 트리거됩니다. 이는 파서 버전 1.0을 사용하는 CSV에 대해서도 전체 검사가 트리거되고 자동 통계가 만들어진다는 것을 의미합니다. 통계가 자동으로 만들어지지 않는 경우 쿼리에 사용하는 열, 특히 DISTINCT, JOIN, WHERE, ORDER BY 및 GROUP BY에 사용되는 열에 대해 수동으로 통계를 만듭니다. 자세한 내용은 서버리스 SQL 풀의 통계를 확인하세요.

데이터 형식

다음은 서버리스 SQL 풀에서 데이터 형식을 사용하는 모범 사례입니다.

적절한 데이터 형식 사용

쿼리에서 사용하는 데이터 형식은 성능과 동시성에 영향을 줍니다. 이러한 지침을 따르면 더 나은 성능을 얻을 수 있습니다.

가장 큰 가능한 값을 수용할 수 있는 가장 작은 데이터 크기를 사용합니다.
- 최대 문자 값 길이가 30자인 경우 길이가 30인 문자 데이터 형식을 사용합니다.
- 모든 문자 열 값이 고정 크기인 경우 char 또는 nchar를 사용합니다. 그렇지 않으면 varchar 또는 nvarchar를 사용합니다.
- 최대 정수 열 값이 500이면 이 값을 수용할 수 있는 가장 작은 데이터 형식인 smallint를 사용합니다. 자세한 내용은 정수 데이터 형식 범위를 참조하세요.
가능하다면 nvarchar 및 nchar 대신 varchar 및 char을 사용합니다.
- UTF-8 인코딩을 사용하여 Parquet, Azure Cosmos DB, Delta Lake 또는 CSV에서 데이터를 읽는 경우 일부 UTF8 데이터 정렬과 함께 varchar 형식을 사용합니다.
- 유니코드(예: ASCII)가 아닌 CSV 파일에서 데이터를 읽는 경우 UTF8 데이터 정렬을 사용하지 않는 varchar 형식을 사용합니다.
- UTF-16 CSV 파일에서 데이터를 읽는 경우 nvarchar 형식을 사용합니다.
가능하다면 정수 기반 데이터 형식을 사용합니다. SORT, JOIN 및 GROUP BY 작업은 문자 데이터보다 정수에서 더 빠르게 수행됩니다.
스키마 유추를 사용하는 경우 유추된 데이터 형식을 확인하고 가능한 경우 더 작은 형식으로 명시적으로 재정의합니다.

유추한 데이터 형식 확인

스키마 유추는 파일 스키마를 알지 못해도 신속하게 쿼리를 작성하고 데이터를 탐색하는 데 도움을 줍니다. 이 편의를 위해 유추된 데이터 형식이 실제 데이터 형식보다 클 수 있습니다. 이 불일치는 적절한 데이터 형식이 사용되는지 확인하는 데 충분한 정보가 원본 파일에 없을 때 발생합니다. 예를 들어 Parquet 파일에는 최대 문자 열 길이에 대한 메타데이터가 없습니다. 따라서 서버리스 SQL 풀은 이 길이를 varchar(8000)로 유추합니다.

공유 가능한 관리형 및 외부 Spark 테이블이 SQL 엔진에서 외부 테이블로 공개될 경우 상황이 달라질 수 있다는 사실에 유의하세요. Spark 테이블은 Synapse SQL 엔진과 다른 데이터 형식을 제공합니다. Spark 테이블 데이터 유형 및 SQL 유형 간 매핑은 여기에서 확인할 수 있습니다.

시스템 저장 프로시저 sp_describe_first_results_set를 사용하여 쿼리의 결과 데이터 형식을 확인할 수 있습니다.

다음 예제에서는 유추한 데이터 형식을 최적화하는 방법을 보여줍니다. 이 프로시저는 유추한 데이터 형식을 보여 주는 데 사용됩니다.

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

결과 세트:

column_ordinal	name	system_type_name	max_length
1	vendor_id	varchar(8000)	8000
2	pickup_datetime	datetime2(7)	8
3	passenger_count	int	4

쿼리에 대해 유추한 데이터 형식을 알고 있으면 다음과 같이 적절한 데이터 형식을 지정할 수 있습니다.

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

필터 최적화

다음은 서버리스 SQL 풀에서 쿼리를 사용하는 모범 사례입니다.

와일드카드를 푸시하여 경로에서 수준 내리기

경로에 와일드카드를 사용하여 여러 파일 및 폴더를 쿼리할 수 있습니다. 서버리스 SQL 풀은 스토리지 API를 사용하여 첫 번째 별표(*)부터 시작하여 스토리지 계정의 파일을 나열합니다. 지정된 경로와 일치하지 않는 파일은 제거됩니다. 초기 파일 목록을 줄이면 지정된 경로의 첫 번째 와일드카드까지 일치하는 파일이 여러 개 있을 때 성능을 높일 수 있습니다.

filename 및 filepath 함수를 사용하여 특정 파티션을 대상으로 지정

데이터는 종종 여러 파티션으로 구성됩니다. 서버리스 SQL 풀에 특정 폴더와 파일을 쿼리하도록 지시할 수 있습니다. 이렇게 하면 쿼리에서 읽고 처리해야 하는 파일 수와 데이터 양이 줄어듭니다. 그로 인한 보너스로 성능이 향상됩니다.

자세한 내용은 filename 및 filepath 함수에 대해 읽어보고 특정 파일 쿼리 예제를 참조하세요.

팁

항상 filepath 및 filename 함수의 결과를 적절한 데이터 형식으로 캐스팅하세요. 문자 데이터 형식을 사용하는 경우 적절한 길이를 사용해야 합니다.

파티션 제거에 사용되는 filepath 및 filename 함수는 현재 Azure Synapse Analytics용 Apache Spark에서 생성된 테이블마다 자동으로 생성된 외부 테이블을 제외한 나머지 외부 테이블을 지원하지 않습니다.

저장된 데이터가 분할되지 않는 경우 분할하는 것이 좋습니다. 이러한 방식으로 이러한 함수를 사용하여 해당 파일을 대상으로 하는 쿼리를 최적화할 수 있습니다. 서버리스 SQL 풀에서 분할된 Apache Spark for Azure Synapse 테이블을 쿼리하는 경우 쿼리는 자동으로 필요한 파일만 대상으로 지정합니다.

적절한 데이터 정렬을 사용하여 문자 열에 대한 조건자 푸시다운 활용

Parquet 파일의 데이터는 행 그룹으로 구성됩니다. 서버리스 SQL 풀은 WHERE 절에 지정된 조건자에 따라 행 그룹을 건너뛰므로 IO가 줄어듭니다. 그 결과 쿼리 성능이 향상됩니다.

Parquet 파일의 문자 열에 대한 조건자 푸시다운은 Latin1_General_100_BIN2_UTF8 데이터 정렬에만 지원됩니다. WITH 절을 사용하여 특정 열에 대한 데이터 정렬을 지정할 수 있습니다. WITH 절을 사용하여 이 데이터 정렬을 지정하지 않으면 데이터베이스 데이터 정렬이 사용됩니다.

반복 쿼리 최적화

다음은 서버리스 SQL 풀에서 CETAS를 사용하는 모범 사례입니다.

CETAS를 사용하여 쿼리 성능 및 조인 향상

CETAS는 서버리스 SQL 풀에서 제공하는 가장 중요한 기능 중 하나입니다. CETAS는 외부 테이블 메타데이터를 만들고 SELECT 쿼리 결과를 스토리지 계정의 파일 세트로 내보내는 병렬 작업입니다.

CETAS를 사용하면 조인된 참조 테이블처럼 쿼리에서 자주 사용하는 부분을 새 파일 세트에 구체화할 수 있습니다. 그러면 여러 쿼리에서 공통 조인을 반복하는 대신, 이 단일 외부 테이블에 조인할 수 있습니다.

CETAS에서 Parquet 파일을 생성하므로 첫 번째 쿼리가 이 외부 테이블을 대상으로 할 때 통계가 자동으로 만들어집니다. 그 결과 CETAS를 사용하여 생성된 테이블을 대상으로 하는 후속 쿼리의 성능이 향상됩니다.

Azure 데이터 쿼리

서버리스 SQL 풀에서는 외부 테이블 및 OPENROWSET 함수를 사용하여 Azure Storage 또는 Azure Cosmos DB에서 데이터를 쿼리할 수 있습니다. 스토리지에 대한 적절한 권한 설정이 있는지 확인합니다.

CSV 데이터 쿼리

단일 CSV 파일 쿼리 또는 폴더 및 여러 CSV 파일을 쿼리하는 방법에 대해 알아봅니다. 분할된 파일을 쿼리할 수도 있습니다.

Parquet 데이터 쿼리

중첩 형식을 사용하여 Parquet 파일을 쿼리하는 방법을 알아봅니다. 분할된 파일을 쿼리할 수도 있습니다.

Delta Lake 쿼리

중첩 형식을 사용하여 Delta Lake 파일을 쿼리하는 방법을 알아봅니다.

Azure Cosmos DB 데이터 쿼리

Azure Cosmos DB 분석 저장소를 쿼리하는 방법을 알아봅니다. 온라인 생성기를 사용하여 샘플 Azure Cosmos DB 문서를 기반으로 WITH 절을 생성할 수 있습니다. Azure Cosmos DB 컨테이너를 기반으로 하는 뷰를 만들 수 있습니다.

JSON 데이터 쿼리

JSON 파일 쿼리 방법 알아보기 분할된 파일을 쿼리할 수도 있습니다.

뷰, 테이블 및 기타 데이터베이스 개체 만들기

뷰 및 외부 테이블을 만들고 사용하거나 행 수준 보안을 설정하는 방법을 알아봅니다. 분할된 파일이 있는 경우 분할된 뷰를 사용해야 합니다.

데이터 복사 및 변환(CETAS)

CETAS 명령을 사용하여 쿼리 결과를 스토리지에 저장하는 방법을 알아봅니다.

다음 단계

일반적인 문제에 대한 해결 방법은 서버리스 SQL 풀 문제 해결 문서를 검토하세요.
서버리스 SQL 풀이 아닌 전용 SQL 풀을 사용하는 경우 특정 지침은 전용 SQL 풀 모범 사례를 참조하세요.
Azure Synapse Analytics 질문과 대답
작업 영역 관리 ID에 권한 부여

다음을 통해 공유