Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use SQL query Linux log, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

SQL is a special purpose programming language, a database query and programming language for accessing data and querying, updating and managing relational database systems. This article will use SQL to query Linux logs.

Set up an environment

Q is a command-line tool that allows us to execute SQL queries directly on any file or query results, such as the result set of the ps-ef query process command.

The purpose is that the text is the database table, er, of course, this sentence is understood by myself, ha.

It takes ordinary files or result sets as database tables, supports almost all SQL structures, such as WHERE, GROUP BY, JOINS, etc., supports automatic column name and column type detection, supports cross-file join queries, which are described in detail later and supports multiple encodings.

Installation is relatively simple, in the Linux CentOS environment, as long as the following three steps to complete, the Windows environment is only necessary to install an exe can be used.

Wget https://github.com/harelba/q/releases/download/1.7.1/q-text-as-data-1.7.1-1.noarch.rpm # download version sudo rpm-ivh q-text-as-data-1.7.1-1.noarch.rpm # installation Q-- version # View installation version

"official document: https://harelba.github.io/q

Grammar

Q supports all SQLiteSQL syntax, standard command line format Q + parameter command + "SQL"

Q ""

I want to query the contents of the myfile.log file, directly Q "SELECT * FROM myfile.log".

Q "SELECT * FROM myfile.log"

Q there is no problem with the use of no additional parameters, but the use of parameters will make the display results more beautiful, so here is a brief understanding of its parameters are divided into two types.

Input input command: refers to the operation on the file or result set to be queried, such as the-H command, which indicates that the input data contains the header row.

Q-H "SELECT * FROM myfile.log"

In this case, the column name is automatically detected and can be used in the query statement. If this option is not provided, the column is automatically named cX, starting with C1, and so on.

Q "select C1 from c2..."

Output output command: acts on the result set of the query output, such as:-O, and causes the query result to display the column name.

[root@iZ2zebfzaequ90bdlz820sZ software] # ps-ef | Q-H "select count (UID) from-where UID='root'" 104 [root@iZ2zebfzaequ90bdlz820sZ software] # ps-ef | Q-H-O "select count (UID) from-where UID='root'" count (UID) 104

There are many parameters are not listed, interested students take a look at the official website, and then we focus on how to use SQL to deal with a variety of query log scenarios. Picture

There are many thieves in the game.

Let's take a look at how to write this SQL in several common scenarios that query logs.

1. Keyword query

Keyword retrieval should be the most frequently used operation in daily development, but I personally don't think Q has any advantage because it has to specify a column when querying.

[root@iZ2zebfzaequ90bdlz820sZ software] # Q "select * from douyin.log where c9 like'% to be resolved%'" 2021-06-11 14 from douyin.log where 46 like 49.323 INFO 22790-[nio-8888-exec-2] c.x.douyin.controller.ParserController: to be resolved URL: url=https%3A%2F%2Fv.douyin.com%2Fe9g9uJ6%2F 2021-06-11 14Suzhou 57Suzhou 31.938 INFO 22790- -- [nio-8888-exec-5] c.x.douyin.controller.ParserController: to be resolved URL: url=https%3A%2F%2Fv.douyin.com%2Fe9pdhGP%2F 2021-06-11 15 URL 23 purl 48.004 INFO 22790-[nio-8888-exec-2] c.x.douyin.controller.ParserController: to be resolved URL: url=https%3A%2F%2Fv.douyin.com%2Fe9pQjBR % 2F 2021-06-11 2

The grep command is used for full-text search.

[root@iZ2zebfzaequ90bdlz820sZ software] # cat douyin.log | grep'to be resolved URL'2021-06-11 1440 46 URL'2021 49.323 INFO 22790-[nio-8888-exec-2] c.x.douyin.controller.ParserController: to be resolved URL: url=https%3A%2F%2Fv.douyin.com%2Fe9g9uJ6%2F2021-06-11 14 grep 57 URL'2021 31.938 INFO 22790-[nio-8888-exec-5] c.x.douyin.controller.ParserController: to be resolved URL : url=https%3A%2F%2Fv.douyin.com%2Fe9pdhGP%2F

2. Fuzzy query

Like fuzzy search, if the text content column name is directly retrieved by the column name, then directly according to the column number C1, c2, cN.

[root@iZ2zebfzaequ90bdlz820sZ software] # cat test.logabc2345232425 [root@iZ2zebfzaequ90bdlz820sZ software] # Q-H-t "select * from test.log where abc like'% 2%'" Warning: column count is one-did you provide the correct delimiter?2232425

3. Intersection and union

The UNION and UNION ALL operators are supported to intersect or join multiple files.

The following two files, test.log and test1.log, are built, and the contents are overlapped. Use union to remove duplicates.

Q-H-t "select * from test.log union select * from test1.log" [root@iZ2zebfzaequ90bdlz820sZ software] # cat test.logabc2345 [root@iZ2zebfzaequ90bdlz820sZ software] # cat test1.logabc3456 [root@iZ2zebfzaequ90bdlz820sZ software] # Q-H-t "select * from test.log union select * from test1.log" Warning: column count is one-did you provide the correct delimiter?Warning: column count is one-did you provide the correct delimiter?23456

4. Deduplication of content

For example, count the total number of uuid fields in the. / clicks.csv file under a certain path.

Q-H-t "SELECT COUNT (DISTINCT (uuid)) FROM. / clicks.csv"

5. Automatic detection of column type

Note: Q will understand whether each column is a number or a string and determine whether to filter based on a real value comparison or a string comparison. The-t command is used here.

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

6. Field operation

Read the system command query results and calculate the total value of each user and group in the / tmp directory. The field can be calculated and processed.

Sudo find / tmp-ls | Q "SELECT c5 AS total FROM (c7) / 1024.0 ls 1024 AS total FROM-GROUP BY c5 ORDER BY total desc" [root@iZ2zebfzaequ90bdlz820sZ software] # sudo find / tmp-ls | Q "SELECT c5 AS total FROM (c7) / 1024.0 take 1024 AS total FROM-GROUP BY c5 ORDER BY total desc c6 ORDER BY total desc"

7. Data statistics

Statistics system has the largest number of processes of the first three users ID, sorted in descending order, which needs to be used in conjunction with system commands, first query all processes and then filter using SQL, where the Q command is equivalent to the grep command.

Ps-ef | Q-H "SELECT UID,COUNT (*) cnt FROM-GROUP BY UID ORDER BY cnt DESC LIMIT 3" [root@iZ2zebfzaequ90bdlz820sZ software] # ps-ef | Q-H "SELECT UID,COUNT (*) cnt FROM-GROUP BY UID ORDER BY cnt DESC LIMIT 3" root 104www 16rabbitmq 4 [root@iZ2zebfzaequ90bdlz820sZ software] # ps-ef | Q-H-O "SELECT UID,COUNT (*) cnt FROM-GROUP BY UID ORDER BY cnt DESC LIMIT 3" UID cntroot 110www 16rabbitmq 4

We see the difference between adding and not adding the-O command to see whether the title of the query result is displayed.

8, even file check

In general, our log files will be divided into many fixed-capacity sub-files on a daily basis. In the absence of a unified log collection server, if you do not check a keyword within an error interval, it is tantamount to looking for a needle in a haystack. If the picture can merge all the contents of the file, it will be a lot easier to check. Q supports the joint query of the file like a database table.

Q-H "select * from douyin.log a join douyin-2021-06-18.0.log b on (a.c2=b.c3) where b.c1query root`" above is all the content of this article "how to query Linux logs using SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.