awk if match

Shell Programming and Scripting
1

Hi,

This is the file content:

#160814 20:43:00 server id 2  end_log_pos 169934694     Query        thread_id=8927407    exec_time=0    error_code=0
use sun_final/*!*/;
SET TIMESTAMP=1471207380/*!*/;
DELETE FROM `top_pack` WHERE     `top_pack`.`id` = 3023

Trying like:

awk '{if(match($0, /#16.*/)) {printf "Timestamp : " $1 " " $2}}'      <filename>

How can I match DELETE FROM `top_pack` so I can pick up and print like the below result:

Timestamp : #160814 20:43:00 Table: top_pack      Query Type : DELETE

Thanks!

2

Hello ashokvpp,

Please use code tags for Inputs/commands/codes into your posts as per forum rules. Could you please try following.

awk  '{match($0,/^#[0-9]+ [0-9]+:[0-9]+:[0-9]+/);Q=substr($0,RSTART,RLENGTH); match($0,/.*FROM `top_pack/);substr($0,RSTART,RLENGTH);P=substr($0,RSTART,RLENGTH);sub(/ FROM.*/,X,P);if(Q){print "Timestamp : " Q};if(P){print "Table: top_pack Query Type : " P}}'  Input_file

Output will be as follows.

 
Timestamp : #160814 20:43:00
Table: top_pack Query Type : DELETE

If you need both output into a single line then following could be helpful.

awk  '{match($0,/^#[0-9]+ [0-9]+:[0-9]+:[0-9]+/);Q=substr($0,RSTART,RLENGTH); match($0,/.*FROM `top_pack/);substr($0,RSTART,RLENGTH);P=substr($0,RSTART,RLENGTH);sub(/ FROM.*/,X,P);if(Q){V="Timestamp : " Q};if(P && V){print V OFS "Table: top_pack Query Type : " P}}'  Input_file

Output will be as follows.

Timestamp : #160814 20:43:00 Table: top_pack Query Type : DELETE

EDIT: Adding a non-one liner form of solutions too as follows.

Solution1:

awk  '{match($0,/^#[0-9]+ [0-9]+:[0-9]+:[0-9]+/);
                Q=substr($0,RSTART,RLENGTH);
       match($0,/.*FROM `top_pack/);
                substr($0,RSTART,RLENGTH);
                P=substr($0,RSTART,RLENGTH);
                sub(/ FROM.*/,X,P);
                if(Q){
                        print "Timestamp : " Q
                     };
                if(P){
                        print "Table: top_pack Query Type : " P
                     }
      }
     '   Input_file

Solution2:

awk  '{match($0,/^#[0-9]+ [0-9]+:[0-9]+:[0-9]+/);
                Q=substr($0,RSTART,RLENGTH);
       match($0,/.*FROM `top_pack/);
                substr($0,RSTART,RLENGTH);
                P=substr($0,RSTART,RLENGTH);
                sub(/ FROM.*/,X,P);
                if(Q){
                        V="Timestamp : " Q
                     };
                if(P && V){
                                print V OFS "Table: top_pack Query Type : " P
                          }
       }
     '   Input_file

Thanks,
R. Singh

1 Like
3

Try

Input

[akshay@localhost tmp]$ cat file
#160814 20:43:00 server id 2  end_log_pos 169934694     Query        thread_id=8927407    exec_time=0    error_code=0
use sun_final/*!*/;
SET TIMESTAMP=1471207380/*!*/;
DELETE FROM `top_pack` WHERE     `top_pack`.`id` = 3023

#Assumption 1

awk '
/^#/{
        s=$1 OFS $2
}
/^.* FROM `.*`/{
       print "Timestamp : ",s,"Table :", substr($3,2,length($3)-2), "Query Type :", $1
}' file

One liner

awk '/^#/{s=$1 OFS $2}/^.* FROM `.*`/{print "Timestamp : ",s,"Table :", substr($3,2,length($3)-2), "Query Type :", $1}' file
Timestamp :  #160814 20:43:00 Table : top_pack Query Type : DELETE

#Assumption 2

awk -vn=3 '
/^.* FROM `.*`/{
     print "Timestamp : ", l[NR%n], "Table :", substr($3,2,length($3)-2), "Query Type :", $1
}
{
     l[NR%n]=$1 " "$2
}' file

One liner

awk -vn=3 '/^.* FROM `.*`/{print "Timestamp : ", l[NR%n], "Table :", substr($3,2,length($3)-2),"Query Type :", $1}{l[NR%n]=$1 " " $2}' file
Timestamp : #160814 20:43:00 Table : top_pack Query Type : DELETE

First one is better since its not using array..

1 Like
4 
awk '/^#/{s=$1 OFS $2}/^.* FROM `.*`/{print "Timestamp : ",s,"Table :", substr($3,2,length($3)-2), "Query Type :", $1}'

I need to match 3 sql's like INSERT INTO <tablename> , UPDATE <tablename> & DELETE from <tablename> from the filecontent?

5

Try

[akshay@localhost tmp]$ cat f
#160814 20:43:00 server id 2  end_log_pos 169934694     Query        thread_id=8927407    exec_time=0    error_code=0
use sun_final/*!*/;
SET TIMESTAMP=1471207380/*!*/;
DELETE FROM `top_pack1` WHERE     `top_pack1`.`id` = 3023
#test160814 20:43:00 server id 2  end_log_pos 169934694     Query        thread_id=8927407    exec_time=0    error_code=0
use sun_final/*!*/;
SET TIMESTAMP=1471207380/*!*/;
UPDATE `top_pack2` WHERE     `top_pack2`.`id` = 3023
#test2160814 20:43:00 server id 2  end_log_pos 169934694     Query        thread_id=8927407    exec_time=0    error_code=0
use sun_final/*!*/;
SET TIMESTAMP=1471207380/*!*/;
INSERT INTO `top_pack3` WHERE     `top_pack3`.`id` = 3023


[akshay@localhost tmp]$ awk '/^#/{s=$1 OFS $2}/^.* (FROM|INTO)? `.*`/{f=/FROM|INTO/?3:2;print "Timestamp : ",s,"Table :", substr($f,2,length($f)-2), "Query Type :", $1}' f
Timestamp :  #160814 20:43:00 Table : top_pack1 Query Type : DELETE
Timestamp :  #test160814 20:43:00 Table : top_pack2 Query Type : UPDATE
Timestamp :  #test2160814 20:43:00 Table : top_pack3 Query Type : INSERT
1 Like
6

Good to know we can use like
(FROM|INTO)?