Mixing
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?
apache apache-nifi data-extraction
asked Jan 2 at 21:32
shradsshrads
1358
add a comment |
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?
apache apache-nifi data-extraction
asked Jan 2 at 21:32
shradsshrads
1358
add a comment |
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?
apache apache-nifi data-extraction
asked Jan 2 at 21:32
shradsshrads
1358
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?
apache apache-nifi data-extraction
apache apache-nifi data-extraction
asked Jan 2 at 21:32
shradsshrads
1358
asked Jan 2 at 21:32
shradsshrads
1358
edited Jan 2 at 23:24
shrads
asked Jan 2 at 21:32
shradsshrads
1358
asked Jan 2 at 21:32
shradsshrads
1358
asked Jan 2 at 21:32
shradsshrads
1358
1358
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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"]
answered Jan 2 at 21:56
mattybmattyb
7,7861018
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013435%2fextract-the-attribute-name-and-its-value-from-file-and-push-into-mysql-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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"]
answered Jan 2 at 21:56
mattybmattyb
7,7861018
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
add a comment |
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"]
answered Jan 2 at 21:56
mattybmattyb
7,7861018
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
add a comment |
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"]
answered Jan 2 at 21:56
mattybmattyb
7,7861018
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"]
answered Jan 2 at 21:56
mattybmattyb
7,7861018
answered Jan 2 at 21:56
mattybmattyb
7,7861018
answered Jan 2 at 21:56
mattybmattyb
7,7861018
answered Jan 2 at 21:56
mattybmattyb
7,7861018
7,7861018
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
add a comment |
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
Thank You for replying. I have updated my question. Actually the first field in the data is not the same i.e. the first field in each record changes. Please see the updated question. Can you tell what changes required now??
– shrads
Jan 2 at 23:24
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
You should be able to use UpdateRecord to change the name of the field from Name to Names, then send the updated JSON into PutDatabaseRecord
– mattyb
Jan 3 at 21:42
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013435%2fextract-the-attribute-name-and-its-value-from-file-and-push-into-mysql-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
