Apache Drill - Getting error on KVGEN method
I have a huge dataset where each record have json data similar to below -
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231,"districts":{"id":41,"name":"abc","region":"123"}}}
When I am trying to genrate key value pairs using select kvgen(t.project) from dfs.filePath t in apache drill, I am getting below error -
DrillRuntimeException: Mappify/kvgen does not support heterogeneous value types. All values in the input map must be of the same type. The field [createDate] has a differing type [minor_type: BIGINT mode: OPTIONAL ]
It looks like drill expects all values to be of same type. But how to do that? Is there any function available in drill?
My drill version is 1.9.0
apache apache-drill mapr
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
add a comment |
I have a huge dataset where each record have json data similar to below -
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231,"districts":{"id":41,"name":"abc","region":"123"}}}
When I am trying to genrate key value pairs using select kvgen(t.project) from dfs.filePath t in apache drill, I am getting below error -
DrillRuntimeException: Mappify/kvgen does not support heterogeneous value types. All values in the input map must be of the same type. The field [createDate] has a differing type [minor_type: BIGINT mode: OPTIONAL ]
It looks like drill expects all values to be of same type. But how to do that? Is there any function available in drill?
My drill version is 1.9.0
apache apache-drill mapr
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
add a comment |
I have a huge dataset where each record have json data similar to below -
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231,"districts":{"id":41,"name":"abc","region":"123"}}}
When I am trying to genrate key value pairs using select kvgen(t.project) from dfs.filePath t in apache drill, I am getting below error -
DrillRuntimeException: Mappify/kvgen does not support heterogeneous value types. All values in the input map must be of the same type. The field [createDate] has a differing type [minor_type: BIGINT mode: OPTIONAL ]
It looks like drill expects all values to be of same type. But how to do that? Is there any function available in drill?
My drill version is 1.9.0
apache apache-drill mapr
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
I have a huge dataset where each record have json data similar to below -
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231,"districts":{"id":41,"name":"abc","region":"123"}}}
When I am trying to genrate key value pairs using select kvgen(t.project) from dfs.filePath t in apache drill, I am getting below error -
DrillRuntimeException: Mappify/kvgen does not support heterogeneous value types. All values in the input map must be of the same type. The field [createDate] has a differing type [minor_type: BIGINT mode: OPTIONAL ]
It looks like drill expects all values to be of same type. But how to do that? Is there any function available in drill?
My drill version is 1.9.0
apache apache-drill mapr
apache apache-drill mapr
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
edited Nov 23 '18 at 6:40
Lokesh Jain
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
asked Nov 22 '18 at 10:48
Lokesh JainLokesh Jain
537
537
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Try setting session option store.json.all_text_mode to true.
https://drill.apache.org/docs/json-data-model/
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
add a comment |
I figured it out. KVGEN method doesn't work if json is nested.
To make it work, there are two approaches which can be followed -
- Take out the nested json outside
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231},"districts":{"id":41,"name":"abc","region":"123"}}
and then apply KVGEN method as select kvgen(t.project) from dfs.filePath t
Apply kvgen method on inner json first and then use nested query as below
select tbl2.col1.id, tbl2.col2.value from (select tbl1.project as col1, flatten(kvgen(tbl1.project.districts)) col2 from dfs.filePath tbl1) tbl2
And as rightly mentioned by @arina-yelchiyeva, session option store.json.all_text_mode needs to be set to true.
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
add a comment |
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%2f53429243%2fapache-drill-getting-error-on-kvgen-method%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try setting session option store.json.all_text_mode to true.
https://drill.apache.org/docs/json-data-model/
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
add a comment |
Try setting session option store.json.all_text_mode to true.
https://drill.apache.org/docs/json-data-model/
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
add a comment |
Try setting session option store.json.all_text_mode to true.
https://drill.apache.org/docs/json-data-model/
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
Try setting session option store.json.all_text_mode to true.
https://drill.apache.org/docs/json-data-model/
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
answered Nov 22 '18 at 15:42
Arina YelchiyevaArina Yelchiyeva
36616
36616
add a comment |
add a comment |
I figured it out. KVGEN method doesn't work if json is nested.
To make it work, there are two approaches which can be followed -
- Take out the nested json outside
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231},"districts":{"id":41,"name":"abc","region":"123"}}
and then apply KVGEN method as select kvgen(t.project) from dfs.filePath t
Apply kvgen method on inner json first and then use nested query as below
select tbl2.col1.id, tbl2.col2.value from (select tbl1.project as col1, flatten(kvgen(tbl1.project.districts)) col2 from dfs.filePath tbl1) tbl2
And as rightly mentioned by @arina-yelchiyeva, session option store.json.all_text_mode needs to be set to true.
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
add a comment |
I figured it out. KVGEN method doesn't work if json is nested.
To make it work, there are two approaches which can be followed -
- Take out the nested json outside
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231},"districts":{"id":41,"name":"abc","region":"123"}}
and then apply KVGEN method as select kvgen(t.project) from dfs.filePath t
Apply kvgen method on inner json first and then use nested query as below
select tbl2.col1.id, tbl2.col2.value from (select tbl1.project as col1, flatten(kvgen(tbl1.project.districts)) col2 from dfs.filePath tbl1) tbl2
And as rightly mentioned by @arina-yelchiyeva, session option store.json.all_text_mode needs to be set to true.
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
add a comment |
I figured it out. KVGEN method doesn't work if json is nested.
To make it work, there are two approaches which can be followed -
- Take out the nested json outside
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231},"districts":{"id":41,"name":"abc","region":"123"}}
and then apply KVGEN method as select kvgen(t.project) from dfs.filePath t
Apply kvgen method on inner json first and then use nested query as below
select tbl2.col1.id, tbl2.col2.value from (select tbl1.project as col1, flatten(kvgen(tbl1.project.districts)) col2 from dfs.filePath tbl1) tbl2
And as rightly mentioned by @arina-yelchiyeva, session option store.json.all_text_mode needs to be set to true.
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
I figured it out. KVGEN method doesn't work if json is nested.
To make it work, there are two approaches which can be followed -
- Take out the nested json outside
{"project":{"id":"2625","createDate":1542597000000,"rank":0,"highlight":false,"isDisplay":true,"isNewProject":true,"propertyId":2231},"districts":{"id":41,"name":"abc","region":"123"}}
and then apply KVGEN method as select kvgen(t.project) from dfs.filePath t
Apply kvgen method on inner json first and then use nested query as below
select tbl2.col1.id, tbl2.col2.value from (select tbl1.project as col1, flatten(kvgen(tbl1.project.districts)) col2 from dfs.filePath tbl1) tbl2
And as rightly mentioned by @arina-yelchiyeva, session option store.json.all_text_mode needs to be set to true.
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
answered Nov 26 '18 at 9:29
Lokesh JainLokesh Jain
537
537
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%2f53429243%2fapache-drill-getting-error-on-kvgen-method%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
