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. Substitute your own relations and column names.

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 a real child; metadata validation resolves a registered table or view for the same role instead. See Vector Inputs for the relation identity rules and the ID, dense-vector type, null, finite-value, and runtime-dimension contract.

Role	Required	Validation reference	Description
input	yes	table	Dense-vector rows consumed by this operation.

Vector element types

Element type	Valid metrics
Float32	l2_expanded, l2_sqrt_expanded
Float64	l2_expanded, l2_sqrt_expanded

Arguments and options

Scalar SQL arguments

Argument	Type	Required	Description
n_clusters	integer	yes	Number of clusters fitted for this one statement.
metric	enum ("l2_expanded", "l2_sqrt_expanded")	no	L2 distance form used while fitting and assigning the current input relation.
max_iter	integer	no	Maximum fitting iterations for this invocation.
tol	number	no	Non-negative convergence tolerance used by the fitted model.
n_init	integer	no	Number of initialization attempts performed within this call.
init	enum ("kmeans++", "random")	no	Centroid initialization strategy for this fitted model.

SQL value argument schemas

Argument	Required	Literal shape	Default	Constraints	Description
init	no	string	"kmeans++"	one of "kmeans++", "random"	Centroid initialization strategy for this fitted model.
max_iter	no	integer	100	minimum 1; maximum 2147483647	Maximum fitting iterations for this invocation.
metric	no	string	"l2_expanded"	one of "l2_expanded", "l2_sqrt_expanded"	L2 distance form used while fitting and assigning the current input relation. Supported element/metric combinations: Float32: l2_expanded, l2_sqrt_expanded; Float64: l2_expanded, l2_sqrt_expanded.
n_clusters	yes	integer	No default	minimum 1; maximum 2147483647	Number of clusters fitted for this one statement.
n_init	no	integer	1	minimum 1; maximum 2147483647	Number of initialization attempts performed within this call.
tol	no	number	0.0001	minimum 0	Non-negative convergence tolerance used by the fitted model.

Vector binding shapes

Each relation subquery must project a non-null id field followed by either one or more non-null feature fields of a supported element type (Float32, Float64) or one non-null list vector field named vector.

For wide vectors, the projection order defines the feature dimensions. A list vector relation must contain no feature field beside id and vector.

Output

Column	Type	Nullable	Description
row_ordinal	UInt64	no	Zero-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.
id	same_as_input.id	no	Logical ID copied from the input relation.
cluster_id	Int32	no	Query-local numeric assignment label, not a stable business or topic identifier.

Concrete schemas are call-specific. Run gpu_validate_call against registered relations to inspect the output schema after the actual ID types and literal options are validated.

Examples

Amazon Reviews

This example uses the Amazon Reviews demo dataset.

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

A support analyst wants to inspect products whose complaints changed between 2021 and 2022. The query selects 99,539 sampled reviews rated at most two stars of headphones and earbuds, groups their BGE-M3 vectors into 24 clusters, and counts each product's reviews in those clusters by year. Accessories are excluded before fitting.

A windowed sum supplies each product's annual denominator without fitting a second model. The result keeps products with at least 30 sampled low-rating reviews in each year and a cluster with at least ten reviews in 2022 whose share rose by more than ten percentage points. There are 155 products with enough sampled reviews in both years. The result joins back to the catalog and an original review.

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;

The first three rows captured on 2026-09-28 were:

Product (shortened)	Cluster	2021 cluster / product total	2022 cluster / product total	Change
Altec Lansing NanoPods, royal blue	7	1 / 42 (2.38%)	10 / 33 (30.30%)	+27.92 pp
Jabra Talk 65	10	4 / 73 (5.48%)	14 / 58 (24.14%)	+18.66 pp
Sony WH-CH510, blue	8	6 / 67 (8.96%)	14 / 55 (25.45%)	+16.50 pp

The NanoPods result links to review 14707192, which says “Second set will not charge.” A support analyst now has a product, a measured change within the sampled low-rating reviews, and an original report to investigate. The saved rows include the full text and product IDs for each result.

The query took a median 0.752 seconds over three runs. This capture used mixed execution: the vector input and KMeans ran on the GPU, with part of the subsequent relational work in DataFusion. See the capture conditions.

example_review is the 2022 review with the smallest ID for that product and cluster. It provides an audit link; it is not selected as a cluster's most representative review. Several reviews should be read before assigning a topic name.

