CODEWITHSUNDEEP
apacheapache-nifidata-extraction
Shrads
Shrads

Reputation: 883

Extract the attribute name and its value from file and push into MySQL table

I want to extract the attribute name and its value and then further push it into the Database Tables.

The data is as follows:

   enter code here
{"Name":"Sam","Lastname":"Charles","Address":"1103 pioneer St"}
{"housename":"Jake","Lastname":"Stevenson","Address":"Abel St"}
{"foodname":"pudding","Lastname":"luther","Address":"Half Moon Bay"}

How Can i achieve this in NiFi so i can extract these values and push into MySQL? My MySQL table looks like the following:(the output I Expect is as follows):

**Names           Lastname                  Address**

Sam             Charles                    1103 pioneer St

Jake           Stevenson                   Abel St

Pudding         luther                    Half MoonBay

Any suggestion is appreciated. Thank you. What processor to use and what regex to use to achieve this?

Upvotes: 0

Views: 63

Answers (1)

mattyb
mattyb

Reputation: 12083

You can use PutDatabaseRecord for this, if the field names match the column names (which they appear to) then PutDatabaseRecord will generate the correct SQL to do an INSERT statement. If your input data is one JSON per line, you'll need at least NiFi 1.7.0 (for NIFI-4456). You can configure a JsonTreeReader with the following schema (to match your input data and target columns):

{
 "namespace": "nifi",
 "name": "myRecord",
 "type": "record",
 "fields": [
  {"name": "Name", "type": "string"},
  {"name": "Lastname", "type": "string"},
  {"name": "Address", "type": "string"}
 ]
}

If there can be null values in the data, then replace "type": "string" with "type": ["null", "string"]

Upvotes: 2

Related Questions