Como trabalhar com arrays no ClickHouse

Neste guia, você aprenderá a usar arrays no ClickHouse e algumas das funções de array mais usadas.

Um array é uma estrutura de dados em memória que agrupa valores. Chamamos esses valores de elementos do array, e cada elemento pode ser referenciado por um índice, que indica a posição do elemento nesse agrupamento.

Arrays no ClickHouse podem ser criados usando a função array:

array(T)

Ou então, usando []:

[]

Por exemplo, você pode criar um array de números:

SELECT array(1, 2, 3) AS numeric_array

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

Ou um array de strings:

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

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

Ou um array de tipos aninhados, como tuplas:

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

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

Talvez você fique tentado a criar um Array com tipos diferentes assim:

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

No entanto, os elementos do array sempre devem ter um supertipo comum, ou seja, o menor tipo de dado capaz de representar valores de dois ou mais tipos diferentes sem perda, permitindo que sejam usados em conjunto. Se não houver um supertipo comum, você receberá uma exceção ao tentar formar o array:

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)

ao criar arrays dinamicamente, o ClickHouse escolhe o tipo mais estreito que comporta todos os elementos. Por exemplo, se você criar um array de inteiros e números de ponto flutuante, será escolhido um supertipo de 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) │
└─────────────┴────────────────┘

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']] │
└──────────────────────────────────────┴───────────────┴──────────────────────────┴─────────────────────────────┘

Usar o índice com [] é uma forma conveniente de acessar elementos de um array. No ClickHouse, é importante saber que o índice do array sempre começa em 1. Isso pode ser diferente de outras linguagens de programação com as quais você está acostumado, nas quais os arrays são indexados a partir de zero.

Por exemplo, dado um array, você pode selecionar o primeiro elemento dele escrevendo:

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

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

Também é possível usar índices negativos. Dessa forma, você pode selecionar elementos em relação ao último elemento:

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

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

Apesar de os arrays serem indexados a partir de 1, ainda é possível acessar elementos na posição 0. O valor retornado será o valor padrão do tipo do array. No exemplo abaixo, uma string vazia é retornada, pois esse é o valor padrão do tipo de dado String:

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

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

O ClickHouse oferece uma série de funções úteis que operam sobre arrays. Nesta seção, veremos algumas das mais úteis, começando pelas mais simples e avançando em complexidade.

A função length é usada para retornar o número de elementos no array:

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

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

Você também pode usar a função arrayEnumerate para retornar um array com os índices dos elementos:

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

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

Se você quiser encontrar o índice de um determinado valor, pode usar a função indexOf:

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

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

Observe que essa função retornará o primeiro índice encontrado caso haja vários valores idênticos no array. Se os elementos do seu array estiverem em ordem crescente, você poderá usar a função indexOfAssumeSorted.

As funções has, hasAll e hasAny são úteis para determinar se um array contém um determinado valor. Considere o exemplo a seguir:

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

Até agora, os exemplos foram bem simples. A utilidade dos arrays fica realmente evidente quando eles são usados em um dataset do mundo real.

Usaremos o dataset ontime, que contém dados de voos do Bureau of Transportation Statistics. Você pode encontrar esse dataset no playground SQL.

Selecionamos esse dataset porque os arrays costumam ser muito adequados para trabalhar com dados de séries temporais e podem ajudar a simplificar consultas que, de outra forma, seriam complexas.

Há muitas colunas neste conjunto de dados, mas vamos nos concentrar em um subconjunto delas. Execute a consulta abaixo para ver como são os nossos dados:

-- SELECT
-- *
-- FROM ontime.ontime LIMIT 100

SELECT
    FlightDate,
    Origin,
    OriginCityName,
    Dest,
    DestCityName,
    DepTime,
    DepDelayMinutes,
    ArrTime,
    ArrDelayMinutes
FROM ontime.ontime LIMIT 5

Vamos dar uma olhada nos 10 aeroportos mais movimentados dos EUA em um dia específico escolhido aleatoriamente, por exemplo, '2024-01-01'. Queremos entender quantos voos partem de cada aeroporto. Nossos dados contêm uma linha por voo, mas seria conveniente se pudéssemos agrupar os dados pelo aeroporto de origem e reunir os destinos em um array.

Para isso, podemos usar a função de agregação groupArray, que recebe os valores da coluna especificada de cada linha e os agrupa em um array.

Execute a consulta abaixo para ver como ela funciona:

SELECT
    FlightDate,
    Origin,
    groupArray(toStringCutToZero(Dest)) AS Destinations
FROM ontime.ontime
WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA') AND FlightDate='2024-01-01'
GROUP BY FlightDate, Origin
ORDER BY length(Destinations)

O toStringCutToZero na consulta acima é usado para remover caracteres nulos que aparecem após o código de 3 letras de alguns aeroportos.

Com os dados nesse formato, podemos encontrar facilmente a ordem dos aeroportos mais movimentados calculando o comprimento dos arrays "Destinations" agrupados:

