How to retrieve Latest Uploaded records from Hive , In my table dont DATE column , Only Modified Timestamp Column only Available ?

[#1]

Hi Team,

I want to retrieve the recent uploaded records in that day , My table dont have Date column , But Time stamp column is availble

Example : Last uploaded date is 2018-03-15

Upload happens in below Time

2018-03-07 01:13:26

2018-03-07 01:13:34

2018-03-07 01:13:39

2018-03-07 01:13:43

2018-03-07 01:13:50

2018-03-07 01:13:54

Now i want to find latest upload date , In that date how many records are uploaded ?

In where condition i can able to give only one time stamp ( Max time stamp ), So In that day remaining time stamps how can i give ?

[#2 · Accepted solution]

@Satya Nittala

You can use to_date function in your where clause to get only the max(Last uploaded date) records.

Example:-

I'm having a table

hive> select * from test_table;
+-----+-------+-----------+----------------------+--+
| id  | name  |    dt     |        daily         |
+-----+-------+-----------+----------------------+--+
| 1   | hcc   | 12:00:00  | 2017-10-10 12:00:00  |
| 1   | foo   | 12:00:00  | 2017-10-11 12:00:00  |
| 1   | foo   | 12:00:00  | 2017-10-12 12:00:00  |
| 2   | bar   | 09:00:00  | 2017-12-23 09:00:00  |
| 3   | foo   | 09:00:00  | 2018-03-15 09:00:00  |
| 4   | bar   | 09:00:00  | 2018-03-15 09:00:00  |
+-----+-------+-----------+----------------------+--+

Now i want to get only the records that having max date i.e 2017-03-15 is our max date.

hive> select count(*) from test_table where to_Date(daily) in (select max(to_Date(daily)) from test_table);

+------+--+
| _c0  |
+------+--+
| 2    |
+------+--+

As i have got only 2 records because there are 2 records in the table that are having 2018-03-15 as date in daily column and we have used to_Date function it will extract only the date from timestamp.

to_date function:-

hive> select to_date(daily) from test_table;
+-------------+--+
|     _c0     |
+-------------+--+
| 2017-10-10  |
| 2017-10-11  |
| 2017-10-12  |
| 2017-12-23  |
| 2018-03-15  |
| 2018-03-15  |
+-------------+--+

Let us know if you having some issues..!!

.

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

@Shu , Thank You...its working..