컬럼 내 배열 펼치기
컬럼 내 배열 펼치기 (Unnest arrays within a column)
COMPLEX<json> 컬럼을 펼치는(unnest) 방법을 찾고 있다면 Nested columns (중첩 컬럼)을 참고해 주세요. 이 튜토리얼은 배열로 저장된 데이터가 있는 컬럼을 unnest 데이터소스로 펼치는 방법을 보여 드릴게요.
출처: 문서
본문
예를 들어 [a,b] 나 [c,d,f] 같은 값을 가진 dim3 이라는 컬럼이 있다면, unnest 데이터소스는 그 데이터를 a, b 같은 단일 값을 가진 개별 행들의 새 컬럼으로 출력할 수 있어요. 이때 다음을 유의해야 해요:
- 데이터를 unnest하면 전체 행 수가 극적으로 늘어날 수 있어요.
- 배열 안의 배열은 unnest할 수 없어요.
Druid 콘솔이나 API로 데이터를 unnest할 수 있어요. 시작할 때는 중첩된 데이터와 unnest된 데이터를 보기 쉬운 Druid 콘솔을 사용하는 게 좋아요.
사전 준비 (Prerequisites)
quickstart 같은 Druid 클러스터가 필요해요. 클러스터에는 기존 데이터소스가 필요하지 않아요. 이 튜토리얼의 일부로 기본 데이터소스를 로드할 거예요.
중첩 값이 있는 데이터 로드하기 (Load data with nested values)
수집하는 데이터에는 다음과 비슷한 행 몇 개가 있어요:
t:2000-01-01, m1:1.0, m2:1.0, dim1:, dim2:[a], dim3:[a,b], dim4:[x,y], dim5:[a,b]
이 튜토리얼의 초점은 dim3 의 중첩 배열 값이에요.
이 데이터는 SQL 기반 수집 쿼리를 실행하거나 JSON 기반 수집 스펙을 제출해서 로드할 수 있어요. 예시는 nested_data 라는 테이블에 데이터를 로드해요:
-
SQL 기반 수집 (SQL-based ingestion)
REPLACE INTO nested_data OVERWRITE ALL SELECT TIME_PARSE("t") as __time, dim1, dim2, dim3, dim4, dim5, m1, m2 FROM TABLE( EXTERN( '{"type":"inline","data":"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"],\"dim4\":[\"x\",\"y\"],\"dim5\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"],\"dim4\":[\"e\",\"f\"],\"dim5\":[\"a\",\"b\",\"c\",\"d\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}"}', '{"type":"json"}', '[{"name":"t","type":"string"},{"name":"dim1","type":"string"},{"name":"dim2","type":"string"},{"name":"dim3","type":"string"},{"name":"dim4","type":"string"},{"name":"dim5","type":"string"},{"name":"m1","type":"float"},{"name":"m2","type":"double"}]' )) PARTITIONED BY YEAR -
수집 스펙 (Ingestion spec)
{ "type": "index_parallel", "spec": { "ioConfig": { "type": "index_parallel", "inputSource": { "type": "inline", "data":"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"],\"dim4\":[\"x\",\"y\"],\"dim5\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"],\"dim4\":[\"e\",\"f\"],\"dim5\":[\"a\",\"b\",\"c\",\"d\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}" }, "inputFormat": { "type": "json" } }, "tuningConfig": { "type": "index_parallel", "partitionsSpec": { "type": "dynamic" } }, "dataSchema": { "dataSource": "nested_data", "granularitySpec": { "type": "uniform", "queryGranularity": "NONE", "rollup": false, "segmentGranularity": "YEAR" }, "timestampSpec": { "column": "t", "format": "auto" }, "dimensionsSpec": { "dimensions": [ "dim1", "dim2", "dim3", "dim4", "dim5" ] }, "metricsSpec": [ { "name": "m1", "type": "floatSum", "fieldName": "m1" }, { "name": "m2", "type": "doubleSum", "fieldName": "m2" } ] } } }
데이터 보기 (View the data)
이제 데이터가 로드됐으니 다음 쿼리를 실행해 주세요:
SELECT * FROM nested_data
결과에서 dim3 이라는 컬럼이 ["a","b"] 같은 중첩 값을 가진다는 점을 확인해 주세요. 다음에 나오는 예제 쿼리들은 dim3 을 unnest하고 unnest된 레코드에 대해 쿼리를 실행해요. 작성하는 쿼리 유형에 따라 SQL 쿼리로 unnest 또는 네이티브 쿼리로 unnest를 참고해 주세요.
SQL 쿼리로 unnest하기 (Unnest using SQL queries)
다음은 UNNEST 의 일반 구문이에요:
SELECT column_alias_name FROM datasource CROSS JOIN UNNEST(source_expression) AS table_alias_name(column_alias_name)
구문에 대한 자세한 내용은 UNNEST를 참고해 주세요.
데이터소스에서 단일 소스 표현식 unnest하기 (Unnest a single source expression in a datasource)
다음 쿼리는 nested_data 테이블에서 d3 이라는 컬럼을 반환해요. d3 는 소스 컬럼 dim3 의 unnest된 값을 담아요:
SELECT d3 FROM "nested_data" CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)
MV_TO_ARRAY 헬퍼 함수에 주목해 주세요. 이 함수는 dim3 의 멀티-벨류 레코드를 배열로 변환해요. dim3 이 멀티-벨류 문자열 dimension이므로 필요해요.
unnest하는 컬럼이 문자열 dimension이 아니라면 MV_TO_ARRAY 헬퍼 함수를 사용할 필요가 없어요.
가상 컬럼 unnest하기 (Unnest a virtual column)
가상 컬럼(여러 컬럼을 하나로 취급)으로 unnest할 수 있어요. 다음 쿼리는 두 개의 소스 컬럼과 unnest된 데이터를 담은 세 번째 가상 컬럼을 반환해요:
SELECT dim4,dim5,d45 FROM nested_data CROSS JOIN UNNEST(ARRAY[dim4,dim5]) AS example_table(d45)
가상 컬럼 d45 는 두 소스 컬럼의 곱(product)이에요. 전체 행 수가 늘어난 걸 확인해 주세요. nested_data 테이블은 원래 행이 일곱 개뿐이었어요.
가상 컬럼을 unnest하는 또 다른 방법은 ARRAY_CONCAT 으로 연결하는 거예요:
SELECT dim4,dim5,d45 FROM nested_data CROSS JOIN UNNEST(ARRAY_CONCAT(dim4,dim5)) AS example_table(d45)
목표에 따라 어떤 방법을 사용할지 정하세요.
여러 소스 표현식 unnest하기 (Unnest multiple source expressions)
단일 쿼리에 여러 UNNEST 절을 포함할 수 있어요. 각 UNNEST 절에는 다음이 필요해요:
UNNEST(source_expression) AS table_alias_name(column_alias_name)
각 UNNEST 절의 table_alias_name 과 column_alias_name 은 고유해야 해요.
예제 쿼리는 nested_data 데이터소스에서 다음을 반환해요:
- 소스 컬럼
dim3,dim4,dim5 d3으로 별칭이 붙은dim3의 unnest 버전dim4와dim5로 구성되어d45로 별칭이 붙은 unnest된 가상 컬럼
SELECT dim3,dim4,dim5,d3,d45 FROM "nested_data" CROSS JOIN UNNEST(MV_TO_ARRAY("dim3")) AS foo1(d3) CROSS JOIN UNNEST(ARRAY[dim4,dim5]) AS foo2(d45)
테이블의 하위 집합에서 컬럼 unnest하기 (Unnest a column from a subset of a table)
다음 쿼리는 nested_data 테이블의 세 컬럼만 데이터소스로 사용해요. 그 하위 집합에서 dim3 컬럼을 d3 로 unnest하고 d3 를 반환해요.
SELECT d3 FROM (SELECT dim1, dim2, dim3 FROM "nested_data") CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)
필터와 함께 unnest하기 (Unnest with a filter)
쿼리에 필터를 포함해 어떤 행을 unnest할지 지정할 수 있어요. 다음 쿼리는:
dim2를 기준으로 소스 표현식을 필터링해요.dim3의 레코드를d3로 unnest해요.- 필터와 일치하는
dim2레코드가 있는 unnest된d3레코드를 반환해요.
SELECT d3 FROM (SELECT * FROM nested_data WHERE dim2 IN ('abc')) CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3)
UNNEST 절의 결과를 필터링할 수도 있어요. 다음 예시는 인라인 배열 [1,2,3] 을 unnest하지만 필터와 일치하는 행만 반환해요:
SELECT * FROM UNNEST(ARRAY[1,2,3]) AS example_table(d1) WHERE d1 IN ('1','2')
즉, Druid가 다음 조건을 모두 충족하는 행만 반환하는 쿼리를 실행할 수 있다는 뜻이에요:
dim3의 unnest된 값(d3로 별칭)이IN ('b', 'd')와 일치m1의 값이 2 미만
SELECT * FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY("dim3")) AS foo(d3) WHERE d3 IN ('b', 'd') and m1 < 2
조건을 충족하는 행이 하나뿐이므로 쿼리는 행 하나만 반환해요. 필터를 수정하면 결과가 바뀌는 걸 볼 수 있어요.
unnest 후 GROUP BY (Unnest and then GROUP BY)
다음 쿼리는 dim3 을 unnest한 뒤 출력 d3 에 대해 GROUP BY 를 수행해요.
SELECT d3 FROM nested_data CROSS JOIN UNNEST(MV_TO_ARRAY(dim3)) AS example_table(d3) GROUP BY d3
ORDER BY d3 DESC 나 LIMIT 같은 절을 포함해 결과를 더 변환할 수 있어요.
네이티브 쿼리로 unnest하기 (Unnest using native queries)
다음 섹션은 쿼리에서 unnest 데이터소스를 사용하는 방법의 예시를 보여 줘요. 모두 튜토리얼 앞부분에서 만든 nested_data 테이블을 사용해요.
단일 unnest 데이터소스로 여러 컬럼을 unnest할 수 있어요. 다만 새 행 수가 매우 많아질 수 있으니 주의해야 해요.
Scan 쿼리 (Scan query)
다음 네이티브 Scan 쿼리는 데이터소스의 행을 반환하고 unnest 데이터소스 유형을 사용해 dim3 컬럼의 값을 unnest해요:
쿼리 보기 (Show the query)
{
"queryType": "scan",
"dataSource": {
"type": "unnest",
"base": {
"type": "table",
"name": "nested_data"
},
"virtualColumn": {
"type": "expression",
"name": "unnest-dim3",
"expression": "\"dim3\""
}
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"limit": 100,
"columns": [
"__time",
"dim1",
"dim2",
"dim3",
"m1",
"m2",
"unnest-dim3"
],
"granularity": {
"type": "all"
},
"context": {
"debug": true,
"useCache": false
}
}
결과에서 이전보다 행이 더 많고 unnest-dim3 이라는 추가 컬럼이 있다는 점을 확인해 주세요. unnest-dim3 의 값은 dim3 컬럼과 같지만 중첩된 값이 더 이상 중첩되지 않고 각각 별도의 레코드가 돼요.
필터를 구현할 수 있어요. 예를 들어 <dim2> 에 "a" 또는 "abc" 값이 있는 행만 결과를 필터링하도록 Scan 쿼리에 다음을 추가할 수 있어요:
"filter": {
"type": "in",
"dimension": "dim2",
"values": [
"a",
"abc",
]
},
groupBy 쿼리 (groupBy query)
다음 쿼리는 dim3 컬럼의 unnest 버전을 unnest-dim3 컬럼으로 내림차순으로 정렬해 반환해요.
쿼리 보기 (Show the query)
{
"queryType": "groupBy",
"dataSource": {
"type": "unnest",
"base": "nested_data",
"virtualColumn": {
"type": "expression",
"name": "unnest-dim3",
"expression": "\"dim3\""
}
},
"intervals": ["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"],
"granularity": "all",
"dimensions": [
"unnest-dim3"
],
"limitSpec": {
"type": "default",
"columns": [
{
"dimension": "unnest-dim3",
"direction": "descending"
}
],
"limit": 1001
},
"context": {
"debug": true
}
}
topN 쿼리 (topN query)
예제 topN 쿼리는 dim3 을 unnest-dim3 컬럼으로 unnest해요. 쿼리는 unnest된 컬럼을 topN 쿼리의 dimension으로 사용해요. 결과는 topN-unnest-d3 이라는 컬럼으로 출력되고, m1 의 최소값을 나타내는 집계 값인 a0 컬럼을 기준으로 숫자 오름차순으로 정렬돼요.
쿼리 보기 (Show the query)
{
"queryType": "topN",
"dataSource": {
"type": "unnest",
"base": {
"type": "table",
"name": "nested_data"
},
"virtualColumn": {
"type": "expression",
"name": "unnest-dim3",
"expression": "\"dim3\""
},
},
"dimension": {
"type": "default",
"dimension": "unnest-dim3",
"outputName": "topN-unnest-d3",
"outputType": "STRING"
},
"metric": {
"type": "inverted",
"metric": {
"type": "numeric",
"metric": "a0"
}
},
"threshold": 3,
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "floatMin",
"name": "a0",
"fieldName": "m1"
}
],
"context": {
"debug": true
}
}
JOIN 쿼리로 unnest하기 (Unnest with a JOIN query)
이 쿼리는 nested_data 테이블을 자기 자신과 조인하고 unnest된 데이터를 unnest-dim3 이라는 새 컬럼에 출력해요.
쿼리 보기 (Show the query)
{
"queryType": "scan",
"dataSource": {
"type": "unnest",
"base": {
"type": "join",
"left": {
"type": "table",
"name": "nested_data"
},
"right": {
"type": "query",
"query": {
"queryType": "scan",
"dataSource": {
"type": "table",
"name": "nested_data"
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"virtualColumns": [
{
"type": "expression",
"name": "v0",
"expression": "\"m2\"",
"outputType": "FLOAT"
}
],
"resultFormat": "compactedList",
"columns": [
"__time",
"dim1",
"dim2",
"dim3",
"m1",
"m2",
"v0"
],
"context": {
"sqlOuterLimit": 1001,
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
},
"rightPrefix": "j0.",
"condition": "(\"m1\" == \"j0.v0\")",
"joinType": "INNER"
},
"virtualColumn": {
"type": "expression",
"name": "unnest-dim3",
"expression": "\"dim3\""
}
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"resultFormat": "compactedList",
"limit": 1001,
"columns": [
"__time",
"dim1",
"dim2",
"dim3",
"j0.__time",
"j0.dim1",
"j0.dim2",
"j0.dim3",
"j0.m1",
"j0.m2",
"m1",
"m2",
"unnest-dim3"
],
"context": {
"sqlOuterLimit": 1001,
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
가상 컬럼 unnest하기 (Unnest a virtual column)
unnest 데이터소스는 가상 컬럼(virtual column)을 unnest하는 것을 지원해요. 가상 컬럼은 여러 소스 컬럼에서 데이터를 가져올 수 있는 쿼리 가능한 복합 컬럼이에요.
다음 쿼리는 dim45 와 m1 컬럼을 반환해요. dim45 컬럼은 dim4 와 dim5 컬럼의 배열을 담은 가상 컬럼의 unnest 버전이에요.
쿼리 보기 (Show the query)
{
"queryType": "scan",
"dataSource":{
"type": "unnest",
"base": {
"type": "table",
"name": "nested_data"
},
"virtualColumn": {
"type": "expression",
"name": "dim45",
"expression": "array_concat(\"dim4\",\"dim5\")",
"outputType": "ARRAY<STRING>"
},
}
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"resultFormat": "compactedList",
"limit": 1001,
"columns": [
"dim45",
"m1"
],
"granularity": {
"type": "all"
},
"context": {
"debug": true,
"useCache": false
}
}
컬럼과 가상 컬럼 unnest하기 (Unnest a column and a virtual column)
다음 Scan 쿼리는 dim3 컬럼을 d3 로, dim4 와 dim5 로 구성된 가상 컬럼을 d45 컬럼으로 unnest해요. 그런 다음 그 소스 컬럼들과 unnest된 변형들을 반환해요.
쿼리 보기 (Show the query)
{
"queryType": "scan",
"dataSource": {
"type": "unnest",
"base": {
"type": "unnest",
"base": {
"type": "table",
"name": "nested_data"
},
"virtualColumn": {
"type": "expression",
"name": "d3",
"expression": "\"dim3\"",
"outputType": "STRING"
},
},
"virtualColumn": {
"type": "expression",
"name": "d45",
"expression": "array(\"dim4\",\"dim5\")",
"outputType": "ARRAY<STRING>"
},
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"resultFormat": "compactedList",
"limit": 1001,
"columns": [
"dim3",
"d3",
"dim4",
"dim5",
"d45"
],
"context": {
"queryId": "2618b9ce-6c0d-414e-b88d-16fb59b9c481",
"sqlOuterLimit": 1001,
"sqlQueryId": "2618b9ce-6c0d-414e-b88d-16fb59b9c481",
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
더 알아보기 (Learn more)
더 자세한 내용은 다음을 참고해 주세요:
- UNNEST SQL 함수 (UNNEST SQL function) — SQL에서 UNNEST 사용.
- 데이터소스에서 unnest — unnest 데이터소스에 대한 문서.