3

I have lines like these -

/mnt/internal-app/logs/internal-app.log_2019-08-21.log.gz:2019-08-21 07:31:14,153 5458142 [XNIO-3 task-4] INFO  c.c.p.i.m.ws.FileManger [FileName.java:1838] - UUIDs in this bucket 8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576
/mnt/internal-app/logs/internal-app.log_2019-08-21.log.gz:2019-08-21 07:31:14,153 5458144 [XNIO-3 task-4] INFO  c.c.p.i.m.ws.FileManger [FileName.java:1838] - UUIDs in this bucket 6501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

What I ultimately need to do is, collect all the UUIDs and prepare an SQL insert statement like this -

insert into sometable (uuid) values ("6501792126581991569","8073766106536916628")..(..);

There are huge number of such lines, almost 500k. I am unable to apply regex by opening the file in my sublime text editor.

So, I am trying it via grep.

I tried this -

zgrep "UUIDs in this bucket" /mnt/internal-app/logs/internal-app.log_2019-08-2* | grep -Eo ".* UUIDs in this bucket(.*)" | cut -d: -f5

It printed more than what I needed -

1838] - UUIDs in this bucket 8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

How do I pick only from the UUIDs?

Update

Corrected sql query syntax -

insert into sometable (uuid) values ("6501792126581991569"),("8073766106536916628")..(..);
3
  • Do you just want the UUIDs with the commas inbetween? Aug 28, 2019 at 12:23
  • And do you want all UUIDs or only two as you show in your insert statement?
    – terdon
    Aug 28, 2019 at 13:13
  • I want all the UUIDs Aug 28, 2019 at 13:15

3 Answers 3

4

If you want all of the numbers after UUIDs in this bucket, you can use sed like so:

$ zcat file.gz | sed -n 's/^.*UUIDs in this bucket //p' 
8501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576
6501792126581991569,8073766106536916628,4830289023695906800,6135982080116553120,8306484440313978157,9040948912536460872,8471856544054164043,5431263453539111247,7661719762428556576

Or, use perl and output the full SQL statement:

$ zcat file.gz | perl -ne 'chomp;if(s/^.*UUIDs in this bucket //){@uuids=split(/,/); $k{$_}++ for @uuids} END{ print "insert into sometable (uuid) values (" , join ",",map{qq/"$_"/} keys(%k); print ");\n"}' 
insert into sometable (uuid) values ("6135982080116553120","4830289023695906800","8501792126581991569","9040948912536460872","7661719762428556576","8471856544054164043","8306484440313978157","6501792126581991569","5431263453539111247","8073766106536916628");

Or, slightly more legibly:

$ zcat file.gz | 
    perl -ne 'chomp;
              if(s/^.*UUIDs in this bucket //){
                @uuids=split(/,/); 
                $k{$_}++ for @uuids
              }
              END{
                print "insert into sometable (uuid) values (" , 
                           join ",",map{qq/"$_"/} @uuids; 
                print ");\n"
            }'
insert into sometable (uuid) values ("6501792126581991569","8073766106536916628","4830289023695906800","6135982080116553120","8306484440313978157","9040948912536460872","8471856544054164043","5431263453539111247","7661719762428556576");
6
  • Bang on! Just a little modification, to do a single insert query, to avoid too many inserts - perl -ne 'chomp;if(s/^.*UUIDs in this bucket //){@uuids=split(/,/); print "," , join ",",map{qq/"$_"/} @uuids; print "\n"}' Aug 28, 2019 at 13:38
  • 1
    @SandeepanNath ah yes, good point. Please see updated answer. It now dedupes the UUIDs and only prints a single insert statement.
    – terdon
    Aug 28, 2019 at 13:44
  • or use perl's DBI module to connect to the sql database and actually run the sql statement. Also, perl's PerlIO::gzip module can be used to read files whether they're compressed or not.
    – cas
    Aug 28, 2019 at 14:17
  • 1
    @cas all true, but I felt that's kind of beyond the scope of the question :).
    – terdon
    Aug 28, 2019 at 14:48
  • @terdon a little more help needed. For the single insert query to work, I need it to be like values ("6501792126581991569"),("8073766106536916628").. - Notice each of the UUIDs wrapped in single brackets, which I missed in my question. Aug 28, 2019 at 14:56
4

If you are willing/able to use tools other than grep you could do it with awk fairly easily since it looks like you always want the end of the line. You can have it print only the last field like:

zcat /mnt/internal-app/logs/internal-app.log_2019-08-2* | awk '/UUIDs in this bucket/ {print $NF}'

I don't know if any forms of zgrep don't support Perl style regex, but assuming yours does too you could do it with that like

zgrep -Po 'UUIDs in this bucket \K.*' /mnt/internal-app/logs/internal-app.log_2019-08-2*

since the \K tells the pattern to not count everything before it as part of the match. So this will only print the things that come after the match.

4
  • The \K from the 2nd solution did it for me. Although the exact solution you provided did not work, as I still got some parts of the lines before the "UUIDs in this bucket" section. This is what worked for me - zgrep 'UUIDs in this bucket' /mnt/internal-app/logs/internal-app.log_2019-08-2* | grep -oP ".* UUIDs in this bucket \K.*" Aug 28, 2019 at 13:14
  • As far as I know, most (all?) grep implementations other than GNU grep have neither -P nor -o. This will work fine for GNU grep, but you might want to point that limitation out.
    – terdon
    Aug 28, 2019 at 13:21
  • @terdon fair enough, but since he used -o in the question I felt it was probably safe to include that sort of answer Aug 28, 2019 at 14:54
  • 1
    @EricRenouf oh, absolutely! This is the sort of thing I would have done too! I only commented because you'd written "I don't know if any forms of zgrep don't support Perl style regex", so I thought I'd point out that most don't :)
    – terdon
    Aug 28, 2019 at 16:43
0

Another perl way to generate the SQL code:

zcat -f /mnt/internal-app/logs/internal-app.log_2019-08-2* |
  perl -lne 'BEGIN{$"=q(",")}
             @u = m{(?:UUIDs in this bucket |\G,)\K\d+}g;
             print qq(insert into sometable (uuid) values ("@u");) if @u'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.