时间戳

首页 > 解决方案 > net.snowflake.client.jdbc.SnowflakeSQLException: SQL 编译错误: 读者账户不支持操作

apache-spark - net.snowflake.client.jdbc.SnowflakeSQLException: SQL 编译错误: 读者账户不支持操作

问题描述

我正在尝试从 Snowflake 中创建的视图中读取数据，并通过 PySpark 将数据存储到 GCS。

SNOWFLAKE_SOURCE_NAME = "net.snowflake.spark.snowflake"

sfOptions = {
  "sfURL" : "XXX.snowflakecomputing.com",
  "sfUser" : "XXX",
  "sfPassword" : "XXX",
  "sfDatabase" : "DB",
  "sfSchema" : "XXX",
  "sfWarehouse": "DWH"
}

df = spark.read.format(SNOWFLAKE_SOURCE_NAME) \
  options(**sfOptions) \
  option("query",  "SELECT * FROM JOB_v1").load()

df.show()

我正在使用以下软件包：

packages net.snowflake:snowflake-jdbc:3.8.0
net.snowflake:spark-snowflake_2.11:2.4.14-spark_2.4

我可以加载数据、计算行数、打印模式，但是当我尝试写入或显示数据框时，会出现以下异常：

Py4JJavaError: An error occurred while calling o43.showString.
: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Operation is not supported in reader account.
    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:139)
    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)
    at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:491)
    at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:368)
    at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:486)
    at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:237)
    at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:176)
    at net.snowflake.client.core.SFStatement.execute(SFStatement.java:683)
    at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:242)
    at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:160)
    at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$executePreparedQueryInterruptibly$1.apply(SnowflakeJDBCWrapper.scala:256)
    at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$executePreparedQueryInterruptibly$1.apply(SnowflakeJDBCWrapper.scala:254)
    at net.snowflake.spark.snowflake.JDBCWrapper$$anonfun$1.apply(SnowflakeJDBCWrapper.scala:291)
    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)

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

解决方案

这里需要考虑几件事：

  1. 读者帐户主要用于查询帐户提供者共享的数据。不支持向帐户添加新数据和/或更新帐户中的共享数据 - 详细信息：https ://docs.snowflake.com/en/user-guide/data-sharing-reader-create.html#what-is -restricted-allowed-in-a-reader-account

  2. use_copy_unload - 如果为 FALSE，Snowflake 在选择数据时使用箭头数据格式。如果设置为 TRUE，则 Snowflake 恢复为使用 COPY UNLOAD 命令传输选定数据的旧行为。此参数是可选的。默认值为假。

推荐阅读