PPL 질의에서의 Subsearch

이 기능은 실험적 기능이라 프로덕션 환경에서는 사용을 권장하지 않아요. 기능 진행 상황에 대한 업데이트나 피드백이 있으면 OpenSearch 포럼에서 토론에 참여해 주세요.

subsearch(일명 subquery)는 한 질의의 결과를 다른 질의 안에서 사용할 수 있게 해줘요. OpenSearch Piped Processing Language(PPL)는 네 가지 유형의 subsearch 명령을 지원해요.

in
exists
scalar
relation

첫 세 가지 subsearch 명령(in, exists, scalar)은 where 명령(where <boolean expression>)과 검색 필터(search source=* <boolean expression>)에서 사용할 수 있는 표현식이에요. relation subsearch 명령은 join 연산에서 사용하는 문(statement)이에요.

출처: 문서

본문

`in`

in subsearch는 필드의 값이 다른 질의의 결과에 존재하는지 확인할 수 있게 해줘요. 다른 인덱스나 질의의 데이터에 기반해 결과를 필터링하고 싶을 때 유용해요.

문법 (Syntax)

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

사용법 (Usage)

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 ]
source = outer a not in [ source = inner | fields b ]
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`

exists subsearch는 subsearch 질의가 어떤 결과를 반환하는지 확인해요. 관련 레코드의 존재 여부를 확인하고 싶은 상관 서브쿼리(correlated subquery)에서 특히 유용해요.

문법 (Syntax)

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

사용법 (Usage)

다음 예시들은 단순한 집계 비교부터 복잡한 중첩 계산까지 exists subsearch를 구현하는 다양한 방법을 보여줘요.

다음과 같은 가정을 바탕으로 작성되어 있어요.

a와 b는 table outer의 필드예요.
c와 d는 table inner의 필드예요.
e와 f는 table nested의 필드예요.

상관 (Correlated)

다음 예시에서 내부 질의는 외부 질의의 필드를 참조해요(예: a = c), 그로 인해 질의 간에 의존성이 생기죠. subsearch는 외부 질의의 각 행마다 한 번씩 평가돼요.

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 ]
source = outer not exists [ source = inner | where a = c ]
source = table as t1 exists [ source = table as t2 | where t1.a = t2.a ]

비상관 (Uncorrelated)

다음 예시에서 subsearch는 외부 질의와 독립적이에요. 내부 질의는 외부 질의의 어떤 필드도 참조하지 않으므로, 외부 질의에 행이 몇 개 있든 상관없이 한 번만 평가돼요.

source = outer | where exists [ source = inner | where c > 10 ]
source = outer | where not exists [ source = inner | where c > 10 ]

중첩 (Nested)

다음 예시는 하나의 subsearch를 다른 subsearch 안에 중첩시켜 여러 수준의 질의 복잡성을 만드는 방법을 보여줘요. 이 방식은 서로 다른 데이터 소스의 여러 조건이 필요한 복잡한 필터링 시나리오에 유용해요.

source = outer | where exists [ source = inner1 | where a = c and exists [ source = nested | where c = e ] ]
source = outer | where exists [ source = inner1 | where a = c | where exists [ source = nested | where c = e ] ]

`scalar`

scalar subsearch는 비교나 계산에 사용할 수 있는 단일 값을 반환해요. 필드를 다른 질의의 집계 값과 비교해야 할 때 유용해요.

문법 (Syntax)

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

사용법 (Usage)

다음 예시들은 단순한 집계 비교부터 복잡한 중첩 계산까지 scalar subsearch를 구현하는 다양한 방법을 보여줘요.

비상관 (Uncorrelated)

다음 예시에서 scalar subsearch는 외부 질의와 독립적이에요. 이 subsearch들은 계산이나 비교에 사용할 수 있는 단일 값을 검색해요.

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
source = outer | where a > [ source = inner | stats min(c) ] | fields a
source = outer a > [ source = inner | stats min(c) ] | fields a

상관 (Correlated)

다음 예시에서 scalar subsearch는 외부 질의의 필드를 참조해서, 내부 질의 결과가 외부 질의의 각 행에 의존하게 되는 의존성을 만들면서요.

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
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
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 subsearch를 중첩해서 복잡한 비교를 만들거나 한 subsearch 결과를 다른 subsearch 안에서 사용하는 방법을 보여줘요.

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`

relation subsearch는 질의 결과를 join 연산의 데이터셋으로 사용할 수 있게 해줘요. 정적 인덱스에 직접 조인하는 대신 필터링되거나 변환된 데이터셋과 조인해야 할 때 유용해요.

문법 (Syntax)

join on <condition> [ search source=... | ... | ... ] [as alias]

사용법 (Usage)

다음 예시는 join 연산에서 relation subsearch를 사용하는 방법을 보여줘요. 첫 번째 예시는 필터링된 데이터셋과 조인하는 방법이고, 두 번째는 다른 질의 안에 relation subsearch를 중첩하는 방법이에요.

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

예시 (Examples)

다음 예시들은 다중 수준 질의나 여러 subsearch 유형 중첩 같은 질의 시나리오에서 서로 다른 subsearch 유형들이 함께 동작하는 방법을 보여줘요.

복합 질의 예시

다음 예시들은 복잡한 질의에서 서로 다른 유형의 subsearch를 결합하는 방법을 보여줘요.

예시 1: in 및 scalar subsearch를 사용한 질의

다음 질의는 in과 scalar subsearch를 모두 사용해서, 이름이 "forest"로 시작하는 부품(part)을 공급하면서 1994년에 주문된 총 수량의 절반보다 많은 가용 수량(availqty)을 가진 캐나다 공급자(supplier)를 찾아요.

source = supplier
| join ON s_nationkey = n_nationkey nation
| where n_name = 'CANADA'
   and s_suppkey in [ /* in subsearch */
     source = partsupp
     | where ps_partkey in [ /* nested in subsearch */
         source = part
         | where like(p_name, 'forest%')
         | fields p_partkey
       ]
       and ps_availqty > [ /* scalar subsearch */
         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
         | fields half_sum_l_quantity
       ]
     | fields ps_suppkey

예시 2: relation, scalar, exists subsearch를 사용한 질의

다음 질의는 relation, scalar, exists subsearch를 사용해서, 특정 국가 코드에 속하면서 평균 이상의 계좌 잔액(account balance)을 가졌지만 주문을 하나도 하지 않은 고객을 찾아요.

source = [  /* relation subsearch */
  source = customer
    | where substring(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
      and c_acctbal > [ /* scalar subsearch */
          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 [ /* correlated exists subsearch */
          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

제한 사항 (Limitations)

PPL subsearch는 plugins.calcite.enabled가 true로 설정되어 있을 때만 동작해요.

PPL 질의에서의 Subsearch