Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.ocient.com/llms.txt

Use this file to discover all available pages before exploring further.

supports these machine learning models for clustering and dimension reduction. To create the model, use the CREATE MLMODEL syntax. For details, see CREATE MLMODEL.
Model option names are case-sensitive.

​
Principal Component Analysis

Model Type: PRINCIPAL COMPONENT ANALYSIS Principal Component Analysis (PCA) is typically not used as a model on its own. PCA is most commonly used on the inputs to other models. PCA serves two purposes.
  1. PCA normalizes all numeric feature data. Some types of models are sensitive to the scale of numeric features, and when different features have different scales, the results end up skewed. PCA normalizes all features to the same scale.
  2. PCA is used for dimensionality reduction. PCA computes linear combinations of the original features to put the most signal into a smaller number of new features.
The input result set when creating a PCA model is N numeric columns, which are all features. There is no label or dependent variable. After you create a PCA model, the sys.principal_component_analysis_models system catalog table contains information on the percentage of the signal in each PCA feature. You can use this information to figure out how many of the output features to keep. Also, after you create the model, you can see its details by querying the sys.machine_learning_models and sys.machine_learning_model_options system catalog tables.

​
Principal Component Analysis and Logistic Regression

You can also use PCA models as inputs to other models. For example, if you have three features and you want to use PCA to reduce the number to two features, you can execute the following SQL statements.
SQL
CREATE MLMODEL reduceTo2
TYPE PRINCIPAL COMPONENT ANALYSIS ON (
  SELECT
    c1,
    c2,
    c3
  FROM public.my_table
);
You can use this model as input for another model, for example, logistic regression.
SQL
CREATE MLMODEL binaryClass
TYPE LOGISTIC REGRESSION ON (
  SELECT
    reduceTo2(c1, c2, c3, 1),
    reduceTo2(c1, c2, c3, 2),
    label
  FROM …
);
To correctly use this model later, you must pass the original features through the PCA model when you execute the logistic regression model.
SQL
SELECT
  binaryClass(
    reduceTo2(x1, x2, x3, 1),
    reduceTo2(x1, x2, x3, 2)
  )
FROM … ;
Similarly, to create a PCA analysis over four variables, execute this SQL statement:
SQL
CREATE MLMODEL my_model
TYPE PRINCIPAL COMPONENT ANALYSIS ON (
  SELECT
    c1,
    c2,
    c3,
    c4
  FROM public.my_table
);

​
Model Options

featureArray — If you set this option to true, the model expects only one array-type column as input instead of multiple columns of training data. Each array row in the input column must be of the same size. Regardless of whether you use this option, the model treats training data the same with labeling and weight scoring. The default value is false. loadBalance — If you set this option, the database appends the USING load_balance_shuffle = <value> clause to all intermediate SQL queries the model executes during training where value is the specified option value (true or false). The default value is unspecified. In this case, the database does not add this clause. queryInternalParallelism — If you set this option, the database appends the USING parallelism = <value> clause to all intermediate SQL queries the model executes during training where value is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause. skipDropTable — If you set this option to false, the database deletes any intermediate tables created during model training. If you set this option to true, the database prevents the deletion of any intermediate tables created during model training. The default value is false.

​
K-Means Clustering

Model Type: KMEANS K-means is an unsupervised clustering algorithm. All of the columns in the input result set are features, and there is no label. All the input columns must be numeric. The algorithm finds k points such that all points are classified by the closest k points. Distance calculations are Euclidean by default.

​
Model Options

​
Required

k — This option must be a positive integer. The option specifies the algorithm for how many clusters to make.

​
Optional

