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.
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
I forgot to say that it’s a Kettle Plugin
That’s good to hear Santa, I mean Samatar
Merry Xmas
Tom
SQLite support is in the latest Kettle-2.4.0 dev drop.
Thanks Matt. I’ll check it out in the next few days.
[...] 28th, 2006 In a previous post I mentioned that I use a combination of Ruby and SQLite as a micro-ETL environment. Many of you [...]
hi,
i want know how to intigrate different source into one source.
like
sales table
sales_qty table
qty table
i am using kettle
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
[...] start. I started the blog in February 2006 as a destination for my del.icio.us feed auto-posts, but my first real post wasn’t until December 2006. Mind you, my 11 year old son, who only started blogging this [...]
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
hello, please help can not connect with the database anywhere verssion 6.0
Hola Martha,
La versión 6 de qué?
how to connect J2ME and SQLite ?
@Tsolmon
Short answer, I don’t know, but see http://www.mail-archive.com/sqlitejdbc@googlegroups.com/msg00052.html
Tom
thank you so much
@Tsolmon
Have you had any luck getting SQLite to work with J2ME?
is there any way to work with SQLite and J2ME??? Please help me……..
@nazmul
As I’ve said before, I don’t know, see http://www.mail-archive.com/search?l=sqlitejdbc@googlegroups.com&q=j2me
Have you considered using JavaDB (aka Derby) http://developers.sun.com/javadb/index.jsp
..if xfer of information between SQLite and JavaDB is required use CSV files, JavaDB handles CSVs as a first-order datasources.
Tom
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
@Amir_60
No need to do any of the above, SQLite now comes as standard in Kettle (PDI).
Tom