Common Lisp ClickHouse Client Library
For users on version 0.48.0 and below, check the deprecated documentation below.
cl-user> (load "ch.lisp")
██ ██ ██ ██ λ
██ ██ ██ ██
██ ██ ██ ██
██ ██ ██ ██ ██
██ ██ ██ ██ ██
██ ██ ██ ██
██ ██ ██ ██
██ ██ ██ ██
ClickHouse Common Lisp Client loaded successfully!
Version: 0.49.0
Usage: (ch:make-database :host "localhost")
T
cl-user >$ sbcl --load ch.lisp$ make loadLoad the clickhouse-cl library:
(cl:load "ch.lisp")Run any example:
(load "examples/basic-connection.lisp")Modify the connection parameters in examples to match your setup:
(defparameter *host* "localhost")
(defparameter *port* 8123)
(defparameter *username* "default")
(defparameter *password* nil)Some examples use sample tables. Create them with:
-- Run this in your ClickHouse instance
CREATE DATABASE IF NOT EXISTS examples;
CREATE TABLE examples.users (
id UInt32,
name String,
email String,
age UInt8,
created_at DateTime
) ENGINE = MergeTree()
ORDER BY id;
CREATE TABLE examples.events (
timestamp DateTime,
user_id UInt32,
event_type String,
properties Map(String, String)
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id);Each example is self-contained and includes:
- Connection setup
- Sample data (where applicable)
- Demonstration code
- Expected output
- Cleanup code
Most examples can be run directly by loading them into your Lisp environment.
cl-user > (load "ch.lisp")
cl-user > (load "ch-test.lisp")
cl-user > (ch-tests:example-usage)
ClickHouse-CL Test Suite Usage:
===============================
(load "ch.lisp") ; Load the main library
(load "ch-tests.lisp") ; Load test suite
(ch-tests:run-all-tests) ; Run all tests
(ch-tests:run-unit-tests) ; Run unit tests only
(ch-tests:run-integration-tests) ; Run integration tests
(ch-tests:run-performance-tests) ; Run performance tests
(ch-tests:print-test-summary) ; Show detailed results
Test Configuration:
*test-host*: "localhost"
*test-port*: 8123
*test-username*: "default"
To use different test server:
(setf ch-tests:*test-host* "your-server")
(setf ch-tests:*test-port* 8443)
(setf ch-tests:*test-username* "testuser")
NIL
cl-user >$ make unit-tests
$ make integration-tests
$ make performance-tests
$ make all-testsThe documentation below is relevant to clickhouse-cl version 0.48.0 and below. clickhouse-cl version 0.49.0 and above no longer uses asdf:defsystem to load as a system.
Common Lisp ClickHouse Client Library
- Install
- No Line Breaks
databaseClass- Formats
- Input Parameters
- Examples
- Bugs, Features, and Vulnerabilities Reporting
clickhouse-cl is on Ultralisp.org!
> (ql-dist:install-dist "http://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 "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)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.
| 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 |
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
NILThe 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.
| 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 |
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
TThis 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.