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

MK
answered a year 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?

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