SQL/Impala
I have a scenario where I need to join my input file with a mapping table which has map columns and derived columns. The join keys are dynamic and need to taken from a dynamic metadata table.
Metadata table can be modified by user to either update or insert new keys. The join happens in multiple passes. Pass1 will have one set of columns as keys , Pass2 will have another set of columns as join keys.
Pass2 derived values can overwrite Pass1 derived values. The derived columns are same through out.
Example:
Keys metadata table
Mapping Table
In
Input File
Join keys for Pass1 are A,B,C,D,E. Join will map values 10,20,30,40,50 from Input file to Values 10,20,30,40,50 in mapping table - DerivedValues1,2,3,4 will be derived.
Join Keys for Pass2 are A,B,C,D - Join will map values 10,20,30,40 from Input File to values 10,20,30,40 in mapping table - DerivedValues1,2,3,4 will be derived and can overwrite values derived in Pass1
In Pass3 - Column A and Derived Value3 will be join key. Join will map values 15, Jasmine to mapping table - DerivedValues1,2,3,4 will be derived.
Any idea how we could do this in SQL or Impala. Basically the platform is built in Big Data and we just need to write SQL like queries to implement this logic
Appreciate any help.
THanks Nitin
Key1 = 'E'for this row.