READ_TEXT

Reference material for READ_TEXT function

A table-valued function (TVF) that reads text files line by line. READ_TEXT returns a table with a single content column of type TEXT NULL, where each row contains one line from the input file. Unlike READ_CSV, READ_TEXT has a fixed output schema and does not parse or split fields — each line is returned as-is.

READ_TEXT is useful for ingesting unstructured or semi-structured text data such as log files, where standard CSV parsing would fail due to delimiters or special characters in the content.

Syntax

READ_TEXT (
  URL => <url>
  [, COMPRESSION => <file_compression>]
  [, AWS_ACCESS_KEY_ID => <aws_access_key_id>]
  [, AWS_SECRET_ACCESS_KEY => <aws_secret_access_key>]
  [, AWS_SESSION_TOKEN => <aws_session_token>]
  [, AWS_ROLE_ARN => <aws_role_arn>]
  [, AWS_ROLE_EXTERNAL_ID => <aws_role_external_id>]
)

Parameters

Parameter	Description	Supported input types
URL	The location of the file in an Amazon S3 bucket. The expected format is s3://{bucket_name}/{full_file_path_glob_pattern}.	TEXT
COMPRESSION	The compression type of the input file. If not set, compression is inferred from the file extension.	TEXT
AWS_ACCESS_KEY_ID	The AWS access key ID.	TEXT
AWS_SECRET_ACCESS_KEY	The AWS secret access key.	TEXT
AWS_SESSION_TOKEN	The AWS session token.	TEXT
AWS_ROLE_ARN	The AWS role ARN.	TEXT
AWS_ROLE_EXTERNAL_ID	The AWS role external ID.	TEXT

• The URL can be passed as either the first positional parameter or a named parameter.
• If you provide either AWS_ACCESS_KEY_ID or AWS_SECRET_ACCESS_KEY, you must provide both.
• Providing an AWS session token is optional.
• Credentials are not required for accessing public buckets.
• The URL also accepts the upload:// scheme to read a file sent in the same HTTP request. See Upload and query local files.

Return type

The result is a table with a single column:

Column name	Type	Description
content	TEXT NULL	One line from the input file.

A special column $source_file_name can be used to identify the source file of each row in the result set.

Examples

Example: Reading a text file

The following example reads a text file and returns the first five lines:

SELECT * FROM READ_TEXT('s3://your-bucket/logs/server.log') LIMIT 5;

Returns

content
2026.03.19 22:30:25.150729 [ 1811245 ] <Trace> Pipe: Pipe capacity is 1.00 MiB
2026.03.19 22:30:25.221936 [ 1811245 ] <Information> : Starting server …
2026.03.19 22:30:25.221979 [ 1811245 ] <Information> Application: starting up
2026.03.19 22:30:25.221988 [ 1811245 ] <Information> Application: OS Name = Linux
2026.03.19 22:30:25.222753 [ 1811245 ] <Information> Application: Successfully used prctl

Example: Using glob patterns to read multiple files

The URL can represent a single file or a glob pattern. If a glob pattern is used, all files matching the pattern are read. Use $source_file_name to identify the source file of each row:

SELECT $source_file_name, content
FROM READ_TEXT(URL => 's3://your-bucket/logs/*.log')
LIMIT 5;

Example: Reading a compressed file

SELECT * FROM READ_TEXT(
    URL => 's3://your-bucket/logs/server.log.gz',
    COMPRESSION => 'gzip'
) LIMIT 5;

Example: Parsing log files with regular expressions

READ_TEXT is well-suited for ingesting log files that would cause errors with READ_CSV due to delimiters or special characters. You can use string functions and regular expressions to extract structured fields from the raw text:

SELECT
    parts[1] AS log_timestamp,
    parts[2]::BIGINT AS thread_id,
    parts[3] AS log_level,
    parts[4] AS message
FROM (
    SELECT REGEXP_MATCHES(
        TRIM(content),
        '^\s*(\d{4}\.\d{2}\.\d{2} \d{2}:\d{2}:\d{2}\.\d+)\s+\[\s*(\d+)\s*\]\s*\{[^}]*\}\s*<([^>]+)>\s*(.*)$'
    ) AS parts, content
    FROM READ_TEXT('s3://your-bucket/logs/server.log')
)
LIMIT 5;