Hive Extract Data in a Array

I need to extract the 5th value from data string array in Hive,

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")

How can I extract "cdpp asd" ie 5th value.

We can use SUBSTR, and INSTR but is there any other way to achieve this?

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

how are you supposed to extract a value from array by using substr or instr?

– mangusta
Nov 20 '18 at 7:49

add a comment |

I need to extract the 5th value from data string array in Hive,

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")

How can I extract "cdpp asd" ie 5th value.

We can use SUBSTR, and INSTR but is there any other way to achieve this?

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

how are you supposed to extract a value from array by using substr or instr?

– mangusta
Nov 20 '18 at 7:49

add a comment |

I need to extract the 5th value from data string array in Hive,

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")

How can I extract "cdpp asd" ie 5th value.

We can use SUBSTR, and INSTR but is there any other way to achieve this?

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

I need to extract the 5th value from data string array in Hive,

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")

How can I extract "cdpp asd" ie 5th value.

We can use SUBSTR, and INSTR but is there any other way to achieve this?

sql hive hiveql

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

edited Nov 20 '18 at 5:55

Aqueous Carlos

293213

asked Nov 20 '18 at 5:50

Bastian

559

asked Nov 20 '18 at 5:50

Bastian

559

asked Nov 20 '18 at 5:50

Bastian

559

how are you supposed to extract a value from array by using substr or instr?

– mangusta
Nov 20 '18 at 7:49

add a comment |

how are you supposed to extract a value from array by using substr or instr?

– mangusta
Nov 20 '18 at 7:49

how are you supposed to extract a value from array by using substr or instr?

– mangusta
Nov 20 '18 at 7:49

add a comment |

3 Answers
3

active

oldest

votes

If your array is in string column then you can remove brackets and double quotes using regexp_replace and split resulted string to get an array using split():

 select split(regexp_replace('("abc", "123-4567", "10", "ax",   "cdpp asd", "00", "q", "na", "avail", "n", "n",   "na")','^\(|\)$|"',''),', *')[4];

OK

cdpp asd

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

1

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

add a comment |

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")



Select arr[4] from tablename;

Output:

cdpp asd

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

add a comment |

1、Maybe you can try to write a UDF to cast this string to an Array arr, then you can use arr[4] to visit the 5th value;

2、Or you can use the following way to get the 5th value:

    select tf.* from (

        select regexp_replace('("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")','\(|\)|"','') as str

    ) t lateral view posexplode(split(str,', ')) tf as pos,val

    where tf.pos = 4;

Note: this way require the array string has no brackets.

answered Nov 20 '18 at 11:25

StrongYoung

3571213

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53386979%2fhive-extract-data-in-a-array%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

If your array is in string column then you can remove brackets and double quotes using regexp_replace and split resulted string to get an array using split():

 select split(regexp_replace('("abc", "123-4567", "10", "ax",   "cdpp asd", "00", "q", "na", "avail", "n", "n",   "na")','^\(|\)$|"',''),', *')[4];

OK

cdpp asd

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

1

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

add a comment |

If your array is in string column then you can remove brackets and double quotes using regexp_replace and split resulted string to get an array using split():

 select split(regexp_replace('("abc", "123-4567", "10", "ax",   "cdpp asd", "00", "q", "na", "avail", "n", "n",   "na")','^\(|\)$|"',''),', *')[4];

OK

cdpp asd

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

1

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

add a comment |

If your array is in string column then you can remove brackets and double quotes using regexp_replace and split resulted string to get an array using split():

 select split(regexp_replace('("abc", "123-4567", "10", "ax",   "cdpp asd", "00", "q", "na", "avail", "n", "n",   "na")','^\(|\)$|"',''),', *')[4];

OK

cdpp asd

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

If your array is in string column then you can remove brackets and double quotes using regexp_replace and split resulted string to get an array using split():

 select split(regexp_replace('("abc", "123-4567", "10", "ax",   "cdpp asd", "00", "q", "na", "avail", "n", "n",   "na")','^\(|\)$|"',''),', *')[4];

OK

cdpp asd

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

answered Nov 20 '18 at 12:01

leftjoin

8,35922051

1

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

add a comment |

1

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

Thanks it helped !!!

– Bastian
Nov 22 '18 at 18:21

@user2452689 You are welcome!

– leftjoin
Nov 22 '18 at 18:24

add a comment |

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")



Select arr[4] from tablename;

Output:

cdpp asd

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

add a comment |

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")



Select arr[4] from tablename;

Output:

cdpp asd

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

add a comment |

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")



Select arr[4] from tablename;

Output:

cdpp asd

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

arr = ("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")



Select arr[4] from tablename;

Output:

cdpp asd

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

edited Nov 20 '18 at 8:07

Nick

25.9k101836

edited Nov 20 '18 at 8:07

Nick

25.9k101836

edited Nov 20 '18 at 8:07

Nick

25.9k101836

answered Nov 20 '18 at 7:13

Naresh

113

answered Nov 20 '18 at 7:13

Naresh

113

answered Nov 20 '18 at 7:13

Naresh

113

add a comment |

1、Maybe you can try to write a UDF to cast this string to an Array arr, then you can use arr[4] to visit the 5th value;

2、Or you can use the following way to get the 5th value:

    select tf.* from (

        select regexp_replace('("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")','\(|\)|"','') as str

    ) t lateral view posexplode(split(str,', ')) tf as pos,val

    where tf.pos = 4;

Note: this way require the array string has no brackets.

answered Nov 20 '18 at 11:25

StrongYoung

3571213

add a comment |

1、Maybe you can try to write a UDF to cast this string to an Array arr, then you can use arr[4] to visit the 5th value;

2、Or you can use the following way to get the 5th value:

    select tf.* from (

        select regexp_replace('("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")','\(|\)|"','') as str

    ) t lateral view posexplode(split(str,', ')) tf as pos,val

    where tf.pos = 4;

Note: this way require the array string has no brackets.

answered Nov 20 '18 at 11:25

StrongYoung

3571213

add a comment |

1、Maybe you can try to write a UDF to cast this string to an Array arr, then you can use arr[4] to visit the 5th value;

2、Or you can use the following way to get the 5th value:

    select tf.* from (

        select regexp_replace('("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")','\(|\)|"','') as str

    ) t lateral view posexplode(split(str,', ')) tf as pos,val

    where tf.pos = 4;

Note: this way require the array string has no brackets.

answered Nov 20 '18 at 11:25

StrongYoung

3571213

1、Maybe you can try to write a UDF to cast this string to an Array arr, then you can use arr[4] to visit the 5th value;

2、Or you can use the following way to get the 5th value:

    select tf.* from (

        select regexp_replace('("abc", "123-4567", "10", "ax", "cdpp asd", "00", "q", "na", "avail", "n", "n", "na")','\(|\)|"','') as str

    ) t lateral view posexplode(split(str,', ')) tf as pos,val

    where tf.pos = 4;

Note: this way require the array string has no brackets.

answered Nov 20 '18 at 11:25

StrongYoung

3571213

answered Nov 20 '18 at 11:25

StrongYoung

3571213

answered Nov 20 '18 at 11:25

StrongYoung

3571213

answered Nov 20 '18 at 11:25

StrongYoung

3571213

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu