Using the Data Source Explorer for Extract, Transform, and Load Operations

The Edit submenu option opens the first 500 records of the file in editor mode. Values can be changed (depending on the permissions in the underlying databases) by selecting and then right-clicking a column. A context menu opens, as shown in the following image.

A cell (row/column) combination can be edited by clicking into that cell and changing the value. Select Revert if you want to remove (undo) the change that was made or Save to save the changed data in the database.

The Sample Contents submenu option shows the first 50 records of the table in a scrollable result tab in the lower console pane of the iBDI window. View the column contents from this read only list.

The Extract submenu option should be used with care. This option extracts all of the records of the selected table from the Source system (Hive or RDBMS) into iBDI in record delimited format. There are no record limit numbers or limiting clauses. This is a strict data dump.

When the Extract submenu option is selected, the Extract Data dialog opens, as shown in the following image.

Perform the following steps:

1. In the Output file field, type or browse to the output folder and file.
2. From the Column delimiter drop-down list, select Comma, Semicolon, Space, Tab, or the vertical bar character (|).
3. From the Character string delimiter drop-down list, select double quote ("), single quote ('), or None.
4. Click Finish.

   The extraction process begins to the output file that is specified.

   When the data extract finishes, a message is displayed in the console window indicating the number of records that were written. For example:

   Extracting `customers`...
   Data extraction was successful.
   12435 row(s) extracted.

The Load submenu option requires that a table already exist in the database with the specified name and that the columns in the table match the format of the columns in the incoming data.

Right-click the table in the Data Source Explorer for data loading, select Data from the context menu, and then click Load.

The Load Data dialog opens, as shown in the following image.

Perform the following steps:

1. In the Input file field, type or browse to the input folder and file.
2. From the Column delimiter drop-down list, select Comma, Semicolon, Space, Tab, or the vertical bar character (|).
3. From the Character string delimiter drop-down list, select double quote ("), single quote ('), or None.
4. Select or deselect the Replace existing data check box according to your requirements.
5. Click Finish.

   The data load process begins from the file location to the selected database schema and table name.

   When the data finishes uploading from iBDI to the destination database, a message is displayed in the console window. For example:

   Loading "customerxxx"...
   Data loading was successful.
   12435 row(s) loaded.

If the Replace submenu option is selected, then a delete will be issued against the table for the current contents. If Replace is not selected, then the data will attempt to be loaded as is. If there are duplicates in the data, then the underlying database validation will generate an exception and stop the job if constraints exists against duplicates. iBDI will display a message and the underlying database exception, as shown in the following example:

Loading "customerxxx"...
Data loading was successful.
0 row(s) loaded.
12435 row(s) could not be loaded.
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 
Duplicate entry '12435' for key 'PRIMARY'