CrateDB¶

CrateDB is a distributed and scalable SQL database for storing and analyzing massive amounts of data in near real-time, even with complex queries. It is PostgreSQL-compatible, and based on Lucene.

omniload supports CrateDB as a source and destination database, using different adapters and protocols (HTTP vs. PostgreSQL, see below).

Source¶

For connecting to CrateDB as a database source, omniload uses its SQLAlchemy dialect package sqlalchemy-cratedb, effectively talking HTTP to port 4200 by default.

URI format¶

The URI format for CrateDB as a source is as follows:

crate://<username>:<password>@<host>:<port>?ssl=<ssl>

[!INFO] The driver does not require any option, and will default to crate://crate@localhost:4200?ssl=false when just using crate://.

When connecting to CrateDB on localhost, use:
crate://crate@localhost:4200?ssl=false
When connecting to CrateDB Cloud, the URI looks like this:
crate://admin:<PASSWORD>@<CLUSTERNAME>.eks1.eu-west-1.aws.cratedb.net:4200?ssl=true

URI parameters¶

username (required): The username is required to authenticate with the CrateDB server.
password (required): The password is required to authenticate the provided username.
host (required): The hostname or IP address of the CrateDB server where the database is hosted.
port (required): The TCP port number used by the CrateDB server. Mostly 4200.
ssl (optional): Set to true for a secure HTTPS connection. By default, SSL is disabled (false).

Example¶

This is an example command that will copy the sys.summits table from CrateDB to DuckDB, then display the content from DuckDB.

omniload ingest \
    --source-uri 'crate://crate@localhost:4200/' \
    --source-table 'sys.summits' \
    --dest-uri 'duckdb:///cratedb.duckdb' \
    --dest-table 'dest.summits'

duckdb cratedb.duckdb 'SELECT * FROM dest.summits LIMIT 5'

Note that, as of February 2026, for compatibility and harmonization reasons, the CrateDB source adapter can also be addressed using the cratedb://-style URI format like outlined below. However, please note that both are still different adapters using different protocols.

Destination¶

For connecting to CrateDB as a database destination, omniload uses the dlt cratedb adapter, which is based on the dlt postgres adapter, in turn using the psycopg2 package, effectively speaking the PostgreSQL wire protocol on port 5432 by default.

URI format¶

The URI format for CrateDB as a destination is as follows:

cratedb://<username>:<password>@<host>:<port>?sslmode=<sslmode>

[!INFO] When connecting to CrateDB on localhost, use:
cratedb://crate:@localhost:5432?sslmode=disable
When connecting to CrateDB Cloud, the URI looks like this:
cratedb://admin:<PASSWORD>@<CLUSTERNAME>.eks1.eu-west-1.aws.cratedb.net:5432?sslmode=require

URI parameters¶

username (required): The username is required to authenticate with the CrateDB server.
password (required): The password is required to authenticate the provided username.
host (required): The hostname or IP address of the CrateDB server where the database is hosted.
port (required): The TCP port number used by the CrateDB server. Mostly 5432.
sslmode (optional): Set to one of disable, allow, prefer, require, verify-ca, or verify-full, see PostgreSQL SSL Mode Descriptions.

Example¶

This is an example command that will import a CSV file into CrateDB, then display the content from CrateDB.

wget -O input.csv https://github.com/panodata/omniload/raw/refs/heads/main/omniload/testdata/create_replace.csv

omniload ingest \
   --source-uri 'csv://input.csv' \
   --source-table 'sample' \
   --dest-uri 'cratedb://crate:@localhost:5432/?sslmode=disable' \
   --dest-table 'doc.sample'

uvx crash -c 'SELECT * FROM doc.sample'

[!WARNING] CrateDB supports the replace incremental materialization strategy, but currently does not support the delete+insert, merge, or scd2 strategies.

Appendix¶

To start a single-node instance of CrateDB for evaluation purposes, use Docker or Podman.

docker run --rm -it --name=cratedb \
  --publish=4200:4200 --publish=5432:5432 \
  --env=CRATE_HEAP_SIZE=2g crate/crate:nightly \
  -Cdiscovery.type=single-node

We are tracking development progress and incompatibilities at Support for omniload/CrateDB and “tool: dlt/omniload”. Please join the discussion or share relevant issue reports that help us improve interoperability. Thanks!