Using Clickhouse data source on Grafana
Table of contents
Step 1 - Install clickhouse-server
sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4
echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \
/etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
sudo service clickhouse-server status
clickhouse-server --config-file=/etc/clickhouse-server/config.xml
sudo service clickhouse-server restart
Step 2 - Set permission for clickhosue
Run sudo vim /etc/grafana/grafana.ini
, locate below info.
[plugins]
allow_loading_unsigned_plugins=vertamedia-clickhouse-datasource
Step 3 - Install plugin and restart Grafana server
sudo grafana-cli plugins install vertamedia-clickhouse-datasource
# outputs: ✔ Downloaded vertamedia-clickhouse-datasource v2.4.2 zip successfully
Then run sudo systemctl restart grafana-server
to restart daemon.
Step 4 - Configure Database using UI
-
Add data source for sample data Name: clickhouse-ds URL: http://localhost:8123
- curl http://localhost:8123/
> ok # if ok, it's good to go now.
- run
clickhouse-client
ClickHouse client version 22.2.2.1. Connecting to localhost:9000 as user default. Connected to ClickHouse server version 22.2.2 revision 54455. droplet :) select 1
- run
exit
to endquery
inclickhouse-client
Step 5 - Add exampled dataset
- Follow steps here: UK Property Price Paid
Note:
When creating a new table, I had to run below steps before the table can be created.
- Run
clickhouse-client --query "CREATE DATABASE IF NOT EXISTS [table-name]"
- Start
clickhouse-client
- Then, create table with column type
CREATE TABLE uk_price_paid ( price UInt32, date Date, postcode1 LowCardinality(String), postcode2 LowCardinality(String), type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0), is_new UInt8, duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0), addr1 String, addr2 String, street LowCardinality(String), locality LowCardinality(String), town LowCardinality(String), district LowCardinality(String), county LowCardinality(String), category UInt8 ) ENGINE = MergeTree ORDER BY (postcode1, postcode2, addr1, addr2);
- Check database and tables
SHOW DATABASES DESCRIBE TABLE [table-name]
- Exit
clickhouse-client
- After switch to normal terminal, run the full command below in terminal. Takes minutes!
clickhouse-local --input-format CSV --structure ' uuid String, price UInt32, time DateTime, postcode String, a String, b String, c String, addr1 String, addr2 String, street String, locality String, town String, district String, county String, d String, e String ' --query " WITH splitByChar(' ', postcode) AS p SELECT price, toDate(time) AS date, p[1] AS postcode1, p[2] AS postcode2, transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type, b = 'Y' AS is_new, transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration, addr1, addr2, street, locality, town, district, county, d = 'B' AS category FROM table" --date_time_input_format best_effort < pp-complete.csv | clickhouse-client --query "INSERT INTO uk_price_paid FORMAT TSV"