CODEWITHSUNDEEP
pythonsqlalchemysnowflake-cloud-data-platform
LunaLoveDove
LunaLoveDove

Reputation: 85

Snowflake: SQL compilation error: error line invalid identifier '"dateutc"'

I'm moving data from Postgres to snowflake. Originally it worked however I've added:

df_postgres["dateutc"]= pd.to_datetime(df_postgres["dateutc"])

because the date format was incorrectly loading to snowflake and now I see this error:

SQL compilation error: error line 1 at position 87 invalid identifier '"dateutc"'

Here is my code:

from sqlalchemy import create_engine
import pandas as pd
import glob
import os
from config import postgres_user, postgres_pass, host,port, postgres_db, snow_user, snow_pass,snow_account,snow_warehouse   
from snowflake.connector.pandas_tools import pd_writer
from snowflake.sqlalchemy import URL


from sqlalchemy.dialects import registry
registry.register('snowflake', 'snowflake.sqlalchemy', 'dialect')

    
engine = create_engine(f'postgresql://{postgres_user}:{postgres_pass}@{host}:{port}/{postgres_db}')


conn = engine.connect()

#reads query
df_postgres = pd.read_sql("SELECT * FROM rok.my_table", conn)

#dropping these columns
drop_cols=['RPM', 'RPT']
df_postgres.drop(drop_cols, inplace=True, axis=1)

#changed columns to lowercase
df_postgres.columns = df_postgres.columns.str.lower()


df_postgres["dateutc"]= pd.to_datetime(df_postgres["dateutc"])


print(df_postgres.dateutc.dtype)

sf_conn = create_engine(URL(
    account = snow_account,
    user = snow_user,
    password = snow_pass,
    database = 'test',
    schema = 'my_schema',
    warehouse = 'test',
    role = 'test',
))



df_postgres.to_sql(name='my_table',
                 index = False,  
                 con = sf_conn,
                 if_exists = 'append', 
                 chunksize = 300,
                 method = pd_writer)

Upvotes: 2

Views: 4820

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

Moving Ilja's answer from comment to answer for completeness:

  • Snowflake is case sensitive.
  • When writing "unquoted" SQL, Snowflake will convert table names and fields to uppercase.
  • This usually works, until someone decides to start quoting their identifiers in SQL.
  • pd_writer adds quotes to identifiers.
  • Hence when you have df_postgres["dateutc"] it remains in lowercase when its transformed into a fully quoted query.
  • Writing df_postgres["DATEUTC"] in Python should fix the issue.

Upvotes: 5

Related Questions