'Error parsing json while writing spark dataframe to a snowflake table with the column with json structure

I have a pyspark dataframe with only one column "new_col" with json structure as below:

df.show(truncate=False)

# +----+------------+-----+----+-------------------+----+----+---------------------------------------------------------------------------------------------------------------------------------+
# new_col                                                                                                                          |
# +----+------------+-----+----+-------------------+----+----+---------------------------------------------------------------------------------------------------------------------------------+
# {"Code":"C","Department":"Rest"}, {"Code":"B","Department":"All Other Suppliers"}                                             |
# {"Code":"C","Department":"Rest"}, {"Code":"B","Department":"All Other Suppliers"}, {"Code":"A","Department":"Department Store"}|
# {"Code":"A","Department":"Department Store"}                                                                                  |
# +----+------------+-----+----+-------------------+----+----+---------------------------------------------------------------------------------------------------------------------------------+

I want to write this spark dataframe to snowflake table (TEST) with the below DDL

CREATE or replace TABLE TEST
    (new_col OBJECT);

Here is my function to write the dataframe to the snowflake table

def write_df_to_snowflake(df, table_name, snowflake_options, column_mapping=None):
    if column_mapping is not None:
        df.write.format(SNOWFLAKE_SOURCE_NAME).options(**snowflake_options) \
            .option("dbtable", table_name) \
            .option('columnmap', column_mapping) \
            .mode('append').save()
    else:
        df.write.format(SNOWFLAKE_SOURCE_NAME).options(**snowflake_options) \
            .option("dbtable", table_name) \
            .mode('append').save()

I am getting "Error parsing json" as shown in the screenshot when I try to write it to a snowflake table "TEST" enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source