SUBQUERY 명령어

subquery 명령어는 PPL 쿼리 하나를 다른 쿼리 안에 중첩시켜, 고급 필터링과 데이터 검색을 가능하게 해요. 서브쿼리가 먼저 실행되고, 그 결과를 외부 쿼리가 필터링·비교·조인에 사용해요.

출처: 문서

본문

subquery 명령어를 사용하면 PPL 쿼리 하나를 다른 쿼리 안에 중첩시켜 고급 필터링과 데이터 검색을 할 수 있어요. 서브쿼리가 먼저 실행되고, 그 결과를 외부 쿼리가 필터링, 비교, 조인에 사용해요.

서브쿼리의 일반적인 사용 사례는 다음과 같아요:

다른 쿼리의 결과를 기준으로 데이터 필터링하기
관련 데이터의 존재 여부 확인하기
다른 테이블의 집계 값을 활용하는 계산 수행하기
동적 조건을 가진 복잡한 조인 만들기

구문 (Syntax)

subquery 명령어의 구문은 다음과 같아요:

subquery: [ source=... | ... | ... ]

서브쿼리는 일반 PPL 쿼리와 같은 구문을 사용하지만 반드시 대괄호로 감싸야 해요. 네 가지 주요 서브쿼리 유형이 있어요:

IN
EXISTS
스칼라(Scalar)
관계(Relation)

IN 서브쿼리

필드 값이 서브쿼리 결과에 존재하는지 테스트해요:

where <field> [not] in [ source=... | ... | ... ]

다음은 IN 서브쿼리 구문의 예시예요:

source = outer | where a in [ source = inner | fields b ]
source = outer | where (a) in [ source = inner | fields b ]
source = outer | where (a,b,c) in [ source = inner | fields d,e,f ]
source = outer | where a not in [ source = inner | fields b ]
source = outer | where (a) not in [ source = inner | fields b ]
source = outer | where (a,b,c) not in [ source = inner | fields d,e,f ]
source = outer a in [ source = inner | fields b ] // search filtering with subquery
source = outer a not in [ source = inner | fields b ] // search filtering with subquery
source = outer | where a in [ source = inner1 | where b not in [ source = inner2 | fields c ] | fields b ] // nested
source = table1 | inner join left = l right = r on l.a = r.a AND r.a in [ source = inner | fields d ] | fields l.a, r.a, b, c //as join filter

EXISTS 서브쿼리

서브쿼리가 결과를 하나라도 반환하는지 테스트해요:

where [not] exists [ source=... | ... | ... ]

다음은 EXISTS 서브쿼리 구문의 예시예요:

// Assumptions: `a`, `b` are fields of table outer, `c`, `d` are fields of table inner,  `e`, `f` are fields of table nested
source = outer | where exists [ source = inner | where a = c ]
source = outer | where not exists [ source = inner | where a = c ]
source = outer | where exists [ source = inner | where a = c and b = d ]
source = outer | where not exists [ source = inner | where a = c and b = d ]
source = outer exists [ source = inner | where a = c ] // search filtering with subquery
source = outer not exists [ source = inner | where a = c ] // search filtering with subquery
source = table as t1 exists [ source = table as t2 | where t1.a = t2.a ] //table alias is useful in exists subquery
source = outer | where exists [ source = inner1 | where a = c and exists [ source = nested | where c = e ] ] //nested
source = outer | where exists [ source = inner1 | where a = c | where exists [ source = nested | where c = e ] ] //nested
source = outer | where exists [ source = inner | where c > 10 ] //uncorrelated exists
source = outer | where not exists [ source = inner | where c > 10 ] //uncorrelated exists
source = outer | where exists [ source = inner ] | eval l = "nonEmpty" | fields l //special uncorrelated exists

스칼라 서브쿼리

비교나 계산에 사용할 수 있는 단일 값을 반환해요:

where <field> = [ source=... | ... | ... ]

다음은 스칼라 서브쿼리 구문의 예시예요:

