Athena equivalent to information_schema

Question

For background, I come from a SQLServer background and make heavy use of the system tables & information_schema, to tell me all about my tables and columns.

I didn't expect the exact same power in Athena, but currently very shocked and frustrated with what little seems to be available - unless I've missed something ?

For example, 'describe mytable' - just describes 1 table at a time. How about showing the columns for ALL tables in one result ? It also does not output the table name, nor allow you to manually add that in as a custom column.

All the results of these "show/list/describe" commands seem to produce a text list - not a recordset, so you cannot take the results and join them to other tables or views to make more complex outputs.

Is there any other way to query the contents of my databases ?

Thanks in advance

Answer 1

Athena is based on Presto. Presto provides information_schema schema and I checked and it is accessible in Athena.

You can run e.g. a query like:

SELECT * FROM information_schema.columns;

to get a list of columns of all tables.

You can filter this by "database":

SELECT * FROM information_schema.columns WHERE table_schema = '<databasename>';

Note however that these types of queries are not necessarily very performant.