clickhouse-haskell: A Haskell library as database client for Clickhouse

[ database, library, mit ] [ Propose Tags ]
Versions [RSS] 0.1.1.0, 0.1.2.0, 0.1.2.1, 0.1.2.2, 0.1.2.3, 0.1.2.4
Change log ChangeLog.md
Dependencies aeson (>=1.4.1 && <1.6), array (>=0.5.1 && <0.6), async (>=2.2.2 && <2.3), attoparsec (>=0.13.2 && <0.14), base (>=4.12 && <5), binary (>=0.8.3 && <0.9), bytestring (>=0.10.8 && <0.11), bytestring-to-vector (>=0.3.0 && <0.4), call-stack (>=0.2.0 && <0.3), containers (>=0.5.7 && <0.6), data-default-class (>=0.1.2 && <0.2), data-dword (>=0.3.2 && <0.4), exceptions (>=0.10.4 && <0.11), filepath (>=1.4.1 && <1.5), hashable (>=1.2.7 && <1.4), hashmap (>=1.3.3 && <1.4), haxl (>=2.3.0 && <2.4), http-client (>=0.6.4 && <0.7), io-streams (>=1.5.2 && <1.6), monad-loops (>=0.4.3 && <0.5), monad-parallel (>=0.7.2 && <0.8), mtl (>=2.2.2 && <2.3), network (>=3.1.0 && <3.2), network-ip (>=0.3.0 && <0.4), network-simple (>=0.4.5 && <0.5), parsec (>=3.1.14 && <3.2), resource-pool (>=0.2.3 && <0.3), split (>=0.2.3 && <0.3), streaming-commons (>=0.2.2 && <0.3), text (>=1.2.3 && <1.3), time (>=1.9 && <1.12), transformers (>=0.5.2 && <0.6), tz (>=0.1.3 && <0.2), unix-time (>=0.4.7 && <0.5), unordered-containers (>=0.2.13 && <0.3), uri-encode (>=1.5.0 && <1.6), uuid (>=1.3.13 && <1.4), vector (>=0.12.1 && <0.13), word8 (>=0.1.3 && <0.2) [details]
License MIT
Copyright Copyright (c) 2020-present, EMQ X, Inc.
Author Shi You
Maintainer youshi@emqx.io
Category Database
Home page https://github.com/2049foundation/clickhouse-haskell/
Bug tracker https://github.com/2049foundation/clickhouse-haskell/issues
Source repo head: git clone https://github.com/2049foundation/clickhouse-haskell
Uploaded by MaboroshiChan at 2020-12-05T04:37:50Z
Distributions
Downloads 1282 total (15 in the last 30 days)
Rating 2.0 (votes: 1) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs available [build log]
Last success reported on 2020-12-05 [all 1 reports]

Readme for clickhouse-haskell-0.1.2.1

[back to package description]

Clickhouse-haskell

ClickHouse Haskell Driver with HTTP and native (TCP) interface support. Support both insert and ordinary query. This project has been heavily referenced from the python version. Link: https://github.com/mymarilyn/clickhouse-driver

Features

  • External Data for query processing
  • Types support
    • [U]Int8/16/32/64
    • String/FixedString(N)
    • Array(T)
    • Nullable(T)
    • Decimal
    • SimpleAggregateFunction(F, T)
    • Tuple(T1, T2, ...)
    • Date/DateTime('timezone')/DateTime64('timezone')
    • Enum8/16
    • Nested
    • LowCardinality(T)
    • UUID
  • Query progress information.
  • Block by block results streaming.
  • Reading query profile info.
  • Receiving server logs.
  • Applying Haxl(Concurrency library developed by Facebook) for caching query requests and concurrent querying processes.

Usage of the HTTP Client

In the HTTP client, data can be fetched from Clickhouse server in the format of pure text string, and structured JSON in which user can read the value according to a given key.

Example of data fetch using http client

import Database.ClickHouseDriver
import qualified Data.Text.IO as TIO

main :: IO()
main = do
    env <- httpClient "default" "" --username and password
    showtables <- runQuery env (getText "SHOW TABLES")
    TIO.putStr showtables

