Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

New Contributor

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 REPLIES 2

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

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

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

New Contributor

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.