Query Linux Logs with SQL Using the ‘q’ Tool – A Practical Guide

Setup

q is a lightweight command‑line program that treats any text file or command output as a virtual database table, allowing you to run standard SQL statements against it. Installation on a CentOS system requires three simple steps, while Windows users can just run the provided executable.

wget https://github.com/harelba/q/releases/download/1.7.1/q-text-as-data-1.7.1-1.noarch.rpm
sudo rpm -ivh q-text-as-data-1.7.1-1.noarch.rpm
q --version

Official documentation: https://harelba.github.io/q

SQL Syntax

q supports the full SQLite syntax. The basic command format is: q "<SQL>" For example, to display the entire contents of myfile.log:

q "SELECT * FROM myfile.log"

Parameters

q distinguishes between input options (which affect how the source file is interpreted) and output options (which affect the result presentation). Common input flags include -H to treat the first line as a header, causing automatic column‑name detection. If omitted, columns are named c1, c2, …. Output flags such as -O request that column names be printed in the result set.

# Example with header detection
q -H "SELECT * FROM myfile.log"

# Example with explicit column names in output
q -H -O "SELECT c1, c2 FROM myfile.log"

Typical Log‑Query Scenarios

1. Keyword search

# Search for a specific keyword in a log file
q "SELECT * FROM douyin.log WHERE c9 LIKE '%待解析%'"

Using grep would perform a full‑text search, but q requires you to specify the column to search.

2. Fuzzy matching

# Use the LIKE operator for pattern matching
q -H -t "SELECT * FROM test.log WHERE abc LIKE '%2%'"

3. Union / Intersection

# Combine two log files and remove duplicates
q -H -t "SELECT * FROM test.log UNION SELECT * FROM test1.log"

4. Deduplication

# Count distinct UUIDs in a CSV file
q -H -t "SELECT COUNT(DISTINCT(uuid)) FROM ./clicks.csv"

5. Automatic column‑type detection

# Filter rows where the numeric column "score" exceeds 0.7 and sort
q -H -t "SELECT request_id, score FROM ./clicks.csv WHERE score > 0.7 ORDER BY score DESC LIMIT 5"

6. Field arithmetic

# Compute total size (in GB) of each user/group under /tmp
sudo find /tmp -ls | q "SELECT c5, c6, SUM(c7)/1024.0/1024 AS total FROM - GROUP BY c5, c6 ORDER BY total DESC"

7. Data statistics

# Find the top three users with the most processes
ps -ef | q -H "SELECT UID, COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"

8. Cross‑file queries

# Join two daily log files on specific columns
q -H "SELECT * FROM douyin.log a JOIN douyin-2021-06-18.log b ON (a.c2 = b.c3) WHERE b.c1='root'"

Conclusion

While tools like awk remain powerful, q offers a SQL‑centric approach that can be more approachable for developers already familiar with relational queries. It does not aim to replace existing utilities but to provide an additional, convenient method for log analysis.