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

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.


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

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

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.