Transforming Data with Kettle
These are preliminary notes.
Contents
Setting up Kettle
Setting up jdbc.properties
If you want to design transformation with Kettle, you have first to setup your databases connections. To do this you have to edit the file simple-jndi/jdbc.properties in the Kettle installation directory. (On OS X it was Kettle.app/Contents/Resources/simple-jndi/jdbc.properties) It should define connections to your operational ERP (opentaps) database and to your opentaps analytics database. Usually, you can copy over the file config/jdbc.properties from opentaps analytics. Here is an example:
operational/type=javax.sql.DataSource operational/driver=com.mysql.jdbc.Driver operational/url=jdbc:mysql://127.0.0.1/opentaps_testing operational/user=opentaps operational/password=opentaps analytics/type=javax.sql.DataSource analytics/driver=com.mysql.jdbc.Driver analytics/url=jdbc:mysql://localhost/analytics_1_7 analytics/user=opentaps analytics/password=opentaps analytics_testing/type=javax.sql.DataSource analytics_testing/driver=com.mysql.jdbc.Driver analytics_testing/url=jdbc:mysql://localhost/opentaps_analytics_testing analytics_testing/user=operational analytics_testing/password=operational
Configuring Databases
When you first start Spoon, you will need to set up your databases. Click on the [New] next to the "Repository" input, and then click on "New" to set up a database connection. You would need to set up to database connections, one for analytics and one for the operational database. Select the type of the database, set JNDI as the method of access, and then entered the database name as it is described in your jdbc.properties file above:
Then, to start Spoon, click on the [No Repository] button. You do not need a repository database because your transformations are stored in .ktr files in the opentaps analytics application, not in a kettle transformations database.
Designing Transformation and Job Templates
To create our date dimension seed data using Spoon, the steps are,
- Create a New Transformation using New -> New Transformation
- On the left, you'll see three collapsed sections, Main Tree, Core Objects and Favorite Steps. Expand the Main Tree section.
- Under Transformations right click Database Connections and select New
- Specify the connection information to opentaps_analytics, our data warehouse. Also ensure characterEncoding of UTF8 in options as above.
- Test the connection and ensure it works
- Under Transformations right click Database Connections and select New
You can use templates/opentaps/DateDimensionSeedTemplate.ktr as a starting point. Import it into Spoon, change the database configuration if necessary, and edit the template.
Editing a Transformation
To edit an existing transformation in Spoon, use File > Import from an XML file, and import the file (for example, StoreDimensionTemplate.ktr from analytics/templates/ directory.) When you are done, use File > Export to an XML file to save the transformation as an XML file again, instead of storing it in the database.
Debuging a Transformation
If you have your databases configured correctly, you can run your transformation in Spoon to debug the results. You can click on the [Run] button at the top of the menu, watch the results, which will show you how many rows of data were read and written. This will help you identify any steps that may have failed. If a particular step has encountered an error, it will be highlighted in red. You can click on this step and then click on show logs to see the log file of the errors.
You can also use the [Preview] button, which will let you select steps in the transformation and see the rows of data after a particular step has been executed.