Oracle Database 23ai introduced Oracle AI Vector Search, an exciting new capability that revolutionizes semantic similarity search by generating vectors using transformer models and managing them at scale within the database. Using vectors, you can perform similarity searches that leverage data semantics, going beyond basic text matching to find relevant information based on meaning and context.
In a recent post, we introduced a feature of the OML4Py 2.0 client that streamlines the path to vector embedding generation in Oracle Database 23ai. This tool downloads pre-trained transformer models from the Hugging Face repository, augments them with necessary pre- and post-processing steps, converts the model into ONNX format, and seamlessly loads the model to the database.
Today, we're excited to announce that we are making things even simpler. We are providing a downloadable, augmented version of Hugging Face's all-MiniLM-L12-v2 model in ONNX format. This model, distributed by Hugging Face, can be loaded directly into the database via DBMS_VECTOR.LOAD_ONNX_MODEL or DBMS_DATA_MINING.IMPORT_ONNX_MODEL. The model available for download represents an augmented pipeline that includes tokenization and the required post-processing steps or generating vector embeddings seamlessly with AI Vector Search in Oracle Database 23ai.
Hugging Face's all-MiniLM-L12-v2 model is a compact yet powerful sentence transformers model widely leveraged for optimizing various natural language processing (NLP) tasks. Hugging Face's documentation states that it aims for efficiency and high-performance and features a 12-layer architecture that is designed to excel in tasks such as sentence similarity and text classification. This model's lightweight design is meant to allow for quicker processing and reduced computational demands compared to larger models. Despite its compact size, it is known to uphold high accuracy and reliability, establishing itself as a versatile choice for developers implementing NLP solutions.
Let’s go through the steps that enable you to use the augmented Hugging face embedding model in Autonomous Database.
Load the model to the database. Specify the ONNX model file name and the Object's location URI in Object Storage.
DECLARE
ONNX_MOD_FILE VARCHAR2(100) := 'all_MiniLM_L12_v2.onnx';
MODNAME VARCHAR2(500);
LOCATION_URI VARCHAR2(200) := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
BEGIN
DBMS_OUTPUT.PUT_LINE('ONNX model file name in Object Storage is: '||ONNX_MOD_FILE);
--------------------------------------------
-- Define a model name for the loaded model
--------------------------------------------
SELECT UPPER(REGEXP_SUBSTR(ONNX_MOD_FILE, '[^.]+')) INTO MODNAME from dual;
DBMS_OUTPUT.PUT_LINE('Model will be loaded and saved with name: '||MODNAME);
-----------------------------------------------------
-- Read the ONNX model file from Object Storage into
-- the Autonomous Database data pump directory
-----------------------------------------------------
BEGIN DBMS_DATA_MINING.DROP_MODEL(model_name => MODNAME);
EXCEPTION WHEN OTHERS THEN NULL; END;
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'DATA_PUMP_DIR',
object_uri => LOCATION_URI||ONNX_MOD_FILE);
-----------------------------------------
-- Load the ONNX model to the database
-----------------------------------------
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'DATA_PUMP_DIR',
file_name => ONNX_MOD_FILE,
model_name => MODNAME);
DBMS_OUTPUT.PUT_LINE('New model successfully loaded with name: '||MODNAME);
END;
ONNX model file name in Object Storage is: all_MiniLM_L12_v2.onnx
Model will be loaded and saved with name: ALL_MINILM_L12_V2
New model successfully loaded with name: ALL_MINILM_L12_V2
Validate that the model was imported to the database.
select model_name, algorithm, mining_function from user_mining_models where model_name='ALL_MINILM_L12_V2';
MODEL_NAME ALGORITHM MINING_FUNCTION
---------- --------- ---------------
ALL_MINILM_L12_V2 ONNX EMBEDDING
Generate embedding vectors using the VECTOR_EMBEDDING SQL scoring function. The VECTOR_EMBEDDING function has two input parameters:
The output of the VECTOR_EMBEDDING SQL function is an vector, capturing the semantic features of the input data for use in similarity searches within the database.
Here, we generate embedding vectors using 'The quick brown fox' as the input. Partial output is shown below.
SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'The quick brown fox' as DATA) AS embedding;
EMBEDDING
--------------------------------------------------------------------------------
[1.40532674E-002,-4.24734354E-002,-1.42729701E-002,3.90004814E-002,3.84781733E-0
02,-7.44695729E-003,-1.20800901E-002,2.60837115E-002,-3.97795811E-002,3.85206044
E-002,-8.92989617E-003,-5.55456802E-002,5.72643466E-002,3.43147628E-002,-3.51916
...
...
Create a table with some data and then vectorize it.
CREATE TABLE vec1 (
id NUMBER PRIMARY KEY,
str VARCHAR2(128),
v VECTOR
);
INSERT INTO vec1 (id, str) VALUES (1, 'Twinkle, twinkle, little star');
INSERT INTO vec1 (id, str) VALUES (2, 'Baa baa black sheep');
INSERT INTO vec1 (id, str) VALUES (3, 'Mary had a little lamb');
INSERT INTO vec1 (id, str) VALUES (4, 'Jack and Jill went up the hill');
INSERT INTO vec1 (id, str) VALUES (5, 'Humpty Dumpty had a great fall');
COMMIT; -- omit commit for OML notebooks; commit is automatic.
Create a vector for the str column with id = 2. Partial output is shown below.
SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING str as data) AS embedding
FROM vec1
WHERE id = 2;
EMBEDDING
--------------------------------------------------------------------------------
[-5.06732985E-002,2.79495809E-002,-2.49498505E-002,-1.40845561E-002,-4.07454036E
-002,1.19132325E-002,4.41188365E-002,-1.03995301E-001,9.50366631E-003,4.92134318
E-003,-4.72284891E-002,-8.59436914E-002,-4.93300222E-002,1.57973133E-002,-5.0117
...
...
Create vectors for all of the str columns. Partial output is shown below.
SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING str as data) AS embedding
FROM vec1;
EMBEDDING
--------------------------------------------------------------------------------
[-5.06732985E-002,2.79495809E-002,-2.49498505E-002,-1.40845561E-002,-4.07454036E
-002,1.19132325E-002,4.41188365E-002,-1.03995301E-001,9.50366631E-003,4.92134318
E-003,-4.72284891E-002,-8.59436914E-002,-4.93300222E-002,1.57973133E-002,-5.0117
...
...
Create multiple vectors, one for each of the str columns, and for the string 'random text'
SELECT
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING str as data) AS str_embedding,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'random text' as data) AS hello_embedding
FROM vec1;
STR_EMBEDDING
--------------------------------------------------------------------------------
.04134463E-002,-9.2641887E-004,2.01415271E-002,-1.23053417E-002,6.70282394E-002,
3.41577232E-002,7.14282319E-002,-7.272847E-003,3.6919117E-002,-3.56972888E-002,2
.79667191E-002,-3.16645741E-003,-2.17279536E-003,-2.21561473E-002,1.07383579E-00
...
...
HELLO_EMBEDDING
--------------------------------------------------------------------------------
0665202E-002,4.12154943E-002,-2.81600393E-002,5.66209666E-002,1.05218828E-001,8.
71521235E-003,3.42204273E-002,1.90378297E-002,9.3434602E-003,2.70264558E-002,6.0
9423127E-003,-3.54394764E-002,-5.11234067E-002,1.12803914E-002,-7.34571517E-002,
...
...
Create a table based on an existing table with the vectors created on the fly.
CREATE TABLE vec2 as
SELECT
id,
str,
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING str as data) as v
FROM vec1;
Table created.
Insert a new row creating the vector on the fly.
INSERT INTO vec2 values (11, 'Row, row, row your boat',
VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Hey diddle diddle' as data));
SELECT * FROM vec2;
Twinkle, twinkle, little star [-6.27035573E-002,-5.07730506E-002,3.9072983E-002, ...]
Baa baa black sheep [-5.06732985E-002,2.79495809E-002,-2.49498505E-002, ...]
Mary had a little lamb [-1.83388684E-002,6.17698878E-002,-7.22632371E-003, ...]
Jack and Jill went up the hill [-2.63922885E-002,6.08952455E-002,7.38528883E-003,...]
Humpty Dumpty had a great fall [-6.36104913E-003,1.45655302E-002,3.4387894E-002,...]
Row, row, row your boat [-4.80064079E-002,1.68496892E-002,1.63660683E-002,...]
1. Download and unzip the file containing the ONNX model (link)
$ unzip all_MiniLM_L12_v2_augmented.zip
Archive: all_MiniLM_L12_v2_augmented.zip
inflating: all_MiniLM_L12_v2.onnx
inflating: README-ALL_MINILM_L12_V2_augmented.txt
2. Log into your database instance as sysdba. In this example, we are logging into a pluggable database named ORCLPDB. Replace ORCLPDB with your database name.
$ sqlplus / as sysdba;
SQL> alter session set container=ORCLPDB;
3. Apply grants and define the data dump directory as the path where the ONNX model was unzipped.
Note, in this example, we are using the OMLUSER schema. Replace OMLUSER with your schema name.
SQL> GRANT DB_DEVELOPER_ROLE, CREATE MINING MODEL TO OMLUSER;
SQL> CREATE OR REPLACE DIRECTORY DM_DUMP AS '<path to ONNX model>';
SQL> GRANT READ ON DIRECTORY DM_DUMP TO OMLUSER;
SQL> GRANT WRITE ON DIRECTORY DM_DUMP TO OMLUSER;
SQL> exit
4. Log into your schema.
$ sqlplus omluser/omluser@ORCLPDB;
Load the ONNX model. Optionally drop the model first if a model with the same name already exists in the database.
SQL> exec DBMS_VECTOR.DROP_ONNX_MODEL(model_name => 'ALL_MINILM_L12_V2', force => true);
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'DM_DUMP',
file_name => 'all_MiniLM_L12_v2.onnx',
model_name => 'ALL_MINILM_L12_V2');
END;
/
Once imported, the model can generate embeddings as shown above.
This feature represents a significant step forward in enabling users to leverage vector embeddings in Oracle Database 23ai. Start exploring the possibilities with Oracle AI Vector Search and the all-MiniLM-L12-v2 ONNX model today. For more information, refer to the Oracle AI Vector Search User's Guide.
Oracle Machine Learning