Athena - Listado de particiones para tablas en un esquema

 Una forma sencilla de listar las particiones de una tabla es la siguiente:

SELECT concat(cast(year as varchar), '-', cast(month as varchar), '-', cast(day as varchar)) AS partition_values

FROM nombre_del_esquema.nombre_de_la_tabla

GROUP BY year, month, day

ORDER BY year, month, day;

Para obtener la lista de particiones para un conjunto de tablas podemos usar la cláusula UNIÓN:

SELECT 'tabla1' AS table_name, concat(cast(year as varchar), '-', cast(month as varchar), '-', cast(day as varchar)) AS partition_values

FROM tabla1

GROUP BY year, month, day

UNION

SELECT 'tabla2' AS table_name, concat(cast(year as varchar), '-', cast(month as varchar), '-', cast(day as varchar)) AS partition_values

FROM tabla2

GROUP BY year, month, day

UNION

SELECT 'tabla3' AS table_name, concat(cast(year as varchar), '-', cast(month as varchar), '-', cast(day as varchar)) AS partition_values

FROM tabla3

GROUP BY year, month, day

ORDER BY year, month, day;

Otra forma en Presto es consultar la metadata:

SELECT t.table_name, 

       p.partition_ordinal_position, 

       MAX(CASE WHEN c.column_name = 'year' THEN p.partition_value END) as year,

       MAX(CASE WHEN c.column_name = 'month' THEN p.partition_value END) as month,

       MAX(CASE WHEN c.column_name = 'day' THEN p.partition_value END) as day,

       SUM(stats.number_of_rows) as row_count

FROM information_schema.tables t

INNER JOIN information_schema.columns c ON t.table_catalog = c.table_catalog 

                                          AND t.table_schema = c.table_schema 

                                          AND t.table_name = c.table_name

INNER JOIN information_schema.partitions p ON t.table_catalog = p.table_catalog 

                                             AND t.table_schema = p.table_schema 

                                             AND t.table_name = p.table_name

INNER JOIN (SELECT table_schema, 

                   table_name, 

                   partition_name, 

                   CAST(SPLIT_PART(statistics, ',', 1) AS BIGINT) AS number_of_rows

            FROM information_schema.partition_statistics) stats ON t.table_schema = stats.table_schema 

                                                                     AND t.table_name = stats.table_name 

                                                                     AND p.partition_name = stats.partition_name

WHERE t.table_schema = 'pre_us_gravity_snapshots'

GROUP BY t.table_name, 

         p.partition_ordinal_position, 

         year, 

         month, 

         day

ORDER BY t.table_name, 

         CAST(year AS INTEGER), 

         CAST(month AS INTEGER), 

         CAST(day AS INTEGER), 

         p.partition_ordinal_position;

En esta consulta se utilizan las vistas de information_schema para obtener información sobre las tablas y particiones de la base de datos. Primero se realiza una unión entre las tablas information_schema.tables, information_schema.columns, information_schema.partitions y information_schema.partition_statistics para obtener la información necesaria.

Luego, se utiliza una cláusula CASE en cada una de las particiones para separar el valor de los campos year, month y day en columnas distintas. De esta manera, se puede obtener la información de partición en columnas separadas en lugar de tenerlos agrupados en una sola columna.

Finalmente, se utiliza la función SUM() en la columna number_of_rows de la vista information_schema.partition_statistics para obtener el recuento real de filas de cada partición.

La consulta utiliza la cláusula GROUP BY para agrupar los resultados por la tabla, la posición ordinal de la partición, el año, el mes y el día. La cláusula ORDER BY se utiliza para ordenar los resultados por tabla, año, mes, día y posición ordinal de la partición, asegurando que los resultados se presenten en el orden deseado.