WITH
    '2024-01-01' AS date,
    busy_airports AS (
    SELECT
    FlightDate,
    Origin,
    groupArray(toStringCutToZero(Dest)) AS Destinations
    FROM ontime.ontime
    WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA')
    AND FlightDate = date
    GROUP BY FlightDate, Origin
    ORDER BY length(Destinations)
    )
SELECT
    Origin,
    length(Destinations) AS outward_flights
FROM busy_airports
ORDER BY outward_flights DESC

Vimos na consulta anterior que o Denver International Airport foi o aeroporto com mais voos de saída no dia que escolhemos. Vamos ver quantos desses voos saíram no horário, tiveram atraso de 15 a 30 minutos ou atraso de mais de 30 minutos.

Muitas das funções de array no ClickHouse são as chamadas "funções de ordem superior" e aceitam uma função lambda como primeiro parâmetro. A função arrayMap é um exemplo desse tipo de função de ordem superior e retorna um novo array a partir do array fornecido, aplicando uma função lambda a cada elemento do array original.

Execute a consulta abaixo, que usa a função arrayMap, para ver quais voos tiveram atraso ou saíram no horário. Para pares de origem/destino, ela mostra o número de cauda e o status de cada voo:

WITH arrayMap(
              d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME')),
              groupArray(DepDelayMinutes)
    ) AS statuses

SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    arrayZip(groupArray(Tail_Number), statuses) as tailNumberStatuses
FROM ontime.ontime
WHERE Origin = 'DEN'
  AND FlightDate = '2024-01-01'
  AND DepTime IS NOT NULL
  AND DepDelayMinutes IS NOT NULL
GROUP BY ALL

Na consulta acima, a função arrayMap recebe um array de um único elemento [DepDelayMinutes] e aplica a função lambda d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME' para categorizá-lo. Em seguida, o primeiro elemento do array resultante é extraído com [DepDelayMinutes][1]. A função arrayZip combina o array Tail_Number e o array statuses em um único array.

Em seguida, veremos apenas o número de voos com atraso de 30 minutos ou mais nos aeroportos DEN, ATL e DFW:

SELECT
    Origin,
    OriginCityName,
    length(arrayFilter(d -> d >= 30, groupArray(ArrDelayMinutes))) AS num_delays_30_min_or_more
FROM ontime.ontime
WHERE Origin IN ('DEN', 'ATL', 'DFW')
    AND FlightDate = '2024-01-01'
GROUP BY Origin, OriginCityName
ORDER BY num_delays_30_min_or_more DESC

Na consulta acima, passamos uma função lambda como primeiro argumento para a função arrayFilter. Essa função lambda recebe o atraso em minutos (d) e retorna 1 se a condição for atendida; caso contrário, 0.

d -> d >= 30

Em seguida, vamos descobrir quais pares dos principais aeroportos dos EUA atendem aos mesmos destinos com mais frequência com a ajuda das funções arraySort e arrayIntersect. arraySort recebe um array e ordena os elementos em ordem crescente por padrão, embora você também possa passar uma função lambda para definir a ordem de ordenação. arrayIntersect recebe vários arrays e retorna um array com os elementos presentes em todos eles.

Execute a consulta abaixo para ver essas duas funções de array em ação:

WITH airport_routes AS (
    SELECT 
        Origin,
        arraySort(groupArray(DISTINCT toStringCutToZero(Dest))) AS destinations
    FROM ontime.ontime
    WHERE FlightDate = '2024-01-01'
    GROUP BY Origin
)
SELECT 
    a1.Origin AS airport1,
    a2.Origin AS airport2,
    length(arrayIntersect(a1.destinations, a2.destinations)) AS common_destinations
FROM airport_routes a1
CROSS JOIN airport_routes a2
WHERE a1.Origin < a2.Origin
    AND a1.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
    AND a2.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
ORDER BY common_destinations DESC
LIMIT 10

A consulta funciona em duas etapas principais. Primeiro, ela cria um conjunto de dados temporário chamado airport_routes usando uma Common Table Expression (CTE), analisando todos os voos de 1º de janeiro de 2024 e, para cada aeroporto de origem, montando uma lista ordenada de todos os destinos únicos atendidos por esse aeroporto. No conjunto de resultados airport_routes, por exemplo, DEN pode ter um array com todas as cidades para as quais há voos, como ['ATL', 'BOS', 'LAX', 'MIA', ...], e assim por diante.

Na segunda etapa, a consulta pega cinco grandes hubs dos EUA (DEN, ATL, DFW, ORD e LAS) e compara todos os pares possíveis entre eles. Isso é feito com um cross join, que cria todas as combinações desses aeroportos. Em seguida, para cada par, ela usa a função arrayIntersect para encontrar quais destinos aparecem nas listas de ambos os aeroportos. A função length conta quantos destinos eles têm em comum.

