---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: drain3
excerpt: |
  DuckDB Community Extensions
  Drain log template mining in SQL, with clusters identical to logpai/Drain3

extension:
  name: drain3
  description: Drain log template mining in SQL, with clusters identical to logpai/Drain3
  version: 0.1.0
  language: Rust
  build: cargo
  license: Apache-2.0
  excluded_platforms: "linux_amd64_musl"
  requires_toolchains: "rust;python3"
  maintainers:
    - akshatagarwl

repo:
  github: akshatagarwl/duckdb-drain3
  ref: 3f6d36bd1615c5b2470f2bfbf3ff69d6d661410d

docs:
  hello_world: |
    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;
  extended_description: |
    `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](https://crates.io/crates/drain3) Rust crate, whose clusters and templates are identical
    to [logpai/Drain3](https://github.com/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](https://github.com/akshatagarwl/duckdb-drain3#readme) for examples, limits and
    benchmarks.

extension_star_count: 0
extension_star_count_pretty: 0
extension_download_count: 49
extension_download_count_pretty: 49
image: '/images/community_extensions/social_preview/preview_community_extension_drain3.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), 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.

<div class="extension_compatibility_table"></div>

| 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
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|  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 <MASK> 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 <IP>', '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 <*> / <MASK> 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 <IP>', ['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': '<IP>'}]) 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

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

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

### Added Settings

<div class="extension_settings_table"></div>

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