Hive query to transform an array of struct to array of string

Question

My use case is as following:

I want to copy data from Table-A to Table-B and transform field1 from array of struct to array of string where the string is the val1 property of struct in table-A and ignore val2.

Table-A:
field1: array<struct<val1: str, val2: int>>
sample data:
[{val1: "abc", val2: 123}, {val1: "def", val2: 456}], [{val1: "xyz", val2: 789}]

Table-B:
field1: array<string>
sample data:
["abc", "def"], ["xyz"]

I am not able to figure out how I can select the field1 column with transformation on it through hive query language.

The things I was able to figure out was I could explode the array, perform a select of the val1 and then do collect_list but after trying multiple times could not get the correct syntax.

My query was like:

select collect_list(select col.val1 
  from explode(field1) as col) from table-A

I also want to strictly do it through hiveql and not through a udf in python.

Thank you.

can you post some sample data with the expected output and your attempt? — Vamsi Prabhala
– Vamsi Prabhala, Commented Aug 14, 2020 at 13:49
@VamsiPrabhala, added the sample data. my query was like: select collect_list(select col.val1 from explode(field1) as col) from table-A — rohit
– rohit, Commented Aug 14, 2020 at 14:07

leftjoin · Accepted Answer · 2020-08-15 10:39:47Z

1

Explode original array using Lateral View + explode and collect array of struct.val1 using collect_set() or collect_list():

with mydata as (--This is your data example, use your table instead of this CTE
select stack (2,
array(named_struct("val1", "abc", "val2", 123), named_struct("val1", "def", "val2", 456)), 
array(named_struct("val1", "xyz", "val2", 789))
) as myarray
)

select t.myarray as original_array, collect_set(s.val1) as result_array
  from mydata t
       lateral view explode(myarray) e as s --struct
group by t.myarray

Result:

original_array                                          result_array
[{"val1":"abc","val2":123},{"val1":"def","val2":456}]   ["abc","def"]
[{"val1":"xyz","val2":789}]                             ["xyz"]

Your structure can be also declared as map, not struct. In this case use s['val1'] instead of s.val1 to get map element.

edited Aug 15, 2020 at 10:39

answered Aug 15, 2020 at 6:49

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

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

Comments

Rogier Peters · Accepted Answer · 2021-03-25 13:53:29Z

0

There is some magic in the handling of arrays that allow you to do this:

select t.myarray as original_array, t.myarray.val1 from mydata t

I.e. selecting struct field val1 from an array of structs will return an array of val1

From http://thornydev.blogspot.com/2013/07/querying-json-records-via-hive.html

edited Mar 25, 2021 at 13:53

answered Mar 25, 2021 at 13:47

Rogier Peters

214 bronze badges

Collectives™ on Stack Overflow

Hive query to transform an array of struct to array of string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related