CSV 파일을 Azure Data Explorer에 로드하고 테스트할 일이 생겨서 작업 진행.

포스팅을 위해 공개된 대용량 CSV 파일을 ADX(Azure Data Explorer, 이하 ADX)로 로드하는 과정 진행.

 

대용량 CSV 파일을 ADX로 ingest 하는 과정

- CSV 파일을 ADX가 접근 가능한 blob storage로 업로드

- 업로드한 CSV blob에 대해 SAS token을 생성하고 URI를 작성

- ADX에서 table과 mapping을 생성

- ADX에서 ingest 쿼리로 CSV 파일을 로드

- ADX에서 데이터 조회

 

CSV 파일을 ADX가 접근 가능한 blob storage로 업로드

예제 CSV 파일 - kaggle - Microsoft Malware Prediction | Kaggle

예제 데이터로 train CSV 파일은 약 4G 정도 크기이다. CSV 파일의 스키마 역시 위의 링크에서 확인 가능.

ADX로 로드하기 위해 CSV 파일을 blob storage로 업로드한다.

Azure Portal에서 업로드 : Quickstart - Create a blob with the Azure portal - Azure Storage | Microsoft Docs

azcopy CLI : Quickstart - Create a blob with Azure CLI - Azure Storage | Microsoft Docs

Storage explorer 툴로 업로드 : Quickstart - Create a blob with Azure Storage Explorer - Azure Storage | Microsoft Docs

방식 중에서 선택 가능하다.

 

업로드한 CSV blob에 대해 SAS token을 생성하고 URI를 작성

예를 들어, 아래 이미지처럼, Azure Portal의 경우 blob을 container에서 선택하고 generate SAS를 수행해 token을 생성한다.

 

Opción Generar SAS de un archivo en un contenedor de blobs.

SAS token을 생성하면 해당 blob에 access하기 위한 URI를 얻을 수 있다. ADX에러 로드할때 사용하니 잘 복사해 두자.

 

ADX에서 table과 mapping을 생성

이제 ADX에서 table을 생성한다. 만약 ADX가 처음이라면, Cluster와 database를 생성해야 한다.

아래 문서를 참고해 Cluster와 DB를 생성 가능하다.

Quickstart: Create an Azure Data Explorer cluster & DB | Microsoft Docs

 

컬럼이 많아 약간 길다.

table 생성 쿼리

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

 

이어서 CSV mapping을 생성한다.

 

Table - CSV mapping 생성 쿼리

