Introduction: The Query Problem in the Data Silo Era

In large enterprise environments, data is spread across relational databases, document stores, message queues, data lakes, and cloud data warehouses. Many of these systems can only be accessed through vendor-provided Java SDKs—Oracle JDBC, MongoDB Java Driver, or even internal SOAP endpoints. To analyze this data together, traditional solutions require deploying distributed query engines like Trino, or exporting data to Parquet first.

DuckDB has introduced a revolutionary feature: Pure Java Table Functions. This allows you to register a custom table function directly in your Java application, exposing any Java-accessible data source as a SQL table, and then perform federated queries across it—all without writing a single line of C++ code or building a DuckDB extension.

This article is based on the DuckDB official blog post and walks through a complete MongoDB example.

Background: Why Pure Java Table Functions?

Problems with Traditional Approaches

Before pure Java table functions, accessing a custom data source in DuckDB had limited options:

Advantages of Pure Java Table Functions

Pure Java table functions solve all of the above:

1. Zero native dependencies: Pure Maven project, no C++ toolchain needed
2. Reuse existing clients: Use the vendor’s Java Driver directly, no protocol reimplementation
3. Streaming reads: Data flows into DuckDB in batches via cursor, not all in memory
4. Predicate pushdown: Filters are passed in the source’s query language and executed remotely
5. Federated queries: Remote data JOINs with local CSV/Parquet in a single SQL statement

Core Concept: DuckDB Table Function Lifecycle

DuckDB table functions follow a three-stage lifecycle:

┌─────────────────────────────────────────────────────────────┐
│                  Table Function Lifecycle                     │
├──────────────┬──────────────┬──────────────┬────────────────┤
│    BIND      │     INIT     │    APPLY     │   Cleanup      │
│  (Prepare)   │  (Initialize)│  (Execute)    │                │
├──────────────┼──────────────┼──────────────┼────────────────┤
│ • Read params │ • Open cursor │ • Fill chunk  │ • Close conn   │
│ • Declare schema│ • Connect   │   (≤2048 rows)│ • Release res  │
│ • Create bind │              │ • Return count │                │
│   object     │              │   (0 = done)   │                │
└──────────────┴──────────────┴──────────────┴────────────────┘

• bind: Prepare phase—declare the output column types and names
• init: Pre-execution phase—open cursor or establish connection
• apply: Execution phase—fill one data chunk (up to 2048 rows) at a time, return 0 when done
• initLocal (optional): Per-thread initialization for multi-threaded execution

Hands-on: Building the mongo_query() Table Function

Step 1: Project Setup

Create a Maven project with two dependencies:

<dependencies>
    <dependency>
        <groupId>org.duckdb</groupId>
        <artifactId>duckdb_jdbc</artifactId>
        <version>2.0.0-alpha</version>
    </dependency>
    <dependency>
        <groupId>org.mongodb</groupId>
        <artifactId>mongodb-driver-sync</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Step 2: Define the Parameters Class

public class MongoQueryParameters {
    private String collectionName;
    private String queryJson;
    private String columns;
    private String hostname;
    private int port;
    private String database;
    private String username;
    private String password;

    // getters and setters...
}

Step 3: Implement the Table Function Core

public class MongoQueryFunction implements DuckDBTableFunction {

    // ========== BIND: Declare Output Schema ==========
    @Override
    public DuckDBTableFunctionBindData bind(DuckDBTableFunctionBindInfo info) throws Exception {
        String collectionName = info.getParameter(0).getString();
        String queryJson = info.getParameter(1).getString();
        String columnsJson = info.getNamedParameter("columns").getString();
        String hostname = info.getNamedParameter("hostname").getString();
        int port = info.getNamedParameter("port").getInt();
        String database = info.getNamedParameter("database").getString();

        // Parse columns parameter and declare output columns
        List<String> columnNames = new ArrayList<>();
        for (BsonValue bv : BsonArray.parse(columnsJson)) {
            String name = bv.asString().getValue();
            columnNames.add(name);
            info.addResultColumn(name, String.class);
        }

        // Establish MongoDB connection
        MongoClientSettings settings = MongoClientSettings.builder()
            .serverAddress(new ServerAddress(hostname, port))
            .applyToClusterSettings(builder -> 
                builder.hosts(List.of(new ServerAddress(hostname, port))))
            .build();
        MongoClient client = MongoClients.create(settings);
        MongoCollection<Document> collection = 
            client.getDatabase(database).getCollection(collectionName);

        Document query = Document.parse(queryJson);
        return new MongoQueryBindData(client, collection, columnNames, query);
    }

    // ========== INIT: Open Cursor ==========
    @Override
    public DuckDBTableFunctionInitData init(DuckDBTableFunctionInitInfo info) throws Exception {
        info.setMaxThreads(1); // Single-threaded for this example
        MongoQueryBindData bindData = info.getBindData();
        FindIterable<Document> iter = bindData.collection.find(bindData.query);
        return new MongoQueryInitData(iter.cursor());
    }

    // ========== APPLY: Stream Rows ==========
    @Override
    public long apply(DuckDBTableFunctionCallInfo info, DuckDBDataChunkWriter output) throws Exception {
        MongoCursor<Document> cursor = info.getInitData().getResultCursor();
        long row = 0;
        
        for (; row < output.capacity() && cursor.hasNext(); row++) {
            Document doc = cursor.next();
            for (long col = 0; col < output.columnCount(); col++) {
                copyValueToVector(doc, output.vector(col), row,
                    ((MongoQueryBindData)info.getBindData())
                        .getColumnNames().get((int)col));
            }
        }
        return row; // Return 0 when data is exhausted
    }
}

