이번 포스팅에서는 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에서는 이러한 작업을 위해 다음 명령들을 지원한다. 각각의 차이는 아래와 같다.

위에서 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