Import and Map Data (ETL)
This chapter covers how to import data from CSV files into database tables.
The import of a CSV file can be performed using the Trillo Workbench UI. It consists of these steps:
- Create an Import and give it a name. Select the target database table. If you specify a database table that is not present then a new table with the given name is created to match the columns of CSV.
- Upload a CSV file or select a CSV file from the previously uploaded files.
- Define mapping. This can be very simple to a moderate complexity task. The mapping permits scripting using MVEL. This is very powerful. It permits to write expressions to a program. Therefore, you can perform complex ETL functions.
- Run mapping as a background task - once you submit the mapping, Trillo automatically starts a background task. You can monitor the status of the task by selecting the Task menu.
Import and Map Data using Trillo Workbench
In the above diagram, the middle column of the rightmost panel is mapping. Each card in the middle column defines the mapping of one attribute. One card is automatically created for each target attribute. If no mapping is defined for a target attribute then it is ignored in the processing.
The layout of each card is as follows:
- Target attribute followed by the operation.
- Source or Expression. The value of this field depends on the selection of operation. See below for the details of the source specification for each operation below.
If the selected operation is "=" (assignment) then the source value is the name of the source attribute. You don't have to type the name of the source attribute if you follow these steps:
- Click on a card, it will turn dark to indicate that it is selected.
- Hover over the source attribute, it will show a right arrow. Clicking the arrow will enter the value into the Source or Expression field.
If you select the expression operation then the value of the Source or Expression field can be any MVEL expression. The MVEL expression is computed against the source row (therefore it should be referring to the source attributes). The value or the return value of the MVEL expression is set as the value of the target attribute.
The following is an example of MVEL expression for computing "fullName" target attribute (as an example). The source attribute row is passed as a map whose name is v' (a convention followed by Trillo).
if (v.?middleName == empty) {
v.fullName = v.firstName + " " + v.lastName;
} else {
v.fullName = v.firstName + " " + v.middleName + " " + v.lastName;
}
This is applicable to a string-type target attribute. Concatenation is an expression in terms of source attributes and strings constant (enclosed in double-quotes). The following is an example of a concatenation operation.
city + ", " + state + " " + zip
If you select Template operation, the Source or Expression filed can be any Mustache template. The template uses source attributes as the template variables.
Person {{{firstName}}} {{{lastName}}} lives in state {{{state}}}.
This is a special operation to convert a date from one format to another such as dd/mm/yyyy to mm/dd/yyyy. When the DateFormat operation is selected, two additional fields appear to specify the source and target formats.
Converting Source Attribute from One DateFormat to Another
Once the mapping is defined, a user can submit the import using Trillo Workbench UI. The import runs as a background task using a serverless function called GenericImport (its code is available in the Trillo Workbench under the Functions menu option).
Instead of GenericImport, you can use a custom function by specifying it in the first step of the import wizard (Name). For example, you can use GenricImport as an example and use a custom function to do specialized processing (for example store data in multiple tables).
Last modified 2yr ago