Reading from and Writing to a Database Table – Talend

Use a DataBase through Map

The database support allows you to read and write from databases using a map. To use the database support, you must first import the database using the Import mechanism (File -> Import) in the studio. This reads all of the tables of the database into a set of structures, one for each table. Each database structure has a loop (with an element called Row) that corresponds to each selected or written row.

To READ FROM THE DATABASE, use the structure corresponding to the table as the input structure of a map. If you are reading from multiple tables using a database join , you can create a new structure representing the joined tables using the New Structure wizard (select the create a database join table option).

If you would like to Enrich your career with a Talend certified professional, then visit Mindmajix - A Global online training platform: “Talend Course” . This course will help you to achieve excellence in this domain.

When creating the map, you can specify a SQL select statement using a DatabaseSelect function. You can also use DatabaseJoin function(s) to specify conditions on join(s) as required. All database functions are placed in the IO/Database expression tab.

To write to a database, use a structure corresponding to the table as the output structure of a map. Use a DatabaseInsert or DatabaseUpdate function as required.

--------           Related Page: Talend Database Tutorial           --------

You can read from or write to any number of database tables in a single map by creating an enclosing structure that inherits from the desired database table structures.Lookup functions are also provided to find and update values from database tables.

Database Structures-Tables

When a database is imported, two structures are created for each table in the following categories (each represented by a folder):

• Tables – The structure which contains a loop representing the rows of the table. You use this structure directly in a map. You can also open this structure in the structure editor and view the database data using the Show Document button or Show Sample menu item.If you are reading from or writing to the database, this is the one to use. When reading, use the DatabaseSelect function in the Row element. When writing, use either the DatabaseInsert or DatabaseUpdate function in the Row element.
• Single Row Tables – This is the structure which defines the columns of the table. The Table structure inherits from this one. In addition, each of these structures has a Database representation which defines the properties associated with that table, like primary key and automatic ID generation. Finally, this is also used to construct joined tables .
• Joined Tables – This is initially empty and is where tables that represent a join are placed by the join wizard.

Reading from a Database

When reading a table from a database, use the structure in the Tables as the input to your map. By default, it will read all of the rows from the table. If you wish to select less than that, use the DatabaseSelect on the root element of the table (the element about the Row element).

Frequently Asked TALEND Interview Questions & Answers

If you wish to read multiple unrelated tables (in the same or different databases) as an input to a map, create an enclosing structure with an element for each table to read. And in each table element, inherit from the Tables structure and then specify the DatabaseSelect as usual.

Writing to a Database Table

 

To write to a database, use the structure in Tables. You can either insert new rows into the database or update rows which are already present. When updating, it will find the row to update by the primary key column(s) specified for the table (which is normally detected in the database import).

You can insert into or update multiple tables in the output of the map by inheriting from the structure in Tables to insert/update. Then specify the DatabaseInsert or DatabaseUpdate function as required at the root element of the Tables structure, which is the element that is the parent of the Row element.