用SQL查Linux日志工具-q
概述
Linux文本查找命令不要太多：tail、head、cat、sed、more、less、awk，但对新手不友好。q是一个命令行工具，允许在任意文件或查询结果，如ps -ef查询进程命令的结果集上，执行SQL语句查询。
GitHub
官方文档
理念：文本即数据库表，将普通文件或者结果集当作数据库表，几乎支持所有的SQL结构和关键词，如WHERE、GROUP BY、JOINS等，支持自动列名和列类型检测，跨文件连接查询，和多种编码。
安装
参考：installation手册。
Linux
CentOS环境：
# 下载
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
Windows
去GitHub Release下载最新版，一路next即可：
使用
q -h得到命令使用手册。支持所有SQLite SQL语法，标准命令行格式，q + 参数命令 + SQL：q <命令> <SQL>
查询文件内容：q "SELECT * FROM myfile.log"
利用参数会让显示结果更加美观，参数有2类：
- input输入命令：对要查询的文件或结果集进行操作，如
-H命令表示输入的数据包含标题行。
q -H "SELECT * FROM myfile.log"
在这种情况下，将自动检测列名，并可在查询语句中使用。如果未提供此选项，则列将自动命名为cX，以c1起始以此类推。
q "select c1，c2 from ..." - output输出命令：作用在查询输出的结果集，如
-O让查询出来的结果显示列名：
ps -ef | q -H -O "select count(UID) from - where UID='root'"
具体Input命令参数讲解：
Input Data Options:
-H, --skip-header Skip header row. This has been changed from earlier
version - Only one header row is supported, and the
header row is used for column naming
-d DELIMITER, --delimiter=DELIMITER
Field delimiter. If none specified, then space is used
as the delimiter.
-p, --pipe-delimited
Same as -d '|'. Added for convenience and readability
-t, --tab-delimited
Same as -d <tab>. Just a shorthand for handling
standard tab delimited file You can use $'\t' if you
want (this is how Linux expects to provide tabs in the
command line
-e ENCODING, --encoding=ENCODING
Input file encoding. Defaults to UTF-8. set to none
for not setting any encoding - faster, but at your own
risk...
-z, --gzipped Data is gzipped. Useful for reading from stdin. For
files, .gz means automatic gunzipping
-A, --analyze-only Analyze sample input and provide information about
data types
-m MODE, --mode=MODE
Data parsing mode. fluffy, relaxed and strict. In
strict mode, the -c column-count parameter must be
supplied as well
-c COLUMN_COUNT, --column-count=COLUMN_COUNT
Specific column count when using relaxed or strict
mode
-k, --keep-leading-whitespace
Keep leading whitespace in values. Default behavior
strips leading whitespace off values, in order to
provide out-of-the-box usability for simple use cases.
If you need to preserve whitespace, use this flag.
--disable-double-double-quoting
Disable support for double double-quoting for escaping
the double quote character. By default, you can use ""
inside double quoted fields to escape double quotes.
Mainly for backward compatibility.
--disable-escaped-double-quoting
Disable support for escaped double-quoting for
escaping the double quote character. By default, you
can use \" inside double quoted fields to escape
double quotes. Mainly for backward compatibility.
--as-text Don't detect column types - All columns will be
treated as text columns
-w INPUT_QUOTING_MODE, --input-quoting-mode=INPUT_QUOTING_MODE
Input quoting mode. Possible values are all, minimal
and none. Note the slightly misleading parameter name,
and see the matching -W parameter for output quoting.
-M MAX_COLUMN_LENGTH_LIMIT, --max-column-length-limit=MAX_COLUMN_LENGTH_LIMIT
Sets the maximum column length.
-U, --with-universal-newlines
Expect universal newlines in the data. Limitation: -U
works only with regular files for now, stdin or .gz
files are not supported yet.
具体Output命令参数讲解：
Output Options:
-D OUTPUT_DELIMITER, --output-delimiter=OUTPUT_DELIMITER
Field delimiter for output. If none specified, then
the -d delimiter is used if present, or space if no
delimiter is specified
-P, --pipe-delimited-output
Same as -D '|'. Added for convenience and readability.
-T, --tab-delimited-output
Same as -D <tab>. Just a shorthand for outputting tab
delimited output. You can use -D $'\t' if you want.
-O, --output-header
Output header line. Output column-names are determined
from the query itself. Use column aliases in order to
set your column names in the query. For example,
'select name FirstName,value1/value2 MyCalculation
from ...'. This can be used even if there was no
header in the input.
-b, --beautify Beautify output according to actual values. Might be
slow...
-f FORMATTING, --formatting=FORMATTING
Output-level formatting, in the format X=fmt,Y=fmt
etc, where X,Y are output column numbers (e.g. 1 for
first SELECT column etc.
-E OUTPUT_ENCODING, --output-encoding=OUTPUT_ENCODING
Output encoding. Defaults to 'none', leading to
selecting the system/terminal encoding
-W OUTPUT_QUOTING_MODE, --output-quoting-mode=OUTPUT_QUOTING_MODE
Output quoting mode. Possible values are all, minimal,
nonnumeric and none. Note the slightly misleading
parameter name, and see the matching -w parameter for
input quoting.
-L, --list-user-functions
List all user functions
实战
关键字查询
查询时必须指定某一列：
q "select * from douyin.log where c9 like '%待解析%'"
2021-06-11 14:46:49.323 INFO 22790 --- [nio-8888-exec-2] c.x.douyin.controller.ParserController : 待解析URL :url=https%3A%2F%2Fv.douyin.com%2Fe9g9uJ6%2F
用grep命令则是全文检索：
cat douyin.log | grep '待解析URL'
2021-06-11 14:46:49.323 INFO 22790 --- [nio-8888-exec-2] c.x.douyin.controller.ParserController : 待解析URL :url=https%3A%2F%2Fv.douyin.com%2Fe9g9uJ6%2F
模糊查询
like模糊搜索，如果文本内容列有名字直接用列名检索，没有则直接根据列号c1、c2、cN。
q -H -t "select * from test.log where abc like '%2%'"
交集并集
支持UNION和UNION ALL操作符对多个文件取交集或者并集。
q -H -t "select * from test.log union select * from test1.log"
内容去重
比如统计某个路径下的./clicks.csv文件中，uuid字段去重后出现的总个数。
q -H -t "SELECT COUNT(DISTINCT(uuid)) FROM ./clicks.csv"
列类型自动检测
q会理解每列是数字还是字符串，判断是根据实数值比较，还是字符串比较进行过滤，这里会用到-t命令。
q -H -t "SELECT request_id,score FROM ./clicks.csv WHERE score > 0.7 ORDER BY score DESC LIMIT 5"
字段运算
读取系统命令查询结果，计算/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"
# sudo find /tmp -ls | q "SELECT c5,c6,sum(c7)/1024.0/1024 AS total FROM - GROUP BY c5,c6 ORDER BY total desc"
www www 8.86311340332
root root 0.207922935486
mysql mysql 4.76837158203e-06
数据统计
统计系统拥有最多进程数的前 3个用户ID，按降序排序，需要配合系统命令使用，先查询所有进程再利用SQL筛选，q相当于grep：
ps -ef | q -H "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"
root 104
www 16
rabbitmq 4
ps -ef | q -H -O "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"
UID cnt
root 110
www 16
rabbitmq 4
加与不加-O命令的区别就是否显示查询结果的标题。
连接文件
一般情况下，日志文件会按天分割成很多个固定容量的子文件，在没有统一的日志收集服务器的情况下。可将所有文件内容合并后再查询：
q -H "select * from douyin.log a join douyin-2021-06-18.0.log b on (a.c2=b.c3) where b.c1='root'"
