Skip to main content
Delphix

Casting Values Before Masking (KBA1580)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA#1580

At a Glance

Description This KBA describes how to CAST a value before masking it using Custom SQL. The CAST value can be any field used in the masking job, such as masked, filter column, or key column.

The KBA also covers some examples and troubleshooting steps.
Affects versions Applicable to all versions of the Masking Engine.
Location UI (before v20.0): Rule Set > [Edit] > Custom SQL
UI (from v20.0): Rule Sets > Actions (Edit) > select table > Edit Custom SQL or Filters

API: GET /table-metadata/{tableMetadataId}/generateCustomSQL
API: PUT /table-metadata/{tableMetadataId}
Custom SQL The SQL in Custom SQL needs to fulfill the following requirements:
  • Must be a SELECT statement.
  • (In-Place) include all columns used in the Algorithms and the key column(s) (ROWID for Oracle).
  • (On-the-Fly) include all columns.
Feature Request A Feature Request, to reduce the size of large columns (i.e LOB, CLOB, Text, PDF, XML, JSON, etc) has been raised - IDEA-3193.
Troubleshooting The most common places for an error are (for more details see below):

Counting Rows:
  
info.log: SELECT COUNT(*) ...


Masking Operation (reading data):

DelphixTableInput.0
Versions Editing the Rule Set has been updated in version 20.0.
More logs For information on troublesshooting:

For information about Casting:

How to edit Custom SQL using the UI:

Issue

This article describes casting values in a field before masking.

You may need to do the following:

  • When there is a conversion error in the Masking Job.
  • When the data has leading or trailing white spaces.
  • When the data needs to be standardized prior to masking - i.e. all UPPER case.
  • When a date has an incorrect format.
Note

Note:

Some of these are changing how data is masked (hence they will not generate an error message). 

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
All All

How to Use Custom SQL

Custom SQL can be modified using the API and User Interface. See documentation for details.

Note

Notes:

When editing the statement, keep the following in mind:

  • The SQL statement needs to be a valid SELECT Statement. 
    • If any column in the table changes, then the SQL needs to be updated.
       
  • In-Place Masking:
    • Only the URI, Masked Columns, and any column needed in Multi Column (MC) algorithms are needed in the SELECT statement.
    • Don't include any additional columns as this will degrade the performance. 
  • On-The-Fly Masking:
    • All columns need to be included. 

Examples

Below are some examples of functions that cast (change the data type of) a value. In many cases, this can resolve issues where the algorithm is not supporting a specific data type or how the data is presented - i.e dates, decimal numbers, currencies, etc. 

All examples are In-Place (IP) and use ID as URI (ROWID for Oracle). 

Note: This is not a complete guide. Please refer to database documentation or Google for syntax and more examples. 

Reduce Size and Improve Performance

If a large text or binary object is masked, it is often not necessary to read and transfer all data to the masking job to mask or redact it.

Feature Request
  • A Feature Request has been added to HASH or NULL large masked columns for CLOBs, XML, PDF, TEXT, JSON, etc (IDEA-3193).
Set to NULL

Sets all values to NULL.

This results in a very small amount of data transferred which improves performance. 
 

Algorithm example: Use with NULL SL.

SQL Server: SELECT ID, NULL as mask from myTable;
Set to 'x'

Sets all values to 'x'.

This results in a very small amount of data transferred which improves performance. 
 

Algorithm example: Use Secure Lookup (to redact to one value) - for example, the string 'The data has been redacted'.

Oracle: SELECT ROWID, 'x' as mask from myTable;
Set to Checksum

Sets all values to a calculated checksum, like '34472462'. 

Two rows with the same value will be masked to the same masked value. 
 

Algorithm example: Secure Lookup. 

SQL Server: SELECT ID, CHECKSUM(mask) as mask from myTable;
Set to Trimmed Hash value

Two rows with the same value will be masked to the same masked value. 

