Load Step

Load step under Query Object loads the incoming data (after extraction and transformation) into a target system (File, Relational database, HDFS, No SQL / Columnar system etc.).

You need to select a target Intellicus connection (already created under Configure > Databases) on which data should be loaded. One of the important configurations is the deselecting of the ‘Read Only’ setting of the target system. If not de-selected, this will not show in the list of target system where the extracted data can be loaded.

There are target connection-specific properties required to load data into the system.

On Opening the Target Connection, you will see the option “Default.” When you select the default connection, it will correspond to the default connection that the user selected on the configure page. In such case, the Load function will work for the default connection.

You can fetch list of existing Tables/Files from the connection.

Loading into File/RDBMS

Figure 1: Loading into RDBMS

The properties of Load step (in case target system is a file system) are:

PropertyValuesCommentsTarget ConnectionSelect from listSelect a target connection on which data should be loadedTable/File Properties

 

 

Target Table/File NameSelect from listSelect target table/file name using the selected Target Connection.

 

Type in the table/file name if it does not exist. The table/file gets created under the selected target connection

If Table/File ExistsSelect from list:

 

 

Append Data

 

Delete Existing Data

 

Skip Load

 

Drop and Create New Table/Delete and Create New File

 

Truncate Existing Data

Action to be performed in case table/file exists:

 

 

To append data in the end

 

To delete data in existing table

 

To not perform any action on table/file and skip load step

 

To drop the table and create new table /file with same name and new schema.

 

 

To delete data in existing table, but Admin user can roll back this data

EncodingSelect from listSelect the file encoding typeCompressionSelect from listSelect the compression type of network dataSuffix TimeStampCheck/UncheckCheck= suffix timestamp in the chosen format to table/file

 

 

Select from the available list

Data PropertiesIf Record ExistsSelect from list:

 

 

Insert Only

 

Update Only

 

 

Update or Insert

Action to be performed in case record exists:

 

 

Insert the new record

 

Update the existing record with the new

 

In case of an existing record, update it with the new record

Else insert the new record

Ignore Empty RowsCheck/UncheckCheck= ignores empty rows in table while loadingRow PropertiesLine SeparatorType YourselfSpecify the separator that is used to separate rows in the fileSplit After N RowsType YourselfSpecify the maximum number of rows to split the fileInclude HeaderCheck/UncheckCheck to include headers specified in the fileIgnore Empty RowsCheck/UncheckCheck to ignore empty rows specified in the fileColumn PropertiesFetch ColumnsClick the optionFetches updated columns of the existing table

 

You can see the field details of the data to be loaded under Column Properties section

Remove All ColumnsClick the optionRemoves all columns in the target tableEnclosureType YourselfSpecify the enclosure if the file contains enclosure within the fieldsSeparatorType YourselfSpecify the field separator for the fileError HandlingError ThresholdType YourselfSpecify count of error after which loading process should stop.

 

Any positive number>0 means stop processing after that many +1 errors i.e. if its value is 2, stop processing upon 3^rd error.

Loading into NOSQL (HBASE)

The properties of Load step (in case target system is an HBASE system) are:

PropertyValuesCommentsTarget ConnectionSelect from listSelect a target connection on which data should be loadedTable PropertiesTarget Table NameSelect from listSelect target table name using the selected Target ConnectionIf Table ExistsSelect from list:

 

 

Append Data

 

Skip Load

 

 

 

Drop and Create New Table

Action to be performed in case table exists:

 

 

To append data in the end

 

To not perform any action on table and skip load step

 

 

To drop the table and create new table with same name and new schema

Suffix TimeStampCheck/UncheckCheck= suffix timestamp in the chosen format to table.

 

 

Select from the available list

Row PropertiesRow KeySelect from list of available fieldsUnique identifier/field for each rowDataTypeSelect from listData Type of the Row KeyError HandlingError ThresholdType YourselfSpecify count of error after which loading process should stop.

 

Any positive number>0 means stop processing after that many +1 errors i.e. if its value is 2, stop processing upon 3rd error

Column PropertiesFetch Column FamiliesClick the optionFetches updated column families of the existing table.

 

 

You can see the field details of the data to be loaded under Column Properties section

Remove All ColumnsClick the optionRemoves all columns in the target tableLoading into HDFS

The properties of Load step (in case target system is HDFS) are:

PropertyValuesCommentsTarget ConnectionSelect from listSelect a target connection on which data should be loadedFile Properties

 

 

 

Target File NameSelect from listSelect target file name using the selected Target Connection.

 

 

You should have Read/Write/Execute access permissions on the target directory to select target file from

If File ExistsSelect from list:

 

 

Append Data

 

Skip Load

 

 

Delete and Create New File

Action to be performed in case file exists:

 

 

To append data in the end

 

To not perform any action on file and skip load step

 

To delete existing file and create a new file with the same name

EncodingSelect from listOption to encode the file in available formats (ASCII, ISO-8859-1, UTF-8)CompressionSelect from listOption to create the file in compressed format (zip & gzip)Suffix TimeStampCheck/UncheckCheck= suffix timestamp in the chosen format to file.

 

Select from the available list

Row PropertiesLine SeparatorType YourselfSpecify a separator to distinguish rows/recordsSplit After N RowsType YourselfOption to split the output into multiple files.

 

Splitting will be performed on the basis of number of records. Blank/0 means no splitting. Any positive number means split the output after that many records

Include HeaderCheck/UncheckCheck= includes header from file/table.

 

This option will be applicable only for new file

Ignore Empty RowsCheck/UncheckCheck= ignores empty rows in table while loadingColumn PropertiesEnclosureType YourselfSpecify enclosure for column valuesSeparatorType YourselfSpecify the column separatorError HandlingError ThresholdType YourselfSpecify count of error after which loading process should stop.

 

Any positive number>0 means stop processing after that many +1 errors i.e. if its value is 2, stop processing upon 3rd error

You can run and schedule Query Objects which are of Load type from under Explorer.
Running a Query Object will execute the transformation and load the data into target system. You can also schedule the query object in once or recurring mode.

Load step can also be followed by the Data Source step further followed by Format step to be used for generating reports.

Note: Every time the Load step fails, a CSV file (<TargetTableName>_failed_records.csv) gets created with the failed records at <intellicus installation path>/Intellicus/reportengine/bin
The location of CSV file is governed by report server property:”LOAD_STEP_FAILS_DUMP” in ReportEngine_Default.properties (under <intellicus installation path>/Intellicus/reportengine/config) .