Increment column value for unique key in Hive

Question

Below is the data.

col1,col2 combination will give unique key. col3 has 2 different timestamp formats with one being TZ format(2015-02-14T03:45:23.345Z), the other being normal format(2015-02-14 03:45:23).

col1,col2,col3,col4
rank1,IP1,2015-02-14T03:45:23.345Z,2015-02-12 00:00:00Z
rank1,IP1,2015-02-14T03:45:23.145Z,2015-02-12 00:00:00Z
rank1,IP1,2015-02-14T03:45:23.465Z,2015-02-12 00:00:00Z
rank1,IP2,2015-02-14T03:45:23.345Z,2015-02-12 00:00:00Z
rank1,IP2,2015-02-14T03:45:23.125Z,2015-02-12 00:00:00Z
rank2,IP1,2015-02-14 03:44:11,2015-02-12 00:00:00Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:00Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:00Z

Based on unique key(col1,col2) combination,col3 needs to be sorted in ascending. Once col3 is in ascending, we need to increment col4 by every 1 second of that combination.

Below is my query.

select col1,col2,col3,CONCAT(from_unixtime(unix_timestamp(col4, "yyyy-MM-dd HH:mm:ss") + row_number() over 
(partition by col1,col2 order by from_unixtime(unix_timestamp(col3, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) asc) ),'Z') As col4
from ipconf;

I am getting the output below which is incorrect

rank1,IP1,2015-02-14T03:45:23.465Z,2015-02-12 00:00:01Z
rank1,IP1,2015-02-14T03:45:23.345Z,2015-02-12 00:00:02Z
rank1,IP1,2015-02-14T03:45:23.145Z,2015-02-12 00:00:03Z
rank1,IP2,2015-02-14T03:45:23.125Z,2015-02-12 00:00:01Z
rank1,IP2,2015-02-14T03:45:23.345Z,2015-02-12 00:00:02Z
rank2,IP1,2015-02-14 03:44:11,2015-02-12 00:00:01Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:02Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:03Z

Expected output:

rank1,IP1,2015-02-14T03:45:23.145Z,2015-02-12 00:00:01Z
rank1,IP1,2015-02-14T03:45:23.345Z,2015-02-12 00:00:02Z
rank1,IP1,2015-02-14T03:45:23.465Z,2015-02-12 00:00:03Z
rank1,IP2,2015-02-14T03:45:23.125Z,2015-02-12 00:00:01Z
rank1,IP2,2015-02-14T03:45:23.345Z,2015-02-12 00:00:02Z
rank2,IP1,2015-02-14 03:44:11,2015-02-12 00:00:01Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:02Z
rank2,IP1,2015-02-14 03:45:23,2015-02-12 00:00:03Z

You have two different types of dates in col3. You will get null for one of the formats, I think. — philantrovert
(1) Does col1,col2 combination seem unique to you? (2) Does using 2 timestamp formats seem O.K. to you? Does using STRING column to hold timestamps seem O.K. to you? — David דודו Markovitz
Yes.. 1) combination is unique. 2) Yes.. I am using String.. Going with String is fine. — user4119502

Syed Tousif Syed Tousif · Accepted Answer · 2017-09-08T12:40:37

Below query will work for you.

select A.col1, A.col2 ,A.col3,
CONCAT(from_unixtime(unix_timestamp(A.col4, "yyyy-MM-dd HH:mm:ss") + row_number() over
(partition by A.col1,A.col2 order by A.new_rn asc) ),'Z') As col4 
from
(select 
   col1, col2 ,col3, 
   row_number() over ( partition by col1,col2 order by col3 asc ) as new_rn
from ipconf) A;

Increment column value for unique key in Hive

1 Answers