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 / .bz2), Avro files, Windows event logs, Excel workbooks and packet captures, and over the files inside zip archives. 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
Geometry and geography	GEOMETRY and GEOGRAPHY types and spatial functions such as ST_Contains() and ST_Distance_Sphere(), over Parquet files and Delta tables
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, Parquet or Avro files, or Excel workbooks, 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, where the files a query reads from zip archives are copied, and where compressed Avro files are decompressed
--cache-dir DIR	The user cache directory	Where remote CSV, JSON and Avro sources, event logs, workbooks and packet captures, and remote zip archives, 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', 'file.csv.bz2', 'file.avro', 'file.avro.gz', 'file.evtx', 'file.xlsx', 'file.pcap', with one or more comma-separated globs
• read_parquet('glob'), read_csv('glob'), read_json('glob'), read_avro('glob'), read_evtx('glob'), read_xlsx('glob'), read_pcap('glob'); read_csv takes the options in CSV Files, read_json those in JSON Files, read_avro those in Avro Files, and the other three those in Windows Event Logs, Excel Workbooks and Packet Captures
• the files inside a zip archive, such as 'export.zip/*.csv' (see Compressed Files and Zip Archives)
• 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, zstd, or bzip2 (see Compressed Files and Zip Archives)
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 document's own keys are its columns. Objects and arrays inside a document are read as nested types (see Nested Types and JSON Columns). read_json takes these options, with DuckDB's meanings:

Option	Description
columns = {'a': 'BIGINT'}	The columns to read and their types. A type can also be JSON or a nested type: columns = {'user': 'STRUCT(name VARCHAR, geo JSON)', 'tags': 'VARCHAR[]'}. FLOAT[] reads an array of numbers as a vector
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, zstd or bzip2. none is refused, as in DuckDB
hive_partitioning, hive_types	As in Hive Partitions
map_inference_threshold	How many keys an object needs before it can be read as a MAP (see Nested Types). The default is 200; -1 turns this off
field_appearance_threshold	A number from 0 to 1. An object whose keys appear, on average, in a smaller share of its objects than this is a MAP. The default is 0.1; 0 turns this off
maximum_depth	How deep objects and arrays get their own types; deeper values are JSON. -1 (the default) means no limit, 1 makes every column JSON, and 0 reads each document as one JSON column
records	true (the default, also auto) gives a column for each key. false reads each document as one column named json, of the document's type (a STRUCT or a MAP). Each document must still be a JSON object: a line that holds an array or a single value is an error, as before
maximum_object_size	Accepted; it changes nothing

Nested Types

Search samples the first documents (see Sample and Errors) and types each path from the values it holds there. For the document {"id": 1, "user": {"name": "ana", "geo": {"city": "Paris"}}, "tags": ["a", "b"]}, the columns are:

Column	Type
id	BIGINT
user	STRUCT(name VARCHAR, geo STRUCT(city VARCHAR))
tags	VARCHAR[]

• An object is a STRUCT of its keys, in the order the sample first had them. A key that some objects lack is NULL in those rows.
• An array is a list of its elements' type: BIGINT[], DOUBLE[] (when any number has a fraction), VARCHAR[], STRUCT(...)[], and so on. An array that is always empty in the sample is JSON[].
• A key whose values have different kinds in the sample is JSON: for example a number in one document and a string in another, or an object in one and a string in another. Its values are their JSON text, so the string x reads as "x", with its quotes. An integer beside a number with a fraction is DOUBLE, not JSON.
• A key that is always null in the sample is JSON. An object that is always empty ({}) is MAP(VARCHAR, JSON).
• Strings that are all ISO 8601 dates, timestamps or times are DATE, TIMESTAMP or TIME, inside structs and lists too (see Dates and Times).

An object is a MAP(VARCHAR, …) of its values' type instead of a STRUCT in two cases:

• It has 200 keys or more, and their values are of about one type (map_inference_threshold).
• Its keys are spread thin: on average, a key appears in fewer than 10% of the objects at that path (field_appearance_threshold). An object whose keys are IDs is a typical case.

The document's own keys are always columns, even when these rules would make the whole document a MAP. Use records = false to read each document as one column instead:

-- objects whose keys are IDs, as a map
SELECT scores['player-17'] FROM read_json('games.json', map_inference_threshold = 50);

-- each document as one STRUCT column
SELECT json.user.name FROM read_json('events.json', records = false);

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.

The error names the value, the type and the column, for example could not convert JSON value "abc" to BIGINT inside column "user". With ignore_errors, a value that should be an object but isn't reads as a struct whose fields are all NULL.

Search checks only the values that a query reads. When a query filters rows, or keeps only its top rows with ORDER BY and LIMIT, Search may skip the STRUCT, list and MAP values of the rows it leaves out. A value that doesn't fit in a row that the query leaves out is then not an error. The same query always gives the same result.

Dates and Times

A string holding an ISO 8601 date, timestamp or time is typed DATE, TIMESTAMP or TIME, inside structs and lists too. 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.

Avro Files

Apache Avro object container files are read as Parquet files are: each file's records are the rows, and the fields of its record are the columns.

SELECT event, count(*) FROM 'events/*.avro' GROUP BY event;
SELECT * FROM read_avro('s3://capture/hub/0/2026/10/*/*/*.avro', filename = true);

FROM 'file.avro' and read_avro('glob') read local files, and files in Amazon S3, Azure Blob Storage and on HTTPS servers (see Remote Storage). A remote file is downloaded whole into the cache directory (--cache-dir) first, as a CSV file is. read_avro takes filename, file_row_number, union_by_name and the Hive partitioning options of read_parquet (see Hive Partitions).

A file's blocks can be uncompressed, or compressed with deflate, Snappy, Zstandard or bzip2. A file whose blocks are compressed with xz is refused with an error.

Compressed Avro Files

A whole Avro file can also be compressed with gzip, Zstandard or bzip2. Search reads it by its extension, as it reads a compressed CSV or JSON file, and returns the same rows as it does for the plain file:

• .avro.gz is gzip.
• .avro.zst and .avro.zstd are Zstandard.
• .avro.bz2 is bzip2.

SELECT count(*) FROM 'exports/events.avro.gz';
SELECT * FROM read_avro('s3://bucket/2026/10/*.avro.zst', filename = true);

• Search first decompresses each compressed file into a copy in the spill directory (--spill-dir), and deletes the copy when the query ends. The spill directory needs room for these copies, and its limits apply to them.
• A filename column and error messages show the compressed file's own name, not the copy's.
• This compression is separate from the compression of the blocks inside a file, above. COPY … TO writes .avro files only, and sets the blocks' compression with CODEC (see Writing Avro Files).

Avro Types

Avro type	Search type
boolean	BOOLEAN
int, long	BIGINT (INTEGER and BIGINT inside a list, struct or map)
float, double	DOUBLE (FLOAT and DOUBLE inside a list, struct or map)
string, enum	VARCHAR
bytes, fixed	BLOB
record	STRUCT
array	A list. An array of floats or doubles in a column of its own is a vector, FLOAT[] or DOUBLE[], as a Parquet one is
map	MAP(VARCHAR, …)
decimal	DECIMAL of its precision and scale; DOUBLE past 38 digits
uuid, on a string or on a fixed type of 16 bytes	UUID
date	DATE
time-millis, time-micros	TIME
timestamp-millis, timestamp-micros, timestamp-nanos	TIMESTAMP WITH TIME ZONE, nanoseconds rounded to the nearest microsecond
local-timestamp-millis, local-timestamp-micros	TIMESTAMP
local-timestamp-nanos	TIMESTAMP_NS, nanoseconds kept (see Nanosecond Timestamps)
duration	INTERVAL (its text inside a list, struct or map)

