Query Linux Logs with SQL Using the ‘q’ Command-Line Tool

Recently I discovered a handy command-line tool called q that lets you treat any text file or command output as a database table and query it with familiar SQL syntax.

Setup

On a Linux (CentOS) system you can install it with three commands:

<code>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</code>

Windows users only need to download the executable.

Syntax

q supports the full SQLite syntax. The basic command line format is:

<code>q <command> "<SQL>"</code>

For example, to query the whole content of

myfile.log

:

<code>q "SELECT * FROM myfile.log"</code>

Two useful options are:

input

(e.g.,

-H

) tells q that the input contains a header line, enabling automatic column name detection.

output

(e.g.,

-O

) makes q print column names in the result.

Typical Use Cases

1. Keyword search

<code>q "select * from douyin.log where c9 like '%待解析%'"</code>

2. Fuzzy search with LIKE

<code>q -H -t "select * from test.log where abc like '%2%'"</code>

3. Union / Intersection

<code>q -H -t "select * from test.log union select * from test1.log"</code>

4. Distinct count

<code>q -H -t "SELECT COUNT(DISTINCT(uuid)) FROM ./clicks.csv"</code>

5. Column type detection

<code>q -H -t "SELECT request_id,score FROM ./clicks.csv WHERE score > 0.7 ORDER BY score DESC LIMIT 5"</code>

6. Field calculations

<code>sudo find /tmp -ls | q "SELECT c5,c6,sum(c7)/1024.0/1024 AS total FROM - GROUP BY c5,c6 ORDER BY total desc"</code>

7. Aggregation

<code>ps -ef | q -H "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"</code>

8. Cross‑file join

<code>q -H "select * from douyin.log a join douyin-2021-06-18.0.log b on (a.c2=b.c3) where b.c1='root'"</code>

Conclusion

While tools like

awk

are powerful,

q

offers a low‑learning‑curve alternative for developers familiar with SQL, especially when dealing with log files or command output.