15

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

SimonB
  • 962
  • 1
  • 14
  • 36

1 Answers1

29

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.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
  • Now I am kicking myself !! Thanks :) – SimonB Jul 02 '19 at 11:59
  • @SimonB information_schema.tables has no inherent limitation of 100 relations in Trino (formerly Presto). If the limitation exists, it would probably either added by Athena or by a client tool. – Piotr Findeisen Apr 17 '22 at 19:32
  • 1
    Athena limitation on information_schema.VIEWS; it only supports <=100 objects. If your database has more than 100 tables/views, then this view will/can return zero results. Confirmed limitation with Amazon support, but it is still undocumented in any official capacity. – SimonB Apr 18 '22 at 08:57