How to see the date when the table was created?

Question

I have created a table couple months ago. Is there any way in HIVE that I can see when was the table created?

show table doesn't give the date creation of the table.

Aerostatics

Answer 1

Execute the command desc formatted <database>.<table_name> on the hive cli. It will show detailed table information similar to

Detailed Table Information

Database:
Owner:
CreateTime:
LastAccessTime:

Sybille

Okay. But how do you write it? I tried commands like show table. Example: database: customers, table_name: address. Hive query: show table customers.address. – Aerostatics

How can I save it into a table so I can run query against it. I tried to use it inside a subquery or insert it into a table, but it does not work! – Bernoulli

Answer 2

You need to run the following command:

describe formatted <your_table_name>;

Or if you need this information about a particular partition:

describe formatted <your_table_name> partition (<partition_field>=<value>);

Flurried

We have to provide all the partition columns to get this working. Example: My table has two columns as partition. describe formatted db_name.table_name partition (acquisition_date = '2021-10-07', acquisition_hour = '07'); – Vibrio

Answer 3

First of all, enable hive support when you create your spark session:

spark = SparkSession.builder.appName('AppName').enableHiveSupport().getOrCreate()

And then:

df_desc = spark.sql('describe formatted <your_table_name>')
df_desc.show()

Johanna