How to get SQL type from the logmessage of master log

Goal:

Parse the SQL statement to get the SQL type from master log -- such as SELECT, UPDATE, COPY, DELETE, etc.
You need to firstly create an external table(named "masterlog_ext") based on master logs, please refer to here.

Solution:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

create temp table tmp_logminer_openkb as select logtime,loguser,logdatabase,logmessage,    upper(     substring(       regexp_replace(         substring(                                                                 regexp_replace(                 regexp_replace(                   regexp_replace(                      regexp_replace(logmessage,E'--[^\n]*(\\n|$)','','g')  --remove all the comment between ""--"" and ""new line""                      , E'\\n\\s*\\n','','g')                                --remove ALL-SPACE/empty line                   , E'\\s+',' ','g')                                        --remove junk spaces              ,'BEGIN TRANSACTION;','','i')                                 --remove ""begin transaction;""         , 1, 50  )                                                          --get the first 50 characters       , E'((statement:)|(execute.*:))((\\n)|(\\s))*', '' ,'i' )           --remove strings before "":""     from E'(^\\w+)(\\s|\\n)+')                                        --get the first characters as data type     )                                                                       --change it to upper case     sql_type from masterlog_ext where logmessage !~ 'duration:' and logseverity='LOG' and (logmessage like 'execute %' or      logmessage like 'statement:%')    and regexp_replace(logmessage,E'((statement:)|(execute.*:))((\\n)|(\\s))*', '' ,'i' ) !~* '^(begin|commit|end transaction|rollback|start transaction|;)' and regexp_replace(logmessage,E'((statement:)|(execute.*:))((\\n)|(\\s))*', '' ,'i' ) <> '' and logdatabase <> 'gpperfmon' distributed by (logtime) ;