Mapping dynamically for multiple objects using one pipeline(without a mapper for each object)

[userg]

Hello,

My input is S3, where we are receiving files of different objects. Our target is to read these files and insert data into postgres DB where we have created target tables for all objects. How can we  dynamically map all the objects using one control table that will hold input column name and target column name. So if we add a new column in the control table and in target_table in future the pipeline should be able to map and insert the data into postgres DB.

Control_table :

object_name(target_table_name)	Input_column_name(column name from file)	Target_column_name(column name in postgres target table)

Thanks in advance.

[koryknick]

@userg - Take a look at the documentation for the input views of the CSV Parser snap.  There is a second input view that you can use to define the input columns coming from the input view.  I would simply name the fields based on the target column names so that no real translation is required.  So use the column ordering of the input, but use the column naming of the target.

Hope this helps!

[userg]

Hi @koryknick ,

We cannot map it dynamically rit, we are going to get new files(for new objects) and column names. So everytime we get a new field or file we  need to map it manually in the pipeline. Also we have different Target tables to insert. Can we do it using  a control table  , is it possible for real time translation.

Thank you for your response.

[koryknick]

@userg - the point of second input view on the CSV Parser snap is to allow you to read the file generically.  So you would be able to place your table query as the source that defines the input file in the 2nd input view.  Your pipeline would look something like this:

Assuming that you use the default config for the Gate snap, and that you read only the target_column_name value from your definition table in the order of input_column_name, then your expression in the Mapper would look like this:

This is simply an Array.join() method to turn the array created by the Gate snap into a single string, with each array element separated by commas.

Note that I've updated the output view of the Mapper to Binary (rather than document) - this allows it to send a Target Path of $content that will be interpreted by the CSV Parser as a binary stream.  

Hope this helps!