이번 시간에는 지난 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:
- Linearly changes performance of the system for scans, aggregations, and CTAS statements
- Increases the number of readers and writers for PolyBase load operations
- Increases the maximum number of concurrent queries and concurrency slots
Data Warehouse Units (DWUs) for dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics | Microsoft Docs
간략히, 데이터웨어하우스 쿼리에 사용되는 쿼리들은 대부분 집계(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
