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

clickhouse-cl is on Ultralisp.org!

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

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 "https://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)

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

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.

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"

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."

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

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

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

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

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

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

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

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)

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.

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

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)

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))

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

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

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