Skip to content

ak-coram/cl-duckdb

Repository files navigation

cl-duckdb

Build Status

Common Lisp CFFI wrapper around the DuckDB C API

Dependencies

Currently the following Common Lisp implementations and operating systems are tested via CI. Android via Termux (ECL & SBCL) and some other BSDs are also known to work.

  • SBCL (Linux, FreeBSD, Windows, macOS, macOS on AArch64)
  • CCL (Linux, macOS)
  • ECL (Linux, FreeBSD, macOS, macOS on AArch64), see the ECL.org document for specifics

The following native libraries need to be installed in a location where CFFI can find them:

For example on Ubuntu or Debian (amd64):

sudo apt-get install libffi-dev unzip
# Download libduckdb-linux-amd64.zip from the C/C++ section of https://duckdb.org/docs/installation/
sudo unzip ~/Downloads/libduckdb-linux-amd64.zip libduckdb.so -d /usr/lib/

Installation

cl-duckdb can be installed via Quicklisp:

(ql:quickload :duckdb)

The latest version is available from the Ultralisp distribution:

;; Install the ultralisp distribution if you don't have it already
(ql-dist:install-dist "http://dist.ultralisp.org/" :prompt nil)
;; Load cl-duckdb
(ql:quickload :duckdb)

Alternatively you can also rely on ocicl.

Packages

  • DUCKDB (nicknamed DDB): provides the high-level API.
  • DUCKDB-API: contains the low-level bindings to the DuckDB C API.

Usage

Connecting to a database

This library relies on the special variable DUCKDB:*CONNECTION* for a default database connection. Setting up a global default connection is recommmended for interactive REPL sessions:

;; Use an in-memory database as the default connection
(ddb:initialize-default-connection)
;; Use a persistent database as the default connection
(ddb:initialize-default-connection :path "my_database.ddb")
;; Clean up the default connection at the end of the session
(duckdb:disconnect-default-connection)

For manual connection management most functions requiring a database connection also accept a connection object as a keyword argument (see DUCKDB:OPEN-DATABASE and DUCKDB:CONNECT for creating one).

To dynamically bind and automatically clean up a default connection, refer to DUCKDB:WITH-DEFAULT-CONNECTION and DUCKDB:WITH-TRANSIENT-CONNECTION instead.

Basic example

;; Use an in-memory transient database
(ddb:with-transient-connection
  ;; Create a new range table containing integers
  (ddb:run "CREATE TABLE range (i INTEGER PRIMARY KEY)"
           "CREATE SEQUENCE seq_range_i START 1")

  ;; Use a prepared statement to populate the table with a 1000 values
  (ddb:with-statement (statement "INSERT INTO range VALUES (nextval('seq_range_i'))")
    (dotimes (_ 1000) (ddb:perform statement)))

  ;; Solve Project Euler Problem 9
  (let* ((euler9-query (ddb:concat "SELECT a.i * b.i * c.i AS solution "
                                   "FROM range AS c "
                                   "JOIN range AS b ON b.i < c.i "
                                   "JOIN range AS a ON a.i < b.i "
                                   "WHERE a.i + b.i + c.i = ? "
                                   "AND a.i * a.i + b.i * b.i = c.i * c.i"))
         (parameters '(1000))
         (results (ddb:query euler9-query parameters)))
    (format t "PE9 Solution: ~a~%" (ddb:get-result results 'solution 0))))

Interactive example: query remote Parquet data

The DUCKDB:Q (short for QUERY) and DUCKDB:FQ (short for FORMAT-QUERY) functions are provided as shorthands for interactive REPL use:

(ddb:initialize-default-connection)
(ddb:q "INSTALL httpfs") ; => (("Success" . #()))
(let ((url "https://github.com/apache/parquet-mr/raw/master/parquet-hadoop/src/test/resources/test-file-with-no-column-indexes-1.parquet"))
  (ddb:fq "SELECT * FROM read_parquet(?) WHERE id < 10" url))
;; +----+------+--------------------------------+----------------------------------------+
;; | id | name | location                       | phoneNumbers                           |
;; +----+------+--------------------------------+----------------------------------------+
;; |  0 | p0   | NIL                            | ((phone ((number . 0) (kind . cell)))) |
;; |  1 | p1   | ((lon . 1.0d0) (lat . 2.0d0))  | ((phone ((number . 1) (kind . cell)))) |
;; |  2 | p2   | ((lon . 2.0d0) (lat))          | ((phone ((number . 2) (kind . cell)))) |
;; |  3 | p3   | NIL                            | ((phone ((number . 3) (kind . cell)))) |
;; |  4 | p4   | ((lon . 4.0d0) (lat . 8.0d0))  | ((phone ((number . 4) (kind . cell)))) |
;; |  5 | p5   | ((lon . 5.0d0) (lat))          | ((phone ((number . 5) (kind . cell)))) |
;; |  6 | p6   | NIL                            | ((phone ((number . 6) (kind . cell)))) |
;; |  7 | p7   | ((lon . 7.0d0) (lat . 14.0d0)) | ((phone ((number . 7) (kind . cell)))) |
;; |  8 | p8   | ((lon . 8.0d0) (lat))          | ((phone ((number . 8) (kind . cell)))) |
;; |  9 | p9   | NIL                            | ((phone ((number . 9) (kind . cell)))) |
;; +----+------+--------------------------------+----------------------------------------+
;; => NIL

Sparks

There’s some support for plotting query results directly in the REPL via cl-spark:

(ddb:initialize-default-connection) ; => #<DUCKDB::CONNECTION {1014081EF3}>

(ddb:bind-static-table
 'numbers `(("x" . (,(loop :for i :from 0d0 :by 0.2 :below pi :collect i)
                     :duckdb-double)))) ; => NIL

(ddb:spark-query "SELECT x, sin(x) AS y, cos(x) AS z FROM numbers" nil '(x y z))
;; X ▁▁▁▂▂▃▃▄▄▅▅▆▆▇▇█
;; Y ▁▂▃▄▆▆▇▇█▇▇▆▅▄▃▁
;; Z █▇▇▇▆▆▅▅▄▃▃▂▁▁▁▁
;; => NIL

(ddb:vspark-query "SELECT pow(2, x) AS y FROM numbers" nil nil 'y)
;; 1.0                    4.5                     8.0
;; ˫-----------------------+------------------------˧
;; ▏
;; █▏
;; ██▎
;; ███▋
;; █████▍
;; ███████▏
;; █████████▎
;; ███████████▋
;; ██████████████▌
;; █████████████████▊
;; █████████████████████▍
;; █████████████████████████▋
;; ██████████████████████████████▌
;; ████████████████████████████████████▎
;; ██████████████████████████████████████████▋
;; ██████████████████████████████████████████████████
;; => NIL

(ddb:vspark-query "SELECT round(x, 2)::text AS x, sqrt(x) AS y FROM numbers" nil
                  'x 'y)
;;     0.0            0.8660254             1.7320508
;;     ˫---------------------+----------------------˧
;; 0.0 ▏
;; 0.2 ███████████▉
;; 0.4 ████████████████▊
;; 0.6 ████████████████████▋
;; 0.8 ███████████████████████▊
;; 1.0 ██████████████████████████▌
;; 1.2 █████████████████████████████▏
;; 1.4 ███████████████████████████████▍
;; 1.6 █████████████████████████████████▋
;; 1.8 ███████████████████████████████████▋
;; 2.0 █████████████████████████████████████▌
;; 2.2 ███████████████████████████████████████▍
;; 2.4 █████████████████████████████████████████▎
;; 2.6 ██████████████████████████████████████████▊
;; 2.8 ████████████████████████████████████████████▌
;; 3.0 ██████████████████████████████████████████████
;; => NIL

Writing queries via SxQL

If you want to use a syntax based on s-expressions for your queries, then the SxQL library is an option:

(ddb:initialize-default-connection)

;; Load SxQL
(ql:quickload :sxql)
(use-package :sxql)

;; Create a table
(ddb:run (yield (create-table :numbers
                  ((i :type 'integer
                      :primary-key t)))))

;; Define utility function
(defun query-sxql (q) (multiple-value-call #'ddb:query (yield q)))

;; Populate table with values
(loop :for x :below 100
      :do (query-sxql (insert-into :numbers (set= :i x))))

(query-sxql (select ((:as (:sum :i) :sum))
              (from :numbers)
              (where (:even :i))))
;; => (("sum" . #(4950)))

Please refer to the SxQL documentation for more examples.

Appenders

Appenders are one of the ways of loading bulk data into DuckDB. They append rows to a single table of a database:

(ddb:initialize-default-connection) ; => #<DUCKDB::CONNECTION {100B1088F3}>

(ddb:run "CREATE TABLE roman_numerals (i INTEGER, value TEXT)") ; => NIL
(ddb:with-appender (appender "roman_numerals")
  (loop :for i :from 1 :below 4999
        :do (ddb:append-row appender (list i (format nil "~:@R" i))))) ; => NIL
(ddb:get-result (ddb:query "SELECT * FROM roman_numerals WHERE i = 1848" nil)
                'value 0) ; => "MDCCCXXXXVIII"

Querying Lisp vectors and lists as table columns

Currently only the following types are supported (the values are currently copied into DuckDB data chunks internally). Using a combination of vectors and list for different columns is possible, but each column should have the same length. Tables using Lisp data structures are not bound to a single connection and work across different ones.

Specialized vectors

Common Lisp typeDuckDB Type
bitBOOLEAN
(unsigned-byte 8)UTINYINT
(unsigned-byte 16)USMALLINT
(unsigned-byte 32)UINTEGER
(unsigned-byte 64)UBIGINT
(unsigned-byte 128)UHUGEINT
(signed-byte 8)TINYINT
(signed-byte 16)SMALLINT
(signed-byte 32)INTEGER
(signed-byte 64)BIGINT
(signed-byte 128)HUGEINT
single-floatREAL
double-floatDOUBLE

Lists & unspecialized vectors

List columns or unspecialized vectors need to specify the DuckDB column type and can contain the following values:

  • Booleans (nil, t, :false, :true, :null)
  • Integers in range of the corresponding column type
  • Floating point numbers (single-float & double-float)
  • Strings
  • Date, time or datetime values
  • UUIDs
  • NIL values

Examples

(ddb:initialize-default-connection) ; => #<DUCKDB::CONNECTION {10074E8BE3}>

;; Use vectors as columns in a query:
(let ((indexes (make-array '(10) :element-type '(unsigned-byte 8)
                                 :initial-contents '(1 2 3 4 5 6 7 8 9 10)))
      (primes (make-array '(10) :element-type '(unsigned-byte 8)
                                :initial-contents '(2 3 5 7 11 13 17 19 23 29))))
  (ddb:with-static-table ('primes `((i . ,indexes)
                                    (p . ,primes)))
    (ddb:format-query "SELECT * FROM primes" nil)))
;; +----+----+
;; | i  | p  |
;; +----+----+
;; |  1 |  2 |
;; |  2 |  3 |
;; |  3 |  5 |
;; |  4 |  7 |
;; |  5 | 11 |
;; |  6 | 13 |
;; |  7 | 17 |
;; |  8 | 19 |
;; |  9 | 23 |
;; | 10 | 29 |
;; +----+----+
;; => NIL

;; DuckDB column types always have to be specified for lists (NIL
;; values are converted to NULL):
(ddb:with-static-table ('integers `((i . (,(loop :for i :below 1000
                                                 :if (evenp i) :collect i
                                                   :else :collect nil)
                                          :duckdb-integer))))
  (ddb:query (ddb:concat "SELECT sum(i) AS sum "
                         ", COUNT(i) AS not_null_count "
                         "FROM integers")
             nil)) ; => (("sum" . #(249500)) ("not_null_count" . #(500)))

(ddb:with-static-table ('lyrics `(("in the year" . (,(list (format nil "~R" 2525))
                                                    :duckdb-varchar))))
  (ddb:query "SELECT * FROM lyrics" nil))
;; => (("in the year" . #("two thousand five hundred twenty-five")))

;; If another table with the same name exists, you can use the
;; static_table table function directly:
(ddb:run (ddb:concat "CREATE TABLE polysemy (\"That you have but slumbered here, "
                     "While these visions did appear\" VARCHAR)"))
(ddb:with-static-table
    ('polysemy `(("If we shadows have offended, Think but this, and all is mended:"
                  . (() :duckdb-varchar))))
  (ddb:query (ddb:concat "SELECT A.*, B.* FROM static_table('polysemy') AS A "
                         "JOIN polysemy AS B ON true")
             nil))
;; => (("If we shadows have offended, Think but this, and all is mended:" . #())
;;     ("That you have but slumbered here, While these visions did appear" . #()))

(ddb:with-static-table ('bools `((v . ((nil t :false :true :null)
                                       :duckdb-boolean))))
  (ddb:format-query "SELECT v, v IS NULL AS is_null FROM bools" nil))
;; +-----+---------+
;; | v   | is_null |
;; +-----+---------+
;; | NIL | NIL     |
;; | T   | NIL     |
;; | NIL | NIL     |
;; | T   | NIL     |
;; | NIL | T       |
;; +-----+---------+
;; => NIL

;; Static tables can be managed in the global scope using the
;; BIND-STATIC-TABLE, UNBIND-STATIC-TABLE and CLEAR-STATIC-TABLES
;; functions. Temporarily overriding a table definition via
;; WITH-STATIC-TABLE works as expected:
(ddb:bind-static-table
 'alphabet
 `((c . (("α" "β" "γ" "δ") :duckdb-varchar)))) ; => NIL

(labels ((get-characters ()
           (loop :with results := (ddb:query "SELECT c FROM alphabet" nil)
                 :for c :across (ddb:get-result results 'c)
                 :collect c)))
  (ddb:with-static-table ('alphabet `((c . (("" "" "" "")
                                            :duckdb-varchar))))
    (ddb:with-static-table ('alphabet `((c . (("𐌀" "𐌁" "𐌂" "𐌃" "𐌄")
                                              :duckdb-varchar))))
      (format t "Etruscan: ~{~a~^, ~}~%" (get-characters)))
    (format t "Asomtavruli: ~{~a~^, ~}~%" (get-characters)))
  (format t "Greek: ~{~a~^, ~}~%" (get-characters)))
;; Etruscan: 𐌀, 𐌁, 𐌂, 𐌃, 𐌄
;; Asomtavruli: Ⴀ, Ⴁ, Ⴂ, Ⴃ
;; Greek: α, β, γ, δ
;; => NIL

(ddb:unbind-static-table 'alphabet) ; => NIL
(ddb:clear-static-tables) ; => NIL

Type & Value conversions

DuckDB TypeCommon Lisp TypeNote
NULLnullnil (or :null for param. binding)
BOOLEANbooleant, nil (or :true & :false for param. binding)
VARCHARstring
BLOB(vector (unsigned-byte 8))
REALsingle-float
DOUBLEdouble-float
DECIMALratioMax width of 38
TINYINTinteger
UTINYINTinteger
SMALLINTinteger
USMALLINTinteger
INTEGERinteger
UINTEGERinteger
BIGINTinteger
UBIGINTinteger
HUGEINTinteger
UHUGEINTinteger
DATElocal-time:date
TIMESTAMPlocal-time:timestampMicrosecond precision
TIMElocal-time-duration:durationMicrosecond precision
INTERVALperiods:durationMicrosecond precision
UUIDfrugal-uuid:uuid
ENUM typesstring
LIST typeslist
STRUCT typesalist
UNION types*Maps to one of the member types (or nil)
BIT (BITSTRING)bit-vector

NIL as boolean FALSE vs NIL as NULL / custom return value for SQL NULL

(ddb:initialize-default-connection)
;; => #<DUCKDB::CONNECTION {101CAC0A73}>

;; The boolean TRUE and FALSE values are mapped to T and NIL
;; respectively in Lisp, but SQL NULL is also mapped to NIL causing
;; some ambiguity:

(ddb:query "SELECT TRUE AS x, FALSE AS y, NULL AS z" '())
;; => (("x" . #(T)) ("y" . #(NIL)) ("z" . #(NIL)))

;; When necessary it's possible to differentiate between FALSE and
;; NULL by simply using the IS NULL logical operator:

(ddb:query "SELECT FALSE IS NOT NULL AS x, NULL IS NULL AS y" '())
;; => (("x" . #(T)) ("y" . #(T)))

;; When binding parameter values, NIL is bound as FALSE when DuckDB
;; can determine that the parameter type is boolean and as NULL
;; otherwise. This means that simple cases like the following work as
;; expected:

(ddb:run "CREATE TABLE values (v BOOLEAN)"
         '("INSERT INTO values (v) VALUES (?)" (nil))) ; => NIL
(ddb:query "SELECT v, v IS NOT NULL AS is_not_null FROM values" '())
;; => (("v" . #(NIL)) ("is_not_null" . #(T)))

;; In some cases DuckDB doesn't determine parameter types based on the
;; query and NIL is bound as NULL even for boolean parameters:

(ddb:query "SELECT ?::boolean || '' IS NULL AS x" '(nil))
;; => (("x" . #(T)))

;; To differentiate between FALSE and NULL unambiguously when binding
;; boolean parameters, the keywords :FALSE and :NULL can be used. In
;; the query below the first parameter type is not determined by
;; DuckDB, so NIL would be bound AS NULL as seen in the similar
;; example directly above. The second parameter type is correctly
;; identified as boolean, so NIL would be bound as FALSE in this case.

(ddb:query "SELECT ?::boolean || '' IS NOT NULL AS x, ?::boolean IS NULL AS y"
           '(:false :null)) ; => (("x" . #(T)) ("y" . #(T)))

;; For completeness the :TRUE keyword is also supported. When used as
;; a parameter value, it is equivalent to using T:

(ddb:query "SELECT ? = ? AS x" '(:true t)) ; => (("x" . #(T)))

Alternatively the default return value for SQL NULL can be customized:

(ddb:initialize-default-connection)
;; => #<DUCKDB::CONNECTION {10042C24C3}>

;; NIL is used by default
(ddb:query "SELECT ? AS x" '(:null)) ; => (("x" . #(NIL)))

;; Represent SQL NULL values as :NULL in the result for only one call
(ddb:query "SELECT ? AS x" '(:null)
           :sql-null-return-value :null) ; => (("x" . #(:NULL)))

;; Change the default value
(setf ddb:*sql-null-return-value* :null) ; => :NULL
(ddb:query "SELECT ? AS x" '(:null)) ; => (("x" . #(:NULL)))

Development setup

  • Install Quicklisp
  • Clone this repository and add it as a local Quicklisp project, for example:
git clone [email protected]:ak-coram/cl-duckdb.git ~/Projects/cl-duckdb
ln -s ~/Projects/cl-duckdb ~/quicklisp/local-projects/cl-duckdb
  • Start your favored REPL (e.g. sbcl) and load the library using Quicklisp:
(ql:quickload :duckdb)

Running tests

  • Load the tests via Quicklisp:
(ql:quickload :duckdb/test)
;; Using ASDF:
(asdf:test-system :duckdb)
;; Using FiveAM directly:
(fiveam:run! :duckdb)

Running benchmarks

  • Load the benchmarks via Quicklisp:
(ql:quickload :duckdb/benchmark)
  • Use ASDF or run the benchmarks directly:
;; Using ASDF:
(asdf:test-system :duckdb/benchmark)
;; Running directly:
(duckdb/benchmark:run-benchmarks)

Legal