Result

    Fetching 1 queries.
    array0
    array1
    array_t
    array_test
    array_test2
    big
    cardin
    crd
    crd2
    dt
    int_test
    ip
    t
    tande
    tande2
    tande3
    test_table
    test_table2
    test_table3
    test_table4
    test_table5
    test_table6
    tuple
    tuple2

    puretext <- runQuery env (getText "SELECT * FROM test_table")
    TIO.putStr puretext

stdout:

    Fetching 1 queries.
    9987654321      Suzuki  12507   [667]
    9987654321      Suzuki  12507   [667]
    0000000001      JOHN    1557    [45,45,45]
    1234567890      CONNOR  533     [1,2,3,4]
    3543364534      MARRY   220     [0,1,2,3,121,2]
    2258864346      JAME    4452    [42,-10988,66,676,0]
    0987654321      Connan  9984    [24]
    0987654321      Connan  9984    [24]
    9987654321      Suzuki  12507   [667]
    json <- runQuery env (getJSON "SELECT * FROM test_table")
    print json

stdout:

    Right [fromList [("numArray",Array [Number 45.0,Number 45.0,Number 45.0]),("item",   String "JOHN"),("id",String "0000000001"),("number",Number 1557.0)],fromList [("numArray",Array [Number 1.0,Number 2.0,Number 3.0,Number 4.0]),("item",String "CONNOR"),("id",String "1234567890"),("number",Number 533.0)],fromList [("numArray",Array [Number 0.0,Number 1.0,Number 2.0,Number 3.0,Number 121.0,Number 2.0]),("item",String "MARRY"),("id",String "3543364534"),("number",Number 220.0)],fromList [("numArray",Array [Number 42.0,Number -10988.0,Number 66.0,Number 676.0,Number 0.0]),("item",String "JAME"),("id",String "2258864346"),("number",Number 4452.0)],fromList [("numArray",Array [Number 24.0]),("item",String "Connan"),("id",String "0987654321"),("number",Number 9984.0)],fromList [("numArray",Array [Number 24.0]),("item",String "Connan"),("id",String "0987654321"),("number",Number 9984.0)],fromList [("numArray",Array [Number 667.0]),("item",String "Suzuki"),("id",String "9987654321"),("number",Number 12507.0)],fromList [("numArray",Array [Number 667.0]),("item",String "Suzuki"),("id",String "9987654321"),("number",Number 12507.0)],fromList [("numArray",Array [Number 667.0]),("item",String "Suzuki"),("id",String "9987654321"),("number",Number 12507.0)]]

There is also a built-in Clickhouse type for user to send data in rows to Clickhouse server.

Algebraic data type for the Clickhouse types

data ClickhouseType
  = CKBool Bool
  | CKInt8 Int8
  | CKInt16 Int16
  | CKInt32 Int32
  | CKInt64 Int64
  | CKUInt8 Word8
  | CKUInt16 Word16
  | CKUInt32 Word32
  | CKUInt64 Word64
  | CKString ByteString
  | CKFixedLengthString Int ByteString
  | CKTuple (Vector ClickhouseType)
  | CKArray (Vector ClickhouseType)
  | CKDecimal32 Float
  | CKDecimal64 Float
  | CKDecimal128 Float
  | CKIPv4 IP4
  | CKIPv6 IP6
  | CKDate {
    year :: !Integer,
    month :: !Int,
    day :: !Int 
  }
  | CKNull
  deriving (Show, Eq)

Example of sending data from memory

main = do
  env <- httpClient "default" "12345612341"
  create <- exec "CREATE TABLE test (x Int32) ENGINE = Memory" env
  print create
  isSuccess <- insertOneRow "test" [CKInt32 100] env
  print isSuccess
  result <- runQuery env (getText "select * from test")
  TIO.putStr result

stdout:

Right "Inserted successfully"
Right "Inserted successfully"
Fetching 1 queries.
100

Example of sending data from CSV

