I am developing an ETL job whereby a variety of different JSON schemas are extracted from CouchDB and then converted to RDBMS. This will be a flat JSON documents after 2 level (which needs to be extracted). Each key in the JSON is mapped 1 to 1 to a column in a table. Right now, I am manually creating a JSON file in metadata for each schema, and manually mapping it to the corresponding table. However, in a few months, the amount of different JSON schemas received will increase exponentially, so manually creating a metadata file and mapping for each schema will not be feasible.
Is there a way I can build a job that generically takes any JSON schema I throw at it, and automatically maps the value to a column in a table (where the name of a column = the JSON key), maybe using tSetDynamicSchema, or write/extract dynamic fields?
Adding the information to the problem, each different type of schema goes into a different table. The data from another configuration paramters defines what the target table is, which is currently achieved by setting the relevant key/value at the configuration DB. I can take the variable (containing the table name) to the context variable and push the data to the relevant table.
P.S.: I'm trying to implement the solution on Big Data Sandbox 6.2. and the Target System is MSSQL.
A quick revert with example will be much appreciated.