Sets all values to a hash value. This also trims the value to 10 chars, for example: '394D62B149'.

 

Algorithm example: Secure Lookup.

Oracle: select ROWID, substr(standard_hash(mask),1,10) as mask from myTable;

Random Value

Using Newid()

Sets all values to a random string like: '25F4A154-9B37-4A51-9B77-57118AF23FD6'.

Can also be used with LEFT() to reduce the number of bytes (this could improve performance).

As an example, use this with the algorithm: Secure Lookup.

SQL Server: SELECT ID, Newid() as mask from myTable;
Using ROWID

The ROWID is unique and with the Secure Lookup will generate a random masked value.

Note that ROWID is used as URI and as the value to be masked.

Oracle: SELECT ROWID, ROWIDTOCHAR(ROWID) mask from myTable;
Using random

Sets all values to a random string like: 'STLHCKIPIA'.
 

Algorithm example: Secure Lookup.

Oracle: SELECT ROWID, dbms_random.string('U', 10) mask from myTable;

Value to String 

If you encounter an issue due to a value being a number, then casting the value to a string will likely resolve the issue. When casting, it is possible to specifically change the data as needed using arguments.

Cast Number to a String (str)

STR can be used to cast a number to a string. 

SQL Server: SELECT ID, STR(mask) as mask from myTable;
Cast Number to a String (to_char) 

A versatile option to cast a numeric value to a string.

Oracle: SELECT ROWID, TO_CHAR(mask,'90.99') mask from myTable;
Cast Date to a String

Use this if the date needs to be converted to a special format before masking. 

Note: as an example, use (any) Date algorithm.

Oracle: SELECT ROWID, TO_CHAR(mask, 'YYYY/MM/DD') mask from myTable;
SQL Server: SELECT ID, convert(varchar, mask, 111) as mask from myTable;
Cast using cast

The function CAST can also be used.

Syntax (see database documentation for more details): 

CAST(expression AS datatype(length))

Example: 

SQL Server: SELECT ID, CAST(mask as varchar) as mask from myTable;

Clean Data

These examples change the data by standardizing it or removing unwanted spaces - cleaning the data - before masking.

Set to UPPER Case 

Use this if the algorithm is CASE sensitive and John and JOHN should be masked to the same masked value.

SQL Server: SELECT ID, UPPER(mask) as mask from myTable;
Trim Whitespaces  

Use this if the algorithm is sensitive to leading/trailing white spaces.

This is especially problematic when the datatype is CHAR or NCHAR as these will by default have trailing spaces. 

Oracle: SELECT ROWID, LTRIM(RTRIM(mask)) as mask from myTable;
SQL Server: SELECT ID, LTRIM(RTRIM(mask)) as mask from myTable;

Common errors

Errors related to casting will usually be seen when data is read. Errors can also be seen during job creation or during the masking process.

Error generating job

When the job fails to generate (the transformation XML) this usually surfaces as this job error:

2023/11/13 03:59:06 - tbl_Example-xxyyzz - Dispatching started for transformation [KETTLE_MASK...]
2023/11/13 03:59:06 - tbl_Example-xxyyzz - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unable to open transformation: null

The job generation error can be seen in the info.log (UI: Admin > Logs) - below is an example from SQL Server:

2023-11-13T03:58:54,425Z [pool-5-thread-1] ERROR c.dmsuite.common.utils.DatabaseUtil - Error while fetching Column List ::
com.microsoft.sqlserver.jdbc.SQLServerException: 'FooBar' is not a recognized built-in function name.

Solution 

Check and verify that the Custom SQL is valid. If a custom formula is used, this formula needs to be accessible from the masking connector session and its user. 

Missing Field Name

The following error is seen if a field is cast and the new value is not re-named to the same field name:

2023/11/13 04:57:27 - Select values.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Couldn't find field 'MyField' in row!

Solution

Check the Custom SQL and verify that the cast is renamed to the correct column name.

Related Articles

 

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp