Is there a way in presto/athena to get column names based on a condition and use that in group by?

Question

Is there a way in athena/presto to get column names based on a condition and use it in group by?

SHOW COLUMNS FROM {table_name}

This query gives me the column details. But I want to use only the column names in some other query. The structure of the table looks like

name_of_service | cost | usage_date | user_123 | user_212 | user_342 | user_5832 | ...

And so on. There are around 500 columns

The use case I am looking at, looks somewhat like this -

SELECT SUM(cost), {column_names_which_start_with_user}
FROM {db}
GROUP BY cost, {column_names_which_start_with_user}

I am not in control of populating the db and there are 100s of columns whose names change every month. Is there a way to do this?

Answer 1

There's a virtual database called information_schema which can be queried for metadata about tables and columns:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'my_table'
AND column_name LIKE 'user_%'

Unfortunately you can't use this to generate the list of columns of a query, because queries can't be dynamic like that. However, you can use it in your client code to generate the query you are after.