Positional file into vertica

[Anonymous]

Hi,

I have a requirement of loading around 10Million data into my vertica database With certain rules as below:

1. One record can span to multiple rows and they are identified using segments. Every record start at segment 0001 and ends at segment 0005. segment 0001 contains record for Table say A, segment 0002 contains record for Table B , segment 0003 for Table C, segment 0004 for Table D and 0005 for Table E.

2. using Col1, Col2 and current date (format yyyymmdd), I need to form a new value say PaymentID but this information is only in segment 003 or segment 0005

Since I am new to this tool, my approach to solve the problem is very naive. What I am trying to make it professional, fast and acceptable is to load all data in Staging/Temp table in vertica DB with a new col - paymentid (Already Done). Then use ELT components like tVerticaInput,tELTVerticaMap and tELTVerticaOutput or simple verticainput,join,map,verticaoutput to check and update the payment id for one record which falls between segment 0001 to 0005.

Problem is I am not able to use tVerticaInput to join with multiple tJoins. My question here to all the experts are:

1. What do you suggest the better approach to solve this problem?
2. How is it possible to use tVerticaInput to join with multiple tJoins components?
3. How do I monitor the progress of a job I mean is it possible to log the progress of the job in some table?

Appreciate all the help and suggestion.

Onkar

[Anonymous]

Hello Onkar 

1. I don't understand your requirement well, so I can't give you specific answer or job design for now. Can you show us an example with some data?
2. You can use tMap instead of tJoin if there are more than one lookup table. 
3. It is impossible to monitor the progress of a job execution.

Regards
Shong

[Anonymous]

Shong,
Thanks for the reply. Here is basic data:

A1CG0000000053975482        00331       0002           00331Hmsinfortel     PO Box245875
A2CG0000000053975482        0033100331.4.-902200605.110115-145-19    0003     00331DOUGLAS MICHEAL
A3CG0000000053975482        0033100331.4.-902200605.1110115-145-19    00041   000198941
A4CG0000000053975482        00331      0005   028 MAXIMUM NUMBER

In this case, all of the above are one single record however they all belong to separate table due to different segments. First 2 characters are segment which is SEGMENT_ID column in table. From CG... to 0001 upto the bold part belong to second field called BASE_ID and so on so forth. There are some third party payors say for ex TPA and another col say Remittance Claim Number(RCN). These two TPA & RCN is concatenated with current date (form yyyymmdd) and forms a new col say Final Claim Number (FCN).

Here is the main issue: These two details TPA & RCN are available only in segment A3 and not in other segments. What I need to do is extract all the segments (there is no delimited fields. they are positional) and put in separate table along with new column FCN formed from segment A3.

I am able to extract all the columns and create FCN also but not able to add this column in all the tables. The icing on the cake is this FCN will be different for all the BASE_IDs however they will remain same for a given set of segment from A1 to A5. Next record will again start from A1 to A5.

Hope I am able to explain my problem.

Onkar