Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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


  • Ben_VandiverBen_Vandiver Employee
    edited September 2018

    Here's a regex string I have used in the past:
    '^(?{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 (>)

  • edited September 2018

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

  • Jim_KnicelyJim_Knicely Administrator
    edited September 2018

    There is an example in the docs here:

    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();
    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
    (1 row)
    dbadmin=> SELECT maptostring(__raw__) FROM vertica_log LIMIT 2;
       "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 -p 5433 -P 4803 -Y ipv4",
       "thread_id" : "7f6ddd293280",
       "thread_name" : "Main",
       "time" : "2018-09-18 03:05:01.407"
    (2 rows)
  • The original pattern works for me with the {}s replaced correctly.

  • edited September 2018

    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?

  • 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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.