A logical type that Search does not know is read as the type under it, as the Avro specification says. A timestamp outside the years a TIMESTAMP holds, 290309 BC to 294247 AD, is an error (see Dates and Intervals). The SQLite dialect reads a decimal as a REAL.

Unions

• A union of null and one other type, in either order, reads as that type, and null reads as NULL.
• A union of int and long reads as BIGINT, and one of float and double as DOUBLE.
• Any other union reads as a STRUCT with a field for each type other than null, named member0, member1 and so on in the union's order, as Spark reads a union. The field of the type a value has holds the value, and the other fields are NULL.

In an Event Hubs Capture file, SystemProperties is a map whose values are a union of long, double, string and bytes, so a string value is in member2:

SELECT SystemProperties['x-opt-partition-key'].member2 AS partition_key, decode(Body) AS body
FROM 'capture/*.avro';

Files with Different Schemas

The files of a glob can have different schemas:

• Without union_by_name, the columns are the first file's fields, and each file is read by its fields' names. A field that a file lacks is NULL in its rows, or the field's default where the first file gives one.
• With union_by_name, the columns are every file's fields, in the order they first appear.

A field can widen from file to file as Avro promotes it: an int to a long, either of them to a float or a double, and a float to a double. Text and bytes read as each other, and so do an enum and a string. Two types of one field that no promotion joins, such as a long and a string, are an error that names the field and the two files.

Limits

A record that holds itself, directly or through its fields, is refused, as Spark refuses one: a column cannot hold a value of its own type. A record of no fields reads as NULL.

A file that claims more than it can hold, or a schema too large to read, is refused:

• a block of more than 256 MiB, compressed or not
• an array or a map with more items than its bytes have room for
• a schema that nests types more than 64 deep
• a schema that holds more than 262,144 types and fields, where a named type counts once for each place that uses it

Writing Avro Files

COPY … TO 'file.avro', or (FORMAT avro), writes an Avro file (see Writing Files):

COPY (SELECT * FROM 'logs/*.parquet' WHERE level = 'ERROR') TO 'errors.avro' (CODEC 'zstandard');

• CODEC, or COMPRESSION, is the blocks' compression: deflate by default, or null, snappy or zstandard (zstd). Deflate is the default because every Avro reader reads it without anything else installed. Search reads bzip2 but does not write it.
• KV_METADATA {key: 'value'} adds entries to the file's header.

The file's record is named topLevelRecord, as Spark names it. Each column is a union of null and its type, with a default of NULL, and so is each field of a struct, each item of a list and each value of a map:

Search type	Avro type
BIGINT and the other integers	long (int for the narrower ones inside a list, struct or map)
DOUBLE, FLOAT	double, float
BOOLEAN	boolean
VARCHAR, JSON, an ENUM, a BIT	string, of the value's text
GEOMETRY, GEOGRAPHY	string, of the value's WKT
BLOB	bytes
UUID	string of the uuid logical type
DATE	int of the date logical type
TIME	long of time-micros
TIMESTAMP	long of local-timestamp-micros
TIMESTAMP_NS	long of local-timestamp-nanos
TIMESTAMP WITH TIME ZONE	long of timestamp-micros
DECIMAL(p,s)	bytes of the decimal logical type
INTERVAL	A fixed type of 12 bytes of the duration logical type
HUGEINT	double, as Search writes one to Parquet
UBIGINT	A decimal of 20 digits
A vector, FLOAT[] or DOUBLE[]	An array of float or double
A list	An array
A struct	A record named by its column, in a namespace of the records around it
A MAP of text keys	A map

An Avro name is letters, digits and underscores, and starts with a letter or an underscore. A column whose name is not one is refused: give it another name with AS. So are two columns of one name, a struct of unnamed fields, and a MAP whose keys are not text. An INTERVAL is refused where a duration cannot hold it: a negative part, or a time that is not a whole number of milliseconds.

Windows Event Logs

Windows event log files (.evtx) are read one row per event record.

SELECT event_id, count(*) FROM 'logs/Security.evtx' GROUP BY event_id ORDER BY 2 DESC;

SELECT time_created, event_data['TargetUserName'] AS user, event_data['IpAddress'] AS ip
FROM read_evtx('collected/*/Security.evtx', filename = true)
WHERE event_id = 4625;

FROM 'file.evtx' and read_evtx('glob') read local files, files inside zip archives ('export.zip/*.evtx', see Zip Archives), and files in remote storage (see Remote Storage). A remote file is downloaded whole into the cache directory (--cache-dir) first. read_evtx takes filename = true, the Hive partitioning options of read_parquet (see Hive Partitions), and file_date and time_column (see Files Dated by When They Were Written).

Column	Type	What it holds
record_id	BIGINT	The event's record ID
time_created	TIMESTAMP WITH TIME ZONE	When the event happened, to 100 nanoseconds
provider_name	VARCHAR	The name of the provider that wrote the event
provider_guid	UUID	The provider's GUID; NULL for a classic event source
event_id	BIGINT	The event ID
version, level, task, opcode	BIGINT	As the event's System element holds them
keywords	BIGINT	The keywords' 64 bits
channel	VARCHAR	The log's name, such as Security
computer	VARCHAR	The name of the computer the event happened on
user_id	VARCHAR	The security identifier (SID) of the event's user, such as S-1-5-18
process_id, thread_id	BIGINT	The process and the thread that wrote the event
activity_id, related_activity_id	UUID	The event's activity IDs
event_data	MAP(VARCHAR, VARCHAR)	The event's data, by name (see below)
xml	VARCHAR	The event's XML, as Windows writes it

A value the event does not hold is NULL.

• event_data holds the event's data by name. A classic event's insertion strings have no names: they are named Data, Data_2, Data_3 and so on. A name that repeats also takes _2, _3 and so on.
• xml is the event's XML as Windows writes it: what PowerShell's Get-WinEvent shows with ToXml(). Text in an 8-bit code page reads as code page 1252.
• No message text. Windows builds an event's message from the provider's message files, and a log file doesn't hold them.

Damaged Event Logs

Search reads as much of a damaged file as it can:

• A chunk whose checksums do not match its bytes is skipped, with a warning.
• A file that was not closed cleanly is read up to its last whole chunk. A file that ends inside a chunk reads the chunks before it, with a warning.
• An event that does not decode is skipped, with a warning.

An event is skipped, with a warning, when it nests elements more than 64 deep, or holds more than 262,144 tokens or more than 1 MiB of text and values. The events of one chunk can hold at most 8,388,608 tokens and 16 MiB of text and values; past that, the chunk's other events are skipped.

Excel Workbooks

A sheet of an Excel workbook (.xlsx or .xlsm) reads as a table, and COPY writes query results to a workbook.

SELECT * FROM 'assets.xlsx';
SELECT host, owner FROM read_xlsx('assets.xlsx', sheet = 'Hosts', range = 'A1:F100');
COPY (SELECT * FROM 'logs/*.parquet' WHERE level = 'ERROR') TO 'errors.xlsx' (SHEET 'Errors');

FROM 'file.xlsx' and read_xlsx('glob') read local files, files inside zip archives, and files in remote storage, which are downloaded whole into the cache directory first. By default Search reads the workbook's first sheet, and its first row holds the columns' names.

Option	Description
sheet = 'Hosts'	The sheet to read. Case does not matter
range = 'A1:F100'	The cells to read. 'A:F' reads every row of columns A to F, and 'Hosts!A1:F100' names the sheet too
header = false	The first row is data. The columns are then named by their letters: A, B, C
all_varchar = true	Read every value as text
sample_size = n	The rows the columns' types are guessed from (20,480 by default; -1 for every row)
ignore_errors = true	Read a value that does not fit its column's type as NULL, instead of failing the query
stop_at_empty = true	Stop at the first empty row. By default an empty row is skipped
filename, file_date, time_column, and the Hive partitioning options	As for Windows Event Logs

