NiFi: Converting a Nested JSON File into CSV

Question

I'm new to Nifi and in my case have to consume a JSON topic from Kafka and would like to convert it into a CSV file where I need to select only few scalar and some nested fields.

I need to do the following things:

 Consume topic - Done
 Json to CSV
 Include header in the CSV file
 Merge into single file (if its split)
 Give a proper filename with date

Following the below link: https://community.hortonworks.com/articles/64069/converting-a-large-json-file-into-csv.html

But not sure if this is the right approach and also don't know how to make a single file. Im using NiFi (1.8) and schema is stored in Confluent Schema Registry

Json Sample:

{
  "type" : "record",
  "name" : "Customer",
  "namespace" : "namespace1"
  "fields" : [ {
    "name" : "header",
    "type" : {
      "type" : "record",
      "name" : "CustomerDetails",
      "namespace" : "namespace1"
      "fields" : [ {
        "name" : "Id",
        "type" : "string"
      }, {
        "name" : "name",
        "type" : "string"
      }, {
        "name" : "age",
        "type" : [ "null", "int" ],
        "default" : null
      }, {
        "name" : "comm",
        "type" : [ "null", "int" ],
        "default" : null
      } ]
    },
    "doc" : ""
  }, {
    "name" : "data",
    "type" : {
      "type" : "record",
      "name" : "CustomerData"
      "fields" : [ {
        "name" : "tags",
        "type" : {
          "type" : "map",
          "values" : "string"
        }
      }, {
        "name" : "data",
        "type" : [ "null", "bytes" ]
        "default" : null
      } ]
    },
    "doc" : ""
  } ]
}

Please guide me.

What does the JSON look like, and how do you intend on representing the nested field(s) as a CSV value? — mattyb
I have updated sample json. Nested fields should go into as a single record in the csv file. — Leibnitz

mattyb mattyb · Accepted Answer · 2019-05-07T13:34:08

Try ConvertRecord with a JsonTreeReader and CSVRecordSetWriter. The writer can be configured to include the header line, and you won't need to split/merge. UpdateAttribute can be used to set the filename attribute which is the associated filename for the flow file (used by PutFile, e.g.).

If ConvertRecord doesn't give you the output you want, can you please elaborate on the difference between what it gives you and what you expect?

NiFi: Converting a Nested JSON File into CSV

1 Answers