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.

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

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。