Is there any way to get the number of records in multiple tables at once in Athena-CodePudding

I am trying to use Athena to get the number of records from each of several tables at once. I have run the following query but I get an error. Is it possible for Athena to set the table names dynamically like this?

WITH TARGET_TABLES AS(
SELECT table_name
FROM information_schema.tables WHERE table_name like 'prefix_%'
)
SELECT 
 (SELECT COUNT(*) FROM TT.table_name) FROM TARGET_TABLES TT;

CodePudding user response：

As mentioned in the comments that is not possible.

The only option I could think of was dynamically build the select statement with unions. Then copy and paste that result into a new query window and run it.

Something like:

with t(i) as (
    SELECT concat(
            'select ''',
            table_name,
            ''' as table_name,  count(*) from ',
            table_name
        )
    FROM information_schema.tables
    WHERE table_name like 'prefix_%'
)
select array_join(array_agg(i), ' union ') as result
from t

That would give you a result of:

select 'prefix_table1' as table_name, count(*) from prefix_table1 union select 'prefix_table2' as table_name, count(*) from prefix_table2 union select 'prefix_table3' as table_name, count(*) from prefix_table3

Then just copy and paste to a new query window and run it:

table_name      _col1
---------------------
prefix_table1   23
prefix_table2   97
prefix_table3   23