Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Searching multiple patterns and construct SQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Searching multiple patterns and construct SQL
# 1  
Old 03-07-2018
Searching multiple patterns and construct SQL
Hello,

I have attached 2 files

1) Original_table_definition.txt => which has definition of 3 tables
2) describe_table_output.txt => which has again 3 tables definition gotten thorugh doing a show table or describe table way.

Now difference between 3 tables are that tablea has no partition tableb has 1 partition ( on column partb) and tablec has 2 partition ( partc1,partc2)

I am trying to extract data in below format from the describe_table_output.txt

Code:
tablea|NULL   --- because partitionKeys:[]  has nothing in it 
tableb|partb  --  because partitionKeys:[FieldSchema(name:partc1 means there is 1 partition for table
tablec|partc1|partc2 ---  because partitionKeys:[FieldSchema(name:partc1, type:string, comment:null), FieldSchema(name:partc2, type:string, comment:null)]  2 partiton for table

so that i can generate below SQL dynamically

Code:
ANALYZE table tableA COMPUTE STATISTICS;
ANALYZE TABLE tableb PARTITION(partb) COMPUTE STATISTICS;
ANALYZE TABLE tablec PARTITION(partc1,partc2) COMPUTE STATISTICS;

I have the below code to extract tablename from the describe_table_output.txt

Code:
grep -o 'Table(tableName:[^,]*' sample  | awk -F ':' '{ OFS="|";print $2}'

But i have a problem with how to search for the next pattern that is the partition columns

for tablea since there are no parttion column the text looks like this

Quote:
partitionKeys:[]
for tablea since there is only 1 partition it looks like this

Code:
partitionKeys:[FieldSchema(name:partb,

for tablec there are 2 partition so it looks like

Code:
partitionKeys:[FieldSchema(name:partc1, type:string, comment:null), FieldSchema(name:partc2,

Now please note that FieldSchema: key alone is not unique as it is used against individual columns as well as partitioned columns and we want only those FieldSchema: which are preceded by partitionKeys: keyword.

Request your inputs on how to resolve this .

My environment details are

Code:
 echo $BASH_VERSION
3.2.57(1)-release

uname -a
Linux sedcahdp0390 3.0.101-80-default #1 SMP Fri Jul 15 14:30:41 UTC 2016 (eb2ba81) x86_64 x86_64 x86_64 GNU/Linux

awk --version
GNU Awk 3.1.8

sed --version
GNU sed version 4.1.5


Thanks
Original_table_definition.txt (469 Bytes) 
describe_table_output.txt (3.9 KB) 
Last edited by Don Cragun; 03-07-2018 at 04:04 AM.. Reason: Change QUOTE tags to CODE tags, add NOPARSE tags to eliminate accidental smileys.
# 2  
Old 03-07-2018
How about
Code:
awk '
#                                       {gsub (/\r/, _)
#                                       }
match ($0, /tableName:[^,]*/)           {printf "Analyze Table %s ", substr ($0, RSTART+10, RLENGTH-10)
                                        }
match ($0, /partitionKeys:[[][^]]*]/)   {TMP = substr ($0, RSTART+15, RLENGTH-16)
                                         n   = gsub (/FieldSchema\(name:/, "\n", TMP)
                                         if (n) {printf "Partition ("
                                                 m = split (TMP, TARR, "\n")
                                                 for (i=2; i<=m; i++)   {sub (/,.*$/, _, TARR[i])
                                                                         printf "%s%s", TARR[i], i<m?",":""
                                                                        }
                                                 printf ") "
                                                }
                                         printf "Compute statistics;\n"
                                        }
' /tmp/describe_table_output.txt
Analyze Table tablea Compute statistics;
Analyze Table tableb Partition (partb) Compute statistics;
Analyze Table tablec Partition (partc1,partc2) Compute statistics;

Your files have non-*nix (DOS) line terminators (<CR> = 0x0D = \r = ^M), the commented-out gsub can take care of those should they irritate any algorithms.
This User Gave Thanks to RudiC For This Post:
nv186000
# 3  
Old 03-07-2018
Thanks RudiC . Appreciate your quick help. Non Unix line terminators might be because i copied the data from terminal to Notepad++ for uploading to the forum.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Searching multiple patterns using awk

Hello, I have the following input file: qh1adm 20130710111201 : tp import all QH1 u6 -Dsourcesystems=BFI,EBJ qh1adm 20130711151154 : tp import all QH1 u6 -Dsourcesystems=BFI,EBJ qx1adm 20130711151154 : tp count QX1 u6 -Dsourcesystems=B17,E17,EE7 qh1adm 20130711151155 : tp import all... (7 Replies)
Discussion started by: kcboy
7 Replies

2. UNIX for Dummies Questions & Answers

Help searching for patterns occuring near one another in document

I'm very new to unix and linux, so I apologize if the answer to this question should be obvious.. What I would like to know is, is there a way to search a text document ( opened in less, or some other text viewer) for any two or more patterns that appear near one another in the document? In... (1 Reply)
Discussion started by: Colonel Panic
1 Replies

3. Shell Programming and Scripting

How to print the next line by searching with different patterns in AIX server?

Hi, I am having an '.xml' file with 'n' number of lines and also having another file with '.txt' format contains values which i want to search. Now I want to print the next line with the pattern which i am searching in '.xml' file. And the loop has to repeat for different patterns which are... (4 Replies)
Discussion started by: tejastrikez
4 Replies

4. Shell Programming and Scripting

print the next line by searching with different patterns

Hi, I am having an '.xml' file with 'n' number of lines and also having another file with '.txt' format contains values which i want to search. Now I want to print the next line with the pattern which i am searching in '.xml' file. And the loop has to repeat for different patterns which... (5 Replies)
Discussion started by: tejastrikez
5 Replies

5. Shell Programming and Scripting

searching multiple patterns in perl

Hi, I have code like: Output it is comming as: Rels: WM2 Rels: WG2 Rels: 5 - pre/prods.pl Rels: 6 Rels: 7 Rels: 8 Rels: 10 Rels: Int But i want only "Rels: 5" pattern Just above "- pre/prods.pl". By... (7 Replies)
Discussion started by: Anjan1
7 Replies

6. Shell Programming and Scripting

Awk script searching patterns issue

Hi I am having a file like this FILE1 ##################### C16ROTINV_ REFCLK_RXL RXBCLK32_R REFCLK_TXL CLK8_TXLIN RXBCLK32_R DCLK_TXLIN CLK32D_TXL RXACLK32_R ##################### (3 Replies)
Discussion started by: jaita
3 Replies

7. Shell Programming and Scripting

Searching for multiple patterns in a file

Hi All, I have a file in which i have to search for a pattern from the beginning of the file and if the pattern is found , then i have to perform a reverse search from that line to the beginning of the file to get the first occurrence of another pattern. sample input file hey what are you... (8 Replies)
Discussion started by: Kesavan
8 Replies

8. Shell Programming and Scripting

Searching for multiple patterns in files

I have a situation where I need to search for multiple strings (error messages) such as 'aborted' 'file not found' etc in directory having logs. I have put all the error messages in a text file and using the command. grep -f <textfile> <filetobegrepped> I'm doing this thru a script where I... (5 Replies)
Discussion started by: bornon2303
5 Replies

9. Shell Programming and Scripting

Searching patterns in 1 file and deleting all lines with those patterns in 2nd file

Hi Gurus, I have a file say for ex. file1 which has 3500 lines in it which are different account numbers and another file (file2) which has 230000 lines in it. I want to read all the lines in file1 and delete all those lines from file2 which has that same pattern as in file1. I am not quite... (4 Replies)
Discussion started by: toms
4 Replies

10. UNIX for Dummies Questions & Answers

searching for two or more patterns in a line

how can I search for two or more patterns in one line using grep? for example if I want to show only the lines that have patterns "abc" and "123"? and what if I want to show only the lines that have either "abc" or "123" in them? any hint apprecited (4 Replies)
Discussion started by: metalwarrior
4 Replies
Login or Register to Ask a Question