이번 시간에는 지난 Azure SQL Database bulk insert에 이어서, 최초 목표로 했던 Synapse에서 분산 데이터 로드를 수행하는 과정을 정리.

 

Azure Synapse - COPY INTO로 대용량 데이터 분산 로드

 

지난 문서에서 간략히 Synapse란 무엇인지에 대해서 다루었다.

개발자 커뮤니티 SQLER.com - Azure Synapse - Spark와 SQL Data warehouse 서비스

 

이어서, Azure SQL Bulk Insert를 하는 과정을 진행했다.

개발자 커뮤니티 SQLER.com - Azure SQL Database로 CSV 파일 BULK INSERT - Python

 

최종적으로 병렬 분산 데이터 로딩 처리를 수행하는 Synapse의 COPY INTO를 정리한다.

 

Synapse COPY INTO란 무엇인가?

간략히, SQL의 Bulk Insert를 병렬로 처리 가능한 것이 COPY INTO라고 생각하면 쉽다.

COPY INTO (Transact-SQL) - (Azure Synapse Analytics) - SQL Server | Microsoft Docs

COPY INTO는 Synapse의 Dedicated SQL Pool에서 제공하는 기능이다. Synapse는 지난 시간에 소개한 것처럼, "DWU"라는 Data Warehouse Unit으로 스케일링이 가능하다. 

 

Synapse의 DWU를 증가시키면 어떤 부분이 향상되는가?

궁금했던 부분이기도 하다.

Increasing DWUs:

 

간략히, 데이터웨어하우스 쿼리에 사용되는 쿼리들은 대부분 집계(aggregation) 목적이다. 이를 위해서는 대규모 테이블에 대한 full scan 작업이 빈번하다. 이러한

- scan, aggregation, CTAS(Create Table As Select)와 같은 작업에 DWU를 증가시키면 성능이 좋아진다.

- Polybase 데이터 로드 과정에서 발생하는 읽기와 쓰기 작업 속도가 증가한다.

- 최대 동시 수행 쿼리(concurrent queries)와 가용 쿼리 수행 수(concurrency slots)가 증가한다.

 

데이터 로딩 속도에 대한 고찰

다시 이 문서의 목적인 대용량 데이터 로드로 돌아가자.

COPY INTO는 Bulk Insert의 병렬 작업이다. 어떻게 병렬로 처리 가능한가?

 

예를 들어, 100G 크기의 CSV파일이 있다면, 이 파일 1개를 로드 하는 것도 가능하지만, 1기가씩 나눠 100개의 파일을 로드 하는 것 역시 가능하다. DWU를 사용하는 Synapse에서는 DWU에 따라 파일을 분할해 로드 할 것을 권장하고 있다.

COPY INTO (Transact-SQL) - (Azure Synapse Analytics) - SQL Server | Microsoft Docs

예를들어, DWU 100에서는 60개 파일, DWU 3000에서는 360개 파일을 병렬로 로드할 것을 권장한다.

 

CSV 파일 준비 및 분할

지난 Bulk Insert 포스트에서 사용했던 malware 데이터셋을 이용한다. 전체 100만건의 row를 가지는 파일을 split으로 준비했다. 

이 파일은 헤더를 가지고 있다, 즉 이 파일을 다시 split한다면 첫번째 파일만 헤더 정보가 포함되고 단일 쿼리 처리가 귀찮아진다. 첫 라인을 삭제해야 하고 진행하자.

 

CSV 파일의 첫번째 라인 - 헤더 라인 제거

이 문서에서 관련 내용을 볼 수 있다.

scripting - How can I remove the first line of a text file using bash/sed script? - Stack Overflow

FILE="train_1000000.csv"
tail -n +2 "$FILE" > "$FILE.tmp" && mv "$FILE.tmp" "$FILE"

이렇게 처리하면 헤더가 없는 파일이 생성된다. CSV파일 크기는 약 480M 정도 100만라인(-1)이다.

이 파일을 추가로 split 한다.

 

10개, 50개 - CSV 파일 분할

10만 라인 단위로 나눠 10개의 CSV 파일을 생성한다.

split -l 100000 train_1000000_no_header.csv

 

2만 라인 단위로 나눠 50여개의 CSV 파일을 생성했다.

split -l 20000 train_1000000_no_header.csv

 

이 파일들은 Azure의 Blob Storage에 각각 "synapse-copy", "synapse-copy-split-10files", "synapse-copy-split-52files" 컨테이너에 올려 두었고, 각각의 1개 파일은 파일에, 나머지는 컨테이너에 대해 SAS token을 생성하였다.

 

