[ORACLE] Embedding ONNX model for Vector Search tests with Oracle Database 23ai

Hello, everyone.

How's it going?

Today, I'm going to show you how to load an ONNX model into the Oracle 23ai database for your vector search tests. 

But first, what is ONNX?1

Open Neural Network Exchange (ONNX) is an open format built to represent machine learning models. ONNX defines a common set of operators - the building blocks of machine learning and deep learning models - and a common file format to enable AI developers to use models with a variety of frameworks, tools, runtimes, and compilers.

You can find more information about ONNX here.

To begin with, we'll need to download an ONNX model. We can do this here.

  

In my example, I'm use a docker environment to tests and I need to copy the ONNX zip to this environment.

For copy:

docker cp ONNX/all_MiniLM_L12_v2_augmented.zip Orcl23aiORDS:/opt/oracle/DATA_DIR

 

Unzip the file:

 

 

Execute the grants bellow.

sqlplus / as sysdba
alter session set container=freepdb1;

    GRANT DB_DEVELOPER_ROLE, CREATE MINING MODEL TO LAOUC24;

    CREATE OR REPLACE DIRECTORY DATA_DIR AS '/opt/oracle/DATA_DIR';

    GRANT READ,WRITE ON DIRECTORY DATA_DIR TO LAOUC24;

  

Finally, load the model into the database.

sqlplus laouc24/Password123##@FREEPDB1
 

exec DBMS_VECTOR.DROP_ONNX_MODEL (

        model_name => 'ALL_MINILM_L12_V2', 

        force => true

);
 

BEGIN
        DBMS_VECTOR.LOAD_ONNX_MODEL (
                directory => 'DATA_DIR',
                file_name => 'all_MiniLM_L12_v2.onnx',
                model_name => 'ALL_MINILM_L12_V2');
END;
/  

 

 

Check that the model is loaded into the database and run the first test.

SELECT 

    model_name, 

    algorithm, 

    mining_function 

FROM user_mining_models 

WHERE 

     model_name = 'ALL_MINILM_L12_V2';

SELECT 
    VECTOR_EMBEDDING ( 
 ALL_MINILM_L12_V2 USING 'It is a pleasure to be here at LAOUC Tour 2024!' as DATA
    ) AS embedding;

Now you're ready to create your first table, load the data and vectors and start using Vector Search

CREATE TABLE laouc24.vector1 (
        id                      NUMBER PRIMARY KEY,
        name                VARCHAR2(50),
        vector_coord    VECTOR
);

INSERT INTO laouc24.vector1 (id, name, vector_coord) VALUES
        (1, 'Mr. Morcego'           , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Bat' as data)),
        (2, 'Mr. 7 Mountains'      , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. 7 Mountains' as data)),
        (3, 'Mr. Martin'               , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Martin' as data)),
        (4, 'Mrs. Maria Padilha' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mrs. Maria Padilha' as data)),
        (5, 'Mr. Litle Fire'            , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Litle Fire' as data)),
        (6, 'Mrs. Maria Quiteria' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mrs. Maria Quiteria' as data)),
        (7, 'Mr. Seven'               , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Seven' as data)),
        (8, 'Mr. World Spinner'   , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. World Spinner' as data)),
        (9, 'Mr. White Stone'      , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. White Stone' as data)),
        (10, 'Mr. Litle Darkness' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Litle Darkness' as data)),
        (11, 'Mr. Marian'             , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Marian' as data));
        
COMMIT;

SELECT * FROM  laouc24.vector1 where id = 4;

And that's it. Your database is ready for testing using Vector Search.

For more examples and information, you can access this posts here and here.

I hope this tip has helped you.

Regards
Mario

[ORACLE] How to create a test environment with Oracle 23ai running on MacBook Pro M3 Pro.

Hello everyone.

How are you going?

I've finally surrendered to the Macbook's allure for work. 

And my first challange was to run the Oracle 23ai in my Mac.

So, let's get started!

First, we need to install the homebrew.

/bin/bash -c 

"$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)" 

We don't have an installer to Oracle 23ai for the Mac M3. 

So, I'm use Colima + Docker + QEMU to emulate x86_64 arch and run the Oracle 23ai. 

Colima is an open source container runtime for macOS with minimal configuration. In addition,

QEMU was an open source software virtualization tool that performs hardware emulation. 

Now, we need to install Docker, Docker-compose, Colima and reinstall QEMU.

   

   brew update

   brew install docker

   brew install docker-compose

 

   brew install colima

   brew reinstall qemu

* Yes, I forgot to take the screenshots 

We are to install Rosetta, as well.

   oftwareupdate --install-rosetta

According Apple, "Rosetta is not an app that you open or interact with. Rosetta works

automatically in the background whenever you use an app that was built only for Mac

computers with an Intel processor. It translates the app for use with Apple silicon."

Check the colima status.

   colima help

Stop and delete the old profile.

   colima stop

   colima delete

And start Colima with the parameters bellow:

   colima start --cpu 4 --memory 10 --arch x86_64 --vm-type vz --vz-rosetta

   Where:

      --vm-type=vz             ==> Virtual machine type.

      --vz-rosetta                ==> Enable Rosetta for amd64 emulation.

      --mount-type=virtiofs ==> Volume Mount Driver for vz.

      --arch x86_64            ==> Architecture (x86_64).

   colima status

* The default VM created by Colima has 2 CPUs, 60GB storage and 2GB memory runing

Ubuntu.

Access the Colima console:

   colima ssh

   # Check if Colima is using the new profile

   docker context ls

Finally, create a docker container running Oracle 23ai:

 docker run -d -it --name Orcl23ai /

    -p 1527:1521 /

    -p 5507:5500 /

    -p 8087:8080 /

    -p 8447:8443 

    -e ORACLE_PWD=E 

    container-registry.oracle.com/database/free:latest

Check the container and database.

   docker ps --format "table {{.ID}}\t{{.Status}}\t{{.Names}}\t{{.Ports}}"

   docker exec -it Orcl23ai /bin/bash

And thats it. As simple as that.

If you can watch this installation, you can see these videos from my friend Eduardo Claro:

Installing Colima and Docker in Apple Silicon ARM based machines

You can also consult the documentation here - it's for Oracle Autonomous Database Free Container Image, but the process is the same.

I hope it helps you.

Regards

Mario