Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r084

D toc

In the

D s webapp
rtrue
, you can rename individual columns through the column drop-down. Through transform steps, you can apply renaming to one or more columns.

D s minrows

Name Requirements

  • Column names are case-insensitive and cannot begin with whitespace.
  • Column names cannot contain escaped characters, such as \n.
Info

NOTE: When publishing to Avro, Parquet, or database tables,

column names support alphanumeric characters and the underscore (_) character only. Column names cannot begin with a numeral. Other characters cause an error to occur.


Info

NOTE: Column names with spaces or special characters in a transformation must be wrapped by curly braces. Example:

Code Block
column1,{Column 2 with space},column3
Tip

Tip: To prevent potential issues with downstream systems, you should limit your column lengths to no more than 128 characters.

Reserved keywords

The following keywords should not be used as column names, as they may conflict with underlying requirements of the platform or the running environments with which it integrates:

Info

NOTE: This list may not be complete. If your job fails with a duplicate column error, please review your column names to identify potential reserved keywords among them.

  • TRIFACTA__LINEAGE_INFO 
  • TRIFACTA__FILE_LINEAGE_INFO 

    Info

    NOTE: There are two underscore characters in a row (__) after TRIFACTA in each of the above entries.

Rename Individual Columns

Rename a column through column menu

To rename a column, click the drop-down caret next to the column name. Click Rename.

Rename a column through suggestions

Steps:

  1. If your column already exists, click the name of the column. 
  2. Click the Rename suggestion card.
  3. Click Modify.
  4. Replace the newColumnName value with your preferred column name.

Rename a column through transformation

You can use the following transformation to rename a single column through the Transform Builder. In this case, the Rename columns transformation is used to perform a manual rename of MySourceCol to MyNewCol.

D trans
RawWrangletrue
p03ValueMyNewCol
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueManual rename
p02NameColumn
p02ValueMySourceCol
p03NameNew name
SearchTermRename columns

Rename a new column

Columns that are generated through transform steps are given a default name. 

For the following types of transforms, however, you can specify the column name as part of the step:

  • derive
  • extractkv
  • merge
  • nest

When a transform is added to the recipe, an as: clause is automatically added to the transform step. You can modify your transform to change the value of the as: column. For example, the following transform generates a new column with the first word from the Name column. The as: value renames this generated column as FirstName:

D trans
p03ValueFirstName
Typestep
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueFIND(Name,`{start} `,false,0)
p03NameNew name
SearchTermNew formula

Auto-Generated Column Names

When your transforms generate new columns, names are automatically assigned to these columns based on the following pattern. 

  1. If the transform includes a function reference, the function name is included in the new column. Example:

    D trans
    Typestep
    p01NameFormula type
    p01ValueSingle row formula
    p02NameFormula
    p02ValueLEFT(city,3)
    SearchTermNew formula


    New column name: left_city

  2. If the above step is applied again, a duplicate column is generated with the following name. Example:

    D trans
    Typestep
    p01NameFormula type
    p01ValueSingle row formula
    p02NameFormula
    p02ValueLEFT(city,3)
    SearchTermNew formula


    New column name: left_city1

  3. If the transform does not contain a function reference, the following convention is used:

    D trans
    Typestep
    p01NameFormula type
    p01ValueSingle row formula
    p02NameFormula
    p02Value'A'
    SearchTermNew formula

    New column name: column1

    D trans
    Typestep
    p01NameFormula type
    p01ValueSingle row formula
    p02NameFormula
    p02Value'B'
    SearchTermNew formula

    New column name: column2

Rename Multiple Columns

D s product
 enables to rename multiple columns using a single transformation. You can perform this batch renaming using one of the methods described in this section.

Info

NOTE: In macros, Rename Columns transformations do not work. This is a known issue.

Tip

Tip: To prevent potential issues with downstream systems, you should limit your column lengths to no more than 128 characters.


Steps:

  1. Open the Transform Builder to add a new step to your recipe. 
  2. From the drop-down in the first textbox, select Rename columns.
  3. Select your method of renaming. See below.
  4. Select the column or columns to which to apply the rename.

    Tip

    Tip: To apply the renaming across all columns in the dataset, select All. This option is useful for pattern-based renames, such as adding a prefix or changing case.

  5. To add the step to your recipe, click Add.

Manual rename multiple columns

For each column that you select, you must add the new name just below the old one. 

  • To add additional columns to the mapping, click Add.
  • To remove columns from the mapping, click Remove.

Add prefix

For the selected columns, you can apply a specific prefix value to the names.

Old Column NamesPrefixNew Column Names
column1pre_pre_column1
column2pre_pre_column2
column3pre_pre_column3

Transformation:

D trans
RawWrangletrue
p03Valuepre_
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueAdd prefix
p02NameColumn
p02Valuecolumn1,column2,column3
p03NamePrefix
SearchTermRename columns

Add suffix

For the selected columns, you can apply a specific suffix value to the names. Example:

Old Column NamesSuffixNew Column Names
column1_newcolumn1_new
column2_newcolumn2_new
column3_newcolumn3_new

Transformation:

D trans
RawWrangletrue
p03Value_new
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueAdd suffix
p02NameColumn
p02Valuecolumn1,column2,column3
p03NameSuffix
SearchTermRename columns

