Search code examples
sqlhadoophiveimpalasql-function

Workaround to query array column as rows in Impala

In Hive, I can use explode function but how to do that in Impala?

I read this but still have no clue:

Is there a function equivalent to Hive's 'explode' function in Apache Impala?

This is how I created the table in Hive:

create table tb (arr_col array<string>)

insert into tb select array ('A','B') from (select 'temp') x

My query would give error:

select tb.arr_col from tb

..in select list returns a complex type 'ARRAY<STRING>'.
Only scalar types are allowed in the select list.

OR 

select tb.arr_col.item from tb

ERROR: AnalysisException: Illegal column/field reference 'arr_col.item' with intermediate collection 'item' of type 'ARRAY<STRING>'

Please advise the best way. Thanks

Solution

  • This is how you query an array in Impala which could be the equivalent to explode

    select arr_col, m.item from tb , tb.arr_col m ;

    By default impala use the name "item" to access your elements of primitive arrays. In the case of array of structures, you need to change "item" for the field that you want to access.