Masking Inventory Import and Export (KBA1815)

Export Description

Only the selected Rule Set will be exported. CSV format as of 10.0.0.0.

-----------------------------+------------+-------------------------------------------------------------
 CSV Field                   | Value      |  Export comment                                                 
-----------------------------+------------+-------------------------------------------------------------
 Environment Name            | As in UI   | Only the selected Environment
 Rule Set                    | As in UI   | Only the selected Rule Set
-----------------------------+------------+-------------------------------------------------------------
 Table Name                  | As in UI   | All tables in the Rule Set
 Type                        | -          | Normally not used - default '-'
 Parent Column Name          | -          | Normally not used - default '-'
 Column Name                 | As in UI   | All columns in the Rule Set
 Data Type                   | As in UI   | The Data Type for specified column
 Domain                      | As in UI   | The assigned Domain for specified column
 Algorithm                   | As in UI   | The assigned algorithm for specified column
 Document Store Type      ** | As in UI   | If used, example of values are XML or JSON
 File Format              ** | As in UI   | If used, the name of the File Format
 Is Masked                   | TRUE/FALSE | TRUE = specified column is masked. FALSE= Not Masked
 ID Method                   | Auto/User  | User = Domain/Algorithm is locked. Auto = profiler can change
 Row Type                    | 'All Row'  | Normally not used - default 'All Row'
 Date Format                 | As in UI   | The assigned Date Format for specified column
 Notes*                      | As in UI   | To record notes
 Multi-Column Logical Field* | As in UI   | Used with Multi-Column algorithm
 Group Number              * | As in UI   | Used with Multi-Column algorithm
 SQL Type Code           *** | Info only  | A numeric code indicating SQL Type (used with Doc Store) 
-----------------------------+------------+-------------------------------------------------------------

Note 1 ---------------------------------------------------------------------------------
 * From version 6.0.9.0   ** From version 6.0.17.0    *** From version 10.0.0.0
----------------------------------------------------------------------------------------

Import Description

CSV format as of 10.0.0.0.

-----------------------------+------------+----------+----------------------------------
 CSV Field                   | Value      | Import   | Import comment
-----------------------------+------------+----------+----------------------------------
 Environment Name            | As in UI   | Req      | Need to match existing
 Rule Set                    | As in UI   | Req      | Need to match existing
 Table Name                  | As in UI   | Req/Mult | Need to match existing
 Type                        | -          | -        | Normally not used
 Parent Column Name          | -          | -        | Normally not used
 Column Name                 | As in UI   | Req/Mult | Need to match existing
-----------------------------+------------+----------+----------------------------------
 Data Type                   | -          | -        | Information Only
-----------------------------+------------+----------+----------------------------------
 Domain                      | As in UI   | Optional | Mutual Dependency #1
 Algorithm                   | As in UI   | Optional | Mutual Dependency #1
 Document Store Type      ** | As in UI   | Optional | Set by import
 File Format              ** | As in UI   | Optional | Set by import
 Is Masked                   | TRUE/FALSE | Optional | Mutual Dependency #1
-----------------------------+------------+----------+----------------------------------
 ID Method                   | Auto/User  | Optional | Set by import
 Row Type                    | 'All Row'  | Optional | Set by import
 Date Format                 | As in UI   | Optional | Set by import
 Notes*                      | As in UI   | Optional | Set by import
 Multi-Column Logical Field* | As in UI   | Optional | Set by import
 Group Number*               | As in UI   | Optional | Set by import
-----------------------------+------------+----------+----------------------------------
 SQL Type Code           *** | -          | -        | Information Only
-----------------------------+------------+----------+----------------------------------

Note 1 ---------------------------------------------------------------------------------
 #1  Mutual Dependency - All 3 fields must be specified when any of them are set.

Note 2 ---------------------------------------------------------------------------------
 * From version 6.0.9.0   ** From version 6.0.17.0    *** From version 10.0.0.0

Note 3 ---------------------------------------------------------------------------------
 "Req"      - Filed is required
 "Mult"     - Multiple objects can be updated with one CSV file
 "Optional" - Only rows that modify the Inventory are needed. All other can be excluded
----------------------------------------------------------------------------------------

Example

The example below includes the Header and one column.

Example as of version 10.

Environment Name,Rule Set,Table Name,Type,Parent Column Name,Column Name,Data Type,Domain,Algorithm,Document Store Type,File Format,Is Masked,ID Method,Row Type,Date Format,Notes,Multi-Column Logical Field,Group Number,SQL Type Code
MyEnv,MyRS,MyTable,-,-,id,int (10),-,-,-,-,FALSE,Auto,All Row,-,-,-,-,4
MyEnv,MyRS,MyTable,-,-,XML_Data,xml,-,-,XML,sample_file_2fields.xml,TRUE,User,All Row,-,-,-,-,-16

Resolution  

Navigate to the browser settings and enable popups for the masking engine page. 

Reducing import time 

The time can be improved by removing CSV entries that have not changed (usually these are fields not masked).

If the Rule Set has 100,000 columns and only 1,000 are masked - importing only 1,000 vs 100,000 will reduce the import time by a factor of 100. This will see the import taking seconds rather than minutes.

File Format Pattern or File

If the File Format Pattern or File has a comma, this can be worked around by creating (or editing) a Pattern without a comma, then Export and Import the Rule Set. The Rule Set can then be edited to include the comma.

Tip:

This works because the Inventory is assigned to the File Format (and not each individual file or pattern).

Remove rows with commas

Remove the row (or rows) with a comma from the CSV file and Import the file.  

This usually has no impact on the Inventory as only changes are needed in the CSV. If needed, manually change the Inventory. 

Masking Logs 

Errors in the import process are logged in the masking info log (some warnings are not logged).

Below are some examples related to the warning shown in the popup above.

Reading each line of the csv imported Inventory: Environment,RS,table_1,-,-,Col_1,varchar (20),FIRST_NAME,LAST NAME SL,true,Auto,All Row,-
Environment Environment does not exist

Reading each line of the csv imported Inventory: Env,RuleSet,table_1,-,-,Col_1,varchar (20),FIRST_NAME,LAST NAME SL,true,Auto,All Row,-
Rule Set RuleSet does not exist

Reading each line of the csv imported Inventory: Env,RS,table_1,-,-,Column,varchar (20),FIRST_NAME,LAST NAME SL,true,Auto,All Row,-
Env Env, Rule Set RS_BUG : Column Column does not exist

Reading each line of the csv imported Inventory: Env,RS,table_1,-,-,Col,varchar (20),FIRST_NAME,LAST NAME SL,true,Auto,All Row,XXXX@MM!dd
Input date format:XXXX@MM!dd Valid:false

Resolution 

Depending on what object is throwing the warning, please check the object on the Masking Engine UI and correct the reference to the object, the spelling, the case, or the date format. 

To get an example of the CSV file, please export a file from the UI.

Resolution 

Check the masking logs, the row, and the error. The error shows how many columns are expected and the failed row is shown above. 

From the example above:

Error: 
  * Unexpected number of fields
  * on line 19 
  * expected 13, found 9
  
Row:
  * Is      : "Environment,RuleSet,Table,-,-,Column,NUMBER (0),-,-"
  * Expected: "Environment,RuleSet,Table,-,-,Column,NUMBER (0),-,-,true,Auto,All Row,-"
    

Resolution  

The best way to resolve this issue is to export a Rule Set (Inventory), copy the header from this file, and then paste it into the failed import file.

Resolution 

The best resolution here is to be diligent in editing the CSV file. Open it in a text editor, save it to a standard ANSII character set and check for invalid characters.