SQLite JDBC and Kettle (Pentaho Data Integration) ETL

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.

Merry Xmas.

UPDATE:

SQLite now supported by Kettle and bug in driver for JRE 1.5.0_10.

Advertisements

21 responses to “SQLite JDBC and Kettle (Pentaho Data Integration) ETL

  1. Hi tom,

    Palo teams successfully tested plugin which load and extract data from Palo cubes (yes it’s true 🙂 ).
    They make it available for download at release 1.5 delivery (i think january)

    Rgds

    Samatar

  2. I forgot to say that it’s a Kettle Plugin

  3. That’s good to hear Santa, I mean Samatar 😉

    Merry Xmas
    Tom

  4. SQLite support is in the latest Kettle-2.4.0 dev drop.

  5. Thanks Matt. I’ll check it out in the next few days.

  6. Pingback: Ruby and SQLite. A micro ETL environment. « Gobán Saor

  7. hi,
    i want know how to intigrate different source into one source.
    like

    sales table

    sales_qty table
    qty table

  8. i am using kettle

  9. Pradeep.

    I’m not sure I understand the question or to be more precise the problem, integrating and merging data is what an ETL tool does. If you have a specific problem the Kettle forum is the place to raise it. You will find them a very helpful bunch but you most give sufficient details in your question to enable others to help you. And questions like “how do I use an ETL tool” are probably too general and are unlikely to raise a response.

    Tom

  10. Pingback: 10,000 hits … « Gobán Saor

  11. hola por favor si me pueden ayudar no me puedo conectar con la base de datos en anywhere tengo la base en versión 6.0 la versión de kettle es 3.1 con mysql no tuve problema

  12. hello, please help can not connect with the database anywhere verssion 6.0

  13. Hola Martha,

    La versión 6 de qué?

  14. how to connect J2ME and SQLite ?

  15. thank you so much

  16. @Tsolmon

    Have you had any luck getting SQLite to work with J2ME?

  17. is there any way to work with SQLite and J2ME??? Please help me……..

  18. hi every one
    i read this article but i can not use it

    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.
    ???????!!!!!!!!!!!!
    how i can do that??
    other qustion
    witch version of jdbc driver and kettle spoon shoult to use???
    i have kettle ver 3. and jdbc driver that i donot know how use them

    note : iam very beginer
    guid me step by setp