.create table malware_table ingestion csv mapping "malware_table_mapping"
'['
'{"column":"MachineIdentifier","DataType":"string","Properties":{"Ordinal":"0"}},'
'{"column":"ProductName","DataType":"string","Properties":{"Ordinal":"1"}},'
'{"column":"EngineVersion","DataType":"string","Properties":{"Ordinal":"2"}},'
'{"column":"AppVersion","DataType":"string","Properties":{"Ordinal":"3"}},'
'{"column":"AvSigVersion","DataType":"string","Properties":{"Ordinal":"4"}},'
'{"column":"IsBeta","DataType":"int","Properties":{"Ordinal":"5"}},'
'{"column":"RtpStateBitfield","DataType":"real","Properties":{"Ordinal":"6"}},'
'{"column":"IsSxsPassiveMode","DataType":"int","Properties":{"Ordinal":"7"}},'
'{"column":"DefaultBrowsersIdentifier","DataType":"real","Properties":{"Ordinal":"8"}},'
'{"column":"AVProductStatesIdentifier","DataType":"real","Properties":{"Ordinal":"9"}},'
'{"column":"AVProductsInstalled","DataType":"real","Properties":{"Ordinal":"10"}},'
'{"column":"AVProductsEnabled","DataType":"real","Properties":{"Ordinal":"11"}},'
'{"column":"HasTpm","DataType":"int","Properties":{"Ordinal":"12"}},'
'{"column":"CountryIdentifier","DataType":"int","Properties":{"Ordinal":"13"}},'
'{"column":"CityIdentifier","DataType":"real","Properties":{"Ordinal":"14"}},'
'{"column":"OrganizationIdentifier","DataType":"real","Properties":{"Ordinal":"15"}},'
'{"column":"GeoNameIdentifier","DataType":"real","Properties":{"Ordinal":"16"}},'
'{"column":"LocaleEnglishNameIdentifier","DataType":"int","Properties":{"Ordinal":"17"}},'
'{"column":"Platform","DataType":"string","Properties":{"Ordinal":"18"}},'
'{"column":"Processor","DataType":"string","Properties":{"Ordinal":"19"}},'
'{"column":"OsVer","DataType":"string","Properties":{"Ordinal":"20"}},'
'{"column":"OsBuild","DataType":"int","Properties":{"Ordinal":"21"}},'
'{"column":"OsSuite","DataType":"int","Properties":{"Ordinal":"22"}},'
'{"column":"OsPlatformSubRelease","DataType":"string","Properties":{"Ordinal":"23"}},'
'{"column":"OsBuildLab","DataType":"string","Properties":{"Ordinal":"24"}},'
'{"column":"SkuEdition","DataType":"string","Properties":{"Ordinal":"25"}},'
'{"column":"IsProtected","DataType":"real","Properties":{"Ordinal":"26"}},'
'{"column":"AutoSampleOptIn","DataType":"int","Properties":{"Ordinal":"27"}},'
'{"column":"PuaMode","DataType":"string","Properties":{"Ordinal":"28"}},'
'{"column":"SMode","DataType":"real","Properties":{"Ordinal":"29"}},'
'{"column":"IeVerIdentifier","DataType":"real","Properties":{"Ordinal":"30"}},'
'{"column":"SmartScreen","DataType":"string","Properties":{"Ordinal":"31"}},'
'{"column":"Firewall","DataType":"real","Properties":{"Ordinal":"32"}},'
'{"column":"UacLuaenable","DataType":"real","Properties":{"Ordinal":"33"}},'
'{"column":"Census_MDC2FormFactor","DataType":"string","Properties":{"Ordinal":"34"}},'
'{"column":"Census_DeviceFamily","DataType":"string","Properties":{"Ordinal":"35"}},'
'{"column":"Census_OEMNameIdentifier","DataType":"real","Properties":{"Ordinal":"36"}},'
'{"column":"Census_OEMModelIdentifier","DataType":"real","Properties":{"Ordinal":"37"}},'
'{"column":"Census_ProcessorCoreCount","DataType":"real","Properties":{"Ordinal":"38"}},'
'{"column":"Census_ProcessorManufacturerIdentifier","DataType":"real","Properties":{"Ordinal":"39"}},'
'{"column":"Census_ProcessorModelIdentifier","DataType":"real","Properties":{"Ordinal":"40"}},'
'{"column":"Census_ProcessorClass","DataType":"string","Properties":{"Ordinal":"41"}},'
'{"column":"Census_PrimaryDiskTotalCapacity","DataType":"real","Properties":{"Ordinal":"42"}},'
'{"column":"Census_PrimaryDiskTypeName","DataType":"string","Properties":{"Ordinal":"43"}},'
'{"column":"Census_SystemVolumeTotalCapacity","DataType":"real","Properties":{"Ordinal":"44"}},'
'{"column":"Census_HasOpticalDiskDrive","DataType":"int","Properties":{"Ordinal":"45"}},'
'{"column":"Census_TotalPhysicalRAM","DataType":"real","Properties":{"Ordinal":"46"}},'
'{"column":"Census_ChassisTypeName","DataType":"string","Properties":{"Ordinal":"47"}},'
'{"column":"Census_InternalPrimaryDiagonalDisplaySizeInInches","DataType":"real","Properties":{"Ordinal":"48"}},'
'{"column":"Census_InternalPrimaryDisplayResolutionHorizontal","DataType":"real","Properties":{"Ordinal":"49"}},'
'{"column":"Census_InternalPrimaryDisplayResolutionVertical","DataType":"real","Properties":{"Ordinal":"50"}},'
'{"column":"Census_PowerPlatformRoleName","DataType":"string","Properties":{"Ordinal":"51"}},'
'{"column":"Census_InternalBatteryType","DataType":"string","Properties":{"Ordinal":"52"}},'
'{"column":"Census_InternalBatteryNumberOfCharges","DataType":"real","Properties":{"Ordinal":"53"}},'
'{"column":"Census_OSVersion","DataType":"string","Properties":{"Ordinal":"54"}},'
'{"column":"Census_OSArchitecture","DataType":"string","Properties":{"Ordinal":"55"}},'
'{"column":"Census_OSBranch","DataType":"string","Properties":{"Ordinal":"56"}},'
'{"column":"Census_OSBuildNumber","DataType":"int","Properties":{"Ordinal":"57"}},'
'{"column":"Census_OSBuildRevision","DataType":"int","Properties":{"Ordinal":"58"}},'
'{"column":"Census_OSEdition","DataType":"string","Properties":{"Ordinal":"59"}},'
'{"column":"Census_OSSkuName","DataType":"string","Properties":{"Ordinal":"60"}},'
'{"column":"Census_OSInstallTypeName","DataType":"string","Properties":{"Ordinal":"61"}},'
'{"column":"Census_OSInstallLanguageIdentifier","DataType":"real","Properties":{"Ordinal":"62"}},'
'{"column":"Census_OSUILocaleIdentifier","DataType":"int","Properties":{"Ordinal":"63"}},'
'{"column":"Census_OSWUAutoUpdateOptionsName","DataType":"string","Properties":{"Ordinal":"64"}},'
'{"column":"Census_IsPortableOperatingSystem","DataType":"int","Properties":{"Ordinal":"65"}},'
'{"column":"Census_GenuineStateName","DataType":"string","Properties":{"Ordinal":"66"}},'
'{"column":"Census_ActivationChannel","DataType":"string","Properties":{"Ordinal":"67"}},'
'{"column":"Census_IsFlightingInternal","DataType":"real","Properties":{"Ordinal":"68"}},'
'{"column":"Census_IsFlightsDisabled","DataType":"real","Properties":{"Ordinal":"69"}},'
'{"column":"Census_FlightRing","DataType":"string","Properties":{"Ordinal":"70"}},'
'{"column":"Census_ThresholdOptIn","DataType":"real","Properties":{"Ordinal":"71"}},'
'{"column":"Census_FirmwareManufacturerIdentifier","DataType":"real","Properties":{"Ordinal":"72"}},'
'{"column":"Census_FirmwareVersionIdentifier","DataType":"real","Properties":{"Ordinal":"73"}},'
'{"column":"Census_IsSecureBootEnabled","DataType":"int","Properties":{"Ordinal":"74"}},'
'{"column":"Census_IsWIMBootEnabled","DataType":"real","Properties":{"Ordinal":"75"}},'
'{"column":"Census_IsVirtualDevice","DataType":"real","Properties":{"Ordinal":"76"}},'
'{"column":"Census_IsTouchEnabled","DataType":"int","Properties":{"Ordinal":"77"}},'
'{"column":"Census_IsPenCapable","DataType":"int","Properties":{"Ordinal":"78"}},'
'{"column":"Census_IsAlwaysOnAlwaysConnectedCapable","DataType":"real","Properties":{"Ordinal":"79"}},'
'{"column":"Wdft_IsGamer","DataType":"real","Properties":{"Ordinal":"80"}},'
'{"column":"Wdft_RegionIdentifier","DataType":"real","Properties":{"Ordinal":"81"}},'
'{"column":"HasDetections","DataType":"int","Properties":{"Ordinal":"82"}}'
']'

 

