지난 포스트에서는 ADX의 sql_request plugin 을 이용해 ADX에서 SQL로 데이터를 조회하고 간단한 INSERT를 수행했다.

하지만, 대량 데이터 처리에는 적합치 않았고, 이를 위해 ADF(Azure Data Factory, 이하 ADF)를 이용하는 방법을 리뷰.

Azure Data Factory를 이용해 ADX에서 SQL로 데이터 전송

ADF는 클라우드 기반 ETL 도구이나 통합 서비스로, 다양한 데이터 커넥션을 지원하며, workflow orchestration을 지원한다.

Introduction to Azure Data Factory - Azure Data Factory | Microsoft Docs

이번의 목표는 ADF로 ADX에서 SQL로 데이터를 전송하는 것이기에, 이 부분에만 집중해서 진행.

Azure Data Factory 생성

아래 문서를 참조하거나, CLI 등으로 ADF를 생성한다.

Create an Azure data factory using the Azure Data Factory UI - Azure Data Factory | Microsoft Docs

ADF를 생성할때 반드시 github이나 azure DevOps 같은 리포지토리를 세팅해야 한다.

이 리포지토리로 ADF의 pipeline이나 connection 등이 저장된다.

기본적인 화면이며 이 Author 부분에서 pipeline을 구성해 ETL을 수행한다.

ADF에서 ADX로 접속하기 위한 Service Principal 생성

ADF에서 ADX로 리소스 접근 인증을 받기 위해서는 identity가 필요하다. 이럴 경우 Service Principal을 생성해 이 과정에서 이용 가능하며, ADF에서 ADX 구성시 이 Service Principal을 기본 이용한다.

Service Principal에 대한 내용은 개발자 커뮤니티 SQLER.com - Service Princial과 Azure 리소스 접근/사용을 위한 인증 방법 3+1가지 문서에서 참조할 수 있다.

az ad sp create-for-rbac -n "<SP이름>" --role Owner --scopes /subscriptions/<구독ID>/resourceGroups/<리소스그룹이름> 

특정 리소스 그룹에 대해 owner 역할을 하는 SP를 위의 명령으로 생성한다. 이어서 ADX 구성시 필요하니 잘 기록해둔다.

참고링크 : Create an Azure service principal – Azure CLI | Microsoft Docs

ADF 접속을 위해 ADX Permission 추가

ADX는 접근 permission을 이렇게 portal에서 추가 가능하다. 아래 화면처럼 Permission에서 Add를 해서 위에서 생성한 SP를 추가한다.

이제 생성한 SP가 ADF에서 허가되어 ADX로 접근이 가능하다.

참고자료 : Manage database permissions in Azure Data Explorer | Microsoft Docs

ADF에서 ADX와 SQL Linked Connection 생성

미리 connection을 생성하면 이후 ingest copy 과정에서 선택만 하면 되서 수월하다.

아래와 같이 Azure SQL Database connection과 ADX connection을 생성한다.

ADX Connection은 보는 바와 같이 위에서 생성한 Service Principal을 이용해 설정한다.

SQL은 이렇게 기본 SQL 인증으로 구성한다. Linked Connection 생성이 모두 완료되면 Ingest 설정을 수행한다.

ADF Ingest 수행

ADF에서 데이터를 복사하는데 여러 옵션들이 있다. 개별 connection을 만들고, 각 단계별 schema를 생성해 pipeline orchestration 하는 방법도 있으며, 이렇게 Ingest로 바로 copy pipeline을 만들고 복사 할 수도 있다.

이렇게 Ingest를 클릭한다.

이어서, Built-in copy를 진행하고, Run once now를 수행.

Source 부분이 나오는데, Azure Data Explorer를 선택하고 위에서 생성한 ADX Linked Connection을 선택한다.

다음 쿼리 항목에서는 지난 포스트에서 생성한 malware 테이블을 지정한다.

우선 개발/테스트 중이니 10건만 보내고 900만건 정도의 데이터는 테스트 이후에 전송한다.

Soruce Data 부분에서 이렇게 테이블명과 query를 넣는다.

malware_table 
| take 10

우선 10건만 테스트 차원에서 수행하고 이후 전체 데이터로 변경한다.

다음은 Target을 지정한다. 생성한 SQL Database를 지정하고 connection 을 구성 완료한다. 그러면, 아래와 같은 table mapping 화면이 나온다.

테이블 mapping은 이렇게 자동으로 진행도 가능하고 미리 SQL Database에 테이블을 생성한 후, Custom으로 매핑도 가능하다. 내 경우는 자동생성으로 진행했다. (SQL Database에 테이블 이름이 "<Custom query>"으로 생성된다. 이후 적절히 보완해야 한다.)

최종 리뷰가 완료되면 아래처럼 Ingest 설정이 종료된다.

이제, 파이프라인을 ADF의 Author에서 실행하자.

ADF 파이프라인 실행

