데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

이번 시간에는 지난 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
» Azure Synapse - COPY INTO로 대용량 데이터 분산 로드 코난(김대우) 2021.10.21 77
2192 Azure SQL Database로 CSV 파일 BULK INSERT - Python 코난(김대우) 2021.09.27 107
2191 Azure Synapse - Spark와 SQL Data warehouse 서비스 file 코난(김대우) 2021.09.16 102
2190 Azure에서 제공하는 데이터베이스 서비스 종류, AWS 및 GCP와 제품 비교 코난(김대우) 2020.12.25 550
2189 SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 jevida(강성욱) 2020.02.28 1365
2188 SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 jevida(강성욱) 2019.09.24 1674
2187 Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 jevida(강성욱) 2019.09.24 1259
2186 SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 jevida(강성욱) 2019.09.21 1229
2185 SQL Server 2019 Log Writer Workers jevida(강성욱) 2019.09.21 1420
2184 SQL Server Login Timeout 디버깅 jevida(강성욱) 2019.09.19 1843
2183 SQL Server Worker Thread 기본 계산 jevida(강성욱) 2019.09.18 1365
2182 SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) jevida(강성욱) 2019.09.17 1224
2181 SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 jevida(강성욱) 2019.09.14 1257
2180 BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 jevida(강성욱) 2019.09.13 1335
2179 삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) jevida(강성욱) 2019.09.12 1143
2178 QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 jevida(강성욱) 2019.09.11 1055
2177 ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 jevida(강성욱) 2019.09.10 1095
2176 Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 jevida(강성욱) 2019.09.09 1115
2175 SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 jevida(강성욱) 2019.09.04 1260
2174 SQL Server Agent Job에서 sysploicy_purge_history 작업실패 jevida(강성욱) 2019.09.04 1306





XE Login