Ask Your Question
0

Nested JSON data to be inserted into multiple tables using JDBC Producer destination

asked 2019-04-25 22:40:49 -0600

mc gravatar image

I have a requirement to consume a nested JSON data where different parts of data will be inserted into different tables in an RDBMS. I was looking into to use JDBC Producer as destination but it seems it can only insert/update/delete into a single table?

I was thinking maybe i can process the consumed JSON first and process it into different JSON objects mapped to my RDBMS tables and setup different JDBC Producers. I checked different processors like Field Mapper, JSON Parser and likes but I only have the option to map a single field?

Is this flow doable in StreamSets? Any suggestions would help. Thanks.

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2019-04-26 15:28:49 -0600

iamontheinet gravatar image

updated 2019-04-26 15:46:41 -0600

Hi!

Consider the following nested JSON input:

{"widget": {
    "window": {
        "title": "Sample Konfabulator Widget",
        "name": "main_window",
        "width": 500,
        "height": 500
    },
    "image": { 
        "src": "Images/Sun.png",
        "name": "sun1",
        "hOffset": 250,
        "vOffset": 250,
        "alignment": "center"
    },
    "text": {
        "data": "Click Here",
        "size": 36,
        "style": "bold",
        "name": "text1",
        "hOffset": 250,
        "vOffset": 100,
        "alignment": "center",
        "onMouseUp": "sun1.opacity = (sun1.opacity / 100) * 90;"
    }
}}

Ingesting this data and writing to multiple tables window, image, and text dynamically can be accomplished by creating a pipeline that looks like this:

Field Pivoter:

This will get us three records with the map key we are pivoting on (window, image, and text) stored in new field widget_name.

image description

Expression Evaluator:

Create new record header attribute tableName based on the pivot field name using expression ${record:value('/widget_name')}.

image description

Field Remover:

Optionally remove widget_name.

image description

JDBC Producer:

Set Table Name to the new record header attribute created in Expression Evaluator above using expression ${record:attribute('tableName')}.

image description

This results in a total of three inserts with one record per table being inserted in respective table based on the record header attribute tableName.

image description

Hope this helps or at least gives you a good starting point.

Cheers, Dash

edit flag offensive delete link more

Comments

Hi Dash, Thank you. This definitely gave me a very good starting point with our complex data structure. Thanks again!

mc gravatar imagemc ( 2019-04-28 22:49:51 -0600 )edit

You're welcome!

iamontheinet gravatar imageiamontheinet ( 2019-04-29 09:01:08 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-25 22:40:49 -0600

Seen: 143 times

Last updated: Apr 26