Synapse에서 COPY INTO 쿼리 수행 후 속도 비교

DWU는 100으로(가장 저렴한) 구성된 상태이다. 가이드 문서는 60개의 병렬 분산 로딩을 권장하고 있으며, 각각의 작업을 수행하고 시간을 체크해 보았다.

 

테이블 생성 쿼리

Bulk Insert에서 사용했던 쿼리 그대로이다.

create table malware(
MachineIdentifier varchar(256),
ProductName varchar(256),
EngineVersion varchar(256),
AppVersion varchar(256),
AvSigVersion varchar(256),
IsBeta int,
RtpStateBitfield float(24),
IsSxsPassiveMode int,
DefaultBrowsersIdentifier float(24),
AVProductStatesIdentifier float(24),
AVProductsInstalled float(24),
AVProductsEnabled float(24),
HasTpm int,
CountryIdentifier int,
CityIdentifier float(24),
OrganizationIdentifier float(24),
GeoNameIdentifier float(24),
LocaleEnglishNameIdentifier int,
Platform varchar(256),
Processor varchar(256),
OsVer varchar(256),
OsBuild int,
OsSuite int,
OsPlatformSubRelease varchar(256),
OsBuildLab varchar(256),
SkuEdition varchar(256),
IsProtected float(24),
AutoSampleOptIn int,
PuaMode varchar(256),
SMode float(24),
IeVerIdentifier float(24),
SmartScreen varchar(256),
Firewall float(24),
UacLuaenable float(24),
Census_MDC2FormFactor varchar(256),
Census_DeviceFamily varchar(256),
Census_OEMNameIdentifier float(24),
Census_OEMModelIdentifier float(24),
Census_ProcessorCoreCount float(24),
Census_ProcessorManufacturerIdentifier float(24),
Census_ProcessorModelIdentifier float(24),
Census_ProcessorClass varchar(256),
Census_PrimaryDiskTotalCapacity float(24),
Census_PrimaryDiskTypeName varchar(256),
Census_SystemVolumeTotalCapacity float(24),
Census_HasOpticalDiskDrive int,
Census_TotalPhysicalRAM float(24),
Census_ChassisTypeName varchar(256),
Census_InternalPrimaryDiagonalDisplaySizeInInches float(24),
Census_InternalPrimaryDisplayResolutionHorizontal float(24),
Census_InternalPrimaryDisplayResolutionVertical float(24),
Census_PowerPlatformRoleName varchar(256),
Census_InternalBatteryType varchar(256),
Census_InternalBatteryNumberOfCharges float(24),
Census_OSVersion varchar(256),
Census_OSArchitecture varchar(256),
Census_OSBranch varchar(256),
Census_OSBuildNumber int,
Census_OSBuildRevision bigint,
Census_OSEdition varchar(256),
Census_OSSkuName varchar(256),
Census_OSInstallTypeName varchar(256),
Census_OSInstallLanguageIdentifier float(24),
Census_OSUILocaleIdentifier int,
Census_OSWUAutoUpdateOptionsName varchar(256),
Census_IsPortableOperatingSystem int,
Census_GenuineStateName varchar(256),
Census_ActivationChannel varchar(256),
Census_IsFlightingInternal float(24),
Census_IsFlightsDisabled float(24),
Census_FlightRing varchar(256),
Census_ThresholdOptIn float(24),
Census_FirmwareManufacturerIdentifier float(24),
Census_FirmwareVersionIdentifier float(24),
Census_IsSecureBootEnabled int,
Census_IsWIMBootEnabled float(24),
Census_IsVirtualDevice float(24),
Census_IsTouchEnabled int,
Census_IsPenCapable int,
Census_IsAlwaysOnAlwaysConnectedCapable float(24),
Wdft_IsGamer float(24),
Wdft_RegionIdentifier float(24),
HasDetections int
)
GO

 

480M - 1개 CSV 파일을 COPY INTO 수행

1개 파일 로드에 약 39초가 소요되었다. 지난 Bulk Insert에서 267초가 소요된 것에 비하면, 역시 Dedicated instance가 성능면에서 훌륭하다.

--SAS token 방식 1개 파일 COPY INYO
COPY INTO malware
FROM 'https://dwstorstest.blob.core.windows.net/synapse-copy/train_1000000_no_header.csv'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<YOUR-SAS-SECRET-KEY>'),
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0X0A',
    ENCODING = 'UTF8'
)
--39초 소요

 

48M - 10개 CSV 파일을 COPY INTO 수행

10개 파일이기 때문에 컨테이너 내부 CSV 파일은 이렇게 *로 모든 파일을 가져오거나 적절히 필터링한다.

시간은 약 20초 소요되었다. 1개 파일 로드 시 39초에 비하면 절반정도 소요되었다.

-- 10 files
COPY INTO malware
FROM 'https://dwstorstest.blob.core.windows.net/synapse-copy-split-10files/*'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<YOUR-SAS-SECRET-KEY>'),
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0X0A',
    ENCODING = 'UTF8'
)
-- 소요시간 20초

 

 

5M - 52개 CSV 파일을 COPY INTO 수행

최종적으로 52개 파일 수행 결과는 16초가 소요.

-- 52 files
COPY INTO malware
FROM 'https://dwstorstest.blob.core.windows.net/synapse-copy-split-52files/*'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<YOUR-SAS-SECRET-KEY>'),
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0X0A',
    ENCODING = 'UTF8'
)
-- 소요시간 16초

 

결론

480M - Bulk Insert(ASDB - S3) 267초
480M - 1개 파일 COPY INTO(Synapse 100 DWU) 39초
50M - 10개 파일 COPY INTO(Synapse 100 DWU) 20초
5M - 52개 파일 COPY INTO(Synapse 100 DWU) 16초

 

마지막 50여개 파일을 이용한 처리는 개별 파일의 크기가 5M 정도로 작은 크기였다.

공식 가이드는 4M 이상의 파일을 사용하라고 하는데, 스토리지 컨테이너에서 파일을 가져오는 IO작업에 의해 속도 저하도 있을 듯하다. 여하간, 가이드대로 최대 DWU의 병렬 로딩 숫자에 맞춰 최대한 파일을 분할하는 것이 로딩 속도에 주요할 듯하다.

 

참고링크

개발자 커뮤니티 SQLER.com - Azure SQL Database로 CSV 파일 BULK INSERT - Python

개발자 커뮤니티 SQLER.com - Azure Synapse - Spark와 SQL Data warehouse 서비스

COPY INTO (Transact-SQL) - (Azure Synapse Analytics) - SQL Server | Microsoft Docs

Manage compute resource for for dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics | Microsoft Docs

 

No. Subject Author Date Views
Notice [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 82
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 23
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
2203 Microsoft SQL Server 2022 공식발표 file 코난(김대우) 2022.11.24 24
2202 Azure Synapse Analytics 빅데이터 분석 코난(김대우) 2022.11.05 19
2201 Azure Synapse Analytics를 활용한 게임사 유저 이탈분석과 ML Modeling 코난(김대우) 2022.11.02 14
2200 ETL의 끝판왕, 애저 데이터 팩토리 | ep3. 클로징 | 애저 듣고보는 잡학지식 코난(김대우) 2022.11.01 11
2199 ETL의 끝판왕, 애저 데이터 팩토리 | ep2. 데모 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.31 8
2198 ETL의 끝판왕, 애저 데이터 팩토리 | ep1. 소개 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.30 19
2197 ETL의 끝판왕, 애저 데이터 팩토리 | ep0. 인트로 | 애저 듣고보는 잡학지식 코난(김대우) 2022.10.28 35
2196 Azure Cosmos DB의 PostgreSQL file 코난(김대우) 2022.10.19 45
2195 Citus PostgreSQL extension - 분산데이터베이스 file 코난(김대우) 2022.10.18 29
2194 Azure purview - data governance, compliance 솔루션에 대해 file 코난(김대우) 2022.10.16 34
» Azure Synapse - COPY INTO로 대용량 데이터 분산 로드 코난(김대우) 2021.10.21 224
2192 Azure SQL Database로 CSV 파일 BULK INSERT - Python 코난(김대우) 2021.09.27 260
2191 Azure Synapse - Spark와 SQL Data warehouse 서비스 file 코난(김대우) 2021.09.16 187
2190 Azure에서 제공하는 데이터베이스 서비스 종류, AWS 및 GCP와 제품 비교 코난(김대우) 2020.12.25 694
2189 SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 jevida(강성욱) 2020.02.28 1487
2188 SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 jevida(강성욱) 2019.09.24 1745
2187 Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 jevida(강성욱) 2019.09.24 1293
2186 SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 jevida(강성욱) 2019.09.21 1289
2185 SQL Server 2019 Log Writer Workers jevida(강성욱) 2019.09.21 1480
2184 SQL Server Login Timeout 디버깅 jevida(강성욱) 2019.09.19 1950





XE Login

테스트 팝업
Close