Workbook Values

Each column's type comes from its values in the sample:

Values	Type
Whole numbers	BIGINT
Other numbers	DOUBLE
TRUE and FALSE	BOOLEAN
Numbers of a date format	DATE
Numbers of a date and time format	TIMESTAMP
Numbers of a time format	TIME
Numbers of an elapsed-time format, such as [h]:mm:ss	INTERVAL
Text, or values of more than one of these kinds	VARCHAR

• Dates and times are read by the cell's number format, in either of Excel's date systems, from 1900 or from 1904, as the workbook says. Dates of the 1904 system end at 9999-12-31.
• A time of day of a day or more, such as =NOW() formatted h:mm, keeps its date: its column is TIMESTAMP.
• A date or time column does not read a plain number, one of no date or time format, as a date.
• An error cell, such as #N/A, is NULL.
• A formula reads as the value the workbook saved with it.
• Merged cells: the first cell holds the value, and the others are NULL.
• The files of a glob are read by the first workbook's columns. With a header, each workbook's columns are matched by their names, whatever their case, and a column a workbook lacks is NULL in its rows.
• Memory: the workbooks a query reads at once share a quarter of the memory limit for their shared strings. A workbook whose shared strings need more fails with an error that says so.

Search refuses an Excel 97-2003 workbook (.xls), a binary workbook (.xlsb), an OpenDocument spreadsheet (.ods) and a workbook encrypted with a password, with an error that says why. Save such a file as an Excel Workbook (.xlsx) in Excel to read it.

Writing Workbooks

COPY … TO 'file.xlsx', or (FORMAT xlsx), writes a workbook of one sheet (see Writing Files):

• SHEET 'name' names the sheet (Sheet1 by default). HEADER false leaves out the row of the columns' names.
• Numbers, booleans, dates, times and timestamps are written as Excel values, dates and times with date and time formats. Text is written as text, and a NULL is an empty cell.
• An integer past 2^53, which Excel's numbers do not hold exactly, is written as its text. So are a date before 1900 or after 9999, and an infinite date or timestamp. A number that is infinite or not a number (NaN) is the error cell #NUM!.
• FILE_SIZE_BYTES splits the output into files of about the size given (see Directories of Files).
• A sheet holds 1,048,576 rows, the header's among them, so a COPY of more rows fails. A cell holds 32,767 characters: a longer text is cut, with a warning. A column name that is too long is cut, with a warning.

Packet Captures

libpcap's pcap files and pcapng files are read one row per packet.

SELECT src_ip, dst_ip, dst_port, count(*) AS packets, sum(original_length) AS bytes
FROM 'captures/*.pcap'
WHERE ip_protocol = 6
GROUP BY ALL ORDER BY bytes DESC LIMIT 10;

SELECT timestamp, dns_query_name, dns_rcode, dns_answer_count, dns_answers
FROM read_pcap('dns.pcapng')
WHERE dns_response;

FROM 'file.pcap' or 'file.pcapng', and read_pcap('glob'), read local files, files inside zip archives, and files in remote storage, which are downloaded whole into the cache directory first. read_pcap takes filename, the Hive partitioning options of read_parquet, and file_date and time_column (time_column = 'timestamp').

• Files: pcap files of either byte order, with microsecond or nanosecond times, and pcapng files with many sections and interfaces. A capture may be uncompressed, or compressed with gzip, Zstandard or bzip2.
• Link types: Ethernet, with its VLAN tags; Linux cooked captures, versions 1 and 2; raw IP; and BSD loopback.

Column	Type	What it holds
packet_number	BIGINT	The packet's place in its file, from 1
timestamp	TIMESTAMP WITH TIME ZONE	When the packet was captured
interface_id, interface_name	BIGINT, VARCHAR	The pcapng interface the packet was captured on, and its name
captured_length, original_length	BIGINT	The bytes captured, and the bytes the packet had
link_type	BIGINT	The link type's number (1 for Ethernet)
eth_src, eth_dst, eth_type	VARCHAR, VARCHAR, BIGINT	The MAC addresses, and the EtherType of what the frame holds
vlan_ids	BIGINT[]	The VLAN IDs, the outermost first; NULL for none
ip_version, src_ip, dst_ip	BIGINT, VARCHAR, VARCHAR	IPv4 or IPv6, and the addresses
ip_protocol	BIGINT	The protocol: 6 for TCP, 17 for UDP, 1 for ICMP, 58 for ICMPv6
ttl	BIGINT	The time to live, or IPv6's hop limit
ip_id, ip_dont_fragment, ip_more_fragments, ip_fragment_offset	BIGINT, BOOLEAN, BOOLEAN, BIGINT	The fragment fields
src_port, dst_port	BIGINT	The TCP or UDP ports
tcp_flags	BIGINT	The TCP flags: FIN 1, SYN 2, RST 4, PSH 8, ACK 16, URG 32, ECE 64, CWR 128, AE 256
tcp_seq, tcp_ack, tcp_window	BIGINT	The sequence and acknowledgment numbers, and the window
icmp_type, icmp_code	BIGINT	The ICMP or ICMPv6 type and code
payload	BLOB	The packet's data after its headers
dns_id, dns_response, dns_opcode	BIGINT, BOOLEAN, BIGINT	A DNS message's ID, whether it is a response, and its opcode
dns_rcode	VARCHAR	A response's code, such as NOERROR or NXDOMAIN, with the bits an EDNS record adds; NULL for a query
dns_query_name, dns_query_type	VARCHAR	The first question's name and type, such as example.com and AAAA
dns_answer_count	BIGINT	The answers the message's header counts
dns_answers	STRUCT(name VARCHAR, type VARCHAR, ttl BIGINT, data VARCHAR)[]	The answers, in order

A column of a header that the packet does not hold is NULL.

• DNS is read on ports 53, 5353 and 5355, over UDP and over TCP, including a message that the capture's snap length cut short: it reads as far as it goes.
• Not supported: Search does not put IP fragments or TCP streams back together. It does not read the packets inside a tunnel, or the packet an ICMP error quotes.
• Limits: a packet holds at most 16 MiB, and a pcapng block at most 17 MiB. A pcapng file holds at most 65,536 sections and 65,536 interfaces in all. A capture cut short is read up to its last whole packet, with a warning.

Files Dated by When They Were Written

read_evtx, read_xlsx and read_pcap take two options that skip files too old to hold the rows a query asks for:

• file_date = 'modified' dates each file whose path holds no date by the time it was last written: from the storage's listing, or from the file system for a local file.
• time_column names the column of the rows' times, such as time_column = 'time_created' for event logs or 'timestamp' for captures. With it, a filter that asks for rows from a time on skips the files last written more than a day before that time. WHERE time_created >= TIMESTAMP '2026-10-15 00:00:00' does not open the logs last written before 2026-10-14. A filter of times before a time skips no file, because a file can hold older rows. time_column needs file_date = 'modified'.

Compressed Files and Zip Archives

Parquet Page Compression

Search reads Parquet pages compressed with Snappy, gzip, Zstandard, LZ4 or Brotli, in version 1 and version 2 data pages. Both of Parquet's LZ4 codecs are read: LZ4_RAW, which most tools write today, and the older LZ4, which Spark and Hadoop write.

Search writes Snappy, gzip, Zstandard and LZ4_RAW pages, but no Brotli pages (see COMPRESSION in Writing Files).

Compressed CSV, JSON and Text Files

CSV, JSON and text files compressed with gzip, Zstandard or bzip2 are read by their extension: .gz, .zst or .bz2.

SELECT * FROM 'logs.csv.bz2';
SELECT * FROM read_csv('logs.dat', compression = 'bzip2');

