CODEWITHSUNDEEP
pysparkaws-gluesnowflake-cloud-data-platform
Basant Jain
Basant Jain

Reputation: 115

Snowflake : SQL access control error: Insufficient privileges to operate on schema

I have written aws glue job where i am trying to read snowflake tables as spark dataframe and also trying to write a spark dataframe into the snowflake tables. My job is failing stating "Insufficient privileges to operate on schema" in both scenario.

But when i am directly writing insert statement on snowflake cli, i am able to insert data. So basically i have insert privilege.

So why my job is failing when i am trying to insert data from dataframe or reading data from snowflake table as a dataframe?

Below is my code to write data into snowflake table.

sfOptions = {
    "sfURL" : "xt30972.snowflakecomputing.com",
    "sfAccount" : "*****",
    "sfUser" : "*****",
    "sfPassword" : "****",
    "sfDatabase" : "*****",
    "sfSchema" : "******"
}

df=spark.read.format("csv").option("header","false").option("delimiter",',').load(aws s3 file_name)

df2.write.format("net.snowflake.spark.snowflake") \
.options(**sfOptions) \
.option("dbtable", table_name) \
.mode("append") \
.save()

Upvotes: 0

Views: 6074

Answers (1)

mevdiven
mevdiven

Reputation: 1902

When you are using Snowflake CLI, I assume that you switch to a proper role to execute SELECT or INSERT. On Spark, you need to manually switch to the role that has SELECT/INSERT grants before operating on a table. You do this by issuing below.

Utils.runQuery(sfOptions, "USE ROLE <your_role>")

This will switch the role for the duration of your Spark session.

Also, please note that Snowflake's access structure is hierarchy based. That means you need to have "usage" privileges on the database and schema that houses the table you are trying to use. Please make sure that you have all the right grants to the role using to SELECT or INSERT.

Upvotes: 1

Related Questions