---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/recipes/transfer/nginx.md?version=v25.4
  - https://ydb.tech/docs/ru/recipes/transfer/nginx.md?version=v25.4
  - href: en/recipes/transfer/nginx.md
    type: text/markdown
    title: Markdown version
  - href: ../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/recipes/transfer/nginx.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Transfer — streaming NGINX access logs to a table

This guide explains how to set up streaming of NGINX access logs to a YDB [table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4) for further analysis. It covers the default NGINX access log format. To learn more about the NGINX log format and how to configure it, see the [NGINX documentation](https://docs.nginx.com/nginx/admin-guide/monitoring/logging/#set-up-the-access-log).

The default NGINX access log format is as follows:

```txt
$remote_addr - $remote_user [$time_local] "$request" $status $body_bytes_sent "$http_referer" "$http_user_agent"
```

Example:

```txt
::1 - - [01/Sep/2025:15:02:47 +0500] "GET /favicon.ico HTTP/1.1" 404 181 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36"
::1 - - [01/Sep/2025:15:02:51 +0500] "GET / HTTP/1.1" 200 409 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36"
::1 - - [01/Sep/2025:15:02:51 +0500] "GET /favicon.ico HTTP/1.1" 404 181 "http://localhost/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36"
```

This guide covers the following steps:

* [creating a table](#step1) to write the data to;
* [creating the transfer](#step2);
* [verifying the table contents](#step3).

## Prerequisites

To complete the examples in this guide, it needs:

* A YDB cluster, version 25.2 or later. For instructions on how to install a single-node YDB cluster, see the [guide](https://ydb.tech/docs/en/quickstart.md?version=v25.4). For recommendations on deploying YDB for production use, see this [guide](https://ydb.tech/docs/en/devops/deployment-options/index.md?version=v25.4).

* An installed [NGINX HTTP server](https://nginx.org/) with access logging enabled, or access to NGINX access logs from another server.

* A configured process to stream NGINX access logs from a file to the `transfer_recipe/access_log_topic` topic. For example, you can use [Kafka Connect](https://ydb.tech/docs/en/reference/kafka-api/connect/index.md?version=v25.4) [configured](https://ydb.tech/docs/en/reference/kafka-api/connect/connect-examples.md?version=v25.4#file-to-topic) to stream data from a file to a topic.

## Step 1. Create a table {#step1}

Create a [table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4) that will receive data from the `transfer_recipe/access_log_topic` topic. You can do this using a [YQL query](https://ydb.tech/docs/en/yql/reference/syntax/create_table/index.md?version=v25.4):

```yql
CREATE TABLE `transfer_recipe/access_log` (
  partition Uint32 NOT NULL,
  offset Uint64 NOT NULL,
  line Uint64 NOT NULL,
  remote_addr String,
  remote_user String,
  time_local Timestamp,
  request_method String,
  request_path String,
  request_protocol String,
  status Uint32,
  body_bytes_sent Uint64,
  http_referer String,
  http_user_agent Utf8,
  PRIMARY KEY (partition, offset, line)
);
```

The `transfer_recipe/access_log` table has three service columns:

* `partition` — the ID of the topic [partition](https://ydb.tech/docs/en/concepts/glossary.md?version=v25.4#partition) the message was received from;
* `offset` — [the sequence number](https://ydb.tech/docs/en/concepts/glossary.md?version=v25.4#offset) that identifies the message within its partition;
* `line` — the sequence number of the log line within the message.

Together the `partition`, `offset` and `line` columns uniquely identify each line from the access log file.

If you need to store access log data for a limited time, you can configure the [automatic deletion](https://ydb.tech/docs/en/concepts/ttl.md?version=v25.4) of old table rows. For example, to set a retention period of 24 hours, you can use a [YQL query](https://ydb.tech/docs/en/yql/reference/recipes/ttl.md?version=v25.4):

```yql
ALTER TABLE `transfer_recipe/access_log` SET (TTL = Interval("PT24H") ON time_local);
```

## Step 2. Create a transfer {#step2}

After creating the topic and the table, you need to create a data [transfer](https://ydb.tech/docs/en/concepts/transfer.md?version=v25.4) that will move messages from the topic to the table. You can do this using a [YQL query](https://ydb.tech/docs/en/yql/reference/syntax/create-transfer.md?version=v25.4):

```yql
$transformation_lambda = ($msg) -> {
    -- Function to convert a log line into a table row
    $line_lambda = ($line) -> {
        -- First, split the line by the " (double quote) character to separate strings that may contain spaces.
        -- The strings themselves cannot contain double quotes; instead, they are replaced with the \x22 character sequence.
        $parts = String::SplitToList($line.1, '"');
        -- Split each resulting part that isn't a quoted string by spaces.
        $info_parts = String::SplitToList($parts[0], " ");
        $request_parts = String::SplitToList($parts[1], " ");
        $response_parts = String::SplitToList($parts[2], " ");
        -- Convert the date to the Datetime type
        $dateParser = DateTime::Parse("%d/%b/%Y:%H:%M:%S");
        $date = $dateParser(SUBSTRING($info_parts[3], 1));

        -- Return a structure where each named field corresponds to a table column.
        -- Important: The data types of the named fields must match the data types of the table columns. For example, if a column is of type Uint32,
        -- the value of the named field must also be of type Uint32. Otherwise, an explicit CAST is required.
        -- Values for NOT NULL columns must be extracted from optional types using the Unwrap function.
        return <|
            partition: $msg._partition,
            offset: $msg._offset,
            line: $line.0,
            remote_addr: $info_parts[0],
            remote_user: $info_parts[2],
            time_local: DateTime::MakeTimestamp($date),
            request_method: $request_parts[0],
            request_path: $request_parts[1],
            request_protocol: $request_parts[2],
            status: CAST($response_parts[1] AS Uint32),
            body_bytes_sent: CAST($response_parts[2] AS Uint64),
            http_referer: $parts[3],
            http_user_agent: CAST(String::CgiUnescape($parts[5]) AS Utf8) -- Explicitly cast to Utf8, because the http_user_agent column is of type Utf8, not String
        |>;
    };


    $split = String::SplitToList($msg._data, "\n"); -- If a message contains multiple log lines, split it into individual lines
    $lines = ListFilter($split, ($line) -> { -- Filter out empty lines, which can be caused by a trailing \n character
        return LENGTH($line) > 0;
    });

    -- Convert each access log line into a table row
    return ListMap(ListEnumerate($lines), $line_lambda);
};

CREATE TRANSFER `transfer_recipe/access_log_transfer`
  FROM `transfer_recipe/access_log_topic` TO `transfer_recipe/access_log`
  USING $transformation_lambda;
```

In this example:

* `$transformation_lambda` - a transformation rule for converting a topic message into table columns. Each access log line within the message is processed individually using `line_transformation_lambda`;
* `$line_lambda` - a transformation rule for converting a single access log line into a table row;
* `$msg` - variable that contains the topic message being processed.

## Step 3. Verify the table contents {#step3}

After writing messages to the `transfer_recipe/access_log_topic` topic, records will appear in the `transfer_recipe/access_log` table after a short delay. You can verify this using a [YQL query](https://ydb.tech/docs/en/yql/reference/syntax/select/index.md?version=v25.4):

```yql
SELECT *
FROM `transfer_recipe/access_log`;
```

Query result:

| # | partition | offset | line | remote_addr | remote_user | time_local | request_method | request_path | request_protocol | status | body_bytes_sent | http_referer | http_user_agent |
|---|-----------|--------|------|-------------|-------------|------------|----------------|---------------|------------------|--------|-----------------|--------------|-----------------|
| 1 | 0 | 2 | 0 | ::1 | - | 2025-09-01T15:02:51.000000Z | GET | /favicon.ico | HTTP/1.1 | 404 | 181 | `http://localhost/` | Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36|
| 2 | 0 | 1 | 0 | ::1 | - | 2025-09-01T15:02:51.000000Z | GET | / | HTTP/1.1 | 200 | 409 | - | Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36 |
| 3 | 0 | 0 | 0 | ::1 | - | 2025-09-01T15:02:47.000000Z | GET | /favicon.ico | HTTP/1.1 | 404 | 181 | - | Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 YaBrowser/25.6.0.0 Safari/537.36 |

<!-- source: en/recipes/transfer/_includes/batching.md -->
Rows are not added to the table for each message received from the topic; instead, they are buffered and inserted in batches. By default, data is written to the table every 60 seconds or when the volume of accumulated data reaches 8 MB. These parameters can be explicitly configured when [creating](https://ydb.tech/docs/en/yql/reference/syntax/create-transfer.md?version=v25.4) a transfer or [modified](https://ydb.tech/docs/en/yql/reference/syntax/alter-transfer.md?version=v25.4) later.
<!-- endsource: en/recipes/transfer/_includes/batching.md -->

## Conclusion

This guide demonstrates how to stream NGINX access logs to a YDB table. You can process logs of any other text format in a similar way: create a table to store the required data from the log, and write a [lambda function](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=v25.4#lambda) to correctly transform the log lines into table rows.

## See Also

* [Data transfer](https://ydb.tech/docs/en/concepts/transfer.md?version=v25.4)
* [Transfer — quick start](https://ydb.tech/docs/en/recipes/transfer/quickstart.md?version=v25.4)
* [Lambda function](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=v25.4#lambda)
* [CREATE TABLE](https://ydb.tech/docs/en/yql/reference/syntax/create_table/index.md?version=v25.4)
* [CREATE TOPIC](https://ydb.tech/docs/en/yql/reference/syntax/create-topic.md?version=v25.4)
* [CREATE TRANSFER](https://ydb.tech/docs/en/yql/reference/syntax/create-transfer.md?version=v25.4)
* [UNWRAP function](https://ydb.tech/docs/en/yql/reference/builtins/basic.md?version=v25.4#unwrap)
* [COALESCE function](https://ydb.tech/docs/en/yql/reference/builtins/basic.md?version=v25.4#coalesce)