I tried continuously ingesting logs placed in S3 into TiDB Cloud Lake

Hello, I'm sora from the Game Solutions Department.
This time, I tried to see if logs continuously output by an app to S3 could be continuously ingested using TiDB Cloud Lake's AWS – SQS(S3) data source and Integration Task.

Conclusion First

• By routing S3 event notifications to SQS, files placed in S3 were automatically ingested into Lake using only console screen operations
• The actual ingestion was a COPY INTO executed for each file delivered via event
• The settings to delete S3 files after ingestion and to prevent double-ingesting the same file corresponded directly to COPY INTO's PURGE / FORCE options
• If there are rows with missing columns or corrupted rows, the default behavior is for the entire file to fail and retries to continue.
  Setting it to continue discards only those rows and ingests the rest

Architecture

The architecture for this setup is as follows.

• The ECS Fargate app writes one line of JSON log per request to standard output
• FireLens (Fluent Bit) collects them every 10 seconds and places them in S3 as a file with one JSON per line
• Each time a file is created, S3 sends an ObjectCreated event to SQS
• TiDB Cloud Lake assumes an IAM role, receives events from SQS, reads the files written in them from S3, and ingests them

Among Lake's data sources, the following three can ingest files placed in S3.

Of these, AWS – SQS(S3) was the one that could ingest app log files using an IAM role.
The TiDB data source can also use an IAM role, but it can only read Dumpling output.
This point is covered in the following article.

https://dev.classmethod.jp/articles/tidb-cloud-lake-migrate-from-tidb/

AWS Setup

The AWS side was built with Terraform.
Here I only show the parts related to ingestion.

FireLens Output

Specify the Fluent Bit S3 output in the app container's logConfiguration.

logConfiguration = {
  logDriver = "awsfirelens"
  options = {
    Name            = "s3"
    region          = "ap-northeast-1"
    bucket          = aws_s3_bucket.logs.id
    upload_timeout  = "10s"
    total_file_size = "5M"
    use_put_object  = "On"
    s3_key_format   = "/app-logs/%Y/%m/%d/%H%M%S-$UUID.json"
    json_date_key   = "false"
  }
}

The default for upload_timeout is 10 minutes, so it's set to 10 seconds to make it easier to track behavior.
While there are no log lines, no file is created.

The content of files placed in S3 is one JSON per line.
The log router's enable-ecs-log-metadata also appends ECS metadata such as ecs_cluster and container_name to the same line.

{"timestamp":"2026-09-25T03:29:52.310Z","level":"INFO","service":"lake-ingest-app","request_id":"20b41581-...","method":"GET","path":"/api/items","status":200,"latency_ms":10.95,"user_id":90010,"user_agent":"curl/8.7.1","message":"request completed","container_id":"...","container_name":"app","source":"stdout","ecs_cluster":"lake-ingest","ecs_task_arn":"arn:aws:ecs:ap-northeast-1:<account ID>:task/lake-ingest/...","ecs_task_definition":"lake-ingest:3"}

S3 Event Notifications and SQS

Create a standard SQS queue and send S3's ObjectCreated events to it.
Since Lake's AWS – SQS(S3) data source only supports standard queues, FIFO queues cannot be used.
The required settings are described in the following official documentation.

https://docs.pingcap.com/tidbcloudlake/amazon-sqs-s3-iam-role/

resource "aws_s3_bucket_notification" "logs" {
  bucket = aws_s3_bucket.logs.id

  queue {
    queue_arn     = aws_sqs_queue.s3_events.arn
    events        = ["s3:ObjectCreated:*"]
    filter_prefix = "app-logs/"
    filter_suffix = ".json"
  }

  depends_on = [aws_sqs_queue_policy.s3_events]
}

The queue policy only allows sqs:SendMessage from this bucket.

Note that when you configure notifications, S3 sends one test message called s3:TestEvent.
Since it's a message that doesn't point to a file, I manually deleted it before creating the Lake task.

IAM Role Assumed by Lake

The trust policy includes the two Platform role ARNs and External ID displayed in the Lake console.
They are shown on the data source creation screen.

The permissions are S3 read access and SQS message receive/delete.
Since I'll be testing the setting to delete S3 files after ingestion, s3:DeleteObject is also included.

{
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": "arn:aws:s3:::lake-ingest-<account ID>"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::lake-ingest-<account ID>/app-logs/*"
    },
    {
      "Effect": "Allow",
      "Action": [
        "sqs:ReceiveMessage",
        "sqs:DeleteMessage",
        "sqs:GetQueueAttributes",
        "sqs:ChangeMessageVisibility"
      ],
      "Resource": "arn:aws:sqs:ap-northeast-1:<account ID>:lake-ingest-s3-events"
    }
  ]
}

Registering the Data Source

Go to Data > Data Sources > Create and select AWS – SQS(S3) as the Service.

The two Platform role ARNs and External ID shown in the Config section are the values to put in the IAM role's trust policy.
The values to enter are as follows.

SQS messages contain the bucket name and key of the created file as-is.
Lake does not look at everything under a prefix; it only reads the files written in the messages.
Prefix Filter / Suffix Filter are settings that filter which files to ingest based on whether the key matches the conditions.
In this case, they are set to the same values as the S3 notifications.

Once Test Connectivity passes, save with OK.

Creating the Integration Task

Go to Data > Data Integrations > Create and select the data source you created.

File Type can be selected from three options: CSV / PARQUET / NDJSON.
The file extension is .json, but since the content is one JSON per line, select NDJSON.
When NDJSON is selected, the CSV delimiter and header items disappear.

Leave the three Advanced Options at their defaults for now.

The behavior of each will be verified in the latter half.

Clicking Next shows a preview.

