Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.


awk - split data based on the count


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
awk - split data based on the count

Greetings Experts,
I am generating a validation query through awk and facing an issue, which I need to overcome by splitting the data based on the pattern matching count in the value of an array.
Code:
File1  -- 
Table11@column1@date@Table21@column1@varchar(10)@d;
Table11@column2@varchar@Table21@column2@date@d;
Table11@column3@varchar@Table21@column3@date@d;
............
Table11@column100@varchar@Table21@column100@date@d;
Table11@column101@varchar@Table21@column101@date@d;
............
Table11@column320@integer@Table21@column320@date@d;
............
Table19@column1@integer@Table29@column1@varchar@o;
Table19@column2@integer@Table29@column2@varchar@b;
Table19@column3@integer@Table29@column3@varchar@o;

Here Table11 can contain more than 100 columns with type as "d" or "o" which is specified in the last column i.e., there can be more than 100 entries in the combination of column1, column4, column7 there by making it more than 100 entries in the file. My awk script is
Code:
awk -F "@" 'BEGIN { RS=";" ; OFS=","}
{
str="SUM(CASE WHEN T1." $2 "=T2." $5 " THEN 1 ELSE 0 END) " $2"_"$5
a[$1 OFS $4 OFS $7]=(a[$1 OFS $4 OFS $7] ? a[$1 OFS $4 OFS $7] ", \n" str : str
}
END {
for (i in a)
{
k=split(i,b,",")
print "SELECT " a[i] " FROM "b[1] " T1 \n INNER JOIN " b[2] " T2 \n ON T1.COLABC=T2.COLABC;" > "file_output.txt"
} }' File1.txt

I am getting the output as below for the combination of Table11, Table21,d
Code:
SELECT SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
......
SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

But as there is a restriction that I cannot export more than 100 columns in a single select (Teradata Bteq export restriction), I need to break down the contents of array a in case if there are more than 100 entries based on the column1, column4, column7 i.e.,
Code:
SELECT SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
......
SUM(CASE WHEN T1.column100=T2.column100 THEN 1 ELSE 0 END) column100_column100 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

SELECT SUM(CASE WHEN T1.column101=T2.column101 THEN 1 ELSE 0 END) column101_column101,
SUM(CASE WHEN T1.column102=T2.column102 THEN 1 ELSE 0 END) column102_column102,
......
SUM(CASE WHEN T1.column200=T2.column200 THEN 1 ELSE 0 END) column200_column200 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

.....

SELECT SUM(CASE WHEN T1.column301=T2.column301 THEN 1 ELSE 0 END) column301_column301,
SUM(CASE WHEN T1.column302=T2.column302 THEN 1 ELSE 0 END) column302_column302,
......
SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320 FROM Table11 T1
INNER JOIN Table21 T2 
ON T1.COLABC=T2.COLABC;

I am confused on how to handle this with in awk as I need to complete this task with in awk as there are several tables involved which can be processed easily with the array structure in awk. At first I thought of using another array to count++ for column1, column4, column7 combination and write a loop; I need to think on this; or make another array in the combination of x[$1 OFS $4 OFS $7 OFS cnt++] where the variable cnt would be specific to each combination of $1 OFS $4 OFS $7; I am not sure how to do this as the array may not be processed in the increasing order of cnt for the combination of $1 OFS $4 OFS $7 Another alternative is to make a comparison on the contents of the array to check if there are more than 100 SUM(CASE If yes, then split them and process; I am stuck up badly on this and in need of your help greatly. Thank you for your time..
# 2  
x[$1 OFS $4 OFS $7 OFS cnt++] does not make sense.
A counter for each col1,col4,col7 combination is cnt[$1 OFS $4 OFS $7]++ i.e. every cnt element is a counter.
# 3  
Hi MadeInGermany,

Thank you for the suggestion. Creating one more array to count the combination of $1 OFS $4 OFS $7 and then I need to find a way to go through this algorithm
Code:
for (i in a) {
k=split(i,b,",")
for (z=1; z<=cnt[i]; (z+100))  {
y=get the z to z+99 SUM(CASE matching ones here
print "SELECT " y " FROM "b[1] " T1 \n INNER JOIN " b[2] " T2 \n ON T1.COLABC=T2.COLABC;" > "file_output.txt"
}
}

I don't know how to get the z to z+99 matching SUM(CASE from the array a with in the loop. Can someone kindly explain how to achieve this. Please note that storing every part of z to z+99 in another array++is not feasible for my script as that makes the script dependent on the data on the combination of $1 OFS $4 OFS $7
# 4  
That was a hell of a specification to understand - not sure I did to its entirety. Anyway, try
Code:
awk -F "@" '
BEGIN   {FMT1 = "%s%s\tSUM(CASE WHEN T1.%s=T2.%s THEN 1 ELSE 0 END) %s_%s"
         FMT2 = "\nFROM %s T1 INNER JOIN %s T2 ON T1.COLABC=T2.COLABC;\n\n"
        }
        {IX = $1 "," $4 "," $7
         ARR[IX "," ++CNT[IX]] = $2 "_" $5
        }
END     {for (c in CNT) {split  (c, TBL, ",")
                         printf "SELECT "
                         LC = 0
                         LF = DL = ""
                         for (i=1; i<=CNT[c]; i++)      {split (ARR[c "," i], COL, "_")
                                                         printf FMT1, DL, LF, COL[1], COL[2], COL[1], COL[2]
                                                         DL = ","
                                                         LF = "\n"
                                                         if (!(++LC%LNMX) &&
                                                                i<CNT[c])       {printf FMT2, TBL[1], TBL[2] 
                                                                                 printf "SELECT "
                                                                                 DL = LF = ""
                                                                        }
                                                        }
                         printf FMT2, TBL[1], TBL[2] 
                        }
        }
' LNMX=3 file
SELECT 	SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
	SUM(CASE WHEN T1.column3=T2.column3 THEN 1 ELSE 0 END) column3_column3
FROM Table19 T1 INNER JOIN Table29 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2
FROM Table19 T1 INNER JOIN Table29 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column1=T2.column1 THEN 1 ELSE 0 END) column1_column1,
	SUM(CASE WHEN T1.column2=T2.column2 THEN 1 ELSE 0 END) column2_column2,
	SUM(CASE WHEN T1.column3=T2.column3 THEN 1 ELSE 0 END) column3_column3
FROM Table11 T1 INNER JOIN Table21 T2 ON T1.COLABC=T2.COLABC;

SELECT 	SUM(CASE WHEN T1.column100=T2.column100 THEN 1 ELSE 0 END) column100_column100,
	SUM(CASE WHEN T1.column101=T2.column101 THEN 1 ELSE 0 END) column101_column101,
	SUM(CASE WHEN T1.column320=T2.column320 THEN 1 ELSE 0 END) column320_column320
FROM Table11 T1 INNER JOIN Table21 T2 ON T1.COLABC=T2.COLABC;

Set the max No. of lines LNMX to 100 if happy with the result and dealing with your large files...
This User Gave Thanks to RudiC For This Post:
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Split files based on row delimiter count

I have a huge file (around 4-5 GB containing 20 million rows) which has text like: <EOFD>11<EOFD>22<EORD>2<EOFD>2222<EOFD>3333<EORD>3<EOFD>44<EOFD>55<EORD>66<EOFD>888<EOFD>9999<EORD> Actually above is an extracted file from a Sql Server with each field delimited by <EOFD> and each row ends... (8 Replies)
Discussion started by: amvip
8 Replies

2. Shell Programming and Scripting

awk to count and rename based on fields

In the below awk using the tab-delimited input, I am trying count the - symbol in $5 and output the count as well as the renamed condition ins. I am also count the - symbol in $6 and output the count as well as the renamed condition del. I am also count the tomes that in $5 and $6 there are... (6 Replies)
Discussion started by: cmccabe
6 Replies

3. Shell Programming and Scripting

awk script to split file into multiple files based on many columns

So I have a space delimited file that I'd like to split into multiple files based on multiple column values. This is what my data looks like 1bc9A02 1 10 1000 FTDLNLVQALRQFLWSFRLPGEAQKIDRMMEAFAQRYCQCNNGVFQSTDTCYVLSFAIIMLNTSLHNPNVKDKPTVERFIAMNRGINDGGDLPEELLRNLYESIKNEPFKIPELEHHHHHH 1ku1A02 1 10... (9 Replies)
Discussion started by: viored
9 Replies

4. Shell Programming and Scripting

KSH: Split String into smaller substrings based on count

KSH HP-SOL-Lin Cannot use xAWK I have several strings that are quite long and i want to break them down into smaller substrings. What I have String = "word1 word2 word3 word4 .....wordx" What I want String1="word1 word2" String2="word 3 word4" String3="word4 word5" Stringx="wordx... (5 Replies)
Discussion started by: nitrobass24
5 Replies

5. Shell Programming and Scripting

Split File data using awk

HI Guys, I need to split the file in to number of files . file contains FILEHEADER and EOF . I have to split n number of times . I have to form the file with each splitted message between FILEHEADER and EOF using awk beign and end . how to implement please suggest. (2 Replies)
Discussion started by: manish8484
2 Replies

6. Shell Programming and Scripting

split file based on group count

Hi, can some one please help me to split the file based on groups. like in the below scenario x indicates the begining of the group and the file should be split each with 2 groups below there are 10 groups it should create 5 files. could you please help? (4 Replies)
Discussion started by: hitmansilentass
4 Replies

7. Shell Programming and Scripting

awk script to split field data

Hi Experts, I have a Input.txt document which contains data fields seperated by tabs. There are 4 fields totally Named UNIQUE, ORDER, CONTACT and WINS. The UNIQUE field contains unique ID and the CONTACT field contains data seperated by comma in some records. I am looking to write an awk script... (12 Replies)
Discussion started by: forumthreads
12 Replies

8. Shell Programming and Scripting

using awk to count no of records based on conditions

Hi I am having files with date and time stamp as the folder names like 200906051400,200906051500,200906051600 .....hence everyday 24 files will be generated i need to do certain things on this 24 files daily file contains the data like 200906050016370 0 1244141195225298lessrv3 ... (13 Replies)
Discussion started by: aemunathan
13 Replies

9. Shell Programming and Scripting

Split a file based on pattern in awk, grep, sed or perl

Hi All, Can someone please help me write a script for the following requirement in awk, grep, sed or perl. Buuuu xxx bbb Kmmmm rrr ssss uuuu Kwwww zzzz ccc Roooowwww eeee Bxxxx jjjj dddd Kuuuu eeeee nnnn Rpppp cccc vvvv cccc Rhhhhhhyyyy tttt Lhhhh rrrrrssssss Bffff mmmm iiiii Ktttt... (5 Replies)
Discussion started by: kumarn
5 Replies

10. Shell Programming and Scripting

awk script to split a file based on the condition

I have the file with the records like 4234234 US phone 3244234 US cup 2342342 CA phone 8947234 US phone 2389472 CA cup 2348972 US maps 3894234 CA phone I want the records with (US,phone) as record to be in one file, (Us, cup) in another file and (CA,cup) to be in another I mean all... (12 Replies)
Discussion started by: superprogrammer
12 Replies

Featured Tech Videos