Column Renamer

The Column Renamer processor renames columns in your data. The processor can rename a specified column. It can remove a specified prefix, suffix, or string from all column names. It can replace a string with a replacement string from all column names that include a specified string or that match a regular expression. It can also change the case for all column names, or add a prefix or suffix to all column names.

When you configure a Column Renamer processor, you specify the rename type to use and configure related properties. You can configure multiple sets of rename configurations in the processor.

Examples

Adding a suffix
Say you want to add _NA as a suffix to all columns in the data. To do this, you configure the processor as follows:
  • Rename Type: Add Suffix to All Columns
  • Suffix: _NA
Replacing multiple suffixes with the same string
Say your data includes columns with _US and _MX suffixes that you want to update to _NA. To do this, you can use a regular expression, as follows. Note how the dollar sign ($) at the end of the match pattern ensures that the specified pattern matches only when it appears at the end of the column names:
  • Rename Type: Find and Replace in all Columns by Regular Expression
  • Match Pattern: _(US|MX)$
  • Replacement: _NA
Tip: If you are uncomfortable with complex regular expressions, you can always configure additional rename properties instead.
For example, say you discover that some columns use _USA instead of _US. To handle this, you can update the regular expression to allow for this additional case. Or, you can simply add another set of properties, as follows:
  • Rename Type: Find and Replace in all Columns by String
  • Match Pattern: _USA
  • Replacement: _NA
Important: Use both Find and Replace in all Columns options carefully since they can replace matching patterns in any location in the column name. For example, if you use US as the match pattern, the processor can incorrectly rename USER_US as NAER_NA.

Configuring a Column Renamer

Configure a Column Renamer processor to rename columns in your data.

  1. On the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Cache Data Caches processed data.
  2. Configure the following properties on the Rename tab:
    Rename Property Description
    Rename Type Method to use to rename columns:
    • Rename column name for a single column - Renames a single column with the specified new column name.
    • Remove prefix from all columns - Removes a specified string of characters from the beginning of all column names.
    • Remove suffix from all columns - Removes a specified string of characters from the end of all column names.
    • Remove string from all columns - Removes a specified string of characters from any location in column names.
    • Find and replace in all columns by regular expression - Uses a specified match pattern to find the columns to rename, then replaces the matched string with the specified replacement string in any location in column names. For an example, see Examples.
    • Find and replace in all columns by string - Uses a specified match pattern to find the columns to rename, then replaces the matched string with the specified replacement string in any location in column names. For an example, see Examples.
    • Change case for all columns - Changes the case for all column names based on the specified option.
    • Add prefix to all columns - Adds a specified string to the beginning of all column names.
    • Add suffix to all columns - Adds a specified string to the end of all column names.
    Case Insensitive Performs case-insensitive searches for matching column names.

    Available except when changing the case for column names.

    Default is enabled.
    Column Name Name of the column to rename.

    Available when renaming a single column.
    New Column Name New name for the column.

    Available when renaming a single column.
    Remove String String to remove from all column names. The specified string is removed from column names based on the specified rename type.

    Available when removing a prefix, suffix, or string from column names.
    Match Pattern Regular expression that evaluates to the strings to match in the original column names.

    Available when replacing strings in column names.
    Replacement String to replace the matched pattern in column names.

    Available when replacing strings in column names.
    Target Case Case to convert column names to:
    • Upper Case Unquoted - Converts to all uppercase letters, such as SALES_TOTAL.
    • Upper Case Quoted - Converts to all uppercase letters and encloses in quotes, such as "SALES_TOTAL".

      Use for column names with special characters.

    • Lower Case Quoted - Converts to all lowercase letters and encloses in quotes, such as "sales_total".

      Use for column names with special characters.

    • Title Case - Capitalizes the first letter of the column name, such as Sales_total.
    Note: The processor does not convert to lowercase unquoted, because Snowflake converts lowercase unquoted names to uppercase. For more information, see the Snowflake knowledge base.
    Prefix String to add to the beginning of all column names.

    Available when adding a prefix to column names.
    Suffix String to add to the end of all column names.

    Available when adding a suffix to all column names.