write_pandas snowflake connector function is not able to operate on table
write_pandas() does not create the table automatically. You need to create the table by yourself if the table does not exist beforehand. For each time you run write_pandas(), it will just append the dataframe to the table you specified.
On the other hand, if you use df.to_sql(..., method=pd_writer) to write pandas dataframe into snowflake, it will create the table automatically for you, and you can use if_exists in to_sql() to specify different behaviors - append, replace, or fail - if the table already exists.
I am working on a python script that is designed to process some data, create a table if not exists, and truncate the table before inserting a refreshed dataset. I am using a role that has usage, read, write, create table permissions, as well stage permissions set as follows:
grant usage, read, write on future stages in schema <schema> to role <role>
I am using the write_pandas function in python via the snowflake connector. The documentation says that this function uses PUT and Copy Into commands:
To write the data to the table, the function saves the data to Parquet files, uses the PUT command to upload these files to a temporary stage, and uses the COPY INTO <table> command to copy the data from the files to the table. You can use some of the function parameters to control how the PUT and COPY INTO <table> statements are executed.
I still get the error message that I am unable to operate on the schema, and I am not sure what else I need to add. Does someone have the list of permissions that are required to run the write_pandas command?