Delphix Products

 View Only

Rule Set Edit Feature - End of Life

By Anil Kumar posted 06-24-2021 06:27:38 AM

  

Introduction

The Database Rule Set options to specify a Table Suffix, Add Column, Join Table, and List on a table have been deprecated and will be removed from the product. This article describes how to identify if you are using these options, and, if so, how to convert them to an equivalent configuration using other, supported options.

Background

The Masking Engine allows a rule set table’s setting to be customized. These settings can be edited using the Edit Table Settings dialog  (Rule Set -> Edit -> Edit Table). This dialog is shown in the following screenshot:



Note: This dialog is only applicable for database rule sets.


The Edit Table Settings dialog offers the following seven customization options:


  1. Logical Key - If a table has no primary keys defined in the database and is used in an In-Place masking job, you need to specify an existing column or columns to act as a logical key.
  2. Edit Filter - Allows you to specify a filter to run on the data before loading it to the target database.
  3. Custom SQL - Allows you to declare custom SQL statements to filter data for a table.
  4. Table Suffix - If a ruleset has tables with names that change periodically, for example tables that are appended with the current date, this option allows you to include those tables in the rule set.
  5. Add Column - Allows you to select a column or columns from a table when you do not want to load data to all the columns in a table.
  6. Join Table - Use this feature to specify a SQL join to select specific data based on values in a second (or third...) table.
  7. List - Allows you to select a list to use for filtering data in a table.

Of these seven options, the last four options (Table Suffix, Add Column, Join Table, List) are redundant with functionality available in other features. Therefore, these four redundant options have been deprecated and will be removed from the product with the release of 6.0.10.


How to identify if you are affected

