Handling Rollback and Database transactions in Pentaho Data Integration
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
While inserting the data into the database table it often happens that when a job/transformation fails in between the data load the whole transaction gets rolled back.
In general, the table output step in PDI will insert and commit any number of records until there is an error. Consider an example where the ETL process processes 5000 records and the 3999 record has a string instead of an integer (eg. the DDL defines the field as Integer), the job/transformation will come to a sudden halt.
Now the problem is though, that we have 3,999 records in the DB table from this current ETL run.In some situations this might not be an issue, e.g. if you have a CDC process set up which can deal with this (e.g. it can just start off the highest id or timestamp), but in other scenarios, this situation might be a problem.
Pentaho Data Integration provides a transactional feature, which is really quite easy to use:Make sure your DB engine supports transactions! E.g. MySQL MyISAM does not support transactions. So the transformation will run and insert records even though there was an error.
Transactions in transformations: simply by enabling the “Make the transformation database transactional” option in the “Miscellaneous” tab of the transformation settings dialog.
Note: database transactions will not work in the following cases:
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
- This will disable the Use batch update for insert option in the Table Output step also ignore the Commit Size setting. So effectively this will slow down the insert operation.
- if you use database partitioning and multiple connections per step copy need to be created. Performance wise it doesn’t make any sense in that case anyway.
Best Open Source Business Intelligence Software Helical Insight is Here
