Support Questions
Find answers, ask questions, and share your expertise

Nifi: PutSQL or PutDataBaseRecord with multi-record complex JSON

Nifi: PutSQL or PutDataBaseRecord with multi-record complex JSON

Explorer

I have a data flow that is current working very well but looking at improving its performance. We use IBM Informix as a database. Note I'm not a developer by trade and have only been using Nifi for a week - also learning how JSON, Avro and schemas work, so bare with the newbiness.

 

The problem I have is switching from PutSQL to PutDataBaseRecord for these reasons:

  • The JSON is missing some records that are required in the database table (two fields equal to 0 and one date)
  • The timestamp we receive is in seconds instead of milliseconds 
  • The JSON headers are with an uppercase first letter, the database needs them all in lowercase.

With this is it worth pursuing PutDataBaseRecord? I'm experimenting with ReplaceText but not sure if I'm just going down a rabbit hole for no good reason.

 

This is my current Avro Schema:

 

{
  "name": "MyClass",
  "type": "record",
  "namespace": "com.acme.avro",
  "fields": [
    {
      "name": "readings",
      "type": {
        "type": "array",
        "items": {
          "name": "readings_record",
          "type": "record",
          "fields": [
            {
              "name": "meter",
              "type": "string"
            },
            {
              "name": "records",
              "type": {
                "type": "array",
                "items": {
                  "name": "records_record",
                  "type": "record",
                  "fields": [
                    {
                      "name": "timestamp",
                      "type": "string"
                    },
                    {
                      "name": "values",
                      "type": {
                        "name": "values",
                        "type": "record",
                        "fields": [
                          {
                            "name": "DateTime",
                            "type": "int"
                          },
                          {
                            "name": "ExportWh",
                            "type": "float"
                          },
                          {
                            "name": "Ia",
                            "type": "float"
                          },
                          {
                            "name": "Ib",
                            "type": "float"
                          },
                          {
                            "name": "Ic",
                            "type": "float"
                          },
                          {
                            "name": "ImportWh",
                            "type": "float"
                          },
                          {
                            "name": "Pa",
                            "type": "int"
                          },
                          {
                            "name": "Pb",
                            "type": "int"
                          },
                          {
                            "name": "Pc",
                            "type": "int"
                          },
                          {
                            "name": "Pfa",
                            "type": "float"
                          },
                          {
                            "name": "Pfb",
                            "type": "float"
                          },
                          {
                            "name": "Pfc",
                            "type": "float"
                          },
                          {
                            "name": "Pftot",
                            "type": "float"
                          },
                          {
                            "name": "Ptot",
                            "type": "int"
                          },
                          {
                            "name": "Q1varh",
                            "type": "float"
                          },
                          {
                            "name": "Q2varh",
                            "type": "float"
                          },
                          {
                            "name": "Q3varh",
                            "type": "float"
                          },
                          {
                            "name": "Q4varh",
                            "type": "float"
                          },
                          {
                            "name": "Qa",
                            "type": "int"
                          },
                          {
                            "name": "Qb",
                            "type": "int"
                          },
                          {
                            "name": "Qc",
                            "type": "int"
                          },
                          {
                            "name": "Qtot",
                            "type": "int"
                          },
                          {
                            "name": "Sa",
                            "type": "int"
                          },
                          {
                            "name": "Sb",
                            "type": "int"
                          },
                          {
                            "name": "Sc",
                            "type": "int"
                          },
                          {
                            "name": "SerialNumber",
                            "type": "string"
                          },
                          {
                            "name": "Stot",
                            "type": "int"
                          },
                          {
                            "name": "VAh",
                            "type": "float"
                          },
                          {
                            "name": "Va",
                            "type": "float"
                          },
                          {
                            "name": "Vb",
                            "type": "float"
                          },
                          {
                            "name": "Vc",
                            "type": "float"
                          },
                          {
                            "name": "epoch_timestamp",
                            "type": "int"
                          },
                          {
                            "name": "uniqueID",
                            "type": "int"
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            }
          ]
        }
      }
    }
  ]
}

 

 

The workflow is currently as follows:

 

HandleHTTPRequest

ValidateRecord

ValidateRecordValidateRecord

 

HandleHTTPResponse

 

SplitJson

SplitJson1SplitJson1

SplitJson

SplitJson2SplitJson2

SplitJson

SplitJson3SplitJson3

EvaluateJsonPath

EvaluateJsonPathEvaluateJsonPath

UpdateAttribute

UpdateAttributeUpdateAttribute

AttributedToJSON

AttributesToJSONAttributesToJSON

ConvertJSONToSQL

ConvertJSONtoSQLConvertJSONtoSQL

PutSQL

PutSQLPutSQL

My Controller services are as follows:

Avro:

AvroAvro

 

JSON Tree Reader

JSON Tree ReaderJSON Tree Reader

 

JSON Set Writer

JSON Record Set WriterJSON Record Set Writer