DataFusion Table Providers

When integrating these providers, you have two options for crate selection:

1. Leaf Crates (e.g., datafusion-table-providers-postgres, datafusion-table-providers-duckdb):

   • Use these if you only need a single backend.
   • They enable federation by default (opt out with default-features = false).
   • SQLite leaf crates default to the bundled rusqlite feature.

2. Facade Crate (datafusion-table-providers):

   • Use this to access multiple providers via feature flags.
   • It maintains the same module paths and provider feature names (e.g., duckdb, postgres, sqlite).
   • Enabling a provider feature pulls in the corresponding leaf crate with its defaults (including federation).

To run the Python examples, follow these steps:

1. Start a Python virtual environment (venv).
2. Enter the virtual environment.
3. Navigate to the python/ folder and run maturin develop to install the package.
4. Navigate to python/examples/ and run the desired example using python3 [file_name].

To run the ClickHouse example, you need a running ClickHouse server.

1. Start ClickHouse in Docker:

docker run --name clickhouse \
    -e CLICKHOUSE_DB=default \
    -e CLICKHOUSE_USER=admin \
    -e CLICKHOUSE_PASSWORD=secret \
    -p 8123:8123 \
    -p 9000:9000 \
    -d clickhouse/clickhouse-server:24.8-alpine

2. Wait for the server to be ready (check http://localhost:8123/ping).
3. Initialize tables and views using clickhouse-client via docker exec.
4. Run the example from the repo folder.

# Run from repo folder
cargo run -p datafusion-table-providers --example clickhouse --features clickhouse

The ADBC provider requires an ADBC driver installed via the dbc tool. This example uses DuckDB as the driver.

# Install driver
dbc install duckdb

# Run example
cargo run --example adbc --features adbc

The ODBC provider requires system ODBC drivers (e.g., unixodbc-dev and libsqliteodbc on Linux, or unixodbc and sqliteodbc on macOS).

macOS ARM (M1/M2) Note: You must manually configure your ~/.odbcinst.ini file to point to the local sqliteodbc driver path (e.g., /opt/homebrew/Cellar/sqliteodbc/.../lib/libsqlite3odbc.dylib) and verify it with odbcinst -q -d -n SQLite3.

# Run SQLite ODBC example
cargo run --example odbc_sqlite --features odbc

The Flight SQL provider requires roapi. You can install it via Homebrew or Cargo, then start a local Flight SQL server before running the example.

# Install roapi
brew install roapi

# Start a Flight SQL server
roapi -t taxi=https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet &

# Run the example
cargo run -p datafusion-table-providers --example flight-sql --features flight

To use these table providers with efficient federation push-down (e.g., pushing down joins between multiple tables from the same database or implementing TopK queries), you must integrate the datafusion-federation crate.

Instead of using a standard SessionContext, create one using the Federation optimizer rule and query planner by providing a default session state from datafusion-federation.

use datafusion::prelude::SessionContext;

let state = datafusion_federation::default_session_state();
let ctx = SessionContext::with_state(state);

// Register the specific table providers into ctx
// queries will now automatically be federated

The Oracle provider uses rust-oracle (ODPI-C) and requires Oracle Instant Client libraries at runtime. These are dlopen'd on the first connection.

Installation Requirements:

• Linux (Debian/Ubuntu): Install libaio1, wget, and unzip. Download the Instant Client basiclite zip, unzip to /opt/oracle, and set LD_LIBRARY_PATH.
• macOS: brew install instantclient-basic.

To run integration tests, start an Oracle Free server in Docker and wait for the healthcheck to pass.

# Example command to run integration tests
# Requires Oracle Free server running in Docker
cargo test -p datafusion-table-providers --test integration --no-default-features --features oracle -- oracle

To run the Postgres example, you must have a running Postgres server. You can use Docker to set up a test environment:

1. Start Postgres in Docker:

docker run --name postgres -e POSTGRES_PASSWORD=password -e POSTGRES_DB=postgres_db -p 5432:5432 -d postgres:16-alpine

2. Wait ~30 seconds, then initialize schema/data using psql via docker exec.
3. Run the example from the repo folder.

# Run from repo folder
cargo run -p datafusion-table-providers --example postgres --features postgres

The SQLite provider supports two modes for inserting data batches:

1. Prepared Statements (Recommended): Enabled via .with_batch_insert_use_prepared_statements(true). This method prepares a parameterized INSERT statement once and executes it for each row. It is significantly faster (throughput of ~1.5-2 million rows/second) due to statement caching, efficient parameter binding, and reduced parsing overhead.
2. Inline SQL (Legacy): Uses string formatting to generate SQL for every row. This is slower and less memory-efficient.

Use insert_batch_prepared within the implementation to leverage these performance gains.

// When creating the provider via the factory:
let factory = SqliteTableProviderFactory::new()
    .with_batch_insert_use_prepared_statements(true);

The DuckDBTable implementation supports using DuckDB functions (like read_parquet('file.parquet')) as the source for a table. This is achieved by passing a mapping of table names to their corresponding DuckDB function calls in the table_functions parameter of DuckDBTable::new_with_schema.

Internally, these functions are wrapped in a WITH clause (Common Table Expression) and prepended to the generated SQL query. This allows DataFusion to treat the result of a DuckDB function call as a standard table.

Example mapping structure:

• Key: "my_parquet_table"
• Value: "read_parquet('path/to/file.parquet')"

This results in a query structure like: WITH my_parquet_table AS (SELECT * FROM read_parquet('path/to/file.parquet')) SELECT ...