Apache Hive Table Create Date

This post will show you how to check the table’s creation date in Apache Hive. SQL developers will look for such information in system tables or DMVs, depending on the relational database. However, there are no such tables in Apache Hive to get the details. However, there is a way to get it.

Let’s see how it can be achieved with an example.

–Creating a test table.
CREATE TABLE ViewTableCreateDate(SampleColumn String);

–Check the extended properties
DESC EXTENDED ViewTableCreateDate;
OK
SampleColumn string

Detailed Table Information
Table(
tableName:viewtablecreatedate,
dbName:default,
owner:cloudera,
createTime:1622257984,
lastAccessTime:0,
retention:0,
sd:StorageDescriptor(cols:[FieldSchema(name:samplecolumn, type:string, comment:null)],
location:hdfs://quickstart.cloudera:8020/user/hive/warehouse/viewtablecreatedate,
inputFormat:org.apache.hadoop.mapred.TextInputFormat,
outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false,
numBuckets:-1,
serdeInfo:SerDeInfo(
name:null,
serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=1}),
bucketCols:[],
sortCols:[],
parameters:{},
skewedInfo:SkewedInfo(
skewedColNames:[],
skewedColValues:[],
skewedColValueLocationMaps:{}),
storedAsSubDirectories:false),
partitionKeys:[],
parameters:{transient_lastDdlTime=1622257984},
viewOriginalText:null,
viewExpandedText:null,
tableType:MANAGED_TABLE)

Please see the table’s ‘expanded properties’ output above. The information in bold is important to assess. The name of the table, the database in which it is created, the date it was created, the storage format, the location of the stored data, and the table type.

Since the CreateTime and transient lastDdlTime values are in epoch format, you can convert them using “FROM_UNIXTIME” function.

hive> SELECT FROM_UNIXTIME(1622257984);
OK
2021-05-28 20:13:04
Time taken: 0.129 seconds, Fetched: 1 row(s)

Hive_Create_Table_Date
Hope you find this article helpful.