At this point there were 3 files in S3, but only 2 appeared in the preview.
The one that didn't appear was a file created before event notifications were configured.
The preview is looking at event files in SQS, not the S3 listing.

On the next screen, specify the Warehouse and the target table for ingestion.
The database was created in advance using Worksheet.

CREATE DATABASE IF NOT EXISTS ingest;

Columns and types are inferred from the preview data.
timestamp was inferred as VARCHAR, so I changed it to TIMESTAMP.
This is because it's a string like "2026-09-25T02:36:20.351Z" in the JSON, so it's not identified as a time column.
By changing it to TIMESTAMP, it was converted directly during ingestion.

When created with Create, the task is created in a Stopped state.

Verifying Ingestion

When Run is pressed, the 2 events that had arrived in SQS were each executed once.
Events that arrived before the task was created are also ingested if they remain in the queue.

After ingestion, the SQS messages were deleted.
Since Clean Up Original Files is Off, the S3 files remain.

What is being executed in the background can be seen in Monitoring > SQL History.
However, it won't appear if you leave User set to yourself.
Ingestion is executed by a user called system:serviceaccount:<tenant ID>, and the User Agent was lake-sqs-s3-consumer.

Opening one shows that the content is COPY INTO.

COPY INTO `ingest`.`access_logs` (`method`, `latency_ms`, ..., `message`)
FROM 's3://lake-ingest-<account ID>/app-logs/2026/09/25/033258-efa5uDeg.json'
CONNECTION = (external_id = '***', role_arn = '***')
FILE_FORMAT = (type = NDJSON)
PURGE = true
FORCE = false
DISABLE_VARIANT_CHECK = false
ON_ERROR = abort
RETURN_FAILED_ONLY = false

FROM contains only the single file delivered by the event, and authentication is passed directly via CONNECTION without using a Stage.
The screen settings corresponded directly to COPY INTO options.

The Warehouse was running on sora-blog-test, which was specified in the task.
Since the Warehouse starts up each time a file arrives, in an environment where logs keep flowing, the Warehouse will keep running.

When the app was called once in this state, a file was created in S3 and COPY INTO was executed immediately, increasing the row count.
The wait time was almost entirely the time for Fluent Bit to bundle the file (upload_timeout), with almost no wait on the Lake side.

Note that timestamp is stored as UTC, but it was displayed converted to Japan time in Worksheet.
When comparing with the S3 file timestamps, they appear offset by 9 hours.

Data from app calls made while the task was Stopped was also ingested when Start was pressed.
Since events remain in SQS, no data is missed while stopped, as long as it's within SQS's message retention period.

Verifying Clean Up Original Files

In the task editing screen, change Clean Up Original Files to On.
This can be changed even after creation.

When the app was called in this state, the S3 file was deleted after being ingested.
COPY INTO was also executed with PURGE = true.

Files ingested when it was Off remained in S3.
Only files ingested after turning it On were deleted.

Verifying Duplicate Ingestion

Both S3 event notifications and SQS standard queues can deliver the same message twice.
So I re-uploaded a file that had already been ingested with the same key and same content, and sent the same file's event again.

aws s3 cp s3://lake-ingest-<account ID>/app-logs/2026/09/25/032952-L28IvVIt.json ./032952.json
aws s3 cp ./032952.json s3://lake-ingest-<account ID>/app-logs/2026/09/25/032952-L28IvVIt.json

COPY INTO was executed, but Scan Rows was 0 and the row count did not increase.

Since Allow Duplicate Imports is Off (FORCE = false), it was skipped as an already-ingested file.
Even if the same event is delivered twice, it won't be ingested twice.

Clean Up was On at this time, but the skipped file was not deleted from S3.
PURGE only deletes files that were ingested in that execution.
Even if you intend to keep S3 empty with Clean Up, skipped files will remain.

Verifying Behavior When There Are Rows That Cannot Be Ingested

Finally, I placed a file directly in S3 with rows mixed in that cannot be ingested.

When Error Handling is abort

First, I placed a row with some of the table's columns missing.
It was a row from the app log with the ECS metadata columns removed.

BadBytes. Code: 1046, Text = Missing value for column 4 (container_name String NULL). current FILE_FORMAT option: MISSING_FIELD_AS=ERROR
at file 'app-logs/2026/09/25/broken-test-01.json', line 0.

NDJSON ingestion defaults to MISSING_FIELD_AS=ERROR.
If there is even one row with a missing table column, the entire file fails.
If rows with different fields are mixed in, such as app startup logs versus request logs, that alone will cause it to stop.

Failed messages were not removed from SQS and were retried repeatedly.
Run History's Last Run retains the same execution error, updated with each retry.

When Error Handling is continue

I replaced the file with 2 rows — one correct row with all columns and one row that is not JSON — and changed Error Handling to continue.

COPY INTO was executed with ON_ERROR = continue, and only the correct row was ingested.
The non-JSON row was discarded.
The changed settings were also applied to messages that had been waiting to retry.

The choice would seem to be: use abort if you want to notice and stop, or continue if you want to keep ingesting even with some missing data.

Supplement: Building with SQL

There is also a method of periodically running COPY INTO with a SQL Task without using SQS.
The official guide ingests logs placed in S3 with Vector in this form.

https://docs.pingcap.com/tidbcloudlake/ingest-json-logs-with-vector-cloud/

On the AWS side, only S3 is needed, but ingestion happens at intervals set in the Task, not when a file is created.

Closing

This time, I tried to see if logs continuously output by an app to S3 could be continuously ingested using TiDB Cloud Lake's AWS – SQS(S3) data source and Integration Task.
By routing S3 event notifications to SQS, I was able to keep ingesting logs placed in S3 using only screen operations.
I hope this article is helpful to someone.