question Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json. in Support Questions

Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

praful123 — Thu, 16 Feb 2023 16:32:35 GMT

Please help design data flow in nifi, I need to insert the incoming JSON data into table which having single column. The condition are I need to concat all the keys and values and insert that data.

Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

SAMSAL — Thu, 16 Feb 2023 17:36:34 GMT

Hi ,

Can you provide sample\example data of the json and how do you expect to save it as ?

Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

praful123 — Fri, 17 Feb 2023 04:26:16 GMT

Hi, SAM. Thank you for response. I am using ListenHTTP to get json data-->ConvertRecord(to validate json) -->SplitJson(to split)-->Now I have individial Json Objects. Now I have concatenate all the keys and values of each json object and insert to a table have single column called JSON_DATA.

This is my input data,

[ {"Name":"Joh ","Age": 2,"Occupation":"Developer", "Location": "New York","Salary": 75780},

{"Name": "Ja","Age": 9,"Occupation": "Designer", "Location": "San Francisco","Salary": 87800},

{"Name": "Bb kkkk","Age": 5,"Occupation": "Manager", "Location": "London","Salary": 90870} ]...I want insert each json obj inot each new row as string like this in a snowflake table ' {"Name":"Joh ","Age": 2,"Occupation":"Developer", "Location": "New York","Salary": 75780} '

Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

SAMSAL — Fri, 17 Feb 2023 15:08:30 GMT

Hi,

thanks for the information , I came across situation before and I'm not sure if there is better way but you can use the following processors after you split to data into individual records :

ExtractText -> PUTSQL

For the ExtractText:

1- Add dynamic Property to capture all the Json Content from incoming flowfile as follows:

Note: You have to be careful with if each record data can be large (> 1024 Chars). In this case you need to look into modifying "Maximum Buffer Size" and "Maximum Capture Group Length" accordingly otherwise the data will be truncated.

For the PutSQL: Once you configure the JDBC Connection Pool , you can set the SQL Statement Property with something like this:

insert into myTable (jsonCol) values ('${JsonRecord}')

If that helps please accept solution

Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

praful123 — Mon, 20 Feb 2023 05:45:50 GMT

Hi SAM, Thank you for response. I am facing one more error, while inserting data into table. Even though i have tables named test4 in my snowflake table, The PUTSQL processor keep telling "The Table 'test4' doesn't exist or not Authorized(I did give permission too). This is my sql statement(INSERT INTO test4 (JSON_DATA) values ('${JsonRecord}').

Any help will be appreciated.

Re: Apache Nifi: Insert json data into table as single column data into table after concatenating all the keys and values of json.

praful123 — Mon, 20 Feb 2023 06:34:59 GMT

Hi SAM, Actually it worked for me now. No error now. Thank you for your help.