Introduction
Oracle Data Integrator is a comprehensive data integration platform that covers all data integration requirements: from high-volume, high-performance batch loads, to event-driven, trickle-feed integration processes, to SOA-enabled data services. Oracle Data Integrator (ODI) 14c, the latest version of Oracle’s strategic Data Integration offering, provides superior developer productivity and improved user experience with a redesigned flow-based declarative user interface and deeper integration with Oracle GoldenGate. ODI14c further builds on its flexible and high-performance architecture with comprehensive big data support and added parallelism when executing data integration processes.
In this blog, let’s explore how we can migrate vector data using Oracle data integrator.
Use Case
Customer has vector data stored in multiple Oracle databases and wants to migrate it to an Autonomous database to create a centralized repository that supports their AI use cases.
In this blog, we will extract the vector data from my_data table and load it into the target Oracle table using Oracle Data Integrator.
Defining Vector datatype in ODI
ODI offers the flexibility to define a new datatype. As the vector datatype is not captured by built-in ODI modules, a vector datatype can be defined in the ODI Topology – Oracle connections.
Defining the vector datatype will allow ODI to identify it in reverse engineering process.
Extracting the Vector data
Vector data can’t be extracted directly. It must first be converted to a VARCHAR2 or CLOB format before it can be loaded into ODI temporary tables. The Oracle database provides built-in functions to convert vector data into the VARCHAR2 or CLOB data types. The Oracle function FROM_VECTOR can be used to convert vector data to a character data type.
FROM_VECTOR function can be implemented using the Expression component in ODI. Below is a screenshot demonstrating this scenario.
Syntax:
FROM_VECTOR(vector_column returning clob format dense)
Loading the Vector data into Oracle database
The CLOB data can now be loaded into the target table. It can be converted back to the vector data type using the TO_VECTOR function in the Oracle database.
Below is a screenshot of the ODI mapping.
The vector embedding syntax is given below.
Syntax:
To_vector(vector_data)
ODI Mapping
The data is extracted from the source table, and the vector data is converted into CLOB format using the FROM_VECTOR function in the Oracle database. The column is then converted back into the vector data type using the TO_VECTOR function and is loaded into the target table.
LKM SQL to SQL and IKM Oracle insert are used for the above use case.
The data from source table and target table are given below.
Note: The views expressed above are my own and do not constitute an official certification from the Oracle.
Conclusion
In this blog, we learnt an approach to extract the vector data from an Oracle databases and load it into an Autonomous database. This can be extended to multiple databases.
References
https://blogs.oracle.com/machinelearning/post/use-our-prebuilt-onnx-model-now-available-for-embedding-generation-in-oracle-database-23ai