Load vertica.log in flex table and also evolve a regexp function to identify the error settings

deepverticadeepvertica Registered User, VerticaPartners

Can anyone advise me how to load vertica.log in flex table and also evolve a regexp function to identify the error settings

Comments

  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert
    edited September 17

    Here's a regex string I have used in the past:
    FRegexParser(pattern=
    '^(?{time}\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d+) (?{thread_name}[A-Za-z ]+):(?{thread_id}0x[0-9a-f]+)-?(?{transaction_id}[0-9a-f]+)? (?:[(?{component}\w+)] <(?{level}\w+)> )?(?:<(?{elevel}\w+)> @[;?(?{enode}\w+)]?: )?(?{text}.*)'

    (due to web formatting silliness, replace { with less-than (<) and } with greater-than (>)

  • deepverticadeepvertica Registered User, VerticaPartners
    edited September 18

    Thanks but when used this pattern facing syntax error. Can you check this pattern is working for you...

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited September 18

    There is an example in the docs here:

    https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/FlexTables/FREGEXPARSERreference.htm

    Note that there is a doc bug that I just alerted our team about (i.e. the example shown does not specify the "pattern" keyword!).

    Here is a working example:

    dbadmin=> CREATE FLEX TABLE vertica_log();
    CREATE TABLE
    
    dbadmin=> COPY vertica_log FROM '/home/dbadmin/test_db/v_test_db_node0001_catalog/vertica.log' PARSER FREGEXPARSER(pattern='^(?<time>\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d\.\d+) (?<thread_name>[A-Za-z ]+):(?<thread_id>(?:0x)?[0-9a-f]+)-?(?<transaction_id>[0-9a-f])?(?:[(?<component>\w+)] \<(?<level>\w+)\> )?(?:<(?<elevel>\w+)> @[?(?<enode>\w+)]?: )?(?<text>.*)');
     Rows Loaded
    -------------
           18769
    (1 row)
    
    dbadmin=> SELECT maptostring(__raw__) FROM vertica_log LIMIT 2;
                                                                                                                                                         maptostring                                                     
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     {
       "text" : " [Init] <INFO> Log /home/dbadmin/test_db/v_test_db_node0001_catalog/vertica.log opened; #2",
       "thread_id" : "7f6ddd293280",
       "thread_name" : "Main",
       "time" : "2018-09-18 03:05:01.407"
    }
    
     {
       "text" : " [Init] <INFO> Processing command line: /opt/vertica/bin/vertica -D /home/dbadmin/test_db/v_test_db_node0001_catalog -C test_db -n v_test_db_node0001 -h 74.208.100.58 -p 5433 -P 4803 -Y ipv4",
       "thread_id" : "7f6ddd293280",
       "thread_name" : "Main",
       "time" : "2018-09-18 03:05:01.407"
    }
    
    (2 rows)
    
  • Ben_VandiverBen_Vandiver Employee, Registered User, VerticaExpert

    The original pattern works for me with the {}s replaced correctly.

  • deepverticadeepvertica Registered User, VerticaPartners
    edited September 19

    Hi Jim,
    It's working but not got the value of transaction_id, component, level,elevel
    and enode. Can you help me how to find out the value of this?

  • DaveTDaveT Employee, Registered User

    Here's another example but it does not include all the columns used by others. I haven't fully tested it but it seems to work on a small sample I tried. Presumably, the columns I did not include should still be available in the text column and you can easily add additional columns prior to text but you need to make them optional (with trailing ?). Records that do not have a beginning timestamp are also not included here. Those would typically be displays after the initial record that is timestamped.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file