main :: IO()
main = do
    env <- httpClient "default" "12345612341"
    isSuccess <- insertFromFile "test_table" CSV "./test/example.csv" env
    putStr (case isSuccess of
        Right y -> y
        Left x -> CL8.unpack x)
    query <- runQuery env (getText "SELECT * FROM test_table")
    TIO.putStr query

where in example.csv

0000000011,Bob,123,'[1,2,3]'
0000000012,Bob,124,'[4,5,6]'
0000000013,Bob,125,'[7,8,9,10]'
0000000014,Bob,126,'[11,12,13]'

stdout:

0000000010      Alice   123     [1,2,3]
0000000010      Alice   123     [1,2,3]
0000000010      Alice   123     [1,2,3]
0000000010      Alice   123     [1,2,3]
0000000011      Bob     123     [1,2,3]
0000000012      Bob     124     [4,5,6]
0000000013      Bob     125     [7,8,9,10]
0000000014      Bob     126     [11,12,13]

Usage of the Native(TCP) interface

Ping

    conn <- defaultClient
    Database.ClickHouseDriver.ping conn

stdout:

Just "PONG!"

Example of making query with the native interface

main :: IO ()
main = do
    env <- defaultClient --localhost 9000
    res <- query env "SHOW TABLES" 
    print res

stdout:

[[CKString "test"],[CKString "test_table"],[CKString "test_table2"]]

Example of making insert query with the native interface

conn <- defaultClient
insertMany conn "INSERT INTO crd VALUES"
          [
            [CKString "123", CKString "hi"],
            [CKString "456", CKString "lo"]
          ]

In the terminal interface of clickhouse it will show:

id──┬─card─┐
│ 123 │ hi   │
│ 456 │ lo   │

Use of Haxl for concurrency

We can perform multiple fetches concurrently like this:

queryTests :: GenHaxl u w (V.Vector (V.Vector ClickhouseType))
queryTests = do
    one <- fetch "SELECT * FROM UUID_test"
    two <- fetch "SELECT * FROM array_t"
    three <- fetch "SHOW DATABASES"
    four <- fetch "SHOW TABLES"
    return $ V.concat [one, two ,three, four]

Fetching 4 queries.
[[CKString "417ddc5d-e556-4d27-95dd-a34d84e46a50"],...

Stream profile and process infomation

The native interface supports reading infomations coming from server. Originally they come with the queried data wrapped in the algebraic data types:

data CKResult = CKResult
 { query_result ::  Vector (Vector ClickhouseType),
   query_info :: {-# UNPACK #-} ! QueryInfo
 }

data QueryInfo = QueryInfo 
 { profile_info :: {-# UNPACK #-} !BlockStreamProfileInfo,
   progress :: {-# UNPACK #-} !Progress,
   elapsed :: {-# UNPACK #-} !Word
 }

 data BlockStreamProfileInfo = ProfileInfo
  { number_rows :: {-# UNPACK #-} !Word,
    blocks :: {-# UNPACK #-} !Word,
    number_bytes :: {-# UNPACK #-} !Word,
    applied_limit :: {-# UNPACK #-} !Bool,
    rows_before_limit :: {-# UNPACK #-} !Word,
    calculated_rows_before_limit :: {-# UNPACK #-} !Bool
  }

data Progress = Prog
  { rows :: {-# UNPACK #-} !Word,
    bytes :: {-# UNPACK #-} !Word,
    total_rows :: {-# UNPACK #-} !Word,
    written_rows :: {-# UNPACK #-} !Word,
    written_bytes :: {-# UNPACK #-} !Word
  }

One can use executeWithInfo to get results that come with those information. For example:

main = do
    conn <- defaultClient
    res <- executeWithInfo "show databases" conn
    print $ query_result res
    print $ query_info res

The code above prints:

[[CKString "_temporary_and_external_tables"],[CKString "default"],[CKString "system"]]

QueryInfo {profile_info = ProfileInfo {number_rows = 3, blocks = 1, number_bytes = 4224, applied_limit = True, rows_before_limit = 0, calculated_rows_before_limit = True}, progress = Prog {rows = 3, bytes = 331, total_rows = 0, written_rows = 0, written_bytes = 0}, elapsed = 0}