How to get the record count of all tables in athena aws

Question

I'm trying to figure out how to get the total number of records across all tables (in all table schemas) in my aws Athena. The following is what I tried, but it appears that the information schema does not offer the record count. Someone please assist.

SELECT t.table_schema, t.table_name, t.table_rows
FROM   "information_schema"."schemata" s
INNER JOIN "information_schema"."tables" t on s.schema_name = t.table_schema
INNER JOIN "information_schema"."columns" c on c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE c.table_catalog = 'awsdatacatalog'

Answer 1

I would argue that it is not part of the schema information for two rather obvious reasons: first, it does not affect performance; second, in order to offer record counts, Athena, Presto, and Trino must process all data files and sources.

AFAIK The only alternative is to construct the query using SQL or another language, and then execute it because Presto/Trino does not support any type of procedural query execution (such PL/SQL paired with something allowing to run SQL from string). Let's start with this:

with tables(full_name) as(
    SELECT '"' || t.table_schema || '"."' || t.table_name || '"' as full_name
    FROM "information_schema"."tables" t
)

select array_join(array_agg('select ''' || full_name || ''' as table_name, count(*) as rows_count from ' || full_name), ' union all ')
from tables
group by true;

As an alternative, you can write a custom Athena function using lambda, which will execute the necessary SQL statement on the fly.