question Re: Trouble with JsontoSQL processor in Nifi in Archives of Support Questions (Read Only)

Trouble with JsontoSQL processor in Nifi

cgambino — Wed, 20 Apr 2016 02:05:36 GMT

I am having trouble with my JsonToSQL processor in nifi. I am trying to post to

This table

'Home_Events', 'CREATE TABLE `Home_Events` (\n `hub_insteon_id` varchar(255) DEFAULT NULL,\n `device_insteon_id` varchar(45) DEFAULT NULL,\n `device_group` varchar(45) DEFAULT NULL,\n `status` varchar(45) DEFAULT NULL,\n `recieved_at` varchar(45) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1'

With this JSON

{"hub_insteon_id":"","device_group":"1","device_insteon_id":"3F68A2","recieved_at":"","status":"on"}

Getting This Error

ConvertJSONToSQL[id=d0dd4cc5-f2ab-43ab-8921-b2aafea03cb5] Failed to convert StandardFlowFileRecord[uuid=611848ee-f0e8-40a7-8119-0539d4b531dd,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1461081489294-74, container=default, section=74], offset=8383, length=127],offset=0,name=180088917788802,size=127] to a SQL INSERT statement due to org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the Home_Automation.Home_Events table; routing to failure: org.apache.nifi.processor.exception.ProcessException: None of the fields in the JSON map to the columns defined by the Home_Automation.Home_Events table

Any ideas how to resolve this?

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 02:11:41 GMT

Chris can you validate that your DBCPConnectionPool controller is pointing the the appropriate database instance? The JSONToSQL processor will attempt a "describe" using the Connection Service and often this error is the result of that Connection Service not being pointed to the desired database. OR if you using using a Phoenix table be careful as the Phoenix JDBC driver is case sensitive and can make things a little more tricky.

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 02:13:13 GMT

ignore the comment about Phoenix. I see you are using MySQL by the "ENGINE=InnoDB" now.

Re: Trouble with JsontoSQL processor in Nifi

cgambino — Mon, 19 Aug 2019 11:49:02 GMT

So the execute SQL processor successfully connects with the same settings.

returns

Objavro.schemaú{"type":"record","name":"Home_Events","namespace":"any.data","fields":[{"name":"hub_insteon_id","type":["null","string"]},{"name":"device_insteon_id","type":["null","string"]},{"name":"device_group","type":["null","string"]},{"name":"status","type":["null","string"]},{"name":"recieved_at","type":["null","string"]}]}

and the JsonToSQL processor is configured as

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 02:46:35 GMT

Your configuration looks valid to me. Can you post a screenshot showing your configuration for what is being written to the FlowFile contents and feed to the ConvertJSONToSQL processor? It also might help to validate that the JSON payload you expect is actually in the FlowFile's content by using a LogAttribute processor and setting the "Log Payload" Property to true right before going to the ConvertJSONToSQL processor.

Re: Trouble with JsontoSQL processor in Nifi

cgambino — Mon, 19 Aug 2019 11:48:48 GMT

The flow leading into that

and the AttributesToJson Configuration

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 02:59:39 GMT

Ok so the only way you should be seeing this is if JSON isn't in the format the ConvertJSONToSQL is expecting. The processor does a final Iterator<String> fieldNames = rootNode.getFieldNames(); and then performs a while loop on that Iterator incrementing a "fieldCount" variable each time. The only way you could see this is if the JSON isn't really what you think it is. I see the connection between "AttributesToJSON" and "ConvertJSONToSQL" has some FlowFiles in there. Can you right click that connection and list the contents and paste the exact contents of one of them here? Wondering if "AttributesToJSON" is doing something squirrely. I wrote it so its certainly possible ...

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 03:02:10 GMT

Ahh I think I see what the problem is I think it is because you have "Include Core Attributes" set to true in AttributesToJSON and some extra fields are getting introduced into the JSON not present in the database table. Please paste that content I mentioned earlier however so I can validate.

Re: Trouble with JsontoSQL processor in Nifi

cgambino — Wed, 20 Apr 2016 03:58:15 GMT

Didn't fix it

Re: Trouble with JsontoSQL processor in Nifi

cgambino — Wed, 20 Apr 2016 03:59:04 GMT

{"hub_insteon_id":"","device_group":"1","device_insteon_id":"368D4E","recieved_at":"2016-04-11T23:36:36.332240Z","status":"on"}

Re: Trouble with JsontoSQL processor in Nifi

jdyer — Wed, 20 Apr 2016 04:01:52 GMT

Can you please post the JSON coming off of AttributesToJSON? Changing "Include Core Attributes" alone will not solve your problem.

Re: Trouble with JsontoSQL processor in Nifi

cgambino — Wed, 20 Apr 2016 04:11:35 GMT

{"hub_insteon_id":"","device_group":"1","device_insteon_id":"368D4E","recieved_at":"2016-04-11T23:36:36.332240Z","status":"on"}

was taken after i changed it

Re: Trouble with JsontoSQL processor in Nifi

ed_gleeck — Sat, 23 Apr 2016 04:30:19 GMT

Make the following changes:

Change your connection string to include the database. For example: jdbc:mysql://localhost:3309/[databasename]
Take out the schema and the catalog name from the jsontosql processor configuration and just leave the table name. Your schema will come from the connection string

And you should be good to go.

Re: Trouble with JsontoSQL processor in Nifi

ed_gleeck — Sat, 23 Apr 2016 04:31:33 GMT

Oh and don't forget to put a PutSQL processor to actually execute the sql command that ConvertJSONToSQL processor does for you 🙂