The denominator contains sampled low-rating reviews. A ten-point increase in a cluster's share does not imply a ten-point increase in defects or in all customers' complaints. The model assigns one cluster per review even when the text discusses multiple issues. Cluster IDs are local to this fit, and the input ordering does not guarantee identical membership across GPU runs.

For a composition that derives its numerical features directly from raw review text, see KMeans followed by Top-K.

MerRec marketplace

This capture uses the MerRec marketplace interaction sample. The preparation query retained 1,919 users with at least 20 item_view events before the exclusive cutoff of 2023-10-21. Each of the 17 input features is the square root of that user's category item_view event share. The MerRec dataset page describes the snapshot preparation, including the mr_tastes relation.

Which category-specific campaign variants could be worth testing?

The query fits KMeans with eight clusters and n_init = 5 on those 17 transformed features. SQL squares the features before averaging them within each fitted group, then Top-K returns the three largest mean category shares. Each percentage below is an equal-user mean of that category's share of item_view events. It is not a share of all events in a group or a proportion of users.

Query-local cluster	Top category	Second category	Third category
0	Men 62.3%	Women 15.1%	Sports & outdoors 5.0%
1	Toys & Collectibles 79.8%	Electronics 4.5%	Women 2.7%
2	Women 77.0%	Men 5.1%	Home 2.9%
3	Electronics 67.7%	Toys & Collectibles 6.4%	Other 4.5%
4	Toys & Collectibles 22.4%	Women 18.2%	Books 12.0%
5	Kids 69.7%	Women 13.5%	Toys & Collectibles 5.5%
6	Home 46.3%	Women 15.5%	Vintage & collectibles 13.2%
7	Beauty 55.5%	Women 22.3%	Home 5.5%

Cluster 0 is weighted toward Men at 62.3%, with Women at 15.1%. Cluster 5 is weighted toward Kids at 69.7%, with Women at 13.5%. Cluster 4 has a more distributed profile: its leading category, Toys & Collectibles, is 22.4%. These differences can guide which category-specific campaign variants a marketplace team tests. This capture has no conversion outcomes and measures no campaign uplift.

The query returns no group sizes or per-user assignments. Cluster IDs belong to this fit and are not durable audience identifiers. A separate comparison against saved KMeans assignments matched all eight top-three profile means exactly after cluster-label matching. That cross-fit consistency supports this profile table only; it does not establish that the independent fits assigned each user to the same cluster.

The captured plan reported five native plan fragments and zero host DataFusion boundaries. Server query statistics reported 246 ms elapsed. The client Flight elapsed_s was 0.25340579298790544 seconds, including planning and receipt of the full result while excluding preparation. Both are single observations from a shared server, not isolated benchmarks. The JSON capture preserves the timing boundaries, query ID, exact SQL, output schema, all 24 result rows, and source hashes.

Exact captured SQL

