DuckDB read_lines Extension

A DuckDB extension for reading line-based text files with line numbers and efficient subset extraction.

Quick Start

-- Read all lines
SELECT * FROM read_lines('app.log');

-- Read specific lines (positional argument)
SELECT * FROM read_lines('app.log', '100-200');
SELECT * FROM read_lines('app.log', '42 +/-5');

-- Read last 10 lines
SELECT * FROM read_lines('app.log', '+10-');

-- Read specific lines (path-embedded)
SELECT * FROM read_lines('src/file.py:42 +/-5');

-- Read a range with named parameters
SELECT * FROM read_lines('app.log', lines := '100-200', context := 3);

Functions

Function	Description
read_lines(path)	Read all lines from file(s), supports glob patterns
read_lines(path, lines)	Read selected lines (positional lines argument)
read_lines(path, lines, trim)	... with content trimming (pass NULL for lines to keep all)
read_lines_lateral(path[, lines[, trim]])	Lateral join variant for per-row file paths
parse_lines(text, ...)	Parse lines from a string value

Output Columns

Column	Type	Description
line_number	BIGINT	1-indexed line number
content	VARCHAR	Line content (preserves line endings)
byte_offset	BIGINT	Byte position of line start
file_path	VARCHAR	Source file path (file functions only)

Line Selection

Lines can be selected using the lines parameter or embedded in the file path.

Line Spec Syntax

A line spec is a mini-language for selecting lines:

Syntax	Meaning	Example
N	Single line	42
N-M	Range (inclusive)	10-20
N...M	Range (alternative)	10...20
-N or ...N	First N lines (head)	-100
N- or N...	From line N to end (tail)	100-
+N	Nth line from end	+5 (5th from end)
+N-	Last N lines	+10- (last 10 lines)
+N-+M	From Nth-last to Mth-last	+10-+5
spec +/-C	With C lines context	42 +/-3
spec -B +A	With B before, A after	42 -2 +5

Path-Embedded Selection

Line specs can be embedded in the file path after a colon:

read_lines('file.py:42')           -- line 42
read_lines('file.py:10-20')        -- lines 10-20
read_lines('file.py:42 +/-3')      -- line 42 with 3 lines context
read_lines('file.py:-50')          -- first 50 lines
read_lines('file.py:100-')         -- from line 100 to end
read_lines('file.py:+10-')         -- last 10 lines
read_lines('file.py:+5')           -- 5th line from end
read_lines('file.py:+10-+5')       -- from 10th-last to 5th-last

If a file literally named file.py:42 exists, it takes precedence.

Lines Parameter

The lines parameter accepts integers, strings, or structs:

-- Integer: single line or list
lines := 42
lines := [1, 5, 10, 20]

-- String: line spec syntax
lines := '100-200'
lines := '42 +/-3'
lines := ['-10', '100-']        -- first 10 and from 100 to end

Struct Format

Structs provide named fields for complex selections:

Field	Type	Description
start	INT	Range start (with stop)
stop	INT	Range end (with start)
line	INT	Single line number
lines	INT[]	Multiple line numbers
before	INT	Lines of context before
after	INT	Lines of context after
context	INT	Symmetric context (before and after)
inclusive	BOOL	Include stop line (default: true)

-- Range
lines := {start: 100, stop: 200}

-- Single line with context
lines := {line: 42, context: 3}

-- Multiple lines with context
lines := {lines: [10, 20, 30], before: 2, after: 5}

-- Head/tail
lines := {stop: 100}              -- first 100 lines
lines := {start: 100}             -- from line 100 to end

-- Exclusive stop (like Python range)
lines := {start: 1, stop: 11, inclusive: false}   -- lines 1-10

DuckDB unifies struct types, so you can mix forms in a list:

lines := [{line: 5}, {start: 10, stop: 20}, {lines: [30, 40]}]

Global Parameters

Parameter	Type	Description
lines	ANY	Line selection (see above)
trim	ANY	Content trimming (see below); also the optional third positional argument
before	BIGINT	Context lines before each selection
after	BIGINT	Context lines after each selection
context	BIGINT	Symmetric context (sets both before and after)
ignore_errors	BOOL	Skip unreadable files in glob patterns and lines that are not valid UTF-8 (skipped lines keep their line number)

Trimming

By default content preserves each line exactly, including its terminator. The trim argument (third positional in read_lines / read_lines_lateral, named in read_lines / parse_lines) transforms content only — line numbers, byte offsets, and line selection always operate on the raw bytes, and a line that trims to empty still appears.

Value	Effect
NULL / false / 'none'	Preserve exactly (default)
true / 'endings'	Strip the line terminator only
'right'	Strip the terminator and trailing spaces/tabs
'left'	Strip leading spaces/tabs; terminator kept
'both'	'left' + 'right'

-- Clean lines for exact comparison
SELECT * FROM read_lines('server.log', NULL, true) WHERE content = 'ERROR';

-- Ignore indentation and trailing whitespace
SELECT * FROM read_lines('config.yaml', trim='both');

Examples

View error location from stack trace

SELECT line_number, content
FROM read_lines('src/module.py:142 +/-5');

Extract log section

SELECT line_number, content
FROM read_lines('app.log', lines := '1000-1100');

Head and tail

-- First 20 lines
SELECT * FROM read_lines('data.csv', lines := '-20');

-- Last 20 lines (from-end syntax)
SELECT * FROM read_lines('data.csv', lines := '+20-');

-- Last section (from line 500 onward)
SELECT * FROM read_lines('data.csv', lines := '500-');

-- 5th line from end
SELECT * FROM read_lines('data.csv', lines := '+5');

-- Lines from 10th-last to 5th-last
SELECT * FROM read_lines('data.csv', lines := '+10-+5');

Find errors with context

WITH error_lines AS (
    SELECT line_number
    FROM read_lines('app.log')
    WHERE content LIKE '%ERROR%'
)
SELECT l.line_number, l.content
FROM read_lines('app.log',
    lines := (SELECT list(line_number) FROM error_lines),
    context := 2
) l;

Search across files

SELECT file_path, line_number, content
FROM read_lines('logs/*.log')
WHERE content LIKE '%Exception%';

Lateral join for per-row files

SELECT t.id, l.line_number, l.content
FROM my_table t,
     read_lines_lateral(t.file_path) l;

Design Notes

• Line numbering: 1-indexed (matches editors, grep, error messages)
• Range bounds: Inclusive on both ends
• Line endings: \n, \r\n, and lone \r are all separators, and each line's terminator is preserved in content (the final line keeps its lack of one) unless the trim argument says otherwise. A trailing terminator-final empty line is a real line: "a\n\n" is 2 lines, matching wc -l and parse_lines.
• Line counting: read_lines, read_lines_lateral, and parse_lines split identical bytes identically, whether the source is a file or a pipe
• Byte offsets: True source offsets of each line's first content byte
• BOM: A UTF-8 byte-order mark at the start of a source is skipped, not leaked into the first line (offsets remain true offsets, so line 1 of a BOM'd file starts at byte 3)
• Invalid UTF-8: A line that is not valid UTF-8 raises a clear error naming the file and line; with ignore_errors=true the line is skipped and keeps its line number
• Non-seekable sources: Pipes and streams (e.g. shellfs commands) are read incrementally; from-end references ('+2') on a pipe buffer the whole stream, since it cannot be rewound after counting
• Context clamping: Context before line 1 or after EOF is clamped
• Short-circuit: Scanning stops after passing all selected ranges
• Encoding: UTF-8

License

MIT