• A file whose name does not show its compression is read as uncompressed, unless the compression option names it, as in the second query.
• read_csv and read_json_objects take auto (by the extension, the default), none, gzip, zstd or bzip2. read_csv also takes uncompressed. read_json takes auto_detect (the default), uncompressed, gzip, zstd or bzip2.
• A file of several bzip2 streams, as pbzip2 writes, is read whole.
• An Avro file compressed whole, such as events.avro.gz, is read by its extension too (see Compressed Avro Files).
• Search loads each compressed CSV or JSON file into memory whole. The compressed files of one FROM item can take up to half the memory budget (--max-memory) together. A larger file, or a bzip2 or gzip bomb, fails the query with an error that says so.

Zip Archives

A zip archive reads as a directory. What follows the archive's name in a path names the files in it:

SELECT count(*) FROM 'export.zip/*.csv';               -- the CSV files at the archive's top
SELECT * FROM read_json('export.zip/**/*.json');       -- JSON files at any depth
SELECT * FROM 'export.zip/logs';                       -- every file under logs/
SELECT * FROM read_csv('export.zip');                  -- every file, read as CSV
SELECT count(*) FROM 'exports/2026-*.zip/*.parquet';   -- files from several archives
SELECT * FROM 's3://bucket/exports/export.zip/*.csv';  -- an archive in remote storage
SELECT * FROM glob('export.zip/*');                    -- the files an archive holds

