This post is the first of a three-part series where I’m going to show you how to use pre-configured machine learning models to embed vectors into the Oracle Database. Before I dive into how to load a pre-trained machine learning models with ONNX, it is helpful to know what is an ONNX file? And how do you create one to use with the Oracle Database. What is an ONNX model? ONNX is an open-source format designed for machine-learning models. Ensuring cross-platform compatibility and supports major languages and frameworks, facilitating easy and efficient model exchanges. ONNX stands for Open Neural Network Exchange. It is a popular choice that enables deployment, integration, and exchange of models consistently across platforms that support cloud, web, edge, and mobile experiences on all the major platforms. While the name implies neural networks, the framework also encompasses models that employ other algorithms. Many leading machine learning development frameworks, such as TensorFlow, Pytorch, and Scikit-learn to name a few, offer the capability to convert models into the ONNX format. ONNX models offer flexibility to export and import models in many languages, such as Python, C++, or C#. Oracle Database 23ai (latest pending-release) supports importing these externally trained ONNX files into the Oracle Database and perform in-database scoring, that is, applying a machine learning model to new data, through ONNX Runtime. ONNX Runtime is an inference engine for ONNX models. With the ONNX Runtime implementation, you can run machine learning models efficiently in ONNX format. An imported ONNX model is represented as an in-database object, similar to the Oracle Machine Learning (OML) model objects. With the appropriate permissions, ONNX models can be imported for machine learning tasks and used to score models using OML scoring SQL operators. An example of importing an ONNX model is as follows: Begin DBMS_DATA_MINING.IMPORT_ONNX_MODEL(‘’, ‘’, JSON(‘{“function”: “embedding”, “embeddingOutput”: “embedding”, “input”: { “input”: [“DATA”} }}’)); End; / In this example, the IMPORT_ONNX_MODEL procedure is used to import an ONNX model. The following is a break down on what is needed: : is a BLOB argument that holds the ONNX representation of the model. Example: = my_embedding_model.onnx : a user-defined name of the model. This is the name that will be used by SQL when called. Example: = doc_model Obtaining a pre-trained model: Before you can load a pre-trained model, you must have a pre-trained model. Where can you get a pre-trained model? To import a pre-trained model, you first must have the Python packaged called Oracle Machine Learning Utilities (omlutils). Sadly, at the time of this writing, the only way to get this package is via Oracle as a wheel package. Once you have the omlutils package, it needs to be uploaded to the server where the Oracle Database is running. Installing OMLUTILS Hopefully, when Oracle Database 23ai is fully released the omlutils binaries will be included in the Oracle Database Home. Like what Oracle has done with Python 3.12. The next couple of steps will give a overview of how to install the omlutilis into the local Python environment. 1. Verify you have Python 3.12 installed. $ export ORACLE_HOME_23ai=/opt/oracle/product/23ai/dbhome_1 $ cd $ORACLE_HOME_23ai/python/bin $ python -V $ export PATH=$ORACLE_HOME_23ai/python/bin:$PATH $ python -v In both of the “python -V” commands, you should be returned Python 3.12.0 2. Create an ONNX directory $ cd ~ $ mkdir onnx 3. Unzip the omlutils.zip file in the onnx directory $ cd ~/onnx $ unzip ./omlutils.zip -d . 4. After the omlutils have been unzipped, install the package using pip $ cd ~/onnx $ python -m pip install -r requirements.txt $ python -m pip install omlutils-0.13.0-cp312-cp312-linux_x86_64.whl Included Pre-Trained Models With the omlutils package installed it comes with seventeen different pre-trained models that can be used for embedding vectors. These pre-trained models are ready to use immediately and can be seen from Python using the show_preconfigured() function. 1. ‘sentence-transformers/all-mpnet-base-v2', 2. 'sentence-transformers/all-MiniLM-L6-v2', 3. 'sentence-transformers/multi-qa-MiniLM-L6-cos-v1', 4. 'ProsusAI/finbert', 5. 'medicalai/ClinicalBERT', 6. 'sentence-transformers/distiluse-base-multilingual-cased-v2', 7. 'sentence-transformers/all-MiniLM-L12-v2', 8. 'BAAI/bge-small-en-v1.5', 9. 'BAAI/bge-base-en-v1.5', 10. 'taylorAI/bge-micro-v2', 11. 'intfloat/e5-small-v2', 12. 'intfloat/e5-base-v2', 13. 'prajjwal1/bert-tiny', 14. 'thenlper/gte-base', 15. 'thenlper/gte-small', 16. 'TaylorAI/gte-tiny', 17. 'infgrad/stella-base-en-v2’ The steps to see these pre-configured models is as follows from an interactive Python prompt: $ python >>> from omlutils import EmbeddingModel, EmbeddingModelConfig >>> em = EmbeddingModel(model_name=sentence-transformers/all-MiniLM-L6-v2”) >>> emc = EmbeddingModelConfig() >>> emc.show_preconfigured() >>> exit() $ To convert one of these pre-configured models into ONNX file, the steps are as follows: Again, you are using an interactive Python prompt here. For model recalibrations, these steps can be put into a python script that can run on a regular basis. $ cd ~/onnx $ python >>> from omlutils import EmbeddingModel, EmbeddingModelConfig >>> em = EmbeddingModel(model_name=sentence-transformers/all-MiniLM-L6-v2”) >>> em.export2file(all-MiniLM-L6-v2,output_dir=.”) >>> exit() $ When you look in the ~/onnx directory, you will see a ONNX file the matches the name of the pre-configured model. In this example the file name is all-MiniLM-L6-v2.onnx. Summary To round all this out. In the upcoming release of the Oracle Database 23ai, you will have the ability to take an open-source machine learning model and embed the model to the database. Once the model is embedded in the database, you can then use the model to create vectors on existing data or update vectors with these models; enabling a vector database in a secure environment while powering private Retrieval Augmentation Generation (RAG) in diverse envrionments.
If you have been following the last few blog posts, this is the final one with regards to setting up similarity search with Oracle’s upcoming vector datatype. This unique datatype enables you to be similarity search within your applications quickly and easily while keeping everything secure behind Oracle standard security of the Oracle database. If you have not keep up with the last few posts, you can go back and review the other three parts. These posts were designed to provide you with the basics of using Oracle’s Vector datatype, updating existing tables, and using similarity search from the command line. Part 1: https://rheodata.com/vector-datatype/ Part 2: https://rheodata.com/oracle-vector-datatype-updating-table-data/ Part 3:https://rheodata.com/similarity-search-oracle-vector-datatype/ In this post, we are going to look at extending Part 3 by creating a simple Flask Application to do the similarity search through a web page. To do this, there had to be a few minor changes to the previously illustrated Python code. Let’s dive in and see how a similarity search can be done via a web page. Prerequisites: Like Part 3, the prerequisites are with additional added for Flask: Oracle Database 23.4 (limited availability) Python 3.11 or later python_oracledb (2.0.0 or later (limited availability) LLM API Key (Cohere) Flask 3.0.2 Werkzeug 3.0.1 With the prerequisites set, we can now start looking at the code that will define the following application (see image below). Python/HTML This time around we are going to look at two different files – HTML and Python. This is what makes up the Flask Application we are using for this simple similarity search. The underlying table being used is the same as Part 3 – vector.video_games_vec. Before we jump into the Python code, we need to define a template for the HTML page (index.html). This is the main page of the application. Video Game Search: Besides the CSS information, the key items to review ar the items in curly brackets (). This is how Flask setups and uses items returned from the Python code. If you would like more on Flask and how it works with HTML – check out this page: https://flask.palletsprojects.com/en/3.0.x/ Now for Python … Finally! The python code in this example is similar to the one in Part 3; however, it has been broken down into a few more functions to make it easier to use with Flask. The first thing that needs to be done is import all the required packages for the application to work: import oracledb import cohere import array import time import secrets from flask import Flask, render_template, request, redirect, url_for from flask_wtf import FlaskForm from wtforms import StringField, SubmitField from wtforms.validators import DataRequired What you will notice here is the import of Flask and Werkzeug related items. There a good bit of items that needed to be imported but makes the application easier to develop. To keep items simple, we defined the database connection into its own function. This allows us to call for the connection and get the connection in return. This is a simplified function, but keep in mind that this will only work against an Oracle Database 23c (23.4 – Limited Availability). #define database connection function def database_connection(): try: connection = oracledb.connect( user=vector”, password=“”, dsn=“xxx.xxx.xxx.xxx:1521/freepdb1” ) print('connected’) return connection except: print('Could not make a connection’) Next, we are going to define how to vectorize the video game title we are going to search for. Breaking this out into separate function allows us to call and return the vector value at anytime within the application. #define LLM embedding model def cohere_vectorize(vInput): co = cohere.Client(0Yyj8ORoDk6MYjSb8”) data = vInput response = co.embed( texts=[data], model='embed-english-light-v3.0’, input_type=“search_query ) vector_value = response.embeddings[0] return vector_value The variable “vInput” is the title that we want to vectorize for our search. The last function that we are going to define is a function to vectorize the video game title then turns it into a FLOAT64 vector that can be used with SQL for searching the database. Then return the vector string. def exec_vec(text): vec = cohere_vectorize(text) vec2 = array.array(d, vec) return vec2 Application With the functions we needed defined, we can now setup the application to perform the search of the vector.video_games_vec table. First thing we need to do, is define the application. This done with the following statements: app = Flask(__name__) app.secret_key = secrets.token_hex(16) Then we need to define a class for the form itself: class Form(FlaskForm): text = StringField('Video Game Search: ', validators=[DataRequired()]) submit = SubmitField('Submit’) Next, tell the application how to route to the page and what cURL functions to use: @app.route('/', methods=('GET', 'POST’)) Lastly, we need to define a function for the index.html page. This is simply called index(). This function sets up the following: calls the form class variables/lists needed SQL statement to use opens the database connection validate the form retrieve the required information closes the database connection The index() function looks as follows: def index(): form = Form() output_titles = [] output_ids = [] output_genres = [] output_console = [] binds = [] select_stmt = select id, title, genres, console from vector.video_games_vec order by vector_distance(vg_vec1, :1, DOT), id fetch first 5 rows only” connection = database_connection() if form.validate_on_submit(): title = exec_vec(form.text.data) with connection.cursor() as cursor: for (id, title, genere, console,) in cursor.execute(select_stmt, [title]): output_ids.append(id) output_titles.append(title) output_genres.append(genere) output_console.append(console) #print(output_titles) #put all columns in a single list binds = list(zip(output_ids, output_titles, output_genres, output_console)) #print(binds) return render_template('index.html', form=form, output=binds) connection.close() return render_template('index.html', form=form, output=None) if __name__ == __main__”: app.run(debug=True) A couple of key items to point out in this function. The first is the SQL statement. The select statement defines what we are looking for in the vector.video_games_vec table. In this case we are looking for ID, TITLE, GENRES, and CONSOLE. Then we are looking and ordering by the distance between each title by using the VECTOR_DATABASE function using DOT notation, then ordering by ID. Lastly, we are only fetching the first five rows only. When this application executes this SQL statement against the vector.video_games_vec table, we will be using TITLE to find the video game in the table. All rows that are returned are then broken into four different lists. Then the lists are zipped together to give us all the information for the record via the binds list. Lastly, we are telling the application to return the binds list to the output area on the index.html page before closing the connect to the database. You can use this link to see similarity search in action: Video Game Similarity Search Other items to understand You may have noticed that some video games returned more titles than expected. This is because of: We are limiting the result set to the first five rows. This is normal behavior when the result set doesn’t have exactly five of the title. The proximity of the additional titles compared to the title being searched for
