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

deepvertica

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

Ben_Vandiver

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 (>)

deepvertica

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

Jim_Knicely

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_Vandiver

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

deepvertica

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?

DaveT

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.