복합 SQL 질의
복합 SQL 질의 (Complex SQL queries)
단순 SFW(SELECT-FROM-WHERE) 질의 외에도 SQL 플러그인은 서브쿼리(subquery), 조인(join), union, minus 같은 복합 질의를 지원해요. 이 질의들은 둘 이상의 OpenSearch 인덱스에 대해 동작해요. 이런 질의가 내부적으로 어떻게 실행되는지 확인하려면 explain 연산을 사용하세요.
출처: 문서
본문
조인 (Joins)
OpenSearch SQL은 inner join, cross join, left outer join을 지원해요.
제약 사항 (Constraints)
조인에는 여러 제약 사항이 있어요.
-
두 인덱스만 조인할 수 있어요.
-
인덱스에 별칭을 사용해야 해요(예:
people p). -
ON 절 안에는 AND 조건만 사용할 수 있어요.
-
WHERE 문에서 여러 인덱스를 포함하는 트리를 결합하지 마세요. 예를 들어 다음 문장은 동작해요: WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1) 다음 문장은 동작하지 않아요: WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1) -
결과에 GROUP BY 또는 ORDER BY를 사용할 수 없어요.
-
LIMIT with OFFSET(예:
LIMIT 25 OFFSET 25)은 지원되지 않아요.
설명 (Description)
JOIN 절은 각 인덱스에 공통인 값을 사용해 하나 이상의 인덱스에서 열을 결합해요.
문법 (Syntax)
Rule tableSource:
Rule joinPart:
예시 1: Inner join
Inner join은 조인 조건(join predicate)에 따라 두 인덱스의 열을 결합해 새 결과 집합을 만들어요. 두 인덱스를 반복해서 각 문서를 비교해 조인 조건을 만족하는 문서를 찾아요. 선택적으로 JOIN 절 앞에 INNER 키워드를 붙일 수 있어요.
조인 조건은 ON 절로 지정돼요.
SQL 질의:
SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
JOIN employees_nested e
ON a.account_number = e.id
Explain:
explain 출력은 복잡한데, JOIN 절이 서로 다른 질의 플래너 프레임워크에서 실행되는 두 개의 OpenSearch DSL 질의와 연관되기 때문이에요. Physical Plan과 Logical Plan 객체를 살펴보면 해석할 수 있어요.
{
"Physical Plan" : {
"Project [ columns=[a.account_number, a.firstname, a.lastname, e.name, e.id] ]" : {
"Top [ count=200 ]" : {
"BlockHashJoin[ conditions=( a.account_number = e.id ), type=JOIN, blockSize=[FixedBlockSize with size=10000] ]" : {
"Scroll [ employees_nested as e, pageSize=10000 ]" : {
"request" : {
"size" : 200,
"from" : 0,
"_source" : {
"excludes" : [ ],
"includes" : [
"id",
"name"
]
}
}
},
"Scroll [ accounts as a, pageSize=10000 ]" : {
"request" : {
"size" : 200,
"from" : 0,
"_source" : {
"excludes" : [ ],
"includes" : [
"account_number",
"firstname",
"lastname"
]
}
}
},
"useTermsFilterOptimization" : false
}
}
}
},
"description" : "Hash Join algorithm builds hash table based on result of first query, and then probes hash table to find matched rows for each row returned by second query",
"Logical Plan" : {
"Project [ columns=[a.account_number, a.firstname, a.lastname, e.name, e.id] ]" : {
"Top [ count=200 ]" : {
"Join [ conditions=( a.account_number = e.id ) type=JOIN ]" : {
"Group" : [
{
"Project [ columns=[a.account_number, a.firstname, a.lastname] ]" : {
"TableScan" : {
"tableAlias" : "a",
"tableName" : "accounts"
}
}
},
{
"Project [ columns=[e.name, e.id] ]" : {
"TableScan" : {
"tableAlias" : "e",
"tableName" : "employees_nested"
}
}
}
]
}
}
}
}
}
질의는 다음과 같은 결과를 반환해요.
| a.account_number | a.firstname | a.lastname | e.id | e.name |
|---|---|---|---|---|
| 6 | Hattie | Bond | 6 | Jane Smith |
예시 2: Cross join
Cross join(카테시안 조인이라고도 함)은 첫 번째 인덱스의 각 문서를 두 번째 인덱스의 각 문서와 결합해요. 결과 집합은 두 인덱스 문서들의 카테시안 곱(Cartesian product)이에요. 이 연산은 조인 조건을 지정하는 ON 절이 없는 inner join과 비슷해요.
크거나 심지어 중간 크기의 두 인덱스에 cross join을 수행하는 것은 위험해요. 메모리 부족을 방지하기 위해 질의를 종료하는 회로 차단기(circuit breaker)를 트리거할 수 있어요.
SQL 질의:
SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
JOIN employees_nested e
질의는 다음과 같은 결과를 반환해요.
| a.account_number | a.firstname | a.lastname | e.id | e.name |
|---|---|---|---|---|
| 1 | Amber | Duke | 3 | Bob Smith |
| 1 | Amber | Duke | 4 | Susan Smith |
| 1 | Amber | Duke | 6 | Jane Smith |
| 6 | Hattie | Bond | 3 | Bob Smith |
| 6 | Hattie | Bond | 4 | Susan Smith |
| 6 | Hattie | Bond | 6 | Jane Smith |
| 13 | Nanette | Bates | 3 | Bob Smith |
| 13 | Nanette | Bates | 4 | Susan Smith |
| 13 | Nanette | Bates | 6 | Jane Smith |
| 18 | Dale | Adams | 3 | Bob Smith |
| 18 | Dale | Adams | 4 | Susan Smith |
| 18 | Dale | Adams | 6 | Jane Smith |
예시 3: Left outer join
첫 번째 인덱스의 행이 조인 조건을 만족하지 않아도 해당 행을 유지하려면 left outer join을 사용해요. OUTER 키워드는 선택 사항이에요.
SQL 질의:
SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
LEFT JOIN employees_nested e
ON a.account_number = e.id
질의는 다음과 같은 결과를 반환해요.
| a.account_number | a.firstname | a.lastname | e.id | e.name |
|---|---|---|---|---|
| 1 | Amber | Duke | null | null |
| 6 | Hattie | Bond | 6 | Jane Smith |
| 13 | Nanette | Bates | null | null |
| 18 | Dale | Adams | null | null |
Subquery
서브쿼리는 다른 문장 안에서 사용되고 괄호로 묶인 완전한 SELECT 문이에요. explain 출력에서 일부 서브쿼리가 실제로는 실행을 위해 동등한 조인 질의로 변환되는 것을 확인할 수 있어요.
예시 1: Table subquery
SQL 질의:
SELECT a1.firstname, a1.lastname, a1.balance
FROM accounts a1
WHERE a1.account_number IN (
SELECT a2.account_number
FROM accounts a2
WHERE a2.balance > 10000
)
Explain:
{
"Physical Plan" : {
"Project [ columns=[a1.balance, a1.firstname, a1.lastname] ]" : {
"Top [ count=200 ]" : {
"BlockHashJoin[ conditions=( a1.account_number = a2.account_number ), type=JOIN, blockSize=[FixedBlockSize with size=10000] ]" : {
"Scroll [ accounts as a2, pageSize=10000 ]" : {
"request" : {
"size" : 200,
"query" : {
"bool" : {
"filter" : [
{
"bool" : {
"adjust_pure_negative" : true,
"must" : [
{
"bool" : {
"adjust_pure_negative" : true,
"must" : [
{
"bool" : {
"adjust_pure_negative" : true,
"must_not" : [
{
"bool" : {
"adjust_pure_negative" : true,
"must_not" : [
{
"exists" : {
"field" : "account_number",
"boost" : 1
}
}
],
"boost" : 1
}
}
],
"boost" : 1
}
},
{
"range" : {
"balance" : {
"include_lower" : false,
"include_upper" : true,
"from" : 10000,
"boost" : 1,
"to" : null
}
}
}
],
"boost" : 1
}
}
],
"boost" : 1
}
}
],
"adjust_pure_negative" : true,
"boost" : 1
}
},
"from" : 0
}
},
"Scroll [ accounts as a1, pageSize=10000 ]" : {
"request" : {
"size" : 200,
"from" : 0,
"_source" : {
"excludes" : [ ],
"includes" : [
"firstname",
"lastname",
"balance",
"account_number"
]
}
}
},
"useTermsFilterOptimization" : false
}
}
}
},
"description" : "Hash Join algorithm builds hash table based on result of first query, and then probes hash table to find matched rows for each row returned by second query",
"Logical Plan" : {
"Project [ columns=[a1.balance, a1.firstname, a1.lastname] ]" : {
"Top [ count=200 ]" : {
"Join [ conditions=( a1.account_number = a2.account_number ) type=JOIN ]" : {
"Group" : [
{
"Project [ columns=[a1.balance, a1.firstname, a1.lastname, a1.account_number] ]" : {
"TableScan" : {
"tableAlias" : "a1",
"tableName" : "accounts"
}
}
},
{
"Project [ columns=[a2.account_number] ]" : {
"Filter [ conditions=[AND ( AND account_number ISN null, AND balance GT 10000 ) ] ]" : {
"TableScan" : {
"tableAlias" : "a2",
"tableName" : "accounts"
}
}
}
}
]
}
}
}
}
}
질의는 다음과 같은 결과를 반환해요.
| a1.firstname | a1.lastname | a1.balance |
|---|---|---|
| Amber | Duke | 39225 |
| Nanette | Bates | 32838 |
예시 2: From subquery
SQL 질의:
SELECT a.f, a.l, a.a
FROM (
SELECT firstname AS f, lastname AS l, age AS a
FROM accounts
WHERE age > 30
) AS a
Explain:
{
"from" : 0,
"size" : 200,
"query" : {
"bool" : {
"filter" : [
{
"bool" : {
"must" : [
{
"range" : {
"age" : {
"from" : 30,
"to" : null,
"include_lower" : false,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
"_source" : {
"includes" : [
"firstname",
"lastname",
"age"
],
"excludes" : [ ]
}
}
질의는 다음과 같은 결과를 반환해요.
| f | l | a |
|---|---|---|
| Amber | Duke | 32 |
| Dale | Adams | 33 |
| Hattie | Bond | 36 |