Machine learning and other AI models can originate from many sources. You may use pre-built transformer models from other vendors, deploy native Python and R models from your data science team, or use GPU compute to train models that can be readily used in non-GPU environments. Your Oracle Database enables you to work with these models in a variety of ways to support your AI application development.
In this blog, we explore how Oracle Database and Autonomous Database enable you to bring your own model (BYOM) to the database for ease of model deployment. The ability to use these models through Oracle’s native SQL integration and REST APIs means that your enterprise applications can easily leverage machine learning models.
There are many types of models, produced from a range of tools and environments and represented in various formats. Here are just a few examples:
Let’s look at each of these and how you can use these with your database.
When it comes to solving business use cases using historical structured data, traditional machine learning algorithms are a long-proven technology that can produce accurate and efficient results.
Using Oracle Machine Learning in Oracle Database and Autonomous Database, you can build and use models directly in the database. This includes classification, regression, clustering, anomaly detection, time series, and other machine learning techniques. Using in-database models can result in higher performance and scalability by taking advantage of in-database optimizations and the elimination of data movement for in-database data, as well as ease of model deployment. You can bring these models to other Oracle Database or Autonomous Database instances using the Oracle Machine Learning model export and import capability. See the blog Top 5 Architectures for Enterprise Machine Learning with Oracle Database for a broader discussion of deployment options.
For example, you can export a one or more models using various filtering criteria, such as model name, algorithm name, class of algorithm, and even all models in the user’s schema. Here, we export all decision tree models to a file ‘dt_models’ in the directory ‘OMLDIR’ from the user OMLUSER1. Then, we import those models to another database remapping the schema from OMLUSER1 to another user OMLUSER2, along with the tablespace. The filename is augmented as shown during export.
EXECUTE dbms_data_mining.export_model(filename => 'dt_models',
directory => 'OMLDIR',
model_filter => 'ALGORITHM_NAME IN (''DECISION_TREE'')');
EXECUTE dbms_data_mining.import_model(filename => 'dt_models01.dmp',
directory => 'OMLDIR',
schema_remap => 'OMLUSER1': 'OMLUSER2',
tablespace_remap => 'USER1:USER2');
If you need real-time inferencing response for streaming and other applications via REST endpoints, you can deploy in-database classification, regression, clustering, and feature extraction models to Oracle Machine Learning Services on Autonomous Database Serverless. These models can originate from both Oracle Database and Autonomous Database instances – crossing the on-premises/cloud boundary.
OML Services enables key elements of MLOps requirements – supporting model management, deployment, and monitoring. It’s designed for streaming and real-time applications – often with millisecond response times. Unlike other solutions that require provisioning a VM for 24/7 availability, OML Services is provisioned and maintained as part of Autonomous Database Serverless, so users pay only the additional compute when producing actual predictions.
Python and R are among the top data science languages and environments for developing machine learning models. They enable users to produce models represented as Python or R objects. Often, solution developers persist these models in flat files and then load models explicitly to Python or R environments.
Using Oracle Machine Learning, you can instead bring these models to your database and store them using the datastore feature of OML4Py and OML4R. See How to store R and Python objects in Oracle Database to learn more about this capability.
Further, you can invoke your user-defined Python or R functions that load these models directly from the database for inferencing or other purposes. Using OML4Py or OML4R embedded execution, such user-defined functions can be run in database-spawned and managed Python or R engines. Apart from using Python or R APIs, you can invoke these functions from SQL, and on Autonomous Database, also from REST endpoints. Further, you can use third-party Python and R packages with your user-defined functions.
For example, we can create a native Python linear regression model and save it in the database using a datastore named ‘linear_regression_model’.
import oml
from sklearn import linear_model
mod = linear_model.LinearRegression()
X = iris[["PETAL_WIDTH"]]
y = iris[["PETAL_LENGTH"]]
lm.fit(X, y)
oml.ds.save(mod,name = "linear_regression_model")
Next, define a function ‘my_score_function’ that takes a Pandas DataFrame and the name of the datastore as arguments. This function loads the model from the datastore and returns the predictions. Then, store this function in the database in the Python script repository.
def my_score_function(dat,datastore):
import pandas as pd
import oml
obj_dict = oml.ds.load(name="linear_regression_model",to_globals=False)
regr = obj_dict["mod"]
pred = regr.predict(dat[['PETAL_WIDTH']])
return pd.concat([dat[['SPECIES', 'PETAL_LENGTH']],
oml.script.create("score_mod", func=score_mod, is_global=False)
Now, you can invoke your function using the data-parallel embedded Python execution function ‘pyqRowEval’. The input data is the table named ‘IRIS’, with the parameters list that includes the datastore name, the output format specification, the number of rows to process in each batch, and the name of the user-defined function. By enabling parallelism in the parameter list, multiple Python engines could be engaged in processing batches of 15 records at a time – in parallel for improved performance.
SELECT *
FROM table(pyqRowEval(
inp_nam => 'IRIS',
par_lst => '{"oml_input_type":"pandas.DataFrame", "oml_parallel_flag":true, "datastore": "linear_regression_model"}'
out_fmt => '{"SPECIES":"varchar2(10)","PETAL_LENGTH":"number","Pred_PETAL_LENGTH":"number"}',
row_num => 15,
scr_name => 'my_score_function'));
This is just one sequence of leveraging embedded Python (or R) execution with the database.
Not to get ahead of ourselves, but such Python and R models can also often be exported in ONNX format (discussed next), in which case you can deploy classification, regression, clustering, and feature extraction models to OML Services to use via REST endpoints in real-time applications. See Deploy an XGBoost Model using OML Services for an example of doing exactly this.
These same ONNX-format models can be imported directly in the database for use through the Oracle Machine Learning SQL prediction operators, which we discuss in the next section.
The Open Neural Network Exchange (ONNX) is an open format designed to represent machine learning models. It defines a common set of operators and a common file format to enable using models in a variety of ways. While the name ONNX includes “neural network,” it applies to a far wider range of model types, that is, those derived from non-neural network algorithms.
To enable using ONNX-format models for inferencing, ONNX Runtime is a high-performance engine for deploying ONNX-format models, which includes neural network and deep learning models as well as other types of machine learning models.
As for in-database models, you can deploy ONNX-format models to OML Services using REST endpoints. As noted earlier, OML Services enables inferencing from REST endpoints and is designed for streaming and real-time applications.
You can build a model using, for example, Torch or TensorFlow, even leveraging GPU resources to support compute and data intensive models like CNNs and RNNs. After converting to ONNX format and importing them to OML Services, you can use these models from the convenience of REST endpoints.
You can also import ONNX-format models to use the Oracle Database 23ai in-database ONNX Runtime. Such models require adding pre- and post-processing steps that are bundled with the ONNX-format model to enable model inputs and outputs to work seamlessly with the database.
Once imported, these models behave like native in-database models for inferencing so you can use the same integrated SQL prediction operators as you can for native in-database models.
Below is a customer churn classification model example that uses the DBMS_DATA_MINING package from OML4SQL. First, we retrieve our ONNX-format model bundle from object storage. We call it a bundle because it includes the pre- and post-processing steps required to be used with the in-database ONNX Runtime. Using the 'import_onnx_model' procedure, we provide the model name, model data (retrieved using 'get_object'), and metadata, which identifies this as a ‘classification’ model.
DECLARE
model_source BLOB := NULL;
BEGIN
model_source := DBMS_CLOUD.get_object(
credential_name => 'OBJ_STORE_CRED',
object_uri => 'https://objectstorage…bucketname/o/classification-churn-bundle.onnx');
DBMS_DATA_MINING.import_onnx_model(
model_name => "churn_model",
model_data => model_source,
metadata => JSON('{ function : "classification" }'));
END;
Now, we can use the prediction operator with this model as part of our SQL query to get a prediction for each customer in our customer table.
SELECT case_id, prediction(churn_model using *) AS prediction FROM CUSTOMERS
In addition to classification, you can also import ONNX-format regression and clustering models. However, there is another class of model you can bring to your database using ONNX format: transformers.
Transformers, or embedding models, play a central role in enabling semantic similarity search as you find in Oracle Database 23ai AI Vector Search. Transformers convert content, such as text, images, and audio, into a multi-dimensional vector representation.
A vector, also called an embedding, at its simplest is a sequence of numbers. These numbers – called dimensions – capture the important semantic aspects of the data. Thinking in human terms, we may think of a dog as having dimensions like breed, fur color, height, and weight. Thinking in computer terms, when using transformers, these dimensions form a semantic abstraction over an N-dimensional space, where N is often in the hundreds or thousands.
The closer one vector is to another, the more similar the content. There are many ways to compute distance, but the one you’re probably most familiar with is Euclidean distance. Other distance measures include cosine, Manhattan, dot product, and Hamming.
Consider the following scenario, which illustrates using transformers for semantic similarity search. We provide a data object, for example, text like “What is Autonomous Database?” We use the transformer, or embedding model, to convert this to a vector, or embedding. In advance, we populated a vector store using Oracle product documentation, blogs, and other collateral. Each of these documents was converted into one or more vectors and stored in a table. Computing the distance between our input vector and each of the vectors stored in our table, we can select the top results that have the closest distance to our input vector. From here, we can retrieve the documents or text chunks that most relate to our input vector.
With Oracle Database 23ai, you can load text transformer models directly into your database for use with AI Vector Search. If you’re a Python user, OML4Py provides classes in the oml.utils package to generate a portable ONNX-format model from supported Hugging Face transformers. This package automatically adds the pre- and post-processing steps for in-database use, allowing you to save the model to your database for use with the in-database ONNX Runtime. You can also save this model to a file if you want to import it to other databases or to create a snapshot of the model.
Here's an example that creates an embedding model from the Hugging Face sentence transfer ‘all-MiniLM-L6-v2’ and then saves it to the database with the name “sentence-transformer”, and to a file with the same name.
import oml
from oml.utils import EmbeddingModel
em = EmbeddingModel(model_name="sentence-transformers/all-MiniLM-L6-v2")
em.export2db("sentence-transformer")
em.export2file("sentence-transformer")
If you aren’t a Python user, you can also import such models using the DBMS_DATA_MINING package. As for traditional ML models, the model must have the appropriate pre- and post-processing steps. Here, we access the model from Object Storage, and use 'import_onnx_model', specifying the model name, model data, and that this is an embedding model.
DECLARE
model_source BLOB := NULL;
BEGIN
model_source := DBMS_CLOUD.get_object(
credential_name => 'OBJ_STORE_CRED',
object_uri =>
'https://objectstorage…bucketname/o/sentence-transformer-bundle.onnx');
DBMS_DATA_MINING.import_onnx_model(
model_name => "sentence-transformer",
model_data => model_source,
metadata => JSON('{ function : "embedding" }'));
END;
See Pre-built Embedding Generation model for Oracle Database 23ai for additional examples about using an Oracle-provided augmented version (“bundle”) of the popular Hugging Face all-MiniLM-L12-v2 transformer.
Now, the model is ready to use with AI Vector Search as a first-class database object. Consider the following example, where our objective is to get entertainment recommendations for movies, TV shows, and music based on movie scripts, TV show transcripts, and song lyrics produced in a specific year ordered by relevance of the content. We might ask for “the top 10 entertainment recommendations involving hopeful messaging around climate change that was produced in the last few years.”
We first need to vectorize the text content. The following code uses the data from our ‘entertainment_content’ table to create a table with the column ‘embedding’. For this, we use the AI Vector Search SQL function 'VECTOR_EMBEDDING' and specify the ‘sentence_transformer’ model we loaded above and the column we want to convert to a 'VECTOR' type – ‘content_text’. We call this new column ‘embedding’. (For this example, we’re omitting chunking the text for finer grained accuracy.)
CREATE TABLE entertainment_vector_table AS
SELECT id, content_title, content_type, year, content_text,
VECTOR_EMBEDDING(sentence_transformer
USING content_text) AS embedding
FROM entertainment_content;
For performance and scalability, AI Vector Search supports the creation of vector indexes. Here, we create the index vector_idx on our embedding column using the in-memory neighbor graph index.
CREATE VECTOR INDEX vector_idx ON
entertainment_vector_table (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH;
Now, we’re ready to issue a query to find the top 10 content recommendations. We first convert the request into a vector embedding using the sentence transformer, filter for content produced by year, and then find the content that most closely matches.
SELECT id, content_title, content_type FROM entertainment_vector_table t
WHERE t.year > 2021
ORDER BY VECTOR_DISTANCE(
embedding,
VECTOR_EMBEDDING(sentence_transformer
USING :request)) ASC
FETCH APPROXIMATE FIRST 10 ROWS ONLY;
Note that we’re combining structured data filtering using the WHERE clause to find content released since 2021, and then ordering the result by vector distance based on our request. Using the 'APPROXIMATE' clause, we’re leveraging the vector index for high-performance retrieval.
Generative AI encompasses a wide range of models that can generate new content such as text, code, images, audio, and even video. These models produce highly realistic and complex content that mimics human creativity. Generative AI models are often general-knowledge tools that work across industries and problem domains. One type of generative AI model is a large language model (LLM), which supports natural language “understanding” and content generation.
LLMs enable various use cases, whether generating content for an email, answering questions on specific topics, summarizing articles, or characterizing the sentiment of a social media post. They can even generate code in popular programming languages, like Python, R, and Java, and query languages like SQL.
The Select AI feature of Oracle Autonomous Database enables you to leverage LLMs from multiple AI providers and to develop powerful applications using generative AI for the use cases cited above and many others – all using SQL and PL/SQL. Further, Select AI enables retrieval augmented generation (RAG) to work with your database metadata to generate SQL that can run on your database, or to search content from your vector store, like Oracle AI Vector Search, for more accurate and up-to-date LLM-generated responses.
Select AI enables several “actions” for LLM interaction based on the user-provided prompt:
You specify an AI profile, which identifies the AI provider and LLM you want to use, among other settings. Then you can simply ask your question on the SQL command prompt, like:
SQL> SELECT AI what are net sales for each quarter in 2024
SQL> SELECT AI CHAT what is Autonomous Database
The first example uses ‘runsql’ to query data from your database using database metadata. The second example uses the chat function as a passthrough to the LLM to use its general knowledge to learn more about Oracle Autonomous Database.
Or, you can use your LLM from PL/SQL via the DBMS_CLOUD_AI.GENERATE procedure.
DECLARE
v_response VARCHAR2(4000);
BEGIN
v_response := DBMS_CLOUD_AI.GENERATE(prompt => 'Generate email using the JSON specification
{"task": "Pick 5 great things to do at the location",
"task_rules": "
1. Encourage the customer to do these things. Really sell them as to why it would be right for them
2. Consider all the information about the customer that''s provided, including family and dog situation,
whether they have a car and income
3. Format the result with emojis and make it fun",
"last_name": "Mouly",
"first_name": "Jennine",
"location": "Paris, Île-De-France",
"age": 37,
"gender": "Female",
"has_kids": "NO",
"num_cars": 2,
"income_level": "Above 110,000",
"dog_owner": "NO"}’,
profile_name => 'OPENAI’,
action => 'chat’);
DBMS_OUTPUT.PUT_LINE(v_response);
END;
This example generates an email to encourage a customer to check out activities at a location of interest to the customer. This prompt includes instructions to the LLM along with information about the customer to help the LLM generate a customized email with relevant recommendations.
As we’ve explored here, there are many types of models you can bring to your database that originated from a range of tools and environments. These include:
Oracle enables you to bring your own model to the database to use in a variety of ways to support your AI application development.
In our next blog on this topic, I’ll provide more insight on choosing among the BYOM options.
Check out these resources:
You can test drive these features using the easy-to-use Oracle LiveLabs environment. These two workshops can get you started:
Mark Hornick is Senior Director, Machine Learning and AI Product Management. Mark has more than 20 years of experience integrating and leveraging machine learning with Oracle software as well as working with internal and external customers to apply Oracle’s machine learning technologies. Mark is Oracle’s representative to the R Consortium and is an Oracle Adviser and founding member of the Analytics and Data Oracle User Community. He has been issued seven US patents. Mark holds a bachelor’s degree from Rutgers University and a master’s degree from Brown University, both in computer science. Follow him on Twitter and connect on LinkedIn.