Uploading Vectors to Hana Cloud with CAP, NodeJS

[michaelschmid03]

Hi there,
So i am working on a SAP CAP node.js app running on SAP BTP CloudFoundry Environment, in which i am struggling to Implement Editing or Creating Entitities containing Vectors using CDS QL.

Let me explain this with a example:

Schema.cds

entity ExampleEntity:cuid,managed{
Product:Integer;
ProductDescription:String; 
Vector:Vector(3);
}

A Query like this will work just fine and the syntax is documented [1]

let oResult= await SELECT.from('ExampleEntity')
  .where`cosine_similarity(Vector, to_real_vector(${embedding})) > 0.9`

But what about a Insert?
The Offcial Documentation [2] of SAP HANA CLOUD shows me this syntax in SQL.

CREATE TABLE VECTORTAB (ID INT, V REAL_VECTOR(3));
INSERT INTO VECTORTAB VALUES (1, TO_REAL_VECTOR('[2,3,5]'));

I've tried to adapt this to CAP Syntax with no luck so far.

 let oResult = await INSERT.into(ExampleEntity).entries({
        ID: sID,
        ...other fields,
        Vector: `to_real_vector('[2,3,5]')`,
      })

While this is what id expect the syntax to be like i receive this error: (keep in mind this is a cds validation error not a sql error)
[error] - 500 > {
'@Common.numericSeverity': 4,
code: '500',
message: 'Cannot set parameter at row: 1. Wrong input for BINARY type'
}
Indicating that vector structure only works on filter and sort conditions, maybe there is a way to create the binary buffer in nodejs? Or is the syntax i've tried so far just wrong?

The only thing working is rawdogging SQL inside of my CAP Custom handler, which is possible, but not really pretty.

let oResult = await cds.run(
  `INSERT INTO ch_company_productname_tablename
  (ID,...,Vector) 
  VALUES ('${sID}',...,TO_REAL_VECTOR('${vector}'))`
);

Any advice or suggestions would be appreciated
Sources:
[1] https://cap.cloud.sap/docs/guides/databases-hana#vector-embeddings
[2] https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-vector-engine-guide/i...

[MioYasutake · Accepted solution]

@michaelschmid03 

To me, the following git repository was helpful.

https://github.com/SAP-samples/cap-llm-plugin-samples/blob/main/samples/hr-approval-rag-usecase/srv/...

Also, please take a look at this blog post, explaining CAP LLM Plugin.

https://community.sap.com/t5/technology-blogs-by-sap/cap-llm-plugin-empowering-developers-for-rapid-...

[michaelschmid03 · Accepted solution]

edit - duplicate

[michaelschmid03 · Accepted solution]

Thank you, i used this as workaround for a while. Then SAP fixed it making the creation of this Buffer unnecessary.

[Vitaliy-R]

I am not familiar with JavaScript, and therefore, not much familiar with coding in CAP either. I haven't done much research, so maybe there is a better solution. Now, after this long disclaimer, knowing the binary structure of the HANA vector data type, can you do such a conversion in your code and supply a binary value to the INSERT?

const { Buffer } = require('buffer');

// Function to convert a vector of real numbers into the described binary format: https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-vector-engine-guide/constructing-and-serializing-vectors

function vectorToBinary(vector) {
// Create a Buffer with enough space for the dimension (4 bytes) plus the floating-point numbers
const n = vector.length;
const buffer = Buffer.alloc(4 + n * 4);

// Write the dimension (n) as a little-endian 32-bit integer
buffer.writeUInt32LE(n, 0);

// Write each floating-point number as a little-endian IEEE 754 single-precision float
for (let i = 0; i < n; i++) {
buffer.writeFloatLE(vector[i], 4 + i * 4);
}

return buffer;
}

// Example usage
const vector = [2,3,5]; // Example vector from the question
const binaryData = vectorToBinary(vector);

console.log('Binary Data (Hex):', binaryData.toString('hex').toUpperCase());

Regards,
-Vitaliy

Cc @KRiedelsheimer who might have a better answer 🤓

[Vitaliy-R]

@michaelschmid03Did it work for you? I am just curios.

[michaelschmid03]

@Vitaliy-R

[michaelschmid03]

@Vitaliy-R Didnt try it. but that solution is quite smiliar to what the cap-llm plugin did. So Kudos for you to for figuring it out on your own!
Then SAP just decided they parse it on their end, you can just pass a JSON of said vector these days.