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.

Instructions#

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 hacks@Aiven.io.

  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!