Use KV to Table to convert key-value data to table format - Platform For AI - Alibaba Cloud - Platform For AI

How it works

Given a KV column where each row contains comma-separated key:value pairs:

Input — one row per sample, one KV column:

kv
`1:1,2:2,3:-3.3`
`1:10,2:20,3:-33.3`

Output — one numeric column per key:

kv_1	kv_2	kv_3
1.0	2.0	-3.3
10.0	20.0	-33.3

The component also writes a key_map table that records the mapping between column names and key indexes:

col_name	col_index	col_type
kv_1	1	double
kv_2	2	double
kv_3	3	double

Converted columns appear before any appended columns in the output.

Key concepts

KV format

Each value in the KV column must follow the format key:value,key:value,... where:

Key is a column name index (integer).
Value must be of the BIGINT or DOUBLE type. Only columns of numeric data types can be converted.
If a row contains duplicate keys, the component sums their values.

key_map table

The key_map table records column-name-to-key-index mappings and data type information. Its schema uses three STRING columns:

Column	Description
`col_name`	Output column name
`col_index`	Key index
`col_datatype`	Data type. Defaults to DOUBLE if not specified

You can import a custom key_map table to control which keys are converted and what data types the output columns use. When a key_map table is imported, only keys present in both the key_map table and the input KV column are converted, and the output column data types match the key_map table.

The component always exports a key_map table, regardless of whether one is imported.

Output column naming

Output column names follow the pattern {kv_column_name}_{key} — for example, a KV column named kv with key 1 produces kv_1.

The following characters are not supported in column names: %&()*+-./;<>=?

Names longer than 128 characters are truncated to 128 characters.

If an appended column name matches a converted column name, the component reports an error.

Limitations

Maximum output columns: 1,200. When top1200 is set to true, the component exports only the first 1,200 columns if the output exceeds this limit. When set to false, the component reports an error instead.
Maximum input rows: 100,000,000

Configure the component

Method 1: Machine Learning Designer

Configure the KV to Table component on the pipeline canvas in Machine Learning Designer.

Tab	Parameter	Description
Fields setting	KV Column	Names of the columns that contain data in key-value format
	Appended Columns	Names of columns to pass through unchanged
	KV Delimiter	Delimiter between keys and values. Default: `:`
	KV Pair Delimiter	Delimiter between key-value pairs. Default: `,`
Parameters setting	Reserve the First 1,200 Columns	When enabled, exports only the first 1,200 columns if the output exceeds the limit. When disabled, the component reports an error instead
Tuning	Computing Cores	Number of cores. Auto-allocated based on input data volume
Tuning	Memory Size per Core	Memory per core, in MB. Auto-allocated based on input data volume

Method 2: PAI commands

Run the component from a SQL Script component using PAI commands. For more information, see SQL Script.

PAI -name KVToTable
    -project algo_public
    -DinputTableName=test
    -DoutputTableName=test_out
    -DoutputKeyMapTableName=test_keymap_out
    -DkvColName=kv;

Parameter	Required	Default	Description
`inputTableName`	Yes	—	Name of the input table
`kvColName`	Yes	—	Names of the KV columns
`outputTableName`	Yes	—	Name of the output table
`outputKeyMapTableName`	Yes	—	Name of the output key_map table
`inputKeyMapTableName`	No	—	Name of an input key_map table to control key-to-column mapping
`appendColName`	No	—	Names of columns to pass through unchanged
`inputTablePartitions`	No	All partitions	Partitions to read from the input table. Format: `Partition_name=value`. For multi-level partitions: `name1=value1/name2=value2`. Separate multiple partitions with commas
`kvDelimiter`	No	`:`	Delimiter between keys and values
`itemDelimiter`	No	`,`	Delimiter between key-value pairs
`top1200`	No	`true`	Whether to export only the first 1,200 columns when the output exceeds the limit. Set to `false` to report an error instead
`lifecycle`	No	—	Lifecycle of the output table
`coreNum`	No	System-determined	Number of cores. Must be a positive integer
`memSizePerCore`	No	System-determined	Memory per core, in MB. Valid range: (100, 65536)

Example

Create the input table:

DROP TABLE IF EXISTS test;
CREATE TABLE test AS
SELECT * FROM
    (
        SELECT '1:1,2:2,3:-3.3' AS kv UNION ALL
        SELECT '1:10,2:20,3:-33.3' AS kv
    ) tmp;

Run the KV to Table component:

PAI -name KVToTable
    -project algo_public
    -DinputTableName=test
    -DoutputTableName=test_out
    -DoutputKeyMapTableName=test_keymap_out
    -DkvColName=kv;

Check the output table (test_out):

kv_1	kv_2	kv_3
1.0	2.0	-3.3
10.0	20.0	-33.3

Check the key_map table (test_keymap_out):

col_name	col_index	col_type
kv_1	1	double
kv_2	2	double
kv_3	3	double

FAQ

Which columns are converted when I provide a key_map table?

Only columns whose keys appear in both the key_map table and the input KV column are converted.

What data type do the output columns use when I provide a key_map table?

Output columns use the data type specified in the key_map table's col_datatype column. If col_datatype is not specified, the output defaults to DOUBLE.

Why is there a column name conflict?

If an appended column shares a name with a converted key column, the component reports an error. Rename one of the conflicting columns before running the component.

Which column types can be converted?

Only columns of numeric data types can be converted.

What happens when a column name exceeds 128 characters?

Only the first 128 characters are kept.

What happens when a row contains duplicate keys?

The component sums the values of duplicate keys.