CODEWITHSUNDEEP
snowflake-cloud-data-platform
KristiLuna
KristiLuna

Reputation: 1893

Snowflake - expecting VARIANT but got VARCHAR for column with MERGE

I've seen a similar question floating around like this but I was unable to apply it to my case.

I'm seeing the below error when trying to merge into a table from an s3 file. (Also, I've dropped this table because previously I did have this monthly_budgets column as a varchar but its a nested column that looks like this [{id:0, etc.}]) and I need to unnest and I believe I can only do this if its a variant column, here's the error:

snowflake.connector.errors.ProgrammingError: 002023 (22000): 019c66f5-0602-3d9f-0000-01b526f2feb2: SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(16777216) for column MONTHLY_BUDGETS

These are my queries:

CREATE TABLE IF NOT EXISTS my_table (
line_name string,
io_id integer,
monthly_budgets variant
            )

and I'm doing this merge:

MERGE INTO my_schema.my_table AS m
USING (
    SELECT  $1 AS col1, $2 AS col2, $3 AS col3
    FROM @luigi.items.csv (file_format => 'my_schema.my_table')
) AS s
ON m.io_idid = s.col2
WHEN MATCHED
THEN UPDATE SET m.line_name = s.col1, m.io_id = s.col2, m.monthly_budgets = s.col3
WHEN NOT MATCHED
THEN INSERT (line_name, io_id, monthly_budgets) VALUES (s.col1, s.col2, s.col3);

Upvotes: 1

Views: 7211

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

If s.col3 is a JSON in the CSV file, then you should be able to turn it into a JSON variant using the parse_json function:

MERGE INTO my_schema.my_table AS m
USING (
    SELECT  $1 AS col1, $2 AS col2, $3 AS col3
    FROM @luigi.items.csv (file_format => 'my_schema.my_table')
) AS s
ON m.io_idid = s.col2
WHEN MATCHED
THEN UPDATE SET m.line_name = s.col1, m.io_id = s.col2, m.monthly_budgets = parse_json(s.col3)
WHEN NOT MATCHED
THEN INSERT (line_name, io_id, monthly_budgets) VALUES (s.col1, s.col2, parse_json(s.col3));

I have no way to test this without sample data, but it should work if the s.col3 is a string with valid JSON data.

Upvotes: 2

Related Questions