Reading from and Writing to a Database Table – Talend
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). 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
DatabaseUpdate function as required.
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.
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
DatabaseSelectfunction in the
Rowelement. When writing, use either the
DatabaseUpdatefunction in the
- 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).
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
DatabaseUpdate function as required at the root element of the Tables structure, which is the element that is the parent of the Row element.