Search

VirtualMetric Search is a lightweight search engine for federated search and edge search. The search executable is a single binary. It needs no server, daemon, cluster, JVM or cgo, and no setup step. It runs SQL directly over Parquet, CSV and NDJSON files where they are stored, and has an interactive shell. It can also run as an optional HTTP server of queries, search --serve, described in HTTP Server.

search "SELECT service, count(*) FROM 'logs/*.parquet' GROUP BY 1"

The same engine runs inside Directors and Agents, where the Enrich processor uses it. What the engine does differently there is listed in In Directors and Agents.

note

This page covers the search CLI and its SQL. The console's search page, which uses KQL, is covered under Federated Search.

Synopsis

search [OPTIONS] "<sql>"
search [OPTIONS] -c "<sql>"
search [OPTIONS] -f file.sql
search [OPTIONS]                          # interactive shell; reads a script from stdin when piped
search index "<glob>" --cols c1,c2        # full-text indexes
search index "<glob>" --keys k1,k2        # codes indexes of text key columns
search index "<glob>" --vectors emb       # vector indexes (HNSW)
search watch --dir DIR [--state-dir DIR] [--interval 1s] [--all] "<sql>"
search --serve [ADDR] [OPTIONS]           # HTTP server of queries

search --help prints the options and a summary of the SQL.

Capabilities

Capability	Description
Cold search	SQL directly over Parquet, CSV and NDJSON (also .gz / .zst). Row groups that the statistics rule out are not read
Remote storage	Files in Amazon S3, Azure Blob Storage and on HTTPS servers, queried where they are stored
Hot search	Load a file or a query into memory once, then query it repeatedly
Federated search	Join Parquet, CSV and NDJSON in one statement
PostgreSQL syntax	CTEs, window functions, joins, FILTER, :: casts, ILIKE, DATE and INTERVAL literals, DESCRIBE
SQLite dialect	--dialect sqlite, the semantics of the Enrich processor
Full-text search	@@, match(), phrases, prefixes, fuzzy terms and BM25 ranking, with optional sidecar indexes
Vector search	Vector search over embeddings stored in Parquet or NDJSON, or created from text at query time (embed()), with pgvector's distance operators, an optional HNSW index and hybrid search
Log patterns	patterns() groups log messages by their pattern, such as <*> failed to login, with counts
Incremental windows	search watch folds new files into hopping and tumbling windows as they arrive
Output	Tables for reading; CSV, TSV, JSON, JSONL, TOON and Markdown for other programs; COPY … TO writes CSV, JSON or Parquet files, and MERGE … TO merges Parquet files into one
Edge-friendly	Pure Go and a single file. A desktop mode limits CPU and memory use on shared machines

Options

Resources

Option	Default	Description
--mode server	-	The default: every core and up to 90% of the available memory
--mode desktop	-	A quarter of the cores and at most 512 MB, for shared workstations
--threads N	-	Worker threads
--max-memory SIZE	-	The memory budget (512MB, 4GB); beyond it, aggregations spill to disk
--spill-dir DIR	$TMPDIR/vmsearch-spill	Where aggregations spill
--cache-dir DIR	The user cache directory	Where remote CSV and JSON sources are kept

Output

Option	Default	Description
--output F, -o F	table	The output format; see Output. --format F is accepted too
--max-rows N	40	Rows a table shows; 0 for all
--max-width N	The terminal's width	Columns a table may take
--stats, --no-stats	-	Print or suppress the statistics line
--quiet	-	Print results only

SQL

