I’ve been a big fan of SQLite for several years now. Although I come from an Oracle database background, I find for day-to-day data smith’ing SQLite is ideal. Combine it with the expressive power of Ruby and you have a very powerful micro-ETL environment.
I’m also a big fan of Kettle ETL (now part of the growing Pentaho open-source BI family), but Kettle doesn’t offer SQLite as an one of its supported database options. It does however, offer support for access to any database via a Generic Driver connection option.
Problem, SQLite is a C implementation and doesn’t come with a JDBC driver as standard, but there are several 3rd party Java wrappers available . I decided on SQLiteJDBC from David Crawshaw . Why this one? Open source (goes without saying), simple clear example of how to use it, pure JAVA option, and an active community. If anybody has experience of using any of the others, let me know.
How to integrate SQLite JDBC with Kettle:
- Download Pure Java version from http://www.zentus.com/sqlitejdbc/. There’s also a Windows and a MacOS X JNI based version but unless speed is a limiting factor the JAVA only version has fewer moving parts (i.e. no need to put a DLL on the system PATH) and should also work under Linux.
- Move the JAR to the KETTLE libext directory (this is where the other JDBC drivers hang out, but anywhere on the CLASSPATH will do).
- Ensure the JAR file is on the CLASSPATH. Under WindowsXP I added “set CLASSPATH=%CLASSPATH%;libext\sqlitejdbc-v030-nested.jar” to the SPOON.BAT file.
Connecting to a SQLite database within Kettle.
- Within SPOON, make a new connection using for example a Table Input step.
- In the General tab, pick “Generic database” from the list of connection types.
- Pick “Native(JDBC)” from the Method of Access list.
- No further information required on this tab, go to the Generic tab.
- In the URL field, enter “jdbc:sqlite:c:\mydata\mydatabase.db” where c:\mydata\mydatabase.db is the SQLite database you wish to connect to.
- In the Driver class field, enter “org.sqlite.JDBC”.
That’s it, you should be able to read and write to SQLite database files within Kettle.
Now, if only Kettle could load and extract from my PALO cubes, I’d be a happy man; maybe I’ll ask Santa.