Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive metadata report

avatar
New Contributor

Hello, we are still in the process of implementing Hortonworks in our institution. I have a hive database with hive managed tables and I refresh the data every night from the source (Oracle, Sql Server) by replacing the old data file with new file. I wanted to get last update time (which would be the last refresh date time). Does hive store that info in the metadata table? I do see 'transient_lastDdlTime' when I run 'describe formatted table_name' command. Is that the one that is storing the information I am looking for? Does that get updated anytime table data is updated (in my case replaced?) How do I query that as it doesn't seem to be in a different date time format? Any help would be greatly appreciated.

4 REPLIES 4

avatar
Guru

@Rkg Grg It would be easy for you to test it out. If the table is managed then simply perform "describe formatted" and monitor if the time is changing after 2 minutes without changing anything. Alternatively, do the same test after adding 1 row and see if that changes the "transient_lastDdlTime".

avatar
New Contributor

Thanks for taking your time to answer my question. I did a test and it seems it updates the transient_lastDdlTime value when an insert is made. However, it doesn't update the value when the data file of the table is replaced (which I am doing). Is there some settings in metadata tables or in hive that needs to be turned on for the table have this kind of metadata updated? Also, the value of transient_lastDdlTime is in something like '1483631785'. How do I change this to reflect a regular date time?

Thank you.

avatar
Rising Star
You can get the date/timestamp value of transient_lastDdlTime by running below command:
select cast(from_unixtime(1483631785) AS timestamp);

avatar

@Rkg Grg

'last_ddltime' gets updated everytime a table is modified.

How are you replacing the data file of the table ?

I have always used LOAD DATA to replace the data from a new file and it updates last_ddltime on every occassion.

LOAD DATA [LOCAL] INPATH '/pathToNewFile' OVERWRITE INTO TABLE tablename;

Converting unixtime to timestamp

select cast(from_unixtime(1483631785) AS timestamp);