Merge Table Columns Using the PAI Merge Columns Component - Platform for AI - Alibaba Cloud - Platform For AI

The Merge Columns algorithm component combines two tables by appending their columns side-by-side. Both input tables must have the same number of rows, or an error occurs. If one of the two tables is partitioned, connect the partitioned table to the second input port.

Component configuration

You can configure the Merge Columns component in the following ways.

Method 1: Visual configuration

On the Field Settings tab, use Customize Output Columns from Left Table and Customize Output Columns from Right Table to select the columns for the output table.

Method 2: PAI command

Configure the component using a PAI command in the SQL Script component. For more information, see SQL Script.

PAI -name appendColumns
    -project algo_public
    -DinputTableNames=maple_test_appendcol_basic_input1,maple_test_appendcol_basic_input2
    -DoutputTableName=maple_test_appendcol_setOutCol_output
    -DoutputTableColNames=x0,x1,x2,x3,x4,x5,x6,x7,x8,x9;

Parameter	Required	Description	Default
inputTableNames	Yes	The names of the two input tables, separated by a comma (,).	None
outputTableName	Yes	The name of the output table.	None
selectedColNamesList	No	A list of selected column names that correspond to the input tables. Columns from the same table are separated by commas (,). Columns from different tables are separated by semicolons (`;`). Note If you select all columns from both tables, you must enclose the entire parameter value in double quotation marks (""). Otherwise, the system interprets the semicolon (`;`) as a command terminator. If you select all columns from one table, you can omit the column names for that table but must still include the semicolon (;).	None
inputPartitionsInfoList	No	A list of selected partitions that correspond to the input tables. Partitions from the same table are separated by forward slashes (/). Partitions from different tables are separated by semicolons (`;`). Note If you select all partitions, you must enclose the entire parameter value in double quotation marks (""). Otherwise, the system interprets the semicolon (`;`) as a command terminator. If a table is not partitioned, you can omit the partition names but must still include the semicolon (`;`).	None
autoRenameCol	No	Specifies whether to automatically rename the columns in the output table.	false
outputTableColNames	No	The new names for the columns in the output table. If you leave this parameter empty, the original column names are used. Important This parameter has no effect if `autoRenameCol` is set to `true`.	None
lifecycle	No	The lifecycle of the output table, in days. The value must be a positive integer.	None
coreNum	No	The number of cores for the job. This parameter is used with `memSizePerCore`. The value must be a positive integer in the range of [1, 9999].	Determined by the system
memSizePerCore	No	The memory size per core, in MB. The value must be a positive integer in the range of [1024, 65536].	Determined by the system

Example

This example merges the following two source tables:

Source table 1

Table 1. maple_test_appendcol_basic_input1
col0:bigint	col1:double	col2:string	col3:Datetime	col4:Boolean
10	0.0	aaaa	2015-10-01 00:00:00	TRUE
11	1.0	aaaa	2015-10-01 00:00:00	FALSE
12	2.0	aaaa	2015-10-01 00:00:00	TRUE
13	3.0	aaaa	2015-10-01 00:00:00	TRUE
14	4.0	aaaa	2015-10-01 00:00:00	TRUE

Source table 2

Table 2. maple_test_appendcol_basic_input2
col10:bigint	col11:double	col12:string	col13:Datetime	col14:Boolean
110	10.0	2aaaa	2015-10-01 00:00:00	TRUE
111	11.0	2aaaa	2015-10-01 00:00:00	FALSE
112	12.0	2aaaa	2015-10-01 00:00:00	TRUE
113	13.0	2aaaa	2015-10-01 00:00:00	TRUE
114	14.0	2aaaa	2015-10-01 00:00:00	FALSE

Run the following PAI command to merge the source tables.

PAI -name appendColumns
    -project algo_public
    -DinputTableNames=maple_test_appendcol_basic_input1,maple_test_appendcol_basic_input2
    -DoutputTableName=maple_test_appendcol_setOutCol_output
    -DoutputTableColNames=x0,x1,x2,x3,x4,x5,x6,x7,x8,x9;

The command generates the following output table.

Table 3. maple_test_appendcol_setOutCol_output
x0	x1	x2	x3	x4	x5	x6	x7	x8	x9
10	0	aaaa	2015-10-01 00:00:00	true	110	10	2aaaa	2015-10-01 00:00:00	true
11	1	aaaa	2015-10-01 00:00:00	false	111	11	2aaaa	2015-10-01 00:00:00	false
12	2	aaaa	2015-10-01 00:00:00	true	112	12	2aaaa	2015-10-01 00:00:00	true
13	3	aaaa	2015-10-01 00:00:00	true	113	13	2aaaa	2015-10-01 00:00:00	true
14	4	aaaa	2015-10-01 00:00:00	true	114	14	2aaaa	2015-10-01 00:00:00	false