CODEWITHSUNDEEP
hiveexplode
user3570083
user3570083

Reputation: 21

hive explode with sequence number

The data is like below:

col1  Col2  pathstr
3   5   some_string_a> some_string_b>some_string_c
8   6   some_string_d> some_string_e>some_string_f

The third column "pathstr" is a path data with order. I user explode function as below: 

SELECT col1, col2, path,
FROM table_paths
LATERAL VIEW explode(split(pathstr,'>')) subView as path;

and got the following:

3 5 some_string_a
3 5 some_string_b
3 5 some_string_c
8 6 some_string_d
8 6 some_string_e
8 6 some_string_f

However, exploded data loses the order information of the path string. I wonder if I can generate an extra 'sequence' column like below. Or there is a better way to do it. 

3 5 some_string_a, 1
3 5 some_string_b, 2
3 5 some_string_c, 3
8 6 some_string_d, 1
8 6 some_string_e, 2
8 6 some_string_f, 3

Upvotes: 2

Views: 5502

Answers (2)

Elijah
Elijah

Reputation: 25

you can use row_number() or rank() or dense_rank()

SELECT col1, col2, row_number(t.path) over(partition by col1, col2)
FROM
(SELECT col1, col2, path,
FROM table_paths
LATERAL VIEW explode(split(pathstr,'>')) subView as path) t

Upvotes: 0

hrushikesh
hrushikesh

Reputation: 317

You can use posexplode. It explodes with two columns, the position in the array and the value.

Example hive query:

hive> SELECT a.col1, a.col2, b.path, b.pos
> FROM (
>     SELECT 3 col1, 5 col2, 
>         "some_string_a> some_string_b>some_string_c" pathstr
>     UNION ALL
>     SELECT 8 col1, 6 col2, 
>         "some_string_d> some_string_e>some_string_f" pathstr
> ) a
> LATERAL VIEW POSEXPLODE(split(pathstr,'>')) b as pos, path
> ;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201708181020_16679, Tracking URL = /jobdetails.jsp?jobid=job_201708181020_16679
Kill Command = /opt/mapr/hadoop/hadoop-0.20.2/bin/../bin/hadoop job  -kill job_201708181020_16679
Hadoop job information for Stage-1: number of mappers: 0; number of reducers: 0
2017-08-19 07:48:31,023 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.5 sec
MapReduce Total cumulative CPU time: 1 seconds 500 msec
Ended Job = job_201708181020_16679
MapReduce Jobs Launched: 
Job 0:  Cumulative CPU: 1.5 sec   MAPRFS Read: 264 MAPRFS Write: 80 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 500 msec
OK
3   5   some_string_a   0
3   5    some_string_b  1
3   5   some_string_c   2
8   6   some_string_d   0
8   6    some_string_e  1
8   6   some_string_f   2
Time taken: 327.33 seconds, Fetched: 6 row(s)

Upvotes: 3

Related Questions