Using DuckDB inside MySQL

We are pleased to announce a new extension for VillageSQL Server that enables running DuckDB queries from within MySQL and joining those results with MySQL query results. DuckDB has emerged as the analytical engine of choice for fast querying of data formats such as Parquet. It excels with analytics queries because of its columnar storage, vectorized execution, and embedded architecture. Applications often need to combine the results of analytical queries with operational results, though. There are multiple ways to do this, but many are suboptimal when the query's results need to be returned to an application that is connected to an operational database such as MySQL.

The new vsql-duckdb extension from VillageSQL solves this by embedding DuckDB inside MySQL, keeping your existing database connection and SQL as the interaction point. It embeds DuckDB inside VillageSQL Server and exposes three functions that pass query text to it, which is similar to what pg_duckdb offers for PostgreSQL.

VillageSQL is the innovation platform for MySQL that adds an extension framework (VEF), similar to PostgreSQL's extension framework, to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today.

Three functions

The extension has three functions. Two of the functions take DuckDB query text as a string and differ only in what they hand back. duckdb_scalar() returns the first value of the first row, which covers counts, sums, and anything else that is a single answer. duckdb_query() returns the whole result as a JSON array with one object per row, and MySQL's JSON_TABLE turns that array back into rows you can join. The third function, duckdb_status(), takes no query at all and reports which DuckDB version is compiled in, which file readers the bundle was built with, and whether your object storage credential loaded.

Installing

