Forum Discussion

mohit_jain's avatar
mohit_jain
New Contributor III
3 years ago
Solved

Comparing the schema and discard the Object

Hi Team, I have one scenario and want to solve it as soon as possible. I have two files : Initial Load , Incremental Load We have loaded the initial load data into the DB which contains the column...
  • virender_prajap's avatar
    virender_prajap
    3 years ago

    mohit_jain the solution is very straight forward.
    1. Get the header from your initial file.
    2. Merge it with your incremental file.
    3. Filter match and unmatched in two different output streams.

    Download solution pipeline Compare_Schema_Discard_load & below is the screenshot.

mohit_jain's avatar
mohit_jain
New Contributor III
3 years ago

Hi alchemiz 

Thanks for you reply
The only thing is here we are not getting headers from the pipeline parameter( There are no parent and child pipeline in this). We have a 2 files . 
1. Initial file - which record is already dropped into the SFTP location.
2. Incremental File - File will come on the daily basis.
We have to compare both the files header and fetch the extra call out the load the unfetch column.
Here I am attaching two files for you reference can you please perform on this file in the pipeline and fetch the extra column out.
I am also attaching the pipeline for you reference.
Initial Load:

Incremental Load: 

Here we have to discard the Column E and load the A, B, C, D column. Also Fetch the E column and give alert that there is E column extra in the file

Pipeline Design :

Thanks 

Mohit Jain

 

 

 

virender_prajap's avatar
virender_prajap
New Contributor III
3 years ago

mohit_jain the solution is very straight forward.
1. Get the header from your initial file.
2. Merge it with your incremental file.
3. Filter match and unmatched in two different output streams.

Download solution pipeline Compare_Schema_Discard_load & below is the screenshot.

  • mohit_jain's avatar
    mohit_jain
    New Contributor III
    3 years ago

    Thank you so much virender_prajap 
    It works for me.....  ğŸ˜Š

  • mohit_jain's avatar
    mohit_jain
    New Contributor III
    3 years ago

    Hi virender 

    Do we have can compare the json which is coming into the string also with the same scenario
    We have to compare 2 things the col_name and the data type of the json and fetch out the unmatched value

    Let me give you one example:-
    Original Json :- It contains the col_name and the data_type of the column name
    [{"metadata":"{col_name=ID_CODE, data_type=varchar(32768)},{col_name=ABC_CODE, data_type=varchar(32768)},{col_name=PSD_CODE, data_type=varchar(32768)},{col_name=NUM_REGIST, data_type=varchar(32768)},{col_name=PROD_ORIG_NAME1, data_type=varchar(32768)}"}]

    And the updated one is 
    [{"metadata":"{col_name=ID_CODE, data_type=varchar(32768)},{col_name=ABC_CODE, data_type=varchar(32768)},{col_name=PSD_CODE, data_type=varchar(32768)},{col_name=NUM_REGIST, data_type=varchar(32768)},{col_name=PROD_ORIG_NAME1, data_type=varchar(32768)},{col_name=PROD_ORIG_NAME2, data_type=varchar(32768)}"}]
    Now here we are getting the {col_name=PROD_ORIG_NAME2, data_type=varchar(32768)} as the extra column and we have to fetch the out to unmacted value.
    Can you please help me in this scenario also and please make this note we have to compaire both the col_name and data_type also.

    Really thanks in advace.