A site about Talend
SQLite is arguably the most widely used database. In this article, we'll look at its simplicity of use and how it may be used in your day-to-day Talend development.
SQLite is an Embedded Database that is included with Talend and works straight from the box. SQLite comes with all of the usual features that you'd expect from a fully functional RDBMS and I find it an invaluable tool for many aspects of building Talend Jobs. There are some cases where SQLite is not appropriate; however, I find it extremely useful for many of my Data Integration tasks. A quick read of When to use SQLite should help you to decide if it meets your needs.
There are many use cases for SQLite; here, I've listed just some of the uses that I have found for it.
Create your own test data or use the data supplied below by copying it to a file named
Id,Gender,Title,FirstName,LastName 1,Male,Mr,Elliot,Jackson 2,Female,Miss,Molly,Jackson 3,Male,Mr,Jack,Mcdonald 4,Male,Mr,Jacob,Brown 5,Male,Mr,Nathan,Turner 6,Female,Miss,Georgia,Jones 7,Female,Mrs,Annabelle,Cook 8,Male,Mr,Harry,Barnes 9,Female,Mrs,Sienna,Holmes 10,Male,Mr,Alexander,Lloyd
We'll now configure the components of this new Job.
In this example, our configuration of tSQLiteConnection is very straightforward. We'll simply define a path to a regular File system file that will host our new database, for example,
We'll now configure our tFileInputDelimited component so that it will read the input file that we created earlier. If you have not already created this, refer to the section on Test Data, above.
Set File name/Stream to the path of your input file, for example,
/data/person.csv. Set Field Separator to
"," and Header to
1 as shown below.
Finally, we'll configure tSQLiteOutput. We're going to use the connection that we've already established, so select the checkbox Use an existing connection. We need to specify the database table name that we will be using and this can be done by setting Table to
PERSON or a name of your own choosing.
The Action on table field allows us to specify the actions that should be performed against the table when processing your data. For the purpose of this tutorial, set the value to
Create table if does not exist. The completed settings are shown below.
Now that you've completed the Job, you can see that SQLite requires minimal configuration. You can now run your Job and take a look at the results.
There are a number of tools available for managing an SQLite database. I use an excellent Firefox extension called SQLite Manager. For the purposes of this tutorial, I'm going to use SQLite Manager to open our database and select all data from the new
PERSON table, as shown below.
SQLite is a powerful yet simple to use embedded database. I have created some very large tables using SQLite and it has proven to be both robust and fast. There are many ways that you can make use of SQLite within your Talend development. In future articles, we'll look at some of these in more depth.comments powered by Disqus