Link Search Menu Expand Document

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 end query in clickhouse-client

Step 5 - Add exampled dataset

Note:

When creating a new table, I had to run below steps before the table can be created.

  1. Run clickhouse-client --query "CREATE DATABASE IF NOT EXISTS [table-name]"
  2. Start clickhouse-client
  3. 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);
    
  4. Check database and tables
     SHOW DATABASES
     DESCRIBE TABLE [table-name]
    
  5. Exit clickhouse-client
  6. 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"
    

Get started with Clickhouse Playground on Nginx

Clickhouse play on Nginx