Search code examples
sqlhadoophiveimpala

Impala/Hive get list of tables along with creator and date created

I'm trying to clean up some dev/test tables in Impala for my team, but can't seem to find a way to list out tables with their creator and the date last accessed. The show tables command simply lists out the table names. Because there are hundreds of tables, with less than a quarter belonging to our team, going through each table individually to see if it should be dropped would take hours.

Is there not a way to list out table names along with the creator and date created?

Edit: I can see the creator and creation date/time when I click on a table's info button in Hue, so I know the information is stored somewhere: enter image description here

Solution

  • One way around this, take each table and describe table. You need to run the following command:

    describe formatted <your_table_name>;

    There you can find details such as below,

    Database:

    Owner:

    CreateTime:

    LastAccessTime:

    Other way around is, in mysql metadata is stored in hive database you can query for tables created and its meta information in below query,

    use hive;
select * from TBLS;