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/gcloud-ssh-key.pub

output

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

Command

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 keyserver.ubuntu.com --recv E0C56BD4


 echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ 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;

Output

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

Ok.

0 rows in set. Elapsed: 0.005 sec.

Using the database

use test

Creating a new table

CREATE TABLE test1
(
    `id` UInt64,
    `name` String
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id

Output


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

Ok.

0 rows in set. Elapsed: 0.010 ```

Displaying all tables


show tables

Output

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

┌─name──┐
│ 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.

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