Using the Custom SQL Rule Set Feature (KBA4455)
KBA
KBA# 4455At a Glance
| Description: | Used to Filter and Alter (including casting) data before masking using SQL statement. |
|---|---|
| Location: | Custom SQL can be modified from the UI and the API. UI: [Environment] > Rule Set > [Edit] > Custom SQL API: tableMetadata |
| Configuration: | The following parameter is used to configure this feature:
|
| UI Code: | When used, the following code appears in the Rule Set (UI): 'SQL'. |
| Oracle: | Masking Oracle databases will need ROWID in the SELECT statement. |
| More Info: | For information about investigating errors and the logs:
More info: |
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 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.2 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
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 will depend on the job type (IP or OTF).
The 'Custom SQL' can also be used to alter datatypes and values (as in casting).
Example
Below is an example of a Custom SQL for In-Place (IP):
SELECT [id_col], [mask_col1], [mask_col2],... FROM [table] WHERE [predicate];
Warning: UNION and ORDER
A warning when creating the SELECT statement - Some statements can cause deadlocks. For example:
- Union will perform multiple scans on the table which can cause deadlocks.
- Order can access blocks/pages in random order and cause deadlocks.
- Where can also cause deadlocks - especially if columns in the predicate are indexed (same reason as Order).
Custom SQL and IP or OTF Masking
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.
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.
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.
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.
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:
- Masking Docs: Managing Rule Set - Custom SQL.
- KBA: Casting Values before Masking (KBA1580)
- KBA: Using Custom SQL (KBA1764)