CODEWITHSUNDEEP
bashhadoop
Raghunath
Raghunath

Reputation: 614

how to use Posexplode function in hive

I am using posexplode to split single to multiple records in hive. Along with multiple records as output i need to generate sequence number for each row.

col1, col2, col3 and col4 are defined as string because rarely we get alpha data as well.

col1 | col2| col3 | col4 
---------------------------
  7  |  9  |  A   |  3
  5  |  6  |  9

Seq | Col
----------
 1  |  7
 2  |  9
 3  |  A
 4  |  3
 1  |  5
 2  |  6
 3  |  9

I am using below mentioned query but I am getting error

-bash: syntax error near unexpected token (

My query is :

SELECT  
    seq, col  
FROM 
  (SELECT  array(  col1, col2 , col3,col4) as arr_r FROM srctable ) arrayrec
LATERAL VIEW posexplode(arrayrec) EXPLODED_rec as seq, col

How can this be resolved

I am able to run successfully this query :

SELECT  col  FROM 
(SELECT  array(  col1, col2 , col3,col4) 
as arr_r FROM srctable ) arrayrec
LATERAL VIEW explode(arrayrec) EXPLODED_rec as col

Which produces below output

  Col
 -----
   7
   9
   A
   3
   5
   6
   9

I have checked the link : How to get first n elements in an array in Hive

Upvotes: 3

Views: 21198

Answers (1)

user3122114
user3122114

Reputation: 131

Try 

SELECT  Seq, col  FROM 
(SELECT  array(  col1, col2 , col3,col4) 
as arr_r FROM srctable ) arrayrec
LATERAL VIEW posexplode(arrayrec.arr_r) EXPLODED_rec as Seq, col;

Also check your hive version. posexplode() is available as of Hive 0.13.0.

Upvotes: 2

Related Questions