//Uncorrelated scalar subquery in Select
source = outer | eval m = [ source = inner | stats max(c) ] | fields m, a
source = outer | eval m = [ source = inner | stats max(c) ] + b | fields m, a
//Uncorrelated scalar subquery in Where**
source = outer | where a > [ source = inner | stats min(c) ] | fields a
//Uncorrelated scalar subquery in Search filter
source = outer a > [ source = inner | stats min(c) ] | fields a
//Correlated scalar subquery in Select
source = outer | eval m = [ source = inner | where outer.b = inner.d | stats max(c) ] | fields m, a
source = outer | eval m = [ source = inner | where b = d | stats max(c) ] | fields m, a
source = outer | eval m = [ source = inner | where outer.b > inner.d | stats max(c) ] | fields m, a
//Correlated scalar subquery in Where
source = outer | where a = [ source = inner | where outer.b = inner.d | stats max(c) ]
source = outer | where a = [ source = inner | where b = d | stats max(c) ]
source = outer | where [ source = inner | where outer.b = inner.d OR inner.d = 1 | stats count() ] > 0 | fields a
//Correlated scalar subquery in Search filter
source = outer a = [ source = inner | where b = d | stats max(c) ]
source = outer [ source = inner | where outer.b = inner.d OR inner.d = 1 | stats count() ] > 0 | fields a
//Nested scalar subquery
source = outer | where a = [ source = inner | stats max(c) | sort c ] OR b = [ source = inner | where c = 1 | stats min(d) | sort d ]
source = outer | where a = [ source = inner | where c =  [ source = nested | stats max(e) by f | sort f ] | stats max(d) by c | sort c | head 1 ]

관계(Relation) 서브쿼리

조인 연산에서 동적 오른쪽 데이터를 제공하는 데 사용해요:

| join ON condition [ source=... | ... | ... ]

다음은 관계 서브쿼리 구문의 예시예요:

source = table1 | join left = l right = r on condition [ source = table2 | where d > 10 | head 5 ] //subquery in join right side
source = [ source = table1 | join left = l right = r [ source = table2 | where d > 10 | head 5 ] | stats count(a) by b ] as outer | head 1

설정 (Configuration)

subquery 명령어 동작은 plugins.ppl.subsearch.maxout 설정으로 구성돼요. 이 설정은 서브검색(subsearch)에서 반환할 최대 행 수를 지정해요. 기본값은 10000이에요. 값 0은 제한이 없음을 나타내요.

설정을 갱신하려면 다음 요청을 보내요:

PUT /_plugins/_query/settings
{
  "persistent": {
    "plugins.ppl.subsearch.maxout": "0"
  }
}

예제 1: TPC-H q20

다음 쿼리는 중첩 서브쿼리를 사용한 복잡한 TPC-H 쿼리 20 구현을 보여줘요:

source = supplier
| join ON s_nationkey = n_nationkey nation
| where n_name = 'CANADA'
  and s_suppkey in [
    source = partsupp
    | where ps_partkey in [
        source = part
        | where like(p_name, 'forest%')
        | fields p_partkey
      ]
      and ps_availqty > [
        source = lineitem
        | where l_partkey = ps_partkey
          and l_suppkey = ps_suppkey
          and l_shipdate >= date('1994-01-01')
          and l_shipdate < date_add(date('1994-01-01'), interval 1 year)
        | stats sum(l_quantity) as sum_l_quantity
        | eval half_sum_l_quantity = 0.5 * sum_l_quantity // Stats and Eval commands can combine when issues/819 resolved
        | fields half_sum_l_quantity
      ]
    | fields ps_suppkey
  ]

예제 2: TPC-H q22

다음 쿼리는 EXISTS 및 스칼라 서브쿼리를 사용한 TPC-H 쿼리 22 구현을 보여줘요:

source = [
  source = customer
    | where substring(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
      and c_acctbal > [
          source = customer
          | where c_acctbal > 0.00
            and substring(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
          | stats avg(c_acctbal)
        ]
      and not exists [
          source = orders
          | where o_custkey = c_custkey
        ]
    | eval cntrycode = substring(c_phone, 1, 2)
    | fields cntrycode, c_acctbal
  ] as custsale
| stats count() as numcust, sum(c_acctbal) as totacctbal by cntrycode
| sort cntrycode

SUBQUERY 명령어