Working with ClickHouse in Go. Part 1: Basics

Wed, Jul 6, 2016 2-minute read

ClickHouse

ClickHouse is an open-source column-oriented database management system that allows generating analytical data reports in real time. Created by Yandex developers for internal purposes, but then has migrated as open-source tool. It currently powers Yandex.Metrica, world’s second largest web analytics platform, with over 13 trillion database records and over 20 billion events a day, generating customized reports on-the-fly, directly from non-aggregated data. So it is really fast.

The reason I do really love ClickHouse is it supports SQL syntax. Now I’ll show how to start to work with it and get some basic profit.

Install ClickHouse on Ubuntu

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4    # optional

sudo mkdir -p /etc/apt/sources.list.d
echo "deb http://repo.yandex.ru/clickhouse/trusty stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install clickhouse-server-common clickhouse-client

sudo service clickhouse-server start
clickhouse-client

clickhouse-client package contains clickhouse-client application — interactive ClickHouse client. Let’s create database and a table using this tool.

clickhouse-client
ClickHouse client version 1.1.53983.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.53983.

:) create database golang_test;

CREATE DATABASE golang_test

Ok.

0 rows in set. Elapsed: 0.003 sec.

:) CREATE TABLE logs
(
    date Date,
    size Int32,
    message String
) ENGINE = MergeTree(date, message, 8192)

Ok.

0 rows in set. Elapsed: 0.004 sec.

Now we have created MergeTree table, there are few more engines supported by ClickHouse.

Connect from Go

There is only one driver written in Go for this RDBMS - go-clickhouse. It works only with http transport (ClickHouse is accessible by tcp too). Default http address is http://localhost:8123.

go get github.com/roistat/go-clickhouse

Let’s establish connection:

transport := clickhouse.NewHttpTransport()
conn := clickhouse.NewConn("localhost:8123", transport)
err := conn.Ping()
if err != nil {
    panic(err)
}

If everything is fine here we can insert some data to this table and then fetch it.

q := clickhouse.NewQuery("INSERT INTO golang_test.logs VALUES (toDate(now()), ?, ?)", 1, "Log message")
q.Exec(conn)

q := clickhouse.NewQuery("SELECT `message` FROM golang_test.logs")
i := q.Iter(conn)
for {
    var message string
    scanned := i.Scan(&message)
    if scanned {
        fmt.Println(message)
    } else if err != nil {
        panic(i.Error())
    }
}

ClickHouse is a really nice thing, highly reliable, simple and handy, feature-rich.