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 Antwort
1
Akzeptierte Antwort

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
beantwortet vor einem Jahr
  • 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?

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen