Skip to main content

KMeans

UDTF: cuvs_kmeans

Official cuVS reference: C API

K-means clustering assignments for dense vector rows.

Quickstart​

The call below expects the registered relation input_vectors (the input role), passed as a parenthesized SELECT subquery.

SELECT id, cluster_id
FROM cuvs_kmeans(
input => (SELECT id, d0, d1 FROM input_vectors),
n_clusters => 8
)
ORDER BY row_ordinal;

Inputs​

Each relation argument is a parenthesized SELECT subquery that the planner keeps as an operator child. Metadata validation resolves a registered table or view for the same role. See Vector Inputs for the relation identity rules and the ID, dense-vector type, null, finite-value, and runtime-dimension contract.

RoleRequiredValidation referenceDescription
inputyestableDense-vector rows consumed by this operation.

Vector element types​

Element typeValid metrics
Float32l2_expanded, l2_sqrt_expanded
Float64l2_expanded, l2_sqrt_expanded

Arguments and options​

Scalar SQL arguments​

ArgumentTypeRequiredDefaultConstraintsDescription
initstringno"kmeans++"one of "kmeans++", "random"Centroid initialization strategy for this fitted model.
max_iterintegerno100minimum 1; maximum 2147483647Maximum fitting iterations for this invocation.
metricstringno"l2_expanded"one of "l2_expanded", "l2_sqrt_expanded"L2 distance form used while fitting and assigning the current input relation.
n_clustersintegeryesNo defaultminimum 1; maximum 2147483647Number of clusters to fit.
n_initintegerno1minimum 1; maximum 2147483647Number of initialization attempts performed within this call.
tolnumberno0.0001minimum 0Non-negative convergence tolerance used by the fitted model.

Vector binding shapes​

Each relation subquery must project an id field followed by either one or more feature fields of a supported element type (Float32, Float64) or one list field of a supported element type, under any name.

For wide vectors, the projection order defines the feature dimensions. A list vector relation must contain no feature field beside id and its list field. Nullable field types are accepted. A null ID, feature value, list row, or list element fails the query.

Output​

ColumnTypeNullableDescription
row_ordinalUInt64noZero-based ordinal of the evaluated input row; it disambiguates duplicate IDs. Follows the input's top-level ORDER BY on output columns; unspecified without one.
idsame_as_input.idnoLogical ID copied from the input relation.
cluster_idInt32noQuery-local numeric assignment label, not a stable business or topic identifier.

The output schema depends on the call. Run gpu_validate_call against registered relations to inspect the schema that the validated ID types and literal options produce.

Examples​

This example uses the Amazon Reviews demo dataset.

Which products have a changed mix of low-rating reviews?​

The query groups 99,539 sampled 2021 to 2022 headphone and earbud reviews using prepared text vectors, then compares each product's share in each KMeans group across the two years. Each denominator counts sampled reviews rated at most two stars for that product and year.

The KMeans assignments join back to review dates, product titles and original text. The linked example is the 2022 review with the smallest ID for its product and group. It is not selected as representative text.

WITH assignments AS (
SELECT id, cluster_id
FROM cuvs_kmeans(
input => (SELECT v.id, v.vector
FROM semantic_reviews_vectors v
JOIN semantic_reviews_text r ON r.id = v.id
WHERE r.category_l3 = 'Headphones & Earbuds'
ORDER BY v.id),
n_clusters => 24, n_init => 5, max_iter => 100, tol => 0.0001)
), issues AS (
SELECT r.product_id, a.cluster_id,
SUM(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2021 THEN 1 ELSE 0 END) AS n2021,
SUM(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2022 THEN 1 ELSE 0 END) AS n2022,
MIN(CASE WHEN EXTRACT(YEAR FROM r.review_time) = 2022 THEN r.id END) AS example_id
FROM assignments a JOIN semantic_reviews_text r ON r.id = a.id
GROUP BY r.product_id, a.cluster_id
), totals AS (
SELECT *, SUM(n2021) OVER (PARTITION BY product_id) AS total2021,
SUM(n2022) OVER (PARTITION BY product_id) AS total2022
FROM issues
)
SELECT p.product_id, p.title, t.cluster_id,
t.n2021, t.total2021, t.n2022, t.total2022,
100.0 * t.n2021 / t.total2021 AS share2021_pct,
100.0 * t.n2022 / t.total2022 AS share2022_pct,
100.0 * t.n2022 / t.total2022 - 100.0 * t.n2021 / t.total2021 AS change_pp,
t.example_id, r.embedding_text AS example_review
FROM totals t
JOIN products p ON p.product_id = t.product_id
JOIN semantic_reviews_text r ON r.id = t.example_id
WHERE t.total2021 >= 30 AND t.total2022 >= 30 AND t.n2022 >= 10
AND 1.0 * t.n2022 / t.total2022 - 1.0 * t.n2021 / t.total2021 > 0.10
ORDER BY change_pp DESC, p.product_id, t.cluster_id
LIMIT 10

For Altec Lansing NanoPods, group 7 changes as follows:

YearReviews in groupSampled low-rating reviewsGroup share
20211422.38%
2022103330.30%

The query also returns one example review per product. The saved capture keeps its review ID (14707192); the text stays in the source dataset.

The query took 30.46 s.

Follow-up: check the charging explanation. The 27.92-point change raises a specific question about charging complaints. A raw-table query counts NanoPods reviews containing "charg" in the title or body. It uses all 2021 to 2022 reviews rated at most two stars, without the embedding sample, text-length filter or verified-purchase condition.

SELECT EXTRACT(YEAR FROM review_time) AS review_year,
COUNT(*) AS low_rating_reviews,
SUM(CASE WHEN LOWER(COALESCE(title, '') || ' ' || COALESCE(text, '')) LIKE '%charg%' THEN 1 ELSE 0 END) AS charging_mentions
FROM reviews
WHERE product_id = 1507685 AND rating <= 2
AND review_time >= TIMESTAMP '2021-01-01'
AND review_time < TIMESTAMP '2023-01-01'
GROUP BY EXTRACT(YEAR FROM review_time)
ORDER BY review_year;
YearMatching reviewsAll low-rating reviewsMatching share
2021469250.00%
2022376755.22%

The query took 0.12 s.

Charging language was already common in 2021. The broader source check does not support treating the group-share increase as a comparable rise in charging complaints.

The main capture records a mixed KMeans query with 20 host DataFusion boundaries. The native raw-table checks and five original matching reviews are in the follow-up capture.

Limits​

  • Fits a model and returns assignments within one call; it does not return a reusable model, centroids, or inertia.
  • The evaluated input must be non-empty and n_clusters must not exceed its row count.
  • cluster_id values are query-local labels. Do not attach permanent business meaning to their numeric values.
  • Cluster sizes can differ slightly between runs, because centroid sums accumulate with floating-point atomics; the native seed is fixed at 0 and cannot be set.