-- Fit one query-local KMeans model, then rank each fitted group's mean category shares.
WITH assignments AS (
    SELECT id, cluster_id
    FROM cuvs_kmeans(
        input => (
            SELECT
                id,
                COALESCE(women, CAST(0 AS REAL)) AS women,
                COALESCE(toys, CAST(0 AS REAL)) AS toys,
                COALESCE(kids, CAST(0 AS REAL)) AS kids,
                COALESCE(men, CAST(0 AS REAL)) AS men,
                COALESCE(home, CAST(0 AS REAL)) AS home,
                COALESCE(electronics, CAST(0 AS REAL)) AS electronics,
                COALESCE(beauty, CAST(0 AS REAL)) AS beauty,
                COALESCE(vintage, CAST(0 AS REAL)) AS vintage,
                COALESCE(books, CAST(0 AS REAL)) AS books,
                COALESCE(sports, CAST(0 AS REAL)) AS sports,
                COALESCE(other, CAST(0 AS REAL)) AS other,
                COALESCE(handmade, CAST(0 AS REAL)) AS handmade,
                COALESCE(garden, CAST(0 AS REAL)) AS garden,
                COALESCE(arts, CAST(0 AS REAL)) AS arts,
                COALESCE(pets, CAST(0 AS REAL)) AS pets,
                COALESCE(office, CAST(0 AS REAL)) AS office,
                COALESCE(tools, CAST(0 AS REAL)) AS tools
            FROM mr_tastes
            ORDER BY id
        ),
        n_clusters => 8,
        n_init => 5
    )
), profiles AS (
    SELECT
        a.cluster_id AS id,
        COALESCE(CAST(AVG(COALESCE(t.women, CAST(0 AS REAL)) * COALESCE(t.women, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS women,
        COALESCE(CAST(AVG(COALESCE(t.toys, CAST(0 AS REAL)) * COALESCE(t.toys, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS toys,
        COALESCE(CAST(AVG(COALESCE(t.kids, CAST(0 AS REAL)) * COALESCE(t.kids, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS kids,
        COALESCE(CAST(AVG(COALESCE(t.men, CAST(0 AS REAL)) * COALESCE(t.men, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS men,
        COALESCE(CAST(AVG(COALESCE(t.home, CAST(0 AS REAL)) * COALESCE(t.home, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS home,
        COALESCE(CAST(AVG(COALESCE(t.electronics, CAST(0 AS REAL)) * COALESCE(t.electronics, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS electronics,
        COALESCE(CAST(AVG(COALESCE(t.beauty, CAST(0 AS REAL)) * COALESCE(t.beauty, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS beauty,
        COALESCE(CAST(AVG(COALESCE(t.vintage, CAST(0 AS REAL)) * COALESCE(t.vintage, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS vintage,
        COALESCE(CAST(AVG(COALESCE(t.books, CAST(0 AS REAL)) * COALESCE(t.books, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS books,
        COALESCE(CAST(AVG(COALESCE(t.sports, CAST(0 AS REAL)) * COALESCE(t.sports, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS sports,
        COALESCE(CAST(AVG(COALESCE(t.other, CAST(0 AS REAL)) * COALESCE(t.other, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS other,
        COALESCE(CAST(AVG(COALESCE(t.handmade, CAST(0 AS REAL)) * COALESCE(t.handmade, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS handmade,
        COALESCE(CAST(AVG(COALESCE(t.garden, CAST(0 AS REAL)) * COALESCE(t.garden, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS garden,
        COALESCE(CAST(AVG(COALESCE(t.arts, CAST(0 AS REAL)) * COALESCE(t.arts, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS arts,
        COALESCE(CAST(AVG(COALESCE(t.pets, CAST(0 AS REAL)) * COALESCE(t.pets, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS pets,
        COALESCE(CAST(AVG(COALESCE(t.office, CAST(0 AS REAL)) * COALESCE(t.office, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS office,
        COALESCE(CAST(AVG(COALESCE(t.tools, CAST(0 AS REAL)) * COALESCE(t.tools, CAST(0 AS REAL))) AS REAL), CAST(0 AS REAL)) AS tools
    FROM assignments a
    JOIN mr_tastes t ON t.id = a.id
    GROUP BY a.cluster_id
)
SELECT
    ranked.id AS cluster_id,
    ranked.rank,
    CASE ranked.position
        WHEN 0 THEN 'Women'
        WHEN 1 THEN 'Toys & Collectibles'
        WHEN 2 THEN 'Kids'
        WHEN 3 THEN 'Men'
        WHEN 4 THEN 'Home'
        WHEN 5 THEN 'Electronics'
        WHEN 6 THEN 'Beauty'
        WHEN 7 THEN 'Vintage & collectibles'
        WHEN 8 THEN 'Books'
        WHEN 9 THEN 'Sports & outdoors'
        WHEN 10 THEN 'Other'
        WHEN 11 THEN 'Handmade'
        WHEN 12 THEN 'Garden & Outdoor'
        WHEN 13 THEN 'Arts & Crafts'
        WHEN 14 THEN 'Pet Supplies'
        WHEN 15 THEN 'Office'
        WHEN 16 THEN 'Tools'
    END AS category,
    ranked.value AS mean_view_share
FROM cuvs_top_k(
    input => (
        SELECT id, women, toys, kids, men, home, electronics, beauty, vintage,
               books, sports, other, handmade, garden, arts, pets, office, tools
        FROM profiles
        ORDER BY id
    ),
    k => 3,
    select => 'max'
) ranked
ORDER BY cluster_id, rank;

Limits

• Validation resolves named tables or views and reads schemas only; it does not execute relation scans or GPU work.
• Execution relation arguments require parenthesized subqueries; dry-run validation accepts registered named relations only.
• Fits a model and returns assignments within one statement; 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.

To dry-run validate relation metadata, column types, and options without execution, see gpu_validate_call.