VARIANT Support ?

[markddrake]

How do you insert a JSON object into a column of type VARIANT..

     var snowflake = require('snowflake-sdk');
     class Test {

      getConnectionProperties() {
        return {
         account           : 'pra44106'
       , username          : 'MARKDDRAKE'
      , password          : 'Not4Oracle'
      , database          : 'DEMO_DB'
      }
     }
    
     establishConnection() {
      
       const connection = this.connection;
       return new Promise(function(resolve,reject) {
          connection.connect(function(err,conn) {
            if (err) {
             reject(err);
           }
           resolve(conn);
         })
      })
    } 
  
    executeSQL(sqlStatement, args) {
      
    const self = this
    
    return new Promise(function(resolve,reject) {
      
      self.connection.execute({
        sqlText: sqlStatement
       ,binds  : args
       ,complete: function(err,statement,rows) {
                    if (err) {
                      resolve(err);
                    }
                    else {
                      resolve(rows);
                    }
                  }
      })
    })
    } 
  
    async runTest()  {   
      this.connection = snowflake.createConnection(this.getConnectionProperties());
      this.connection = await this.establishConnection();
      let results = await this.executeSQL(`drop table if exists "PUBLIC"."TEST"`,[]);
      console.log('drop():',results);
      results = await this.executeSQL(`create table "PUBLIC"."TEST" ( "C" VARIANT)`,[]);
      console.log('create():',results);
      results = await this.executeSQL(`insert into "PUBLIC"."TEST"("C") VALUES (?)`,[{"key" : 
  "value"}])
      console.log('insert():',results);
      results = await this.executeSQL(`insert into "PUBLIC"."TEST"("C") VALUES (PARSE_JSON(?))`, 
 [JSON.stringify({"key" : "value"})])
      console.log('insert PARSE_JSON():',results);
    }  
  }

async function main() {

  const test = new Test();
  await test.runTest();

}
main()

Generates the following...

     C:\Development\YADAMU\WiP\snowflake>node sftest2
     drop(): [ { status: 'TEST successfully dropped.' } ]
     create(): [ { status: 'Table TEST successfully created.' } ]
     insert(): { OperationFailedError: SQL compilation error:
     Unsupported data type 'VARIANT'.
         at createError (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\errors.js:524:15)
         at Object.exports.createOperationFailedError (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\errors.js:303:10)
         at Object.callback (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\services\sf.js:586:28)
         at Request._callback (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\http\base.js:107:19)
         at Request.self.callback (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:185:22)
         at Request.emit (events.js:197:13)
         at Request.<anonymous> (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:1161:10)
         at Request.emit (events.js:197:13)
         at IncomingMessage.<anonymous> (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:1083:12)
         at Object.onceWrapper (events.js:285:13)
       name: 'OperationFailedError',
       code: '002040',
       message: "SQL compilation error:\nUnsupported data type 'VARIANT'.",
       sqlState: '42601',
       data:
        { internalError: false,
          errorCode: '002040',
          age: 0,
          sqlState: '42601',
          queryId: '018ef0f6-0166-f87c-0000-0000ea034901',
          line: -1,
          pos: -1,
          type: 'COMPILATION' } }
     insert PARSE_JSON(): { OperationFailedError: SQL compilation error:
     Invalid expression [PARSE_JSON(?)] in VALUES clause
         at createError (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\errors.js:524:15)
         at Object.exports.createOperationFailedError (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\errors.js:303:10)
         at Object.callback (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\services\sf.js:586:28)
         at Request._callback (C:\Development\YADAMU\WiP\snowflake\node_modules\snowflake-sdk\lib\http\base.js:107:19)
         at Request.self.callback (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:185:22)
         at Request.emit (events.js:197:13)
         at Request.<anonymous> (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:1161:10)
         at Request.emit (events.js:197:13)
         at IncomingMessage.<anonymous> (C:\Development\YADAMU\WiP\snowflake\node_modules\request\request.js:1083:12)
         at Object.onceWrapper (events.js:285:13)
       name: 'OperationFailedError',
       code: '002014',
       message:
        'SQL compilation error:\nInvalid expression [PARSE_JSON(?)] in VALUES clause',
       sqlState: '22000',
       data:
        { internalError: false,
          errorCode: '002014',
          age: 0,
          sqlState: '22000',
          queryId: '018ef0f6-011a-1f64-0000-0000ea034905',
          line: -1,
          pos: -1,
          type: 'COMPILATION' } }
     
     C:\Development\YADAMU\WiP\snowflake>

I sort of understand why I can't insert the JSON object directly, given that the VARIANT data type can be used to store other types of content (XML), so the obvious workaround would be to supply a specific cast using PARSE_JSON, but that insert syntax does seem to like that...

[markddrake]

Ok,. insert into table select PARSE_JSON(?) appears to work, but how would I make that work with a multiple rows... A Union All of many SELECT operations

[smtakeda]

How about insert into T select * from parse_json()...? Currently variant type is not natively supported for binding.

[markddrake]

Yeah, I think that what I said in my follow-up comment..... However since array binds are not support for an insert select you have to do a fugly UNION all if you want to insert multiple rows in one go.

[markddrake]

with other databases you can use surround parameter markers with SQL operators in the values clause which would be much cleaner.

[turbaszek]

Assuming that we have table like:

CREATE TABLE TEST(value VARIANT);

then we can also do:

let values = [{a: 1, b: 2}, {a: 3, b: 4}];
connection.execute({
   sqlText: "INSERT INTO IDENTIFIER(?) SELECT value FROM TABLE(FLATTEN(PARSE_JSON(?)))",
   binds: ["TEST", JSON.stringify(values)]
});

This should reduce the query size and remove the UNION hack.

[mike-marcacci]

Thanks for the tip @turbaszek. Expand upon this, assuming the table:

create table test(
  id int,
  foo variant
);

The following should work:

connection.execute({
  sqlText: `
  insert into test
  select
    value:id as id,
    value:foo as variant
  from table(flatten(parse_json(?)))
  `,
  binds: [JSON.stringify([
    {id: 1, foo: [{a: '1', b: '2'}]},
    {id: 2, foo: [{c: '3', d: '4'}]}
  ])]
});

This was not immediately obvious to me given limited familiarity with the details of flatten and parse_json but was fairly easy to figure out. Posting in case it saves somebody else the 15 minutes.

[sfc-gh-dszmolka]

i see this issue was not updated for a while :( at least on the plus side now the driver (since 1.6.14) is capable of handling array binding. There's also some documentation for it. closing this old issue since the driver should be able to handle array binding without workaround. thank you everyone for providing the workarounds/troubleshooting here!

[kirillgroshkov]

Still unclear how to insert data into VARIANT columns. I still get the Unsupported data type 'VARIANT' when I insert like:

insert into TABLE (ID, SRC_JSON) values (?, ?)

[
  ['abc', { a: 'a' }]
]

There's no documentation about how to achieve that, and it's probably still unsupported. Issue should be re-opened then?

[kirillgroshkov]

After 2 hours of googling, this started to work for me. There's a typo though, instead of value:foo as variant it should be value:foo as foo

connection.execute({
  sqlText: `
  insert into test
  select
    value:id as id,
    value:foo as variant
  from table(flatten(parse_json(?)))
  `,
  binds: [JSON.stringify([
    {id: 1, foo: [{a: '1', b: '2'}]},
    {id: 2, foo: [{c: '3', d: '4'}]}
  ])]
});

[sfc-gh-dszmolka]

thank you for sharing your solution! it is sorta documented in the integrations tests, but agree that's not the best way to keep it :) will work with the Docs team to get this amended in the official Snowflake docs.