Parsing specific keywords in Select Statements and formatting

Question

I have a sample select statement:

Select D.account_csn, D.account_key, D.industry_id, I.industry_group_nm, I.industry_segment_nm From ecs.DARN_INDUSTRY I JOIN ecs.DARN_ACCOUNT D
ON I.SRC_ID=D.INDUSTRY_ID
WHERE D.ACCOUNT_CSN='5070000240'

I would like to parse the select statements into separate files. The first file name is called ecs.DARN_INDUSTRY and inside the file it should look like this:

industry_group_nm
industry_segment_nm

Similarly another file called ecs.DARN_ACCOUNT and the content looks like this:

account_csn
account_key
industry_id

How do I do this in Bash or Python??

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

I doubt you will find a truly simple answer (maybe someone can prove otherwise). However, you might find python-sqlparse useful.

Parsing general SQL statments will be complicated and it is difficult to guess exactly what you are trying to accomplish. However, I think you are trying to extract the tables and corresponding column references via sql parsing, in which case, look at this question which basically asks that very thing directly.

edited May 23 '17 at 12:33

Community

1
1

answered Jul 15 '14 at 05:47

Gary Walker

8,831
3
19
41

I'll take a look. Thank you for showing me this module. – stanley_manley Jul 16 '14 at 20:47

score 1 · Answer 2 · edited Jan 24 '17 at 14:45

1

Here is a long working command through awk,

awk 'NR==1{gsub(/^.*\./,"",$5);gsub(/^.*\./,"",$6);gsub(/.$/,"",$5); printf $5"\n"$6"\n" > "DARN_INDUSTRY"; gsub(/^.*\./,"",$2);gsub(/^.*\./,"",$3);gsub(/^.*\./,"",$4);gsub(/.$/,"",$2);gsub(/.$/,"",$3);gsub(/.$/,"",$4); printf $2"\n"$3"\n"$4"\n" > "DARN_ACCOUNT"}' file

Explanation:

gsub(/^.*\./,"",$5) remove all the characters upto the first . symbol in colum number 5.
printf $5"\n"$6"\n" > "DARN_INDUSTRY" redirects the output of printf command to the file named DARN_INDUSTRY.
gsub(/.$/,"",$4) Removes the last character in column 4.

edited Jan 24 '17 at 14:45

marc_s

732,580
175
1,330
1,459

answered Jul 15 '14 at 06:13

Avinash Raj

172,303
28
230
274

Thank you for answering my question. But this solution looks like it works for a specific scenario. I may have other tables which may be difficult to differentiate. Just by column numbers. I was hoping on getting a script that can dynamically generate the files by regex and some pattern matching criterias – stanley_manley Jul 16 '14 at 20:47

Parsing specific keywords in Select Statements and formatting

2 Answers2