Variant Column Issue

[agoyal1271]

What happened:
Not able to flatten varaint column type of snowflake in dremio

What you expected to happen:

Once the table is imported as a pds and a vds is created converting variant column to json, I expect to extract elements from the column as I would do with any other source type containing json
How to reproduce it (as minimally and precisely as possible):
a) Create a table in snowflake with variant type column
b) Import that table as PDS in dremio. As per the yaml, the variant column is recognized as varchar in dremio
c) Use function convert_from to recognize that column as json and save as a vds
d) Now start extracting elements (through UI or command) from the vds created in step c and you will encounter an error (Using CONVERT_FROM(*, 'JSON') is only supported against string literals and direct table references of types VARCHAR and VARBINARY.

Anything else we need to know?:

Details: Please slack me if you need repro details
dremio_issue.zip
35540b76-fa41-4b95-afc3-95fcedd3f9f1.zip

• Dremio version (Help > About from the UI):
• Profile zip:
• server.log:

[naren-dremio]

@agoyal1271 I was able to reproduce the error you got:

create database variant_test;
create table varia (float1 float, v variant, float2 float);
insert into varia (float1, v, float2) values (1.23, null, null);
insert into varia (v) select ('{"key3": "value3", "key4": "value4"}'::variant);

The column v appears as following (Not just in Dremio but in any JDBC tool):

"{\"key3\": \"value3\", \"key4\": \"value4\"}"

Whereas if I create a table and insert an object using parse_json function in snowflake like below:

create table demonstration1 (
    id integer,
    array1 array,
    variant1 variant,
    object1 object
    );

insert into demonstration1 (id, array1, variant1, object1) 
  select 
    1, 
    array_construct(1, 2, 3), 
    parse_json(' { "key1": "value1", "key2": "value2" } '),
    parse_json(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": "1b" }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } } ')
    ;

insert into demonstration1 (id, array1, variant1, object1) 
  select 
    1, 
    array_construct(1, 2, 3, null), 
    parse_json(' { "key1": "value1", "key2": NULL } '),
    parse_json(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": NULL }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } '
              ||
               ' } ')
  ;

Then you can convert the type to JSON in Dremio and extract the elements without any issue. See below for a screen recording of the behavior of the above table in Dremio:

Screen.Recording.2022-01-03.at.10.18.50.AM.mov

Other than inserting the data like the example shown above, I don't see any other way of making this to work and converting the types in Dremio.