ADX에서 ingest 쿼리로 CSV 파일을 로드

이제 마지막 작업으로 CSV를 ADX로 로드한다. CSV ingest는 아래 명령으로 수행한다. SAS token을 생성하고 받은 URI를 이용해 ingest를 수행한다. 약 9분 내외의 로드 시간이 소요되었으며, ADX cluster와 storage는 같은 region에 있었다.

 

ADX 테이블로 CSV ingest 실행

.ingest into table malware_table 'https://<YOUR-STORAGE-ACCOUNT>.blob.core.windows.net/msmalware-dataset/aaa.csv?sv=2020-04-08&st=2021-09-15T08%3A09%3A48Z&se=2021-10-31T08%3A09%3A00Z&sr=b&sp=r&sig=XXX' 

 

ADX에서 데이터 조회

ingest가 완료되면 테이블에서 데이터를 조회할 수 있다. 아래 명령들을 이용해 샘플 데이터를 보고, 전체 row count도 가능하다. 8,921,484 건의 데이터가 있다.

malware_table 
| take 100

malware_table 
| summarize Count=count()
// 8921484

 

이전 문서에서는 Azure event hub의 streaming message를 ADX로 ingest하는 과정을 진행했고, 이 문서에서는 batch로 대용량 CSV파일(또는 파일들)을 로드하는 과정을 수행했다.

 

