How to select and rename columns dynamically based on a Regex?

[DanyEle]

I’ve got a bunch of columns as a result of a columnization processor, and I’d like to rename them to a more intelligible name.

Ex:

year_month_date_start_interval_2021minus01_value_SUM   -> a2021_01
kw_concat_year_2023_KW9_value_SUM                       -> a2023_KW9
date_2022-05-15_00:00:00dot0_value_SUM                   -> a2022_05_15

[DanyEle]

Solution by @christoph.pernul in Slack

• Use the describe inputTable in a query to get the names
• Upply your regex and use the query helper to rename the columns

[christoph.michel]

As the question arises from time to time, to illustrate the suggestion in some more detail find one example of a query processor that applies the above-mentioned strategy for dynamic renaming:

Steps in the above sample query:

1. Create a column with the new column names in a sub-query :
   Get new names:
   ○ Apply a REGEX_REPLACE() to the column names.
   ○ The THEN clause should generate a string in which the original column names get “aliased” by their Regex replacements.
2. COLLECT_SET() from the column of the new column naming strings, SORT_ARRAY() and feed it as a column into the Query Helper Processor’s SELECT-statement.
3. Use “1=1” for the Query Helper Processor’s WHERE-statement.