Apply rename to all columns

The following transformation performs the same rename as the previous one. Instead, it uses the All option to apply the rename across all columns of the dataset. If the number of columns changes in the future, then the rename is still applied across all of the columns in the dataset.

Transformation:

D trans
RawWrangletrue
p03Value_new
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueAdd suffix
p02NameColumns
p02ValueAll
p03NameSuffix
SearchTermRename columns

Convert to lowercase

For the selected columns, you can convert the columns names to lowercase. Example:

Old Column NamesNew Column Names
Dailydaily
POS_Costpos_cost
Sales_Typesales_type

Transformation:

D trans
RawWrangletrue
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueConvert to lowercase
p02NameColumn
p02ValueDaily,POS_Cost,Sales_Type
SearchTermRename columns

For example, if the old column name is Sales_Type, then the new column name is renamed to sales_type.

Convert to UPPERCASE

For the selected columns, you can convert the columns names to uppercase. Example:

Old Column NamesNew Column Names
Daily

DAILY

POS_CostPOS_COST
Sales_TypeSALES_TYPE

Transformation:

D trans
RawWrangletrue
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueConvert to UPPERCASE
p02NameColumn
p02ValueDaily,POS_Cost,Sales_Type
SearchTermRename columns

For example, if the old column name is Sales_Type, then the new column name is renamed to SALES_TYPE.

Keep from beginning (left)

For the selected columns, you can specify the number of characters to keep from the beginning (left) of the column names.  Based on the number of characters you provide, the column name is updated. Example:

Old Column NamesNumber of charactersNew Column Names
Daily3

Dai

POS_Cost3POS
Sales_Type3Sal

Transformation:

D trans
RawWrangletrue
p03Value3
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueKeep from beginning (left)
p02NameColumn
p02ValueDaily,POS_Cost,Sales_Type
p03NameNumber of characters
SearchTermRename columns

For example, if the old column name is Sales_Type, then based on the number of characters to keep from the beginning (left) is 3, then new column name is renamed to Sal.

Keep from end (right)

For the selected columns, you can specify the number of characters to keep from end (right) of the column names.  Based on the number of characters you provide, the column name is updated. Example:

Old Column NamesNumber of charactersNew Column Names
Daily4

aily

POS_Cost4Cost
Sales_Type4Type

Transformation:

D trans
RawWrangletrue
p03Value4
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueKeep from beginning (right)
p02NameColumn
p02ValueDaily,POS_Cost,Sales_Type
p03NameNumber of characters
SearchTermRename columns

For example, if the old column name is Sales_Type, then based on the number of characters to keep from the end (right) is 4, then new column name is renamed to Type.

Info

NOTE: If the number of characters are more than the length of the column names, then the whole name of the column is retained.

Find and replace

You can apply literals, 

D s lang
itempatterns
, or regular expressions to match patterns of text in the source column names. These matching values can then be replaced by a fixed value.

Tip

Tip: The default behavior is to replace the first instance.Use the Match all occurrences checkbox to apply the pattern matching across all columns in your set.

For the selected columns, you can specify the number of characters to keep from end (right) of the column names.  Based on the number of characters you provide, the column name is updated. Example:

Old Column NamesNew Column Names
column1

Field1

column2

Field2

column3

Field3

Transformation:

D trans
RawWrangletrue
p03Value'column'
Typestep
WrangleText##NO_WRANGLE##
p01NameOption
p01ValueFind and replace
p02NameColumn
p02Valuecolumn1,column2,column3
p03NameFind
p04Value'Field'
p04NameReplace with
SearchTermRename columns

The above uses literal values for find and replace. For more information on pattern-based matching, see Text Matching.

Use row(s) as column names

When this method is applied, all of the values in the specified row or rows are used as the new names for each column.

Info

NOTE: This method applies to all columns in the dataset.

Types:

TypeDescription
Use a single row to rename columns

Specify the row number in the sample to use as the source for column names.

Info

NOTE: Source row number information must be available. See below.

Use the first row in the sample to rename columns Use the first row in the sample as the name for all columns.
Combine multiple rows to rename columns

Specify two or more rows to combine into column names. Details are below.

Info

NOTE: Source row number information must be available. See below.

Source row number information:

Info

NOTE: If source row number information is no longer available, this method cannot be used for column rename.

  • If a value is not applied for the source row number, the next row of data is used.
  • Source row numbers apply. Current row numbers may not be the same. In the data grid, mouse over the leftmost column to see available row information.
  • Each value in the row or combination of values across rows must be unique within the set of new column names.
  • The row is removed from its original position.
  • If the product is unable to find unique multi-row headers for the column, the first row of the header set is used.

Combine multiple rows

The following transformation renames the columns in the dataset based on the values in rows 3 and 4 of the data:

D trans
p03Value3
p06NameFill across?
p01NameOption
p06ValueSelected
p03NameRow Numbers - row A
p04Value4
SearchTermRename columns
Typestep
p05NameChoose your separator
p01ValueUse row(s) as column names
p02NameType
p02ValueCombine multiple rows to name columns
p05Value'_'
p04NameRow Numbers - row B

In the above:

  • The separator is defined as an underscore character (_). This value can be empty.
  • When Fill across is selected, if any row value is empty, the last non-empty value for the row in a previous column is used as part of the column header.