How to Install and Use trdsql in Linux to Execute SQL Queries on CSV, TSV, and JSON Files

Linux system administrators, DevOps engineers, and data analysts frequently process tabular datasets, server logs, and metrics formatted in CSV, TSV, JSON, or LTSV. While command-line tools like awk, sed, cut, and jq are excellent for basic row and column manipulations, constructing complex analytical workflows—such as multi-table joins, subqueries, rolling window functions, and multi-key aggregations—can become cumbersome and error-prone.

trdsql is an open-source, high-performance command-line tool written in Go that enables you to execute standard SQL queries directly on structured text files without importing them into a standalone database server. By embedding SQLite and PostgreSQL compatibility layers, trdsql turns ordinary text files into queryable relational tables instantaneously.

Key Features of trdsql

• Zero-Infrastructure Queries: Queries structured files directly without launching daemon services or setting up external database instances.
• Cross-Format JOINs: Seamlessly joins data between different file formats (e.g., joining a CSV customer list with a JSON transaction log in a single SQL statement).
• Pluggable SQL Engines: Supports SQLite syntax by default, with native drivers for MySQL and PostgreSQL engines when required.
• Flexible Output Formatting: Exports query results as clean ASCII tables, CSV, TSV, JSON, LTSV, Markdown, or raw values.
• Gzip Compression Support: Reads directly from .gz compressed archives on the fly without manual decompression.

Installing trdsql on Linux

Method 1: Direct Binary Download (Recommended)

Download the pre-compiled binary directly from the official GitHub releases:

# Download latest release archive
curl -sLO https://github.com/noborus/trdsql/releases/latest/download/trdsql_linux_amd64.tar.gz

# Extract binary
tar -xzf trdsql_linux_amd64.tar.gz

# Move to system path
sudo mv trdsql_linux_amd64/trdsql /usr/local/bin/

# Verify installation
trdsql -version

Method 2: Using the Go Toolchain

If Go is installed on your Linux system, compile and install trdsql directly:

go install github.com/noborus/trdsql@latest

Core Syntax and Essential Flags

The standard syntax for trdsql follows familiar SQL patterns:

trdsql [options] "SELECT ... FROM <file> ..."

Crucial Command Flags

• -ih: Treats the first row of input files as column headers (column names become SQL identifiers).
• -id <delimiter>: Specifies the input delimiter (defaults to comma for CSV, tab for TSV).
• -ijson: Interprets input as JSON or newline-delimited JSON (NDJSON).
• -oat: Outputs results in an ASCII table with borders (ideal for terminal viewing).
• -ocsv: Formats results as standard CSV.
• -ojson: Exports results as a structured JSON array.
• -omd: Outputs results formatted as a clean Markdown table.

Practical SQL Query Examples on Linux Datasets

1. Filtering and Aggregating CSV Data

Suppose you have an Apache/Nginx web server access log exported to a CSV file named access_logs.csv with headers ip,method,status,bytes,path:

# Find top 5 paths by total bytes transferred with 200 OK status
trdsql -ih -oat "SELECT path, COUNT(*) AS hits, SUM(bytes)/1024 AS kb_transferred   FROM access_logs.csv   WHERE status = '200'   GROUP BY path   ORDER BY kb_transferred DESC   LIMIT 5"

2. Joining Two Different Files

One of the strongest capabilities of trdsql is joining disparate data files in a single execution. Suppose you have users.csv (containing user_id,username,department) and activity.tsv (containing user_id,login_count,last_login):

trdsql -ih -oat "SELECT u.username, u.department, a.login_count, a.last_login   FROM users.csv AS u   INNER JOIN activity.tsv AS a ON u.user_id = a.user_id   WHERE a.login_count > 50   ORDER BY a.login_count DESC"

3. Querying Structured JSON and NDJSON Files

When working with JSON files containing arrays of objects, use the -ijson flag:

# Query a Docker or Kubernetes metrics JSON file
trdsql -ijson -oat "SELECT container_name, memory_usage_mb, cpu_percent   FROM containers.json   WHERE cpu_percent > 80.0   ORDER BY cpu_percent DESC"

Input and Output Format Matrix

Use the following flag reference to convert data between multiple formats seamlessly during SQL execution:

Format	Input Flag	Output Flag	Use Case
CSV	-icomm / -ih	-ocsv	Standard spreadsheets and export pipelines
TSV	-it / -ih	-otsv	Tab-separated logs and bioinformatics datasets
JSON / NDJSON	-ijson	-ojson	REST APIs, cloud logs, and document stores
ASCII Table	N/A	-oat	Human-readable terminal reporting
Markdown	N/A	-omd	Documentation, Git READMEs, and technical wikis
LTSV	-iltsv	-oltsv	Labeled Tab-Separated Value server logs

Streaming Shell Pipelines into trdsql

In addition to physical files, trdsql accepts input streamed from standard input using the special pseudo-filename -:

# Query system processes using ps and trdsql
ps aux | trdsql -id " " -ih -oat "SELECT USER, PID, "COMMAND"   FROM -   WHERE "%CPU" > 5.0   ORDER BY "%CPU" DESC"

By leveraging trdsql in shell scripts and terminal investigations, administrators bypass the overhead of database installations while enjoying the full expressive power of relational SQL.