Hive array of string into array of int

Question

In Hive I have a table with a column as string datatype and it contains set of numbers say marks. So I want to apply some arithmetic operations like addition, so I used split command and it returns array. Bt I can't apply addition to the array I think I need to convert into array to apply any arithmetic operations. I used command followed

select sum(a) from (select split(marks, ':') as a from tbl) b;

And I tried to cast it into array also bt not working giving some error. I tried following commands

select sum(a) from (select cast(split(marks, ':') as a array<int>) from table) b;
select sum(a) from (select cast(b) as array<int> from (select split(marks, ':') as b from tbl) c) d;

Please suggest me the solution for this.. And also how to cast array to array

Could you please provide your input sample? That way it'll be easy to help you — Rohit Nimmala
– Rohit Nimmala, Commented Jun 6, 2018 at 5:29
Marks 45:52:56:74 85:65:63:42 63:52:43:68 Marks column as string datatype and table name is tbl — Suresh Kumar
– Suresh Kumar, Commented Jun 6, 2018 at 6:48
You can do CAST(Split(marks,':')[0] AS bigint),CAST(Split(marks,':')[1] AS bigint),CAST(Split(marks,':')[2] AS bigint),CAST(Split(marks,':')[3] AS bigint) — Rohit Nimmala
– Rohit Nimmala, Commented Jun 6, 2018 at 6:52

Bhagyashree Sarkar · Accepted Answer · 2018-06-06 05:22:56Z

1

You can use this:

select sum(cast(a as int)) from TableName;

answered Jun 6, 2018 at 5:22

Bhagyashree Sarkar

5194 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suresh Kumar Over a year ago

It won't work I think it will give classCastException because a is an array<string> so we can't cast into int..

Bhagyashree Sarkar Over a year ago

You can use this: select a, sum(cast(a as int)) as sum_of_marks from TableName lateral view explode (split(marks,':')) t1 AS a

Rohit Nimmala · Accepted Answer · 2018-06-06 07:34:09Z

1

You can do something like this, hope this helps

Select 
 sum(a), 
 sum(b), 
 sum(c), 
 sum(d)
From
(Select
  CAST(Split(marks,':')[0] AS bigint) AS a, 
  CAST(Split(marks,':')[1] AS bigint) AS b,
  CAST(Split(marks,':')[2] AS bigint) AS c, 
  CAST(Split(marks,':')[3] AS bigint) AS d
From
Table) split_data
Group by <some column>

edited Jun 6, 2018 at 7:34

answered Jun 6, 2018 at 7:05

Rohit Nimmala

1,55912 silver badges28 bronze badges

Collectives™ on Stack Overflow

Hive array of string into array of int

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related