CodingCloudGoogle Cloud
Setting Clickhouse column data warehouse at Google Cloud Compute Engine VM
I am trying to set up Clickhouse column data warehouse at Google Cloud using their free compute engine VM
2 Min18 Nov, 2021

I didn't have Google Cloud account associated with my email, so I signed up for one. It needs a valid Credit Card and mobile number to check if you are human.

On successful sign up I get 300$ to spend within 3 months.

Creating a free forever Google Cloud Compute Engine VM

As per Google Cloud documentation you can have a 1 non-preemptible e2-micro VM instance (1GB 2vCPU, 30GB Disk etc.) per month free forever in some region with some restrictions.

I wanted following stuffs in my VM before I can install Clickhouse on to that:

  1. Ubuntu 20.x LTS
  2. SSH access from my machine

Enabling SSH based access to Google Compute Engine VM

Step 1

Created a ssh private and public key on my mac using the following command

ssh-keygen -t rsa -f ~/.ssh/gcloud-ssh-key -C mrityunjay -b 2048

Step 2

Copied the public key from the console using the following command:

cat ~/.ssh/


ssh-rsa <Gibrish :)> mrityunjay

Step 3

I went to Google Cloud Console > Compute Engine > Metadata > SSH Keys Section.

I clicked on Edit > Add Item and pasted the previously copied public SSH key and saved the item.

Newly added SSH keys started appearing in the list.

Creating a new VM

Creating a new VM is a breeze with easy-to-use options on the screen in the Console.

I made sure the following stuff:

  1. Selected region as us-west-1.
  2. Changed Disk to 30 GB general purpose.
  3. Choose Ubuntu 20 LTS.
  4. Paste Google Cloud SSH Public Key.

VM successfully created!

Accessing my VM


ssh -i ~/.ssh/gcloud-ssh-key [email protected]

And I am within the Ubuntu 20.x Machine.

Setting up the ClickHouse

I found a great article on DigitalOcean documentation for setting up ClickHouse on Ubuntu 20.x LTS.

Most of my setup follows instructions from the article. God bless the author.

Adding Yandex managed ClickHouse APT repository

sudo apt-key adv --keyserver --recv E0C56BD4

 echo "deb main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt update

Installing clickhouse-server and clickhouse-client packages

sudo apt install clickhouse-server clickhouse-client

I provided a secure password for default user of ClickHouse.

Setup done!

ClickHouse Administration

Starting ClickHouse on Ubuntu 20.X LTS

sudo service clickhouse-server start

Checking status for the ClickHouse

sudo service clickhouse-server status

Connecting to ClickHouse

clickhouse-client --password

On success I was seeing a shell which inviting me to try some commands.

ClickHouse is in heart like a RDBMS with some twist.

Executing few commands

Creating a new database

create database test;


Query id: 9dc02359-02da-480e-b538-96d63d1155ad


0 rows in set. Elapsed: 0.005 sec.

Using the database

use test

Creating a new table

    `id` UInt64,
    `name` String
ENGINE = MergeTree


Query id: dd2cd818-780b-4c63-9c8f-695a83121719


0 rows in set. Elapsed: 0.010 ```

Displaying all tables

show tables


Query id: 2adb7b7e-3f53-40cf-8b2d-263c27a487a0

│ test1 │

1 rows in set. Elapsed: 0.013 sec. 

Okay so for this tree in my garden I can stop now with trying out commands of ClickHouse.

Accessing ClickHouse from other Machine

To access ClickHouse from another machine I made following changes in ClickHouse config to allow listening from all interfaces.

sudo nano /etc/clickhouse-server/config.xml

And uncomment <!-- <listen_host>::</listen_host> -->.

Restart server

sudo service clickhouse-server restart

There are two ports on which ClickHouse listen 8123 (HTTP) and 9000 (Internal)

I enabled them in Google Cloud Compute Engine Firewall options.

And I am good to go with accessing ClickHouse DB from other system.

Allowing MySQL Connector


ClickHouse supports MySQL wire protocol. It can be enabled by mysql_port setting in configuration file:


 <!-- Compatibility with MySQL protocol.

 ClickHouse will pretend to be MySQL for applications connecting to this port.


Uncomment or add the above line in /etc/clickhouse-server/config.xml file. Restart server after saving.

Allowing PostgreSQL Connector

 <!-- Compatibility with PostgreSQL protocol.

 ClickHouse will pretend to be PostgreSQL for applications connecting to this port.



Uncomment or add the above line in /etc/clickhouse-server/config.xml file. Restart server after saving.

© 2021, All Rights Reserved
Made In India 🇮🇳 with ❤️
Quick Links