I am getting the tables detailed information in Hive with the below query, but I am not finding the equivalent in Athena.
use schema_name;
SHOW TABLE EXTENDED LIKE '*'
As part of the output of above query, I will be getting values for below attributes for each table name. TableName, owner, location,inputformat,outputformat, partioned,partitioncolumns, totalFileSize, maxFileSize, minFileSize, lastAccessTime, lastUpdateTime
Want to get all above details in Athena and this is the approach I am following.
SELECT table_name FROM information_schema.tables WHERE table_schema = 'logging' // Lists all the tables under logging schema.
DESCRIBE EXTENDED AwsDataCatalog.logging.logtable1; // Getting the details in plain text per table, can parse and some how we can fetch relevant data. Do the same for all other tables under schema.
Limitation with above query is, we have to execute the query for each table instead of getting all the tables detailed information in one go.
Is there any better way to query and fetch the required information?