Delete DB

Workshop - Delete DB

Sometimes you might have to delete data from a table. If the operation to do it is simple, for example:

DELETE FROM ORDERS_TABLE WHERE STATUS='Shipped'

Or

DELETE FROM TMP_TABLE

You could simply execute it by using an SQL job entry or an Execute SQL script step. If you face the second of the above situations, you can even use a Truncate table job entry.

For more complex scenarios, you should use the Delete step.

Steel Wheels are launching a campaign, focusing on Customers who have ordered more than 50 of each of their various Productlines.

Delete

---

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

1. Inspect data

Before we kick off .. lets take a look at the stg_sales_data table data to get an understanding of what results to expect..

1. View the STG_SALES_DATA data.

STG_SALES_DATA

As you can see we have a QUANTITYORDERED for each of our PRODUCTLINES. Each ORDERNUMBER is associated with Customer details.

1. Execute the following statement.

select * from STG_SALES_DATA
where QUANTITYORDERED > '50';

STG_SALES_DATA constraint QUANTITYORDERED > 50

The results indicate that ORDERNUMBER 10339 where the QUANTITYORDERED is 55 should be the first expected record.

2. 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.

1. Drag the CSV File Input step onto the canvas.

2. Open the CSV File Input properties dialog box.

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

CSV File input - PRODUCTLINE list

3. Parameters

Parameters are associated with local variables in the transformation or job.

1. Double-click on the canvas and select the Parameter tab.

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

Set parameters

4. Get Variables

This step allows you to get the value of a variable. This step can return rows or add values to input rows.

You must specify the complete variable specification in the format ${variable} or %%variable%% (as described in Variables) . That means you can also enter complete strings in the variable column, not just a variable.

For example, you can specify: ${java.io.tmpdir}/kettle/tempfile.txt and it will be expanded to /tmp/kettle/tempfile.txt on Unix-like systems. To convert the Variable into a data type other than String use Select Values - Meta Data tab. To get system values, including command line arguments, use the Get System Info step.

1. Drag the Get variables step onto the canvas.

2. Open the Get variables properties dialog box.

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

Get variables

5. Delete

Be careful when using the Pentaho Comparators ..!

Ensure you have backed up everything before executing as this action is a destructive change.

1. Drag the Delete step onto the canvas.

2. Open the Delete properties dialog box.

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

The Pentaho comparators are the opposite to SQL..!!

Delete step

• The value of the QUANTITYORDERED is set: greater / equal to the min_quantityordered.

• PRODUCTLINE values mapped.

• Delete records from the STG_SALES_DATA table.

6. RUN

Check the results in the database table.

The Pentaho Comparators may result in unexpected behaviour.

1. Run the Transformation.

2. In your DB managment tool View the STG_SALES_DATA table.

STG_SALES_DATA