perform Regex on JSON data across each row and push into the MySQL table using NiFi
I want to achieve the following use case:
I have a file as follows:
enter code here
{"FirstName":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{"HouseName":67783,"Lastname":"Stevenson","Address":"Abel St"}
{"FoodName":7473,"Lastname":"luther","Address":"Half Moon Bay"}
si I want to add "NAME" and "Value" tag in the first column across each row so I can easily push all the FirstName, HouseName, and FoodName attributes under one column in MySQL named 'Name' and its respective values under "Value" column in MySQL. for example I want the data to look like as following:
{NAME:"FirstName","Value":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{NAME:"HouseName","Value":67783,"Lastname":"Stevenson","Address":"Abel St"}
{NAME:"FoodName","Value":7473,"Lastname":"luther","Address":"Half Moon Bay"}
My table in MySQL is as follows:
Name Value Lastname Address
I am using the following flow:
GetFile->SplitRecord->ConvertJsonToSQL ->PutSQL
I want under the NAME column all the first column attribute names of each row(FirstName, HouseName, FoodName) and under Value column its respective values to be entered.
How can i achieve this use case in NiFi?
What Regex I should use in the ReplaceText to achieve this. Anny help is appreciated. Thank You!
mysql regex apache apache-nifi
asked Jan 3 at 17:03
shradsshrads
1358
add a comment |
I want to achieve the following use case:
I have a file as follows:
enter code here
{"FirstName":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{"HouseName":67783,"Lastname":"Stevenson","Address":"Abel St"}
{"FoodName":7473,"Lastname":"luther","Address":"Half Moon Bay"}
si I want to add "NAME" and "Value" tag in the first column across each row so I can easily push all the FirstName, HouseName, and FoodName attributes under one column in MySQL named 'Name' and its respective values under "Value" column in MySQL. for example I want the data to look like as following:
{NAME:"FirstName","Value":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{NAME:"HouseName","Value":67783,"Lastname":"Stevenson","Address":"Abel St"}
{NAME:"FoodName","Value":7473,"Lastname":"luther","Address":"Half Moon Bay"}
My table in MySQL is as follows:
Name Value Lastname Address
I am using the following flow:
GetFile->SplitRecord->ConvertJsonToSQL ->PutSQL
I want under the NAME column all the first column attribute names of each row(FirstName, HouseName, FoodName) and under Value column its respective values to be entered.
How can i achieve this use case in NiFi?
What Regex I should use in the ReplaceText to achieve this. Anny help is appreciated. Thank You!
mysql regex apache apache-nifi
asked Jan 3 at 17:03
shradsshrads
1358
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20
add a comment |
I want to achieve the following use case:
I have a file as follows:
enter code here
{"FirstName":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{"HouseName":67783,"Lastname":"Stevenson","Address":"Abel St"}
{"FoodName":7473,"Lastname":"luther","Address":"Half Moon Bay"}
si I want to add "NAME" and "Value" tag in the first column across each row so I can easily push all the FirstName, HouseName, and FoodName attributes under one column in MySQL named 'Name' and its respective values under "Value" column in MySQL. for example I want the data to look like as following:
{NAME:"FirstName","Value":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{NAME:"HouseName","Value":67783,"Lastname":"Stevenson","Address":"Abel St"}
{NAME:"FoodName","Value":7473,"Lastname":"luther","Address":"Half Moon Bay"}
My table in MySQL is as follows:
Name Value Lastname Address
I am using the following flow:
GetFile->SplitRecord->ConvertJsonToSQL ->PutSQL
I want under the NAME column all the first column attribute names of each row(FirstName, HouseName, FoodName) and under Value column its respective values to be entered.
How can i achieve this use case in NiFi?
What Regex I should use in the ReplaceText to achieve this. Anny help is appreciated. Thank You!
mysql regex apache apache-nifi
asked Jan 3 at 17:03
shradsshrads
1358
I want to achieve the following use case:
I have a file as follows:
enter code here
{"FirstName":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{"HouseName":67783,"Lastname":"Stevenson","Address":"Abel St"}
{"FoodName":7473,"Lastname":"luther","Address":"Half Moon Bay"}
si I want to add "NAME" and "Value" tag in the first column across each row so I can easily push all the FirstName, HouseName, and FoodName attributes under one column in MySQL named 'Name' and its respective values under "Value" column in MySQL. for example I want the data to look like as following:
{NAME:"FirstName","Value":6785,"Lastname":"Charles","Address":"1103 pioneer St"}
{NAME:"HouseName","Value":67783,"Lastname":"Stevenson","Address":"Abel St"}
{NAME:"FoodName","Value":7473,"Lastname":"luther","Address":"Half Moon Bay"}
My table in MySQL is as follows:
Name Value Lastname Address
I am using the following flow:
GetFile->SplitRecord->ConvertJsonToSQL ->PutSQL
I want under the NAME column all the first column attribute names of each row(FirstName, HouseName, FoodName) and under Value column its respective values to be entered.
How can i achieve this use case in NiFi?
What Regex I should use in the ReplaceText to achieve this. Anny help is appreciated. Thank You!
mysql regex apache apache-nifi
mysql regex apache apache-nifi
asked Jan 3 at 17:03
shradsshrads
1358
asked Jan 3 at 17:03
shradsshrads
1358
edited Jan 3 at 18:26
shrads
asked Jan 3 at 17:03
shradsshrads
1358
asked Jan 3 at 17:03
shradsshrads
1358
asked Jan 3 at 17:03
shradsshrads
1358
1358
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20
add a comment |
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20
add a comment |
1 Answer
1
active
oldest
votes
You can use GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:
[
{
"operation": "shift",
"spec": {
"*": {
"Address": "[#2].Address",
"Lastname": "[#2].Lastname",
"*Name": {
"$": "[#3].Name",
"@": "[#3].Value"
}
}
}
}
]
Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.
answered Jan 3 at 21:53
mattybmattyb
7,7861018
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%2f54026693%2fperform-regex-on-json-data-across-each-row-and-push-into-the-mysql-table-using-n%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 GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:
[
{
"operation": "shift",
"spec": {
"*": {
"Address": "[#2].Address",
"Lastname": "[#2].Lastname",
"*Name": {
"$": "[#3].Name",
"@": "[#3].Value"
}
}
}
}
]
Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.
answered Jan 3 at 21:53
mattybmattyb
7,7861018
add a comment |
You can use GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:
[
{
"operation": "shift",
"spec": {
"*": {
"Address": "[#2].Address",
"Lastname": "[#2].Lastname",
"*Name": {
"$": "[#3].Name",
"@": "[#3].Value"
}
}
}
}
]
Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.
answered Jan 3 at 21:53
mattybmattyb
7,7861018
add a comment |
You can use GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:
[
{
"operation": "shift",
"spec": {
"*": {
"Address": "[#2].Address",
"Lastname": "[#2].Lastname",
"*Name": {
"$": "[#3].Name",
"@": "[#3].Value"
}
}
}
}
]
Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.
answered Jan 3 at 21:53
mattybmattyb
7,7861018
You can use GetFile -> JoltTransformJson (or JoltTransformRecord) -> PutDatabaseRecord for this, which avoids the split and separate SQL generation/execution. Use the following spec in JoltTransformRecord:
[
{
"operation": "shift",
"spec": {
"*": {
"Address": "[#2].Address",
"Lastname": "[#2].Lastname",
"*Name": {
"$": "[#3].Name",
"@": "[#3].Value"
}
}
}
}
]
Notice I changed the output NAME column to Name to match your MySQL column name, it makes things easier on PutDatabaseRecord when the columns match the field names.
answered Jan 3 at 21:53
mattybmattyb
7,7861018
answered Jan 3 at 21:53
mattybmattyb
7,7861018
answered Jan 3 at 21:53
mattybmattyb
7,7861018
answered Jan 3 at 21:53
mattybmattyb
7,7861018
7,7861018
add a comment |
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%2f54026693%2fperform-regex-on-json-data-across-each-row-and-push-into-the-mysql-table-using-n%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
What have you tried? What didn't work? What did you get? What did you expect? What doesn't work with your code and where is it?
– Toto
Jan 3 at 17:05
@Toto I don't know which processor to use to achieve this. I am a newbie! I think ReplaceText would help but then I don't know what Regex to use to achieve my use case. Any Help is much appreciated?
– shrads
Jan 3 at 17:12
Sounds like a few lines of PHP code. (I never heard of Nifi.)
– Rick James
Jan 3 at 18:20