epsilon — If you specify this option, the value must be a valid positive floating point value. When the maximum distance that a centroid moves from one iteration of the algorithm to the next is less than this value, the algorithm terminates. This parameter defaults to 1e-4. normalize — If you set this option to true, the model normalizes the data before the start of training. The default value is true. featureArray — If you set this option to true, the model expects only one array-type column as input instead of multiple columns of training data. Each array row in the input column must be of the same size. Regardless of whether you use this option, the model treats training data the same with labeling and weight scoring. The default value is false. loadBalance — If you set this option, the database appends the USING load_balance_shuffle = <value> clause to all intermediate SQL queries the model executes during training where value is the specified option value (true or false). The default value is unspecified. In this case, the database does not add this clause. queryInternalParallelism — If you set this option, the database appends the USING parallelism = <value> clause to all intermediate SQL queries the model executes during training where value is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause. skipDropTable — If you set this option to false, the database deletes any intermediate tables created during model training. If you set this option to true, the database prevents the deletion of any intermediate tables created during model training. The default value is false.

​
Execute the Model

Create a K-means model with 8 clusters.
SQL
CREATE MLMODEL my_model
TYPE KMEANS
ON (
  SELECT
    x1,
    x2,
    x3,
    x4
  FROM public.my_table
)
options(
  'k' -> '8'
);
Also, after you create the model, you can see its details by querying the sys.machine_learning_models and sys.machine_learning_model_options system catalog tables. Because there are no labels for clusters, when you execute this function after training with the same number (and same order) of features as input, the result is an integer that specifies the cluster to which the point belongs.
SQL
SELECT my_model(x1, x2, x3, x4) FROM my_table;
After you execute a model, you can find the details about the execution results in the sys.k_means_models system catalog table. For details, see the description of the associated system catalog tables in the Machine Learning section in the System Catalog.

​
Gaussian Mixture

Model Type: GAUSSIAN MIXTURE MODEL Gaussian Mixture Models (GMMs) find the mixture of N Gaussian distributions that best fit the data. The most common use is unsupervised clustering, but there are some key differences compared to K-means clustering:
  • GMMs can handle clusters that are not circular, i.e., clusters in the shape of ovals or higher-dimensional analogs with variances in different directions.
  • Clusters can have an arbitrary rotation, i.e., they can have covariance.
  • GMMs can handle instances when clusters are not as equally likely. If a point is right between two clusters, it is more likely to be more common in the training data.
  • GMMs can show the probability of a new point belonging to each cluster instead of providing only a single cluster value.
GMMs generally handle more complex data than K-means models, but they require more training and processing time during execution.

​
Model Options

​
Required

numDistributions — Must be a positive integer. This value specifies the number of clusters of Gaussian distributions for the model to make.

​
Optional

epsilon — If you specify this option, the value must be a valid positive floating point value. When the maximum distance that the entire best model moves in its n-dimensional space is less than this value, the algorithm terminates. This parameter defaults to 1e-8 (0.00000001 as a decimal number). normalize — If you set this option to true, the model automatically computes the mean and standard deviation of each feature and uses them to normalize the data during training. Defaults to true. featureArray — If you set this option to true, the model expects only one array-type column as input instead of multiple columns of training data. Each array row in the input column must be of the same size. Regardless of whether you use this option, the model treats training data the same with labeling and weight scoring. The default value is false. loadBalance — If you set this option, the database appends the USING load_balance_shuffle = <value> clause to all intermediate SQL queries the model executes during training where value is the specified option value (true or false). The default value is unspecified. In this case, the database does not add this clause. queryInternalParallelism — If you set this option, the database appends the USING parallelism = <value> clause to all intermediate SQL queries the model executes during training where value is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause. skipDropTable — If you set this option to false, the database deletes any intermediate tables created during model training. If you set this option to true, the database prevents the deletion of any intermediate tables created during model training. The default value is false.

​
Execute the Model

Create a GMM with three clusters of Gaussian distributions.
SQL
CREATE MLMODEL gmm
TYPE GAUSSIAN MIXTURE MODEL
ON (
  SELECT
    x,
    y,
    z
  FROM my_table
)
options(
  'numDistributions' -> '3'
);
Also, after you create the model, you can see its details by querying the sys.machine_learning_models and sys.machine_learning_model_options system catalog tables. When you execute this function after training with the same number (and same order) of features as input, the result is an integer that specifies the probable cluster to which the point belongs.
SQL
SELECT gmm(x,y,z) FROM my_table;
After you execute a model, you can find the details about the execution results in the sys.gaussian_mixture_models system catalog table. For details, see the description of the associated system catalog tables in the Machine Learning section in the System Catalog.

