Snowflake Connector Error: 001003 (42000): SQL compilation error (syntax error)

Viewed 4866

I am trying to load a pandas dataframe into a Snowflake table.

Here is my SF table DDL:

create or replace TABLE MY_TABLE (
    WEBSITE VARCHAR(16777216),
    ORGANIZATION_NAME VARCHAR(16777216),
    ORGANIZATION_NAME_URL VARCHAR(16777216),
    IPO_STATUS VARCHAR(16777216),
    FOUNDED_DATE VARCHAR(16777216),
    FOUNDED_DATE_PRECISION VARCHAR(16777216),
    TOTAL_FUNDING_AMOUNT VARCHAR(16777216),
    TOTAL_FUNDING_AMOUNT_CURRENCY VARCHAR(16777216),
    TOTAL_FUNDING_AMOUNT_CURRENCY_USD VARCHAR(16777216),
    LAST_FUNDING_AMOUNT VARCHAR(16777216),
    LAST_FUNDING_AMOUNT_CURRENCY VARCHAR(16777216),
    LAST_FUNDING_AMOUNT_CURRENCY_USD VARCHAR(16777216),
    LAST_FUNDING_DATE VARCHAR(16777216),
    INDUSTRIES VARCHAR(16777216),
    INDUSTRY_GROUPS VARCHAR(16777216),
    LAST_FUNDING_TYPE VARCHAR(16777216),
    IT_SPEND VARCHAR(16777216),
    IT_SPEND_CURRENCY VARCHAR(16777216),
    IT_SPEND_CURRENCY_USD VARCHAR(16777216)
);

And here is the structure of my dataframe:

Data columns (total 19 columns):
 #   Column                                  Non-Null Count  Dtype  
---  ------                                  --------------  -----  
 0   Website                                 3924 non-null   object 
 1   Organization Name                       3924 non-null   object 
 2   Organization Name URL                   3924 non-null   object 
 3   IPO Status                              3924 non-null   object 
 4   Founded Date                            3837 non-null   object 
 5   Founded Date Precision                  3837 non-null   object 
 6   Total Funding Amount                    2397 non-null   float64
 7   Total Funding Amount Currency           2397 non-null   object 
 8   Total Funding Amount Currency (in USD)  2397 non-null   float64
 9   Last Funding Amount                     2259 non-null   float64
 10  Last Funding Amount Currency            2259 non-null   object 
 11  Last Funding Amount Currency (in USD)   2259 non-null   float64
 12  Last Funding Date                       2612 non-null   object 
 13  Industries                              3894 non-null   object 
 14  Industry Groups                         3894 non-null   object 
 15  Last Funding Type                       2612 non-null   object 
 16  Aberdeen - IT Spend                     1576 non-null   float64
 17  Aberdeen - IT Spend Currency            1627 non-null   object 
 18  Aberdeen - IT Spend Currency (in USD)   1576 non-null   float64

Per Snowflake's documentation, I've written the following code to help me write the dataframe into my database:

success, nchunks, nrows, _ = write_pandas(conn, 
                                          df, 
                                          "MY_DB.MY_SCHEMA.MY_TABLE", 
                                          quote_identifiers=False)

print(str(success) + ', ' + str(nchunks) + ', ' + str(nrows))

The problem I run into is the following:

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 133 unexpected 'Name'.

I have used this function for ingesting other dataframes and there have been no issues in the past (so I know that my connection/ connector is working) - maybe there is something wrong with my data or how I've set up the table in Snowflake? I have some NaN values.. could this be an issue?

Here is an excerpt of the data:

enter image description here

I have also checked out other SO answers, such as here and here but they do not relate to my problem directly. I am relatively new to Snowflake and SQL so any help to resolve this would be greatly appreciated!

1 Answers

you have spaces in your df column names , you have to set quote_identifiers parameter set to true :

success, nchunks, nrows, _ = write_pandas(conn, 
                                          df, 
                                          "MY_DB.MY_SCHEMA.MY_TABLE", 
                                          quote_identifiers=True)

although I'm not sure if snowflake will be able to map it to your columns where they have underscore instead of space

Related