Azure Synapse Analytics 전용 SQL 풀에 데이터를 로드하는 모범 사례

Tip

Microsoft Fabric Data Warehouse는 데이터 레이크 기반의 엔터프라이즈 규모 관계형 웨어하우스로, 미래 대비 아키텍처, 기본 제공 AI 및 새로운 기능을 제공합니다. 데이터 웨어하우징이 처음이라면, Fabric Data Warehouse부터 시작하세요. 기존 전용 SQL 풀 워크로드는 Fabric으로 업그레이드함으로써 데이터 과학, 실시간 분석 및 보고 전반에 걸쳐 새로운 기능에 액세스할 수 있습니다.

이 문서에서는 데이터 로드를 위한 권장 사항 및 성능 최적화를 찾을 수 있습니다.

Azure Storage 데이터 준비

대기 시간을 최소화하려면 스토리지 계층과 전용 SQL 풀을 공동 배치합니다.

ORC 파일 형식으로 데이터를 내보낼 때 큰 텍스트 열이 포함되어 있으면 Java 메모리 부족 오류가 발생할 수 있습니다. 이 제한을 해결하려면 열의 하위 집합만 내보냅니다.

PolyBase는 1,000,000바이트 이상의 데이터가 있는 행을 로드할 수 없습니다. Azure Blob Storage 또는 Azure 데이터 레이크 Store의 텍스트 파일에 데이터를 배치하는 경우 1,000,000바이트 미만의 데이터가 있어야 합니다. 이 바이트 제한은 테이블 스키마에 관계없이 적용됩니다.

모든 파일 형식은 성능 특성이 다릅니다. 가장 빠른 로드를 위해 압축된 구분된 텍스트 파일을 사용합니다. UTF-8과 UTF-16 성능 간의 차이는 최소화됩니다.

큰 압축 파일을 더 작은 압축 파일로 분할합니다.

충분한 컴퓨팅 자원으로 작업 실행

가장 빠른 로드 속도를 위해 한 번에 하나의 부하 작업만 실행합니다. 가능하지 않은 경우 최소한의 부하를 동시에 실행합니다. 대규모 로드 작업이 예상되는 경우 로드 전에 전용 SQL 풀을 확장하는 것이 좋습니다.

적절한 컴퓨팅 리소스를 사용하여 부하를 실행하려면 로드를 실행하도록 지정된 로드 사용자를 만듭니다. 각 로드 사용자를 특정 리소스 클래스 또는 워크로드 그룹에 할당합니다. 부하를 실행하려면 로드하는 사용자 중 한 명으로 로그인한 다음 부하를 실행합니다. 부하는 사용자의 리소스 클래스와 함께 실행됩니다. 이 메서드는 사용자의 리소스 클래스를 현재 리소스 클래스에 맞게 변경하려고 하는 것보다 더 간단합니다.

로딩 작업 사용자 생성

이 예제에서는 특정 워크로드 그룹으로 분류된 로드 사용자를 만듭니다. 첫 번째 단계는 마스터에 연결 하고 로그인을 만드는 것입니다.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

전용 SQL 풀에 연결하고 사용자를 만듭니다. 다음 코드에서는 mySampleDataWarehouse라는 데이터베이스에 연결되어 있다고 가정합니다. 로더라는 사용자를 만드는 방법을 보여 주고 COPY 문을 사용하여 테이블을 만들고 로드할 수 있는 권한을 사용자에게 부여합니다. 그런 다음 최대 리소스를 사용하여 사용자를 DataLoads 워크로드 그룹으로 분류합니다.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Important

이는 SQL 풀의 100개% 리소스를 단일 로드에 할당하는 극단적인 예입니다. 이렇게 하면 최대 동시성을 1로 설정할 수 있습니다. 이는 워크로드 전체에서 리소스의 균형을 맞추기 위해 고유한 구성으로 다른 워크로드 그룹을 만들어야 하는 초기 로드에만 사용해야 합니다.

로드 워크로드 그룹에 대한 리소스를 사용하여 부하를 실행하려면 로더로 로그인하고 부하를 실행합니다.

여러 사용자가 로드할 수 있도록 허용

여러 사용자가 데이터 웨어하우스에 데이터를 로드해야 하는 경우가 많습니다. CREATE TABLE AS SELECT(Transact-SQL)를 사용하여 로드하려면 데이터베이스의 CONTROL 권한이 필요합니다. CONTROL 권한은 모든 스키마에 대한 제어 액세스 권한을 부여합니다. 로드하는 모든 사용자가 모든 스키마에 대한 제어 액세스 권한을 갖지 않도록 할 수 있습니다. 사용 권한을 제한하려면 DENY CONTROL 문을 사용합니다.

예를 들어 데이터베이스 스키마, 부서 A에 대한 schema_A 및 부서 B에 대한 schema_B 고려합니다. 데이터베이스 사용자가 user_A 및 user_B 부서 A와 B에서 PolyBase 로드를 위한 사용자로 각각 사용할 수 있습니다. 둘 다 CONTROL 데이터베이스 사용 권한이 부여되었습니다. 스키마 A와 B의 작성자는 이제 DENY를 사용하여 스키마를 잠급니다.

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A 및 user_B 이제 다른 부서의 스키마에서 잠깁니다.

준비 테이블에 로딩

데이터를 데이터 웨어하우스 테이블로 이동하는 가장 빠른 로드 속도를 달성하려면 데이터를 준비 테이블로 로드합니다. 준비 테이블을 힙으로 정의하고 배포 옵션에 라운드 로빈을 사용합니다.

