Oracle Cloud Infrastructure Documentation

Applying Transformations

You can apply a transformation to a single attribute, or you can filter the attributes by a name pattern or data type and then use the Actions menu to apply a transformation to the group of filtered attributes.

Important

For complex data types, see Hierarchical Data Types to understand what is supported.

The following attribute transformations are available.

Change Case Transformation

This transformation lets you change the text case in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Change Case.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Change Case.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. Select the Type of case transformation to perform. You can choose from:
    • Lower: Changes the text string to all lowercase letters
    • Proper: Changes the text string to begin with an uppercase letter, followed by lowercase letters
    • Upper: Changes the text string to all uppercase letters
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Change Data Type Transformation

This transformation lets you change the data type of an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type, and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Change Data Type.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Change Data Type.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. Select the Data Type to transform the attribute or group of attributes to.
  3. Depending on the new data type you selected, you might have to specify other values such as a Length or Scale.
  4. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  5. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  6. Click Apply.
Create Unique ID Transformation

Create Unique ID transformation lets you add an attribute to the data set. The values for the attribute are filled with a 128-bit universally unique identifier (UUID).

  1. On the Data tab, select Create Unique ID from the Actions menu.
    Important

    You can only apply this transformation to the entire data set. If you filter the attributes, the option is disabled.
  2. In the Create Unique ID dialog box, select either String or Number as the data type for the new attribute.
  3. In New attribute name, enter a name for the new attribute.
  4. Click Apply.
Exclude Transformation

This transformation excludes the entire attribute and the data within from the data entity.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Exclude.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Exclude.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. In the Exclude dialog box, click Apply if you confirm that you want to exclude the selected attribute or the group of filtered attributes.
Extract Transformation

This transformation lets you search the data set for specific data values to extract into a new attribute.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Extract.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Extract.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. For Pattern or Extract Pattern, enter a text string or a regular expression following the Oracle Database format to extract from the data entity.
    Note

    Learn more about the Oracle Database regular expression format.
  3. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter the name of the new attribute the extracted values are stored in, or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  4. Select the Data Type of the transformed attribute.
  5. If applicable, enter the Length of the transformed attribute.
  6. Click Apply.
Format Transformation

The format transformation lets you to apply a specific formatting rule like a date or number format to your data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Format.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Format.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. In the Formatting dialog or panel, your options depend on the attribute data type:
    • For String, select the Trimming method (Trim Left, Trim Right, or Trim Both).
    • For Number, enter a value in Decimal Places (optional).
    • For Date, select the date format, and select or deselect the Display Time check box as required.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Hash Transformation

The hash transformation lets you to encrypt data, and generate encrypted values for attributes.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Hash.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Hash.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. In the Hash dialog or panel:
    • Select the hash type (MD5, SHA1, or SHA2).
    • If applicable, select the number of bits.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Merge Attributes Transformation

This transformation lets you merge two or more attributes into a new attribute.

  1. From the Transformations menu on the header of the attribute you want to transform, select Merge Attributes.
  2. For Attributes, select at least one other attribute to merge with.
  3. For Delimiter, select the style of delimiter to separate the values. You can choose from:
    • Comma
    • Space
    • Tab
    • Semicolon
    • Colon
    • Pipe
  4. (Optional) For Prefix, enter a value to add to the front of the data values.
  5. (Optional) For Suffix, enter a value to add to the end of the data values.
  6. For Name, enter the name of the new attribute.
  7. Select the Data Type of the transformed attribute.
  8. If applicable, enter the Length of the transformed attribute.
  9. Click Apply.
Null Fill Up Transformation

This transformation lets you populate null data values in an attribute with a text string, and add an attribute to hold the transformed data values.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Null Fill Up.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Null Fill Up.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. For Replace by, enter a text string or pattern to replace the attribute's null values with.
  3. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  4. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  5. Select the Data Type of the transformed attribute.
  6. If applicable, enter the Length of the transformed attribute.
  7. Click Apply.
Rename Transformation

This transformation renames an attribute.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Rename.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Rename.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new name for the attribute.
    • For New name pattern, by default $0 is entered, which represents the entire matched string. You can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  3. Click Apply.
Replace Transformation

This transformation lets you replace data values in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Replace.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Replace.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. For Search String, enter the text string to look for in the selected attribute.
  3. For Replace String, enter the text string to replace the search string with.
  4. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  5. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  6. Select the Data Type of the transformed attribute.
  7. If applicable, enter the Length of the transformed attribute.
  8. Click Apply.
Replace by Pattern Transformation

This transformation lets you enter a regular expression to search and replace data values in an attribute, and add an attribute to hold the transformed data.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Replace by Pattern.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Replace by Pattern.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Filter by Pattern field.
    • To filter by a Data Type, select a type from the menu.
  2. Select one of the following:
    • Custom: Enter your own regular expression.
    • Template: Use one of the predefined expressions. You can choose from the following regular expression templates:
      • Credit Card (XXXX-XXXX-XXXX-XXXX)
      • Credit Card (XXXX-XXXX-XXXX-1234)
      • Social Security Number (XXX-XX-XXXX)
      • Social Security Number (XXX-XX-1234)
      • Phone Number (XXX-XXX-XXXX)
      • Phone Number (XXX-XXX-1234)
      • Email Address (XX...XX@XX.XX.XXX)
      • Email Address (XX...XX@oracle.com)
  3. If you selected a template in the previous step, the Search Regular Expression and Replace Regular Expression fields are automatically populated for you.
  4. For Search Regular Expression, enter or edit the regular expression that matches the pattern you want to look for in the selected attribute.
  5. For Replace Regular Expression, enter or edit the regular expression to replace the search strings with.
  6. (Optional) Do one of the following, depending on whether you are transforming a single attribute or a group of attributes:
    • For Name, enter a new attribute name or leave the name as-is.
    • For New attribute name pattern, by default $0 is entered, which represents the entire matched string. You can leave the name as-is or you can replace the name with a suffix or prefix. For example, enter $0_REVISED to append a suffix, or enter PREFIX_$0 to add a prefix to the matched string.
  7. (Optional) Select Keep Source Attributes if you want to keep the original attribute in the data.
  8. Select the Data Type of the transformed attribute.
  9. If applicable, enter the Length of the transformed attribute.
  10. Click Apply.
Sort Transformation

The sort transformation lets you sort data in ascending or descending order.

You can apply the transformation on a single attribute, or you can filter the attributes by a name pattern or data type and then do a bulk transformation on a group of attributes.

  1. For a single attribute: From the Transformations menu on the header of the attribute you want to transform, select Sort.

    For a group of attributes: Filter the attributes by using a name pattern or data type. Then click the Actions menu, and select Sort.

    • To filter by a name pattern, enter a simple regex pattern using wildcards (? and *) in the Search by pattern field.
    • To filter by a Data Type, select a type from the menu. All data types is the default.
  2. Select the order you want from the Sort order menu, and then click Apply.