Support Questions

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

Trouble converting JSON to AVRO in Nifi

avatar
Explorer

I'm trying to ingest JSON data and store it in AVRO format for consumption downstream. I'm using the ConvertJSONToAvro processor (1.3). The data is coming out of the processor as successful, but there is no data.

Here is the incoming JSON

{
  "type" : "record",
  "name" : "vendor",
  "fields" : [ {
    "businessentityid" : 1518,
    "accountnumber" : "INTERNAT0004",
    "name" : "International Trek Center",
    "creditrating" : 1
  }, {
    "businessentityid" : 1520,
    "accountnumber" : "G&KBI0001",
    "name" : "G & K Bicycle Corp.",
    "creditrating" : 1
  }, {
    "businessentityid" : 1546,
    "accountnumber" : "GREENLA0001",
    "name" : "Green Lake Bike Company",
    "creditrating" : 1
  }, {
    "businessentityid" : 1574,
    "accountnumber" : "JEFFSSP0001",
    "name" : "Jeff's Sporting Goods",
    "creditrating" : 1
  }, {
    "businessentityid" : 1594,
    "accountnumber" : "FITNESS0001",
    "name" : "Fitness Association",
    "creditrating" : 1
  }, {
    "businessentityid" : 1636,
    "accountnumber" : "INTEGRAT0001",
    "name" : "Integrated Sport Products",
    "creditrating" : 1
  }, {
    "businessentityid" : 1676,
    "accountnumber" : "TEAMATH0001",
    "name" : "Team Athletic Co.",
    "creditrating" : 1
  } ]
}

Here is the AVRO schema

{
		"type":"${schema.type}",
		"name":"${schema.name}",
		"fields":
		[
			{
				"name":"businessentityid",
				"type":["int","null"]
			},
			{
				"name":"accountnumber",
				"type":["string","null"]
			},
			{
				"name":"name",
				"type":["string","null"]
			},
			{
				"name":"creditrating",
				"type":["int","null"]
			}
		]
}	

Here is the JSON output, after converting it back to JSON.

{
  "businessentityid" : null,
  "accountnumber" : null,
  "name" : "vendor",
  "creditrating" : null
}

Any assistance is greatly appreciated. BTW, trying to turn up the logging to anything other than INFO in Nifi has not yielded any different logging behavior.

Thanks,

M

1 ACCEPTED SOLUTION

avatar
Explorer

Thanks for your help. Finally figured out the Nifi JSONToAvro processor can only accept 1 row at a time, versus an array of rows. The following worked for me: <Json array containing multiple rows of data> --> SplitJson --> ConvertJSONToAvro --> MergeContent ...

Thanks again for your help.

View solution in original post

7 REPLIES 7

avatar
Master Guru

The incoming JSON you showed does not match your schema...

Your schema above shows that there is a single record with the fields "businessentityid", "accountnumber", "name", "creditrating".

Your incoming JSON shows a field named "fields" with an array of JSON documents where each JSON document then has the four fields.

If you want to make a schema that matches that JSON, your schema needs to declare a field called "fields" of type array, where each entry of the array is a type record with the four fields.

avatar
Explorer

So the schema should look something like? ...

{
		"type":"record",
		"name":"vendor",
		"fields":
		[
			"name":"children",
			"type":
			{
				"type":"array",
				"items":
				{
					"name":"Child",
					"type":"record",
					"fields":
					[
						{
							"name":"businessentityid",
							"type":["int","null"]
						},
						{
							"name":"accountnumber",
							"type":["string","null"]
						},
						{
							"name":"name",
							"type":["string","null"]
						},
						{
							"name":"creditrating",
							"type":["int","null"]
						}
					]
				}
			}
		]
}	

avatar
Master Guru

That looks close... where you have "name" : "children" it seems like that should be "name" : "fields" since "fields" is the array element in your JSON.

Your example JSON also has top-level fields for "type" and "name" so I would think you want to declare those in the schema, unless you just want to skip over them.

avatar
Explorer

Hmm, the following schema doesn't validate. Any ideas?

{
		"type":"record",
		"name":"vendors",
		"fields":
		[
			"name":"fields",
			"type":
			{
				"type":"array",
				"items":
				{
					"name":"vendor",
					"type":"record",
					"fields":
					[
						{
							"name":"businessentityid",
							"type":["int","null"]
						},
						{
							"name":"accountnumber",
							"type":["string","null"]
						},
						{
							"name":"name",
							"type":["string","null"]
						},
						{
							"name":"creditrating",
							"type":["int","null"]
						}
					]
				}
			}
		]
}

avatar
Explorer

Thanks for your help. Finally figured out the Nifi JSONToAvro processor can only accept 1 row at a time, versus an array of rows. The following worked for me: <Json array containing multiple rows of data> --> SplitJson --> ConvertJSONToAvro --> MergeContent ...

Thanks again for your help.

avatar
New Contributor

Can u please help me...while converting json to avro format,in avro data logical type is not coming

...need to add anything in json

avatar
Community Manager

@Avrotojson, as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: