clickhouse-cl

Common Lisp ClickHouse Client Library

• Install
  • Ultralisp.org
  • git clone
  • Releases
• No Line Breaks
• database Class
  • Slots
  • Usage
  • Methods
    • ping
    • replicas-status
    • query
    • infile
  • Console Option
  • Timeouts
• Formats
  • Functions
    • jget
• Input Parameters
• Examples
  • Connection to a local database
  • Query
  • Connecting to ClickHouse Cloud
• Bugs, Features, and Vulnerabilities Reporting

Install

Ultralisp.org

clickhouse-cl is on Ultralisp.org!

> (ql-dist:install-dist "http://dist.ultralisp.org/" :prompt nil)
...
> (ql:quickload :clickhouse)
...

git clone

Clone this repo wherever your quicklisp local-projects folder is configured.

~/quicklisp/local-projects/$ git clone https://github.com/juliojimenez/clickhouse-cl
~/quicklisp/local-projects/$ cd clickhouse-cl
~/quicklisp/local-projects/clickhouse-cl/$

Some dependencies are on Ultralisp.org, make sure you have it...

> (ql-dist:install-dist "http://dist.ultralisp.org/" :prompt nil)
...

In the emacs SLIME REPL or SBCL, load clickhouse-cl with...

> (ql:quickload :clickhouse)
To load "clickhouse":
  Load 1 ASDF system:
    clickhouse
; Loading "clickhouse"
[package clickhouse]

(:CLICKHOUSE)

Releases

You can also download a release, extract it into your local-projects, and follow the same steps above (minus the git clone, of course).

No Line Breaks (Emacs)

To prevent line breaks, which makes query outputs with many fields difficult to read, issue the command M-x toggle-truncate-lines RET in the Emacs minibuffer.

If that doesn't work, checkout this StackExchange post for other options.

database Class

Slots

Name	Accessor	Default	Description
host	y	localhost	Database hostname
port	y	8123	Database port, i.e. 8443 or 8123
ssl	y	nil	SSL option, boolean, t or nil.
username	y	default	Database username
password	y	nil	Database password

Usage

Creating a instance of database.

