SNOW-1936378: Add possibility for Loader to support Vector type

[mhryb]

Hello, is it possible to support Vector type for StreamLoader ?

What is the current behavior?

Currently Loader does not support Vector type, only array.

What is the desired behavior?

It would be good if loader supports Vector type.

How would this improve snowflake-jdbc?

It gives a possibility to support Vector type for loader. So users can load vector data as other types.

References, Other Background

As I can see for loading operation the driver creates a temp table and then use select/merge operation to move the data. It would be nice if during this operation we could convert an array to Vector if the target column is vector.

[sfc-gh-sghosh]

Hello @mhryb ,

Thanks for raising the issue.
At present VECTOR data type is only supported for SQL , python connector etc.
The supported VECTOR data type is only (FLOAT/INT )
If you want to convert ARRAY to VECTOR data type in target table with data loading, you can use casting

`String createTableSQL = "CREATE OR REPLACE TABLE my_vector_table (vector_col VECTOR(FLOAT, 3));";
stmt.execute(createTableSQL);
System.out.println("Table 'my_vector_table' created successfully.");

    String createArrayTableSQL = "CREATE OR REPLACE TABLE array_example (array_column ARRAY);";
    stmt.execute(createArrayTableSQL);
    System.out.println("Table 'array_example' created successfully.");
    
   
    String insertArrayDataSQL = "INSERT INTO array_example (array_column) "
                              + "SELECT ARRAY_CONSTRUCT(12, 14.0, 100);";
    stmt.execute(insertArrayDataSQL);
    System.out.println("Data inserted into 'array_example' table successfully.");
    
    
    String insertVectorDataSQL = "INSERT INTO my_vector_table (vector_col) "
                               + "SELECT array_column::VECTOR(FLOAT, 3) "
                               + "FROM array_example;";
    stmt.execute(insertVectorDataSQL);
    System.out.println("Data inserted into 'my_vector_table' successfully.");
    

    String selectSQL = "SELECT * FROM my_vector_table;";
    ResultSet rs = stmt.executeQuery(selectSQL);
    

    while (rs.next()) {
        System.out.println("VECTOR Column: " + rs.getString("VECTOR_COL"));
    }

Output:
VECTOR Column: [12.000000,14.000000,100.000000]
`

Regards,
Sujan

[mhryb]

Hello @sfc-gh-sghosh, thanks for the answer.
Yes, I can do that, but it would be nice if snowflake-jdbc StreamLoader supports this.
So we would have a possibility to do all the operations "Insert", "Update", "Upsert" with Vectors using StreamLoader .

[mhryb]

Let me describe with more details what I mean.
For example here we are inserting / merging data from temporary table to a real table:

snowflake-jdbc/src/main/java/net/snowflake/client/loader/ProcessQueue.java

Line 293 in c7b4136

loadStatement = "INSERT INTO "

So it would be nice to add possibility to cast array to a vector if target column is a vector.