​
Linear Discriminant Analysis

Model Type: LINEAR DISCRIMINANT ANALYSIS Linear Discriminant Analysis (LDA) is a dimension-reduction technique similar to PCA. While PCA is unsupervised, LDA can classify and use class labels to find linear combinations of features that best separate the classes. The input result set when you create an LDA model is N numeric columns that are all features. The last column is a class label and can be any data type. After you create an LDA model, the sys.linear_discriminant_analysis_models system catalog table contains information on the percentage of the signal that is in each LDA feature. You can use this information to figure out how many of the output features to keep. Also, after you create the model, you can see its details by querying the sys.machine_learning_models and sys.machine_learning_model_options system catalog tables.

​
Linear Discriminant Analysis and Logistic Regression

You can use LDA models as inputs to other models. For example, to reduce the number of features from three to two using LDA, you can execute these SQL statements.
SQL
CREATE MLMODEL reduceTo2
TYPE LINEAR DISCRIMINANT ANALYSIS ON (
  SELECT
    c1,
    c2,
    c3,
    label
  FROM public.my_table
);
You can use this model as input for another model, for example, logistic regression.
SQL
CREATE MLMODEL binaryClass
TYPE LOGISTIC REGRESSION ON (
  SELECT
    reduceTo2(c1, c2, c3, 1),
    reduceTo2(c1, c2, c3, 2),
    label
  FROM …
  );
To correctly use this model later, you must pass the original features through the LDA model when you execute the logistic regression model.
SQL
SELECT
  binaryClass(
    reduceTo2(x1, x2, x3, 1),
    reduceTo2(x1, x2, x3, 2)
  )
FROM … ;
Similarly, to create an LDA model over four variables, execute this SQL statement.
SQL
CREATE MLMODEL my_model
TYPE LINEAR DISCRIMINANT ANALYSIS ON (
  SELECT
    c1,
    c2,
    c3,
    c4,
    label
  FROM public.my_table
);

​
Model Options

normalize — If you set this option to true, the model automatically computes the mean and standard deviation of each feature and uses them to normalize the data during training. Defaults to true. featureArray — If you set this option to true, the model expects only one array-type column as input instead of multiple columns of training data. Each array row in the input column must be of the same size. Regardless of whether you use this option, the model treats training data the same with labeling and weight scoring. The default value is false. loadBalance — If you set this option, the database appends the USING load_balance_shuffle = <value> clause to all intermediate SQL queries the model executes during training where value is the specified option value (true or false). The default value is unspecified. In this case, the database does not add this clause. queryInternalParallelism — If you set this option, the database appends the USING parallelism = <value> clause to all intermediate SQL queries the model executes during training where value is the specified positive integer value. The default value is unspecified. In this case, the database does not add this clause. skipDropTable — If you set this option to false, the database deletes any intermediate tables created during model training. If you set this option to true, the database prevents the deletion of any intermediate tables created during model training. The default value is false.

​
Execute the Model

When you execute the trained LDA model, you must provide the same original input features in the same order, followed by a positive integer argument that specifies the LDA component to return. The LDA component index starts at 1.
SQL
SELECT
  my_model(col1, col2, col3, col4, 2) as component2,
  my_model(col1, col2, col3, col4, 3) as component3,
FROM public.my_table;

​
Bibliography

Bahmani, Bahman, Benjamin Moseley, Andrea Vattani, Ravi Kumar, and Sergei Vassilvitskii. “Scalable K-Means++.” Proceedings of the VLDB Endowment 5, no. 7 (2012): 622–33. https://doi.org/10.14778/2180912.2180915. Classification Models Other Models Machine Learning Models

​

Last modified on May 21, 2026