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 Risposta
1
Risposta accettata

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
con risposta un anno fa
  • 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?

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande