question Re: How to retrieve Latest Uploaded records from Hive , In my table dont DATE column , Only Modified Timestamp Column only Available ? in Archives of Support Questions (Read Only)

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

nittala999 — Thu, 15 Mar 2018 17:18:06 GMT

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 ?

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

Shu_ashu — Thu, 15 Mar 2018 20:07:16 GMT

@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.

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

nittala999 — Tue, 20 Mar 2018 17:38:09 GMT

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