Step 4: Register the Table Function

try (Connection conn = DriverManager.getConnection("jdbc:duckdb:")) {
    DuckDBFunctions.tableFunction()
        .withName("mongo_query")
        .withParameter(String.class)      // collection name
        .withParameter(String.class)      // Mongo filter (JSON)
        .withNamedParameter("columns", String.class)
        .withNamedParameter("hostname", String.class)
        .withNamedParameter("port", Integer.class)
        .withNamedParameter("database", String.class)
        .withNamedParameter("username", String.class)
        .withNamedParameter("password", String.class)
        .withFunction(new MongoQueryFunction())
        .register(conn);

    // Now use it directly in SQL!
    ResultSet rs = conn.executeQuery(
        "SELECT * FROM mongo_query('orders', " +
        "'{\"status\": \"shipped\"}', " +
        "columns='[\"customer_id\", \"amount\"]', " +
        "hostname='localhost', port=27017, database='app')"
    );
}

Federated Query in Action: Cross-Source JOIN

Once the table function is registered, the most powerful scenario is federated querying across heterogeneous sources—remote MongoDB data JOINed with local CSV files in a single SQL statement:

SELECT 
    c.region,
    COUNT(*) AS orders,
    SUM(o.amount::DECIMAL(10,2)) AS revenue
FROM mongo_query(
    'orders', 
    '{"status": "shipped"}',
    columns='["customer_id", "amount"]',
    database='app'
) AS o
JOIN 'customers/*.csv' AS c
ON c.customer_id = o.customer_id
GROUP BY c.region;

What does this SQL do?

1. mongo_query() streams shipped orders from remote MongoDB
2. 'customers/*.csv' reads local customer CSV files (supports glob patterns)
3. JOINs the two sources and aggregates by region

No data export step, no intermediate table—just one clean SQL statement.

Comparison with Traditional Approaches

Current Limitations and Considerations

1. Java Client Only

Pure Java table functions cannot be packaged as DuckDB extensions (extensions are native shared libraries). This means they’re only available through the DuckDB Java client (JDBC/ODBC), not from CLI or Python.

2. Manual Lifecycle Management

In the current release, objects returned from bind() and init() must be managed by the caller. Implement AutoCloseable:

public class MongoQueryInitData implements AutoCloseable {
    private final MongoCursor<Document> cursor;
    
    @Override
    public void close() {
        if (cursor != null) cursor.close();
    }
}

3. No Composite Types Yet

The vector API currently supports only scalar types (STRING, INTEGER, DOUBLE, etc.). STRUCT, LIST, and other nested types are planned for the future. Nested data must be flattened or serialized to strings for now.

4. Multi-threading Requires Extra Work

The example uses setMaxThreads(1) for single-threaded execution. For multi-threaded parallel scanning, you need to implement the initLocal callback and maintain per-thread state.

Monetization Suggestions

Direction 1: Internal Enterprise Data Query Platform

Pain point: Enterprise data is scattered across Oracle, MongoDB, MySQL, CSV files, etc. Business users need cross-system data but lack technical skills.

Solution: Use pure Java table functions to build an internal data query platform:

• Register table functions for each data source (Oracle JDBC, MongoDB, S3 Parquet, etc.)
• Provide a SQL query interface (SQLPad or custom Web UI)
• Business users query across systems with SQL, unaware of data location

Monetization:

• Implementation fee: $1,500–$7,000
• Annual maintenance: $3,000–$15,000
• Per-query pricing for advanced analytics: $0.50–$3.00

Direction 2: Data Source Plugin Marketplace

Pain point: Independent developers need to quickly integrate various data sources for data products, but writing C++ extensions is too hard.

Solution: Publish a series of pure Java table function extensions on GitHub (Salesforce, Stripe, Shopify API, etc.), each as a standalone Maven library:

• Basic version: free and open source
• Enterprise version (with caching, connection pooling, access control): paid subscription

Monetization:

• GitHub Sponsors: $50–$500/month
• Enterprise subscription: $29–$199/month/developer
• Consulting: $50–$200/hour

Direction 3: Data Broker Service

Pain point: SMEs need data but can’t build data pipelines. They’re willing to pay for clean, analyzed data.

Solution: Use Java table functions to query multiple sources in real-time and package results as standardized data products:

• E-commerce sales data (Shopify + Stripe + MongoDB aggregation)
• Social media trend data (Twitter API + storage analysis)
• Financial market data (broker API + news sources)

Monetization:

• Data subscription: $15–$150/month
• API call billing: $0.001–$0.01/call
• Custom data products: $700–$7,000/project

Conclusion

DuckDB’s pure Java table functions represent a major breakthrough in embedded analytics. They allow Java developers to expose any data source as a SQL table using familiar tools (Maven, JDBC Driver), perform federated queries with local files and other sources, while avoiding the complexity and risk of C++ extensions.

With DuckDB v2.0’s release and feature maturation, this capability will become an essential part of the Java analytics infrastructure. Whether for enterprise cross-system query platforms or independent developers’ data SaaS products, pure Java table functions provide unprecedented flexibility and development efficiency.

📖 Official Docs: DuckDB Java Table Functions
📦 Example Code: duckdb_mongo_example
💬 Community: DuckDB Discord #java channel