41.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 23c) 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 41-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-feature-extraction-cur.sql |
ATTRIBUTE_IMPORTANCE |
ALGO_CUR_DECOMPOSITION |
oml4sql-classification-decision-tree.sql |
CLASSIFICATION |
ALGO_DECISION_TREE |
oml4sql-cross-validation-decision-tree.sql |
CLASSIFICATION |
ALGO_DECISION_TREE |
oml4sql-classification-glm.sql |
CLASSIFICATION |
ALGO_GENERALIZED_LINEAR_MODEL |
oml4sql-time-series-mset.sql |
CLASSIFICATION |
ALGO_MSET_SPRT |
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-o-cluster.sql |
CLUSTERING |
ALGO_O_CLUSTER |
oml4sql-partitioned-models-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-classification-regression-xgboost.sql |
CLASSIFICATION |
ALGO_XGBOOST |
oml4sql-feature-extraction-text-analysis-esa.sql |
FEATURE_EXTRACTION |
ALGO_EXPLICIT_SEMANTIC_ANALYS |
oml4sql-feature-extraction-nmf.sql |
FEATURE_EXTRACTION |
ALGO_NONNEGATIVE_MATRIX_FACTOR |
oml4sql-feature-extraction-text-analysis-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_ANALYSIS |
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-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 |