CODEWITHSUNDEEP
hadoophiveimpala
HP.
HP.

Reputation: 19896

Convert other types to Impala array

I have a program that inject the string like this ('A','B') into a query. How to convert it into array lateral view like this

col
---
 A
 B

There is no explode(array('A','B')) function in Impala vs. Hive. I cannot store these values in table as it's in memory and injects dynamically into SQL.

The reason I need this is I have another table to join with the items in the array.

Thanks

Upvotes: 0

Views: 2531

Answers (1)

hlagos
hlagos

Reputation: 7947

Impala only allow to select primitive types as part of the select statement, to explode your array (I am guessing your table only has a column called myarray) you will need to do something likes this.

Demo: In Hive

create table test_array (myarray array<string>) stored as parquet ; 
-- tricky way to insert values for array 

insert into test_array select  array('a','b') from (select '1') t;
insert into test_array select  array('c','d') from (select '1') t;

Impala

select myarray.item from test_array, test_array.myarray ;

Results

+------+
| item |
+------+
| a    |
| b    |
| c    |
| d    |
+------+

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.

One interesting thing if we add an empty array 

 insert into test_array select  array() from (select '1') t;

if we run the same query, you will not see the empty array as part of the result, this is because it is essentially like do an inner join between your records and the array excluding empty values. If you want to include your records with empty array information, the query will looks like 

 select myarray.item from test_array LEFT OUTER JOIN  test_array.myarray ;

+------+
| item |
+------+
| c    |
| d    |
| a    |
| b    |
| NULL |
+------+

(You can find more information about different joins here

Upvotes: 1

Related Questions