ClickHouse에서 배열 다루기

ClickHouse에서 배열 다루기

이 가이드에서는 ClickHouse에서 배열을 사용하는 방법과 가장 흔히 쓰이는 배열 함수들을 배워볼게요.

출처: 문서

본문

배열 소개

배열은 값들을 함께 묶는 인메모리 데이터 구조예요. 이 값들을 배열의 요소 라고 부르고, 각 요소는 그 묶음에서 요소의 위치를 나타내는 인덱스로 참조할 수 있어요. ClickHouse에서 배열은 array 함수를 사용해 만들 수 있어요:

array(T)

또는 []로 만들 수도 있어요:

[]

예를 들어 숫자 배열을 만들 수 있어요:

SELECT array(1, 2, 3) AS numeric_array

┌─numeric_array─┐
│ [1,2,3]       │
└───────────────┘

또는 문자열 배열:

SELECT array('hello', 'world') AS string_array

┌─string_array──────┐
│ ['hello','world'] │
└───────────────────┘

또는 튜플 같은 중첩 타입의 배열:

SELECT array(tuple(1, 2), tuple(3, 4))

┌─[(1, 2), (3, 4)]─┐
│ [(1,2),(3,4)]    │
└──────────────────┘

이렇게 서로 다른 타입의 배열을 만들고 싶을 수도 있어요:

SELECT array('Hello', 'world', 1, 2, 3)

하지만 배열 요소는 항상 공통 상위 타입을 가져야 해요. 공통 상위 타입은 손실 없이 두 개 이상의 서로 다른 타입의 값을 나타낼 수 있는 가장 작은 데이터 타입으로, 그것들을 함께 사용할 수 있게 해줘요. 공통 상위 타입이 없으면 배열을 만들려 할 때 예외가 발생해요:

Received exception:
Code: 386. DB::Exception: There is no supertype for types String, String, UInt8, UInt8, UInt8 because some of them are String/FixedString/Enum and some of them are not: In scope SELECT ['Hello', 'world', 1, 2, 3]. (NO_COMMON_TYPE)

배열을 즉석에서 만들 때 ClickHouse는 모든 요소에 맞는 가장 좁은 타입을 골라요. 예를 들어 정수와 부동소수점의 배열을 만들면 float의 상위 타입이 선택돼요:

SELECT [1::UInt8, 2.5::Float32, 3::UInt8] AS mixed_array, toTypeName([1, 2.5, 3]) AS array_type;

┌─mixed_array─┬─array_type─────┐
│ [1,2.5,3]   │ Array(Float64) │
└─────────────┴────────────────┘

서로 다른 타입의 배열 만들기

use_variant_as_common_type 설정을 사용하면 위에서 설명한 기본 동작을 바꿀 수 있어요. 이 설정은 인자 타입에 공통 타입이 없을 때 if/multiIf/array/map 함수의 결과 타입으로 Variant 타입을 사용할 수 있게 해줘요. 예를 들어:

SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    toTypeName(array)
SETTINGS use_variant_as_common_type = 1;

┌─array────────────────────────────────┬─toTypeName(array)────────────────────────────┐
│ [1,'ClickHouse',['Another','Array']] │ Array(Variant(Array(String), String, UInt8)) │
└──────────────────────────────────────┴──────────────────────────────────────────────┘

그런 다음 타입 이름으로 배열에서 타입을 읽을 수도 있어요:

SELECT
    [1, 'ClickHouse', ['Another', 'Array']] AS array,
    array.UInt8,
    array.String,
    array.`Array(String)`
SETTINGS use_variant_as_common_type = 1;

┌─array────────────────────────────────┬─array.UInt8───┬─array.String─────────────┬─array.Array(String)─────────┐
│ [1,'ClickHouse',['Another','Array']] │ [1,NULL,NULL] │ [NULL,'ClickHouse',NULL] │ [[],[],['Another','Array']] │
└──────────────────────────────────────┴───────────────┴──────────────────────────┴─────────────────────────────┘

[]와 함께 인덱스를 사용하면 배열 요소에 편리하게 접근할 수 있어요. ClickHouse에서는 배열 인덱스가 항상 1부터 시작한다는 점을 아는 것이 중요해요. 이는 배열이 0부터 시작하는 다른 프로그래밍 언어와 다를 수 있어요. 예를 들어 배열이 주어지면 다음과 같이 배열의 첫 번째 요소를 선택할 수 있어요:

