不支持 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= SELECT A.E_No,A.E_Name FROM Scheema.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;HAVING WHERE GROUP ORDER INTERSECT MINUS EXCEPT UNION ) , 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("user", 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

"select a, b, c, d from table where d is not null with UR as table" ，这与在大型机 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

select a, b, c from (select a, b, c from table where d is not null with UR) as table这就是麻烦开始的地方.

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

push the sql as ""select a, b, c, d from table as tbl"

push the sql as ""select a, b, c, d from table as tbl"

with predicates= Array("d is not null with 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屋！