what is the string similarity-top n algorithm component-Platform For AI(PAI)-阿里云帮助中心

For each row in an input table, this component calculates string similarity against all rows in a mapping table and returns the top N closest matches.

Choose a similarity method

Five similarity methods are available. The default, levenshtein_sim, works well for most use cases.

Method	Full name
`levenshtein_sim` (default)	Levenshtein similarity
`lcs_sim`	Longest Common Subsequence similarity
`ssk`	String Subsequence Kernel
`cosine`	Cosine similarity
`simhash_hamming_sim`	SimHash Hamming similarity

The ssk, cosine, and simhash_hamming_sim methods require the k (substring length) parameter. The ssk and simhash_hamming_sim methods also require the lambda (matching string weight) parameter.

Configure the component

Configure the component on the Designer workflow page or using a PAI command.

Configure via the Designer UI

On the Designer workflow page, select the component and configure the following parameters.

Field settings tab

Parameter	Description
Columns to append from input table	Columns from the input table to include in the output
Columns to append from mapping table	Columns from the mapping table to include in the output
Left table column for similarity calculation	Column from the input table to use for similarity calculation
Mapping table column for similarity calculation	Column from the mapping table to use for similarity calculation. The component calculates similarity between each row in the input table and all strings in the mapping table, then returns the top N results.
Similarity column name in output table	Name for the similarity score column in the output table. Must start with a letter and contain only letters (a–z, A–Z), digits, and underscores (`_`). Maximum length: 128 bytes. Default: `output`

Parameter settings tab

Parameter	Description
Number of top similarity values	Number of top matches to return per input row. Must be a positive integer. Default: `10`
Similarity calculation method	Similarity method to use. Default: `levenshtein_sim`. See Choose a similarity method
Substring length (`k`)	Required when the method is `ssk`, `cosine`, or `simhash_hamming_sim`. Value range: (0, 100). Default: `2`
Matching string weight (`lambda`)	Required when the method is `ssk` or `simhash_hamming_sim`. Value range: (0, 1). Default: `0.5`

Execution tuning tab

Parameter	Description
Number of cores	Number of CPU cores to allocate. Allocated automatically by default
Memory per core (MB)	Memory per CPU core. Allocated automatically by default

Configure via PAI command

Run the component using the SQL Script component. For more information, see SQL Script.

PAI -name string_similarity_topn
    -project algo_public
    -DinputTableName="pai_test_string_similarity_topn"
    -DoutputTableName="pai_test_string_similarity_topn_output"
    -DmapTableName="pai_test_string_similarity_map_topn"
    -DinputSelectedColName="col0"
    -DmapSelectedColName="col1"

Parameters

Parameter	Required	Description	Default
`inputTableName`	Yes	Name of the input table	—
`mapTableName`	Yes	Name of the mapping table	—
`outputTableName`	Yes	Name of the output table	—
`inputSelectedColName1`	No	Column from the input table to use for similarity calculation	First STRING column in the table
`inputSelectedColName2`	No	Column from the mapping table to use for similarity calculation	First STRING column in the table
`mapSelectedColName`	Yes	Column from the mapping table to use for similarity calculation	—
`inputAppendColNames`	No	Columns from the input table to include in the output table	—
`inputAppendRenameColNames`	No	Aliases for appended columns from the input table	—
`mapAppendColNames`	No	Columns from the mapping table to include in the output table	—
`mapAppendRenameColNames`	No	Aliases for appended columns from the mapping table	—
`inputTablePartitions`	No	Partitions of the input table to process	All partitions
`mapTablePartitions`	No	Partitions of the mapping table to process	All partitions
`outputColName`	No	Name of the similarity score column in the output table. Same naming constraints as the UI parameter	`output`
`method`	No	Similarity method. Valid values: `levenshtein_sim`, `lcs_sim`, `ssk`, `cosine`, `simhash_hamming_sim`	`levenshtein_sim`
`k`	No	Substring length. Required when `method` is `ssk`, `cosine`, or `simhash_hamming_sim`. Value range: (0, 100)	`2`
`lambda`	No	Matching string weight. Required when `method` is `ssk` or `simhash_hamming_sim`. Value range: (0, 1)	`0.5`
`lifecycle`	No	Number of days to retain the output table. Must be a positive integer	—
`coreNum`	No	Number of CPU cores to allocate	System-assigned
`memSizePerCore`	No	Memory per CPU core (MB)	Automatically assigned

Example

Input data

Use an ODPS SQL node to create the pai_ft_string_similarity_topn_input table:

CREATE TABLE pai_ft_string_similarity_topn_input AS
SELECT * FROM (
    SELECT 0 AS id, 'Beijing' AS col0
    UNION ALL
    SELECT 1 AS id, 'Beijing Shanghai' AS col0
    UNION ALL
    SELECT 2 AS id, 'Beijing Shanghai Shenzhen' AS col0
) tmp;

Result:

id	col0
0	Beijing
1	Beijing Shanghai
2	Beijing Shanghai Shenzhen

Run the PAI command

Run the following PAI command using an SQL Script component or ODPS SQL node:

PAI -name string_similarity_topn
    -project algo_public
    -DinputTableName=pai_ft_string_similarity_topn_input
    -DmapTableName=pai_ft_string_similarity_topn_input
    -DoutputTableName=pai_ft_string_similarity_topn_output
    -DinputSelectedColName=col0
    -DmapSelectedColName=col0
    -DinputAppendColNames=col0
    -DinputAppendRenameColNames=input_col0
    -DmapAppendColNames=col0
    -DmapAppendRenameColNames=map_col0
    -Dmethod=simhash_hamming_sim

Output

input_col0	map_col0	output
Beijing	Beijing	1.0
Beijing	Beijing Shanghai	0.90625
Beijing	Beijing Shanghai Shenzhen	0.796875
Beijing Shanghai	Beijing Shanghai	1.0
Beijing Shanghai	Beijing	0.90625
Beijing Shanghai	Beijing Shanghai Shenzhen	0.828125
Beijing Shanghai Shenzhen	Beijing Shanghai Shenzhen	1.0
Beijing Shanghai Shenzhen	Beijing Shanghai	0.828125
Beijing Shanghai Shenzhen	Beijing	0.796875

Resource usage

This component uses M × N computational complexity: to find the closest matches for N input rows within a mapping table of M rows, the algorithm computes M × N pairwise similarity scores.

Resource	Formula	Limit
Worker count	(M × N) / (1,024 × 1,024 × 32)	Max 1,000 workers
Memory per worker	N / 8 MB	4 GB – 64 GB
Maximum computing units (CUs)	1,000 × 64 / 4	16,000 CUs

One computing unit (CU) provides 4 GB of memory. For billing details, see Billing example for Machine Learning Designer.

What's next

Designer overview — get started with the Designer workflow canvas
String Similarity — calculate pairwise similarity between two tables without Top N ranking