Auditing

SmartLoader provides auditing capabilities where audit records are loaded automatically into a pre-defined audit table in the database where data is being loaded. This is in addition to the log files that are produced in the Log folder.

Turning On Audit Records

If the source file contains audit_sid column then an audit record will be written. SmartLoader will detect the audit_sid column if:

  • audit_sid column position is provided by -auditSidColumnNumber option. The first column has index 0

  • -addAuditSid is set so audit_sid column will be added during data loading into the database

If the source file does not contain and uadit_sid column, turning on auditing logging into the database requires the inclusion of a new field definition in the config file named audit_sid as shown below. This new field should be listed first in the config file.

- !field
   name: audit_sid
   generated: true
   size: 21
   type: Long

Upon making the above changes to the config file, SmartLoader will create a dim_audit table in the defined database that has the following structure:

audit_sid       BIGINT NOT NULL                     Unique number like a primary key per file per loading session
time_created    DATETIME DEFAULT CURRENT_TIMESTAMP  Time when the file was uploaded
date_sid        INTEGER NOT NULL                    Date when file was uploaded - YYYMMDD
process_id      VARCHAR(100) NOT NULL               String that is specified in the -processId property in the configuration file
status          INTEGER DEFAULT 1                   1: successful file upload - 0: File was re-uploaded (See Note below)
integer_col_1   BIGINT                              Time required to load the data to the database
integer_col_2   BIGINT                              Count of the data rows that were read from file
integer_col_3   BIGINT                              Count of the data rows that were rejected by the database
integer_col_4   BIGINT                              Count of the data rows that were loaded successfully into the database
integer_col_5   BIGINT                              Not set by SmartLoader
integer_col_6   BIGINT                              Count of the data rows filtered by filterCurrentRow()
integer_col_7   BIGINT                              File size
integer_col_8   BIGINT                              Size of the unpacked data
integer_col_9   BIGINT                              Count of the data rows that were rejected by SmartLoader
varchar_col_1   VARCHAR(1000)                       File name
varchar_col_2   VARCHAR(1000)                       Table name
varchar_col_3   VARCHAR(1000)                       Not set by SmartLoader
varchar_col_4   VARCHAR(1000)                       Build version of SmartLoader
varchar_col_5   VARCHAR(1000)                       Not set by SmartLoader
varchar_col_6   VARCHAR(1000)                       Not set by SmartLoader
varchar_col_7   VARCHAR(1000)                       Not set by SmartLoader
varchar_col_8   VARCHAR(1000)                       Not set by SmartLoader
varchar_col_9   VARCHAR(1000)                       Not set by SmartLoader

Note: if a file is re-uploaded, the status value of the initial upload is set to 0.

The destination table should contain the field audit_sid, otherwise, one of the following conditions will take place:

  • If the createTable property is set to True, SmartLoader will create a new table that includes the audit_sid field. Data migration from the previous table will have to be done manually.

  • If the createTable property is set to False, SmartLoader will throw an error since it could not match the fields in the destination table with the list of fields defined in the config file.