Nifi 1.7, How to insert json data to MySQL server use PutSQL or PutDatabaseRecord ??

[#1]

hi guys,

I have json as:

{
"type" : "insert",
"timestamp" : 1536959101000,
"binlog_filename" : "mysql-bin-changelog.234234",
"binlog_position" : 223,
"database" : "test",
"table_name" : "demo",
"table_id" : 138,
"columns" : [ {
"id" : 1,
"name" : "id",
"column_type" : 12,
"value" : "IboECKV "
}, {
"id" : 2,
"name" : "col2",
"column_type" : 93,
"value" : "Fri Sep 14 21:05:02 UTC 2018"
}, {
"id" : 3,
"name" : "col3",
"column_type" : 4,
"value" : 10
}, {
"id" : 4,
"name" : "col4",
"column_type" : 4,
"value" : 0
}]
}

How to insert json data to MySQL server use PutSQL or PutDatabaseRecord ??

Many Thanks

[#2]

You can use JoltTransformJSON to convert this JSON into just the "name: value" pairs used by PutDatabaseRecord, here is a spec that will do it:

[{
  "operation": "shift",
  "spec": {
    "columns": {
      "*": {
        "@(value)": "[#1].@(1,name)"
      }
    }
  }
}]

If you find that PutDatabaseRecord is slow, it's likely because you're putting one record at a time into the DB. Instead consider using MergeRecord to bundle together more CDC events, then you can use the following spec to transform all of them for use in PutDatabaseRecord as a "micro-batch":

[{
  "operation": "shift",
  "spec": {
    "*": {
      "columns": {
        "*": {
          "@(value)": "[#4].@(1,name)"
        }
      }
    }
  }
}]

[#3]

Hi I'm using PutDatabaseRecord in this case. But I mentioned properties manually in schema registry. Is there any way that JsonPathReader reads the flowfile as schema dynamically.