참고링크 : 

Microsoft Malware Prediction | Kaggle

Azure Data Explorer data ingestion overview | Microsoft Docs

Data mappings - Azure Data Explorer | Microsoft Docs

Ingest sample data into Azure Data Explorer | Microsoft Docs

Quickstart: Create an Azure Data Explorer cluster & DB | Microsoft Docs

Grant limited access to data with shared access signatures (SAS) - Azure Storage | Microsoft Docs

Delegate access with a shared access signature - Azure Storage | Microsoft Docs

count() (aggregation function) - Azure Data Explorer | Microsoft Docs

 

No. Subject Author Date Views
Notice [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 124
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 25
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
333 SonarQube 리뷰 및 Azure DevOps 연결 file 코난(김대우) 2021.10.01 111
332 PySpark, koalas와 pandas dataframe file 코난(김대우) 2021.09.29 193
331 Apache Spark, pyspark 설치 후 jupyter notebook 실행 file 코난(김대우) 2021.09.29 186
330 Azure Data Explorer의 데이터를 Python Pandas Dataframe과 CSV로 변환 코난(김대우) 2021.09.28 100
329 Azure Blob Storage SAS token 생성 코난(김대우) 2021.09.17 102
328 Azure Data Factory를 이용해 ADX에서 SQL로 900만건의 데이터 전송 file 코난(김대우) 2021.09.16 156
327 Azure Data Explorer에서 SQL서버 데이터베이스 테이블 조회/삽입 - sql_request plugin file 코난(김대우) 2021.09.16 84
» Azure Data Explorer에 대량 CSV 파일 ingest 코난(김대우) 2021.09.15 84
325 Azure Event Hubs의 데이터를 Azure Data Explorer로 전송 file 코난(김대우) 2021.09.15 116
324 Azure Event Hubs로 kafka message 전송 처리 file 코난(김대우) 2021.09.15 139
323 Service Principal과 Azure 리소스 접근/사용을 위한 인증 방법 3+1가지 file 코난(김대우) 2020.12.26 345
322 Azure storage 관리 도구 - storage explorer 설치와 사용 방법 코난(김대우) 2020.12.25 245
321 Azure cli - command line interface 명령줄 인터페이스 도구를 쓰는 이유와 방법 코난(김대우) 2020.12.25 250
320 클라우드 오픈소스 개발환경 - WSL [1] file 코난(김대우) 2020.12.20 966
319 Cloud RoadShow 세션 발표 자료 코난(김대우) 2016.05.04 11334
318 한방에 보는 HDInsight 소개 정보 코난(김대우) 2015.03.12 9788
317 모던 비즈니스를 위한 클라우드 (6) Azure 데이터 서비스 - DaaS 코난(김대우) 2014.11.11 15520
316 모던 비즈니스를 위한 클라우드 (5) 모바일 디바이스를 위한 Mobile Service 코난(김대우) 2014.11.11 16387
315 모던 비즈니스를 위한 클라우드 (4) 클라우드 플랫폼의 주인공은 PaaS! Azure Website 코난(김대우) 2014.11.11 16141
314 모던 비즈니스를 위한 클라우드 (3) 클라우드 서비스의 초석, 인프라스트럭처 코난(김대우) 2014.11.11 14031





XE Login

테스트 팝업
Close