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 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-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-anomaly-detection-1csvm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-classification-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-classification-text-analysis-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-partitioned-models-svm.sql |
CLASSIFICATION |
ALGO_SUPPORT_VECTOR_MACHINES |
oml4sql-classification-regression-xgboost.sql |
CLASSIFICATION |
ALGO_XGBOOST |
oml4sql-clustering-expectation-maximization.sql |
CLUSTERING |
ALGO_EXPECTATION_MAXIMIZATION |
oml4sql-clustering-kmeans.sql |
CLUSTERING |
ALGO_KMEANS |
oml4sql-clustering-kmeans-star-schema.sql |
CLUSTERING |
ALGO_KMEANS |
oml4sql-clustering-o-cluster.sql |
CLUSTERING |
ALGO_O_CLUSTER |
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-singular-value-decomposition.sql |
FEATURE_EXTRACTION |
ALGO_SINGULAR_VALUE_DECOMP |
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-classification-regression-xgboost.sql |
REGRESSION |
ALGO_XGBOOST |
oml4sql-time-series-exponential-smoothing.sql |
TIME_SERIES |
ALGO_EXPONENTIAL_SMOOTHING |
Another example is oml4sql-attribute-importance.sql, which uses the DBMS_PREDICTIVE_ANALYTICS.EXPLAIN procedure to find the importance of attributes that independently impact the target attribute.
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-rextensible-algorithm-registration.sql |
CLASSIFICATION |
glm |
oml4sql-rextensible-association-rules.sql |
ASSOCIATION |
apriori |
oml4sql-rextensible-attribute-importance-via-rf.sql |
REGRESSION |
randomForest |
oml4sql-rextensible-glm.sql |
REGRESSION |
glm |
oml4sql-rextensible-kmeans.sql |
CLUSTERING |
kmeans |
oml4sql-rextensible-principal-components.sql |
FEATURE_EXTRACTION |
prcomp |
oml4sql-rextensible-regression-tree.sql |
REGRESSION |
rpart |
oml4sql-regression-r-neural-networks.sql |
REGRESSION |
nnet |