GitHub - juliojimenez/clickhouse-cl: Common Lisp ClickHouse Client Library

cl-user> (load "ch.lisp")
 
    ██  ██  ██  ██  λ
    ██  ██  ██  ██
    ██  ██  ██  ██
    ██  ██  ██  ██  ██
    ██  ██  ██  ██  ██
    ██  ██  ██  ██
    ██  ██  ██  ██
    ██  ██  ██  ██
 
ClickHouse Common Lisp Client loaded successfully!
Version: 0.49.0
Usage: (ch:make-database :host "localhost")
 
T
cl-user >

(load "examples/basic-connection.lisp")

(defparameter *host* "localhost")
(defparameter *port* 8123)
(defparameter *username* "default")
(defparameter *password* nil)

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

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

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

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

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

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

(:CLICKHOUSE)

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

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

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

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

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

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

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

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

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

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

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

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

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

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