Athena query to count rows for each table in Glue catalog

Question

Hi,

Following from this article: 
[Get record count for all tables in mysql database](https://www.tutorialspoint.com/get-record-count-for-all-tables-in-mysql-database#:~:text=To%20get%20the%20count%20of,The%20syntax%20is%20as%20follows.&text=mysql%3E%20SELECT%20SUM(TABLE_ROWS)%20%2D%3EFROM%20INFORMATION_SCHEMA.),
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;
```

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

```

Athena query to count rows for each table in Glue catalog

相關內容