로드는 일반적으로 먼저 준비 테이블에 로드한 다음 프로덕션 데이터 웨어하우스 테이블에 데이터를 삽입하는 2단계 프로세스입니다. 프로덕션 테이블에서 해시 배포를 사용하는 경우 해시 배포를 사용하여 준비 테이블을 정의하는 경우 로드 및 삽입하는 총 시간이 더 빠를 수 있습니다. 준비 테이블에 로드하는 데 시간이 오래 걸리지만, 프로덕션 테이블에 행을 삽입하는 두 번째 단계에서는 배포 간에 데이터 이동이 발생하지 않습니다.

columnstore 인덱스로 적재

Columnstore 인덱스에는 데이터를 고품질 행 그룹으로 압축하려면 많은 양의 메모리가 필요합니다. 최상의 압축 및 인덱스 효율성을 위해 columnstore 인덱스는 최대 1,048,576개의 행을 각 행 그룹으로 압축해야 합니다. 메모리 압력이 있는 경우 columnstore 인덱스가 최대 압축 속도를 달성하지 못할 수 있습니다. 이렇게 하면 쿼리 성능이 저하됩니다. 자세한 내용은 Columnstore 메모리 최적화를 참조하세요.

  • 로드하는 사용자에게 최대 압축 속도를 달성할 수 있는 충분한 메모리가 있는지 확인하려면 중간 또는 큰 리소스 클래스의 멤버인 로드 사용자를 사용합니다.
  • 새 행 그룹을 완전히 채울 수 있는 충분한 행을 로드합니다. 대량 로드 중에 1,048,576개 행이 모일 때마다 전체 행 그룹은 columnstore로 직접 압축됩니다. 행이 102,400개 미만인 로드는 행을 b-트리 인덱스가 있는 델타스토어로 보냅니다. 행이 너무 적으면 모두 델타스토어로 이동하여 columnstore 형식으로 즉시 압축되지 않을 수 있습니다.

SQLBulkCopy API 또는 BCP를 사용할 때 일괄 처리 크기 늘리기

COPY 문을 사용하여 로드하면 전용 SQL 풀에서 가장 높은 처리량을 제공합니다. COPY를 사용하여 로드할 수 없고 SqLBulkCopy API 또는 bcp를 사용해야 하는 경우 처리량을 높이기 위해 일괄 처리 크기를 늘리는 것이 좋습니다.

Tip

100K에서 1M 행 사이의 일괄 처리 크기는 최적의 일괄 처리 크기 용량을 결정하기 위한 권장 기준입니다.

로드 오류 관리

외부 테이블을 사용하는 로드는 "쿼리가 중단되었습니다. 외부 원본에서 읽는 동안 최대 거부 임계값에 도달했습니다."라는 오류와 함께 실패할 수 있습니다. 이 메시지는 외부 데이터에 더티 레코드가 포함되어 있음을 나타냅니다. 데이터 형식 및 열 수가 외부 테이블의 열 정의와 일치하지 않거나 데이터가 지정된 외부 파일 형식을 준수하지 않는 경우 데이터 레코드는 더티로 간주됩니다.

더티 레코드를 수정하려면 외부 테이블 및 외부 파일 형식 정의가 올바르고 외부 데이터가 이러한 정의를 준수하는지 확인합니다. 외부 데이터 레코드의 하위 집합이 더러워진 경우 'CREATE EXTERNAL TABLE' 의 거부 옵션을 사용하여 쿼리에 대해 이러한 레코드를 거부하도록 선택할 수 있습니다.

프로덕션 테이블에 데이터 삽입

INSERT 문이 있는 작은 테이블에 한 번 로드하거나 조회를 정기적으로 다시 로드하는 경우와 같은 INSERT INTO MyLookup VALUES (1, 'Type 1')명령문으로 충분히 잘 수행될 수 있습니다. 그러나 싱글톤 삽입은 대량 로드를 수행하는 것만큼 효율적이지 않습니다.

하루 종일 수천 개 이상의 단일 삽입이 있는 경우 삽입을 일괄 처리하여 대량 로드할 수 있습니다. 파일에 단일 삽입을 추가하는 프로세스를 개발한 다음, 파일을 주기적으로 로드하는 다른 프로세스를 만듭니다.

로드 후 통계 만들기

쿼리 성능을 향상시키려면 첫 번째 로드 후 모든 테이블의 모든 열에 대한 통계를 만들거나 데이터에서 주요 변경 내용이 발생하는 것이 중요합니다. 통계 만들기는 수동으로 수행하거나 통계 자동 생성을 사용하도록 설정할 수 있습니다.

통계에 대한 자세한 설명은 통계를 참조 하세요. 다음 예제에서는 Customer_Speed 테이블의 5개 열에 대한 통계를 수동으로 만드는 방법을 보여 줍니다.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

스토리지 키 회전

액세스 키를 Blob Storage로 정기적으로 변경하는 것이 좋습니다. Blob Storage 계정에 대한 두 개의 스토리지 키가 있으므로 키를 전환할 수 있습니다.

Azure Storage 계정 키를 회전하려면 다음을 수행합니다.

키가 변경된 각 스토리지 계정에 대해 ALTER DATABASE SCOPED CREDENTIAL을 발급합니다.

Example:

초기 키가 생성됩니다.

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

키 1에서 키 2로 키 회전

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

기본 외부 데이터 원본에 대한 다른 변경은 필요하지 않습니다.

관련 콘텐츠

  • Last updated on