A.1 About the OML4SQL Examples
The OML4SQL examples illustrate typical approaches to data preparation, algorithm selection, algorithm tuning, testing, and scoring.
You can learn a great deal about the OML4SQL application programming interface from the OML4SQL examples. The examples are simple. They include extensive inline comments to help you understand the code. They delete all temporary objects on exit so that you can run the examples repeatedly without setup or cleanup.
The OML4SQL examples are available on GitHub at https://github.com/oracle/oracle-db-examples/tree/master/machine-learning/sql/. Select the Database release (for example 23ai) to see the examples.
The OML4SQL examples create a
set of machine learning models in the user's schema. The
following table lists the file name of the example and the
mining_function
value and algorithm the example uses.
Table A-1 Models Created by Examples
File Name | MINING_FUNCTION | Algorithm |
---|---|---|
oml4sql-anomaly-detection-1class-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINE |
oml4sql-anomaly-detection-em.sql |
CLASSIFICATION |
ALGO_EXPECTATION_MAXIMIZATION |
oml4sql-association-rules.sql |
ASSOCIATION |
ALGO_APRIORI_ASSOCIATION_RULES |
oml4sql-classification-decision-tree.sql |
CLASSIFICATION |
ALGO_DECISION_TREE |
oml4sql-classification-glm.sql |
CLASSIFICATION |
ALGO_GENERALIZED_LINEAR_MODEL |
oml4sql-classification-naive-bayes.sql |
CLASSIFICATION |
ALGO_NAIVE_BAYES |
oml4sql-classification-neural-networks.sql |
CLASSIFICATION |
ALGO_NEURAL_NETWORK |
oml4sql-classification-random-forest.sql |
CLASSIFICATION |
ALGO_RANDOM_FOREST |
oml4sql-classification-regression-xgboost.sql |
CLASSIFICATION |
ALGO_XGBOOST |
oml4sql-classification-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-classification-text-analysis-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-clustering-expectation-maximization.sql |
CLUSTERING |
ALGO_EXPECTATION_MAXIMIZATION |
oml4sql-clustering-kmeanms-star-schema.sql |
CLUSTERING |
ALGO_KMEANS |
oml4sql-clustering-kmeans.sql |
CLUSTERING |
ALGO_KMEANS |
oml4sql-clustering-ocluster.sql |
CLUSTERING |
ALGO_O_CLUSTER |
oml4sql-cross-validation-decision-tree.sql |
CLASSIFICATION |
ALGO_DECISION_TREE |
oml4sql-feature-extraction-cur.sql |
ATTRIBUTE_IMPORTANCE |
ALGO_CUR_DECOMPOSITION |
oml4sql-feature-extraction-nmf.sql |
FEATURE_EXTRACTION |
ALGO_NONNEGATIVE_MATRIX_FACTOR |
oml4sql-feature-extraction-svd.sql |
FEATURE_EXTRACTION |
ALGO_SINGULAR_VALUE_DECOMP |
oml4sql-feature-extraction-text-mining-esa.sql |
FEATURE_EXTRACTION |
ALGO_EXPLICIT_SEMANTIC_ANALYS |
oml4sql-feature-extraction-text-mining-nmf.sql |
FEATURE_EXTRACTION |
ALGO_NONNEGATIVE_MATRIX_FACTOR |
oml4sql-feature-extraction-text-term-extraction.sql |
FEATURE_EXTRACTION |
ALGO_EXPLICIT_SEMANTIC_ANALYSIS |
oml4sql-partitioned-models-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-regression-glm.sql |
REGRESSION |
ALGO_GENERALIZED_LINEAR_MODEL |
oml4sql-regression-neural-networks.sql |
REGRESSION |
ALGO_NEURAL_NETWORK |
oml4sql-regression-random-forest.sql |
REGRESSION |
ALGO_RANDOM_FOREST |
oml4sql-regression-svm.sql |
REGRESSION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-singular-value-decomposition.sql |
REGRESSION |
ALGO_SINGULAR_VALUE_DECOMPOSITION |
oml4sql-survival-analysis-xgboost.sql |
REGRESSION |
ALGO_XGBOOST |
oml4sql-time-series-esm-auto-model-search.sql |
TIME_SERIES |
ALGO_EXPONENTIAL_SMOOTHING |
oml4sql-time-series-exponential-smoothing.sql |
TIME_SERIES |
ALGO_EXPONENTIAL_SMOOTHING |
oml4sql-time-series-mset.sql |
CLASSIFICATION |
ALGO_MSET_SPRT |
oml4sql-time-series-regression-dataset.sql |
- | This is a dataset to construct time series regression model. |
oml4sql-time-series-regression.sql |
TIME_SERIES and
REGRESSION |
Uses ALGO_EXPONENTIAL_SMOOTHING ,
ALGO_GENERALIZED_MODEL , and
ALGO_XGBOOST |
A few examples other than those listed in the table above are:
oml4sql-attribute-importance.sql
, which uses the
DBMS_PREDICTIVE_ANALYTICS.EXPLAIN
procedure to find the
importance of attributes that independently impact the target attribute.
oml4sql-feature-extraction-text-term-extraction.sql
example,
which uses the CTX.DDL
package for text extraction.
Another set of examples demonstrates the use of the
ALGO_EXTENSIBLE_LANG
algorithm to register R language functions
and create R models. The following table lists the R Extensibility examples. It
shows the file name of the example and the MINING_FUNCTION value and R function
used.
File Name | MINING_FUNCTION | R Function |
---|---|---|
oml4sql-r-extensible-algorithm-registration.sql |
CLASSIFICATION |
glm |
oml4sql-r-extensible-association-rules.sql |
ASSOCIATION |
apriori |
oml4sql-r-extensible-attribute-importance-via-rf.sql |
REGRESSION |
randomForest |
oml4sql-r-extensible-glm.sql |
REGRESSION |
glm |
oml4sql-r-extensible-kmeans.sql |
CLUSTERING |
kmeans |
oml4sql-r-extensible-principal-components.sql |
FEATURE_EXTRACTION |
prcomp |
oml4sql-r-extensible-regression-tree.sql |
REGRESSION |
rpart |
oml4sql-r-extensible-regression-neural-networks.sql |
REGRESSION |
nnet |
Parent topic: Oracle Machine Learning for SQL Examples