Search Shortcut cmd + k | ctrl + k

drain3

Drain log template mining in SQL, with clusters identical to logpai/Drain3

Maintainer(s): akshatagarwl

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Platform	1.4.4	1.4.5	1.5.5	1.5.6	2.0.0
Linux AMD64
Linux ARM64
macOS AMD64
macOS ARM64
Windows AMD64
Windows ARM64
WebAssembly

Installing and Loading

INSTALL drain3 FROM community;
LOAD drain3;

Example

CREATE TABLE logs AS SELECT * FROM (VALUES
  (1, 'user alice logged in from 10.0.0.1 after 3 attempts'),
  (2, 'user bob logged in from 10.0.0.7 after 1 attempts'),
  (3, 'connection closed by peer 10.1.1.1')) t(ts, message);

-- Every row with its template and parameters, trained in ts order.
FROM drain_parse('logs', 'message', order_by := 'ts');

-- Train a model once, then match new lines in parallel.
CREATE TABLE models AS SELECT drain_train(message ORDER BY ts) AS model FROM logs;
SELECT drain_match(model, 'user carol logged in from 10.9.8.7 after 2 attempts') FROM models;

-- Top templates.
SELECT unnest(drain_clusters(model), recursive := true) FROM models;

About drain3

drain3 runs the Drain log parsing algorithm inside DuckDB. It groups log lines into templates such as user <*> logged in from <*> and extracts the variable parts. It is built on the drain3 Rust crate, whose clusters and templates are identical to logpai/Drain3 on all 16 Loghub-2k datasets.

drain_parse(table, message_column, order_by := ...): every row plus cluster_id, template and params.
drain_train(message ORDER BY ts): an aggregate that returns the trained model as a BLOB.
drain_match(model, message): matches a message without training. It runs in parallel.
drain_clusters(model), drain_params(template, message), drain_render(template, params).

Training is order-dependent. Use ORDER BY for results identical to logpai/Drain3. Defaults (sim_threshold, depth, max_children, masks, …) match logpai/Drain3. See the README for examples, limits and benchmarks.

Added Functions

function_name	function_type	description	comment	examples
drain_clusters	scalar	The clusters of a model as a list of {cluster_id, size, template}, by id.	NULL	[SELECT c.* FROM (SELECT unnest(drain_clusters(drain_train(message ORDER BY ts))) AS c FROM logs) ORDER BY c.size DESC]
drain_match	scalar	Match a message against a trained model without changing it. Returns {cluster_id, template, params}, or NULL when no cluster matches. search := 'never' (default, logpai's match), 'fallback' or 'always'.	NULL	[SELECT drain_match(m.model, l.message).template FROM logs l, models m]
drain_params	scalar	The variable parts of a message under a template: one value per <*> or token, in order. NULL when the message does not fit the template. Same rules as logpai/Drain3's extract_parameters.	NULL	[SELECT drain_params('user <*> logged in from ', 'user bob logged in from 10.0.0.1')]
drain_parse	table_macro	Parse a table or view of log lines with Drain: every row plus cluster_id, template (the cluster's final template) and params. Rows are trained one by one in order_by order (default: scan order), single-threaded, exactly like logpai/Drain3.	NULL	[FROM drain_parse('logs', 'message', order_by := 'ts')]
drain_render	scalar	Fill the <*> / slots of a template with params, in order: the inverse of drain_params. Whitespace comes out normalized (single spaces), as in templates.	NULL	[SELECT drain_render('user <*> logged in from ', ['bob', '10.0.0.1'])]
drain_train	aggregate	Train a Drain log template model on the messages of a group and return it as a BLOB. Training is order-dependent: write drain_train(msg ORDER BY ts) for a deterministic, logpai/Drain3-identical model. Named parameters: sim_threshold, depth, max_children, max_clusters, extra_delimiters, masks, param_str, parametrize_numeric_tokens.	NULL	[SELECT drain_train(message ORDER BY ts) FROM logs, SELECT drain_train(message ORDER BY ts, masks := [{'pattern': '\d+.\d+.\d+.\d+', 'mask': ''}]) FROM logs]
drain_train_ids	aggregate	Like drain_train, but also returns the cluster id each input row was assigned while training (cluster_ids, in input order), and the final template and size of every cluster (templates[id], sizes[id]). The building block of drain_parse.	NULL	[SELECT drain_train_ids(message ORDER BY ts) FROM logs]

Overloaded Functions

This extension does not add any function overloads.

Added Types

type_name	type_size	logical_type	type_category	internal
STRUCT	0	STRUCT	COMPOSITE	true
VARCHAR	16	VARCHAR	STRING	true

Added Settings

name	description	input_type	scope	aliases	typed_value
temp_directory	Set the directory to which temporary files are written.	VARCHAR	GLOBAL	[]	post.db.tmp

DuckDB Home Code of Conduct Trademark Use Blog

drain3

In this article