• *, ? and [...] match within one folder of the archive, and ** matches any number of folders. A name without these characters that names a folder reads every file under it.
• An archive named alone reads every file in it. The query's function (read_csv, read_json, and so on) sets the format. Without one, the first file's extension sets it.
• Every reader reads files in archives: Parquet, CSV, JSON, Avro and text files, compressed ones among them (export.zip/logs.csv.gz), and Windows event logs, Excel workbooks and packet captures (export.zip/*.evtx).
• A file in an archive is named by the archive's path and its own path, such as export.zip/logs/year=2026/a.csv. That name shows in a filename column, in glob and in error messages. Folders named key=value, in the archive's path or inside the archive, are read as Hive partition keys (see Hive Partitions).
• A remote archive, in Amazon S3, Azure Blob Storage, Google Cloud Storage or on an HTTPS server, is downloaded to the local cache (--cache-dir) first, as remote CSV and JSON files are.
• Search reads entries that are stored, or compressed with deflate or bzip2. It reads zip64 archives, archives made by Windows, macOS, 7-Zip, Info-ZIP and Python, and self-extracting archives.
• Search skips folders and the metadata macOS adds (__MACOSX/ and .DS_Store).
• Search copies each file a query reads from an archive to the spill directory (--spill-dir), and deletes the copy when the query ends. The spill directory needs room for these copies.

Archive Limits

• An archive can list up to 100,000 entries, and its directory can take up to 64 MB.
• One query can read up to 100,000 files from archives. The archives one query opens can list up to 1,000,000 entries together.
• Search refuses archives built as zip bombs: entries whose data overlap, and deflated entries that claim to hold more than deflate can. A deflated entry may claim at most 1,032 times its compressed size, plus 1,024 bytes.
• A file that decompresses to more or fewer bytes than the archive says, or to bytes with another checksum, fails the query.
• Search doesn't read encrypted entries, entries compressed with other methods (Deflate64, LZMA, Zstandard, XZ, PPMd), archives split into several files, or archives inside archives. The error names the archive and the file.

The engine inside Agents reads neither bzip2 files nor zip archives, nor Parquet pages compressed with Brotli. The search command and the engine inside Directors read all three (see In Directors and Agents). On an Agent, a query of a zip archive, or of a path into one, fails with an error that says a Director reads it: logs.zip: zip archives are read by a director, not by an agent. The error comes before the agent looks for the archive, so a path into an archive that does not exist gets it too. A folder whose name ends in .zip is still read as a folder on an Agent, as it is on a Director.

Hive Partitions

Parquet, CSV, JSON and Avro 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, JSON and Avro 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.

Large Parquet Row Groups

Before Search reads a Parquet row group, it works out how much memory the row group needs. It works this out from what the file's footer and page headers say: how many rows and values each column holds, and how large each page is once uncompressed. A query may use half of its memory limit to read files. The other half is for sorting, grouping and joining.

• A row group that fits in the half of the limit for reading files is read whole.
• A row group that does not fit is read a part at a time, where the query allows that. Most queries do: aggregates such as count, sum and GROUP BY, and queries that return rows without ranking them.
• When the query cannot read the row group a part at a time, it fails with an error that names the file and the row group. Some queries that rank rows with ORDER BY … LIMIT, and full-text searches, cannot.
• When a single page or a single value is larger than the query may use, the query fails with the same kind of error.

Search also refuses a page whose header says it is larger than its compressed bytes can hold. Such a file is damaged, or was made to look larger than it is.

A Parquet file can say it holds billions of rows in a few bytes. A column of NULLs, or of one value repeated, really is that small on disk. Earlier versions set aside memory for every row the file claimed, so one such file could use all of a server's memory. A query on such a file now reads within its memory limit, or fails with a clear error. The errors are:

parquet: a read needs more memory than the limit allows: row group 0 of hits.parquet needs 16.0GB to read, and this query may use 512.0MB. Raise the memory limit, or read fewer columns
parquet: a read needs more memory than the limit allows: column URL of row group 3 of hits.parquet needs 900.0MB to read, and a read may use 512.0MB
parquet: a read needs more memory than the limit allows: a page of hits.parquet needs 600.0MB uncompressed, and a read may use 512.0MB
parquet: a read needs more memory than the limit allows: the text values of a page need 4.0GB, and a read may use 512.0MB

The last one comes from a text value that the file stores once and repeats for many rows: a value of 64 KB repeated a million times is 64 GB once read.

To read such a file:

• Raise the memory limit, with --max-memory, or SET memory_limit = '8GB' in the shell.
• Read fewer columns. Each column of a row group adds to the memory it needs.
• Write the file with smaller row groups, with COPY's ROW_GROUP_SIZE option (see Writing Files).

SET memory_limit = '1GB';
-- An aggregate reads a large row group a part at a time.
SELECT count(*), max(ResolutionWidth) FROM 'hits.parquet';
-- If a row group needs more than 512 MB and the query can't read it in parts,
-- it fails with the first error above.

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, JSON and Avro sources, Windows event logs, Excel workbooks, packet captures and zip archives 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

read_json, read_ndjson and FROM 'file.json' read a JSON object as a STRUCT column (or a MAP), and an array as a list column (see Nested Types). SELECT * and DESCRIBE show one column for each of the document's own keys.

• Dotted names. user.name and user.geo.city read the field. So do t.user.name, with a table alias, and the quoted name "user.name", as long as no column has that exact name. Search reads only that field of each document, not the whole object.
• Map keys. m.key reads a MAP's value for that key, the same as m['key'].
• JSON functions. The -> and ->> operators and the JSON functions below work on STRUCT, list and MAP columns: user->>'name', user->'geo'->>'city', json_extract_string(user, '$.geo.city'), json_array_length(tags). On a column read straight from a JSON file, they read the object's JSON text from the file. On any other STRUCT, list or MAP value, they read the value written as JSON. They read JSON stored in a text column as well.
• The JSON text. to_json(user), or user::JSON, gives the object's JSON text exactly as the file holds it, white space included. Keys that the document doesn't have are not added. On a struct that does not come straight from a JSON file, to_json writes every field, with null for the missing ones.
• Counting. count(user) counts the documents that have the object, and user IS NULL finds the ones that don't. With ignore_errors = true, a value that should be an object but isn't is a struct whose fields are all NULL, not a NULL, so count(user) counts it too and user IS NULL doesn't match it.

COPY of a JSON read to Parquet writes nested columns, which other Parquet readers read as nested columns.

-- the types of a file's columns
DESCRIBE SELECT * FROM 'events.json';

-- a field of each document
SELECT user.geo.city, count(*) FROM 'events.json' GROUP BY 1;

-- the object's JSON text, as the file holds it
SELECT to_json(user) FROM 'events.json';

-- nested Parquet
COPY (SELECT * FROM 'events.json') TO 'events.parquet' (FORMAT parquet);

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.

Upgrading

Earlier versions read every object as a text column of its JSON, and every key inside it as another text column with a dotted name (user, user.name, user.geo, user.geo.city). An array was a text column of its JSON, or a FLOAT[] when it held only numbers. Existing queries over JSON files change as follows:

• SELECT * returns fewer columns: one for each top-level key. Ask for a field with user.name.
• SELECT user returns a STRUCT, shown as {'name': ana, 'geo': {'city': Paris}}. Use to_json(user) for the JSON text.
• user::VARCHAR gives the struct's text ({'name': ana, ...}), not the JSON text. Use to_json(user).
• Arrays of numbers are BIGINT[] or DOUBLE[], not FLOAT[]. The vector functions (<->, <=>, array_cosine_similarity) still take them. To read one as FLOAT[], name the type in columns.
• A key with values of two kinds was VARCHAR. It is now JSON, and its string values keep their quotes.
• A key that is always null in the sample is now a JSON column. Before, it was not a column at all.
• COPY … TO 'file.parquet' writes user as one nested column instead of one flat column for each path.
• A field read with a dot keeps its dotted path as the result column's name (user.geo.city). Use AS to choose another name.

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 value beside a JSON is cast to JSON, as DuckDB casts it: a number becomes its digits, a date its quoted text, and a struct an object. Text that is JSON stays as it is; any other text is an error, but only on the rows that read it.
• CASE, coalesce, ifnull, nullif, greatest, least, UNION and the other set operations give a JSON where one of their values is a JSON.
• A list holding a JSON value is a JSON[], whatever its other elements are, and json_extract also returns one for a wildcard path. Its elements print as JSON.
• Aggregates and window functions that return one of their values keep the type: min, max, first, last, any_value, arg_min and arg_max (of their value), mode, list (a JSON[]), and first_value and the other window functions. A GROUP BY key keeps it too. min and max compare the texts.
• A lag or lead default beside JSON values is the exception: its text is taken unchecked, as DuckDB takes it, so lag(j, 2, 'plain') gives plain.
• COPY writes a JSON column as JSON to JSON files, and with the JSON type to Parquet.

->> 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.

Parquet VARIANT

VARIANT is the Parquet type for semi-structured data that Spark 4, Databricks and Iceberg write. Search reads a VARIANT column as a JSON column: each row's document is reassembled from the column's parts, with nothing to declare.

COPY writes JSON columns as VARIANT instead of text with the VARIANT true option, which applies to Parquet files only:

COPY (SELECT data FROM events ORDER BY data->>'$.commit.collection') TO 'bluesky.parquet' (FORMAT parquet, VARIANT true);

With VARIANT true, the file's list, struct, map and vector columns are written as text, not as Parquet nested columns. Leave VARIANT off to keep those columns nested.

Each JSON column is shredded where it can be: its most common scalar fields are also stored as typed columns of their own. A field is shredded when:

• at least an eighth of the documents hold it, and
• 95% of its values are of one kind: a string, a 64-bit integer or a boolean.

A column has 64 shredded fields at most, the most common first. Fields inside nested objects count too. A column with no field that meets both conditions is written as VARIANT without shredding: each row's document is kept whole, and a query reads its fields from the documents.

The fields are chosen from a sample of the rows. A COPY without ORDER BY samples the file's first rows. A sorted COPY takes its sample from across the whole result, so a file sorted by a key is shredded for all of its documents, not just for the first key's rows. A value of another kind than its field's type, such as a number where most rows hold a string, is kept in the document's fallback value, so nothing is lost.

Where a file holds every value of a field in its typed column, these expressions read that column instead of parsing each document:

• j->>'$.a.b'
• json_extract_string(j, '$.a.b')
• CAST(j->>'$.a.b' AS BIGINT)

They then use the column's statistics, dictionary codes and filter pushdown. For example, take a file written as above from events shaped like JSONBench's Bluesky data, where every document holds commit.collection and commit.operation as strings. The query below reads both fields from their own columns. Because the file is sorted by collection, the filter can also skip the row groups whose statistics rule the collection out:

SELECT data->>'$.commit.operation' AS operation, count(*) AS events
FROM 'bluesky.parquet'
WHERE data->>'$.commit.collection' = 'app.bsky.feed.post'
GROUP BY operation
ORDER BY events DESC;

Across several files, a field is read this way only when every file shreds it alike. Otherwise it is read from the documents. The answers are the same either way: what the JSON function answers of the document, so NULL for a missing field, a document that is not an object, or a NULL document. In the SQLite dialect, paths are always read from the documents.

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)
• mode(x), the most frequent value, as in DuckDB:
  • DISTINCT is ignored. mode(DISTINCT x) is mode(x): of 5, 7 and 7 it is 7.
  • Ties. mode() WITHIN GROUP (ORDER BY x) gives a tie to the least value, and ORDER BY x DESC to the greatest. A plain mode(x) names no order: a tie goes to the value that came first while a group's values stay few on one worker, and to the least where the values are many and repeat. With several threads, which value came first can differ between runs, as in DuckDB. A query that needs one answer should use WITHIN GROUP.
  • Speed. mode(), median() and the discrete quantiles run on all the processors for integers, reals, dates, timestamps, DECIMALs of up to 18 digits, text and intervals. BOOLEAN, TIME, BLOB, UUID, nested values, HUGEINT, DECIMALs of more than 18 digits and text with a collation run one row at a time, with the same answers.
• string_agg(x, sep), also named group_concat() and listagg(). With a NULL separator, string_agg(x, NULL) reads no rows in the default dialect, and its value is NULL whatever the rows hold. In a PostgreSQL client's session it still joins the values with no separator, as PostgreSQL does
• 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 microseconds, from 290309 BC to 294247 AD, as DuckDB's does. Dates such as 0001-01-01 and 9999-12-31, which Delta Lake and Parquet tables often use to mean "no date", work as any other timestamp does.

• A timestamp before 1 AD is written with (BC), as 0044-03-15 (BC) 12:00:00. One after 9999 is written with all its year's digits, as 10000-01-01 00:00:00.
• A TIMESTAMP keeps six digits after the second. Text with more is read without the extra digits.
• epoch_ms() of a timestamp rounds to the nearest millisecond.
• make_timestamp(), make_timestamp_ms() and epoch_ms() of a number beyond those years are an error, rather than wrapping to a different timestamp.

A DATE reaches further, from 5877642-06-25 (BC) to 5881580-07-10, whether it is written as text or made with make_date(). What happens to a date outside a timestamp's years depends on whether it has to become a timestamp:

• Comparing a date with a timestamp works for any date. The date compares as its midnight, before or after every timestamp, against a TIMESTAMP or a TIMESTAMPTZ.
• date_diff, date_sub and age of two dates work from the dates' days, without converting them into timestamps, so they give DuckDB's answers for any date: date_diff and date_sub count the part they are given, and age returns an interval.
• A RANGE window frame with an interval over a DATE column works for any date.
• Converting such a date into a timestamp is an error. This covers date_trunc, a date plus an interval or a TIME, generate_series and range of dates, time_bucket and timezone(). It also covers a CASE, coalesce or greatest that combines the date with a timestamp. These fail only on the rows that actually use the date, so a guarded CASE still works.
• An explicit CAST of a column's date gives NULL instead, as other out-of-range casts do (see Casts).
• epoch_ns() of a date or a timestamp outside the years 1677 to 2262 is an error, as in DuckDB: a BIGINT of nanoseconds holds no more.
• A Parquet timestamp outside a timestamp's years fails the query that reads its column. This covers milliseconds, INT96 and timestamps inside lists. The error names the column and the value, for example column ts: timestamp out of range: … is outside the years a timestamp holds, 290309 BC to 294247 AD. Queries that don't read the column are unaffected.
• Infinite timestamps read from Parquet (infinity, -infinity) are written as infinity and -infinity in CSV and JSON output and in COPY TO CSV and JSON files.

Upgrading

A timestamp used to hold nanoseconds, so it reached only the years 1677 to 2262, and dates such as 0001-01-01 and 9999-12-31 failed with timestamp out of range. They now read as they are written. epoch_ms() now rounds to the nearest millisecond, where it used to cut the extra microseconds off.

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

Nanosecond Timestamps

A TIMESTAMP_NS keeps nanoseconds, and holds the years 1677 to 2262. A query has one when it:

• reads a Parquet column of nanoseconds that is not adjusted to UTC, as OpenTelemetry data often has, or an Avro local-timestamp-nanos field (see Avro Types);
• writes TIMESTAMP_NS '2024-01-01 12:00:00.123456789', CAST(x AS TIMESTAMP_NS) or make_timestamp_ns(n), of n nanoseconds since 1970;
• calls strptime() with a format that reads nanoseconds (%n) and no time zone.

SELECT TIMESTAMP_NS '2024-01-01 12:00:00.123456789' AS ts, typeof(ts), epoch_ns(ts);
-- 2024-01-01 12:00:00.123456789 | TIMESTAMP_NS | 1704110400123456789

These keep the nanoseconds:

• comparisons, sorting, grouping, joins, DISTINCT and UNION
• min(), max(), first(), last(), arg_min(), arg_max(), any_value(), mode(), count() and list()
• epoch_ns(), nanosecond(), strftime() with %n, typeof() and a cast to text
• writing Parquet, Avro, CSV and JSON files

Every other function first rounds a TIMESTAMP_NS to the nearest microsecond, and works on that TIMESTAMP. avg(), median(), date_trunc(), time_bucket() and adding an INTERVAL all return a TIMESTAMP. A TIMESTAMP_NS compared with a TIMESTAMP, a DATE or text turns the other value into a TIMESTAMP_NS first.

• A TIMESTAMP outside the years 1677 to 2262 can't be cast to TIMESTAMP_NS: the cast is an error.
• A TIMESTAMP_NS and a TIMESTAMP WITH TIME ZONE don't combine in one expression. A comparison, a CASE, coalesce(), greatest(), least() or a VALUES list of both is an error. Cast one of the two first.
• A Parquet column of nanoseconds that is adjusted to UTC, and an Avro timestamp-nanos field, read as TIMESTAMP WITH TIME ZONE, rounded to the nearest microsecond.
• A RANGE BETWEEN INTERVAL … window frame can't order by a TIMESTAMP_NS. Cast the column to TIMESTAMP in the ORDER BY.
• TIMESTAMP_S and TIMESTAMP_MS hold their values rounded to the second or the millisecond, and typeof() names them.

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
• the PARTITION BY of a window, which makes one partition of the values that are equal under the collation. Under NOCASE, 'A' and 'a' are one partition. The collation comes from the key's column (declared on it, or passed on by a derived table, a WITH, a view or a set operation) or from a COLLATE written in the PARTITION BY.
• join keys
• GROUP BY and DISTINCT, which make one group of the values that are equal under the collation. The group's value is its first row's.
• LIKE and GLOB, which match the text and the pattern under the collation. An ESCAPE character is matched under it too.
• min() and max(), as aggregates and as window functions, which compare under the collation and keep the first of equal values
• set operations:
  • UNION keeps one of each set of rows that are equal under the collation.
  • INTERSECT and EXCEPT match rows by their exact bytes, then keep one of each set of returned rows that are equal under the collation.

A set operation's column takes the collation of the last operand that has one. In UNION BY NAME, this is matched by column name. A run of UNIONs counts as one operation.

count(DISTINCT x) ignores a collation, as in DuckDB.

When both sides of a comparison, a LIKE or a GLOB have different collations, the left side's applies. DuckDB refuses this case.

GROUP BY ALL applies a collation, where DuckDB's ignores it. DuckDB's GROUP BY 1 applies it.

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

A collation is part of a column's type, as in DuckDB:

• A table column can be declared with a collation, in CREATE TABLE t (name VARCHAR COLLATE NOCASE) and in ALTER TABLE … ADD COLUMN. The column compares under it wherever it is read.
• A column keeps its collation when a derived table, a WITH, a view, a VALUES list or a LATERAL subquery passes it on, including into CREATE TABLE AS.
• duckdb_tables() and EXPORT DATABASE write a column's COLLATE back as it was written (COLLATE NoCase stays NoCase), on VARCHAR columns only.
• A collation on a column that is not VARCHAR is an error. So is a collation name DuckDB does not have.
• A subquery's value keeps its column's collation. That covers a scalar subquery, and IN, ANY and ALL of a subquery. These three compare under the subquery column's collation, or the left side's where the subquery column has none, as DuckDB does.
• coalesce, ifnull, nullif and the aggregates that return their argument's type (min, max, first, any_value and the like) keep the collation. string_agg does not.
• A CASE takes the collation of the values it returns, not of its conditions. A comparison's result has no collation.

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 and SQLite's rules for them: NOCASE, which ignores the case of ASCII letters only, RTRIM and BINARY.

In the SQLite dialect, a column keeps its collation, as in SQLite:

• A table column can be declared with a collation, in CREATE TABLE t (v TEXT COLLATE NOCASE) and in ALTER TABLE … ADD COLUMN. The collation can be BINARY, NOCASE or RTRIM, on a column of any type. Any other name is an error, as in SQLite.
• A subquery's, a view's or a WITH's column keeps its collation when its item is a column or a COLLATE. A CREATE TABLE AS column keeps none.
• A comparison uses an explicit COLLATE first, the left side's if both have one. Then it uses the left operand's column's collation, even when that is BINARY. Then the right operand's.
• A function's result and a scalar subquery's value have no collation. LIKE and GLOB take none.
• x IN (a, b) compares under x's collation alone. x IN (a), with a constant a, compares as x = a does. x IN (subquery) compares as x = y does, where y is the subquery's column. When the subquery is a set operation, y is its last operand's column.
• The collation applies to GROUP BY, DISTINCT, DISTINCT aggregates, min() and max(), ORDER BY, an aggregate's ORDER BY, a window's PARTITION BY and ORDER BY, joins and correlated subqueries. USING and NATURAL joins compare under the left table's collation.
• A window's PARTITION BY makes one partition of the values that are equal under the key's collation, as SQLite's does. The key's collation is its column's, or that of a COLLATE written in the PARTITION BY. v || '' has none, as a function's result has none, so PARTITION BY v || '' partitions by the exact text.
• In a set operation, rows compare under the collation of the first operand that has one. A subquery over a set operation takes its first operand's column collations.

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.

Geometry and Geography

GEOMETRY and GEOGRAPHY are types of spatial values: points, line strings, polygons, their multi forms and geometry collections, with optional Z and M values (POINT Z, POINT M, POINT ZM).

search "SELECT name, p, ST_Within(p, ST_GeomFromText('POLYGON ((-74.02 40.70, -73.91 40.70, -73.91 40.88, -74.02 40.88, -74.02 40.70))')) AS in_area, round(ST_Distance_Sphere(p, ST_Point(-73.9857, 40.7484))) AS meters FROM (VALUES ('Empire State Building', ST_Point(-73.9857, 40.7484)), ('LaGuardia Airport', ST_Point(-73.874, 40.7769)), ('Central Park', ST_Point(-73.9654, 40.7829))) t(name, p) ORDER BY meters"

┌───────────────────────┬──────────────────────────┬─────────┬────────┐
│ name                  │ p                        │ in_area │ meters │
│ varchar               │ geometry                 │ boolean │ double │
├───────────────────────┼──────────────────────────┼─────────┼────────┤
│ Empire State Building │ POINT (-73.9857 40.7484) │ true    │    0.0 │
│ Central Park          │ POINT (-73.9654 40.7829) │ true    │ 4200.0 │
│ LaGuardia Airport     │ POINT (-73.874 40.7769)  │ false   │ 9927.0 │
└───────────────────────┴──────────────────────────┴─────────┴────────┘

Spatial types and functions run in the search CLI and on Directors, not on Agents (see In Directors and Agents).

Spatial Types

A GEOMETRY has planar coordinates: X and Y on a flat plane. A GEOGRAPHY holds the same shapes, as longitudes and latitudes in degrees on the earth.

• Each type names a coordinate system. GEOMETRY and GEOGRAPHY use OGC:CRS84, longitude and latitude on WGS 84. GEOMETRY('EPSG:3857') names another one.
• A GEOGRAPHY can also name the algorithm its edges follow: spherical (the default), vincenty, thomas, andoyer or karney, as in GEOGRAPHY('OGC:CRS84', 'karney').
• GEOMETRY(4326) names a coordinate system by its spatial reference id. 4326 is OGC:CRS84, and any other id n is EPSG:n.
• Values of two types with different coordinate systems can't be mixed in one function, or cast into each other.

Geometry Values

Text casts to a geometry as well-known text (WKT), 'POINT (1 2)'::GEOMETRY, and a BLOB as well-known binary (WKB). A geometry casts to VARCHAR as its WKT, and to BLOB as its WKB.

Search keeps every geometry as WKB in one form, ISO WKB in little-endian order, including a geometry that a file wrote in another form. So two equal geometries are equal however they were written.

CSV, JSON and Avro output write a geometry as its WKT.

Spatial Functions

Function	Returns
ST_Point(x, y)	A GEOMETRY point
ST_GeomFromText(wkt), ST_GeomFromWKB(blob)	The GEOMETRY of WKT or WKB
ST_GeogFromText(wkt), ST_GeogFromWKB(blob)	The GEOGRAPHY of WKT or WKB
ST_AsText(g)	WKT
ST_AsWKB(g), ST_AsBinary(g)	WKB, a BLOB
ST_X(p), ST_Y(p)	A point's X and Y
ST_Distance(a, b)	The planar distance between two geometries
ST_Area(g)	The planar area of a geometry
ST_Contains(a, b)	True where a contains b
ST_Within(a, b)	True where a is within b
ST_Intersects(a, b)	True where the two share a point
ST_DWithin(a, b, distance)	True where the planar distance is distance or less
ST_Distance_Sphere(p, q)	The distance in meters between two points on the earth, by the haversine formula on a sphere of 6,371,000 meters
ST_DistanceSphere(p, q)	The distance in meters between two points on the earth, on a sphere of WGS 84's mean radius, 6,371,008.77 meters. ST_DistanceSphere(p, q, radius) uses the radius given

ST_Distance, ST_Area, ST_Contains, ST_Within, ST_Intersects and ST_DWithin are planar, and take GEOMETRY values. A point on a polygon's edge is not inside the polygon (ST_Contains is false), but it does touch it (ST_Intersects is true).

The two sphere distances take points whose X is the longitude and whose Y is the latitude. Both work across the 180° meridian and at the poles. They measure on a sphere, so they can differ from a distance on the ellipsoid by up to about half a percent.

SELECT name, ST_Distance_Sphere(location, ST_Point(-73.9857, 40.7484)) AS meters
FROM 'places.parquet'
WHERE ST_DWithin(location, ST_Point(-73.9857, 40.7484), 0.1)
ORDER BY meters;

Geometry in Files and Tables

Parquet files. Search reads and writes the GEOMETRY and GEOGRAPHY column types, with each row group's bounding box. It also reads files that describe their geometry columns with GeoParquet 1.x metadata.

Delta tables (HyperLake). A table can have GEOMETRY and GEOGRAPHY columns. Such a table uses the geospatial table feature, so its readers need Delta reader version 3 and writer version 7. CREATE TABLE, INSERT, UPDATE, DELETE, MERGE, OPTIMIZE, COPY … (FORMAT delta) and CONVERT TO DELTA all take geometry columns.

ATTACH 'lake' AS lake (TYPE delta);
CREATE TABLE lake.sites (id BIGINT, location GEOMETRY, area GEOGRAPHY);
INSERT INTO lake.sites VALUES (1, 'POINT (10 10)', 'POLYGON ((0 0, 1 0, 1 1, 0 0))');
SELECT id FROM lake.sites WHERE ST_Intersects(location, ST_GeomFromText('POLYGON ((5 5, 15 5, 15 15, 5 15, 5 5))'));

Skipping row groups and files. A filter such as ST_Intersects(location, ST_GeomFromText('POLYGON (…)')) or ST_Distance_Sphere(location, ST_Point(13.4, 52.5)) < 50000 skips the Parquet row groups and the Delta files whose bounding boxes can't match. EXPLAIN shows the box, and EXPLAIN ANALYZE shows how many row groups were skipped:

search "EXPLAIN SELECT count(*) FROM 'places.parquet' WHERE ST_DWithin(g, ST_Point(10, 20), 0.5)"

PROJECTION  count(*)
└── AGGREGATE  aggregates: count(*) · vectorized
    └── FILTER  st_dwithin(g, st_point(10, 20), 0.5) · pushed into the scan: g && BOX(9.5 19.5, 10.5 20.5)
        └── SCAN  'places.parquet' · 1 Parquet file

KQL Spatial Functions

A KQL query of the Enrich processor can call two spatial functions:

• geo_distance_2points(lon1, lat1, lon2, lat2) gives the distance in meters on KQL's sphere of 6,371,010 meters.
• geo_point_in_polygon(lon, lat, polygon) takes a GeoJSON Polygon or MultiPolygon written in the query. Its edges are straight lines in longitude and latitude, while KQL defines them as the shortest paths on the earth, so near a long edge the answer can differ.

Both give NULL for a longitude outside -180 to 180 or a latitude outside -90 to 90.

Spatial Limits

• A Parquet file that Search writes can't have a geometry column beside a LIST, STRUCT or MAP column.
• Search doesn't write to a Delta table that has both a geometry column and an array, struct or map column.
• A Delta table with a geometry column can't use UniForm (Iceberg metadata).
• In a list, a struct or a map, a geometry is kept as its WKT text.
• A UNION, a CASE or a VALUES list of geometries of two different types gives their WKT as text. Cast them to one type first.
• ST_Contains checks each polygon of a multipolygon or a geometry collection on its own. A shape that only two of its polygons together cover is not contained, such as a square that a geometry collection holds as two halves sharing an edge.
• A Parquet geography column whose edge algorithm Search doesn't know reads as WKB, a BLOB.
• Not yet available: GeoJSON functions, buffers, unions and intersections of shapes, transforms between coordinate systems, and spatial indexes.

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_wildcard('*time?ut*')	A term that fits the pattern (see Wildcard Terms)
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.

Wildcard Terms

ts_wildcard(pattern [, max_words]) matches a term that fits a pattern. It is written as the other terms are: message @@ ts_wildcard('*time?ut*'), which matches timeout.

• * matches any run of characters, and ? matches one character. A \ before either makes it a plain character. A letter matches either case.
• With a full-text index, Search matches the pattern against the index's list of words, never against the rows. A pattern that starts with a wildcard, such as *timeout or *gate*, therefore reads only the rows that hold a matching word, not every row. Without an index, Search matches each row's words.
• A pattern can match up to 1,000,000 words of an index. If it matches more, the query fails with an error that names the pattern and the limit. A second argument sets another limit: ts_wildcard('*1*', 5000000).
• It combines with every other term under AND, OR and NOT, and works with bm25() and ts_headline().

Highlighting Matches

ts_headline([config,] document, query [, options]) returns the document's text with the words that the search matched marked. A long text is cut to the part that shows them best. It is PostgreSQL's function. The query is the right side of @@: a string, or a ts_* query.

search "SELECT ts_headline(message, ts_phrase('bad gateway'), 'MaxWords=8, MinWords=4')
        FROM 'logs/*.parquet' WHERE message @@ ts_phrase('bad gateway')
        ORDER BY bm25() DESC LIMIT 10"

The options are PostgreSQL's, with its names and the defaults in the table below. They are written as PostgreSQL writes them, for example 'MaxWords=10, MinWords=5, StartSel=<em>, StopSel=</em>'. A bad option gets PostgreSQL's error, such as MinWords must be less than MaxWords.

Option	Default	Description
StartSel, StopSel	<b>, </b>	What goes before and after each marked word
MaxWords, MinWords	35, 15	The longest and the shortest headline, in words
ShortWord	3	A headline doesn't start or end with a word of this many characters or fewer
HighlightAll	false	true makes the whole document the headline
MaxFragments	0	Above 0, shows up to that many fragments of up to MaxWords words each
FragmentDelimiter	" ... ", three dots with a space on each side	What goes between two fragments
MergePhrases	false	Search's own option. true marks the words of a phrase match as one span (<b>bad gateway</b>). By default each word is marked, as PostgreSQL marks them

• Which part of a long text is shown follows PostgreSQL 18's rules, so a headline of plain text is the same as PostgreSQL's.
• Search marks only the words that the query matched. It marks a phrase's words where they form the phrase, never marks a word under NOT, and marks nothing in a text that the query doesn't match. PostgreSQL marks every word of the query instead.
• A configuration name, such as 'english' or 'simple', is accepted and ignored: the text is always split into words as the full-text index splits it.
• The headline is made only for the rows that the query returns, after its ORDER BY and LIMIT. ORDER BY bm25() DESC LIMIT 10 highlights 10 rows.

Facets

facets(source [, top = n] [, histogram = 'field' | ['field', ...]]) counts the values of several fields among a search's matches, in one pass. It gives the lists shown beside a search's hits (service: api 120, web 45) and the chart of hits over time:

search "SELECT * FROM facets(
          (SELECT service, level, date_trunc('hour', ts) AS hour
           FROM 'logs/*.parquet' WHERE message @@ 'timeout'),
          top = 5, histogram = 'hour')"

The result has a row for each field and value: field, value (as text) and count.

• The source is a subquery. Its select items are the fields, named by their aliases, and its WHERE chooses the rows. A path or a table's name works as a source too, and then every column is a field.
• Each field gives its top values by count, 10 by default. Values with the same count come in the order of the values.
• A histogram field, one name or a list of names, gives every value it holds, in order. Make it a time bucket, such as date_trunc('hour', ts), or a number's bucket, such as floor(latency / 50) * 50. For ranges of a number, make the field a CASE.
• Counts are exact. NULL is counted as a value of its own, after the other values, as GROUP BY counts it.
• All the fields are counted in one pass over the matches. A column that Parquet stores with a dictionary is counted by its codes.
• facets() of a path is allowed only where reading the path directly is.

ts_headline() and facets() run in the search CLI and on Directors, not on Agents (see In Directors and Agents).

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. 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

In NDJSON, a JSON array of numbers is a BIGINT[] or DOUBLE[] list, and the distance functions take it as well. To read one as a FLOAT[] vector, give its type in read_json's columns option.

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.
• A hop() or tumble() duration is a whole number of microseconds: '1500 nanoseconds' is an error.
• 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, .avro, .xlsx), 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|avro|xlsx	The format; xlsx is an Excel workbook (see Writing Workbooks)
HEADER true|false	A CSV header line, or a workbook's row of column names (default true)
SHEET 'name'	The name of a workbook's sheet (default Sheet1)
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|lz4|lz4_raw|uncompressed	A Parquet file's page compression (default snappy); CODEC is the same option. lz4 and lz4_raw both write LZ4_RAW pages, in a Delta table (FORMAT delta) too. brotli is refused with an error that names the codecs Search writes: Search reads Brotli pages but doesn't write them
CODEC deflate|null|snappy|zstandard	An Avro file's block compression (default deflate); COMPRESSION is the same option. See Writing Avro Files
KV_METADATA {key: 'value'}	Entries in an Avro file's header
ROW_GROUP_SIZE n	The rows of a Parquet row group (default 122,880; 1,048,576 for a file with a list, a struct, a map or a vector column, unless VARIANT true writes those columns as text)
KV_METADATA {key: 'value'}	Key-value metadata in a Parquet file's footer
VARIANT true|false	A Parquet file's JSON columns written as VARIANT, with their common fields shredded, instead of as text. With true, the file's list, struct, map and vector columns are written as text (default false; see Parquet VARIANT)
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 a TIMESTAMP_NS to the nanosecond), 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.

A Parquet file's row groups hold 122,880 rows each. A file with a list, a struct, a map or a vector column has row groups of 1,048,576 rows, unless VARIANT true writes those columns as text. ROW_GROUP_SIZE sets another size:

COPY (SELECT * FROM 'logs/*.json') TO 'logs.parquet' (FORMAT parquet, ROW_GROUP_SIZE 1000000);

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 122,880 rows, in a file with a list, a struct, a map or a vector column too, 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, LZ4_RAW 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
ts_headline(), facets()	Returns an error
embed()	Returns an error
INTERVAL	Returns an error
GEOMETRY and GEOGRAPHY types, and the spatial functions	Not available. Spatial queries run on the Director and in the search CLI
patterns()	Returns an error
Vector index	Not read. A nearest-vector query scans its files whole, without an error
Files compressed with bzip2, zip archives, and Parquet pages compressed with Brotli	Returns an error. The Director reads all three (see Compressed Files and Zip Archives)
Windows event logs, Excel workbooks and packet captures (read_evtx, read_xlsx, read_pcap)	Returns an error that says a Director reads them. The Director and the search CLI read all three
Avro files (read_avro, or a path such as 'events.avro' or 'events.avro.gz')	Returns an error that says a Director reads them
Delta tables (delta_scan, table_changes), Iceberg tables (iceberg_scan), and the Delta lake statements, such as ATTACH 'dir' AS lake (TYPE delta) and DESCRIBE DETAIL	Returns an error that says a Director reads them

The errors an Agent gives for the readers it lacks:

Query on an Agent	Error
FROM 'logs.zip', FROM 'logs.zip/*.csv', read_csv('logs.zip/a.csv')	logs.zip: zip archives are read by a director, not by an agent
delta_scan('path')	delta_scan: Delta tables are read by a director, not by an agent
table_changes('path', 0)	table_changes: Delta tables are read by a director, not by an agent
iceberg_scan('path')	iceberg_scan: Iceberg tables are read by a director, not by an agent
ATTACH 'dir' AS lake (TYPE delta), DESCRIBE DETAIL 'path' and the other Delta lake statements	a director reads and writes Delta and Iceberg tables, not an agent
FROM 'events.avro.gz'	read_avro: Avro files are read by a director, not by an agent