How to get all tables detailed information under specific schema

Question

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?

Answer 1

Yes, you are correct.

Athena can run only one describe command at a time as of now.

You could try using external script like below:

import boto3

# Initialize Athena client
client = boto3.client('athena', region_name='your-region')

# List of tables you want to describe
tables = ['table1', 'table2', 'table3']

for table in tables:
    query = f"DESCRIBE EXTENDED database_name.{table}"
    
    response = client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={'Database': 'database_name'},
        ResultConfiguration={'OutputLocation': 's3://your-bucket/athena-results/'}
    )
    
    print(f"Started query for table {table}. QueryExecutionId: {response['QueryExecutionId']}")