Catch the bus - Aiven challenge with ClickHouse®#

Welcome to Aiven’s “Catch the bus” challenge, an easy way for you to explore Aiven for ClickHouse®.

With the launch of Aiven for ClickHouse® we finally have the “C” for our KFC stack, the best combination of tools for real-time alerting and historic analytics. The stack utilises Apache Kafka®, Apache Flink®, and ClickHouse® to build up a robust, scalable architecture for getting the most from your data, whether it be batched ETL or real-time sensors.

For this challenge, we’ll be using real-time data from digitransit to plot maps and find new insights. Digitransit provides real-time bus information and positioning data for busses in Helsinki. We’ve saved you a few weeks worth of data as a CSV to experiment with.

Let’s dive right in.


The goal is to find the best bus routes between the different KFC restaurants in Helsinki.

  1. Create an Aiven free trial account here: sign up for free.

  2. Download the dataset here.

  3. Spin up an Aiven for ClickHouse® service and load the dataset. Please see the Aiven for ClickHouse® docs for help with these steps.

  4. Find the routes and plot them on a map. Here’s a notebook with a few optional queries and a mapping library to get you started. You can also check out the ClickHouse docs for advanced SQL information.

  5. Once ready:
    • Tag us on Twitter with #aivencatchthebuschallenge (and #bigdatalondon22 if you’re at the conference) with a screenshot of a map with these routes plotted on it.

    • Or email us at

  6. Lastly, collect your prize if you do the challenge before 21.11.2022:
    • If you do the challenge in connection with a conference, come visit us at our booth to collect your limited edition Aiven cap.

    • Otherwise you’ll receive a voucher for a nice piece of Aiven swag in your inbox upon submitting your solution.

For any help or questions ping us on twitter @aiven_io or @Bengamble7. Good luck!