To get started, build the extension from source (https://github.com/villagesql/vsql-duckdb). The build compiles DuckDB inside it, so it takes a few minutes the first time. The extension runs on VillageSQL Server 0.0.6 or newer. The examples in this post that pass a result into JSON_EXTRACT or JSON_TABLE need a server newer than 0.0.6 (0.0.7-dev as of this writing), which hands VEF function results to MySQL's JSON functions as utf8mb4 text. On 0.0.6, wrap the call in CONVERT(... USING utf8mb4) first. Follow the build instructions on the Readme. The install step writes vsql_duckdb.veb (VillageSQL Extension Bundle) into the directory the server loads extensions from. If you want to confirm where that is, ask the server with SHOW VARIABLES LIKE 'veb_dir'.

Next, install the extension from SQL. The extension declares two preview capabilities, sys_var and keyring, so the server has to allow preview extensions first. SET PERSIST takes effect immediately, so these two statements run back to back with no restart between them:

SET PERSIST vsql_allow_preview_extensions = ON;
INSTALL EXTENSION vsql_duckdb;

Ask duckdb_status() which readers the build gave you:

SELECT JSON_EXTRACT(duckdb_status(), '$.readers') AS readers;

["core_functions", "httpfs", "json", "parquet"]

httpfs is the one that makes object storage work. It handles both s3:// and https:// paths.

Querying a remote file

Point duckdb_scalar() at a public 127 MB Parquet file, with no credentials and nothing copied onto your server, and you get an answer back:

SELECT duckdb_scalar('SELECT count(*) FROM read_parquet(''https://blobs.duckdb.org/data/taxi_2019_04.parquet'')');

7433139

A Parquet file records its own row count in a footer. DuckDB fetches that footer over HTTP and reads the count straight out of it, so it never reads the trip data at all.

A query that reads real column values has to pull those columns across the network first, so it takes longer than a count does. vsql_duckdb.timeout_ms bounds how long the calling connection waits, and it defaults to 30 seconds. A query that runs past it stops with an error. You can raise the limit, and you can also cap how much memory DuckDB takes, how many worker threads it starts, and how large a result one call may return. The README lists every setting with its default and range.

Pointing it at your own bucket

Reading a private bucket takes a region, an access key id, and somewhere to keep the secret access key. No setting holds that secret. The extension reads it from the server's keyring through VEF's keyring capability, and the settings only name the entry to look for.

Reading is all it does there, on purpose. VEF can write a key as well, and a key written that way is unreadable from SQL by anyone, which is what a server credential wants. The catch is that extension functions cannot be granted per user, so a setter would let every user of the server replace the credential. The operator stores the key instead, which needs a keyring component loaded and the keyring_udf plugin:

SELECT keyring_key_store('duckdb_s3_secret', 'AES', 'the-secret-access-key');

SET PERSIST vsql_duckdb.s3_region = 'eu-north-1';
SET PERSIST vsql_duckdb.s3_key_id = 'AKIAEXAMPLE';
SET PERSIST vsql_duckdb.s3_secret_keyring_id = 'duckdb_s3_secret';
SET PERSIST vsql_duckdb.s3_secret_keyring_auth_id = 'root@localhost';

A key stored from SQL belongs to the account that stored it, so s3_secret_keyring_auth_id has to name that account in full user@host form. Call duckdb_status() afterwards and it tells you whether the credential loaded. Google Cloud Storage is configured through the same settings with an HMAC key, and the README covers it along with S3-compatible stores like MinIO.

From there an s3:// path behaves exactly like the public URL above:

SELECT duckdb_scalar('SELECT count(*) FROM read_parquet(''s3://sales/2026/*.parquet'')');

Joining a dataset to a real table

DuckDB has no view of your InnoDB tables, and a query that names one fails in DuckDB's catalog rather than in MySQL. So you do the join in MySQL. duckdb_query hands back a JSON array, JSON_TABLE unpacks that array into rows, and those rows join against a real table like any others.

In the example below, the Parquet side is a synthetic sales dataset, 5 million rows over four files under /data/sales/ in the Hive layout that hive_partitioning = true reads. The files sit on the server's own disk, and the extension refuses local paths until you allow them:

SET PERSIST vsql_duckdb.allow_local_files = ON;

regions is an ordinary InnoDB table with one row per city, holding the region it sits in and the manager who owns it. DuckDB rolls up the files and MySQL joins the totals:

SELECT r.region, r.manager, t.orders, t.revenue
FROM JSON_TABLE(
  duckdb_query('SELECT city, count(*) AS orders, sum(amount) AS revenue
                FROM read_parquet(''/data/sales/**/*.parquet'', hive_partitioning = true)
                GROUP BY city'),
  '$[*]' COLUMNS (city    VARCHAR(64) PATH '$.city',
                  orders  BIGINT      PATH '$.orders',
                  revenue BIGINT      PATH '$.revenue')) AS t
JOIN regions r ON r.city = t.city
ORDER BY t.revenue DESC;

+--------+---------+---------+----------+
| region | manager | orders  | revenue  |
+--------+---------+---------+----------+
| east   | ana     | 1250000 | 61249754 |
| west   | dia     | 1250000 | 61249734 |
| west   | ben     | 1250000 | 61249715 |
| north  | cai     | 1250000 | 61249676 |
+--------+---------+---------+----------+

Only four grouped rows cross between the two engines, because DuckDB does the counting and summing before it hands anything over. Keep its side of the work to counts, sums, and rollups, and the JSON string stays small. Ask it for raw rows instead and the array grows until it reaches the one megabyte result cap.

What it does not do yet

This is an initial version of the duckdb extension for VillageSQL Server. You always write the DuckDB query yourself. There is no CREATE FOREIGN TABLE that makes a Parquet file look like a MySQL table, no pushdown of a MySQL WHERE clause into DuckDB, and no routing of ordinary SQL to DuckDB, so every call is an explicit duckdb_query('...'). DuckDB cannot read your InnoDB tables either, which is why the join belongs in the outer query. A result larger than one megabyte raises an error rather than coming back cut short, because a truncated JSON array loses its closing bracket and stops parsing. Four of DuckDB's components are built in today: parquet, json, httpfs, and core_functions.

There are alternative approaches to connecting MySQL and DuckDB too. DuckDB's own mysql extension will ATTACH your database and join an InnoDB table to a Parquet file in one query. dbtrail reads the binlog and archives it as Parquet for DuckDB to query. Alibaba's AliSQL embeds DuckDB in mysqld as a pluggable storage engine, where ALTER TABLE ... ENGINE=DuckDB converts an existing table to columnar storage.

The first two put a Parquet file within reach, but you have to query from somewhere other than your database. You connect to DuckDB, or you query what a pipeline already copied out. AliSQL does run inside MySQL, but it only stores tables you have already loaded, so a Parquet file sitting in a bucket stays out of reach. In none of the three can an application connected to your MySQL server ask that Parquet file a question.

The settings reference, the pg_duckdb migration table, and the full limitations list are in the vsql-duckdb README. To get VillageSQL Server, start at villagesql.com.

Please let us know your feedback. You can find us on Discord or on GitHub Issues.