This article shows you how to install SQLite, a relational database management system, and how to create an SQLite database and integrate it with Talend. It shows you how to use Talend components to connect to, insert, and retrieve data from SQLite database tables without complex coding.
Installing the SQLite database
Download the sqlite-tools-win32-x86-3320200.zip file, located under the Precompiled Binaries for Windows heading, from the SQLite Download Page.
Create a folder, in this case, C:\sqlite. Extract the sqlite-tools-win32-x86-3320200.zip file into that folder.
Open a command prompt, then run the .\sqlite3.exe database1.db command to create a database called database1.db.
Check your folder and make sure the database1.db file exists.
Login to the database1.db SQLite database and verify that the login is successful.
Integrating Talend Studio with SQLite database
Launch Talend Studio.
In the Repository, expand Metadata, right-click Db Connections, select Create Connection, and create the SQLite connection. Click Next.
Select the Browse button and navigate to the database1.db file. Click the Test connection button, and a pop-up dialog box shows you that the connection is successful. Click OK, then click Finish.
Create a test Job.
Drag and drop the SQLite connection from Metadata onto the designer workspace of your Job. When the Components window opens, select the tSQLiteConnection. Click OK.
Add a tRowGenerator component. Then, using an OnSubjobOk trigger, link it to the tSQLiteConnection component to generate data, and insert it into the SQLite database table.
Add a tSQLiteOutput component to the Job.
Link the tRowGenerator to the tSQLiteOutput component using a Main row.
Double-click the tSQLiteOutput component, and in the Component view, select the Use an existing connection check box, then select tDBConnection_1-SQLite from the Component List drop-down menu.
Enter the table name, in this case, "employee", to insert data.
Click Sync columns to sync the columns from the tRowGenerator schema to the tSQLiteOutput schema.
Add a tSQLiteInput component to retrieve data from the table, link it using an OnSubjobOk trigger. Configure the component, as shown below:
Click the Use an existing connection check box.
Select tDBConnection_1-SQLite, from the Component List drop-down menu, .
Configure the schema.
Enter the table name, in this case, "employee".
From the Query Type pull-down number, select Built-in and fill in the Query field.
Add a tLogRow component to the Job. Link the tSQLiteInput to the tLogRow to display data in the console.
Run the Job and review the output.
Test retrieving the schema from the SQLite database by right-clicking the SQLite connection in Metadata, and selecting Retrieve Schema to open the Schema window. Click Next.
Choose the schema you want to retrieve from the database. In this case, there is only one table schema, EMPLOYEE. Click Next.
The schema for the EMPLOYEE table opens. Click Finish.
Notice that the schema for the EMPLOYEE table and columns is listed under Metadata.