时间戳

首页 > 解决方案 > net.snowflake.client.jdbc.SnowflakeSQLException：SQL 访问控制错误：权限不足，无法对架构“PUBLIC”进行操作

apache-spark - net.snowflake.client.jdbc.SnowflakeSQLException：SQL 访问控制错误：权限不足，无法对架构“PUBLIC”进行操作

问题描述

尝试使用数据框时出现错误。

这是我在 pyspark 的 databricks 笔记本中运行的一段代码，用于从雪花中获取数据

query = "SELECT * FROM TEST_TABLE LIMIT 10"
  SNOWFLAKE_SOURCE_NAME = "net.snowflake.spark.snowflake"
  df = spark.read.format(SNOWFLAKE_SOURCE_NAME).options(**sfOptions).option("query", query).load()
  print(type(df))
  print(df.count())
  print((df.count(), len(df.columns)))

数据框的大小和列数是正确的。

当我做

df.show()

我得到以下错误

  Py4JJavaError: An error occurred while calling o1263.showString.
    : net.snowflake.client.jdbc.SnowflakeSQLException: SQL access control error:
    Insufficient privileges to operate on schema 'PUBLIC'
        at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:152)
        at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)
        at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:495)
        at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:372)
        at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:575)
        at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:265)
        at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:203)
        at net.snowflake.client.core.SFStatement.execute(SFStatement.java:874)
        at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:259)
        at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:181)
        at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$executePreparedQueryInterruptibly$1.apply(SnowflakeJDBCWrapper.scala:317)
        at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$executePreparedQueryInterruptibly$1.apply(SnowflakeJDBCWrapper.scala:315)
        at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$1.apply(SnowflakeJDBCWrapper.scala:355)
        at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
        at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

提前致谢。请帮我。

编辑：问题是因为在通过 spark 在雪花中运行查询时，我无权创建临时阶段。

当我更改雪花客户端选项时，问题已解决，如下所示：

sfOptions = {
        "sfURL": "xxx.snowflakecomputing.com",
        "sfUser": "xxxx",
        "sfDatabase": "TEMP", <- changed this to TEMP from PUBLIC
        "sfSchema": "PUBLIC",
        "sfWarehouse": "xxx",
        "sfRole": "xxxx",
        "pem_private_key": private_key
    }

他们授权开发人员仅在 TEMP DB 中创建临时阶段。

标签： apache-sparkpysparksnowflake-cloud-data-platformdatabricks

解决方案

也许执行以下语句以授予访问权限对您有所帮助。原因可能是您向表对象授予了选择权限，但没有授予数据库和/或模式级别的使用权限。

GRANT USAGE ON DATABASE [database] TO ROLE [role];
GRANT USAGE ON SCHEMA [database].PUBLIC TO ROLE [role];
GRANT SELECT ON TABLE [database].PUBLIC.[table] TO ROLE [role];

文档：https ://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html

推荐阅读