QueryDuckDB

Overview

QueryDuckDB executes Query.jl pipelines with DuckDB: inserting @duckdb() into a query pipeline translates the downstream query operators to SQL and runs them inside DuckDB.

Getting started

using QueryDuckDB, Query, DataFrames

df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3, 5, 2])

result = df |>
    @duckdb() |>
    @filter(_.age > 30 && _.children > 2) |>
    @map({_.name, _.age}) |>
    DataFrame

The whole pipeline downstream of @duckdb() is compiled to a single parameterized SQL statement and executed by an in-process DuckDB database.

File sources

When a pipeline starts from a file loaded with FileIO-style load, QueryDuckDB pushes the file reading itself into DuckDB where possible, so the data is never materialized on the Julia side:

using QueryDuckDB, Query, CSVFiles, DataFrames

result = load("data.csv") |>
    @duckdb() |>
    @filter(_.age > 30) |>
    DataFrame

Package	DuckDB reader
CSVFiles.jl	read_csv
ParquetFiles.jl	read_parquet
ExcelFiles.jl	read_xlsx (not on Windows, see below)
FeatherFiles.jl	none — Julia-side fallback

Any other iterable table (a DataFrame, a file type not listed above, or a file with options DuckDB cannot express) is read on the Julia side and registered with DuckDB as a table — queries always work, push-down is an optimization.

Inspecting query plans

@duckdbplan() terminates a pipeline and returns the generated SQL instead of executing it:

julia> df |> @duckdb() |> @filter(_.age > 30 && _.children > 2) |> @map({_.name, _.age}) |> @duckdbplan()
DuckDB Query Plan
─────────────────
SQL:
  SELECT "name" AS "name", "age" AS "age" FROM "source_tbl" WHERE (("age" > $1) AND ("children" > $2))

Parameters: Any[30, 2]

With explain=true it also runs DuckDB's EXPLAIN to show the physical execution plan:

julia> df |> @duckdb() |> @filter(_.age > 30) |> @duckdbplan(explain=true)
DuckDB Query Plan
─────────────────
SQL:
  SELECT * FROM "source_tbl" WHERE ("age" > $1)

Parameters: Any[30]

Physical Plan:
  ┌───────────────────────────┐
  │           FILTER          │
  │    ────────────────────   │
  │        (age > 30.0)       │
  │                           │
  │           ~1 row          │
  └─────────────┬─────────────┘
  ┌─────────────┴─────────────┐
  │       JULIA_TBL_SCAN      │
  │            ...            │
  └───────────────────────────┘

@queryplan() shows the backend-independent operation tree instead.

Supported operators

Query.jl	SQL
@filter	WHERE (or HAVING after @groupby)
@map, @select, @rename, @mutate	SELECT column list
@orderby, @orderby_descending, @thenby, @thenby_descending	ORDER BY
@take, @drop	LIMIT, OFFSET
@unique()	SELECT DISTINCT
@unique(_.col), @unique({_.a, _.b})	SELECT DISTINCT ON (...)
@groupby	GROUP BY; use aggregations and key(_) in the following @map
@join	INNER JOIN
@left_join, @right_join, @full_join	LEFT/RIGHT/FULL OUTER JOIN
@concat, @union, @except, @intersect	UNION ALL, UNION, EXCEPT, INTERSECT
@union_by, @except_by, @intersect_by	DISTINCT ON with IN/NOT IN
@order, @order_descending	ORDER BY ALL
@shuffle()	ORDER BY random()
@take_last, @drop_last	QUALIFY ROW_NUMBER() OVER () against COUNT(*) OVER ()
@count_by	GROUP BY with COUNT(*)
@count, @any, @all	COUNT(*), EXISTS, NOT EXISTS
@first, @element_at	LIMIT, LIMIT ... OFFSET
@min_by, @max_by	ORDER BY ... LIMIT 1

The second operand of a join or set operation must be a DuckDB source too, so write df1 |> @duckdb() |> @union(df2 |> @duckdb()).

Terminal operators that are not in the table above — @last, @single, @contains, @aggregate, @sequence_equal — still work: they materialize the query and run the in-memory implementation.

Common Julia functions are translated to their SQL equivalents (uppercase, lowercase, strip, replace, startswith, occursin, abs, round, floor, ceil, coalesce, ismissing, in, …), as are the aggregations sum, mean, minimum, maximum, length and count().

Known limitations

• Operators with no SQL equivalent throw a TranslationError naming the operator and suggesting a way forward, usually materializing the query first: @groupjoin, @mapmany, @chunk, @aggregate_by, @take_while, @drop_while, @reverse, @index, @append, @prepend, @zip, @of_type, @cast, @summarize, @pivot_longer and @pivot_wider.
• Set operations return rows in whatever order DuckDB produces, while the in-memory implementation preserves first-seen order.
• @shuffle accepts no rng argument here, because the shuffling is done by DuckDB's own random number generator.
• A @groupby must be followed by a @map with aggregations; the group elements cannot be materialized as arrays. Three-argument @groupby requires a plain column key.
• @unique with a key selector maps to DuckDB's DISTINCT ON, which keeps an arbitrary row per key unless the input is ordered — Query.jl's in-memory implementation keeps the first occurrence.
• 32-bit platforms are not supported. The x86 libduckdb crashes with an access violation inside duckdb_column_name, and DuckDB.jl only defines get_parameter(::BindInfo, ::Int64), which stops matching once Int is Int32. CI therefore does not build the x86 legs.
• Excel push-down is disabled on Windows: loading DuckDB's excel extension crashes with the mingw libduckdb build that DuckDB_jll ships there. Excel files fall back to Julia-side reading (see QueryDuckDB.duckdb_excel_supported).
• Feather files are always read on the Julia side: DuckDB's read_arrow lives in a non-bundled community extension, and FeatherFiles writes the legacy Feather v1 format.