This article introduces the dynamic schema feature. Dynamic schema allows you to design Jobs with an unknown column structure (unknown name and number of columns). If necessary, dynamic columns can be mapped directly to the target using Pass-through mode.
The main application of this functionality is a replication scenario or a simple one-to-one mapping of many columns. This feature makes designing these types of Jobs easy. For example, if you need to migrate a whole database with hundreds of tables, you can do so without explicitly including the table structure, using a single Job.
This article demonstrates the dynamic schema feature through two examples:
These examples are available in the attached Zip file.
This procedure is compatible with all versions of Talend subscription products.
Not every component supports the dynamic schema feature. Refer to the article Which components provide the Dynamic Schema feature? to know which components provide the dynamic schema feature.
This example will extract data from a table called person. The table has many columns and contains the following example data. The objective is to write this data to a file.
In Talend Open Studio for Data Integration, the community version, you have to define each column in the schema to map the real table structure. If the table structure is unknown, it is impossible to read the table.
In Talend subscription products, there is a Dynamic type in the Type list, as seen in the following figure:
Using this type, you will need to define only one column with the Dynamic type, instead of defining all columns.
Create an example Job called ReadTableDataWithDynamicSchema. The Job contains two components:
tMysqlInput_1: read person data from the database.
tFileOutputDelimited_1: write the data to a text file with a header.
The detailed Job settings can be seen in the following figures:
Execute the Job and open the generated file (in this case, it is D:/file/out.txt). Verify that all data is written to the file with the correct column names. The data shows as follows:
id;name;gender;email;department;entry_date 1;Jeffe;Boy;firstname.lastname@example.org;R&D;15-05-2012 2;shong;box;email@example.com;Community;07-08-2011 3;Johnson;Girl;firstname.lastname@example.org;HR;16-04-2014 4;Joyce;Girl;email@example.com;Test;10-09-2012 5;Truman;Boy;Truman@company.com;Marketing;27-02-2013
Here is another example to explain how to read data from a file with the dynamic schema feature. In this example, you will read the file that was generated by the above scenario.
Note: When reading data from a file with a dynamic schema, the first line of the file must be the header that will be read as the column names at run time. In this example, the first line of the file is the header.
Create an example Job called ReadFileDataWithDynamicSchema. The Job contains two components:
tFileInputDelimited_1: read data from the text file.
tMysqlOutput_1: create a table in database and insert the data.
Execute the Job. Verify that a table called person1 was created in the database and the data was inserted.