## Introduction

Every December, the programming community gathers for a collective ritual: [Advent of Code](https://adventofcode.com/). Created by Eric Wastl, it is an Advent calendar of small programming puzzles that releases a new challenge every day from December 1st through the 12th.

These aren't your typical "fix the bug" or "build an API" tasks. They are algorithmic challenges that require complex data structures, graph traversal, 3D geometry, cellular automata simulations, and pathfinding algorithms. Naturally, developers usually reach for general-purpose languages like Python, Rust, Go, or C++ to solve them.

### Why not SQL?

Asking a database to solve these problems is generally considered a mistake. Standard SQL is a declarative language designed for retrieving and aggregating relational data, not for imperative game loops or complex state management. It lacks native support for the data structures usually required for these puzzles (like stacks, queues, or trees), and attempting to solve them with standard JOINs usually leads to performance disasters or syntax errors. In short, solving Advent of Code in SQL is widely considered "impossible" - or at least, incredibly painful.

### The ClickHouse approach

At ClickHouse, we don't like the word "impossible." We believe that with the right tools, **everything** is a data problem. ClickHouse isn't just a fast OLAP database; it is a vectorized query engine with a massive library of analytical functions that can be ~~abused~~ bent to solve general-purpose computing problems.

To prove it, we decided to complete the 2025 Advent of Code unconventionally: using **pure ClickHouse SQL**.

### The rules

To ensure we didn't take any shortcuts, we imposed three strict rules on our solutions:

1. **Pure ClickHouse SQL only**: we allowed absolutely no User Defined Functions (UDFs), and specifically no _executable_ UDFs that would allow us to "cheat" by shelling out to Python or Bash. If the query engine couldn't do it natively, we couldn't do it.

2. **Raw inputs only**: in Advent of Code, the input is often a messy text file.  Sometimes a list of numbers, sometimes an ASCII art map, or a block of cryptic instructions. We were not allowed to pre-process this data. The solution query must accept the raw puzzle input string exactly as provided by the AoC challenge and parse it within the query.

3. **"Single query" constraint**: this is the hardest rule of all. We were not allowed to create tables, materialized views, or temporary tables to store intermediate state. The entire puzzle—from parsing the input, to solving Part 1, to solving the (often substantially more complex) Part 2—must be executed as a **single, atomic query**. This required us to rely heavily on CTEs to chain our logic together in one uninterrupted execution.

Below are the solutions for all 12 days of Advent of Code 2025, demonstrating how we turned "impossible" algorithmic challenges into pure ClickHouse SQL queries.

>Note: in order to comply with Advent of Code's distribution policy, the queries below use a wrapper `URL()` table to fetch the raw puzzle inputs without exposing them. The original query versions with handling for direct string inputs can be found in our [ClickHouse/TreeHouse](https://github.com/ClickHouse/TreeHouse) repository.

## Day 1: The Secret Entrance

**The Puzzle**: The elves have locked their secret entrance with a rotating dial safe. The puzzle involves simulating the movement of a dial labeled 0-99 based on a sequence of instructions like L68 (turn left 68 clicks) or R48 (turn right 48 clicks).

- **Part 1** asks for the final position of the dial after all rotations, starting from 50.
- **Part 2** requires a more complex simulation: counting exactly how many times the dial points to 0 _during_ the entire process, including intermediate clicks as it rotates past zero multiple times.

**How we solved this in ClickHouse SQL:** We treated this simulation as a stream processing problem rather than a procedural loop. Since the state of the dial depends entirely on the history of moves, we can calculate the cumulative position for every single instruction at once. We parsed the directions into positive (Right) and negative (Left) integers, then used a window function to create a running total of steps. For Part 2, where we needed to detect "zero crossings," we compared the current running total with the previous row's total to determine if the dial passed 0.

**Implementation details:**

1. [`sum() OVER (...)`](https://clickhouse.com/docs/en/sql-reference/window-functions): We used standard SQL window functions to maintain the "running total" of the dial's position. By normalizing the left/right directions into positive/negative values, we tracked the cumulative position for every row in a single pass.

```sql
sum(normalized_steps) OVER (ORDER BY instruction_id) AS raw_position
```

2. [`lagInFrame`](https://clickhouse.com/docs/en/sql-reference/window-functions): To count how many times we passed zero, we needed to know where the dial started before the current rotation. We used `lagInFrame` to peek at the position from the previous row. This allowed us to compare the start and end points of a rotation and mathematically determine if 0 fell between them.

**Full Solution:**



[View full puzzle description](https://adventofcode.com/2025/day/1)

---

## Day 2: The Gift Shop

**The Puzzle**: You are helping clean up a gift shop database filled with invalid product IDs. The input is a list of ID ranges (e.g., 11-22, 95-115).

- **Part 1** defines an invalid ID as one composed of a sequence repeated exactly twice (like 1212 or 55).
- **Part 2** expands this to any sequence repeated _at least_ twice (like 123123123 or 11111). The goal is to sum up all invalid IDs found within the given ranges.

**How we solved this in ClickHouse SQL**: Instead of writing a loop to iterate through numbers, we leaned on ClickHouse's ability to "explode" data. We took the compact input ranges (like 11-22) and instantly expanded them into millions of individual rows - one for every integer in the range. Once we had a row for every potential ID, we converted them to strings and applied array functions to check for the repeating patterns in parallel.

**Implementation details**:

1. [`arrayJoin`](https://clickhouse.com/docs/en/sql-reference/functions/array-functions#arrayjoin): This function is our staple for generating rows. We used `range(start, end)` to create an array of integers for each input line, and `arrayJoin` to explode that array into separate rows. This made filtering for invalid IDs a simple `WHERE` clause operation.

```sql
SELECT arrayJoin(range(bounds[1], bounds[2] + 1)) AS number
```

2. [`arrayExists`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayExists): For Part 2, we had to check if any substring length (from 1 up to the string length) formed a repeating pattern. We used `arrayExists` with a lambda function to check every possible substring length. If the lambda returns 1 for any length, the ID is flagged.

```sql
arrayExists(
    x -> (string_length % x = 0) AND (repeat(substring(..., x), ...) = number_string),
    range(1, string_length)
)
```

**Full Solution:**


[View full puzzle description](https://adventofcode.com/2025/day/2)

---

## Day 3: The Lobby

**The Puzzle**: You need to jumpstart an escalator using banks of batteries, where each bank is a string of digits (e.g., 987654321).

- **Part 1** asks you to pick exactly two batteries (digits) to form the largest possible 2-digit number, preserving their original relative order.
- **Part 2** scales this up: pick exactly 12 batteries to form the largest possible 12-digit number. This becomes a greedy optimization problem - you always want the largest available digit that still leaves enough digits after it to complete the sequence.

**How we solved this in ClickHouse SQL**: Part 1 was a straightforward string manipulation, but Part 2 required us to maintain state while iterating through the digits. We needed to track how many digits we still needed to find and our current position in the string so we wouldn't pick digits out of order. We implemented this greedy algorithm directly in SQL using `arrayFold`, which allowed us to iterate through the digits while carrying an accumulator tuple containing our current constraints.

**Implementation details**:

1. [`arrayFold`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayFold): We used this higher-order function to implement `reduce()`-style logic. Our accumulator stored a tuple: `(digits_remaining, current_position, accumulated_value)`. For every step of the fold, we calculated the best valid digit to pick next and updated the state tuple accordingly.

```sql
arrayFold(
    (accumulator, current_element) -> ( ... ), -- Update logic
    digits,
    (num_digits_needed, 0, 0) -- Initial state
)
```

2. [`ngrams`](https://clickhouse.com/docs/sql-reference/functions/splitting-merging-functions#ngrams): To process the string of digits as an array, we used `ngrams(string, 1)`. While typically used for text analysis, here it served as a convenient way to split a string into an array of single characters, which we then cast to integers for the `arrayFold` operation.

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/3)

---

## Day 4: The Printing Department

**The Puzzle**: The elves need to break through a wall of paper rolls. The puzzle is a variation of Conway's Game of Life. You are given a grid where `@` represents a roll of paper.

- **Part 1** defines a rule: a roll can be "accessed" (removed) if it has fewer than 4 neighbors. You count how many rolls fit this criteria initially.
- **Part 2** asks to simulate this process recursively. Removing a roll might open up access to others. You need to keep removing accessible rolls until the system stabilizes, then count the total removed.

**How we solved this in ClickHouse SQL**: Since this problem required iterative simulation where each step depended on the previous one, we used a Recursive CTE. We represented the grid as a set of (x, y) coordinates. In each recursive step, we performed a self-join to count the neighbors for every point. We filtered the list to keep only the points that "survived" (had >= 4 neighbors), implicitly removing the others. We continued this recursion until the count of points stopped changing.

**Implementation details**:

1. [`WITH RECURSIVE`](https://clickhouse.com/docs/sql-reference/statements/select/with#recursive-queries): We used the standard SQL recursive CTE to handle the graph traversal. The base case selected all initial paper roll positions. The recursive step filtered that set down based on neighbor counts.

```sql
WITH RECURSIVE recursive_convergence AS (
    -- Base case: all points
    UNION ALL
    -- Recursive step: keep points with >= 4 neighbors
    SELECT ... HAVING countIf(...) >= 4
)
```

2. [`argMin`](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/argmin): To find the exact moment the simulation stabilized, we tracked the point count at every depth of the recursion. We used `argMin(point_count, depth)` to retrieve the count of remaining points exactly at the minimum depth where the count stopped changing.

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/4)

---

## Day 5: The Cafeteria

**The Puzzle**: The elves have an inventory problem involving lists of "fresh" ID ranges (e.g., 3-5, 10-14).

- **Part 1** asks how many specific item IDs fall into any of the fresh ranges.
- **Part 2** asks for the total count of unique integers covered by the fresh ranges (the union of all intervals). For example, if you have ranges 1-5 and 3-7, the union is 1-7 (size 7), not 1-5 + 3-7 (size 10).

**How we solved this in ClickHouse SQL**: This is a classic interval intersection problem. While Part 1 was a simple filter, Part 2 required merging overlapping intervals. Merging intervals can be mathematically complex to implement manually, but we utilized a specialized ClickHouse aggregation function designed exactly for this purpose, turning a complex geometric algorithm into a one-liner.

**Implementation details**:

1. [`intervalLengthSum`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/intervallengthsum): We used this specialized aggregate function to calculate the total length of the union of intervals. It automatically handles overlapping and nested ranges, saving us from writing complex merging logic.

```sql
SELECT intervalLengthSum(range_tuple.1, range_tuple.2) AS solution
```

2. [`arrayExists`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayExists): For Part 1, we used arrayExists to check if a specific ID fell within _any_ of the valid ranges in the array. This allowed us to perform the check efficiently without exploding the ranges into billions of individual rows.

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/5)

---

## Day 6: The Trash Compactor

**The Puzzle**: You find a math worksheet with problems arranged in columns.

- **Part 1** interprets the input as columns of numbers separated by spaces. You need to sum or multiply the numbers in each column based on the operator at the bottom.
- **Part 2** reveals the input is written "right-to-left" in columns, where digits of a single number are stacked vertically. You must re-parse the grid to reconstruct the numbers, group them by blank columns, and apply the operators.

**How we solved this in ClickHouse SQL**: This puzzle was all about parsing and array manipulation. We treated the input text as a 2D matrix of characters. To switch from the row-based text file to the column-based math problems, we essentially performed a "matrix transposition." We converted the rows of text into arrays of characters, "rotated" them to process columns, and then used array functions to reconstruct the numbers and apply the math operations.

**Implementation details**:

1. [`splitByWhitespace`](https://clickhouse.com/docs/sql-reference/functions/splitting-merging-functions#splitByWhitespace): In Part 1, we used this function to parse the "horizontal" representation. It automatically handled the variable spacing between columns, which would have tripped up simple string splitting.

2. [`arrayProduct`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayProduct): Since ClickHouse lacks a standard `product()` aggregate function, we mapped our columns to arrays of integers and used `arrayProduct` to calculate the multiplication results.

```sql
toInt64(arrayProduct(
    arrayMap(x -> toInt64(x), arraySlice(column, 1, length(column) - 1))
))
```

3. [`arraySplit`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arraySplit): For Part 2, after extracting the raw digits, we needed to group them into valid expressions. We used `arraySplit` to break the large array into chunks whenever we encountered an operator column, effectively separating the mathematical problems.

**Full Solution:**


[View full puzzle description](https://adventofcode.com/2025/day/6)

---

## Day 7: The Teleporter Lab

**The Puzzle**: You are analyzing a tachyon beam in a grid.

- **Part 1** simulates a beam moving downwards. When it hits a splitter `^`, it splits into two beams (left and right). You count the total splits.
- **Part 2** introduces a "quantum many-worlds" twist: instead of splitting the beam, the universe splits. You need to calculate the total number of active "timelines" (paths) at the bottom of the grid.

**How we solved this in ClickHouse SQL**: Simulating individual paths would have caused an exponential explosion. Instead, we approached this like a wave propagation simulation (similar to calculating Pascal's triangle). We processed the grid row-by-row using `arrayFold`. For each row, we maintained a map of "active world counts" at each column position and calculated how the counts flowed into the next row based on the splitters.

**Implementation details**:

1. [`arrayFold`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayFold): We used `arrayFold` to implement the row-by-row simulation state machine. We carried a complex state object - `(left_boundary, right_boundary, worlds_map, part1_counter)` - and updated it for each row of the grid.

2. [`sumMap`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/summap): To handle beams merging (e.g., a left branch and a right branch meeting at the same spot), we used `sumMap`. This allowed us to aggregate values for identical keys in our world map, easily combining the counts of "timelines" converging on a single coordinate. 

```sql
arrayReduce('sumMap', arrayMap(position -> map(...), ...))
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/7)

---

## Day 8: The Playground

**The Puzzle**: The elves are connecting 3D electrical junction boxes.

- **Part 1** asks to connect the 1000 closest pairs of points and analyze the resulting circuit sizes (connected components).
- **Part 2** asks to keep connecting the closest points until _all_ boxes form a single giant circuit (a Minimum Spanning Tree problem).

**How we solved this in ClickHouse SQL**: This is a graph theory problem requiring a disjoint-set (union-find) approach. We generated all possible edges between points and sorted them by distance. Then, we used `arrayFold` to iterate through the edges, merging sets of points into connected components whenever an edge bridged two previously separate groups.

**Implementation details**:

1. [`L2Distance`](https://clickhouse.com/docs/sql-reference/functions/distance-functions#L2Distance): We used ClickHouse's native `L2Distance` function to efficiently calculate the Euclidean distance between 3D coordinates `[x, y, z]`, allowing us to sort the potential connections by length.

2. [`runningAccumulate`](https://clickhouse.com/docs/sql-reference/functions/other-functions#runningAccumulate): For Part 2, we needed to know when we had seen enough unique points to form a single circuit. Instead of running a slow `DISTINCT` count on every row, we used `uniqCombinedState` to create a compact sketch of unique elements, and `runningAccumulate` to merge these sketches row-by-row, providing a running count of unique points efficiently.

```sql
runningAccumulate(points_state) AS unique_points_seen
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/8)

---

## Day 9: The Movie Theater

**The Puzzle**: The theater floor is a grid with some red tiles.

- **Part 1** asks for the largest area rectangle formed using two red tiles as opposite corners.
- **Part 2** adds a constraint: the rectangle must fit entirely inside the loop formed by all the red/green tiles.

**How we solved this in ClickHouse SQL**: We treated this as a geometry problem rather than a grid search. We constructed polygons representing the candidate rectangles and the boundary loop. By converting the bounding boxes into "rings," we could use ClickHouse's native geometry functions to calculate areas and check for containment.

**Implementation details**:

1. [`polygonAreaCartesian`](https://clickhouse.com/docs/sql-reference/functions/geo/polygons#polygonareacartesian): We avoided manual width/height calculations by constructing polygon objects for our rectangles and using `polygonAreaCartesian` to compute their area directly.

2. [`polygonsWithinCartesian`](https://clickhouse.com/docs/sql-reference/functions/geo/polygons#polygonswithincartesian): To check if a rectangle fit inside the loop, we used this containment function. We applied a clever trick here: because geometric functions can be tricky about points shared exactly on an edge, we constructed a slightly **inset** version of the candidate rectangle (shrunk by 0.01 units). This ensured the containment check strictly validated that the rectangle fit _inside_ the boundary polygon without edge alignment errors.

```sql
-- Create slightly inset test bounds (0.01 units inside)
(least(x1, x2) + 0.01, least(y1, y2) + 0.01) AS bottom_left, ...
polygonsWithinCartesian(test_bounds, all_points_ring)
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/9)

---

## Day 10: The Factory

**The Puzzle**: You need to configure factory machines by pressing buttons.

- **Part 1** involves toggling lights (XOR logic) to match a pattern.
- **Part 2** involves incrementing "joltage" counters to reach large target integers using the fewest button presses.

**How we solved this in ClickHouse SQL**: For Part 1, the search space was small enough that we could use brute-force enumeration. We generated every possible button combination and checked it using bitmasks. Part 2 required a smarter approach. We implemented a custom recursive halving algorithm in SQL. We iteratively subtracted button effects and "halved" the remaining target values, reducing the large target numbers down to zero step-by-step.

**Implementation details**:

1. [`bitTest`](https://clickhouse.com/docs/sql-reference/functions/bit-functions#bitTest) and [`bitCount`](https://clickhouse.com/docs/sql-reference/functions/bit-functions#bitCount): We treated button combinations as binary integers. `bitTest` allowed us to check if a specific button was pressed in a combination, and `bitCount` gave us the total number of presses (the cost).

2. [`ARRAY JOIN`](https://clickhouse.com/docs/en/sql-reference/statements/select/array-join): To generate the search space for Part 1, we created a range of integers (0 to 2^N) and used `ARRAY JOIN` to explode them into rows. This created a row for every possible permutation of button presses.

```sql
ARRAY JOIN range(0, toUInt32(pow(2, num_buttons)))
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/10)

---

## Day 11: The Reactor

**The Puzzle**: You are debugging a reactor control graph.

- **Part 1** asks to count all distinct paths from `you` to `out`.
- **Part 2** asks to count paths from `svr` to `out` that satisfy a constraint: they must visit both intermediate nodes `dac` and `fft`.

**How we solved this in ClickHouse SQL**: We solved this using a Recursive CTE to traverse the graph. To handle the constraint in Part 2, we carried "visited flags" in our recursion state. As we traversed the graph, we updated these boolean flags whenever we hit a checkpoint node. At the end, we simply filtered for paths where both flags were true.

**Implementation details**:

1. [`cityHash64`](https://clickhouse.com/docs/sql-reference/functions/hash-functions#cityHash64): String comparisons can be slow in large recursive joins. We converted the node names (like `svr`, `dac`) into deterministic 64-bit integers using `cityHash64`. This made the join operations significantly faster and reduced memory usage.

```sql
cityHash64('svr') AS svr_node
```

2. **State Tracking**: We added boolean columns to our recursive table to track state. This allowed us to solve the "must visit X and Y" constraint in a single pass without needing complex post-processing.

```sql
paths.visited_dac OR (edges.to_node = kn.dac_node) AS visited_dac
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/11)

---

## Day 12: Christmas Tree Farm

**The Puzzle**: Elves need to pack irregular presents (defined by `#` grids) into rectangular regions. This looks like a complex 2D bin-packing problem. However, the puzzle input allows for a heuristic shortcut: checking if the _total area_ of the presents is less than or equal to the _total area_ of the region is sufficient.

**How we solved this in ClickHouse SQL**: Since we could solve this with a volume check, our solution focused on parsing. We converted the ASCII art shapes into binary grids (arrays of 1s and 0s) and calculated the area (count of 1s) for each. We then multiplied the requested quantity of each present by its area and compared the sum to the region's total size.

**Implementation details**:

1. [`replaceRegexpAll`](https://clickhouse.com/docs/sql-reference/functions/string-replace-functions#replaceRegexpAll): We used regex replacement to turn the visual `#` characters into `1` and `.` into `0`. This transformed the "art" into computable binary strings that we could parse into arrays.

2. [`arraySum`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arraySum): We used the lambda version of `arraySum` to perform a "dot product" operation. We multiplied the volume of each present by its area and summed the results in a single, clean expression.

```sql
arraySum(
    (volume, area) -> volume * area,
    requested_shape_volumes,
    areas_per_shape
)
```

**Full Solution:**

[View full puzzle description](https://adventofcode.com/2025/day/12)

---

## Conclusion

The fact that we successfully solved all 12 of these puzzles using pure ClickHouse SQL demonstrates just how versatile our query engine truly is. This is all made possible because ClickHouse includes a massive standard library of built-in functions that bridge the gap between SQL and general-purpose programming. Throughout this challenge, we utilized over a dozen different [String Functions](https://clickhouse.com/docs/en/sql-reference/functions/string-functions) alongside the [format](https://clickhouse.com/docs/en/sql-reference/table-functions/format) table function to manipulate messy inputs into workable datasets. We relied heavily on [Array Functions](https://clickhouse.com/docs/en/sql-reference/functions/array-functions) like [`arrayMap`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayMap) and [`arrayProduct`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayProduct), but the real heroes were [`arrayReduce`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayReduce) and [`arrayFold`](https://clickhouse.com/docs/sql-reference/functions/array-functions#arrayFold), which allowed us to implement complex functional programming logic and maintain state across iterations. When combined with native [Recursive CTEs](https://clickhouse.com/docs/en/sql-reference/statements/select/with#recursive-cte) for pathfinding, [Distance Functions](https://clickhouse.com/docs/en/sql-reference/functions/distance-functions) for 3D geometry, [Polygon Functions](https://clickhouse.com/docs/sql-reference/functions/geo/polygons) for spatial analysis, and [Bitwise Operations](https://clickhouse.com/docs/en/sql-reference/functions/bit-functions) for logic gates, ClickHouse behaves less like a database and more like a high-performance vector computation engine. 

Due to our self-imposed constraints, these SQL solutions may not be as computationally optimal as implementations written in "real" programming languages like Rust or Python, but they nevertheless yield the exact same results. This experiment proves that when you have the right tools, problems that seem "impossible" in SQL become just another interesting data challenge for ClickHouse. You can view the full set of solution queries in our [ClickHouse/TreeHouse](https://github.com/ClickHouse/TreeHouse) repository.  


>Note: Thomas Neumann completed all Advent of Code puzzles in 2024 using Umbra DB.  His work can be found [here.](https://github.com/neumannt/aoc24)



At ClickHouse, we don't like the word "impossible." We believe that with the right tools, everything is a data problem. To prove it, we decided to complete the 2025 Advent of Code unconventionally: using pure ClickHouse SQL.

large_Advent of code 2025 (2).png

medium_Advent of code 2025 (2).png

small_Advent of code 2025 (2).png

thumbnail_Advent of code 2025 (2).png

Advent of code 2025 (2).png

Solving the "Impossible" in ClickHouse: Advent of Code 2025

Building interactive data-driven applications requires not only a fast database, well-structured data, and optimized queries. Your front-end and microservices also need an easy way to consume the data returned by those queries, preferably via well-structured APIs. 

Today, we are happy to announce the new Automatic Query Endpoints feature, which makes this possible. With just a few clicks, you can create an API endpoint directly from any saved SQL query in the ClickHouse Cloud Console. 

<iframe width="768" height="432" src="https://www.youtube.com/embed/uIh7V3Wnsb4?si=tiQQI7JVNBz5PlRy" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

## Why did we build this?

At ClickHouse, we believe that a truly ‘managed’ database-as-a-service product shouldn’t just abstract away infrastructural complexity, it should also provide a best-in-class experience for developers and analysts who work with data every day.  We recently overhauled the ClickHouse Cloud Console experience to support this, drastically shortening the distance between ClickHouse Cloud services and our control plane UI.  This new UI should make ad-hoc data imports, querying, and visualization relatively simple and painless for many analyst users.  

However, whereas our SQL console UI has become an integral part of many analysts’ daily workstreams, it still lacks a few critical features to provide a truly great experience for developers, who are typically more concerned with programmatic ingestion and consumption of data. For developers, then, the Cloud Console UI primarily serves as a source of truth for their data (i.e. does the data exist in table(s), what volume of data is stored, are queries properly optimized, etc.) and a centralized place to create, modify, and monitor ingestion/consumption services.  

![overview.png](/uploads/overview_62d00cd613.png)

In support of this developer-focused use case, we launched ClickPipes—our ClickHouse Cloud-native ingestion platform last year.  ClickPipes initially supported Kafka stream ingestion, but we’ve since implemented support for Redpanda, WarpStream, Azure Event Hub, S3/GCS, Kinesis (most recently), and many other data sources in the near term. In other words, the development of developer-focused tooling around data ingestion is already well underway, so the one remaining piece of functionality is a delightful developer experience around programmatic data consumption.

As we see things, query-based API endpoints represent a simple and flexible framework for programmatic data consumption (and potentially ingestion as well) because they solve two key challenges:

### Challenge #1: Wrangling language clients/drivers

ClickHouse maintains clients for most major programming languages, and most are relatively straightforward to set up. However, deeper functionality that may be immediately useful to developers (connection pooling, keep-alive configuration, compression, streaming, parameterization, etc.) can take some time to figure out.  

Alternatively, it is also fairly easy to hit ClickHouse’s HTTP interface using (for example) the fetch() API in Javascript, but this poses the same set of initial challenges as can be found in language clients—the complexity just moves from client configuration to HTTP header configuration.  

A query-based API endpoints solution can abstract away much of this complexity.

### Challenge #2: Authentication and access control

Even though ClickHouse supports several authentication methods for both Native and HTTP interfaces, in practice, basic auth is (alarmingly) still widely used.  In our experience, many organizations opt for basic auth because it represents the path of least resistance when setting up most language clients/vanilla HTTP requests.  While database-level RBAC can mitigate some of the risks around basic auth, defining the correct user/role grants can often be challenging—especially for new users to ClickHouse

Our query-based API endpoint solution sidesteps this challenge by leveraging our existing Cloud API key/secret authentication pattern. Since each API endpoint effectively represents a single query, access is narrowly scoped to that exact query.

## How it works

To begin, we’ll need a Cloud API key.  API keys can be created and managed from the `API keys` page in the organization-level settings:

![00_api-keys.png](https://cms.clickhouse-dev.com:1337/uploads/00_api_keys_c545e11bfb.png)

Now that we’ve got our key, we can head to the SQL console and create a new query.  For this demo, we’ll use the `youtube` dataset, which contains approximately 4.5 billion records.  As an example query, we’ll return the top 10 uploaders by average views per video in a user-inputted year:

```sql
with sum(view_count) as view_sum,
    round(view_sum / num_uploads, 2) as per_upload
select
    uploader,
    count() as num_uploads,
    formatReadableQuantity(view_sum) as total_views,
    formatReadableQuantity(per_upload) as views_per_video
from
    youtube
where
    toYear(upload_date) = {year: UInt16}
group by uploader
order by per_upload desc
limit 10
```

Note that this query contains a parameter (year).  The SQL console query editor automatically detects ClickHouse query parameter expressions and provides an input for each parameter.  Let’s quickly run this query to make sure that it works: 

![02_sql-console.png](https://cms.clickhouse-dev.com:1337/uploads/02_sql_console_7d45049f54.png)

Next step, we’ll go ahead and save the query:

![01_save-query-as.png](https://cms.clickhouse-dev.com:1337/uploads/01_save_query_as_88e398ab56.png)

Once the query is saved, we can create an API endpoint for it by clicking the ‘Share’ button and selecting ‘API Endpoint’.  We’ll be prompted to specify which API key(s) should be able to access the endpoint:

![03_share_query.png](https://cms.clickhouse-dev.com:1337/uploads/03_share_query_f13e8e3e2f.png)

After selecting an API key, we’ll be good to go!  An example `curl` command demonstrates how we can build requests to the endpoint:

![04_api_endpoint.png](https://cms.clickhouse-dev.com:1337/uploads/04_api_endpoint_b6a4230117.png)

Now, we can try to curl the endpoint:

![endpoints-curltest.png](https://cms.clickhouse-dev.com:1337/uploads/endpoints_curltest_11cc268ccb.png)

Now that we’ve verified that the endpoint works let’s go back to the SQL console.  A new button should appear immediately to the right of the ‘share’ button.  Clicking it will open a flyout containing monitoring data about the query:

![06_insights.png](https://cms.clickhouse-dev.com:1337/uploads/06_insights_30915a022a.png)

### How we built it

To release an initial version of this feature as quickly as possible, we built upon existing functionality in our Cloud Console:

* Saved Queries
* Query parameterization
* Passwordless DB Authentication using certificates
* Server-side query execution
* Existing Cloud API keys as the endpoint authentication method 

In other words, the foundational elements necessary for building query API Endpoints have already been battle-tested in production for months to years.  Implementation primarily required stringing these components together in a new way and extending our SQL console saved queries UI to give users an easy and seamless way to configure and monitor their endpoints.

### What we’re building next

While OpenAPI keys are functional, they are still in Beta—primarily because the current functionality does not provide an optimal solution for direct endpoint calls from a user interface within a webpage. To enhance this user experience, we plan to introduce authentication/authorization via JWT (your own JWT). This will allow these API endpoints to be the building blocks for any developer to construct an analytics application backend. 

A frontend developer would only require two key skills to build their next analytics application: the ability to create a query in ClickHouse (with assistance from our copilot/Gen AI if needed) and proficiency in their preferred web framework.

We also plan to add support for a streaming API, letting you pull all your data via API endpoints without memory constraints.


Today, we are happy to announce Automatic Query Endpoints feature. With just a few clicks, you can create an API endpoint directly from any saved SQL query in the ClickHouse Cloud Console. 

large_Private User Image (3).png

medium_Private User Image (3).png

small_Private User Image (3).png

thumbnail_Private User Image (3).png

Private User Image (3).png

Automatic Query Endpoints: Creating APIs from SQL Queries

Welcome to day 2 of our [launch week](https://clickhouse.com/launch-week/may-2024)!

Last month, we released a big update to ClickHouse Cloud Console, you can read all about it [here](https://clickhouse.com/blog/new-clickhouse-cloud-experience).  In this post, we wanted to share our experience and the knowledge we gained in rebuilding our Cloud Console from the ground up.

## The vision

In late 2022, shortly before ClickHouse Cloud was released, we joined forces with an early-stage startup building a SQL client called Arctype. Following the acquisition, we initially migrated most of Arctype’s SQL client and database visualization functionality into a new ClickHouse Cloud-native (but still standalone) SQL console, but our vision was always to take it one step further — we believe a truly 'managed' DBaaS shouldn't just abstract away infrastructural complexity, it should also provide a best-in-class experience for developers and analysts who work with data every day. In other words, provisioning a ClickHouse Cloud service should only require a couple of clicks, and interacting with this service (and any data contained within it) should not require users to leave the Cloud Console. To achieve this vision, we knew we would need to fold the legacy ClickHouse Cloud Console and the new-fangled SQL Console into a single application.


### Who are we building this for?

Cloud platforms are an interesting beast. You can never really design them with one user in mind. While a lot could be written about the various user personas, for the purposes of this post we'll keep it simple.

First and foremost, there are admin-type users who require a 100ft view of all systems and the ability to manage their user's permissions and security access, as well as monitor their usage and billing information. These users might only spend 1-5 hours _per month_ in any given Cloud interface, but when they need to locate information and complete tasks, speed and ease-of-access are hard requirements.

Second up, we have analyst-type users, who are likely to: view, create, and run queries; import and export large amounts of data; start, stop, and resize ClickHouse services; and share their findings (queries and visualizations) with others. Unlike the Admin user, the Analyst may spend many hours per day in the Cloud interface. They need a comfortable, predictable environment to help immerse themselves in their work.

Third and most important, we have developers.  For developers, a Cloud interface (specifically a Cloud database interface in this case) primarily serves as a source of truth for their data.  For example - does the data exist in table(s), what volume of data is stored, are queries properly optimized, etc.) and a centralized place to create, modify, and monitor ingestion/consumption services.

Suffice to say, each of these user personas represent entirely different patterns of usage, different focuses, and likely differing sets of criteria when judging the quality of our hypothetical new Cloud interface experience.  Designing something with all three in mind was going to be tricky.


### Our meta-level design principles

Whenever you're working with complex systems and a dense amount of data, it's important that it doesn't _feel_ like you're working with complex systems and a dense amount of data. For this reason, we knew that aesthetically, we needed to take this in a clean and light direction. The UI chrome had to be minimal, providing only the most important information in a contextual manner. We opted for clarity and consistency in our approach. Predictability really is the key to any successful user interface—if a user knows what's going to happen next when they perform an operation, they’ll quickly grow to trust and appreciate your application.

As mentioned, we have users who spend multiple hours a day in our Cloud interface. Anywhere you invest such a significant portion of your day has to be comfortable. Comfortability and customizability go hand-in-hand, so we made sure that this new iteration of ClickHouse Cloud was designed in a very modular fashion—one that would allow us to easily switch out colors to create multiple themes.

This was all great in concept, but now we actually needed to build the darn thing.


### This sounds pretty doable; what’s the catch?

There was one slight hiccup on the path towards our agreed vision; it would require us to merge two large codebases into one, but more challenging still, our legacy Cloud Console is written in Angular whereas the SQL Console utilizes React.  In other words, the two applications were written in diametrically-opposing JS frontend frameworks (or more accurately, a framework and a library).

After some spirited debate, we agreed that migrating the legacy Cloud Console Angular application over to React was the best course of action.  There is plenty of literature comparing and contrasting the two, and since our choice of one versus the other is not significant to the story here, we’ll spare everyone the debate blow-by-blow. Suffice to say, however, that anyone familiar with both React and Angular is likely to find our decision understandable (at minimum).

In any event, this meant that achieving our vision effectively entailed performing open heart surgery on (at least) the legacy Cloud Console code base _in addition to_ building a sleek, intuitive, and opinionated end-to-end user experience.  As challenging as that sounded, we had high conviction, and with that, the Unified Console project was born.


## A better design-to-engineering workflow

We'll go into the details later in this post, but before we start, here's the rough overview of our final workflow. We wanted to create a connected system where the design work starts in Figma, syncs tightly with our React component library, and flows right through to our Cloud experience.

![cloud_console_01.png](/uploads/cloud_console_01_63d0479ed9.png)

At this point we had agreed upon both using React as our JS framework, and our design vision, but we still needed to decide on how we were going to rebuild the Cloud Console itself. This time we were thinking longer term, 2-5 years out, and knew we'd be hiring more designers and developers, so we needed a way to ensure new hires could onboard quickly, while still being consistent and true to our visual identity.


## The tokenization of everything

While designing the new version of ClickHouse Cloud, we were also building out our design system in Figma, and as previously mentioned, customizability was a first principle, so we wanted every value to be easily customizable depending on the theme. Not just the colors, but font weight, border radius, shadows, everything. The best way to do this is to use design tokens. Design tokens are essentially variables, they allow us to assign a value to a property, let's say `global.background.default`, and that value can be different depending on the theme. So in a lighter theme, we might see the following:

`global.background.default: white`

while in a darker theme, that same token may have a different value:

`global.background.default: black`

This post isn't really about design tokens, so if you're interested, we recommend watching [this quick 5 minute video](https://youtu.be/wtTstdiBuUk?si=mdC5azu1muKr9_nm) that does a fantastic job of explaining them.

Our aim wasn't to manage a set of design tokens in Figma though — we wanted to establish a connected system where the changes made to the tokens in Figma could flow down through the code and into the product without the developer needing to make any updates.

After some research, we settled on a tool called [Tokens Studio](https://tokens.studio), within which we can store all of our tokens via their Figma plugin and then export them in a JSON format. In more recent months, Tokens Studio has even added seamless syncing with Figma variables, which has been a huge quality-of-life improvement for our designers maintaining multiple themes.

Once the tokens are exported as JSON, we still need a way to convert them into CSS so that they can be used in our components. Enter [Amazon Style Dictionary](https://amzn.github.io/style-dictionary/#/). With just a little [customization](https://github.com/ClickHouse/click-ui/blob/main/build-tokens.js), this incredibly flexible build system took our JSON files and split them per-theme into CSS files full of variables, which could then be imported into our components using a css-in-js library called [styled-components](https://github.com/styled-components/styled-components).


## Click UI

Great progress as it was, this then brought us to our next dilemma — _how do we build these components and where do they live_? In prior roles, some of us have personally been part of teams that had built component libraries from scratch before. While this might be rewarding, at ClickHouse, we have had neither the time nor the appetite to build and maintain such an _entire_ library. Plus, given the large number of great options readily available today, it would be crazy to build a component library from scratch.

So, rather than reinventing the wheel, we elected to use an existing open source (mostly) headless React component library for the base components, and then use our tokens to add the styling. After taking time to play around with many of the leading options, we settled on [Radix-UI](https://www.radix-ui.com), which offered a comprehensive assortment of components, and also great accessibility out of the box.

As our Design System in Figma was called Click UI, it made sense that our component library should be called the same. We set about importing the components from Radix and applying our own styles and customizations to them. At ClickHouse, we have open source running through our veins, so it was never a question that Click UI would be both open source and built-in-public from day one.

Click UI now has over 40 components and is growing all the time. You can follow along and contribute at [https://github.com/ClickHouse/click-ui](https://github.com/ClickHouse/click-ui) or check out our public Storybook playground at [https://click-ui.vercel.app/](https://click-ui.vercel.app)


### Flexibility vs Consistency

A recurrent problem we found while developing Click UI was choosing between the degree of flexibility and consistency we wanted to embed in our library.

For example, for our `Select` component, we debated whether to expose a generic interface that can work well with a greater number of designs unknown at the time of the development or expose a more restrictive interface, less customizable, but that could make the `Select` component cleaner and easier to use.

In React terms, this would translate between the following two interfaces:
```javascript
<Select>
  {children}
</Select>
```

and
```javascript
<Select options=[{value: ‘value1’, label: ‘label1’}] />
```


While the first case can easily accommodate a change in the design (e.g. show an icon for each option) it opens itself to a wider range of possible (mis)usages.

The second alternative, on the other hand, makes using the component easier and by passing only data, doesn’t allow for styles that might break the design, making the overall application more consistent.

This journey is still ongoing, and consequently, we still haven’t found the perfect balance. Obviously, though, if you had to build a design system for a very mature application, you might understandably lean more toward consistency and ease of use.

Attempting to thread this needle, then, we’ve employed a hybrid approach, wherein we implement internal components with flexible interfaces that can be reused to build external components with more restrictive (and cleaner) interfaces.  This means that developers can effectively choose their own adventures — we provide both flexible primitives and ready-to-go building blocks.


### Development and Testing

Since the beginning, we decided to settle on [storybook](http://storybook.js.org) as the tool for documenting and visually testing our components.


Storybook offers a great user experience in demoing single components and showing how to use them with all their properties. It also offers support for visual testing through an easy-to-set-up integration for github. Each time a new PR is open, a set of checks are run against existing components. In case new changes are found, you can compare the look of the new components with the existing ones and decide whether to accept or decline the new changes.

This approach's limitation is that as the number of variations of each component grows, the number of visual tests needed to check each possible combination of the component grows exponentially.

We also opted to use [Vercel](http://vercel.com) because it offers incredibly easy deployments for Storybook-generated applications and auto-generates preview environments for each new PR that is open. This feature is extremely helpful for facilitating the interaction between designers and developers, and the former can easily go to the generated link and check the latest modifications without having to manually deploy the changes on their local machine.


## Implementing the Unified Console

At this stage we had a large set of designs, and we had a good amount of the components prebuilt thanks to Click UI, so now it was all about putting it together.


### The plan

Before we could pop the Champagne, though, there were many tough decisions to be made about migration. In what order would we convert the pages? Would we release the new experience all at once or take a more iterative approach? How long could we afford to hold off on working on new features to ship this release?

Initially, we planned to split the migration into two phases.  Our then-hypothetical phase 1 would tackle service-level functionality — primarily because our new service-centric model charted a very clear course for migration of these pages.  We still had some open questions about how organization-level pages fit into this model, so we planned to work on these in phase 2, along with onboarding and authentication.  Looking back, we found our original notes outlining this plan:

![cloud_console_02.png](/uploads/cloud_console_02_34610aab51.png)

### Some more challenges

We ultimately followed this outline with one notable exception: initially, we planned to cut-over to the new experience immediately after completing the service-level page migrations (step #7 above).  This would have meant temporarily maintaining a bifurcated experience — similar to the legacy Control Plane / SQL console delineation, just inverted. Instead, we elected to complete both phases of the migration and then utilize a feature flag to incrementally rollout the fully-integrated experience to users over a period of weeks.

This plan also posed a couple of challenges:

* **Net-new feature development** — while the migration was underway, we delayed new feature work as much as possible.  Some critical features, however, could not be postponed, and thus needed to be implemented in both the legacy Angular and new React applications.  Double-work aside, this meant that completing some page migrations felt like trying to hit a moving target.
* **Two experiences, one application** — Because the new console experience is effectively built on-top of the existing legacy SQL console application, we had to simultaneously maintain two versions of the same application, each with slightly different layouts, routing mechanisms, and themes.  As we inched closer and closer to completing the migration, an increasing number of regressions began appearing in the legacy SQL console during release testing (and occasionally in Production as well).  Maintaining stability across both experiences became increasingly difficult.


### Rollout and Beyond

Unsurprisingly, rolling out an entirely new experience to tens-of-thousands of existing users — many of whom are paying customers — is not trivial.  In an ideal world, we would have maintained both experiences for several months and offered existing users the option to transition away from the legacy console.  However, largely because of the challenges described above, maintaining both applications was not going to be a viable option — especially given that our team comprises only a handful of engineers.  In other words, we needed to rip the band-aid off relatively quickly so that the team could resume work on other important feature work.

After roughly a month of testing and iteration during private preview, we felt confident enough in the new Cloud Console to begin rolling it out globally.  Our plan was to initially rollout to 20%, incrementing upwards for 2-3 weeks until we reached 100% (assuming no issues arose).  Inevitably, a couple of UX-related requirements complicated this plan:

* All users in an organization should have the same experience.  This is easily solved using an organization-level feature flag.

* Users who are members of multiple organizations should have the same experience across all of their organizations.  This one was slightly trickier, but conveniently, cursory analysis of our organization/userbase determined that roughly 20% of all organizations contained users in multiple organizations.  Thus, we began our rollout with this segment.

From this point forward, nearly everything went according to plan.  We reached 100% rollout on April 30, and as of the time of publication, the volume of bug reports remains relatively low, and feedback has been overwhelmingly positive.  In a similar vein, we’d like to take this opportunity to thank everyone who took the time to provide us with constructive feedback as well—we view this as crucial to our continued evolution of the Cloud Console experience.

So, what’s next?  There are a number of exciting new features landing in the cloud console over the next few weeks — stay tuned!





We’re excited to share with you our experience and the knowledge we gained in rebuilding our Cloud Console from the ground up.

large_cloud_console_banner.png

medium_cloud_console_banner.png

small_cloud_console_banner.png

thumbnail_cloud_console_banner.png

cloud_console_banner.png

How we Rebuilt the Cloud Console (While Running It)

![grafana_geo_hash.png](/uploads/grafana_geo_hash_7d8ef9e91b.png)

## Introduction

ClickHouse is an open-source column-oriented database management system that was originally developed to power web analytics services such as Google Analytics. Many of ClickHouse's original features were developed to help users make sense of their web analytics data in real-time. One such feature is its powerful IP-based lookup feature.

In this blog post, we will explore how to use ClickHouse's `ip_trie` structured dictionary to perform IP-based geolocation. We will start by importing an open-source GeoIP dataset into ClickHouse and using bit functions to transform the data into a usable format. We will then create an `ip_trie` dictionary from our dataset and use the `dictGet()` function to determine the approximate location of a given IP address.

Finally, we will explore how to use geo hashing to aggregate geospatial data and create rich visualizations in tools like Grafana. With these tools at our disposal, we can gain valuable insights into the geographic distribution of our users and better understand how they interact with our websites and applications. So let's get started and learn how to leverage the power of ClickHouse for IP-based geolocation!

While we use ClickHouse Cloud for our examples, they should be reproducible on a self-managed cluster. Alternatively, start a ClickHouse Cloud trial today, receive $300 of free credit, leave the infrastructure to us and get querying!

## Importing and Transforming GeoIP Data in ClickHouse

For this blog post, we’ll use the publicly available [DB-IP city-level dataset](https://github.com/sapics/ip-location-db#db-ip-database-update-monthly) provided by [DB-IP.com](https://db-ip.com) under the terms of the [CC BY 4.0 license](https://creativecommons.org/licenses/by/4.0/).  

### Previewing the GeoIP Data using url()

From [the readme](https://github.com/sapics/ip-location-db#csv-format), we can see that the data is structured as follows:

```sql
| ip_range_start | ip_range_end | country_code | state1 | state2 | city | postcode | latitude | longitude | timezone |
```

Given this structure, let’s start by taking a peek at the data using the [url()](https://clickhouse.com/docs/en/sql-reference/table-functions/url) table function using the SQL console in ClickHouse Cloud:

```sql
select
    *
from
    url(
        'https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz',
        'CSV',
        '
            ip_range_start IPv4, 
            ip_range_end IPv4, 
            country_code Nullable(String), 
            state1 Nullable(String), 
            state2 Nullable(String), 
            city Nullable(String), 
            postcode Nullable(String), 
            latitude Float64, 
            longitude Float64, 
            timezone Nullable(String)
        '
    )
limit
    20;
```

![db_ip.png](/uploads/db_ip_42ad2cc3ad.png)

To make our lives easier, let’s use the [`URL()`](https://clickhouse.com/docs/en/engines/table-engines/special/url) table engine to create a ClickHouse table object with our field names:

```sql
create table geoip_url(
    ip_range_start IPv4, 
    ip_range_end IPv4, 
    country_code Nullable(String), 
    state1 Nullable(String), 
    state2 Nullable(String), 
    city Nullable(String), 
    postcode Nullable(String), 
    latitude Float64, 
    longitude Float64, 
    timezone Nullable(String)
) engine=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV');
```

![dbip_table.png](/uploads/dbip_table_0d61628fe2.png)

Let’s also check how many records are contained in the dataset:

```sql
select count() from geoip_url;
```

![db_ip_counts.png](/uploads/db_ip_counts_b91e94e514.png)

### Using Bit Functions to Convert IP Ranges to CIDR Notation

Because our `ip_trie` dictionary requires IP address ranges to be expressed in CIDR notation, we’ll need to transform `ip_range_start` and `ip_range_end`.  For reference, here’s a brief [description of CIDR notation from Wikipedia](https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing):

<blockquote style="font-size:12px">
<p>These groups, commonly called CIDR blocks, share an initial sequence of bits in the binary representation of their IP addresses. IPv4 CIDR blocks are identified using a syntax similar to that of IPv4 addresses: a dotted-decimal address, followed by a slash, then a number from 0 to 32, i.e., a.b.c.d/n. The dotted decimal portion is the IPv4 address. The number following the slash is the prefix length, the number of shared initial bits, counting from the most-significant bit of the address. When emphasizing only the size of a network, the address portion of the notation is usually omitted. Thus, a /20 block is a CIDR block with an unspecified 20-bit prefix.</p>
<p>
An IP address is part of a CIDR block and is said to match the CIDR prefix if the initial n bits of the address and the CIDR prefix are the same. An IPv4 address is 32 bits so an n-bit CIDR prefix leaves 32 − n bits unmatched, meaning that 232−n IPv4 addresses match a given n-bit CIDR prefix.
</p>

<p>
An IP address is part of a CIDR block and is said to match the CIDR prefix if the initial n bits of the address and the CIDR prefix are the same. An IPv4 address is 32 bits so an n-bit CIDR prefix leaves 32 − n bits unmatched, meaning that 232−n IPv4 addresses match a given n-bit CIDR prefix.
</p>
</blockquote>

In order to generate a CIDR expression for our IP ranges, we’ll need to compare each range to determine unmatched bits in each address and then determine the smallest possible CIDR range that encompasses the whole range. Here’s a diagram to demonstrate:

![cidr_range_calculation.png](/uploads/cidr_range_calculation_398d9ddf49.png)

In this diagram, we can see that our example IP address range (`161.123.218.0` → `161.123.220.255`) contains 768 possible addresses, spanning three /24 CIDR blocks, two /23 blocks, and two /22 blocks.  As a result, the smallest possible CIDR block that contains the range is a /21 block (containing 2048 addresses).  Amazingly, this entire calculation can be accomplished using a handful of bitwise and mathematical functions supported by ClickHouse.

![cidr_bitwise.png](/uploads/cidr_bitwise_5c078aad31.png)

To begin, we’ll use the `bitXor()` function in ClickHouse, which performs a bitwise XOR operation between two numbers and returns the result of the XOR operation.  The XOR operation compares each bit of the two numbers, outputting 1 if the two digits are different and 0 if they are the same.  For example, the expression `bitXor(5,3)` would evaluate to `6` since 5 is represented in binary as `101` and 3 is represented as `011`.  Comparing each bit produces `110` which is equivalent to `6` in decimal. Let’s see what happens when we use cast our IPs as Integers and apply the `bitXor()` function—note that ClickHouse automatically converts bitXor() outputs into decimal form, so we’ve added an additional column to demonstrate the underlying binary expression:

```sql
select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary
from
    geoip_url
limit
    20;
```

![bitXor_cidr.png](/uploads/bit_Xor_cidr_d388198adf.png)

From here, we can see that `bitXor()` will return the number of addresses in our range (for example 255 between `1.0.0.0` and `1.0.0.255`).

![cidr_unmatched_digits.png](/uploads/cidr_unmatched_digits_ca3209c3d7.png)

Next, we’ll use `log2()` and `ceil()` to count the number of binary digits beginning with the first unmatched bit.  We’ll also need to handle scenarios where the start and end of our IP range are equal (thereby yielding a xor output of 0) because `log2(0)` is undefined:

```sql
select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32-unmatched as cidr_suffix
from
    geoip_url
limit
    20;
```

![cidr_suffix_query.png](/uploads/cidr_suffix_query_13217669e9.png)

In the results above, we can see that the `unmatched` field is effectively calculating the position of the first `1` bit from our `bitXor()` output.  Subtracting this value from 32 yields our CIDR block size (suffix).

![cidr_bitwise_not_and.png](/uploads/cidr_bitwise_not_and_29f829107b.png)

Next, we’ll determine the address at which the CIDR block begins, as this will often differ from the address at the beginning of our range, especially for larger blocks.  To do this, we’ll need to perform a [bitwise NOT operation](https://docs.oracle.com/cd/E41183_01/DR/Bitnot.html) `bitNot()` on the total number of addresses contained in our CIDR block, which can be expressed as `pow(2, n) - 1` where `n` is equal to the value of our `unmatched` field above.  Then, we’ll compare the result with the beginning of our IP range using a [bitwise AND operation](https://support.apple.com/guide/functions/bitand-ffae6d40a070/web#:~:text=The%20BITAND%20function%20returns%20the%20bitwise%20AND%20of%20two%20numbers.&text=value%2D1%3A%20The%20first%20number,%2D2%3A%20The%20second%20number.) `bitAnd()` to determine which bits in both expressions are equal to 1. The result can then be converted back into an IPv4 address by first casting it to `UInt64` and then applying the `toIPv4()` function:

```sql
select
    ip_range_start,
    ip_range_end,
    bitXor(ip_range_start, ip_range_end) as xor,
    bin(xor) as xor_binary,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
from
    geoip_url
limit
    20;
```

![cidr_range_query.png](/uploads/cidr_range_query_1c3caa65d1.png)

Finally, we can concatenate our CIDR address and suffix into a single string.  To make this clean, we’ll also move all of our intermediary expressions into a `WITH` clause.  

```sql
with 
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
    ip_range_start,
    ip_range_end,
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr    
from
    geoip_url
limit
    20;
```

![cidr_final_query.png](/uploads/cidr_final_query_f5a8fb03d9.png)

### Importing the transformed GeoIP Data

For our purposes, we’ll only need the IP range, country code and coordinates, so let’s create a new table and insert our GeoIP data:

```sql
create table geoip (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
) 
engine = MergeTree() 
order by cidr;

insert into 
    geoip
with 
    bitXor(ip_range_start, ip_range_end) as xor,
    if(xor != 0, ceil(log2(xor)), 0) as unmatched,
    32 - unmatched as cidr_suffix,
    toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
select
    concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
    latitude,
    longitude,
    country_code    
from
    geoip_url
```

## Creating an `ip_trie` Dictionary for GeoIP Data

In order to perform low-latency IP lookups in ClickHouse, we’ll leverage dictionaries to store `key -> attributes` mapping for our GeoIP data in-memory.  ClickHouse provides an `ip_trie` [dictionary structure](https://clickhouse.com/docs/en/sql-reference/dictionaries#ip_trie) to map our network prefixes (CIDR blocks) to coordinates and country codes:

```sql
create dictionary ip_trie (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
) 
primary key cidr
source(clickhouse(table ‘geoip’))
layout(ip_trie)
lifetime(3600);
```

![dictionary_create_query.png](/uploads/dictionary_create_query_9b10177087.png)

Just to confirm that everything has been correctly loaded into the dictionary, let’s take a quick peek:

```sql
select * from ip_trie limit 20;
```
![ip_trie_rows.png](/uploads/ip_trie_rows_f0b43e6749.png)

Dictionaries in ClickHouse are periodically refreshed based on the underlying table data and the[ lifetime clause](https://clickhouse.com/docs/en/sql-reference/dictionaries#dictionary-updates) used above. In order to update our GeoIP dictionary to reflect the latest changes in the DB-IP dataset, we’ll just need to reinsert data from the `geoip_url` remote table to our `geoip` table with transformations applied.

## Performing IP Lookups from an `ip_trie` Dictionary

Now that we have GeoIP data loaded into our `ip_trie` dictionary (conveniently also named `ip_trie`), we can start using it for IP geolocation.  This can be accomplished using the `dictGet()` function as follows:

```sql
dictGet(
   ‘<dictionary name>’,
   (‘field1’,’field2’, .. .’fieldN’),
   tuple(<ip address in type IPV4 or UInt>)
)
```

To begin, let’s try looking up a single IP:

![dictGet.png](/uploads/dict_Get_3e9d4f1b31.png)

As we can see, `dictGet()` is returning a tuple of all specified fields contained in our dictionary for this IP.  Let’s now apply this lookup on a table containing many IP addresses.  For this demo, we’ll use an excerpt from the ‘EDGAR Log File Data Sets’ [published by the SEC](https://www.sec.gov/about/data/edgar-log-file-data-sets).

```sql
select
    datetime,
    ip,
    accession,
    extention,
    dictGet(
        'ip_trie',
        ('country_code', 'latitude', 'longitude'),
        tuple(ip)
    ) as geo_info
from
    edgar
where
    geo_info.1 != ''
limit
    1000;
```

![dictGetGeo.png](/uploads/dict_Get_Geo_507fbb6f66.png)

Inevitably, this data is much better interpreted using aggregations, so let’s write a query to determine the country-level breakdown of access log events:

```sql
select 
    dictGet(
        'ip_trie',
        ('country_code', 'latitude', 'longitude'),
        tuple(ip)
    ).1 as country,
    formatReadableQuantity(count()) as num_requests
from edgar
where country != ''
group by country
order by count() desc
```

![dictGetAgg.png](/uploads/dict_Get_Agg_c304ad2a8b.png)

## Aggregating Geospatial Data for Visualizations

Tools like Grafana and Metabase can help us generate geospatial visualizations from our latitude/longitude coordinates, but there is one dilemma: plotting tens of millions of points on the map is prohibitively expensive.  Thus, we need to pre-aggregate our geodata result set before rendering a visualization.  To do this, we’ll create geohashes from our coordinates using [`geohashEncode()`](https://clickhouse.com/docs/en/sql-reference/functions/geo/geohash#geohashencode) with a low value for the `precision` parameter, aggregate over this hash, and use the result of the  `count()` function with logarithmic normalization `log10()` to show density while making sure our heat scores scale nicely:

```sql
with coords as (
    select 
        dictGet(
            'ip_trie',
            ('latitude', 'longitude'),
            tuple(ip)
        ) as coords,
        coords.1 as latitude,
        coords.2 as longitude,
        geohashEncode(longitude,latitude,4) as hash
    from 
        edgar
    where
        longitude != 0 
        and latitude != 0
) 
select 
    hash, 
    count() as heat, 
    round(log10(heat),2) as adj_heat 
from 
    coords 
group by 
    hash
```

![dictGeoHash.png](/uploads/dict_Geo_Hash_02a40f61e0.png)

Using geo-hashing, we’ve now rolled up 20M+ records into ~6300 results.  Loading this query into Grafana to power a `Geo Map` chart panel will produce a rich visualization:

![grafana_geo_hash.png](/uploads/grafana_geo_hash_7d8ef9e91b.png)

NB: we can also add a `$__timeFilter()` macro to the query in Grafana to scope our results to a specific time filter.  You can read more about leveraging Grafana with ClickHouse [in this blog](https://clickhouse.com/blog/visualizing-data-with-grafana).  Similar results can be [achieved in Metabase as well](https://clickhouse.com/blog/visualizing-data-with-metabase).



Discover how ClickHouse dictionaries enable the geo-location of IPs, empowering visualization in popular tools like Grafana. Unveil more powerful insights.

Zach Naimon

Blog posts by Zach Naimon

Solving the "Impossible" in ClickHouse: Advent of Code 2025

Automatic Query Endpoints: Creating APIs from SQL Queries

How we Rebuilt the Cloud Console (While Running It)

IP-based Geolocation in ClickHouse