Expert Consultancy from Yellow Pelican

SQLite

A site about Talend

Working with SQLite

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.

Use Cases

There are many use cases for SQLite; here, I've listed just some of the uses that I have found for it.

  • Application Databases for small and medium projects
  • Staging Databases
  • Replacement for text files
  • Auditing and Statistic databases

Components

Talend provides all of the usual Components that you would expect; including tSQLiteConnection, tSQLiteInput, tSQLiteOutput and tSQLiteCommit.

Test Data

Create your own test data or use the data supplied below by copying it to a file named person.csv.

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


A Simple Job

Let's see how simple SQLite is to use by creating a trivial Job that reads a Comma-separated values (CSV) file and loads the data in to a new SQLite table.

Create a new Job and add the following components - tSQLiteConnection, tFileInputDelimited, tSQLiteOutput and tSQLiteCommit. Connect these components as shown in the screenshot below.

Image 1


Component Configuration

We'll now configure the components of this new Job.

tSQLiteConnection

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, /data/SQLiteExample.sqlite.

Image 2


tFileInputDelimited

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.

Image 3


You'll also need to configure the Schema as shown in the following screenshot. You can propagate these changes to the component tSQLiteOutput.

Image 4


tSQLiteOutput

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.

Image 5


Seeing the Results

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.

Image 6


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.

Image 7


Conclusion

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.




Expert Consultancy from Yellow Pelican
comments powered by Disqus

© www.TalendByExample.com