Compile a query

Ship a query as a small native program

Write a query once, in Mojo, and mark the values that change between runs with param(). marrow compile turns it into a native executable in which every parameter is a command-line flag, so one binary answers the same question for any file and any threshold, with no Python and no interpreter behind it.

Note

The code on this page is Mojo, compiled ahead of time. For queries built at run time from Python, see Lazy queries.

Why compile a query

  • The query is the program. Column types are fixed when the query is compiled, so col("amount", int64) >= param("min-amount", int64) fuses into a single loop, and the executable links the kernels this query uses and nothing else. The example below is 3.0 MB of machine code.
  • Far smaller than an interpreter. The same GROUP BY compiled this way is 1.45 MB of machine code; resolving its aggregates by name at run time, as a dynamic frontend must, takes 13.2 MB, 9.1× as much. See why the compiled binary is small.
  • Values change, code does not. Parameters are read when the program starts, so a daily report is one binary run against a different file and threshold each day.
  • A command line for free. --help, --describe, output formats and error messages come from the query itself; there is no argument parsing to write.
  • Easy to ship. --bundle gathers the executable and the libraries it loads into one directory that runs without pixi or a marrow install.

When to use it

Compile a query whose shape is settled and that runs many times: a report, an export, a filter in a data pipeline. A query you are still shaping belongs in Python, where changing it is a line rather than a rebuild — a build takes a minute or two, because the whole library is compiled at -O3 each time.

Write the query

benchmarks/binary_size/query_cli.mojo
"""The compile guide's example, and the `query_cli` binary-size gate."""

from marrow.dtypes import field, int64, string
from marrow.expr import DynRelation, QueryCli, col, param, scan
from marrow.schema import schema


def query() raises -> DynRelation:
    var orders = scan(
        param("src", string),
        schema(
            [field("id", int64), field("amount", int64), field("name", string)]
        ),
    )
    return orders.filter(col("amount", int64) >= param("min-amount", int64))


def main() raises:
    QueryCli(query()).run()
  • query() builds the plan. It is a function because Mojo has no module-level variables.
  • param(name, dtype) is a value supplied at run time, and the input file is one too: scan(param("src", string), ...).
  • QueryCli(query()).run() is the whole program: every param in the plan becomes a --name flag.

Build it

$ marrow compile query_cli.mojo -o orders

In a marrow checkout, where the marrow script is not installed, run the same command as pixi run python -P python/marrow/compile.py compile query_cli.mojo -o orders. Add --fast for a quicker -O1 build of a slower program, and -v to see the underlying mojo build command.

Run it

The flags come from the plan, typed by the parameters’ dtypes:

$ ./orders --help
usage: ./orders [-h] --src string --min-amount int64 [--describe] [--output PATH] [--format FMT] [--max-rows N]

options:
  -h, --help              show this help message
      --src string        (required)
      --min-amount int64  (required)
      --describe          print the query plan and exit
  -o, --output PATH       write results here (default: stdout)
      --format FMT        output format: table | csv | parquet | ipc (default: inferred from --output's extension, else table)
      --max-rows N        rows to print for `table` output, 0 for all (default: 20)

Against a seven-row orders.parquet whose amount column is [100, 250, 90, 1000, 250, null, 42]:

$ ./orders --src orders.parquet --min-amount 250
id  amount  name
--  ------  ----
2   250     bob
4   1000    dave
5   250     erin
(3 rows)

--describe prints the plan baked into the binary, and needs no other arguments:

$ ./orders --describe
Filter(ParquetScan(param(src)), greater_equal(col(amount), param(min-amount)))

A mistake on the command line prints the usage and exits with status 2; a failure while running exits with status 1. Both go to stderr:

$ ./orders --src orders.parquet --min-amount abc
usage: ./orders [-h] --src string --min-amount int64 [--describe] [--output PATH] [--format FMT] [--max-rows N]
./orders: error: --min-amount: expected int64, got 'abc'

Output

The default is an aligned table, capped at --max-rows. --format csv writes RFC 4180 CSV and is never truncated, so it is safe to pipe:

$ ./orders --src orders.parquet --min-amount 250 --format csv
id,amount,name
2,250,bob
4,1000,dave
5,250,erin

-o PATH writes to a file and picks the format from its extension: .csv, .parquet or .pq, .arrow or .ipc. Parquet and Arrow IPC output are opt-in, because their writers add about 700 KB of machine code that a program which only prints should not carry:

QueryCli(query()).run[parquet=True, ipc=True]()

Parameters

A parameter is required unless it has a default, and help= gives it a line in --help; description= sets the program’s own:

param("min-amount", int64, default=Int64(0), help="keep orders at or above this")
QueryCli(query(), description="Orders at or above a minimum amount.").run()
dtype on the command line
integers 250, -3 — refused if the type cannot hold the value
floats 0.25, 1e-3
bool true, false, 1, 0
string, large_string any text

Parameters of other types appear in --help but cannot be set from the command line yet, so they need a default. A name used twice in a plan is one parameter and one flag.

Skip what the filter rules out

Optimize the plan with ScanPruning and the scan reads only the row groups and pages whose statistics could hold a matching row — thresholds from the command line included:

QueryCli(query().optimize[ScanPruning]()).run()
$ ./orders --describe
Filter(ParquetScan(param(src)) pruned by 1, greater_equal(col(amount), param(min-amount)))

It links the optimizer, which adds about 1.5 MB of machine code to this example, so it pays off for large files rather than small ones.

Ship it

A compiled executable loads the Mojo runtime libraries from the environment it was built in. --bundle DIR copies the executable, those libraries and marrow’s Parquet compression codecs into one directory:

$ marrow compile query_cli.mojo -o orders --bundle dist
$ zip -ry orders.zip dist        # -y keeps the bundle's symlinks

dist/orders then runs from any directory with no pixi environment and no marrow install; copy the directory to a machine with the same OS and CPU architecture. The object-store backend is not bundled, so a bundle reads local files.

Limits worth knowing

  • The plan’s shape is fixed at compile time; only parameter values change per run.
  • Column names are checked when the program runs, not when it compiles.
  • There is no incremental build: every compile takes a minute or two.
  • A bundle is checked on the machine that built it. The brotli codec’s own dependency is found the way it was at build time, so brotli-compressed files are the least certain case elsewhere.
  • marrow compile has no -D passthrough, so a GPU build (-D MARROW_GPU=true) needs mojo build directly.
Back to top