Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Trouble with JsontoSQL processor in Nifi

avatar
Expert Contributor

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?

1 ACCEPTED SOLUTION

avatar
Rising Star

Make the following changes:

  1. Change your connection string to include the database. For example: jdbc:mysql://localhost:3309/[databasename]
  2. 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.

View solution in original post

13 REPLIES 13

avatar
Guru

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

avatar
Expert Contributor

{"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

avatar
Rising Star

Make the following changes:

  1. Change your connection string to include the database. For example: jdbc:mysql://localhost:3309/[databasename]
  2. 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.

avatar
Rising Star

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