# Serverless OLAP with Colab + chdb

In our previous episodes, we've introduced [chdb](https://chdb.dev), a 100% compatible clickhouse-powered engine for embedded OLAP workloads. [Like DuckDB but for ClickHouse](https://chdb.dev).

One of our personal goals was to create a *completely serverless ClickHouse* and become able to run ClickHouse queries ***without any server setups, cloud services and idle costs***; *Project* [*chdb*](https://chdb.io) *makes this - and so much more - finally possible.*

### ClickHouse & Colab Notebooks

Amongst its many features, [**chdb**](https://chdb.dev) allows executing fully-fledged ClickHouse SQL queries from **Google Colab Notebooks.** This was previously impossible due to the requirement for a ClickHouse server but thanks to **chdb**, it only takes a couple lines of **Python** to get your favourite OLAP engine roading inside your Notebooks.

There are many examples in the [chdb repository](https://chdb.dev) leveraging Notebooks, but I noticed they were all **stateless**, while our *embedded rocket on a bicycle* can support **persistent sessions** and with Colab support for **Google Drive** as storage, it provides us with a free\* and safe place for our data, with full access control and security.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1697138624807/bc8dfe76-c5ee-4b6c-a9b2-432c2ba313c4.png align="center")

### Making an OLAP ColabHouse

Surprisingly, all it takes is a few lines of code and no external services!

### <mark>👉 </mark> [<mark>TLDR; Here's a Colab Notebook to try right now!</mark>](https://colab.research.google.com/drive/1c0a5eQRdGqgccIsA1YCgh9-kR1e_wljS?usp=sharing)

### Step By Step

Let's start a new **Colab** project and install [**chdb**](https://chdb.dev) using pip and python:

```bash
!pip install chdb --upgrade --quiet
```

Next, let's mount our **Google Drive** as database storage for our **chdb** session:

```python
from google.colab import drive
drive.mount('/mount', force_remount=True)
```

Drive Aaccess is authorized, so let's start a [**chdb**](https://chdb.dev) session using it for persistence:

```python
from chdb import session as chs

## Create a Session using Google Drive Storage for persistence 
sess = chs.Session('/mount/MyDrive/chdb')
sess.query("CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic;")
sess.query("CREATE TABLE db_xxx.download (when DateTime, userid UInt32, bytes Float32) ENGINE=MergeTree PARTITION BY toYYYYMM(when) ORDER BY (userid, when);")
sess.query("INSERT INTO db_xxx.download SELECT now() + number * 60 as when, 25, rand() % 100000000 FROM system.numbers LIMIT 150;")
sess.query("INSERT INTO db_xxx.download SELECT now() + number * 60 as when, randUniform(1,24), rand() % 100000000 FROM system.numbers LIMIT 150;")
sess.query("CREATE MATERIALIZED VIEW db_xxx.download_daily_mv ENGINE = SummingMergeTree PARTITION BY toYYYYMM(day) ORDER BY (userid, day) POPULATE AS SELECT toStartOfDay(when) AS day, userid, count() as downloads, sum(bytes) AS bytes FROM db_xxx.download GROUP BY userid, day;")
```

It's that simple. Did it work? It did, of course! Let's go and read our data the cool way, using our new serverless **Materialized View** to get a summary of our dataset:

```python
print(sess.query("SELECT * FROM db_xxx.download_daily_mv FINAL ORDER BY bytes DESC LIMIT 5", "Pretty"))

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃                 day ┃ userid ┃ downloads ┃      bytes ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ 2023-10-12 00:00:00 │     25 │       150 │ 6934346798 │
├─────────────────────┼────────┼───────────┼────────────┤
│ 2023-10-12 00:00:00 │     13 │        10 │  583895472 │
├─────────────────────┼────────┼───────────┼────────────┤
│ 2023-10-12 00:00:00 │     20 │         9 │  501303012 │
├─────────────────────┼────────┼───────────┼────────────┤
│ 2023-10-12 00:00:00 │     21 │         7 │  500541088 │
├─────────────────────┼────────┼───────────┼────────────┤
│ 2023-10-12 00:00:00 │      8 │        12 │  493559005 │
└─────────────────────┴────────┴───────────┴────────────┘
```

> The above is just an example to get you started. Make it do what you want. SQL Queries, Engines and Functions are 100% compatible with ClickHouse™️.

### Sweet and Simple 🍯

Our Colab query runs transparently and provides persistence for our [chdb sessions](https://chdb.dev) through executions of the same notebook or from other Colab notebook projects.

Let's take a look at the data structure [**chdb/ClickHouse**](https://chdb.dev) has created in our drive:

```python
import os
os.listdir('/mount/MyDrive/chdb')

Mounted at /mount
['user_defined',
 'data',
 'metadata',
 'metadata_dropped',
 'tmp',
 'user_scripts',
 'store']
```

*There's our ClickHouse/chdb data, ready to be reattached for our next execution!*

> Note this approach is not necessarily intended for storing lots of data, but rather metadata referring to remote tables mounted on S3/R2 or URLEngine resources we might want to access and store aggregations from materialized views.

You can name your directories or hash values to produce session folders from names or use HTTP authentication tokens as we do in our [chdb-server API emulator](https://github.com/chdb-io/chdb-server).

TLDR; Here's a [ready notebook you can run with your Google account](https://colab.research.google.com/drive/1c0a5eQRdGqgccIsA1YCgh9-kR1e_wljS?usp=sharing).

[![](https://github.com/chdb-io/chdb/raw/main/docs/_static/snake-chdb.png align="left")](https://chdb.dev)

Thanks, chdb!*This is the cloud service we dreamed of - Go enjoy your free ClickHouse instance with persistent storage and total data access control!*