Platform For AI:KV2Table

Background information

Definition of the key-value table format: The key represents the column name index, and the value supports data of the BIGINT or DOUBLE type. You can import a custom key_map table into this component. The key_map table contains the mappings of column names and keys. Regardless of whether a key_map table is imported, this component exports a key_map table to record the mappings of column names and keys after conversion, such as 1:10, 2:20, and 3:30.

Component parameters

You can use one of the following methods to configure the KV to Table component:

Method 1: Configure the component on the pipeline page

Method 2: Use PAI commands

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

Examples

• Generate input data

  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 PAI commands

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

• Output description

  • Output table

    +------------+------------+------------+
    | kv_1       | kv_2       | kv_3       |
    +------------+------------+------------+
    | 1.0        | 2.0        | -3.3       |
    | 10.0       | 20.0       | -33.3      |
    +------------+------------+------------+

  • Output mapping table

    +------------+------------+------------+
    | col_name   | col_index  | col_type   |
    +------------+------------+------------+
    | kv_1       | 1          | double     |
    | kv_2       | 2          | double     |
    | kv_3       | 3          | double     |
    +------------+------------+------------+

Algorithm scale

The converted columns include the appended columns and the columns converted by using the KV to Table component. The columns converted by using the KV to Table component are exported before the appended columns. If the number of converted columns exceeds the maximum number of columns of the key_map table, and the top1200 parameter is set to true, the maximum number of columns is exported. Otherwise, an error is reported. A maximum of 1,200 columns can be exported.

The number of data records cannot exceed 100 million.

FAQ

• Q: Which columns are converted if the input includes a key_map table?

  A: The converted columns are the columns whose keys exist in both the key_map and key-value tables.

• Q: What is the data type of the key column after conversion if the input includes a key_map table?

  A: The data type of the converted key column is the same as that of the key_map table. If the data type of the key_map table is not specified, the data type of the converted key column is DOUBLE.

• Q: What is the naming convention for the converted key column if a key_map table is imported?

  A: The naming convention is Name of the column in the key:value format + "" + Key.

  The following characters are not supported:

  %&()*+-./;<>=?

• Q: Why are column names conflict with each other?

  A: If you specify an appended column and the name of the appended column is the same as that of the converted key column, an error is reported.

• Q: Which types of columns can be converted?

  A: Only columns of the numeric data types can be converted.

• Q: What would happen if the length of the column name exceeds 128 characters?

  A: Only the first 128 characters of the column name are reserved.

• Q: What do I do if a row contains duplicate keys?

  A: Calculate the sum of the values that correspond to these keys.