Create DB

Create tables ..

If you work with databases, one of the main objectives will be to extract, load and transform your data. Steel Wheels has several data sources that require loading into a database to discover, cleanse, conform, enrich and validate the data for reports.

In this guided Lab, you will:

• Connect to a database

• Create a database table

• Configure: Table Input

Create databases

To create a new transformation

1. In Spoon, click File > New > Transformation.

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

• By clicking New, then Transformation

• By using the CTRL-N hot key

Workflow 1 - Sales Data

Load sales data from a csv file into: STG_SALES_DATA table.

Modify the VARCHAR to prevent the value being truncated.

Load sales data

Follow the steps outlined below:

1.1 CSV File Input

This step provides the ability to read data from a delimited file. The CSV label for this step is a misnomer because you can define whatever separator you want to use, such as pipes, tabs, and semicolons; you are not constrained to using commas. Internal processing allows this step to process data quickly. Options for this step are a subset of the Text File Input step.

This step has fewer overall options than the general Text File Input step, but it has a few advantages over it: NIO

Native system calls for reading the file means faster performance, but it is limited to only local files currently. No VFS support.

Parallel running

If you configure this step to run in multiple copies or in clustered mode, and you enable parallel running, each copy will read a separate block of a single file allowing you to distribute the file reading to several threads or even several slave nodes in a clustered transformation.

Lazy conversion

If you will be reading many fields from the file and many of those fields will not be manipulate, but merely passed through the transformation to land in some other text file or a database, lazy conversion can prevent Kettle from performing unnecessary work on those fields such as converting them into objects such as strings, dates, or numbers.

1. Drag the CSV file input step onto the canvas.

2. Open the CSV file input properties dialog box.

Stepname	csvi-sales_data
Filename	${Internal.Entry.Current.Directory}\sales_data.csv
Delimiter	, (comma)
Lazy Conversion	unchecked
Header row present	checked

The CSV File input parses a sample number of records to set the metadata properties. As the Varchar is being mapped to a database table then you need to be careful to avoid truncation.

3. Ensure the following details are configured, as outlined below:

CSV file input

3. Click on the Get Fields button.

4. Click OK.

1.2 Table Input

The Table Output step allows you to load data into a database table. Table Output is equivalent to the DML operator INSERT. This step provides configuration options for target table and a lot of housekeeping and/or performance-related options such as Commit Size and Use batch update for inserts.

1. Drag the Table Output step onto the canvas.

2. Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:

Table output - options

3. Click on the Database fields.

4. Click on the ‘Get Fields’ button.

Table output - fields

Ensure the mappings bewteen Table fields and Stream fields are correct.

5. Click on the SQL button.

SQL editor

5. Click Execute.

6. Click OK to Close all windows.

1.3 RUN

1. Click the Run button in the Canvas Toolbar.

2. Check that the table has been created in the sampledata database.

STG_SALES_DATA

Workflow 2 - Orders

Load orders data from a csv file into: STG_ORDERS_MERGED table.

Modify the VARCHAR to prevent the value being truncated.

Load orders data

Follow the steps outlined below:

2.1 CSV File Input

1. Drag the CSV file input step onto the canvas.

2. Open the CSV file input properties dialog box.

Ensure the following details are configured, as outlined below:

Add path to orders.txt

Set Content

Get Fields

2.2 Table Output

1. Drag the Table Output step onto the canvas.

2. Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:

Table output - options

3. Click on the Database fields.

4. Click on the ‘Get Fields’ button.

Table output -fields

5. Click on the SQL button.

SQL editor

6. Click Execute.

7. Click OK to Close all windows.

2.3 RUN

1. Click the Run button in the Canvas Toolbar.

2. Check that the table has been created in the SampleData database.

STG_ORDERS_MERGED