不支持Spark sql上下文中的WITH子句 [英] WITH Clause in spark sql Context not supported

问题描述

我正在尝试通过 spark sql context 通过以下查询

I am trying to fetch records from mainframe table using spark sql context with the below query

data_config.db2_qry =从Scheema中选择A.E_No，A.E_Name.Employee A WITH UR

data_config.db2_qry= SELECT A.E_No,A.E_Name FROM Scheema.Employee A WITH UR

但它抛出以下错误

com.ibm.db2.jcc.am.SqlSyntaxErrorException:DB2 SQL错误:SQLCODE = -199，SQLSTATE = 42601，SQLERRMC = WITH;其中组顺序相减减去工会)，FETCH，DRIVER = 4.19.26

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=WITH;HAVING WHERE GROUP ORDER INTERSECT MINUS EXCEPT UNION ) , FETCH, DRIVER=4.19.26

但是如果我直接在大型机控制台中运行相同的查询，它将很好地工作.

but if I run the same query in mainframe console directly it works fine.

如何在Spark的SQL上下文中使用 WITH 子句?

How to use WITH clause in sql context of spark?

我正在使用Spark版本2.4.0

I am using spark version 2.4.0

我正在检索以下记录

filt_cond =(" + data_config.db2_qry +)ref_id"

filt_cond = "(" + data_config.db2_qry + ") ref_id"

db2Df = sqlContext.read.format("jdbc").option("url"，data_config.db2_url).option("driver"，"com.ibm.db2.jcc.DB2Driver").option("dbtable"，filt_cond).option(用户"，data_config.db2_uname).option("password"，data_config.db2_passwd).load()

db2Df = sqlContext.read.format("jdbc").option("url", data_config.db2_url).option("driver", "com.ibm.db2.jcc.DB2Driver").option( "dbtable", filt_cond).option("user", data_config.db2_uname).option("password", data_config.db2_passwd).load()

推荐答案

问题出在向下发送到Mainframe DB2的查询中，用于更改"WITH UR"的spark jdbc方法选择需要更改.

The issue is in query that sent down to Mainframe DB2, spark jdbc method choice used to push "WITH UR" needs change.

spark jdbc读取方法是

spark jdbc read method used here is

def jdbc(url: String, table: String, properties: Properties): DataFrame

，用这种方法说，我们将以下查询推送到db2 sql引擎

and in this method say we pushing the following query to db2 sql engine

从表中选择a，b，c，d，其中d不为null，而UR作为表" ，这与在Mainframe DB2 SQL引擎中推送的查询不同.spark将sql发送为

"select a, b, c, d from table where d is not null with UR as table" , it's not the same query pushed inside the Mainframe DB2 SQL engine. spark sends the sql as

从表中选择a，b，c(从d中选择d，其中UR不为null的表中选择a，b，c)作为表这是麻烦开始的地方.

select a, b, c from (select a, b, c from table where d is not null with UR) as table this is where trouble started.

如果您希望在Mainframe SPUFI或QMF或其他工具中看到与sql相同的错误，请尝试通过spark而不是我们在代码中编写的内容来运行构造的查询.

if you want to see the same error for the sql in Mainframe SPUFI or QMF or with other tool, try running the constructed query by spark rather than what we wrote in code.

要克服在SQL上添加"WITH UR"语法的问题，而不是上面的spark jdbc方法切换到以下允许我们构造谓词的spark jdbc方法.

To overcome this issue on adding "WITH UR" syntax to SQL, instead of above spark jdbc method switch to following spark jdbc method that allows us to construct predicates.

 def jdbc(url: String, table: String, predicates: Array[String],
 connectionProperties: Properties): DataFrame

将SQL作为"从表中选择a，b，c，d作为tbl"

with predicates = Array("d在UR中不为空")

在这种情况下，预期查询被下推.希望这可以帮助您找到解决问题的方向.

in this case the expected query is pushed down. Hope this helps you to get the direction to solve it.

在这里您可以看到有关spark jdbc读取方法的更多详细信息-

here you can see more detail on spark jdbc read methods- Link

这篇关于不支持Spark sql上下文中的WITH子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！