Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.python > #79074 > unrolled thread

Need help in pulling SQL query out of log file...

Started bySagar Deshmukh <sagarddd@gmail.com>
First post2014-10-13 18:47 -0700
Last post2014-10-13 23:09 -0700
Articles 3 — 2 participants

Back to article view | Back to comp.lang.python


Contents

  Need help in pulling SQL query out of log file... Sagar Deshmukh <sagarddd@gmail.com> - 2014-10-13 18:47 -0700
    Re: Need help in pulling SQL query out of log file... alex23 <wuwei23@gmail.com> - 2014-10-14 15:39 +1000
      Re: Need help in pulling SQL query out of log file... Sagar Deshmukh <sagarddd@gmail.com> - 2014-10-13 23:09 -0700

#79074 — Need help in pulling SQL query out of log file...

FromSagar Deshmukh <sagarddd@gmail.com>
Date2014-10-13 18:47 -0700
SubjectNeed help in pulling SQL query out of log file...
Message-ID<6200437a-b90a-4b60-b287-bd5b7fe37e9d@googlegroups.com>
Hi,

I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..

My requirement is i would like to read the SQL query completly and write it to another txt file.. also the log file may not be always same so can not make static choices...

my logfile is like below :

*****LOg file starts******


Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8005 Writer run started.
Fri Aug 08 16:00:04 2014 : READER_1_2_1> BLKR_16007 Reader run started.
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8158 

*****START LOAD SESSION*****

Load Start Time: Fri Aug 08 16:00:04 2014

Target tables:

     EIS_REQUEST_LOG_26MAYBKP
     T_delta_parm_file


Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4010 SQ instance [SQ_Shortcut_to_EIS_REQUEST_LOG] SQL Query [SELECT EIS_REQUEST_LOG_26MAYBKP.RqstId, EIS_REQUEST_LOG_26MAYBKP.RQSTLoadStatCd FROM EIS_REQUEST_LOG_26MAYBKP]
Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4049 RR_4049 SQL Query issued to database : (Fri Aug 08 16:00:04 2014)
Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4035 SQL Error [
FnName: Prepare -- [Teradata][ODBC Teradata Driver][Teradata Database] Object 'EIS_REQUEST_LOG_26MAYBKP' does not exist. ].
Fri Aug 08 16:00:04 2014 : READER_1_2_1> BLKR_16004 ERROR: Prepare failed.
Fri Aug 08 16:00:04 2014 : READER_1_1_1> RR_4029 SQ Instance [SQ_RSTS_Tables] User specified SQL Query [--- Approved By ICC Team-------
---SELECT A.LOGSYS , 
---D."/BIC/NIGNRCSYS" ,
---B.ODSNAME ,
---C.ODSNAME_TECH , 
---C.PARTNO , 
---C.REQUEST ,
---E.SID 
---FROM
---sapbzd."RSISOSMAP"  A, 
---sapbzd."RSTSODS"      B, 
---sapbzd."RSTSODSPART"   C,
---sapbzd."/BIC/PNIGNRCSYS"   D,
---sapbzd."/BI0/SREQUID"   E,
---sapbzd."RSMONMESS"    F
---WHERE A.OLTPSOURCE = ('ZNK_SHP_DDLN_CREATE_BE','ZNK_KNVP2_BD','ZNK_ZVBW_RTN_ORD_ITM_BN','2LIS_02_SCL_BE','ZNK_FX_CRCY_HIS_BE','ZNK_PO_FX_CALC_LOG_BD','2LIS_12_VCHDR_BE','2LIS_02_HDR_BN','ZNK_SHP_DDLN_CHANGE_BD','1_CO_PAGL11000N1_BE','0CUSTOMER_ATTR_BE','2LIS_08TRTLP_BD','2LIS_02_SCN_BD','2LIS_02_HDR_BD','2LIS_13_VDITM_BE','0CO_OM_CCA_9_BE','ZNK_SO_BDSI_OPNDMD_BE','2LIS_11_VAHDR_BE','ZNK_ZVBW_MBEW_BE','2LIS_13_VDHDR_BN','ZNK_SHP_DDLN_CHANGE_BE','NK_ADDR_NUMBR_BN','0CUSTOMER_TEXT_BE','6DB_J_3ABD_DELTA_AFFL_AD','0MAT_PLANT_ATTR_BE','ZNK_BDCPV_BD','1_CO_PAGL11000N1_BD','2LIS_11_VASTI_BD','ZNK_ZVBW_MSKU_BE','ZNK_SHP_DDLN_CREATE_BD','0SCEM_1_BC','2LIS_11_VAHDR_BD','2LIS_11_VASCL_BD','0MATERIAL_TEXT_BE','0MATERIAL_ATTR_BE','ZNK_BDCPV_BE','2LIS_02_ITM_BN','2LIS_11_VASCL_BE','2LIS_11_VAITM_BN','NK_ADDR_NUMBR_BE','2LIS_08TRTK_BE','ZNK_SD_LIKPPS_BN','2LIS_03_BF_BE','ZNK_SO_BDBS_ALLOC_BD','ZNK_TD_3AVASSO_BN','0EC_PCA_3_BD','ZNK_TD_3AVAP_BE','2LIS_11_VAITM_BE','0CUST_SALES_ATTR_BN','0EC_PCA_3_BE','2LIS_13_VDITM_BN','2LIS_11_VASTH_BD','2LIS_13_VDITM_BD','0CUST_SALES_ATTR_BD','ZNK_TD_3AVASSO_BD','2LIS_02_SCN_BE','2LIS_08TRTS_BD','0CUSTOMER_ATTR_BN','ZNK_TD_3AVASSO_BE','ZNK_ZVBW_MSLB_BE','ZNK_TD_3AVAP_BD','0CUSTOMER_TEXT_BN','6DB_J_3ABD_DELTA_US_AD','0CUSTOMER_TEXT_BD','2LIS_11_VAHDR_BN','ZNK_SO_BDBS_ALLOC_BN','0GL_ACCOUNT_TEXT_BE','0GL_ACCOUNT_TEXT_BD','2LIS_11_VAITM_BD','ZNK_TD_3AVATL_BE','ZNK_SO_BDBS_ALLOC_BE','ZNK_EBAN_BE','ZNK_SO_BDSI_OPNDMD_BN','ZNK_SD_LIKPPS_BD','ZNK_ZVBW_RTN_ORD_ITM_BE','2LIS_08TRTS_BN','2LIS_02_HDR_BE','ZNK_TD_3AVATL_BD','ZNK_VBPA_BE','ZNK_FX_CRCY_HIS_BD','2LIS_13_VDHDR_BE','NK_ADDR_NUMBR_BD','2LIS_12_VCITM_BD','2LIS_08TRTK_BD','2LIS_11_VASCL_BN','ZNK_ZVBW_MCHB_BE','6DB_J_3ABD_SCL_DELTA_AP_AE','ZNK_SO_BDSI_OPNDMD_BD','ZNK_KNVP2_BE','0MAT_SALES_ATTR_BE','ZNK_TD_3AVAP_BN','2LIS_13_VDHDR_BD','0GL_ACCOUNT_ATTR_BD','2LIS_02_SCL_BD','ZNK_VBPA_BD','2LIS_02_ITM_BD','ZNK_TD_3AVATL_BN','ZNK_ZVBW_RTN_ORD_ITM_BD','ZNK_PO_FX_CALC_LOG_BE','6DB_J_3ABD_DELTA_EMEA_AD','0GL_ACCOUNT_ATTR_BE','2LIS_03_BF_BD','2!
 LIS_11_V
ASTI_BE','0CO_OM_CCA_9_BD','0CUST_SALES_ATTR_BE','2LIS_12_VCITM_BE','0CUSTOMER_ATTR_BD','2LIS_02_ITM_BE','2LIS_08TRTLP_BE','2LIS_12_VCHDR_BD','ZNK_EBAN_BD','2LIS_08TRTS_BE','2LIS_02_SCL_BN','2LIS_11_VASTH_BE','ZNK_SD_LIKPPS_BE')
---AND A.LOGSYS = D."/BIC/NKLOGSYST"
---AND A.OBJVERS = 'A'
---AND A.TRANSTRU =  B.ODSNAME 
---AND B.DATETO = 99990101
---AND B.OBJSTAT = 'ACT'
---AND B.ODSNAME_TECH = C.ODSNAME_TECH
---AND C.DELFLAG <> 'X'
---AND D."/BIC/NIGNRCSYS" = ('R3_PRA','R3_PRD','R3_PRF','EM_EMP')
---AND D.OBJVERS = 'A'
---AND E.REQUID = C.REQUEST
---AND F.RNR = C.REQUEST
---AND F.MSGNO = '344'
---AND F.AUFRUFER = '09'---

SELECT A.LOGSYS , 
D."/BIC/NIGNRCSYS" ,
B.ODSNAME ,
C.ODSNAME_TECH , 
C.PARTNO , 
C.REQUEST ,
E.SID 
FROM
sapbzd."RSISOSMAP"  A, 
sapbzd."RSTSODS"      B, 
sapbzd."RSTSODSPART"   C,
sapbzd."/BIC/PNIGNRCSYS"   D,
sapbzd."/BI0/SREQUID"   E,
sapbzd."RSMONMESS"    F
WHERE 
A.OLTPSOURCE in ('ZNK_SHP_DDLN_CREATE_BE','ZNK_KNVP2_BD','ZNK_ZVBW_RTN_ORD_ITM_BN','2LIS_02_SCL_BE','ZNK_FX_CRCY_HIS_BE','ZNK_PO_FX_CALC_LOG_BD','2LIS_12_VCHDR_BE','2LIS_02_HDR_BN','ZNK_SHP_DDLN_CHANGE_BD','1_CO_PAGL11000N1_BE','0CUSTOMER_ATTR_BE','2LIS_08TRTLP_BD','2LIS_02_SCN_BD','2LIS_02_HDR_BD','2LIS_13_VDITM_BE','0CO_OM_CCA_9_BE','ZNK_SO_BDSI_OPNDMD_BE','2LIS_11_VAHDR_BE','ZNK_ZVBW_MBEW_BE','2LIS_13_VDHDR_BN','ZNK_SHP_DDLN_CHANGE_BE','NK_ADDR_NUMBR_BN','0CUSTOMER_TEXT_BE','6DB_J_3ABD_DELTA_AFFL_AD','0MAT_PLANT_ATTR_BE','ZNK_BDCPV_BD','1_CO_PAGL11000N1_BD','2LIS_11_VASTI_BD','ZNK_ZVBW_MSKU_BE','ZNK_SHP_DDLN_CREATE_BD','0SCEM_1_BC','2LIS_11_VAHDR_BD','2LIS_11_VASCL_BD','0MATERIAL_TEXT_BE','0MATERIAL_ATTR_BE','ZNK_BDCPV_BE','2LIS_02_ITM_BN','2LIS_11_VASCL_BE','2LIS_11_VAITM_BN','NK_ADDR_NUMBR_BE','2LIS_08TRTK_BE','ZNK_SD_LIKPPS_BN','2LIS_03_BF_BE','ZNK_SO_BDBS_ALLOC_BD','ZNK_TD_3AVASSO_BN','0EC_PCA_3_BD','ZNK_TD_3AVAP_BE','2LIS_11_VAITM_BE','0CUST_SALES_ATTR_BN','0EC_PCA_3_BE','2LIS_13_VDITM_BN','2LIS_11_VASTH_BD','2LIS_13_VDITM_BD','0CUST_SALES_ATTR_BD','ZNK_TD_3AVASSO_BD','2LIS_02_SCN_BE','2LIS_08TRTS_BD','0CUSTOMER_ATTR_BN','ZNK_TD_3AVASSO_BE','ZNK_ZVBW_MSLB_BE','ZNK_TD_3AVAP_BD','0CUSTOMER_TEXT_BN','6DB_J_3ABD_DELTA_US_AD','0CUSTOMER_TEXT_BD','2LIS_11_VAHDR_BN','ZNK_SO_BDBS_ALLOC_BN','0GL_ACCOUNT_TEXT_BE','0GL_ACCOUNT_TEXT_BD','2LIS_11_VAITM_BD','ZNK_TD_3AVATL_BE','ZNK_SO_BDBS_ALLOC_BE','ZNK_EBAN_BE','ZNK_SO_BDSI_OPNDMD_BN','ZNK_SD_LIKPPS_BD','ZNK_ZVBW_RTN_ORD_ITM_BE','2LIS_08TRTS_BN','2LIS_02_HDR_BE','ZNK_TD_3AVATL_BD','ZNK_VBPA_BE','ZNK_FX_CRCY_HIS_BD','2LIS_13_VDHDR_BE','NK_ADDR_NUMBR_BD','2LIS_12_VCITM_BD','2LIS_08TRTK_BD','2LIS_11_VASCL_BN','ZNK_ZVBW_MCHB_BE','6DB_J_3ABD_SCL_DELTA_AP_AE','ZNK_SO_BDSI_OPNDMD_BD','ZNK_KNVP2_BE','0MAT_SALES_ATTR_BE','ZNK_TD_3AVAP_BN','2LIS_13_VDHDR_BD','0GL_ACCOUNT_ATTR_BD','2LIS_02_SCL_BD','ZNK_VBPA_BD','2LIS_02_ITM_BD','ZNK_TD_3AVATL_BN','ZNK_ZVBW_RTN_ORD_ITM_BD','ZNK_PO_FX_CALC_LOG_BE','6DB_J_3ABD_DELTA_EMEA_AD','0GL_ACCOUNT_ATTR_BE','2LIS_03_BF_BD','2LIS_11_V!
 ASTI_BE'
,'0CO_OM_CCA_9_BD','0CUST_SALES_ATTR_BE','2LIS_12_VCITM_BE','0CUSTOMER_ATTR_BD','2LIS_02_ITM_BE','2LIS_08TRTLP_BE','2LIS_12_VCHDR_BD','ZNK_EBAN_BD','2LIS_08TRTS_BE','2LIS_02_SCL_BN','2LIS_11_VASTH_BE','ZNK_SD_LIKPPS_BE')
AND A.LOGSYS = D."/BIC/NKLOGSYST"
AND A.OBJVERS = 'A'
AND A.TRANSTRU = B.ODSNAME 
AND B.DATETO = 99990101
AND B.OBJSTAT = 'ACT'
AND B.ODSNAME_TECH = C.ODSNAME_TECH
AND C.DELFLAG <> 'X'
AND D."/BIC/NIGNRCSYS" in ('R3_PRA','R3_PRD','R3_PRF','EM_EMP')
AND D.OBJVERS = 'A'
AND E.REQUID = C.REQUEST
AND F.RNR = C.REQUEST
AND F.MSGNO = '344'
AND F.AUFRUFER = '09']
Fri Aug 08 16:00:04 2014 : READER_1_1_1> RR_4049 RR_4049 SQL Query issued to database : (Fri Aug 08 16:00:04 2014)
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8325 Final rollback executed for the target [Shortcut_to_EIS_REQUEST_LOG_DFX] at end of load
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8035 Load complete time: Fri Aug 08 16:00:04 2014

LOAD SUMMARY
============

WRT_8036 Target: EIS_REQUEST_LOG_26MAYBKP (Instance Name: [Shortcut_to_EIS_REQUEST_LOG_DFX])
WRT_8044 No data loaded for this target


WRT_8036 Target: T_delta_parm_file (Instance Name: [Shortcut_to_T_delta_parm_file])
WRT_8044 No data loaded for this target

*****Log file ends******

Please help me on this....

[toc] | [next] | [standalone]


#79076

Fromalex23 <wuwei23@gmail.com>
Date2014-10-14 15:39 +1000
Message-ID<m1icv8$auh$1@dont-email.me>
In reply to#79074
On 14/10/2014 11:47 AM, Sagar Deshmukh wrote:
> I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..
> My requirement is i would like to read the SQL query completly and write it to another txt file..

Generally we encourage people to post what they've tried to the list. It 
helps us identify what you know and what you need help with.

However, given:

 > the log file may not be always same so can not make static choices...

You'll probably want to use regular expressions:

     https://docs.python.org/howto/regex.html

Regexps let you search through the text for known patterns and extract 
any that match. To extract all SQL query sections, you'll need to come 
up with a way of uniquely identifying them from all other sections. 
Looking at your example log file, it looks like they're all of the format:

     SQL Query [<the actual sql query>]

 From that we can determine that all SQL queries are prefixed by 'SQL 
Query [' and suffixed by ']', so the content you want is everything 
between those markers. So a possible regular expression might be:

     SQL Query \[(.*?)\]

To quickly explain this:

     1. "SQL Query " matches on that string
     2. Because [] have meaning for regexes, to match on literal 
brackets you need to escape them via \[ and \]
     3. ( ) is a group, whats contained in here will be returned
     4. .* means to grab all matching text
     5. ? means to do an "ungreedy" grab ie it'll stop at the first \] 
it encounters.

Pulling the queries out of your log file should be as simple as:

     import re

     log = open('logfile').read()
     queries = re.findall("SQL Query \[(.*?)\]", log, re.DOTALL)

Because the queries can fall across multiple lines, the re.DOTALL flag 
is required to treat EOL markers as characters.

Hope this helps.

[toc] | [prev] | [next] | [standalone]


#79078

FromSagar Deshmukh <sagarddd@gmail.com>
Date2014-10-13 23:09 -0700
Message-ID<1bdfe07b-45f4-4549-959e-3eb176a8b055@googlegroups.com>
In reply to#79076
On Monday, 13 October 2014 22:40:07 UTC-7, alex23  wrote:
> On 14/10/2014 11:47 AM, Sagar Deshmukh wrote:
> 
> > I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..
> 
> > My requirement is i would like to read the SQL query completly and write it to another txt file..
> 
> 
> 
> Generally we encourage people to post what they've tried to the list. It 
> 
> helps us identify what you know and what you need help with.
> 
> 
> 
> However, given:
> 
> 
> 
>  > the log file may not be always same so can not make static choices...
> 
> 
> 
> You'll probably want to use regular expressions:
> 
> 
> 
>      https://docs.python.org/howto/regex.html
> 
> 
> 
> Regexps let you search through the text for known patterns and extract 
> 
> any that match. To extract all SQL query sections, you'll need to come 
> 
> up with a way of uniquely identifying them from all other sections. 
> 
> Looking at your example log file, it looks like they're all of the format:
> 
> 
> 
>      SQL Query [<the actual sql query>]
> 
> 
> 
>  From that we can determine that all SQL queries are prefixed by 'SQL 
> 
> Query [' and suffixed by ']', so the content you want is everything 
> 
> between those markers. So a possible regular expression might be:
> 
> 
> 
>      SQL Query \[(.*?)\]
> 
> 
> 
> To quickly explain this:
> 
> 
> 
>      1. "SQL Query " matches on that string
> 
>      2. Because [] have meaning for regexes, to match on literal 
> 
> brackets you need to escape them via \[ and \]
> 
>      3. ( ) is a group, whats contained in here will be returned
> 
>      4. .* means to grab all matching text
> 
>      5. ? means to do an "ungreedy" grab ie it'll stop at the first \] 
> 
> it encounters.
> 
> 
> 
> Pulling the queries out of your log file should be as simple as:
> 
> 
> 
>      import re
> 
> 
> 
>      log = open('logfile').read()
> 
>      queries = re.findall("SQL Query \[(.*?)\]", log, re.DOTALL)
> 
> 
> 
> Because the queries can fall across multiple lines, the re.DOTALL flag 
> 
> is required to treat EOL markers as characters.
> 
> 
> 
> Hope this helps.

Hi,

This helps and its working... Thank you so much....

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.python


csiph-web