Option	Default	Description
--dialect D	default	default (PostgreSQL) or sqlite (the Enrich processor's semantics)
--table NAME=FILE	-	Register a file as a table. CSV columns load as text, as the Enrich processor loads lookup tables
--no-index	-	Ignore sidecar indexes
--model DIR	$VMSEARCH_MODEL	The static embedding model embed() runs (Model2Vec format)
--ef-search N	128	The candidates a vector index search considers
-c "<sql>"	-	Run the statements given
-f file.sql	-	Run the statements in a file

Indexing

These options apply to search index.

Option	Default	Description
--cols c1,c2	-	Build full-text indexes of the columns
--keys k1,k2	-	Build codes indexes of text key columns
--vectors emb	-	Build vector indexes (HNSW) of the columns
--metric cosine|l2|ip	cosine	The metric a vector index serves
--hnsw-m N	16	The links each node of a vector index takes, 2 to 1024
--ef-construction N	200	The candidates considered while a vector index is built

Incremental Windows

These options apply to search watch.

Option	Default	Description
--dir DIR	-	The directory new files arrive in. Required
--state-dir DIR	<dir>-state	Where the per-window partials are kept
--interval D	1s	How often the directory is checked
--all	-	Print every window, changed or not

Server

These options apply to search --serve; see HTTP Server.

Option	Default	Description
--serve [ADDR]	127.0.0.1:8080	Serve queries over HTTP on ADDR: a port, a host or host:port. --serve=ADDR is accepted too
--address HOST	127.0.0.1	The address to listen on; 0.0.0.0 for every network interface
--port N	8080	The port to listen on
--token T	$VMSEARCH_TOKEN, else one the server makes	The token every query sends, as Authorization: Bearer T
--tls-cert FILE, --tls-key FILE	-	Serve HTTPS, with TLS 1.2 or later. Give both or neither
--allow-write	-	Let COPY … TO, MERGE … TO and EXPORT DATABASE write files
--allow-private	-	Let a query read a remote source on a loopback or private network address
--cache-memory SIZE	A quarter of the memory available	The memory the scan cache may use; 0 turns it off. See Scan Cache

Sample Data

The examples below use three days of web-service logs, one Parquet file per day (129,600 rows), a CSV file of service owners, an audit trail in gzipped NDJSON, and ten short IT knowledge-base articles in NDJSON:

logs/2026-10-01.parquet   logs/2026-10-02.parquet   logs/2026-10-03.parquet
services.csv              audit/2026-10.json.gz      kb.json

Describing Sources

DESCRIBE lists the columns of a file, a glob or a query, with their types. It reads file footers only, not rows:

search "DESCRIBE 'logs/*.parquet'"

┌─────────────┬─────────────┐
│ column_name │ column_type │
│ varchar     │ varchar     │
├─────────────┼─────────────┤
│ ts          │ TIMESTAMP   │
│ service     │ VARCHAR     │
│ level       │ VARCHAR     │
│ status      │ BIGINT      │
│ latency_ms  │ DOUBLE      │
│ message     │ VARCHAR     │
│ user_id     │ VARCHAR     │
│ client_ip   │ VARCHAR     │
└─────────────┴─────────────┘
8 rows · 2 columns · Run Time 293.8 µs · 0 of 3 files

DESC and DESCRIBE TABLE are equivalent. DESCRIBE also accepts a query, which shows the columns a statement returns before it is run: DESCRIBE SELECT service, avg(latency_ms) FROM 'logs/*.parquet' GROUP BY 1.

Cold Search

Files are queried where they are stored. Nothing is imported first.

search "SELECT service, count(*) AS requests, round(avg(latency_ms), 1) AS avg_ms
        FROM 'logs/*.parquet' GROUP BY service ORDER BY requests DESC, service"

┌─────────┬──────────┬────────┐
│ service │ requests │ avg_ms │
│ varchar │ int64    │ double │
├─────────┼──────────┼────────┤
│ api     │    25920 │  104.3 │
│ auth    │    25920 │  104.7 │
│ billing │    25920 │    105 │
│ search  │    25920 │  104.4 │
│ web     │    25920 │  104.9 │
└─────────┴──────────┴────────┘
5 rows · 3 columns · Run Time 2.4 ms · read 259,205 rows · 296.5KB (118.3MB/s) · 3 of 3 files · 15 row groups (0 skipped)

The last line is the statistics line: what was read and what was skipped. A filter on a column for which the files keep statistics skips whole row groups:

search "SELECT count(*) FROM 'logs/*.parquet' WHERE ts >= '2026-10-03 18:00:00'"

… · 3 of 3 files · 15 row groups (13 skipped)

A query without ORDER BY returns rows in the order of its source: the files in name order, and each file's rows in the order they are stored. LIMIT without ORDER BY returns the first rows in that order.

Sources:

• FROM 'path/*.parquet', 'file.csv', 'file.json', 'file.ndjson.gz', with one or more comma-separated globs
• read_parquet('glob'), read_csv('glob'), read_json('glob'); read_csv takes the options in CSV Files, and read_json those in JSON Files
• a bare name, which finds name.parquet or the Parquet files in name/
• a URL of Amazon S3, Azure Blob Storage or an HTTPS server (see Remote Storage)

CSV Files

A CSV file's delimiter and header are sniffed from its first lines, and its column types from a sample of its rows (see Column Types), as DuckDB sniffs them. So are the formats of dates and timestamps not written in ISO form: 15/03/2024 reads as a DATE of %d/%m/%Y. An ambiguous date such as 03/04/2024 is read day first unless a later value in the file rules that out. sniff_csv('file.csv') shows what was sniffed, in DuckDB's columns, including the DateFormat and TimestampFormat it chose.

read_csv takes the options of DuckDB 1.5.6, with DuckDB's meanings. These change how a file is read:

Option	Description
comment = '#'	A line starting with it is skipped, and a trailing comment is cut from a line. Inside a quoted field it is text. skip and the header do not count comment lines
null_padding = true	A row with fewer fields than the columns is padded with NULLs
ignore_errors = true	A row with a different number of fields, or with a value that does not convert to its column's type, is skipped
strict_mode = false	A row's extra fields are dropped
quote, escape	Any single character, for example quote = '''' or escape = '\'
delim, sep	Up to four characters, for example delim = '||'
dateformat, timestampformat	strptime formats for DATE and TIMESTAMP columns, for example '%d/%m/%Y'
decimal_separator = ',', thousands = '.'	1.234,5 reads as 1234.5
normalize_names = true	Header names become lower-case identifiers, the way DuckDB makes them: accents are removed, a run of blanks becomes one underscore, and a name that starts with a digit or is a SQL keyword gets a leading underscore (_3rd, _select)
max_line_size	A longer line is an error
encoding	utf-8 (the default), utf-16 (either byte order, by its mark) or latin-1
auto_type_candidates	The types the sniffer may choose, for example ['BIGINT', 'VARCHAR']
force_not_null = ['col']	NULL text in these columns reads as an empty string
allow_quoted_nulls = false	A quoted NULL text ("NULL" under nullstr = 'NULL') is text
sample_size	The rows the column types are sniffed from (see Column Types), or -1 for every row
compression	auto (by the file's extension, the default), none or uncompressed, gzip, or zstd
parallel, buffer_size, files_to_sniff	Accepted; they change nothing

SELECT * FROM read_csv('exports/*.csv', delim = ';', decimal_separator = ',', thousands = '.', dateformat = '%d/%m/%Y');

• A comment character is recognized only when comment names one. DuckDB also guesses #; Search does not.
• The rejects tables are not supported: store_rejects and rejects_table are refused.

Column Types

Column types are sniffed from DuckDB's sample: 20,480 records by default, taken in whole chunks of 2,048 that count the header. sample_size sets more, or -1 for every record. Earlier versions sniffed the first 40 rows.

A column's first non-NULL value takes the first of these types that reads it: BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE. A later value that the type does not read widens a BIGINT to a DOUBLE, or a TIMESTAMP to a TIMESTAMP WITH TIME ZONE. Any other mismatch makes the column VARCHAR.

Values	Type
true, false, t, f, yes, no	BOOLEAN
10:00:00, 10:00, 1:2:3	TIME
2024-01-01T10:00:00+02:00	TIMESTAMP WITH TIME ZONE (08:00 UTC)
007	VARCHAR: the leading zero is kept, as DuckDB keeps it
+5	VARCHAR
0x10	BIGINT, 16
99999999999999999999	DOUBLE
2024-01-01, then 5	VARCHAR

A value past the sample that does not read as its column's type is an error that names the value, its column and the type. ignore_errors = true skips the row instead, and sample_size = -1 types the column from every row.

columns and types accept BOOLEAN, TIME and TIMESTAMPTZ. A TIMESTAMPTZ value without an offset is read in the session's time zone (SET TimeZone).

SELECT * FROM read_csv('readings.csv', columns = {'sensor': 'VARCHAR', 'ok': 'BOOLEAN', 'at': 'TIMESTAMPTZ'});

Upgrading

Earlier versions read a value past the sniffed rows that did not fit its column's type as NULL, with no error. It is now an error unless ignore_errors = true.

The SQLite dialect is unchanged: it reads such a value as NULL, and types no column BOOLEAN, TIME or TIMESTAMP WITH TIME ZONE.

Empty Fields and NULL

The NULL rules match DuckDB's:

• An empty field is NULL, whether quoted ("") or not.
• With nullstr set, an unquoted empty field is an empty string, and only the nullstr texts are NULL.
• A quoted number in a numeric column reads as its number.
• A UTF-8 byte order mark at the start of a file is skipped.
• A headerless file's columns are numbered to the width of the last: column00 … column11 for twelve columns.

An empty string that EXPORT DATABASE writes to CSV therefore reads back as NULL, as it does in DuckDB. A Parquet export keeps the empty string.

Upgrading

Earlier versions read a quoted empty field ("") as an empty string and a quoted number in a numeric column as NULL, and kept a UTF-8 byte order mark in the first column's name. Queries over CSV files that relied on any of these return different results after the upgrade.

The SQLite dialect, which the Enrich processor uses, keeps an empty field, quoted or not, as an empty string; a read_csv call in an enrich query takes the options above. The Enrich processor's lookup tables are read by a loader of their own and are unaffected.

JSON Files

Each line of an NDJSON file is one document, and the documents' paths are its columns (see JSON Columns). read_json takes these options, with DuckDB's meanings:

Option	Description
columns = {'a': 'BIGINT'}	The columns to read and their types
ignore_errors = true	A malformed document reads as a row of NULLs, and a value that does not fit its column's type as NULL
sample_size, maximum_sample_files	The documents (default 20,480) and the files (default 32) the column types are sniffed from; -1 for all of them
dateformat, timestampformat	strptime formats for strings to read as DATE and TIMESTAMP values, for example '%d/%m/%Y'
convert_strings_to_integers = true	Strings of integers are typed BIGINT
compression	auto_detect (the default), uncompressed, gzip or zstd. none is refused, as in DuckDB
hive_partitioning, hive_types	As in Hive Partitions
maximum_object_size, field_appearance_threshold	Accepted; they change nothing

Malformed Documents

A line holding null is a row of NULLs. Any other line is malformed when it is not exactly one JSON object followed by nothing but blanks. That covers a document cut short, a second object or a word after the first, a bad literal, a key without a value or without a colon, a missing comma, and an invalid escape. A malformed line is an error that quotes the line; with ignore_errors = true it reads as a row of NULLs instead.

Earlier versions read a document cut short together with the next line as one row of wrong values, and a line that was not an object silently dropped the lines after it.

Documents are read as DuckDB reads them. Trailing commas are accepted in objects and arrays. NaN, Inf and Infinity, in any case and with or without a minus, are DOUBLE values, and a number past a double's range (1e999) reads as infinity.

Sample and Errors

Column types are sniffed from DuckDB's sample: 20,480 documents (sample_size, or -1 for every document) from the first 32 files (maximum_sample_files). Earlier versions sniffed 200 documents. A value past the sample that does not fit its column's type is an error; ignore_errors = true reads it as NULL and keeps the row. A number read into a BIGINT column is rounded half to even, and a boolean reads as 1 or 0.

Dates and Times

A string holding an ISO 8601 date, timestamp or time is typed DATE, TIMESTAMP or TIME. A timestamp with an offset is converted to UTC. As in DuckDB, a column settles on one timestamp format, so timestamps written in two different ISO forms (2024-01-01T10:00:00Z and 2024-01-01T10:00:00.5Z) leave the column VARCHAR. Strings in other date forms (15/03/2024) stay text unless dateformat or timestampformat gives their format:

SELECT * FROM read_json('events/*.ndjson', dateformat = '%d/%m/%Y');

Differences from DuckDB

• Control characters and bytes that are not UTF-8 inside a string are read as they are; DuckDB refuses the document.
• With ignore_errors = true, a document cut short is one NULL row, and the next line still reads.
• Parts of a document that the query does not read are not validated.

Upgrading

• A malformed NDJSON file that used to read without an error now fails, unless ignore_errors = true.
• A value that does not fit its sniffed type is now an error, in JSON files as in CSV files. It used to be NULL.
• ISO date and time strings in JSON files are now DATE, TIMESTAMP or TIME columns, where they were VARCHAR. This changes how they compare and which functions they can be passed to.

The SQLite dialect, which the Enrich processor uses, is unchanged: a malformed document and a value that does not fit its type read as NULL, and no string is typed as a date or a time.

Hive Partitions

Parquet, CSV and JSON files in directories named key=value (the layout that Hive, Spark and COPY … PARTITION_BY write) read each key as a column, after the files' own columns. Over sales/year=2026/region=eu/data.parquet, FROM 'sales/*/*/*.parquet' has year and region columns, and over logs/day=1/a.csv, FROM 'logs/*/*.csv' has a day column.

• A key is a DATE, a TIMESTAMP or an integer when each of its values reads as one, and text otherwise.
• Its values are percent-decoded, and NULL or __HIVE_DEFAULT_PARTITION__ reads as NULL.
• hive_types sets a key's type, for Parquet, CSV and JSON files: read_csv('logs/*/*.csv', hive_types = {'day': VARCHAR}). Integer, date, timestamp and text types are supported.
• A filter on a key skips the row groups of a Parquet file's other partitions. Over CSV files it filters rows; every file is still read.
• Keys are read when every file names the same ones. read_parquet('glob', hive_partitioning = false) reads none, hive_partitioning = true requires them, and hive_types_autocast = false reads every key as text.
• One statement reads a file's keys one way: reading the same files with and without their keys is an error.

Remote Storage

Files in Amazon S3, in stores that implement its API, in Azure Blob Storage and on HTTPS servers are queried where they are stored, as local files are:

search "SELECT service, count(*) FROM 's3://logs/2026/10/*.parquet' GROUP BY service"
search "SELECT count(*) FROM 'az://account.blob.core.windows.net/logs/2026/*.parquet'"
search "SELECT * FROM 'https://example.com/data/services.csv'"

URL	Storage
s3://bucket/key (s3a:// too)	Amazon S3, or a store that implements its API at AWS_ENDPOINT_URL_S3 or AWS_ENDPOINT_URL
az://container/path	Azure Blob Storage, the account named by AZURE_STORAGE_ACCOUNT or the connection string
az://account.blob.core.windows.net/container/path, abfss://container@account.dfs.core.windows.net/path, https://account.blob.core.windows.net/container/path	Azure Blob Storage
https://host/path	An HTTPS server

A glob in an S3 or Azure path lists the matching objects. An HTTPS server is not listed, so its files are named one by one.

A Parquet file is read in ranges: its footer, then the column chunks the query reads. Row groups that statistics rule out, and columns the query does not use, are not downloaded. CSV and JSON sources are downloaded whole into a local cache (--cache-dir, the user cache directory by default) and read from there.

Credentials are found as the clouds' own tools find them:

• Amazon S3, the first source that has them: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY (with AWS_SESSION_TOKEN); the profile AWS_PROFILE names in the shared files; a web identity's role (AWS_WEB_IDENTITY_TOKEN_FILE and AWS_ROLE_ARN, as EKS sets them); the default profile; a container's role (ECS, EKS Pod Identity); an EC2 instance's role through IMDSv2, unless AWS_EC2_METADATA_DISABLED is true. The region is AWS_REGION's, or AWS_DEFAULT_REGION's, or the profile's.
• Azure Blob Storage, the first of: an SAS token in the URL; AZURE_STORAGE_SAS_TOKEN; the account key, from AZURE_STORAGE_KEY or AZURE_STORAGE_CONNECTION_STRING; a Microsoft Entra ID token for a service principal (AZURE_TENANT_ID, AZURE_CLIENT_ID, and AZURE_CLIENT_SECRET or AZURE_CLIENT_CERTIFICATE_PATH), a workload identity (AZURE_FEDERATED_TOKEN_FILE) or a managed identity (App Service, Functions, Azure Arc, a virtual machine).

Sources on loopback and private addresses are allowed, such as a MinIO server on the local network. The HTTP server refuses them unless it runs with --allow-private. Link-local addresses, where cloud metadata endpoints are, are refused as sources. Error messages never quote a URL's query string, which may hold an SAS token.

note

Remote storage is available in the CLI only. The engine inside Directors and Agents reads local files and tables only.

Hot Search

For data that is queried repeatedly, load it into memory once. In the shell, or in a script, a table lives as long as the session:

CREATE TABLE errors AS SELECT * FROM 'logs/*.parquet' WHERE level = 'ERROR';
SELECT service, count(*) AS n FROM errors GROUP BY 1 ORDER BY 2 DESC LIMIT 2;

┌─────────┬───────┐
│ service │ n     │
│ varchar │ int64 │
├─────────┼───────┤
│ auth    │   268 │
│ api     │   268 │
└─────────┴───────┘

INSERT INTO, DROP TABLE and CREATE TEMP TABLE are supported, and .tables lists the loaded tables. A file can also be registered from the command line:

search --table owners=services.csv "SELECT * FROM owners WHERE tier = 'gold'"

Federated Search

One statement can read several formats:

search "SELECT s.owner, count(*) AS errors
        FROM 'logs/*.parquet' l JOIN 'services.csv' s USING (service)
        WHERE l.status >= 500
        GROUP BY s.owner ORDER BY errors DESC"

┌───────────────┬────────┐
│ owner         │ errors │
│ varchar       │ int64  │
├───────────────┼────────┤
│ team-core     │    535 │
│ team-security │    268 │
│ team-web      │    267 │
│ team-payments │    267 │
└───────────────┴────────┘

search "SELECT action, object, count(*) AS n FROM 'audit/*.json.gz' GROUP BY 1, 2 ORDER BY n DESC LIMIT 3"

JSON Columns

Nested NDJSON objects become dotted columns, queried as "request.method". An object is also a column of its own JSON, next to its flattened leaves (user, user.name), as is an array that does not hold only numbers, so a query can read values inside it. The -> and ->> operators and the JSON functions below read these columns, and JSON stored in a text column as well.

search "SELECT user->>'name' AS name, user->'geo'->>'city' AS city, tags->>0 AS first_tag,
               json_extract_string(payload, '$.status') AS status
        FROM 'events.json'"

┌────────┬────────┬───────────┬─────────┐
│ name   │ city   │ first_tag │ status  │
├────────┼────────┼───────────┼─────────┤
│ yusuf  │ Ankara │ login     │ 200     │
│ selman │ Izmir  │ login     │ 401     │
└────────┴────────┴───────────┴─────────┘

Function	Returns
json_extract_string(json, path)	Text: a string unescaped, a number or a boolean as written, an object or an array as its JSON, and NULL for a JSON null or a missing path
extractjson(path, json)	The same, with the arguments in Kusto's order
json_value(json, path)	The same, but scalar values only: an object or an array is NULL

These three functions are also available in the Enrich processor's SQL, on Directors and Agents.

JSON Type

The default dialect has DuckDB's JSON type. A cast to JSON gives a JSON value, as do the functions that return JSON (json_extract, ->, json_object, to_json and the others). A Parquet column of the JSON type reads as JSON too, including inside lists, structs and maps. A table, a CTE or a subquery keeps the type of a JSON column, and CREATE TABLE t (j JSON) refuses text that is not JSON.

• j.key, j['key'] and j[0] read into a JSON value. An index counts from 0, and from the end when it is negative. A missing key or element is NULL.
• A cast reads the value as its type: '"5"'::JSON::INT is 5, and a cast to STRUCT, MAP or a list reads the value as that type ('{"a": 1}'::JSON::STRUCT(a INT) is a struct). A cast to VARCHAR keeps the text.
• Text functions take its text: upper(j), j || 'x', length(j).
• A JSON value compares as its text, with the value beside it written as JSON, so j.status = 200 reads a JSON number.
• A list holding JSON values is a JSON[], which json_extract also returns for a wildcard path. Its elements print as JSON.
• COPY writes a JSON column as JSON to JSON files, and with the JSON type to Parquet.

CASE, coalesce and aggregates of JSON values return VARCHAR; cast the result back to JSON to read into it. ->> returns the value as an SQL value of its own type (a number as a number, a string as its text without quotes), where DuckDB's ->> always returns VARCHAR.

Upgrading

json_extract now returns JSON, as -> does, so a string keeps its quotes ("x") and a JSON null is null. This changes the results of existing queries that read text with it. json_extract_string and ->> still return a string's text without its quotes.

The JSON type belongs to the CLI's default dialect. The SQLite dialect, which the Enrich processor uses on Directors and Agents, has no JSON type: its JSON is text.

SQL Syntax

The default dialect follows PostgreSQL. It supports:

• CTEs (including WITH RECURSIVE), subqueries (including correlated subqueries), and UNION / INTERSECT / EXCEPT
• queries that begin with FROM (FROM 'logs/*.parquet' SELECT host WHERE status >= 500, or FROM t alone)
• inner, left, right, full, cross and natural joins
• LATERAL subqueries that read the FROM items before them through equalities in their WHERE, such as the latest rows of each key (LATERAL (SELECT * FROM m WHERE m.k = t.k ORDER BY ts DESC LIMIT 3))
• ASOF joins, which join each row with the closest row of its key, and POSITIONAL joins, which join rows by their position
• joins on inequalities, such as overlapping time ranges (a.start <= b.end AND a.end >= b.start)
• window functions with ROWS / RANGE / GROUPS frames, and FILTER (WHERE …) on aggregates
• QUALIFY, which filters rows by the values of window functions, DISTINCT ON (…), GROUP BY ALL and ORDER BY ALL
• the range(start, stop, step) and generate_series(start, stop, step) table functions (range leaves stop out, generate_series includes it)
• percentile_cont(f) WITHIN GROUP (ORDER BY x), percentile_disc(f) WITHIN GROUP (ORDER BY x) and median(x)
• mad(x), the median of the values' distances from their median, as in DuckDB: of numbers a DOUBLE (a NaN sorts after every number), of DECIMALs a value of the same DECIMAL type, and of dates, timestamps and times an INTERVAL (3 days 06:00:00). list_mad and mad() as a window function give the same results, except that they read a DECIMAL as a DOUBLE. Other types (VARCHAR, BOOLEAN, INTERVAL, JSON) are refused
• stddev_samp, stddev_pop, var_samp, var_pop, covar_samp, covar_pop and corr (stddev and variance are the sample ones)
• CASE, :: casts, ILIKE, regular expressions, JSON functions, date_trunc and date_diff
• DuckDB's hash(x, …) of one or more values of any type, a UBIGINT. For the same values and types it returns the same number as DuckDB 1.5.6's hash(), so a hash computed in Search can be compared with one computed in DuckDB, for example to bucket or sample rows the same way in both. hash() with no argument is an error, as in DuckDB. Search stores two types more widely than DuckDB, and their hashes differ:
  • A FLOAT is stored as a DOUBLE, so its hash is a DOUBLE's.
  • An integer column is a BIGINT, so a negative value in a column DuckDB would type INTEGER (for example from VALUES (-1)) hashes differently. Casting it to INTEGER (hash(x::INTEGER)) gives DuckDB's answer.
• format('{} of {}', a, b), strftime and strptime, url_encode / url_decode, html_escape / html_unescape, uuid() and mode()
• DuckDB's COLUMNS() of a lambda over the column names, written COLUMNS(lambda c: c LIKE 'a%') or with an arrow, COLUMNS(c -> c LIKE 'a%')
• bit_count() of an integer (BIGINT, HUGEINT or UBIGINT), the number of its set bits, as a BIGINT where DuckDB returns a TINYINT
• md5_number_upper() and md5_number_lower(), a UBIGINT of the first or the last 8 bytes of the MD5 digest, as in DuckDB. md5_number() is not available, since Search has no UHUGEINT type

WITH per_hour AS (
  SELECT date_trunc('hour', ts) AS hour,
         count(*) FILTER (WHERE status >= 500) AS errors
  FROM 'logs/*.parquet' GROUP BY 1
)
SELECT hour, errors,
       round(avg(errors) OVER (ORDER BY hour ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 1) AS moving_avg
FROM per_hour ORDER BY hour LIMIT 3;

Dates and Intervals

DATE '2026-10-03' and TIMESTAMP '2026-10-03 10:00:00' are typed literals. INTERVAL '1 day 2 hours', INTERVAL '30' MINUTE, INTERVAL 30 MINUTE and '90 minutes'::interval are intervals, and now() is the current time.

• A timestamp or a date plus or minus an interval is a timestamp. Months follow the calendar, so January 31 plus one month is the last day of February.
• A date plus or minus a number of days is a date.
• make_timestamp_ms(ms) is the timestamp of a number of milliseconds since 1970, as in DuckDB.

search "SELECT DATE '2026-10-03' + 7 AS next_week, TIMESTAMP '2026-01-31 09:00:00' + INTERVAL '1 month' AS month_end, INTERVAL '90 minutes' AS duration"

┌────────────┬─────────────────────┬──────────┐
│ next_week  │ month_end           │ duration │
│ date       │ timestamp           │ varchar  │
├────────────┼─────────────────────┼──────────┤
│ 2026-10-10 │ 2026-02-28 09:00:00 │ 01:30:00 │
└────────────┴─────────────────────┴──────────┘

Interval units are microseconds, milliseconds, seconds, minutes, hours, days, weeks, months and years, also abbreviated (ms, s, min, h, d). A filter on a literal date, timestamp or interval skips row groups by their statistics, as a filter on a timestamp string does:

search "SELECT count(*) FROM 'logs/*.parquet' WHERE ts >= TIMESTAMP '2026-10-03 18:00:00' - INTERVAL '2 hours'"

… · 3 of 3 files · 15 row groups (12 skipped)

A date compared with a timestamp is compared as its midnight. E'line one\nline two' is an escape string, as in PostgreSQL.

avg() of intervals, also as a window function, gives DuckDB's mean. Each part (months, days and microseconds) is averaged on its own: the months' remainder carries to the days at 30 days a month, and the days' remainder to the microseconds, so the average of 1 month, 2 days and 3 hours is 10 days 17:00:00. An average whose sum is beyond an interval's range is an error, as in DuckDB. sum() of intervals is refused, as in DuckDB.

A timestamp holds nanoseconds, so it reaches the years 1677 to 2262. make_timestamp(), make_timestamp_ms() and epoch_ms() of a number beyond those years are an error, rather than wrapping to a different timestamp.

These literals belong to the default dialect only; the SQLite dialect is unchanged.

Casts

In the default dialect, CAST and :: give NULL for a value that does not convert, never 0. TRY_CAST behaves the same way. This makes it possible to sum the numbers in a text column while other values are left out. The SQLite dialect casts as SQLite does: 'n/a' becomes 0.

search "SELECT CAST('42' AS BIGINT) AS a, CAST('n/a' AS BIGINT) AS b, TRY_CAST('3.7' AS INT) AS c, '7'::INT + 1 AS d"

┌───────┬───────┬───────┬───────┐
│ a     │ b     │ c     │ d     │
│ int64 │ int64 │ int64 │ int64 │
├───────┼───────┼───────┼───────┤
│    42 │  NULL │     4 │     8 │
└───────┴───────┴───────┴───────┘

SELECT max(CAST(replace(user_id, 'user-', '') AS BIGINT)) FROM 'logs/*.parquet';

Collations

In the default dialect, x COLLATE name changes how text is compared, as in DuckDB. The value itself is not changed.

Collation	Compares text
NOCASE	Ignoring the case of every letter, not only ASCII letters: 'É' COLLATE NOCASE = 'é' is true
NOACCENT	Ignoring accents: 'é' COLLATE NOACCENT = 'e' is true
NFC	In Unicode Normalization Form C
RTRIM	Ignoring trailing spaces, as in SQLite
BINARY, C, POSIX	As if no collation were given

Collations can be combined with dots, for example name COLLATE NOCASE.NOACCENT = 'jose'. Any other name is an error, as in DuckDB; DuckDB's ICU locale collations (COLLATE de) are not supported. A collation applies only to text values, and using one on a number is an error, as in DuckDB.

A collation applies to:

• comparisons (=, < and the others, and BETWEEN)
• IN
• a CASE's switch value (CASE x COLLATE NOCASE WHEN 'é' THEN …)
• ORDER BY, and the ORDER BY of a window
• join keys

GROUP BY, DISTINCT, LIKE and the arguments of aggregates ignore a collation.

SELECT x FROM (VALUES ('É'), ('e'), ('f')) t(x) WHERE x COLLATE NOACCENT.NOCASE = 'E' ORDER BY x;
-- e
-- É

Two functions give the same results as DuckDB's:

• strip_accents(text) removes accents: strip_accents('Ação') is Acao.
• nfc_normalize(text) returns the text in Normalization Form C.

The SQLite dialect, which the Enrich processor uses, keeps SQLite's collations: NOCASE, which ignores the case of ASCII letters only, RTRIM and BINARY.

SQLite Dialect

--dialect sqlite, or .dialect sqlite in the shell, follows SQLite 3.53's semantics:

• type affinity in comparisons
• case-insensitive LIKE
• integer division
• SQLite's built-in functions

The Enrich processor uses this dialect, and a reference corpus of SQLite's own answers verifies it.

Full-Text Search

Text columns can be searched without preparation:

search "SELECT ts, service, message FROM 'logs/*.parquet' WHERE message @@ 'timeout' ORDER BY ts LIMIT 3"

┌─────────────────────┬─────────┬────────────────────────────────────────────┐
│ ts                  │ service │ message                                    │
│ timestamp           │ varchar │ varchar                                    │
├─────────────────────┼─────────┼────────────────────────────────────────────┤
│ 2026-10-01 00:00:00 │ api     │ connection timeout to db-primary after 30s │
│ 2026-10-01 00:09:42 │ web     │ connection timeout to db-primary after 30s │
│ 2026-10-01 00:19:24 │ auth    │ connection timeout to db-primary after 30s │
└─────────────────────┴─────────┴────────────────────────────────────────────┘

Query	Matches
message @@ 'upstream gateway'	Any of the terms
match(message, 'card expired'), message MATCH 'card expired'	All of the terms
message @@ ts_phrase('bad gateway')	The phrase
message @@ ts_starts_with('certif')	A prefix
message @@ ts_levenshtein('timout', 1)	A term within one edit
message @@ ts_phrase('slow') ## 2 ## 'orders'	Two words apart ("slow query on orders"); ## [0,3] ## for up to three

Terms are matched as whole words, ignoring case. Combine them with SQL's AND, OR and NOT.

For large data sets, build an index once, beside each Parquet file. Queries use it automatically, and ignore it when the file changes:

search index 'logs/*.parquet' --cols message
search "SELECT ts, message, round(bm25(), 3) AS score
        FROM 'logs/*.parquet' WHERE message @@ 'upstream gateway'
        ORDER BY bm25() DESC LIMIT 3"

bm25() ranks rows by relevance and does not require an index. Over CSV, NDJSON, a table in memory or a Parquet file without an index, it reads the column twice, once to compute how rare each term is and once to score, and returns the same scores the index would. --no-index makes queries ignore indexes.

search index 'logs/*.parquet' --keys service builds a codes index of a text key column. Group-bys, filters and semi-joins on that column then read codes instead of text.

Vector Search

Vector search ranks rows by meaning rather than by shared words. In the sample knowledge base, the question "my inbox is full" finds the article on mailbox quotas, although the two share no words.

Embedding Text at Query Time

embed(text) turns text into a vector at query time, on the CPU. This requires an embedding model, which is optional and is loaded from a directory beside the binary, never built into it. The model is a static embedding model in Model2Vec format. potion-base-8M (MIT licence, about 31 MB) consists of three files from Hugging Face:

mkdir potion && cd potion
for f in config.json tokenizer.json model.safetensors; do
  curl -sSLO https://huggingface.co/minishlab/potion-base-8M/resolve/main/$f
done
export VMSEARCH_MODEL=$PWD     # or: search --model DIR

The model is needed only for embed(), for hybrid_search() over a text column, and for indexing a text column. Vector search over stored embeddings and every other feature work without it.

search "SELECT article, round(cosine_similarity(embed(body), embed('my inbox is full')), 3) AS score
        FROM 'kb.json' ORDER BY score DESC LIMIT 3"

┌────────────────────────┬────────┐
│ article                │ score  │
│ varchar                │ double │
├────────────────────────┼────────┤
│ mailbox-quota.md       │  0.333 │
│ password-reset.md      │  0.242 │
│ data-classification.md │  0.175 │
└────────────────────────┴────────┘

embed() can run over a whole column. A column of repeated values embeds each distinct value once. To find the log message closest in meaning to a description:

search "SELECT message FROM 'logs/*.parquet'
        GROUP BY message ORDER BY min(embed(message) <=> embed('database is unreachable')) LIMIT 1"

┌────────────────────────────────────────────┐
│ message                                    │
│ varchar                                    │
├────────────────────────────────────────────┤
│ connection timeout to db-primary after 30s │
└────────────────────────────────────────────┘

Stored Embeddings

A LIST<FLOAT> or LIST<DOUBLE> column in Parquet, as pyarrow, DuckDB and Spark write embeddings, is a vector column. So is a JSON array of numbers in NDJSON. DESCRIBE shows them as FLOAT[]:

SELECT id, title FROM 'docs/*.parquet' ORDER BY emb <=> embed('mailbox storage limit') LIMIT 10;
SELECT id FROM 'docs/*.parquet' WHERE emb <-> '[0.1, 0.2, 0.3]' < 0.5;      -- everything within a radius

A row's own vector can be the query, to find more like it:

SELECT id, title FROM 'docs/*.parquet'
ORDER BY emb <=> (SELECT emb FROM 'docs/*.parquet' WHERE id = 42) LIMIT 5;

Distances

Operator	Function	Meaning
a <-> b	l2_distance (array_distance)	Euclidean distance
a <=> b	cosine_distance	1 − cosine similarity
a <#> b	negative_inner_product	Negative inner (dot) product
a <+> b	l1_distance	Manhattan distance
-	cosine_similarity, inner_product	Similarities: higher is closer
-	l2_norm, l1_norm, l2_normalize, l1_normalize, vector_dims	Norms and dimensions
-	embed(text)	The text's vector under the --model model

Vectors are written [0.1, 0.2], '[0.1, 0.2]'::FLOAT[] or vector(384). A NULL vector gives a NULL distance, and two vectors of different lengths are an error. Vectors have no order, so =, ORDER BY emb and GROUP BY emb are rejected; order by a distance instead.

Vector Index

For large collections, build an HNSW index once, beside each Parquet file. It is written as one sidecar per file, named <file>.<col>.hnsw. A query for the nearest rows then reads only the rows the index finds:

search index 'docs/*.parquet' --vectors emb
search "SELECT id, title FROM 'docs/*.parquet' ORDER BY emb <=> embed('mailbox storage limit') LIMIT 10"

The statistics line shows vector index when an index answered. The index options are listed under Indexing; --ef-search at query time sets how many candidates a search considers, where more candidates find more of the exact nearest rows, more slowly.

• When the index answers. ORDER BY <distance to a constant vector> LIMIT k uses it. A filter that keeps fewer than k of the candidates runs the query again as an exact scan.
• Metrics. --metric l2 or --metric ip builds an index for <-> or <#>; cosine is the default. A query of another metric scans.
• Text columns. Indexing a text column embeds its values with --model, and that index answers ORDER BY embed(col) <=> embed('…').
• Stale or mismatched indexes. An index of an older version of the file, by size and modification time, is not read, and neither is one of another metric or, for a text column, of another model. The file is then read in full, as is any query the index cannot answer. Running search index again rebuilds such an index.
• NULL vectors. A row whose vector is NULL or empty is not in the index. An order that puts NULLs first (NULLS FIRST, or the SQLite dialect's ascending order) scans.

Hybrid Search

Rows are ranked by keywords and by meaning, and the two rankings are combined by reciprocal rank fusion. One call performs both:

SELECT article, bm25_rank, vector_rank, round(score, 4) AS score
FROM hybrid_search('kb.json', 'body', 'body', 'cannot sign in, account locked', 3);

┌────────────────────┬───────────┬─────────────┬────────┐
│ article            │ bm25_rank │ vector_rank │ score  │
├────────────────────┼───────────┼─────────────┼────────┤
│ account-lockout.md │         1 │           1 │ 0.0328 │
│ mfa-enrollment.md  │         2 │           3 │  0.032 │
│ password-reset.md  │         4 │           2 │ 0.0318 │
└────────────────────┴───────────┴─────────────┴────────┘

hybrid_search(source, text_col, vector_or_text_col, 'query', k [, bm25_weight, vector_weight]) searches a path, a table or CTE name, or a subquery.

• It ranks by BM25 over the text column, and by the cosine distance of a vector column, or of a text column embedded with --model, to the query.
• Each side considers its best 4k rows. A row found by both sides ranks first; a row found by one side is still included.
• It returns the source's columns with bm25_rank, vector_rank and score. A rank is NULL where its side left the row out.
• The optional bm25_weight, vector_weight arguments after k adjust the balance.

Logs repeat their messages, so rank the distinct ones:

SELECT message, bm25_rank, vector_rank, round(score, 4) AS score
FROM hybrid_search((SELECT DISTINCT message FROM 'logs/*.parquet'), 'message', 'message', 'database timeout', 3);

┌────────────────────────────────────────────┬───────────┬─────────────┬────────┐
│ message                                    │ bm25_rank │ vector_rank │ score  │
├────────────────────────────────────────────┼───────────┼─────────────┼────────┤
│ connection timeout to db-primary after 30s │         1 │           1 │ 0.0328 │
│ slow query on orders table                 │      NULL │           2 │ 0.0161 │
│ user login succeeded                       │      NULL │           3 │ 0.0159 │
└────────────────────────────────────────────┴───────────┴─────────────┴────────┘

The same fusion can be written by hand. rrf(rank, …) sums 1 / (60 + rank) over the ranks a row has, and rrf_k(k, rank, …) uses another k:

WITH kw AS (SELECT article, row_number() OVER (ORDER BY bm25() DESC) AS r FROM 'kb.json'
            WHERE body @@ 'account locked' ORDER BY bm25() DESC LIMIT 20),
     vec AS (SELECT article, row_number() OVER (ORDER BY embed(body) <=> embed('account locked')) AS r
             FROM 'kb.json' ORDER BY embed(body) <=> embed('account locked') LIMIT 20)
SELECT coalesce(kw.article, vec.article) AS article, round(rrf(kw.r, vec.r), 4) AS score
FROM kw FULL JOIN vec ON kw.article = vec.article ORDER BY score DESC LIMIT 3;

┌────────────────────────┬────────┐
│ article                │ score  │
├────────────────────────┼────────┤
│ account-lockout.md     │ 0.0328 │
│ password-reset.md      │ 0.0161 │
│ data-classification.md │ 0.0159 │
└────────────────────────┴────────┘

Log Patterns

patterns() groups log messages by their pattern. It masks the numbers, addresses, identifiers and times in each message, and replaces a word that varies among many values with <*>. The result has one row per pattern (count, pattern, sample), the most frequent first, each with its smallest message as a sample:

SELECT * FROM patterns('app/*.parquet', 'msg') LIMIT 5;

┌─────────┬─────────────────────────────────────────────────────┬──────────────────────────────────────────────────────────────┐
│ count   │ pattern                                             │ sample                                                       │
├─────────┼─────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────┤
│ 2001037 │ Accepted password for <*> from <IP> port <NUM> ssh2 │ Accepted password for admin from 1.22.175.67 port 58152 ssh2 │
│ 1500909 │ GET /api/v1/users/<NUM> HTTP/<NUM> <NUM> <NUM>      │ GET /api/v1/users/100 HTTP/1.1 200 396ms                     │
│  998973 │ <*> logged in successfully                          │ admin logged in successfully                                 │
│  801785 │ Failed password for <*> from <IP> port <NUM> ssh2   │ Failed password for admin from 1.214.186.192 port 60421 ssh2 │
│  798745 │ <TIME> worker <NUM> heartbeat ok                    │ 2026-10-08T00:00:00Z worker 10 heartbeat ok                  │
└─────────┴─────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

The source is a path, the name of a table or a CTE, or a subquery, which a WHERE clause can narrow. A source with one column needs no column name:

SELECT count, pattern FROM patterns((SELECT msg FROM 'app/*.parquet' WHERE msg ILIKE '%fail%'));

┌────────┬──────────────────────────────────────────────────────────────┐
│ count  │ pattern                                                      │
├────────┼──────────────────────────────────────────────────────────────┤
│ 801785 │ Failed password for <*> from <IP> port <NUM> ssh2            │
│ 400285 │ Failed password for invalid user <*> from <IP> port <NUM> s… │
│ 300763 │ <*> failed to login                                          │
└────────┴──────────────────────────────────────────────────────────────┘

Masking

Values masked while messages are read:

Placeholder	Stands for
<NUM>	A number with its unit: 42, -1.5, 30ms, 1.2.3
<IP>	An IPv4 or IPv6 address
<HEX>	0x1f, a MAC address, a hash or a trace ID
<UUID>	A UUID
<TIME>	A date, a time of day or both: 2026-10-08T10:00:00Z, 08/Oct/2026:10:00:00, Jul 10
<URL>, <EMAIL>	A URL, an email address

A path is treated as one word with its identifiers masked (/api/v1/users/<NUM>), and so is a host name (db-1.prod.example.com).

Grouping

The remaining words are then compared across messages. A word becomes <*> where its position holds:

• more than eight different words, such as user names
• words that differ only in their digits: web01, web02
• a masked value in some messages and a word in others, such as an address or a host name
• a different word in nearly every message, such as an identifier

A position with only a few different words splits the messages into separate patterns instead, so user login succeeded and user login failed remain separate. The exception is a long message in which fewer than one position in six differs: a position there with three or more different words becomes <*>. A list of values of any length forms one pattern (delete blk_<NUM>).

Incremental Windows

search watch maintains hopping or tumbling windows over the files arriving in a directory. Each new file is read once and folded into per-window partials kept in a state directory, and the windows that changed are printed:

search watch --dir incoming --interval 5s \
  "SELECT service, tumble(ts, '1 day') AS day, count(*) AS requests, avg(latency_ms) AS avg_ms
   FROM 'incoming/*.parquet' GROUP BY 1, 2"

api 2026-10-01 00:00:00 8640 105.83863425925917
web 2026-10-01 00:00:00 8640 105.30731481481483
…

• hop(ts, '1 minute', '1 hour') defines sliding windows.
• HAVING count(*) > 100 prints only the windows over a threshold.
• --all prints every window, changed or not.
• The state persists across restarts (--state-dir, by default <dir>-state).

Output Formats

Tables are drawn for reading. Other formats are for programs, and --output (-o) selects one:

-o	Description
table	The default; numbers right-aligned, NULLs dimmed, long values shortened
csv, tsv	With a header row
json	An array of objects
jsonl	One object per line (ndjson is accepted too)
toon	TOON: column names once, then rows; the same data as JSON in fewer tokens, suited to language models
markdown	A Markdown table
line	One column = value line per value
none	No output (for timing)

search -o toon "SELECT service, count(*) AS errors, max(ts) AS last_seen
                FROM 'logs/*.parquet' WHERE level = 'ERROR'
                GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 3"

[3]{service,errors,last_seen}:
  api,268,"2026-10-03 23:56:30"
  auth,268,"2026-10-03 23:59:44"
  billing,267,"2026-10-03 23:53:16"

search -o json  "…"                      # [{"service":"api","errors":268,…}, …]
search -o jsonl "…" | jq .service
search -o csv   "…" > errors.csv

Statistics

The statistics line under a table can be turned off with --no-stats. For the other formats it is off unless requested with --stats, and it is then written to stderr, so the output stays clean:

search -o csv --stats "…" > out.csv      # the statistics line is printed on the terminal

Wide Results

A SELECT * wider than the terminal is fitted to it:

1. Text columns are narrowed first.
2. If that is not enough, the middle columns are replaced by a … column.
3. The statistics line reports how many columns are shown.

Numbers and timestamps are never shortened.

┌─────────────────────┬─────────┬─────────┬───┬───────────┬───────────┐
│ ts                  │ service │ level   │ … │ user_id   │ client_ip │
│ timestamp           │ varchar │ varchar │ … │ varchar   │ varchar   │
├─────────────────────┼─────────┼─────────┼───┼───────────┼───────────┤
│ 2026-10-01 00:00:00 │ api     │ ERROR   │ … │ user-0    │ 10.0.0.0  │
│ 2026-10-01 00:00:02 │ web     │ INFO    │ … │ user-3146 │ 10.0.1.7  │
│ 2026-10-01 00:00:04 │ auth    │ INFO    │ … │ user-2956 │ 10.0.2.14 │
└─────────────────────┴─────────┴─────────┴───┴───────────┴───────────┘
3 rows · 8 columns (5 shown) · …

--max-rows N (default 40, 0 for all) and --max-width N (default: the terminal's width) set the limits. To see every column, use -o line or -o json.

Writing Files

COPY … TO writes the rows of a query, a table or a file to a new file, and returns the number of rows written:

COPY (SELECT service, count(*) AS errors FROM 'logs/*.parquet' WHERE level = 'ERROR' GROUP BY 1 ORDER BY 1) TO 'errors.csv';
COPY (SELECT * FROM 'logs/*.parquet' WHERE status >= 500) TO 'errors.parquet';
COPY 'services.csv' TO 'services.json';

┌───────┐
│ Count │
│ int64 │
├───────┤
│     5 │
└───────┘

The format follows the file's extension (.csv, .tsv, .json, .jsonl, .ndjson, .parquet), past a compression's (errors.csv.gz is a gzipped CSV file), or the FORMAT option, and is CSV otherwise. Options follow the path in parentheses, with or without WITH:

Option	Description
FORMAT csv|tsv|json|parquet	The format
HEADER true|false	A CSV header line (default true)
DELIMITER ';'	The CSV separator (default ,; '\t' for a tab)
QUOTE ''''	The CSV quote (default "; '' puts no field in quotes)
ESCAPE '\'	What goes before a quote or an escape in a quoted CSV field (default ", which doubles a quote; '' escapes nothing)
FORCE_QUOTE (col, …), FORCE_QUOTE *	Every value of these columns in quotes, and their names in the header; a NULL stays out of quotes
NULL 'NA'	The text of a NULL in CSV (default an empty field)
NEW_LINE '\r\n'	What ends a CSV record (default \n; \r and \n are read as the characters)
PREFIX '[', SUFFIX ']'	Text before a CSV file's first record and after its last, which then ends no record; both or neither
DATEFORMAT '%d/%m/%Y', TIMESTAMPFORMAT '%Y%m%d %H:%M'	A CSV or JSON file's dates, or timestamps, written by a strftime format; TIMESTAMPFORMAT 'ISO' is %Y-%m-%dT%H:%M:%S.%fZ
ARRAY true|false	JSON as one array instead of one object per line (default false)
COMPRESSION gzip|zstd|none	A CSV or JSON file's compression (default: as a .gz or .zst extension says, else none)
COMPRESSION snappy|zstd|gzip|uncompressed	A Parquet file's page compression (default snappy); CODEC is the same option
ROW_GROUP_SIZE n	The rows of a Parquet row group (default 1,048,576)
KV_METADATA {key: 'value'}	Key-value metadata in a Parquet file's footer
PARTITION_BY (col, …)	A directory of Hive partitions (see Directories of Files)
PER_THREAD_OUTPUT true	A directory holding the rows' file, data_0 plus the format's extension
FILE_SIZE_BYTES 1000, FILE_SIZE_BYTES '100MB'	A directory of files of about this size each (see Directories of Files); 0 is refused
FILENAME_PATTERN 'f_{i}'	The names of a directory's files (default data_{i})
OVERWRITE, OVERWRITE_OR_IGNORE	Write into a directory that is not empty: replacing what it holds, or only its files of the names written
WRITE_PARTITION_COLUMNS true	Keep the partition columns in the partitions' files (default false)
RETURN_FILES true	Adds Files, the list of paths written, to the result beside Count
USE_TMP_FILE true|false, PARQUET_VERSION V1|V2	Accepted for compatibility. Every file is already written beside its path and renamed into place, and Search writes version 1 pages, which every reader reads

COPY (SELECT * FROM 'logs/*.parquet' WHERE level = 'WARN') TO 'warnings.txt' (FORMAT csv, DELIMITER ';', HEADER false);

A CSV field is put in quotes as DuckDB's COPY puts one: when it holds the delimiter, the quote, a carriage return or a line feed, or when it is the NULL text, so that a value of that text can be told from a NULL. With the default empty NULL text, an empty string is therefore written "".

JSON is written one object per line, a double as DuckDB writes it (2.0, 1e21, NaN, Infinity). Parquet files are written with Snappy compression, unless COMPRESSION says otherwise, and column statistics. Numbers, booleans, dates and timestamps keep their types (timestamps to the microsecond, in UTC), and other values are written as text. The file is written beside its destination and replaces it only when complete, so a failed COPY leaves no partial file.

Rows are written in the order the query returns them. A copy of a file sorted by time therefore stays sorted, and its row groups' statistics continue to let time filters skip row groups. A query without aggregation or ORDER BY is written as it is read, without its result held in memory.

COPY … TO writes local files only.

note

COPY is available in the CLI only. The engine inside Directors and Agents does not write files.

Directories of Files

With PARTITION_BY, the path is a directory, written as DuckDB writes one: a directory for each partition, col=value for each partition column, holding data_0 plus the format's extension with the partition's rows. A value is percent-encoded where it would not make a valid directory name, and a NULL is __HIVE_DEFAULT_PARTITION__. read_parquet reads such a tree back (see Hive Partitions). The partitions are written beside the directory and moved into it once every file is complete, so a COPY that fails leaves the directory as it was, including what OVERWRITE would have replaced.

COPY (SELECT *, year(ts) AS year, month(ts) AS month FROM 'logs/*.parquet') TO 'by_month' (FORMAT parquet, PARTITION_BY (year, month));
-- by_month/year=2026/month=10/data_0.parquet, …

With FILE_SIZE_BYTES, the path is also a directory, of files data_0, data_1 and so on. A file is complete once it holds the size or more, and the rows after it go to the next. A CSV or JSON file is measured every 2,048 rows, and a Parquet file at the end of each row group. Under FILE_SIZE_BYTES a row group holds DuckDB's 122,880 rows, not the usual 1,048,576, unless ROW_GROUP_SIZE sets another number. The files are therefore somewhat larger than the size, as DuckDB's are.

A directory that already holds anything is refused unless OVERWRITE or OVERWRITE_OR_IGNORE is set. As in DuckDB, PER_THREAD_OUTPUT and FILE_SIZE_BYTES cannot be combined with PARTITION_BY.

FILENAME_PATTERN names the files of these directories, including PARTITION_BY's:

• {i} is the file's number.
• {uuid} and {uuidv4} are a random UUID; {uuidv7} is a UUID of the current time.
• A pattern with none of these gets the number appended: FILENAME_PATTERN 'part' writes part0.

A COPY to a single file ignores it.

COPY (SELECT * FROM 'logs/*.parquet') TO 'export' (FORMAT parquet, FILE_SIZE_BYTES '256MB', FILENAME_PATTERN 'logs_{i}', RETURN_FILES true);
-- Count | Files
-- 52000000 | [export/logs_0.parquet, export/logs_1.parquet, …]

Merging Files

MERGE writes the rows of several Parquet files as one Parquet file, in the order the files are named; the files a pattern matches are taken in name order. It returns what it wrote:

MERGE 'logs/*.parquet' TO 'logs.parquet';

┌───────┬────────┬────────────┬────────┬──────────┬────────┐
│ files │ rows   │ row_groups │ copied │ combined │ bytes  │
│ int64 │ int64  │ int64      │ int64  │ int64    │ int64  │
├───────┼────────┼────────────┼────────┼──────────┼────────┤
│     3 │ 129600 │          2 │      1 │        1 │ 373281 │
└───────┴────────┴────────────┴────────┴──────────┴────────┘

Files can also be named one by one:

MERGE 's3://logs/2026-10-01/part-1.parquet', 's3://logs/2026-10-01/part-2.parquet' TO 'logs-2026-10-01.parquet';

The rows are not decoded and written again; the merged file is assembled from the inputs' row groups:

• A row group with at least half of ROW_GROUP_SIZE rows, or half of ROW_GROUP_SIZE_BYTES, is copied unchanged: its pages, dictionaries, statistics, bloom filters and page indexes are kept, at their new offsets. On Linux, the row groups of a local file are copied by the kernel, and on a file system that shares blocks between files (XFS, Btrfs), the copy shares the input's blocks instead of duplicating them.
• Consecutive smaller row groups are combined into row groups of up to ROW_GROUP_SIZE rows. Their pages are kept; their dictionaries are merged into one, and only the pages whose dictionary codes change are encoded again. The combined row group has statistics, page indexes and bloom filters of its own, built from those of the row groups it was made of.

The files must have the same columns, with the same types; top-level columns may be in a different order. The merged file has the first file's schema and its key-value metadata. Row groups compressed with a codec other than Snappy, gzip, Zstandard or none are copied but not combined. Encrypted files, and files whose column chunks are stored in other files, are refused.

Option	Default	Description
ROW_GROUP_SIZE n	122880	The most rows a combined row group holds
ROW_GROUP_SIZE_BYTES n	64 MB	The most compressed bytes a combined row group holds

MERGE 'events/*.parquet' TO 'events.parquet' (ROW_GROUP_SIZE 500000);

The file is written beside its destination and replaces it only when complete, so a failed MERGE leaves no partial file, and a file can be merged into itself to combine its small row groups:

MERGE 'events.parquet' TO 'events.parquet';

Inputs can be local files or objects of Amazon S3, Azure Blob Storage and HTTPS servers. An HTTPS server is not listed, so its files are named one by one rather than with a glob. At most four combined row groups are held in memory at a time.

Remote Destinations

The destination can also be an object of Amazon S3 or Azure Blob Storage, named as in Remote Storage, with the same credentials:

MERGE 's3://logs/2026-10-01/*.parquet' TO 's3://logs/2026-10-01.parquet';
MERGE 'az://logs/2026-10-01/*.parquet' TO 'az://archive/2026-10-01.parquet';

The object is uploaded in parts while it is written (an S3 multipart upload, or the blocks of an Azure block blob), and it is created only when the last part is committed. A failed MERGE leaves the object as it was and aborts the S3 upload it started; Azure discards blocks that were never committed. A small file is uploaded in a single request. Parts start at 16 MB and grow for very large files, and up to 128 MB of them are in flight at a time.

The credentials need write access: s3:PutObject and s3:AbortMultipartUpload for S3, or a role such as Storage Blob Data Contributor (or an SAS that allows writes) for Azure.

warning

If the process is killed while an S3 upload is in flight, the parts already uploaded remain until a lifecycle rule for incomplete multipart uploads removes them.

MERGE … TO cannot write to an HTTPS server. MERGE is available in the CLI only.

Interactive Shell

search with no query opens an interactive shell; statements end with ;.

• Editing: the arrow keys, Home and End, Ctrl-Left and Ctrl-Right by word, and the Emacs keys (Ctrl-A, E, K, U, W, Alt-B, F, D).
• History: Up and Down recall earlier lines, kept in ~/.vmsearch_history (readable only by the current user).
• Highlighting: keywords, strings, numbers and comments are highlighted as you type.
• Ctrl-C cancels a running query, or abandons the statement being typed.
• Ctrl-D on an empty line exits.

Command	Description
.mode FORMAT (.output)	The output format
.maxrows N, .maxwidth N	Table limits
.timer on|off	The statistics line
.dialect default|sqlite	The SQL dialect
.threads N	Worker threads
.table NAME=FILE, .tables	Register and list tables
.read FILE	Run a script
.help, .quit	Help, and exit

Piped input runs as a script: search < queries.sql. So do -f file.sql and -c "…".

HTTP Server

search --serve starts an HTTP server that runs the SQL queries clients send to it. It runs as one long-running process, which keeps the files' footers, indexes and pages from one query to the next, so repeated queries run faster. When you run search once per query instead, each run reads them again. A search command that runs without --serve is unchanged.

search --serve                                  # http://127.0.0.1:8080
search --serve 9000                             # http://127.0.0.1:9000
search --serve 0.0.0.0:8080                     # every network interface
search --serve --address 0.0.0.0 --port 8080

Command	The server listens on
search --serve	127.0.0.1:8080, the default
search --serve 9000	127.0.0.1:9000
search --serve 0.0.0.0	0.0.0.0:8080
search --serve 0.0.0.0:8080	Every network interface, on port 8080
search --serve --address 0.0.0.0 --port 8080	The same address, given as two flags, the way the Director's API takes them
search --serve 0.0.0.0:8080 --port 9000	Nothing. The server refuses to start, because the port is given twice
search --serve -c "SELECT 1"	Nothing. The server refuses to start, because it does not run statements of its own

--serve=ADDR works too. IPv6 addresses work as [::1]:8080 or as ::1. The server's options are listed under Options.

note

--serve is not the same as --mode server. --mode server is the resource profile: every core, and up to 90% of the memory. It is the default for both the CLI and the server.

Queries

Every query sends the server's token (see Token):

curl -H "Authorization: Bearer $TOKEN" \
  --data-binary "SELECT count(*) FROM 'logs/*.parquet'" \
  "http://127.0.0.1:8080/query?format=csv"

Request	What it does
POST /query	Runs the SQL script in the request body. The response is the result of the script's last statement
POST /query?format=csv	Picks the output format: json (the default), jsonl, csv, tsv, table, markdown, line, toon or none
GET /ping	Answers ok. It does not need the token, so a health check can use it

The X-Search-Rows header holds the number of rows in the result, and the X-Search-Run-Time header the query's run time in seconds. A result larger than 1 MB is streamed while the query runs; the two values then come as HTTP trailers at the end of the response, and if the query fails after the response has started, the X-Search-Error trailer holds the error.

Status	When
400	The query fails before the response starts; the body is {"error": "…"}. Also an unknown format, or an empty script
401	The request does not carry the right token
405	A method other than POST on /query
413	A script larger than 1 MB

Token

What is set	The token the server uses
--token T	T
The VMSEARCH_TOKEN environment variable, and no --token	The variable's value
Neither	A random token that the server makes. The server prints it when it starts, with a curl example

The server needs the token on every address, 127.0.0.1 included: any local user can reach a loopback port, and so can any web page that a browser on the same host opens.

warning

A query can read any file that the server's user can read. Run the server as a user that can read the data it serves and nothing more, and serve HTTPS with --tls-cert and --tls-key when the server listens on a network.

Without --allow-write, the server refuses COPY … TO, MERGE … TO and EXPORT DATABASE. Without --allow-private, it refuses a remote source on a loopback or private address, such as a MinIO server on the local network, which the CLI allows (see Remote Storage).

Sessions and Limits

• Each request is a session of its own. A table that a request creates is gone when the request ends.
• The server runs one query at a time, on all of its cores. Other requests wait for their turn.
• Ctrl-C or SIGTERM stops the server. The server lets the running query finish first.
• A client has 10 seconds to send the request headers and 1 minute to send the whole request. The server closes an idle connection after 2 minutes.

Scan Cache

The server keeps the pages that its queries read in memory, already decompressed. Later queries read those pages from memory, without reading them from the file or decompressing them again. Only search --serve uses the cache; a single search command does not.

Configuration	Size of the scan cache
--cache-memory 8GB	8 GB
--cache-memory 0	No cache
No --cache-memory	A quarter of the memory available. In a container, that is a quarter of the container's memory limit
No --cache-memory, and the server cannot tell how much memory there is	No cache

• Sizes take KB, MB or GB (or M and G), in steps of 1024, so 1GB is 1024³ bytes. These are the same units as --max-memory takes.
• The server prints the size of the cache when it starts.
• When the cache is full, it removes the pages that no query has read recently.
• The cache tells files apart by their path, size and modification time. When a file changes, the server reads it again.
• The cache holds pages of compressed Parquet column chunks, from local or remote files. It does not hold CSV or JSON data, or column chunks that are stored uncompressed.

note

The cache's memory does not count toward --max-memory, which limits what queries use before their aggregations spill to disk. Size the two together: with the defaults, the cache can take a quarter of the memory on top of what the queries use.

In Directors and Agents

The engine inside Directors and Agents runs the Enrich processor's SQL. It reads local files and tables only, and does not write files: remote storage, COPY and MERGE belong to the CLI alone.

The JSON functions json_extract_string, extractjson and json_value are available on Directors and Agents alike. The Agent build leaves out the following:

Feature	On an Agent
bm25() without a full-text index	Returns an error
hybrid_search()	Returns an error
embed()	Returns an error
INTERVAL	Returns an error
patterns()	Returns an error
Vector index	Not read. A nearest-vector query scans its files whole, without an error