Hive metadata report

[#1]

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.

[#2]

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

[#3]

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.

[#4]

You can get the date/timestamp value of transient_lastDdlTime by running below command:

select cast(from_unixtime(1483631785) AS timestamp);

[#5]

@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);