ClickHouse POC

ClickHouse installation

For ClickHouse installation, see https://hub.docker.com/r/clickhouse/clickhouse-server on docker hub.

To start the server

docker-compose -f docker/docker-compose.yml up -d

To export the default configuration file

With a running server container, to get the default config.xml, do :

docker cp clickhouse-server:/etc/clickhouse-server/config.xml ./config.xml

ClickHouse documentation

Here you'll find the Documentation

ClickHouse client

To connect to the server with the clickhouse-client command line

# run the client in the server container
docker exec -it clickhouse-server clickhouse-client
# run the client in a separate container
docker run -it --rm --link clickhouse-server:clickhouse-server clickhouse/clickhouse-client --host clickhouse-server --user plugis --password telemac

To check the local installation, enter

select 1+1

if you see this, it works.

SELECT 1 + 1

┌─plus(1, 1)─┐
│          2 │
└────────────┘

Connect with MySQL client

see MySQL Interface

mysql --protocol tcp -h 127.0.0.1 -u plugis -p -P 9004

Let's try the OnTime sample dataset

Here is the OnTime dataset sample

The sample_ontime directory contains the files to run this test.

The following will :

• download sample files
• create the table
• load

cd sample_ontime
sh download.sh
cat create_table.sql | docker run -i --rm --link clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
sh load.sh

Now, ne connect to the server with the cli :

docker run -it --rm --link clickhouse-server:clickhouse-server clickhouse/clickhouse-client --host clickhouse-server --user plugis --password telemac

and we issue some requests :

show tables

select count(*) from ontime

select avg(c1) from (select Year, Month, count(*) as c1 from ontime group by Year, Month);

Experiment with other requests of this page

Test results

Import duration

real	31m1.694s
user	15m12.788s
sys	1m44.384s

177920306 / 1860 = 95 656 rows/s imported

First select count(*) from ontime

34aa739d94e1 :) select count(*) from ontime

SELECT count(*)
FROM ontime 

┌───count()─┐
│ 177920306 │
└───────────┘

1 rows in set. Elapsed: 0.802 sec. Processed 177.92 million rows, 177.92 MB (221.72 million rows/s., 221.72 MB/s.) 

Data ingestion

We're creating a log table with the following fields :

name	required	type
ts	true	Timestamp
id	true	String
device	true	Integer
grp	repeated	Integer
type	true	String
gateway	false	String
size	false	Integer
signal	false	Float
noise	false	Float
snr	false	Float
sf	false	Integer
sensitivity	false	Integer
client	false	String
device_profile	false	String
port	false	Integer
insert_at	false	Timestamp
deveui	false	String
rssi	false	Float
frequency	false	Float

CREATE TABLE ontime
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    UniqueCarrier FixedString(7),
    AirlineID Int32,
    Carrier FixedString(2),
    TailNum String,
    FlightNum String,
    OriginAirportID Int32,
    OriginAirportSeqID Int32,
    OriginCityMarketID Int32,
    Origin FixedString(5),
    OriginCityName String,
    OriginState FixedString(2),
    OriginStateFips String,
    OriginStateName String,
    OriginWac Int32,
    DestAirportID Int32,
    DestAirportSeqID Int32,
    DestCityMarketID Int32,
    Dest FixedString(5),
    DestCityName String,
    DestState FixedString(2),
    DestStateFips String,
    DestStateName String,
    DestWac Int32,
    CRSDepTime Int32,
    DepTime Int32,
    DepDelay Int32,
    DepDelayMinutes Int32,
    DepDel15 Int32,
    DepartureDelayGroups String,
    DepTimeBlk String,
    TaxiOut Int32,
    WheelsOff Int32,
    WheelsOn Int32,
    TaxiIn Int32,
    CRSArrTime Int32,
    ArrTime Int32,
    ArrDelay Int32,
    ArrDelayMinutes Int32,
    ArrDel15 Int32,
    ArrivalDelayGroups Int32,
    ArrTimeBlk String,
    Cancelled UInt8,
    CancellationCode FixedString(1),
    Diverted UInt8,
    CRSElapsedTime Int32,
    ActualElapsedTime Int32,
    AirTime Int32,
    Flights Int32,
    Distance Int32,
    DistanceGroup UInt8,
    CarrierDelay Int32,
    WeatherDelay Int32,
    NASDelay Int32,
    SecurityDelay Int32,
    LateAircraftDelay Int32,
    FirstDepTime String,
    TotalAddGTime String,
    LongestAddGTime String,
    DivAirportLandings String,
    DivReachedDest String,
    DivActualElapsedTime String,
    DivArrDelay String,
    DivDistance String,
    Div1Airport String,
    Div1AirportID Int32,
    Div1AirportSeqID Int32,
    Div1WheelsOn String,
    Div1TotalGTime String,
    Div1LongestGTime String,
    Div1WheelsOff String,
    Div1TailNum String,
    Div2Airport String,
    Div2AirportID Int32,
    Div2AirportSeqID Int32,
    Div2WheelsOn String,
    Div2TotalGTime String,
    Div2LongestGTime String,
    Div2WheelsOff String,
    Div2TailNum String,
    Div3Airport String,
    Div3AirportID Int32,
    Div3AirportSeqID Int32,
    Div3WheelsOn String,
    Div3TotalGTime String,
    Div3LongestGTime String,
    Div3WheelsOff String,
    Div3TailNum String,
    Div4Airport String,
    Div4AirportID Int32,
    Div4AirportSeqID Int32,
    Div4WheelsOn String,
    Div4TotalGTime String,
    Div4LongestGTime String,
    Div4WheelsOff String,
    Div4TailNum String,
    Div5Airport String,
    Div5AirportID Int32,
    Div5AirportSeqID Int32,
    Div5WheelsOn String,
    Div5TotalGTime String,
    Div5LongestGTime String,
    Div5WheelsOff String,
    Div5TailNum String
)
ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);

Test the most popular go clickhouse client library

kshvakov/clickhouse

NATS

not working

  CREATE TABLE log
  (
    timestamp DateTime,
    message JSON
  )
  ENGINE = NATS
    SETTINGS nats_url = 'wss://nats.megalarm.fr:443',
             nats_secure = 1,
             nats_username = 'megalarm',
             nats_password = 'megalarm',
             nats_subjects = 'log.>',
             nats_format = 'JSONEachRow';
  INSERT INTO log
  SETTINGS stream_like_engine_insert_queue = 'LOGS'
  VALUES (1, 1);