question Re: Trouble converting JSON to AVRO in Nifi in Archives of Support Questions (Read Only)

Trouble converting JSON to AVRO in Nifi

mvogt99 — Mon, 31 Jul 2017 22:04:13 GMT

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,

Re: Trouble converting JSON to AVRO in Nifi

bbende — Mon, 31 Jul 2017 23:04:14 GMT

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.

Re: Trouble converting JSON to AVRO in Nifi

mvogt99 — Tue, 01 Aug 2017 00:50:31 GMT

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"]
						}
					]
				}
			}
		]
}

Re: Trouble converting JSON to AVRO in Nifi

bbende — Tue, 01 Aug 2017 00:54:53 GMT

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.

Re: Trouble converting JSON to AVRO in Nifi

mvogt99 — Tue, 01 Aug 2017 01:34:42 GMT

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"]
						}
					]
				}
			}
		]
}

Re: Trouble converting JSON to AVRO in Nifi

mvogt99 — Tue, 01 Aug 2017 03:41:52 GMT

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.

Re: Trouble converting JSON to AVRO in Nifi

Avrotojson — Thu, 08 Dec 2022 06:27:45 GMT

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

Re: Trouble converting JSON to AVRO in Nifi

VidyaSargur — Fri, 09 Dec 2022 06:55:01 GMT

@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.