Skip to main content
Delphix

Rule Set Feature: Custom SQL (KBA4455)

 

KBA

KBA# 4455

At a Glance   

Description: Used to Filter and Alter (including casting) data before masking using SQL statement.
Location: UI: Rule Set > Edit Table Settings > Custom SQL
API: [not available]
Configuration: The following parameter is used to configure this feature: 
  • Custom SQL: Complete SELECT SQL statement. 
Rule Set
Indicator:
Table Indicator in the Rule Set: 'SQL'.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Usage   

This feature is used to specifically define the SQL statement used to read the data from the database table. The columns needed in the statement depends on the job type (IP or OTF).

The 'Custom SQL' can also be used to alter datatypes and values (as in casting). 

Note

Note:

In earlier versions, the Custom SQL requires the SQL statement to be defined in one single line.

 

Masking: In-Place

When using In-Place (IP) the 'Custom SQL' should include: 

  • All masked columns.
  • All required Unique Row Identifiers (URI).
  • Optional: Column detailed to be required for a custom algorithm. 

URI is the Unique Row Identifier, which is required to update the masked row in In-Place masking. This URI is usually a Primary Key or, for Oracle, the ROWID. At times it is required to manually set this URI or to create a key other than the Primary Key.

Note

Note:

If additional columns are specified, these are not used. Additional columns may adversely affect performace and consume additional memory.

 

Masking: On-The-Fly

When using On-The-Fly (OTF), the 'Custom SQL' should include all columns. The best is to only use 'Custom SQL' if a value needs to be CAST. Any column that is not included will be set to NULL. 

Note

Note:

For Oracle, ROWID should not be included.

Profiling

The 'Custom SQL' affects profiling. If all columns need to be profiled, ensure the Custom SQL is erased or contains all columns. 

UI Examples  

In the examples below, the following names have been used: 

  • Rule Set: 'Rule Set Features RS'
  • Original Table Name: 'Main_Table'

Rule Set page  

Please note the 'SQL' indicator when the table has a 'Custom SQL' defined. 

UI RS - SQL.png

Edit Table Settings popup  

This shows an example of a 'Custom SQL'. In this case, it is the engine generated SQL. 

The SQL statement will include all columns in the table (based on the Rule Set). Columns not required need to be deleted. 


UI RS - Popup - Custom SQL .png

 

No Invalid Input error  

There is no verification if the 'Custom SQL' is valid or not. The product assumes the SQL is valid and that it can be used in the Masking Job. 

Related Articles  

The following articles may provide more information or related information to this article: