Monday, July 7, 2014

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:

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

No comments:

Post a Comment

Popular Posts