Azure, AWS, GCP 클라우드 활용 Tip과 강좌 게시판

이곳은 개발자를 위한 Azure, AWS, GCP등 클라우드 활용 Tip과 강좌 게시판 입니다. 클라우드 환경을 개발하면서 알아내신 Tip이나 강좌, 새로운 소식을 적어 주시면 다른 클라우드를 공부하는 개발자 분들에게 큰 도움이 됩니다. 감사합니다. SQLER.com은 개발자와 IT전문가의 지식 나눔을 실천하기 위해 노력하고 있습니다.

이번 포스팅에서는 ADX(Azure Data Explorer - 이하 ADX)에서 SQL쿼리로 주로 사용되는 "SELECT INTO"와 INSERT SELECT 와 같은, 결과셋을 테이블로 복사하는 쿼리를 수행하는 방안에 대해서 정리.

 

Azure Data Explorer - SELECT INTO(CTAS) 또는 INSERT SELECT 쿼리 수행

 

먼저, 결과셋을 테이블로 복사하는 SELECT INTO와 INSERT SELECT에 대해 짧게 리뷰

 

SELECT INTO (CTAS - CREATE TABLE AS SELECT) 구문

일반 구문 예제로, SELECT된 결과를 "EmployeeAddresses" 테이블로 복사한다. 이때, 일반적으로 INTO로 지정한 타겟 테이블을 생성하면서 결과셋(result set) 데이터를 복사한다.

SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,   
    sp.Name AS [State/Province], a.PostalCode  
INTO dbo.EmployeeAddresses  
FROM Person.Person AS c  
    JOIN HumanResources.Employee AS e   
    ON e.BusinessEntityID = c.BusinessEntityID  
    JOIN Person.BusinessEntityAddress AS bea  
    ON e.BusinessEntityID = bea.BusinessEntityID   
    JOIN Person.Address AS a  
    ON bea.AddressID = a.AddressID  
    JOIN Person.StateProvince as sp   
    ON sp.StateProvinceID = a.StateProvinceID;  
GO  

참고링크: INTO Clause (Transact-SQL) - SQL Server | Microsoft Docs

 

INSERT SELECT 구문

SELECT된 결과셋을 INSERT 구문의 테이블에 추가한다. 일반적으로, 테이블은 이미 생성되어 있어야 하고, SELECT의 컬럼과 INSERT의 컬럼이 서로 매칭되어야 한다.

CREATE TABLE dbo.EmployeeSales  
( DataSource   varchar(20) NOT NULL,  
  BusinessEntityID   varchar(11) NOT NULL,  
  LastName     varchar(40) NOT NULL,  
  SalesDollars money NOT NULL  
);  
GO  

--INSERT...SELECT example  
INSERT INTO dbo.EmployeeSales  
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY sp.BusinessEntityID, c.LastName;  
GO  

참고링크: INSERT (Transact-SQL) - SQL Server | Microsoft Docs

 

ADX에서 SELECT INTO와 INSERT SELECT 구문 사용

그럼, ADX에서 실제 쿼리를 이용해 SELECT INTO와 INSERT SELECT를 수행한다.

먼저, 테스트를 위해 ADX에 StormEvents 샘플 데이터를 로드 한다.

 

ADX에 StormEvents 샘플 데이터 로드

// StormEvents 샘플 테이블 생성
.create table StormEvents (StartTime: datetime, EndTime: datetime, EpisodeId: int, EventId: int, State: string, EventType: string, InjuriesDirect: int, InjuriesIndirect: int, DeathsDirect: int, DeathsIndirect: int, DamageProperty: int, DamageCrops: int, Source: string, BeginLocation: string, EndLocation: string, BeginLat: real, BeginLon: real, EndLat: real, EndLon: real, EpisodeNarrative: string, EventNarrative: string, StormSummary: dynamic)

// CSV 파일에서 ingest 수행
.ingest into table StormEvents 'https://raw.githubusercontent.com/CloudBreadPaPa/adx-sample-dataset-stormevents/main/dataset/StormEvents.csv' with (ignoreFirstRecord=true)

// 샘플 데이터 조회
StormEvents
| sort by StartTime desc
| take 10

 

ADX의 ingest query - .set, .append, .set-or-append, .set-or-replace

ADX에서는 이러한 작업을 위해 다음 명령들을 지원한다. 각각의 차이는 아래와 같다.

ADX 쿼리 명령 테이블이 존재 테이블 없음
.set 명령실패 테이블이 생성되고 데이터 ingest
.append 테이블에 데이터 추가(append) 명령실패
.set-or-append 테이블에 데이터추가 테이블이 생성되고 데이터 ingest
.set-or-replace 테이블의 데이터 교체됨(replace) 테이블이 생성되고 데이터 ingest

 

위에서 StormEvents 샘플 테이블을 로드 했다. 코드로 살펴보자.

 

.set - SELECT INTO(CTAS)와 유사

결과셋 조건에 맞춰 테이블을 생성하고 데이터를 추가한다.

// State가 "OHIO"인 데이터만 가지고 OHIOStormEvents 테이블 생성
.set OHIOStormEvents <|
   StormEvents
   | where State == "OHIO"
   | take 100
   