You can complete the following procedure to determine if you are affected by the deprecation of these settings:

    1. For each rule set on the Rule Set page, click the pencil icon to edit the rule set
    2. Review each table in the list
    3. If any table has one of the following qualifiers, then you are using one of the deprecated settings:
    •  COL - Add Column feature is used
    • JC  - Join Table feature is used
    • LST  - List feature is used
    • SUF  - Table Suffix feature is used

          Alternate Functionality


          • If you were using the Table Suffix functionality, you can achieve the same results with a series of API calls. These API calls are described below.
          • For Add Column, Join Table, and List, you need to convert these settings to the equivalent Custom SQL configuration before upgrading to a release without these features.

          Table Suffix

          When this setting is used, a ruleset automatically handles a table with a name that matches a regular pattern. After removal of this feature, you need to update the ruleset to remove any outdated table names, add any new table names, and apply the same algorithms to columns of the new table.

          Step 1: Get the table metadata ID of the old table with a suffix

          In the following example, API calls are used to implement the functional equivalent of the table suffix feature on a rule set with ruleset_id = 99.


          API Endpoint:

          GET /table-metadata

          Request URL:

          GET http://<masking-host>/masking/api/table-metadata?ruleset_id=99&page_number=1

          Response Body:

          {

            "_pageInfo": {

              "numberOnPage": 2,

              "total": 2

            },

            "responseList": [

              {

                "tableMetadataId": 33,

                "tableName": "another_table",

                "rulesetId": 99,

                "customSql": ""

              },

              {

                "tableMetadataId": 34,

                "tableName": "old_test_table_with_suffix",

                "rulesetId": 99,

                "customSql": "SELECT \"DATA_00\" , \"ID\" , \"DATA_01\" , \"DATA_02\" FROM \"dbo\".\"testdata_DEFAULT\""

              }

            ]

          }


          Note: The old table, named “old_test_table_with_suffix”, has a tableMetadataId = 34.

          Step 2:  Create table metadata with the new table name


          API Endpoint:

          POST /table-metadata API

          Request URL:

          http://<masking-host>/masking/api/table-metadata

          Request body:

          {

            "tableName": "test_table_with_suffix",

            "rulesetId": 99

          }

          Response Body:

          {

            "tableMetadataId": 35,

            "tableName": "test_table_with_suffix",

            "rulesetId": 99

          }


          Note: The new table, named “test_table_with_suffix”, was assigned tableMetadataId = 35.

          Step 3: Get All column metadata for the old tableMetadataId

          Retrieve the old column metadata using table_metadata_id = 34 (from Step 1) and is_masked = true.


          API Endpoint:

          GET /column-metadata

          Request URL:

          http://<masking-host>/masking/api/column-metadata?table_metadata_id=34&is_masked=true&page_number=1

          Response Body:

          "responseList": [

              {

                "columnMetadataId": 382,

                "columnName": "idd",

                "tableMetadataId": 34,

                "algorithmName": "RandomValueLookup",

                "domainName": "RANDOM_VALUE_SL",

                "dataType": "int",

                "columnLength": 0,

                "isMasked": true,

                "isProfilerWritable": true,

                "isPrimaryKey": false,

                "isIndex": false,

                "isForeignKey": false,

                "notes": ""

              }

            ]

            

          Step 4: Get All column metadata for the new tableMetadataId 

          Get column metadata for the new table with tableMetadataId = 35 (revealed in Step 2).


          API Endpoint:

          GET /column-metadata

          Request URL:

          http://<masking-host>/masking/api/column-metadata?table_metadata_id=35&page_number=1

          Response Body:

          "responseList": [

              {

                "columnMetadataId": 383,

                "columnName": "idd",

                "tableMetadataId": 35,

                "dataType": "int",

                "columnLength": 0,

                "isMasked": false,

                "isProfilerWritable": true,

                "isPrimaryKey": false,

                "isIndex": false,

                "isForeignKey": false

              }

            ]


          Step 5: Apply the same algorithms to the new columns


          Match the exact column name with the result from Step 3, and apply "algorithmName" and "domainName" from the matched old column metadata.


          API Endpoint:

          PUT /column-metadata/{columnMetadataId}

          Request URL:

          http://<masking-host>/masking/api/column-metadata/383

          Request body:

          Request body:

          {

            "algorithmName": "RandomValueLookup",

            "domainName": "RANDOM_VALUE_SL",

            "isProfilerWritable": false

          }

          Response Body:

          {

            "columnMetadataId": 383,

            "columnName": "idd",

            "tableMetadataId": 35,

            "algorithmName": "RandomValueLookup",

            "domainName": "RANDOM_VALUE_SL",

            "dataType": "int",

            "columnLength": 0,

            "isMasked": true,

            "isProfilerWritable": false,

            "isPrimaryKey": false,

            "isIndex": false,

            "isForeignKey": false

          }

          Step 6: Delete the old table metadata

          Use the tableMetadataId = 34 of the old table (revealed in Step 1).


          API Endpoint:

          DELETE /table-metadata/{tableMetadataId}

          Request URL:

          http://<masking-host>/masking/api/table-metadata/34

          Add Column


          Users can edit the Custom SQL to remove the columns that should not be read/written.


          Before upgrading to a release without the Add Column feature, the Add Column configuration needs to be converted to the equivalent Custom SQL configuration. For example if columns DATA_01 and DATA_00 are selected in the Add Column setting (See first screenshot below), then the Custom SQL needs to be updated as shown in the second screenshot below. 


          Note: Custom SQL with the existing configuration of the Add Column feature is auto generated when you click the Custom SQL option. You can save this SQL by clicking Save in the dialog.





          Join Table

          Users can edit the Custom SQL to specify a SQL join to select specific data based on values in a second (or third...) table.


          Before upgrading to a release without the Join Table features, the Join Table configuration needs to be converted to the equivalent Custom SQL configuration. For example, if the Join Table setting is configured as shown in the first screenshot below, then the Custom SQL needs to be updated as shown in the second screenshot below.


          Notes: 

          • Custom SQL with the existing configuration of the Join Table feature is auto generated when you click the Custom SQL option. You can save this SQL by clicking Save in the dialog.
          • There is an issue in releases 6.0.3.0 to 6.0.8.0 which causes the Custom SQL tab not to automatically populate the Custom SQL input box. However, you can edit and save the Custom SQL manually by building the SQL query based on the features you applied. This issue (DLPX-70520) has been fixed in the 6.0.9.0 release.



          List

          Users can edit the Custom SQL to define the IN clause for a column.


          Before upgrading to a release without the List feature, the List configuration needs to be converted to the equivalent Custom SQL configuration. For example the List setting is configured as shown in the first screenshot below, then the Custom SQL needs to be updated as shown in the second screenshot below.


          Notes: 

          • Custom SQL with existing configuration of the List feature is auto generated when you click the Custom SQL option. You can save this SQL by clicking Save in the dialog.
          • There is an issue in releases 6.0.3.0 to 6.0.8.0 which causes the Custom SQL tab not to automatically populate the Custom SQL input box. However, you can edit and save the Custom SQL manually by building the SQL query based on the features you applied. This issue (DLPX-70520) has been fixed in the 6.0.9.0 release.



          Combinations of Add Column, Join Table, and List


          In the examples above, only one setting has been converted to Custom SQL at a time. In some situations, you might have multiple settings enabled on a table. In those situations, the same process described above can be used to convert each setting to Custom SQL. For example if a table had the Add Column, Join Table and List settings configured as shown in their respective examples above, all these settings could be converted into Custom SQL as shown in the example below.



          #Masking
          #eol
          #end_of_life
          #support
          #sql
          0 comments
          67 views

          Permalink