How can I find last modified timestamp for a table in Hive?

Question

I'm trying to fetch last modified timestamp of a table in Hive.

Answer 1

if you are using mysql as metadata use following...

select TABLE_NAME, UPDATE_TIME, TABLE_SCHEMA from TABLES where TABLE_SCHEMA = 'employees';

Answer 2

You may get the timestamp by executing

describe formatted table_name

Answer 3

Here there is already an answer for how to see last modified date for a hive table. I am just sharing how to check last modified date for a hive table partition.

1. Connect to hive cluster to run hive queries. In most of the cases, you can simply connect by running hive command : hive

2. DESCRIBE FORMATTED <database>.<table_name> PARTITION(<partition_column>=<partition_value>);

3. In the response you will see something like this : transient_lastDdlTime 1631640957

4. SELECT CAST(from_unixtime(1631640957) AS timestamp);

Answer 4

you can execute the below command and convert the output of transient_lastDdlTime from timestamp to date.
It will give the last modified timestamp for the table.

show create table TABLE_NAME;

Answer 5

Get the transient_lastDdlTime from your Hive table.

SHOW CREATE TABLE table_name;

Then copy paste the transient_lastDdlTime in below query to get the value as timestamp.

SELECT CAST(from_unixtime(your_transient_lastDdlTime_value) AS timestamp);

Answer 6

This transient_lastDdlTime gives when the last select was run, not necessary modified

Answer 7

Please use the below command:

show TBLPROPERTIES table_name ('transient_lastDdlTime');

Answer 8

With the help of above answers I have created a simple solution for the forthcoming developers.

time_column=`beeline --hivevar db=hiveDatabase --hivevar tab=hiveTable --silent=true --showHeader=false --outputformat=tsv2 -e 'show create table ${db}.${tab}' | egrep 'transient_lastDdlTime'`
time_value=`echo $time_column | sed 's/[|,)]//g' | awk -F '=' '{print $2}' | sed "s/'//g"`
tran_date=`date -d @$time_value +'%Y-%m-%d %H:%M:%S'`
echo $tran_date

I used beeline alias. Make sure you setup alias properly and invoke the above script. If there are no alias used then use the complete beeline command(with jdbc connection) by replacing beeline above. Leave a question in the comment if any.