// 테스트 조회
OHIOStormEvents
| take 10

 

.append - INSERT SELECT와 유사

이미 생성된 테이블에 결과셋을 추가한다.

// 우천 테이블을 "Flood" 조건으로 생성하고 데이터 추가 
.set RainStormEvents <|
   StormEvents
   | where EventType == "Flood"
   | take 10
   
// 우천 테이블 테스트 조회
RainStormEvents
| take 10

// 기존 RainStormEvents 테이블에 "Thunderstorm Wind" 조건 결과 추가
.append RainStormEvents <|
    StormEvents
    | where EventType == "Thunderstorm Wind"
    | take 10

// 테스트 조회
RainStormEvents
| take 20

 

.set-or-append"

위의 ".set"과 ".append"를 확인했다면, ".set-or-append" 좀더 유연하다. 테이블이 없으면 생성하고 추가하거나 테이블이 있으면, 결과셋을 추가한다.

.set-or-append RainStormEvents <|
    StormEvents
    | where EventType == "Rip Current"
    | take 10

// 테스트 조회
RainStormEvents
| take 30

 

.set-or-replace

주의해서 사용하자. 기존 테이블이 있으면 데이터를 삭제하고 치환한다. 테이블이 없으면 생성하고 결과셋을 추가한다.

.set-or-replace RainStormEvents <|
    StormEvents
    | where EventType == "Hail"
    | take 10

// 테스트 조회 - 기존 데이터는 지워지고, "Hail" 이벤트만 10건 추가되어 있다.
RainStormEvents
| take 30

 

참고사항

"async" 파라미터 

비동기 처리로 즉시 결과가 리턴 되며, "OperationId"가 리턴 된다. ".show operations" 명령으로 ingestion 상태를 확인 가능.

 

"distributed" 속성

모든 노드들이 병렬로 처리하도록 속성 설정이 가능. 기본값은 "false". 1G가 넘는 대량 데이터에서 및 serialization이 필요 없을 경우에 유용하다. 작은 데이터에서는 사용을 권장하지 않는다.

 

 

참고링크

CloudBreadPaPa/adx-sample-dataset-stormevents: ADX sample dataset - StormEvents CSV (github.com)

Kusto query ingestion (set, append, replace) - Azure Data Explorer | Microsoft Docs

Tables management - Azure Data Explorer | Microsoft Docs

CREATE TABLE AS SELECT (CTAS) - Azure Synapse Analytics | Microsoft Docs

INTO Clause (Transact-SQL) - SQL Server | Microsoft Docs

No. Subject Author Date Views
339 Azure Event hubs - Apache Avro 데이터 처리 file 코난(김대우) 2021.10.28 43
» Azure Data Explorer - SELECT INTO(CTAS) 또는 INSERT SELECT 쿼리 수행 코난(김대우) 2021.10.26 30
337 Azure Data Explorer에서 Trigger 기능 구현 - update policy file 코난(김대우) 2021.10.22 33
336 vscode에서 일관된 팀 단위 개발 환경 구성 - devcontainer file 코난(김대우) 2021.10.19 46
335 Bicep - Azure 클라우드 리소스 배포를 위한 언어 file 코난(김대우) 2021.10.19 25
334 Azure Data Explorer - Event Hub 스트리밍 ingest 중 컬럼추가 file 코난(김대우) 2021.10.18 22
333 SonarQube 리뷰 및 Azure DevOps 연결 file 코난(김대우) 2021.10.01 34
332 PySpark, koalas와 pandas dataframe file 코난(김대우) 2021.09.29 60
331 Apache Spark, pyspark 설치 후 jupyter notebook 실행 file 코난(김대우) 2021.09.29 32
330 Azure Data Explorer의 데이터를 Python Pandas Dataframe과 CSV로 변환 코난(김대우) 2021.09.28 34
329 Azure Blob Storage SAS token 생성 코난(김대우) 2021.09.17 41
328 Azure Data Factory를 이용해 ADX에서 SQL로 900만건의 데이터 전송 file 코난(김대우) 2021.09.16 64
327 Azure Data Explorer에서 SQL서버 데이터베이스 테이블 조회/삽입 - sql_request plugin file 코난(김대우) 2021.09.16 43
326 Azure Data Explorer에 대량 CSV 파일 ingest 코난(김대우) 2021.09.15 40
325 Azure Event Hubs의 데이터를 Azure Data Explorer로 전송 file 코난(김대우) 2021.09.15 45
324 Azure Event Hubs로 kafka message 전송 처리 file 코난(김대우) 2021.09.15 44
323 Service Principal과 Azure 리소스 접근/사용을 위한 인증 방법 3+1가지 file 코난(김대우) 2020.12.26 274
322 Azure storage 관리 도구 - storage explorer 설치와 사용 방법 코난(김대우) 2020.12.25 209
321 Azure cli - command line interface 명령줄 인터페이스 도구를 쓰는 이유와 방법 코난(김대우) 2020.12.25 205
320 클라우드 오픈소스 개발환경 - WSL [1] file 코난(김대우) 2020.12.20 431





XE Login