The ML.CENTROIDS function

This document describes theML.CENTROIDS function, which lets you returninformation about thecentroidsin ak-means model.

Syntax

ML.CENTROIDS(  MODEL `PROJECT_ID.DATASET.MODEL`,  STRUCT([,STANDARDIZE AS standardize]))

Arguments

ML.CENTROIDS takes the following arguments:

  • PROJECT_ID: your project ID.
  • DATASET: the BigQuery dataset that containsthe model.
  • MODEL: the name of the model.
  • STANDARDIZE: aBOOL value that specifies whether thecentroid features should be standardized to assume that all features have amean of0 and a standard deviation of1. Standardizing the features allowsthe absolute magnitude of the values to be compared to each other. The defaultvalue isFALSE.

Output

ML.CENTROIDS returns the following columns:

  • trial_id: anINT64 value that contains the hyperparameter tuning trial ID.This column is only returned if you ran hyperparameter tuning when creatingthe model.
  • centroid_id: anINT64 value that contains the centroid ID.
  • feature: aSTRING value that contains the feature column name.
  • numerical_value: aFLOAT64 value that contains the feature value for thecentroid thatcentroid_id identifies if the column identified by thefeature value is numeric. Otherwise,numerical_value isNULL.

  • categorical_value: anARRAY<STRUCT> value that contains informationabout categorical features. Each struct contains the following fields:

    • categorical_value.category: aSTRING value that contains the name ofeach category.
    • categorical_value.value: aSTRING value that contains the value ofcategorical_value.category for the centroid thatcentroid_ididentifies.

  • geography_value: aSTRING value that contains thecategorical_value.category value for the centroid thatcentroid_ididentifies if the column identified by thefeature value is of typeGEOGRAPHY. Otherwise,geography_value value isNULL.

The output contains one row per feature per centroid.

Examples

The following examples show how to useML.CENTROIDS with and without thestandardize argument.

Without standardization

Numerical features

The following example retrieves centroid information from the modelmydataset.my_kmeans_model in your default project. This model onlycontains numerical features.

SELECT*FROMML.CENTROIDS(MODEL`mydataset.my_kmeans_model`)

This query returns results like the following:

+-------------+-------------------+----------------------+---------------------+| centroid_id | feature           | numerical_value      | categorical_value   |+-------------+-------------------+----------------------+---------------------+|           3 | x_coordinate      |            3095929.0 |                  [] ||           3 | y_coordinate      | 1.0089726307692308E7 |                  [] ||           2 | x_coordinate      |        3117072.65625 |                  [] ||           2 | y_coordinate      | 1.0083220745833334E7 |                  [] ||           1 | x_coordinate      |    3259947.096227731 |                  [] ||           1 | y_coordinate      | 1.0105690227895036E7 |                  [] ||           4 | x_coordinate      |   3109887.9056603773 |                  [] ||           4 | y_coordinate      | 1.0057112358490566E7 |                  [] |+-------------+-------------------+----------------------+---------------------+

Categorical features

The following example retrieves centroid information from the modelmydataset.my_kmeans_model in your default project. This model containscategorical features.

SELECT*FROMML.CENTROIDS(MODEL`mydataset.my_kmeans_model`)ORDERBYcentroid_id;

This query returns results like the following:

+-------------+-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| centroid_id | feature           |numerical_value| categorical_value                                                                                                                                                                                                                                              |+-------------+-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|           1 | department        |          NULL | [{"category":"Medieval Art","feature_value":"1.0"}]                                                                                                                                                                                                            ||           1 | medium            |          NULL | [{"category":"Iron","feature_value":"0.21602160216021601"},{"category":"Glass, ceramic","feature_value":"0.3933393339333933"},{"category":"Copper alloy","feature_value":"0.39063906390639064"}]                                                               ||           2 | medium            |          NULL | [{"category":"Wood, gesso, paint","feature_value":"0.15"},{"category":"Carnelian","feature_value":"0.2692307692307692"},{"category":"Papyrus, ink","feature_value":"0.2653846153846154"},{"category":"Steatite, glazed","feature_value":"0.3153846153846154"}] ||           2 | department        |          NULL | [{"category":"Egyptian Art","feature_value":"1.0"}]                                                                                                                                                                                                            ||           3 | medium            |          NULL | [{"category":"Faience","feature_value":"1.0"}]                                                                                                                                                                                                                 ||           3 | department        |          NULL | [{"category":"Egyptian Art","feature_value":"1.0"}]                                                                                                                                                                                                            ||           4 | medium            |          NULL | [{"category":"Steatite","feature_value":"1.0"}]                                                                                                                                                                                                                ||           4 | department        |          NULL | [{"category":"Egyptian Art","feature_value":"1.0"}]                                                                                                                                                                                                            ||           5 | medium            |          NULL | [{"category":"Red quartzite","feature_value":"0.20316027088036118"},{"category":"Bronze or copper alloy","feature_value":"0.3476297968397291"},{"category":"Gold","feature_value":"0.4492099322799097"}]                                                       ||           5 | department        |          NULL | [{"category":"Egyptian Art","feature_value":"1.0"}]                                                                                                                                                                                                            |+-------------+-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Numerical and categorical features

The following are the results from the same query against a k-means model withboth numerical and categorical features.

+-------------+--------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| centroid_id |      feature       |  numerical_value  | categorical_value                                                                                                                                                                                                                                                                 |+-------------+--------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|           1 | start_station_name |              NULL | [{"category":"Toomey Rd @ South Lamar","value":"0.5714285714285714"},{"category":"State Capitol @ 14th & Colorado","value":"0.42857142857142855"}]                                                                                                                                ||           1 | duration_minutes   | 9.142857142857142 | []                                                                                                                                                                                                                                                                                ||           2 | duration_minutes   |               9.0 | []                                                                                                                                                                                                                                                                                ||           2 | start_station_name |              NULL | [{"category":"Rainey @ River St","value":"0.14285714285714285"},{"category":"11th & San Jacinto","value":"0.42857142857142855"},{"category":"ACC - West & 12th Street","value":"0.14285714285714285"},{"category":"East 11th St. at Victory Grill","value":"0.2857142857142857"}] |+-------------+--------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

With standardization

The following example retrieves centroid information from the modelmydataset.my_kmeans_model in your default project. The query in this exampleassumes that all features have a mean of0 and a standard deviation of1.

SELECT*FROMML.CENTROIDS(MODEL`mydataset.my_kmeans_model`,STRUCT(TRUEASstandardize))

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.