A condição a1.Origin < a2.Origin garante que cada par apareça apenas uma vez. Sem isso, você obteria tanto JFK-LAX quanto LAX-JFK como resultados separados, o que seria redundante, já que representam a mesma comparação. Por fim, a consulta ordena os resultados para mostrar quais pares de aeroportos têm o maior número de destinos em comum e retorna apenas os 10 primeiros. Isso revela quais grandes hubs têm as redes de rotas mais sobrepostas, o que pode indicar mercados competitivos em que várias companhias aéreas atendem aos mesmos pares de cidades, ou hubs que atendem regiões geográficas semelhantes e que poderiam ser usados como pontos alternativos de conexão para viajantes.

Enquanto analisamos os atrasos, vamos usar mais uma função de array de ordem superior, arrayReduce, para encontrar o atraso médio e o atraso máximo de cada rota que parte do Aeroporto Internacional de Denver:

SELECT
    Origin,
    toStringCutToZero(Dest) AS Destination,
    groupArray(DepDelayMinutes) AS delays,
    round(arrayReduce('avg', groupArray(DepDelayMinutes)), 2) AS avg_delay,
    round(arrayReduce('max', groupArray(DepDelayMinutes)), 2) AS worst_delay
FROM ontime.ontime
WHERE Origin = 'DEN'
    AND FlightDate = '2024-01-01'
    AND DepDelayMinutes IS NOT NULL
GROUP BY Origin, Destination
ORDER BY avg_delay DESC

No exemplo acima, usamos arrayReduce para encontrar os atrasos médio e máximo de vários voos que partem de DEN. arrayReduce aplica uma função agregada, especificada no primeiro parâmetro da função, aos elementos do array fornecido, especificado no segundo parâmetro da função.

As funções regulares no ClickHouse têm a propriedade de retornar o mesmo número de linhas que recebem. Há, no entanto, uma função interessante e única que quebra essa regra e que vale a pena conhecer: a função arrayJoin.

arrayJoin "explode" um array, criando uma linha separada para cada elemento. Isso é semelhante às funções SQL UNNEST ou EXPLODE em outros bancos de dados.

Ao contrário da maioria das funções de array, que retornam arrays ou valores escalares, arrayJoin altera fundamentalmente o conjunto de resultados ao multiplicar o número de linhas.

Considere a consulta abaixo, que retorna um array de valores de 0 a 100 em passos de 10. Podemos considerar esse array como diferentes tempos de atraso: 0 minutos, 10 minutos, 20 minutos e assim por diante.

WITH range(0, 100, 10) AS delay
SELECT delay

Podemos escrever uma consulta usando arrayJoin para descobrir quantos atrasos houve até cada um desses tempos, entre dois aeroportos. A consulta abaixo cria um histograma mostrando a distribuição dos atrasos de voos de Denver (DEN) para Miami (MIA) em 1º de janeiro de 2024, usando buckets cumulativos de atraso:

WITH range(0, 100, 10) AS delay,
    toStringCutToZero(Dest) AS Destination

SELECT
    'Up to ' || arrayJoin(delay) || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= arrayJoin(delay)) AS flightsDelayed
FROM ontime.ontime
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY delayTime
ORDER BY flightsDelayed DESC

Na consulta acima, retornamos um array de atrasos usando uma cláusula CTE (cláusula WITH). Destination converte o código de destino em string.

Usamos arrayJoin para explodir o array de atrasos em linhas separadas. Cada valor do array delay se torna sua própria linha com o alias del, e obtemos 10 linhas: uma para del=0, uma para del=10, uma para del=20 etc. Para cada limite de atraso (del), a consulta conta quantos voos tiveram atrasos maiores ou iguais a esse limite usando countIf(DepDelayMinutes >= del).

arrayJoin também tem um equivalente como comando SQL: ARRAY JOIN. A consulta acima é reproduzida abaixo com o comando SQL equivalente para comparação:

WITH range(0, 100, 10) AS delay, 
     toStringCutToZero(Dest) AS Destination

SELECT    
    'Up to ' || del || ' minutes' AS delayTime,
    countIf(DepDelayMinutes >= del) flightsDelayed
FROM ontime.ontime
ARRAY JOIN delay AS del
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY ALL
ORDER BY flightsDelayed DESC

Parabéns! Você aprendeu a trabalhar com arrays no ClickHouse, desde a criação básica de arrays e a indexação até funções poderosas como groupArray, arrayFilter, arrayMap, arrayReduce e arrayJoin. Para continuar sua jornada de aprendizado, explore a referência completa de funções de array para descobrir outras funções, como arrayFlatten, arrayReverse e arrayDistinct. Você também pode querer aprender sobre estruturas de dados relacionadas, como os tipos tupla, JSON e Map, que funcionam bem em conjunto com arrays. Pratique a aplicação desses conceitos aos seus próprios conjuntos de dados e experimente diferentes consultas no playground SQL ou em outros conjuntos de dados de exemplo.

Arrays são um recurso fundamental no ClickHouse, permitindo consultas analíticas eficientes — à medida que você se sentir mais à vontade com as funções de array, verá que elas podem simplificar drasticamente agregações complexas e análises de séries temporais. Para se aprofundar ainda mais em arrays, recomendamos o vídeo do YouTube abaixo com Mark, nosso especialista residente em dados: