tmducken.duckdb — com.techascent/tmducken 0.8.1-08

DuckDB C-level bindings for tech.ml.dataset.

Current datatype support:

• boolean, all numeric types int8->int64, uint8->uint64, float32, float64.
• string
• LocalDate, Instant column types.

Example:

user> (require '[tech.v3.dataset :as ds])
nil
user> (require '[tmducken.duckdb :as duckdb])
nil
user> (duckdb/initialize!)
10:04:14.814 [nREPL-session-635e9bc8-2923-442b-9fad-da547210617b] INFO tmducken.duckdb - Attempting to load duckdb from "/home/chrisn/dev/cnuernber/tmducken/binaries/libduckdb.so"
true
user> (def stocks
      (-> (ds/->dataset "https://github.com/techascent/tech.ml.dataset/raw/master/test/data/stocks.csv" {:key-fn keyword})
          (vary-meta assoc :name :stocks)))
#'user/stocks
user> (def db (duckdb/open-db))
#'user/db
user> (def conn (duckdb/connect db))
#'user/conn
user> (duckdb/create-table! conn stocks)
"stocks"
user> (duckdb/insert-dataset! conn stocks)
560
user> (ds/head (duckdb/sql->dataset conn "select * from stocks"))

_unnamed [5 3]:

| symbol |       date | price |
|--------|------------|------:|
|   MSFT | 2000-01-01 | 39.81 |
|   MSFT | 2000-02-01 | 36.35 |
|   MSFT | 2000-03-01 | 43.22 |
|   MSFT | 2000-04-01 | 28.37 |
|   MSFT | 2000-05-01 | 25.45 |

close-db clj

(close-db db)

Close the database.

connect clj

(connect db)

Create a new database connection from an opened database. Users should call disconnect to close this connection.

create-table! clj

(create-table! conn dataset)

(create-table! conn dataset options)

Create an sql table based off of the column datatypes of the dataset. Note that users can also call [[execute-query!]] with their own sql create-table string. Note that the fastest way to get data into the system is [[append-dataset!]].

Options:

• :table-name - Name of the table to create. If not supplied the dataset name will be used.
• :primary-key - sequence of column names to be used as the primary key.

disconnect clj

(disconnect conn)

Disconnect a connection.

drop-table! clj

(drop-table! conn dataset)

get-config-options clj

(get-config-options)

Returns a sequence of maps of {:name :desc} describing valid valid configuration options to the open-db function.

initialize! clj

(initialize!)

(initialize! {:keys [duckdb-home]})

Initialize the duckdb ffi system. This must be called first should be called only once. It is safe, however, to call this multiple times.

Options:

• :duckdb-home - Directory in which to find the duckdb shared library. Users can pass this in. If not passed in, then the environment variable DUCKDB_HOME is checked. If neither is passed in then the library will be searched in the normal system library paths.

initialized? clj

(initialized?)

insert-dataset! clj

(insert-dataset! conn dataset)

(insert-dataset! conn dataset options)

Append this dataset using the higher performance append api of duckdb. This is recommended as opposed to using sql statements or prepared statements. That being said the schema of this dataset must match precisely the schema of the target table.

open-db clj

(open-db)

(open-db path)

(open-db path config-options)

Open a database. path may be nil in which case database is opened in-memory. For valid config options call get-config-options. Options must be passed as a map of string->string. As duckdb is dynamically linked configuration options may change but with linux-amd64-0.3.1 current options are:

tmducken.duckdb> (get-config-options)
[{:name "access_mode",
  :desc "Access mode of the database ([AUTOMATIC], READ_ONLY or READ_WRITE)"}
 {:name "default_order",
  :desc "The order type used when none is specified ([ASC] or DESC)"}
 {:name "default_null_order",
  :desc "Null ordering used when none is specified ([NULLS_FIRST] or NULLS_LAST)"}
 {:name "enable_external_access",
  :desc
  "Allow the database to access external state (through e.g. COPY TO/FROM, CSV readers, pandas replacement scans, etc)"}
 {:name "enable_object_cache",
  :desc "Whether or not object cache is used to cache e.g. Parquet metadata"}
 {:name "max_memory", :desc "The maximum memory of the system (e.g. 1GB)"}
 {:name "threads", :desc "The number of total threads used by the system"}]

prepare clj

(prepare conn sql)

(prepare conn sql options)

Create a prepared statement returning a clojure function you can call taking args specified in the prepared statement. This function is auto-closeable which releases the prepared statement - it is also registered with the resource system so it will be released when it is no longer reachable by the gc system. . The function return value can be either a sequence of datasets or a single dataset. For :streaming, the sequence is read from the result and has no count. For :realized the sequence is of known length and the result is completely realized before the first dataset is read. Finally you can have single which means the system will return a single dataset. The default is :streaming.

In the cases where a sequence is returned, the object returned is auto-closeable result object itself will be destroyed when the object is closed or when it is no longer reachable.

In general datasets are copied into the JVM on a chunk-by-chunk basis. If the user simply desires to reduce over the return value the datasets are can be zero-copied during the reduction with an option to immediately release each dataset. It is extremely quick to clone the dataset into jvm heap storage, however, so please stick with the defaults - which are safe and memory efficient - unless you have a very good reason to change them.

Options are passed through to dataset creation.

Options:

• :result-type - one of #{:streaming :realized :single} with:streaming` being the default.
  • :streaming - uncountable supplier/sequence of datasets - auto-closeable.
  • :realized - all results realized, countable supplier/sequence of datasets - auto-closeable.
  • :single - results realized into a single dataset with chunks and result being immediately released.
• :reduce-type - One of #{:clone :zero-copy-imm :zero-copy} defaulting to :clone. - When the result is reduced the dataset is initially read via zero-copy directly from the result batch. Then one of three things happen:
  • :clone - dataset cloned and batch released just before rf - safest option and default.
  • :zero-copy-imm - rf called with zero-copy dataset and batch released just after. This is very memory and cpu efficient but you need to ensure that no part of the dataset escapes rf.
  • :zero-copy - Datasets are merely passed to rf and batch is not released but is registered with the resource system. This is used to efficiently concatenate the results into one dataset after which all batches are released.

Examples:

user> (def stmt (duckdb/prepare conn "select * from stocks"))
Aug 31, 2023 8:52:25 AM clojure.tools.logging$eval5800$fn__5803 invoke
INFO: Reference thread starting
#'user/stmt
user> stmt
#duckdb-prepared-statement-0["select * from stocks"]
user> (stmt)
#duckdb-streaming-result["select * from stocks"]
user> (ds/head (first *1))
_unnamed [5 3]:

| symbol |       date | price |
|--------|------------|------:|
|   MSFT | 2000-01-01 | 39.81 |
|   MSFT | 2000-02-01 | 36.35 |
|   MSFT | 2000-03-01 | 43.22 |
|   MSFT | 2000-04-01 | 28.37 |
|   MSFT | 2000-05-01 | 25.45 |

run-query! clj

(run-query! conn sql)

(run-query! conn sql options)

Run a query ignoring the results. Useful for queries such as single-row insert or update where you don't care about the results.

sql->dataset clj

(sql->dataset conn sql)

(sql->dataset conn sql options)

Execute a query returning a single dataset. This runs the query in a context that releases the memory used for the result set before function returns returning a dataset that has no native bindings.

sql->datasets clj

(sql->datasets conn sql)

(sql->datasets conn sql options)

Execute a query returning either a sequence of datasets or a single dataset.

See documentation and options for prepare.

Examples:

tmducken.duckdb> (first (sql->datasets conn "select * from stocks"))
_unnamed [560 3]:

| symbol |       date | price |
|--------|------------|------:|
|   MSFT | 2000-01-01 | 39.81 |
|   MSFT | 2000-02-01 | 36.35 |
|   MSFT | 2000-03-01 | 43.22 |
|   MSFT | 2000-04-01 | 28.37 |
|   MSFT | 2000-05-01 | 25.45 |
|   MSFT | 2000-06-01 | 32.54 |
|   MSFT | 2000-07-01 | 28.40 |



tmducken.duckdb> (ds/head (sql->dataset conn "select * from stocks"))
_unnamed [5 3]:

| symbol |       date | price |
|--------|------------|------:|
|   MSFT | 2000-01-01 | 39.81 |
|   MSFT | 2000-02-01 | 36.35 |
|   MSFT | 2000-03-01 | 43.22 |
|   MSFT | 2000-04-01 | 28.37 |
|   MSFT | 2000-05-01 | 25.45 |