Converting a table in one form to another using Snowflake

Question

I am trying to load a CSV file into Snowflake. The sample format of the input csv table in s3 location is as follows (with 2 columns: ID, Location_count): Input csv table

I need to transform it in the below format:(with 3 columns:ID, Location, Count) Output csv table

However when I am trying to load the input file using the following query after creating database, external stage and file format, it returns LOAD_FAILED

create or replace table table_name ( id integer, Location_count variant );

select parse_json(Location_count) as c;

list @stage_name; copy into table_name from @stage_name file_format = 'fileformatname' on_error = 'continue';

Can you please share your code for creating the external stage and the file-format as well as the full error message? — Marcel
You can check if it is a data error by using the VALIDATION_MODE, of the COPY into snowflake command docs.snowflake.com/en/sql-reference/sql/copy-into-table.html — hkandpal

Rob Silva Rob Silva · Accepted Answer · 2021-02-25T02:41:31

you will probably need to parse_json that 2nd column as part of a copy-transformation. For example:

create file format myformat type = csv field_delimiter = ',' FIELD_OPTIONALLY_ENCLOSED_BY = '"';

create or replace stage csv_stage file_format = (format_name = myformat);

copy into @csv_stage from ( select '1', '{"SHS-TRN":654738,"PRN-UTN":78956,"NCT-JHN":96767}') ;

create or replace table blah (id integer, something variant);

copy into blah from (select $1, parse_json($2) from @csv_stage);

Converting a table in one form to another using Snowflake

1 Answers