Vector32 Examples Using TD_KMeansPredict - Teradata VantageCloud Lake

Embedding Support Example

Input

DROP TABLE input_table_udt;
CREATE MULTISET TABLE input_table_udt(id INTEGER, array_col Vector32) PRIMARY
INDEX ( id );
INSERT INTO input_table_udt values(1, '-0.06944,0.080352,0.045963,0.006985,-0.000496');
INSERT INTO input_table_udt values(2, '-0.025461,0.045302,-0.028107,0.031248,0.00077');
INSERT INTO input_table_udt values(3, '-0.028107,0.016781,-0.023147,-0.068779,-0.07936');
INSERT INTO input_table_udt values(4, '-0.030091,0.027611,-0.047616,-0.025461,0.029595');

input_table_udt

DROP TABLE initial_centroids_table_udt;
CREATE MULTISET TABLE initial_centroids_table_udt AS (SELECT * FROM input_table_udt WHERE id in (2,4)) with data;

initial_centroids_table_udt

Query (InitialCentroidsTable not provided)

SELECT td_clusterid_kmeans, cast(array_col as Varchar(100)) AS array_col, td_size_kmeans, td_withinss_kmeans, id, td_modelinfo_kmeans
FROM TD_KMeans (
ON input_table_udt as InputTable
USING
IdColumn('id')
TargetColumns('array_col')
NumClusters(2)
Seed(0)
StopThreshold(0.0395)
MaxIterNum(3)
EmbeddingSize(5)
UseSIMD('t')  
)as dt;

Output

Query (InitialCentroidsTable is provided)

SELECT td_clusterid_kmeans, cast(array_col as Varchar(100)) as array_col, td_size_kmeans, td_withinss_kmeans, id, td_modelinfo_kmeans FROM TD_KMeans (
ON input_table_udt as InputTable
ON initial_centroids_table_udt as InitialCentroidsTable DIMENSION
USING
IdColumn('id')
TargetColumns('array_col')
StopThreshold(0.0395)
MaxIterNum(3)
EmbeddingSize(5)
UseSIMD('t')  
)as dt;

Output

Query (OutputClusterAssignment is set to true)

Select * from TD_KMeans (
on input_table_udt as InputTable
on initial_centroids_table_udt as InitialCentroidsTable DIMENSION
using
IdColumn('id')
TargetColumns('array_col')
StopThreshold(0.0395)
MaxIterNum(3)
OutputClusterAssignment('true')
EmbeddingSize(5)
UseSIMD('t')  
)as dt;

Output

Query (OutputClusterAssignment is set to true, and ModelTable (OUT TABLE clause) is provided)

SELECT * FROM TD_KMeans (
ON input_table_udt as InputTable
ON initial_centroids_table_udt AS InitialCentroidsTable DIMENSION
OUT TABLE ModelTable(kmeans_model_udt)
USING
IdColumn('id')
TargetColumns('array_col')
StopThreshold(0.0395)
MaxIterNum(3)
OutputClusterAssignment('true')
EmbeddingSize(5)
UseSIMD('t')  
)as dt;

Output

ModelTable: kmeans_model

Example (InitialCentroidsMethod: 'kmeans++')

Input

DROP TABLE input_table_1_udt;
CREATE TABLE input_table_1_udt(id INTEGER, array_col Vector32) Primary Index(id);
INSERT INTO input_table_1_udt values(1, '-0.06944,0.080352,0.045963,0.006985,-0.000496');
INSERT INTO input_table_1_udt values(2, '-0.025461,0.045302,-0.028107,0.031248,0.00077');
INSERT INTO input_table_1_udt values(3, '-0.028107,0.016781,-0.023147,-0.068779,-0.07936');
INSERT INTO input_table_1_udt values(4, '-0.030091,0.027611,-0.047616,-0.025461,0.029595');
INSERT INTO input_table_1_udt values(5, '0.024635,-0.025461,-0.029926,0.046294,-0.065142');
INSERT INTO input_table_1_udt values(6, '-0.019013,0.037366,-0.008019,0.065472,-0.054891');
INSERT INTO input_table_1_udt values(7, '-0.009507,0.022816,0.009341,-0.041995,0.022651');
INSERT INTO input_table_1_udt values(8, '0.028603,0.059851,0.047286,0.014467,0.002118');
INSERT INTO input_table_1_udt values(9, '0.016616,-0.009383,-0.001643,0.015624,0.002831');

input_table_1_udt

Query

SELECT * FROM TD_KMeans (
ON input_table_1_udt AS InputTable
USING
IdColumn('id')
TargetColumns('array_col')
InitialCentroidsMethod('kmeans++')
NumClusters(3)
Seed(10)
StopThreshold(0.0395)
MaxIterNum(3)
EmbeddingSize(5)
UseSIMD('t')  
)as dt;

Output