Athena query to count rows for each table in Glue catalog

0

Hi,

Following from this article: Get record count for all tables in mysql database, is there an Athena on Presto version of the following MySQL query?

SELECT table_name
       , table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = schema_name;
1 Answer
1
Accepted Answer

You can do this as a two step process.

  1. Dynamically building the SQL for getting the counts using the below query.
  2. Running the output of the SQL to generate the counts
with tname_vw(i) as (
    SELECT concat(
            'select ''',
            table_name,
            ''' as table_name,  count(*) from ',
            table_name
        )
    FROM information_schema.tables
    WHERE table_schema = 'schema_name'
)
select array_join(array_agg(i), ' union ') as result
from tname_vw

MKB
answered 2 months ago
  • Thanks MKB,

    this query returns one row with the following string:

    """ select table_1 as table_name, count() from table_1 union select table_2 as table_name, count() from table_2 ... """

  • AWS-learner, Once you run the Query within the sting, you should be seeing the counts for the respective tables in the schema. Does that address your issue?

  • Oh I see, thanks it works! Is there a way to pass the output of the dynamic query to a function and then run the final query within Athena? Thanks!

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions