> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> This engine allows processing of application log files as a stream of records.

# FileLog table engine

This engine allows processing of application log files as a stream of records.

`FileLog` lets you:

* Subscribe to log files.
* Process new records as they are appended to subscribed log files.

<h2 id="creating-a-table">
  Creating a table
</h2>

```sql theme={null}
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = FileLog('path_to_logs', 'format_name') SETTINGS
    [poll_timeout_ms = 0,]
    [poll_max_batch_size = 0,]
    [max_block_size = 0,]
    [max_threads = 0,]
    [poll_directory_watch_events_backoff_init = 500,]
    [poll_directory_watch_events_backoff_max = 32000,]
    [poll_directory_watch_events_backoff_factor = 2,]
    [handle_error_mode = 'default']
```

Engine arguments:

* `path_to_logs` – Path to log files to subscribe. It can be path to a directory with log files or to a single log file. A relative path is resolved against the `user_files_path` directory, like in the [file](/reference/functions/table-functions/file) table function; a relative path that is already inside `user_files_path` from the working directory of the server (for example, `user_files/my_app/app.log` when the server runs in its data directory, as in the official Docker image) keeps that meaning. The file name in the path can have [globs](/reference/functions/table-functions/file#globs-in-path) (`*`, `?`, `{abc,def}`, `{N..M}`) to read only the matching files of the directory, see [Selecting files with globs](#selecting-files-with-globs). Note that ClickHouse allows only paths inside `user_files` directory.
* `format_name` - Record format. Note that FileLog process each line in a file as a separate record and not all data formats are suitable for it.

Optional parameters:

* `poll_timeout_ms` - Timeout for single poll from log file. Default: [stream\_poll\_timeout\_ms](/reference/settings/session-settings/stream#stream_poll_timeout_ms).
* `poll_max_batch_size` — Maximum amount of records to be polled in a single poll. Default: [max\_block\_size](/reference/settings/session-settings/max#max_block_size).
* `max_block_size` — The maximum batch size (in records) for poll. Default: [max\_insert\_block\_size](/reference/settings/session-settings/max-insert#max_insert_block_size).
* `max_threads` - Number of max threads to parse files, default is 0, which means the number will be max(1, physical\_cpu\_cores / 4).
* `poll_directory_watch_events_backoff_init` - The initial sleep value for watch directory thread. Default: `500`.
* `poll_directory_watch_events_backoff_max` - The max sleep value for watch directory thread. Default: `32000`.
* `poll_directory_watch_events_backoff_factor` - The speed of backoff, exponential by default. Default: `2`.
* `handle_error_mode` — How to handle errors for FileLog engine. Possible values: default (a direct `SELECT` throws an exception if a record fails to parse; while the table streams into materialized views, such a record is skipped and the error is written to the server log), stream (the exception message and raw record will be saved in virtual columns `_error` and `_raw_record`).

<h2 id="description">
  Description
</h2>

The delivered records are tracked automatically, so each record in a log file is only counted once. A file that is shorter than the offset recorded for it when it is next read, as after `logrotate` with `copytruncate`, is read again from the beginning. A truncation is not detected if the file grows back to at least that offset before it is next read. A file with several names in the directory (hard links) is read under one of them.

A file that cannot be opened (a symlink whose target was removed, a file not readable by the server, or the file of a single-file table that was removed) is skipped with an error in the server log and retried until it can be opened while the table is loaded; a file that was missing is then read from its start. A symlink is read only if its target exists when the table finds it.

`SELECT` is not particularly useful for reading records (except for debugging), because each record can be read only once. It is more practical to create real-time threads using [materialized views](/reference/statements/create/view). To do this:

1. Use the engine to create a FileLog table and consider it a data stream.
2. Create a table with the desired structure.
3. Create a materialized view that converts data from the engine and puts it into a previously created table.

When the `MATERIALIZED VIEW` joins the engine, it starts collecting data in the background. This allows you to continually receive records from log files and convert them to the required format using `SELECT`.
One FileLog table can have as many materialized views as you like, they do not read data from the table directly, but receive new records (in blocks), this way you can write to several tables with different detail level (with grouping - aggregation and without).

Example:

```sql theme={null}
CREATE TABLE logs (
    timestamp UInt64,
    level String,
    message String
  ) ENGINE = FileLog('my_app/app.log', 'JSONEachRow');

CREATE TABLE daily (
    day Date,
    level String,
    total UInt64
  ) ENGINE = SummingMergeTree
  PARTITION BY toYYYYMM(day)
  ORDER BY (day, level);

CREATE MATERIALIZED VIEW consumer TO daily
    AS SELECT toDate(toDateTime(timestamp)) AS day, level, count() AS total
    FROM logs GROUP BY day, level;

SELECT level, sum(total) FROM daily GROUP BY level;
```

To stop receiving streams data or to change the conversion logic, detach the materialized view:

```sql theme={null}
DETACH TABLE consumer;
ATTACH TABLE consumer;
```

If you want to change the target table by using `ALTER`, we recommend disabling the material view to avoid discrepancies between the target table and the data from the view.

<h2 id="selecting-files-with-globs">
  Selecting files with globs
</h2>

When the file name in `path_to_logs` has globs, the table reads the files of that directory whose names match, including the ones that appear later. Globs are not supported in the directory part of the path.

A file that the table reads keeps being read when it is renamed to a name that does not match, until it is removed from the directory. A file that is created and renamed to a name that does not match while the table is detached or the server is stopped is not read. On macOS, where the directory is watched by comparing its listings, the same holds for a file that is created and renamed to a name that does not match before the table lists it. This is what log rotation needs. For example, `logrotate` with `compress` and `delaycompress` keeps the directory like this:

```text theme={null}
app.log         the file the application writes
app.log.1       the previous file, renamed by logrotate, compressed on the next rotation
app.log.2.gz    older files, compressed
```

A table on `FileLog('/var/lib/clickhouse/user_files/my_app/*.log', 'JSONEachRow')` reads `app.log`; after the rotation it keeps reading `app.log.1`, so the lines the application writes there before it reopens its log are not lost, and it never reads the compressed files. Make sure the glob does not match the compressed file names.

<h2 id="virtual-columns">
  Virtual columns
</h2>

* `_filename` - Name of the log file. Data type: `LowCardinality(String)`.
* `_offset` - Offset in the log file. Data type: `UInt64`.

Additional virtual columns when `handle_error_mode='stream'`:

* `_raw_record` - Raw record that couldn't be parsed successfully. Data type: `Nullable(String)`.
* `_error` - Exception message happened during failed parsing. Data type: `Nullable(String)`.

Note: `_raw_record` and `_error` virtual columns are filled only in case of exception during parsing, they are always `NULL` when message was parsed successfully.

<h2 id="data-durability">
  Data durability
</h2>

The `FileLog` engine records the offset it has consumed for a chunk before the insert that chunk belongs to has been committed, so an interrupted server can leave the recorded offset ahead of the data that reached the target table. On restart each log file resumes from the offset recorded in its metadata directory, so those rows are never re-read: they are lost with no error and `count()` is simply smaller. An ordinary process failure is enough to expose this, and it does not require a power loss, because the offset is recorded in a metadata file that is renamed into place while the target part is still being written.

A loss of the OS page cache can additionally discard data that had already been written to the target table; examples are a device-level power loss and an unclean host or kernel reset. The metadata files holding the offsets are themselves written without an fsync of the file or of its directory, so they carry no durability guarantee of their own either.

Unlike the message-broker engines, `FileLog` cannot be protected against this by making the target durable first. Because the offset is recorded from inside the reading pipeline, before the insert it belongs to has finished, setting `fsync_after_insert = 1` on the target `MergeTree` tables does not establish the inserted part as durable before the offset advances. Treat `FileLog` consumption as best-effort tailing of local files: where no rows may be lost, keep the source log files until the consumed data has been verified in the target, so that consumption can be repeated. [SYSTEM RESET FILELOG](/reference/statements/system#reset-filelog), or dropping and recreating the table, discards the recorded offsets and re-reads the files from the beginning.