ADF의 Author화면을 보면, 생성한 Ingest가 pipeline으로 생성되어 있다. 다시 한번 체크하고 Debug를 실행해 pipeline을 실행한다. 실행하면, 아래쪽에 pipeline 실행 상태가 표시되고, 문제 없이 완료될 것이다. Azure SQL Database에서 조회해 체크해 보면 10건의 데이터가 잘 들어온 것을 확인 가능하다.

900만건 데이터를 ADX에서 SQL로 전송

이제 pipeline의 "Copy data"를 선택해 아래쪽 "Source" 탭의 쿼리와 Query timeout을 수정한다.

쿼리는 아래처럼 900만건 정도의 데이터를 모두 가져오기 할 수 있도록 지정하고 timeout은 1시간으로 우선 지정했다.

malware_table

실행 오류 #1

Failure happened on 'Source' side. 'Type=Kusto.Data.Exceptions.KustoServicePartialQueryFailureException,Message=Query execution has exceeded the allowed limits (80DA0003): Query result set has exceeded the internal data size limit 67108864 (E_QUERY_RESULT_SET_TOO_LARGE; see https://aka.ms/kustoquerylimits). 

실행오류 #2 

Failure happened on 'Source' side. 'Type=Kusto.Data.Exceptions.KustoServicePartialQueryFailureException,Message=Query execution has exceeded the allowed limits (80DA0003): Query result set has exceeded the internal record count limit 500000 (E_QUERY_RESULT_SET_TOO_LARGE; see https://aka.ms/kustoquerylimits). 

1번과 2번 모두 일반적인 오류라 공식 문서가 가이드 된다.

Query limits - Azure Data Explorer | Microsoft Docs 

이 방법들 중에 나는 900만건 전송이 필요하니 "client request property" 수정 방안을 택했다.

Request properties and ClientRequestProperties - Azure Data Explorer | Microsoft Docs

데이터의 사이즈와 row 건수 모두가 limit이 존재한다. 아래 쿼리를 ADX에 전송하는 형태로 변경했고 해결되었다.

set truncationmaxsize = 6710886400;
set truncationmaxrecords = 10000000;
malware_table

실행오류 #3

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The database 'dwsqldb2' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40544,Class=17,ErrorCode=-2146232060,State=2,Errors=[{Class=17,Number=40544,State=2,Message=The database 'dwsqldb2' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.,},],'

가장 저렴한 개발/테스트 용도의 "basic" 사이즈로 했더니 quota 오류가 발생했다.

10G 정도의 용량과 S3 정도로 sacle을 변경하고 재수행해 해결되었다.

실행오류 #4

Details
Failure happened on 'Source' side. 'Type=System.IO.IOException,Message=Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.,Source=System,''Type=System.Net.Sockets.SocketException,Message=An existing connection was forcibly closed by the remote host,Source=System,'

실행 시간이 1시간 넘게 걸리니, ADF의 Source ADX 설정에서 query timeout을 1시간으로 설정한 부분에서 오류가 났다.

Timeout을 10시간으로 늘리고 해결되었다.

파이프라인이 실행되는 동안, 이렇게 상세한 실행 과정을 볼 수 있다.

ADF를 이용해 ADX에서 SQL로 데이터 전송 리뷰

이렇게 해서 전체 데이터를 ADF를 이용해 ADX에서 SQL Database로 옮길 수 있었다.

SQL Database 뿐만 아니라 Synapse를 이용할 예정인데, 엔진이 같으니 양쪽 모두 문제 없이 가능할 것으로 예상된다.

ADF를 이용한 방법도 좋은 오케스트레이션 기능과 code 없이 ETL을 구성하는 훌륭한 장점을 제공한다.

하지만, 시간이 1시간 20분 가량 소요되었고, 성능을 높이는 옵션이 있기는 하겠으나 월등하게 빠른 처리는 불가능한 것으로 보인다. 위의 화면과 같은 전송 과정을 리뷰하니, 아마도 curosr 형태로 개별 단위 전송이 이루어지는 것으로 판단되며, SQL이 제공하는 BULK INSERT나 no-logging 대량 operation으로는 진행되지 않는 것으로 보인다.(설정이 있는지는 좀더 리서치 해야할 듯)

다음 방법으로는 마지막으로, 코드를 이용해 ADX에서 데이터를 잃고, 이 데이터를 SQL Database로 BULK INSERT 하는 방법을 검토할 예정이다.

참고링크:

Introduction to Azure Data Factory - Azure Data Factory | Microsoft Docs
Copy data to or from Azure Data Explorer - Azure Data Factory & Azure Synapse | Microsoft Docs

Copy and transform data in Azure SQL Database - Azure Data Factory & Azure Synapse | Microsoft Docs

Kusto query result set exceeds internal limit - Azure Data Explorer | Microsoft Docs

Request properties and ClientRequestProperties - Azure Data Explorer | Microsoft Docs

Query limits - Azure Data Explorer | Microsoft Docs

개발자 커뮤니티 SQLER.com - Service Princial과 Azure 리소스 접근/사용을 위한 인증 방법 3+1가지