(make-instance 'clickhouse:database :host "clickhouse.example.com" :port "8123" :username "example" :password "1amAsecretPassWord")

The clickhouse-cl package nickname is ch and will be used throughout this README for brevity.

Binding an instance of database.

(defparameter *db* (make-instance 'ch:database :host "localhost" :port "8123" :ssl nil :username "default" :password "1amAsecretPassWord"))

Reading and setting a slot.

> (ch::password *db*)
"1amAsecretPassWord"
> (setf (ch::password *db*) "chang3m3plea5e")
"chang3m3plea5e"

Methods

ping

ch:ping obj :ping bool :console bool

> (ch:ping *db*)
"Ok."

The :ping t keyword parameter explicitly calls the instance /ping endpoint.

> (ch:ping *db* :ping t)
"Ok."

replicas-status

ch:replicas-status obj :console bool :verbose bool

> (ch:replicas-status *db*)
"Ok."

query

ch:query obj query :console bool :no-format bool :timeout int

> (ch:query *db* "SELECT 1")
"1"

infile

ch:infile obj file table format :no-format bool :timeout int

> (ch:infile *db* "/Users/path/example.parquet" "sometable" "Parquet")

Console Option

All methods can take the keyword parameter :console t, providing a cleaner output when interacting directly with the library in the REPL.

> (ch:query *db* "SHOW DATABASES")
"INFORMATION_SCHEMA
default
information_schema
system"

> (ch:query *db* "SHOW DATABASES" :console t)
INFORMATION_SCHEMA
default
information_schema
letsgetitstarted
system
NIL

Timeouts

The default query method timeout is 60 seconds. Use the :timeout seconds keyword parameter to change the default for long running operations.

(ch:query *db* "INSERT INTO crypto_prices 
                    SELECT 
                        trade_date,
                        crypto_name,
                        volume,
                        price,
                        market_cap,
                        change_1_day
                    FROM s3('https://learn-clickhouse.s3.us-east-2.amazonaws.com/crypto_prices.csv',
                            'CSVWithNames'
                     )
                    SETTINGS input_format_try_infer_integers=0" :timeout 300)

Formats

ClickHouse can accept and return data in various formats. A format supported for input can be used to parse the data provided to INSERTs, to perform SELECTs from a file-backed table such as File, URL or HDFS, or to read a dictionary. A format supported for output can be used to arrange the results of a SELECT, and to perform INSERTs into a file-backed table. (Formats)

clickhouse-cl supports automatic input and output format processing for the formats below. If such processing is not desired, the keyword parameter :no-format t is added to the query method.

Format	Input	Output	Result
TabSeparated	✔️	✔️	'('(string*)*)
TabSeparatedRaw	✔️	✔️	'('(string*)*)
TabSeparatedWithNames	✔️	✔️	'('(string*)*)
TabSeparatedWithNamesAndTypes	✔️	✔️	'('(string*)*)
TabSeparatedRawWithNames	✔️	✔️	'('(string*)*)
TabSeparatedRawWithNamesAndTypes	✔️	✔️	'('(string*)*)
Template	✔️	✔️	string
CSV	✔️	✔️	'('(string*)*)
CSVWithNames	✔️	✔️	'('(string*)*)
CSVWithNamesAndTypes	✔️	✔️	'('(string*)*)
SQLInsert		✔️	string
Values	✔️	✔️	'('(string*)*)
Vertical		✔️	string
JSON	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONAsString	✔️		string
JSONStrings	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONColumns	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONColumnsWithMetadata	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompact	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactStrings		✔️	BOOST-JSON:JSON-OBJECT
JSONCompactColumns	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONEachRow	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONEachRowWithProgress		✔️	BOOST-JSON:JSON-OBJECT
JSONStringsEachRow	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONStringsEachRowWithProgress		✔️	BOOST-JSON:JSON-OBJECT
JSONCompactEachRow	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactEachRowWithNames	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactEachRowWithNamesAndTypes	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactStringsEachRow	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactStringsEachRowWithNames	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONCompactStringsEachRowWithNamesAndTypes	✔️	✔️	BOOST-JSON:JSON-OBJECT
JSONObjectEachRow	✔️	✔️	BOOST-JSON:JSON-OBJECT
TSKV	✔️	✔️	'('('(k . v)))
Pretty		✔️	string
PrettyNoEscapes		✔️	string
PrettyMonoBlock		✔️	string
PrettyNoEscapesMonoBlock		✔️	string
PrettyCompact		✔️	string
PrettyCompactNoEscapes		✔️	string
PrettyCompactMonoBlock		✔️	string
PrettyCompactNoEscapesMonoBlock		✔️	string
PrettySpace		✔️	string
PrettySpaceNoEscapes		✔️	string
PrettySpaceMonoBlock		✔️	string
PrettySpaceNoEscapesMonoBlock		✔️	string
Parquet	✔️
ParquetMetadata	✔️
Null		✔️
XML		✔️	string
LineAsString	✔️	✔️	string
Markdown		✔️	string

Functions

jget

Helper function used to access key values in formats that result in a BOOST-JSON:JSON-OBJECT.

ch:jget obj key

> (defparameter *db* (make-instance 'ch:database))
*DB*
> (defparameter *result* (ch:query *db* "SELECT trip_id, passenger_count FROM trips LIMIT 10 FORMAT JSON"))
*RESULT*
> *result*
#<BOOST-JSON:JSON-OBJECT {"meta":#,"data":#,"rows":10,"rows_before_limit_at_least":10,"statistics":#}>
> (ch:jget *result* "rows")
10
T

Input Parameters

This feature is an oversimplification of input parameters as seen in clickhouse-client.

To interpolate inputs into a query, use the function input-parameters with the input marker $i.

ch:input-parameters query &rest input

 (ch:query *db* (ch:input-parameters "SELECT $i" "1") :console t)

Examples

Connecting to a local database

This would be applicable to a recently installed database, prior to applying a password and/or adding any users.

(defparameter *db* (make-instance 'ch:database))

Query

(ch:query *db* "SELECT 1")

Connecting to ClickHouse Cloud

This example connects to a ClickHouse Cloud database loaded with the NYC Taxi dataset.

> (ql:quickload :clickhouse)
> (defparameter *db* (make-instance 'clickhouse:database
				    :host "iqr3flp7yf.us-east-1.aws.clickhouse.cloud"
				    :port 8443
				    :ssl t
				    :username "default"
				    :password ")UwB2oL|QQpi"))
> (ch:query *db* "SELECT count()
                  FROM nyc_taxi 
                  FORMAT PrettySpaceNoEscapes" :console t)

  count()

 20000000
NIL
> (ch:query *db* "SELECT 
                    trip_id,
                    total_amount,
                    trip_distance
                  FROM nyc_taxi
                  LIMIT 5 
                  FORMAT PrettySpaceNoEscapes" :console t)

    trip_id   total_amount   trip_distance

 1199999902          19.56            2.59 
 1199999919           10.3             2.4 
 1199999944           24.3            5.13 
 1199999969           9.95             1.2 
 1199999990            9.8            2.17 
NIL

Bugs, Features, and Vulnerabilities Reporting

To report bugs, request a feature, or report a security vulnerability, please submit a new issue.