You have table data from multiple business systems, where the data contains the same fields or fields with the same meaning. You want to merge multiple tables row-wise and output a combined table. Currently, you achieve this through the Spark SQL operator, which is cumbersome, and you want to enable codeless implementation.
A Union All operator is added, allowing row-wise merging of multiple tables into a combined table, as shown in the following figure.
The configuration page of the Union All operator is shown in the following figure.
Options include Union (deduplicated combination) and Union All (non-deduplicated combination).
Union: If duplicate records exist across tables, only one is retained.
Union All: All records from all tables are retained.
Map Fields with Same Name: Fields with identical names are matched. (Fuzzy matching is supported.) The logic is the same as that of Map Fields with Same Name in Field Mapping in the Data Synchronization node.
Map in Sequence: Fields are matched based on their order in the table. The logic is the same as that of Map Fields in Same Row in Field Mapping in the Data Synchronization node.
In Field Mapping, if a source table has no corresponding field to map, that field defaults to empty.
In Data Preview, the corresponding table data will be displayed as a null value.
You can rename the result field in the Union Result row.
Starting from V4.1.6.1, changes to the source table fields do not impact the result field names that had been modified in Union Result.
You can select fields from the field drop-down list to manually map fields. You can also select Empty.
The mapping results will be automatically refreshed after you make changes to the mappings.
On the Data Preview page, you can preview the first 1000 rows by default, with 20 rows per page.
1. If you want two fields with the same name not to be merged when Union All is selected, you can manually adjust the mappings by setting the field of one of the tables to Empty, as shown in the following figure.
2. If you add an input operator before a configured Union All operator, you need to click the Union All operator first, and then the system will refresh the configuration in the Union All operator automatically.
You can download the example data: Contract_Table_Washington D.C.xlsx, Contract_Table_New York.xlsx.
You have two contract data tables and want to merge data into a single table for subsequent analysis.
1. Create a scheduled task, drag a Data Transformation node onto the page, and enter the Data Transformation editing page.
2. Drag in a DB Table Input operator and configure it to read the Contract_Table_WashingtonDC table, as shown in the following figure.
3. Drag in another DB Table Input operator and configure it to read the Contract_Table_NewYork table, as shown in the following figure.
1. Drag in a Union All operator and connect it to the two DB Table Input operators, as shown in the following figure.
2. The configuration page of the Union All operator is shown in the following figure.
Select Union as the Union Mode to exclude duplicate records across the merged tables.
FineDataLink will automatically match fields with identical names. For fields that do not have matching names but share the same meaning, you need to perform manual mapping. For the Total_Contract_Price (USD) column, select the Total_Contract_Price field; for the Signing_Area column, select the Sales_Area field. The following figure shows the final effect.
3. Click Data Preview, as shown in the following figure.
1. Drag in a DB Table Output operator and configure the operator to output the data after merging, as shown in the following figure.
Click Run to execute the task. The running result in Log upon successful execution is shown in the following figure.
Data in the database table is shown in the following figure.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy