CODEWITHSUNDEEP
snowflake-cloud-data-platform
jmizzo
jmizzo

Reputation: 43

insert csv file into snowflake as variant not working

I am trying to copy csv data into snowflake table with only one column(variant). When I run copy into statement, my variant column is only displaying data from first column. I'm not sure what the problem is. Please help.

Create or replace table name( RAW variant )

COPY INTO db_name.table_name FROM (SELECT s.$1::VARIANT FROM @stage_name.csv s);

Upvotes: 1

Views: 1309

Answers (2)

Usha Rani
Usha Rani

Reputation: 1

In your scenario, when loading data into a single VARIANT column, Snowflake expects each CSV row to be treated as a single value for that column. However, with your current COPY INTO command, it’s only grabbing the first column because the query selects s.$1::VARIANT, which references only the first column of the CSV.

To capture the entire row as a JSON-like structure in a single VARIANT column, you can use the OBJECT_CONSTRUCT function to create a JSON object from the whole row. Here’s how to modify your COPY INTO statement:

COPY INTO db_name.table_name FROM (SELECT OBJECT_CONSTRUCT(*) FROM @stage_name.csv s);

This approach will load each row as a JSON object into your VARIANT column, capturing all the columns from the CSV file.

Upvotes: 0

Jeffrey Jacobs
Jeffrey Jacobs

Reputation: 332

Assuming your .csv file consists of multiple, valid JSON, try using a file format of type JSON instead of .csv. See https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html. Alternatively, use PARSE_JSON in your SELECT.

Upvotes: 2

Related Questions