DuckDB connector#

The DuckDB connector allows querying and creating tables in an external DuckDB instance. This can be used to join data between different systems like DuckDB and Hive, or between two different DuckDB instances.

Requirements#

All cluster nodes must include libstdc++ as required by the DuckDB JDBC driver.
The path to the persistent DuckDB database must be identical and available on all cluster nodes and point to the same storage location.

Configuration#

The connector can query a DuckDB database. Create a catalog properties file that specifies the DuckDb connector by setting the connector.name to duckdb.

For example, to access a database as the example catalog, create the file etc/catalog/example.properties. Replace the connection properties as appropriate for your setup:

connector.name=duckdb
connection-url=jdbc:duckdb:<path>

The connection-url defines the connection information and parameters to pass to the DuckDB JDBC driver. The parameters for the URL are available in the DuckDB JDBC driver documentation.

The <path> must point to an existing, persistent DuckDB database file. For example, use jdbc:duckdb:/opt/duckdb/trino.duckdb for a database created with the command duckdb /opt/duckdb/trino.duckdb. The database automatically contains the main schema and the information_schema schema. Use the main schema for your new tables or create a new schema.

When using the connector on a Trino cluster the path must be consistent on all nodes and point to a shared storage to ensure that all nodes operate on the same database.

Using a in-memory DuckDB database jdbc:duckdb: is not supported.

Refer to the DuckDB documentation for tips on securing DuckDB. Note that Trino connects to the database using the JDBC driver and does not use the DuckDB CLI.

Multiple DuckDB servers#

The DuckDB connector can only access a single database within a DuckDB instance. Thus, if you have multiple DuckDB servers, or want to connect to multiple DuckDB servers, you must configure multiple instances of the DuckDB connector.

Type mapping#

Because Trino and DuckDB each support types that the other does not, this connector modifies some types when reading or writing data. Data types may not map the same way in both directions between Trino and the data source. Refer to the following sections for type mapping in each direction.

List of DuckDB data types.

DuckDB type to Trino type mapping#

The connector maps DuckDB types to the corresponding Trino types following this table:

DuckDB type to Trino type mapping#
DuckDB type	Trino type	Notes
`BOOLEAN`	`BOOLEAN`
`TINYINT`	`TINYINT`
`SMALLINT`	`SMALLINT`
`INTEGER`	`INTEGER`
`BIGINT`	`BIGINT`
`FLOAT`	`REAL`
`DOUBLE`	`DOUBLE`
`DECIMAL`	`DECIMAL`	Default precision and scale are (18,3).
`VARCHAR`	`VARCHAR`
`DATE`	`DATE`

No other types are supported.

Trino type to DuckDB type mapping#

The connector maps Trino types to the corresponding DuckDB types following this table:

Trino type to DuckDB type mapping#
Trino type	DuckDB type	Notes
`BOOLEAN`	`BOOLEAN`
`TINYINT`	`TINYINT`
`SMALLINT`	`SMALLINT`
`INTEGER`	`INTEGER`
`BIGINT`	`BIGINT`
`REAL`	`REAL`
`DOUBLE`	`DOUBLE`
`DECIMAL`	`DECIMAL`
`CHAR`	`VARCHAR`
`VARCHAR`	`VARCHAR`
`DATE`	`DATE`

No other types are supported.

Type mapping configuration properties#

The following properties can be used to configure how data types from the connected data source are mapped to Trino data types and how the metadata is cached in Trino.

Property name

Description

Default value

unsupported-type-handling

Configure how unsupported column data types are handled:

IGNORE, column is not accessible.
CONVERT_TO_VARCHAR, column is converted to unbounded VARCHAR.

The respective catalog session property is unsupported_type_handling.

IGNORE

jdbc-types-mapped-to-varchar

Allow forced mapping of comma separated lists of data types to convert to unbounded VARCHAR

SQL support#

The connector provides read access and write access to data and metadata in a DuckDB database. In addition to the globally available and read operation statements, the connector supports the following features:

Procedures#

`system.flush_metadata_cache()`#

Flush JDBC metadata caches. For example, the following system call flushes the metadata caches for all schemas in the example catalog

USE example.example_schema;
CALL system.flush_metadata_cache();

`system.execute('query')`#

The execute procedure allows you to execute a query in the underlying data source directly. The query must use supported syntax of the connected data source. Use the procedure to access features which are not available in Trino or to execute queries that return no result set and therefore can not be used with the query or raw_query pass-through table function. Typical use cases are statements that create or alter objects, and require native feature such as constraints, default values, automatic identifier creation, or indexes. Queries can also invoke statements that insert, update, or delete data, and do not return any data as a result.

The query text is not parsed by Trino, only passed through, and therefore only subject to any security or access control of the underlying data source.

The following example sets the current database to the example_schema of the example catalog. Then it calls the procedure in that schema to drop the default value from your_column on your_table table using the standard SQL syntax in the parameter value assigned for query:

USE example.example_schema;
CALL system.execute(query => 'ALTER TABLE your_table ALTER COLUMN your_column DROP DEFAULT');

Verify that the specific database supports this syntax, and adapt as necessary based on the documentation for the specific connected database and database version.

Table functions#

The connector provides specific table functions to access DuckDB.

`query(varchar) -> table`#

The query function allows you to query the underlying database directly. It requires syntax native to DuckDB, because the full query is pushed down and processed in DuckDB. This can be useful for accessing native features which are not available in Trino or for improving query performance in situations where running a query natively may be faster.

Find details about the SQL support of DuckDB that you can use in the query in the DuckDB SQL Command Reference and other statements and functions.

The native query passed to the underlying data source is required to return a table as a result set. Only the data source performs validation or security checks for these queries using its own configuration. Trino does not perform these tasks. Only use passthrough queries to read data.

As a simple example, query the example catalog and select an entire table:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'SELECT
        *
      FROM
        tpch.nation'
    )
  );

Note

The query engine does not preserve the order of the results of this function. If the passed query contains an ORDER BY clause, the function result may not be ordered as expected.