HiveQL: Merge array columns
0
I have a table with columns having arrays, how to write HiveQL to merge the array columns?
| id | colA | colB | colC
+----+------------------+-----------------+------------------
| 1 | ["john", "james"]| ["peter"] | ["sam","peter"]
| 2 | ["jane"] | ["doug"] | ["mary","peter"]
| 3 | ["jan", "james"] | ["peter","mary"]| ["sam","peter"]
Write query to display data like this:
| id | newcol
+----+------------------------------------------------------
| 1 | ["john", "james", "peter", "sam","peter"]
| 2 | ["jane", "doug", "mary","peter"]
| 3 | ["jan", "james", "peter","mary","sam","peter"]
sql hive hiveql
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
add a comment |
0
I have a table with columns having arrays, how to write HiveQL to merge the array columns?
| id | colA | colB | colC
+----+------------------+-----------------+------------------
| 1 | ["john", "james"]| ["peter"] | ["sam","peter"]
| 2 | ["jane"] | ["doug"] | ["mary","peter"]
| 3 | ["jan", "james"] | ["peter","mary"]| ["sam","peter"]
Write query to display data like this:
| id | newcol
+----+------------------------------------------------------
| 1 | ["john", "james", "peter", "sam","peter"]
| 2 | ["jane", "doug", "mary","peter"]
| 3 | ["jan", "james", "peter","mary","sam","peter"]
sql hive hiveql
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
display the data
– DevEx
Dec 28 '18 at 13:12
add a comment |
0
0
0
I have a table with columns having arrays, how to write HiveQL to merge the array columns?
| id | colA | colB | colC
+----+------------------+-----------------+------------------
| 1 | ["john", "james"]| ["peter"] | ["sam","peter"]
| 2 | ["jane"] | ["doug"] | ["mary","peter"]
| 3 | ["jan", "james"] | ["peter","mary"]| ["sam","peter"]
Write query to display data like this:
| id | newcol
+----+------------------------------------------------------
| 1 | ["john", "james", "peter", "sam","peter"]
| 2 | ["jane", "doug", "mary","peter"]
| 3 | ["jan", "james", "peter","mary","sam","peter"]
sql hive hiveql
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
I have a table with columns having arrays, how to write HiveQL to merge the array columns?
| id | colA | colB | colC
+----+------------------+-----------------+------------------
| 1 | ["john", "james"]| ["peter"] | ["sam","peter"]
| 2 | ["jane"] | ["doug"] | ["mary","peter"]
| 3 | ["jan", "james"] | ["peter","mary"]| ["sam","peter"]
Write query to display data like this:
| id | newcol
+----+------------------------------------------------------
| 1 | ["john", "james", "peter", "sam","peter"]
| 2 | ["jane", "doug", "mary","peter"]
| 3 | ["jan", "james", "peter","mary","sam","peter"]
sql hive hiveql
sql hive hiveql
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
edited Dec 28 '18 at 13:13
DevEx
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
asked Dec 28 '18 at 12:48
DevExDevEx
83731836
83731836
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
display the data
– DevEx
Dec 28 '18 at 13:12
add a comment |
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
display the data
– DevEx
Dec 28 '18 at 13:12
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
display the data
– DevEx
Dec 28 '18 at 13:12
display the data
– DevEx
Dec 28 '18 at 13:12
add a comment |
3 Answers
3
active
oldest
votes
1
If you would like to create a new string array out of several string arrays, you can use the following (note that you need to check colB and colC for being empty, in order to avoid extra commas):
split (
concat (
concat_ws(',',colA),
if(size(colB)>0, concat(',', concat_ws(',',colB)), '' ),
if(size(colC)>0, concat(',', concat_ws(',',colC)), '' )
),',')
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
add a comment |
1
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;
OK
["john","james","peter","sam","peter"]
for your case.
select split(concat_ws(',',ColA, ColB, ColC), ",") as a;
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i haveHive 1.2.1000and it works. not sure about any other version.
– Gaurang Shah
Dec 29 '18 at 1:00
add a comment |
0
Something like that :
Concat or ||
SELECT id, colA||','||colB||','||colC AS newcol FROM myTable
OR
SELECT id, Concat(colA,',',colB,',',colC,) AS newcol FROM myTable
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
which hive version are you using, for me it saysCONCAT only takes primitive arguments
– Gaurang Shah
Dec 28 '18 at 19:15
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
});
}
});
draft saved
draft discarded
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%2f53958869%2fhiveql-merge-array-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
If you would like to create a new string array out of several string arrays, you can use the following (note that you need to check colB and colC for being empty, in order to avoid extra commas):
split (
concat (
concat_ws(',',colA),
if(size(colB)>0, concat(',', concat_ws(',',colB)), '' ),
if(size(colC)>0, concat(',', concat_ws(',',colC)), '' )
),',')
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
add a comment |
1
If you would like to create a new string array out of several string arrays, you can use the following (note that you need to check colB and colC for being empty, in order to avoid extra commas):
split (
concat (
concat_ws(',',colA),
if(size(colB)>0, concat(',', concat_ws(',',colB)), '' ),
if(size(colC)>0, concat(',', concat_ws(',',colC)), '' )
),',')
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
add a comment |
1
1
1
If you would like to create a new string array out of several string arrays, you can use the following (note that you need to check colB and colC for being empty, in order to avoid extra commas):
split (
concat (
concat_ws(',',colA),
if(size(colB)>0, concat(',', concat_ws(',',colB)), '' ),
if(size(colC)>0, concat(',', concat_ws(',',colC)), '' )
),',')
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
If you would like to create a new string array out of several string arrays, you can use the following (note that you need to check colB and colC for being empty, in order to avoid extra commas):
split (
concat (
concat_ws(',',colA),
if(size(colB)>0, concat(',', concat_ws(',',colB)), '' ),
if(size(colC)>0, concat(',', concat_ws(',',colC)), '' )
),',')
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
edited Dec 28 '18 at 15:04
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
answered Dec 28 '18 at 14:33
mangustamangusta
1,63921326
1,63921326
add a comment |
add a comment |
1
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;
OK
["john","james","peter","sam","peter"]
for your case.
select split(concat_ws(',',ColA, ColB, ColC), ",") as a;
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i haveHive 1.2.1000and it works. not sure about any other version.
– Gaurang Shah
Dec 29 '18 at 1:00
add a comment |
1
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;
OK
["john","james","peter","sam","peter"]
for your case.
select split(concat_ws(',',ColA, ColB, ColC), ",") as a;
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i haveHive 1.2.1000and it works. not sure about any other version.
– Gaurang Shah
Dec 29 '18 at 1:00
add a comment |
1
1
1
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;
OK
["john","james","peter","sam","peter"]
for your case.
select split(concat_ws(',',ColA, ColB, ColC), ",") as a;
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;
OK
["john","james","peter","sam","peter"]
for your case.
select split(concat_ws(',',ColA, ColB, ColC), ",") as a;
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
answered Dec 28 '18 at 19:10
Gaurang ShahGaurang Shah
3,04211333
3,04211333
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i haveHive 1.2.1000and it works. not sure about any other version.
– Gaurang Shah
Dec 29 '18 at 1:00
add a comment |
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i haveHive 1.2.1000and it works. not sure about any other version.
– Gaurang Shah
Dec 29 '18 at 1:00
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
does "concat_ws" accept multiple arrays as argument?
– mangusta
Dec 29 '18 at 0:50
@mangusta i have
Hive 1.2.1000 and it works. not sure about any other version.– Gaurang Shah
Dec 29 '18 at 1:00
@mangusta i have
Hive 1.2.1000 and it works. not sure about any other version.– Gaurang Shah
Dec 29 '18 at 1:00
add a comment |
0
Something like that :
Concat or ||
SELECT id, colA||','||colB||','||colC AS newcol FROM myTable
OR
SELECT id, Concat(colA,',',colB,',',colC,) AS newcol FROM myTable
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
which hive version are you using, for me it saysCONCAT only takes primitive arguments
– Gaurang Shah
Dec 28 '18 at 19:15
add a comment |
0
Something like that :
Concat or ||
SELECT id, colA||','||colB||','||colC AS newcol FROM myTable
OR
SELECT id, Concat(colA,',',colB,',',colC,) AS newcol FROM myTable
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
which hive version are you using, for me it saysCONCAT only takes primitive arguments
– Gaurang Shah
Dec 28 '18 at 19:15
add a comment |
0
0
0
Something like that :
Concat or ||
SELECT id, colA||','||colB||','||colC AS newcol FROM myTable
OR
SELECT id, Concat(colA,',',colB,',',colC,) AS newcol FROM myTable
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
Something like that :
Concat or ||
SELECT id, colA||','||colB||','||colC AS newcol FROM myTable
OR
SELECT id, Concat(colA,',',colB,',',colC,) AS newcol FROM myTable
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
answered Dec 28 '18 at 13:18
Daniel E.Daniel E.
1,9921721
1,9921721
which hive version are you using, for me it saysCONCAT only takes primitive arguments
– Gaurang Shah
Dec 28 '18 at 19:15
add a comment |
which hive version are you using, for me it saysCONCAT only takes primitive arguments
– Gaurang Shah
Dec 28 '18 at 19:15
which hive version are you using, for me it says
CONCAT only takes primitive arguments– Gaurang Shah
Dec 28 '18 at 19:15
which hive version are you using, for me it says
CONCAT only takes primitive arguments– Gaurang Shah
Dec 28 '18 at 19:15
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53958869%2fhiveql-merge-array-columns%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
Do you want to display the data or do a new table ?
– Daniel E.
Dec 28 '18 at 13:09
display the data
– DevEx
Dec 28 '18 at 13:12