JSON message to Hive (preferably by spark/scala using dataframe)
I have a single JSON file(sample_json789.json) which comprises of header and data information. I need to store this JSON message in Hive table (Preferably using Spark/Scala). Since it is a nested JSON message, the problem arises when we want to store a part of JSON message in a column.
JSON Message :
{
"Header": {
"Field1": "field1",
"Field2": "field2",
"Field3": "field3",
"Field4": "field4",
}
}
{
"Data": {
"dataField": "dataField1",
"dataDescription": "This is data 1",
"codes": {
"code": "XYZ123",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC123",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF123",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
{
"Data": {
"codeCategory": "dataField1",
"fieldName": "This is data 1",
"codes": {
"code": "XYZ456",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC456",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF456",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
The first part of the JSON message comprises of the header information which needs to be stored in a table: HeaderTable
HeaderTable
Field1 | Field2 | Field3 | Field4
----------------------------------
field1 | field2 | field3 | field4
The other part(Data) of the JSON message comprises of multiple data information which needs to be stored in the table: DataTable. However, the issue in this table is, a part of the JSON message needs to be stored in the column as well.
DataTable(EXPECTED OUTPUT)
jsonFileName | code | jsonMsg
-------------------------------------------------------------------------
sample_json789.json | XYZ123 | { "Data": { "dataField": "dataField1",....
sample_json789.json | XYZ456 | { "Data": { "dataField": "dataField2",....
I am facing difficulty in storing "DataTable" in the Hive because of jsonMsg. Above is the sample message but the actual message is quite large.
Below are the steps(Spark/scala) :
1. val jsonFile = sc.textFile(<json file path>)
2. val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
3. val jsonDF = hiveContext.read.json(jsonFile)
4. jsonDF.saveAsTable("testTable")
Error: jsonMessage field exceeds 2000 limit (It's more than 3000)
Also, the jsonMsg field displays in WrappedArray.
How to store jsonMessage in hive table(preferable by Spark/scala)?
json scala apache-spark hive
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
add a comment |
I have a single JSON file(sample_json789.json) which comprises of header and data information. I need to store this JSON message in Hive table (Preferably using Spark/Scala). Since it is a nested JSON message, the problem arises when we want to store a part of JSON message in a column.
JSON Message :
{
"Header": {
"Field1": "field1",
"Field2": "field2",
"Field3": "field3",
"Field4": "field4",
}
}
{
"Data": {
"dataField": "dataField1",
"dataDescription": "This is data 1",
"codes": {
"code": "XYZ123",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC123",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF123",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
{
"Data": {
"codeCategory": "dataField1",
"fieldName": "This is data 1",
"codes": {
"code": "XYZ456",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC456",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF456",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
The first part of the JSON message comprises of the header information which needs to be stored in a table: HeaderTable
HeaderTable
Field1 | Field2 | Field3 | Field4
----------------------------------
field1 | field2 | field3 | field4
The other part(Data) of the JSON message comprises of multiple data information which needs to be stored in the table: DataTable. However, the issue in this table is, a part of the JSON message needs to be stored in the column as well.
DataTable(EXPECTED OUTPUT)
jsonFileName | code | jsonMsg
-------------------------------------------------------------------------
sample_json789.json | XYZ123 | { "Data": { "dataField": "dataField1",....
sample_json789.json | XYZ456 | { "Data": { "dataField": "dataField2",....
I am facing difficulty in storing "DataTable" in the Hive because of jsonMsg. Above is the sample message but the actual message is quite large.
Below are the steps(Spark/scala) :
1. val jsonFile = sc.textFile(<json file path>)
2. val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
3. val jsonDF = hiveContext.read.json(jsonFile)
4. jsonDF.saveAsTable("testTable")
Error: jsonMessage field exceeds 2000 limit (It's more than 3000)
Also, the jsonMsg field displays in WrappedArray.
How to store jsonMessage in hive table(preferable by Spark/scala)?
json scala apache-spark hive
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41
add a comment |
I have a single JSON file(sample_json789.json) which comprises of header and data information. I need to store this JSON message in Hive table (Preferably using Spark/Scala). Since it is a nested JSON message, the problem arises when we want to store a part of JSON message in a column.
JSON Message :
{
"Header": {
"Field1": "field1",
"Field2": "field2",
"Field3": "field3",
"Field4": "field4",
}
}
{
"Data": {
"dataField": "dataField1",
"dataDescription": "This is data 1",
"codes": {
"code": "XYZ123",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC123",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF123",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
{
"Data": {
"codeCategory": "dataField1",
"fieldName": "This is data 1",
"codes": {
"code": "XYZ456",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC456",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF456",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
The first part of the JSON message comprises of the header information which needs to be stored in a table: HeaderTable
HeaderTable
Field1 | Field2 | Field3 | Field4
----------------------------------
field1 | field2 | field3 | field4
The other part(Data) of the JSON message comprises of multiple data information which needs to be stored in the table: DataTable. However, the issue in this table is, a part of the JSON message needs to be stored in the column as well.
DataTable(EXPECTED OUTPUT)
jsonFileName | code | jsonMsg
-------------------------------------------------------------------------
sample_json789.json | XYZ123 | { "Data": { "dataField": "dataField1",....
sample_json789.json | XYZ456 | { "Data": { "dataField": "dataField2",....
I am facing difficulty in storing "DataTable" in the Hive because of jsonMsg. Above is the sample message but the actual message is quite large.
Below are the steps(Spark/scala) :
1. val jsonFile = sc.textFile(<json file path>)
2. val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
3. val jsonDF = hiveContext.read.json(jsonFile)
4. jsonDF.saveAsTable("testTable")
Error: jsonMessage field exceeds 2000 limit (It's more than 3000)
Also, the jsonMsg field displays in WrappedArray.
How to store jsonMessage in hive table(preferable by Spark/scala)?
json scala apache-spark hive
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
I have a single JSON file(sample_json789.json) which comprises of header and data information. I need to store this JSON message in Hive table (Preferably using Spark/Scala). Since it is a nested JSON message, the problem arises when we want to store a part of JSON message in a column.
JSON Message :
{
"Header": {
"Field1": "field1",
"Field2": "field2",
"Field3": "field3",
"Field4": "field4",
}
}
{
"Data": {
"dataField": "dataField1",
"dataDescription": "This is data 1",
"codes": {
"code": "XYZ123",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC123",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF123",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
{
"Data": {
"codeCategory": "dataField1",
"fieldName": "This is data 1",
"codes": {
"code": "XYZ456",
"codeDescription": "LAST 3 OF 26"
},
"otherCodes":[{
"key":"ABC456",
"KeyDescription":"descriptionofABC"
},
{
"key":"DEF456",
"keyDescription":"DescriptionforDEF"
}
]"
}
}
The first part of the JSON message comprises of the header information which needs to be stored in a table: HeaderTable
HeaderTable
Field1 | Field2 | Field3 | Field4
----------------------------------
field1 | field2 | field3 | field4
The other part(Data) of the JSON message comprises of multiple data information which needs to be stored in the table: DataTable. However, the issue in this table is, a part of the JSON message needs to be stored in the column as well.
DataTable(EXPECTED OUTPUT)
jsonFileName | code | jsonMsg
-------------------------------------------------------------------------
sample_json789.json | XYZ123 | { "Data": { "dataField": "dataField1",....
sample_json789.json | XYZ456 | { "Data": { "dataField": "dataField2",....
I am facing difficulty in storing "DataTable" in the Hive because of jsonMsg. Above is the sample message but the actual message is quite large.
Below are the steps(Spark/scala) :
1. val jsonFile = sc.textFile(<json file path>)
2. val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
3. val jsonDF = hiveContext.read.json(jsonFile)
4. jsonDF.saveAsTable("testTable")
Error: jsonMessage field exceeds 2000 limit (It's more than 3000)
Also, the jsonMsg field displays in WrappedArray.
How to store jsonMessage in hive table(preferable by Spark/scala)?
json scala apache-spark hive
json scala apache-spark hive
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
asked Nov 21 '18 at 7:04
AnirudhAnirudh
142
142
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41
add a comment |
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41
add a comment |
0
active
oldest
votes
Your Answer
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%2f53406848%2fjson-message-to-hive-preferably-by-spark-scala-using-dataframe%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53406848%2fjson-message-to-hive-preferably-by-spark-scala-using-dataframe%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
"The first part of the JSON message" and "The other part(Data)" are two separate questions, aren't they? Could you split the question into two questions at StackOverflow (if you haven't found their solutions)? That would help a lot. Thanks.
– Jacek Laskowski
Nov 21 '18 at 10:41