WITH array('hello', 'world') AS string_array
SELECT string_array[1];

┌─arrayElement⋯g_array, 1)─┐
│ hello                    │
└──────────────────────────┘

음수 인덱스도 사용할 수 있어요. 이렇게 하면 마지막 요소를 기준으로 요소를 선택할 수 있어요:

WITH array('hello', 'world') AS string_array
SELECT string_array[-1];

┌─arrayElement⋯g_array, -1)─┐
│ world                     │
└───────────────────────────┘

배열이 1-기반 인덱스임에도 불구하고 위치 0의 요소에 접근할 수 있어요. 반환되는 값은 배열 타입의 기본 값 이에요. 아래 예제에서는 문자열 데이터 타입의 기본 값이 빈 문자열이므로 빈 문자열이 반환돼요:

WITH ['hello', 'world', 'arrays are great aren\'t they?'] AS string_array
SELECT string_array[0]

┌─arrayElement⋯g_array, 0)─┐
│                          │
└──────────────────────────┘

배열 함수

ClickHouse는 배열에 대해 동작하는 유용한 함수를 많이 제공해요. 이 섹션에서는 가장 유용한 몇 가지를 가장 단순한 것부터 복잡도가 증가하는 순서로 살펴볼게요.

length, arrayEnumerate, indexOf, has* 함수

length 함수는 배열의 요소 수를 반환하는 데 사용돼요:

WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT length(string_array);

┌─length(string_array)─┐
│                    3 │
└──────────────────────┘

arrayEnumerate 함수를 사용해 요소의 인덱스 배열을 반환할 수도 있어요:

WITH array('learning', 'ClickHouse', 'arrays') AS string_array
SELECT arrayEnumerate(string_array);

┌─arrayEnumerate(string_array)─┐
│ [1,2,3]                      │
└──────────────────────────────┘

특정 값의 인덱스를 찾으려면 indexOf 함수를 사용할 수 있어요:

SELECT indexOf([4, 2, 8, 8, 9], 8);

┌─indexOf([4, 2, 8, 8, 9], 8)─┐
│                           3 │
└─────────────────────────────┘

배열에 동일한 값이 여러 개 있으면 이 함수는 마주치는 첫 번째 인덱스를 반환한다는 점에 유의하세요. 배열 요소가 오름차순으로 정렬되어 있으면 indexOfAssumeSorted 함수를 사용할 수 있어요. has, hasAll, hasAny 함수는 배열이 주어진 값을 포함하는지 판단하는 데 유용해요. 다음 예제를 고려해보세요:

