Convert triple to KV - Platform For AI - Alibaba Cloud Documentation Center

When building recommendation or ranking models, training data is often stored as a trituple table (row, col, value)—for example, user ID, item ID, and click count. The Convert Row, Column, and Value to KV Pair component in Machine Learning Designer transforms this format into a sparse key-value table (row, [col_id:value]) and a corresponding index table that maps column names to integer IDs. This is the standard preprocessing step for feeding sparse feature data into models that expect vector-indexed input.

How it works

The component takes a trituple table whose columns represent a row identifier, a column name, and a numeric value, then does the following:

Assigns a unique integer ID (col_id) to each distinct column name and records the mapping in an index table.
Groups rows by the row identifier and concatenates all col_id:value pairs into a single key-value string.

Input data type requirements

The input trituple table must follow one of two column type patterns:

Pattern	Column types	Value column type
`XXD`	Row: any, Col: any	DOUBLE
`XXL`	Row: any, Col: any	BIGINT

In the output key-value table, the row and value columns retain their original data types. The col_id column in the index table is always BIGINT.

Example

Input trituple table:

id	word	count
01	a	10
01	b	20
01	c	30

The component generates two output tables:

Key-value table — each row's column values are collapsed into a single col_id:value string:

id	key_value
01	1:10;2:20;3:30

Index table — maps each original column name to its assigned integer ID:

key	key_id
a	1
b	2
c	3

Note

The delimiter between a key and its value, and the delimiter between key-value pairs, are both customizable.

Configure the component

Option 1: Configure on the pipeline canvas

On the Machine Learning Designer pipeline canvas, click the component to open its settings panel. The following tables describe the parameters.

Fields Setting tab

Parameter	Required	Description	Default
Columns reserved during KV conversion	Yes	The column that remains unchanged after conversion (the row identifier).	—
Output keys	Yes	The column whose values become keys in the key-value table.	—
Output values	Yes	The column whose values become values in the key-value table.	—
Key column in input index table	No	The key column in an existing index table. Required if you provide an index table.	—
Index ID column of key in input index table	No	The index ID column in an existing index table. Required if you provide an index table.	—
KV delimiter	No	The delimiter between a key and its value.	`:` (colon)
KV pair delimiter	No	The delimiter between key-value pairs.	`,` (comma)

Tuning tab

Parameter	Required	Description	Default
Total number of instances	No	The number of compute instances. Must be a positive integer.	Auto-calculated from input data
Memory size (MB)	No	The total memory. Must be a positive integer.	Auto-calculated from input data

Option 2: Use a PAI command

Run the component via a PAI command in the SQL Script component.

PAI -name triple_to_kv
    -project algo_public
    -DinputTableName=test_data
    -DoutputTableName=test_kv_out
    -DindexOutputTableName=test_index_out
    -DidColName=id
    -DkeyColName=word
    -DvalueColName=count
    -DinputTablePartitions=ds=test1
    -DindexInputTableName=test_index_input
    -DindexInputKeyColName=word
    -DindexInputKeyIdColName=word_id
    -DkvDelimiter=:
    -DpairDelimiter=;
    -Dlifecycle=3

Parameters

Parameter	Required	Description	Default
`inputTableName`	Yes	Name of the input trituple table.	—
`idColName`	Yes	The row identifier column—remains unchanged in the output.	—
`keyColName`	Yes	The column whose values become keys.	—
`valueColName`	Yes	The column whose values become values.	—
`outputTableName`	Yes	Name of the output key-value table.	—
`indexOutputTableName`	Yes	Name of the output index table.	—
`indexInputTableName`	No	An existing index table to use. Must contain data.	—
`indexInputKeyColName`	No	The key column in the existing index table. Required if `indexInputTableName` is set.	—
`indexInputKeyIdColName`	No	The index ID column in the existing index table. Required if `indexInputTableName` is set.	—
`inputTablePartitions`	No	A partition of the input table. Only one partition name is supported.	—
`kvDelimiter`	No	The delimiter between a key and its value.	`:` (colon)
`pairDelimiter`	No	The delimiter between key-value pairs.	`,` (comma)
`lifecycle`	No	The lifecycle of the output table.	—
`coreNum`	No	The number of compute instances. Must be a positive integer.	Auto-calculated from input data
`memSizePerCore`	No	The total memory. Must be a positive integer.	Auto-calculated from input data

Example

The following example converts a trituple table with two users' interaction records into a key-value table and an index table.

Step 1: Create the input table (triple2kv_test_input)

drop table if exists triple2kv_test_input;
create table triple2kv_test_input as
select * from (
  select '01' as id, 'a' as word, 10 as count
  union all select '01' as id, 'b' as word, 20 as count
  union all select '01' as id, 'c' as word, 30 as count
  union all select '02' as id, 'a' as word, 100 as count
  union all select '02' as id, 'd' as word, 200 as count
  union all select '02' as id, 'e' as word, 300 as count
) tmp;

Step 2: Run the PAI command

PAI -name triple_to_kv
    -project algo_public
    -DinputTableName=triple2kv_test_input
    -DoutputTableName=triple2kv_test_input_out
    -DindexOutputTableName=triple2kv_test_input_index_out
    -DidColName=id
    -DkeyColName=word
    -DvalueColName=count
    -Dlifecycle=1;

Output

Column names a, b, c, d, e are assigned IDs 1–5. The key-value table (triple2kv_test_input_out) collapses each user's interactions into a single string:

+----+-------------------+
| id | key_value         |
+----+-------------------+
| 01 | 1:10;2:20;3:30    |
| 02 | 1:100;4:200;5:300 |
+----+-------------------+

The index table (triple2kv_test_input_index_out) records the column-name-to-ID mapping:

+-----+--------+
| key | key_id |
+-----+--------+
| a   | 1      |
| b   | 2      |
| c   | 3      |
| d   | 4      |
| e   | 5      |
+-----+--------+