WITH ['Airbus A380', 'Airbus A350', 'Airbus A220', 'Boeing 737', 'Boeing 747-400'] AS airplanes
SELECT
    has(airplanes, 'Airbus A350') AS has_true,
    has(airplanes, 'Lockheed Martin F-22 Raptor') AS has_false,
    hasAny(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAny_true,
    hasAny(airplanes, ['Lockheed Martin F-22 Raptor', 'Eurofighter Typhoon']) AS hasAny_false,
    hasAll(airplanes, ['Boeing 737', 'Boeing 747-400']) AS hasAll_true,
    hasAll(airplanes, ['Boeing 737', 'Eurofighter Typhoon']) AS hasAll_false
FORMAT Vertical;

has_true:     1
has_false:    0
hasAny_true:  1
hasAny_false: 0
hasAll_true:  1
hasAll_false: 0

배열 함수로 항공 데이터 탐색하기

지금까지 예제들은 꽤 단순했어요. 배열의 유용성은 실제 세계의 데이터셋에 사용될 때 정말 드러나요. 우리는 교통 통계국의 항공 데이터를 담은 ontime 데이터셋을 사용할 거예요. 이 데이터셋은 SQL playground에서 찾을 수 있어요. 배열이 시계열 데이터 작업에 종종 잘 맞고 복잡한 쿼리를 단순화하는 데 도움이 되므로 이 데이터셋을 선택했어요.

아래에서 "play" 버튼을 클릭하면 문서에서 직접 쿼리를 실행하고 결과를 실시간으로 볼 수 있어요.

groupArray

이 데이터셋에는 많은 컬럼이 있지만, 우리는 컬럼의 부분집합에 집중할 거예요. 아래 쿼리를 실행해 데이터가 어떤지 확인해보세요. 무작위로 고른 특정 날짜, 예를 들어 '2024-01-01'에 미국에서 가장 바쁜 공항 상위 10곳을 살펴볼게요. 각 공항에서 몇 편의 항공편이 출발하는지 이해하고 싶어요. 우리 데이터는 항공편당 한 행을 포함하지만, 데이터를 출발 공항별로 그룹화하고 도착지를 배열로 묶으면 편리하겠죠. 이를 위해 groupArray 집계 함수를 사용할 수 있는데, 각 행의 지정 컬럼 값을 가져와서 배열로 묶어요. 아래 쿼리를 실행해 어떻게 동작하는지 확인해보세요. 위 쿼리의 toStringCutToZero는 공항의 세 글자 지정 뒤에 나타나는 null 문자를 제거하는 데 사용돼요. 이 형식의 데이터로 Roll-up된 "Destinations" 배열의 길이를 찾아 가장 바쁜 공항의 순서를 쉽게 찾을 수 있어요.

arrayMap와 arrayZip

이전 쿼리에서 Denver International Airport가 우리가 고른 특정 날짜에 최외향 항공편이 가장 많은 공항임을 확인했어요. 그 항공편 중 몇 편이 정시였고, 15-30분 지연됐고, 30분 이상 지연됐는지 살펴볼게요. ClickHouse의 많은 배열 함수는 소위 "고차 함수(higher-order functions)"라고 불리며 첫 번째 매개변수로 람다 함수를 받아요. arrayMap 함수는 그러한 고차 함수의 예시이며, 원래 배열의 각 요소에 람다 함수를 적용해 제공된 배열에서 새 배열을 반환해요. arrayMap 함수를 사용해 어떤 항공편이 지연됐는지 정시였는지 확인하는 아래 쿼리를 실행해보세요. 출발지/도착지 쌍에 대해 모든 항공편의 꼬리 번호와 상태를 보여줘요. 위 쿼리에서 arrayMap 함수는 단일 요소 배열 [DepDelayMinutes]를 받아 분류하기 위해 람다 함수 d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME'를 적용해요. 그런 다음 결과 배열의 첫 번째 요소가 [DepDelayMinutes][1]로 추출돼요. arrayZip 함수는 Tail_Number 배열과 statuses 배열을 하나의 배열로 결합해요.

arrayFilter

다음으로 공항 DEN, ATL, DFW에 대해 30분 이상 지연된 항공편 수만 살펴볼게요. 위 쿼리에서 arrayFilter 함수의 첫 번째 인자로 람다 함수를 전달해요. 이 람다 함수 자체가 지연(분)을 받아 조건이 충족되면 1을, 아니면 0을 반환해요.

d -> d >= 30

arraySort와 arrayIntersect

다음으로 arraySort와 arrayIntersect 함수를 사용해 미국 주요 공항 중 어떤 쌍이 가장 흔한 도착지를 제공하는지 알아볼게요. arraySort는 배열을 받아 기본적으로 오름차순으로 요소를 정렬하지만, 정렬 순서를 정의하기 위해 람다 함수를 전달할 수도 있어요. arrayIntersect는 여러 배열을 받아 모든 배열에 존재하는 요소를 포함하는 배열을 반환해요. 아래 쿼리를 실행해 이 두 배열 함수를 실전에서 볼 수 있어요. 이 쿼리는 두 가지 주요 단계로 동작해요. 첫째, 2024년 1월 1일의 모든 항공편을 보고 각 출발 공항에 대해 그 공항이 제공하는 모든 고유 도착지의 정렬된 목록을 만드는 Common Table Expression(CTE)을 사용해 airport_routes라는 임시 데이터셋을 만들어요. airport_routes 결과 집합에서 예를 들어 DEN은 ['ATL', 'BOS', 'LAX', 'MIA', ...]처럼 가는 모든 도시를 포함하는 배열을 가질 수 있어요. 두 번째 단계에서 쿼리는 다섯 개의 미국 주요 허브 공항(DEN, ATL, DFW, ORD, LAS)을 가져와 모든 가능한 쌍을 비교해요. 이는 이 공항들의 모든 조합을 만드는 cross join으로 수행돼요. 그런 다음 각 쌍에 대해 arrayIntersect 함수를 사용해 어떤 도착지가 두 공항 목록 모두에 나타나는지 찾아요. length 함수는 공통으로 가진 도착지 수를 세어요. 조건 a1.Origin < a2.Origin은 각 쌍이 한 번만 나타나도록 보장해요. 이게 없으면 JFK-LAX와 LAX-JFK가 별도의 결과로 나오는데, 같은 비교이므로 중복돼요. 마지막으로 쿼리는 결과를 정렬해 어떤 공항 쌍이 가장 많은 공유 도착지를 가지는지 보여주고 상위 10개만 반환해요. 이는 어떤 주요 허브가 가장 많은 중복 노선망을 가지는지 드러내는데, 이는 여러 항공사가 같은 도시 쌍을 운항하는 경쟁 시장이나 비슷한 지리적 지역을 서비스하여 여행자에게 대체 연결 지점으로 사용될 수 있는 허브를 나타낼 수 있어요.

arrayReduce

지연을 살펴보는 동안 또 다른 고차 배열 함수인 arrayReduce를 사용해 Denver International Airport에서 각 경로의 평균과 최대 지연을 찾아볼게요. 위 예제에서 우리는 arrayReduce를 사용해 DEN에서 다양한 외향 항공편의 평균과 최대 지연을 찾았어요. arrayReduce는 함수의 첫 번째 매개변수에 지정된 집계 함수를 두 번째 매개변수에 지정된 제공 배열의 요소에 적용해요.

arrayJoin

ClickHouse의 일반 함수는 받은 행 수와 같은 수의 행을 반환한다는 특성이 있어요. 하지만 이 규칙을 깨는 흥미롭고 독특한 함수가 하나 있는데, 배울 가치가 있는 arrayJoin 함수예요. arrayJoin은 배열을 가져와 각 요소에 대해 별도의 행을 만들어 배열을 "폭발(explode)"시켜요. 이는 다른 데이터베이스의 UNNEST나 EXPLODE SQL 함수와 유사해요. 배열이나 스칼라 값을 반환하는 대부분의 배열 함수와 달리 arrayJoin은 행 수를 곱해 결과 집합을 근본적으로 바꿔요. 0부터 100까지 10 단계로 값을 반환하는 아래 쿼리를 고려해보세요. 배열을 서로 다른 지연 시간(0분, 10분, 20분 등)으로 생각할 수 있어요. arrayJoin을 사용하는 쿼리를 작성해 두 공항 사이에서 그 분 수까지의 지연이 몇 번 있었는지 계산할 수 있어요. 아래 쿼리는 누적 지연 버킷을 사용해 2024년 1월 1일 덴버(DEN)에서 마이애미(MIA)까지 항공편 지연의 분포를 보여주는 히스토그램을 만들어요. 위 쿼리에서 CTE 절(WITH 절)을 사용해 지연 배열을 반환해요. Destination은 도착지 코드를 문자열로 변환해요. arrayJoin을 사용해 지연 배열을 별도의 행으로 폭발시켜요. delay 배열의 각 값은 별칭 del로 자신의 행이 되고, del=0, del=10, del=20 등 각각에 대해 하나씩 총 10개의 행을 얻어요. 각 지연 임계값(del)에 대해 쿼리는 countIf(DepDelayMinutes >= del)를 사용해 그 임계값 이상으로 지연된 항공편 수를 세어요. arrayJoin에는 동등한 SQL 명령 ARRAY JOIN도 있어요. 위 쿼리는 비교를 위해 위와 동등한 SQL 명령으로 아래에 재현돼요.

다음 단계 (Next steps)

축하해요! 기본 배열 생성과 인덱싱부터 groupArray, arrayFilter, arrayMap, arrayReduce, arrayJoin 같은 강력한 함수까지 ClickHouse에서 배열을 사용하는 방법을 배웠어요. 학습 여정을 계속하려면 완전한 배열 함수 참조를 탐색해 arrayFlatten, arrayReverse, arrayDistinct 같은 추가 함수를 발견해보세요. 배열과 잘 어울리는 tuples, JSON, Map 타입 같은 관련 데이터 구조에 대해서도 배우고 싶을 거예요. 이 개념들을 자신의 데이터셋에 적용해 연습하고, SQL playground나 다른 예제 데이터셋에서 다양한 쿼리를 실험해보세요. 배열은 ClickHouse의 기본 기능으로 효율적인 분석 쿼리를 가능하게 해요 — 배열 함수에 익숙해질수록 복잡한 집계와 시계열 분석을 극적으로 단순화할 수 있다는 것을 발견하게 될 거예요. 배열에 대해 더 재미있게 배우려면 우리의 데이터 전문가 Mark의 아래 YouTube 동영상을 추천해요.

더 알아보기 (Learn more)

• Array functions 참조
• Array 데이터 타입