删除pyspark中特定条件下的特定行

xdnvmnnf  于 2021-05-27  发布在  Spark
关注(0)|答案(0)|浏览(326)

我是spark的新手。我想用sparksql删除一行。由于tettable中的不兼容删除,到目前为止我已经读到,像sql查询一样操作delete,我需要将表永久保存在pyspark中,我猜就是hive table。我也这么做了,我的代码是

spark.sql("select b.ENTITYID as ENTITYID, cm.BLDGID as BldgID,cm.LEASID as LeaseID,coalesce(l.SUITID,(select EmptyDefault from EmptyDefault)) as SuiteID,(select CurrDate from CurrDate) as TxnDate,cm.INCCAT as IncomeCat,'??' as SourceCode,(Select CurrPeriod from CurrPeriod)as Period,coalesce(case when cm.DEPARTMENT ='@' then 'null' else cm.DEPARTMENT end, null) as Dept,'Lease' as ActualProjected ,fnGetChargeInd(cm.EFFDATE,cm.FRQUENCY,cm.BEGMONTH,(select CurrPeriod from CurrPeriod))*coalesce (cm.AMOUNT,0) as  ChargeAmt,0 as OpenAmt,cm.CURRCODE as CurrencyCode,case when ('PERIOD.DATACLSD') is null then 'Open' else 'Closed' end as GLClosedStatus,'Unposted'as GLPostedStatus ,'Unpaid' as PaidStatus,cm.FRQUENCY as Frequency,0 as RetroPD from CMRECC cm join BLDG b on cm.BLDGID =b.BLDGID join LEAS l on cm.BLDGID =l.BLDGID and cm.LEASID =l.LEASID and (l.VACATE is null or l.VACATE >= ('select CurrDate from CurrDate')) and (l.EXPIR >= ('select CurrDate from CurrDate') or l.EXPIR < ('select RunDate from RunDate')) left outer join PERIOD on b.ENTITYID =  PERIOD.ENTITYID and ('select CurrPeriod from CurrPeriod')=PERIOD.PERIOD where ('select CurrDate from CurrDate')>=cm.EFFDATE  and (select CurrDate from CurrDate) <= coalesce(cm.EFFDATE,cast(date_add(( select min(cm2.EFFDATE) from CMRECC cm2 where cm2.BLDGID = cm.BLDGID and cm2.LEASID = cm.LEASID and cm2.INCCAT = cm.INCCAT and 'cm2.EFFDATE' > 'cm.EFFDATE'),-1) as timestamp)  ,case when l.EXPIR <(select RunDate from RunDate)then (Select RunDate from RunDate) else l.EXPIR end)").write.saveAsTable('Fact_Temp1')

在那之后,我的Pypark里有了一张永久的table
接下来我查询了delete操作

spark.sql("DELETE from Fact_Temp1 where ActualProjected='Lease' and  ChargAmt=0").show()

我犯了这个错误

pyspark.sql.utils.ParseException: u"\nOperation not allowed: DELETE from(line 1, pos 0)\n\n== SQL ==\nDELETE from Fact_Temp1 where ActualProjected='Lease'and ChargeAmt=0\n^^^\n"

我有点困惑。有什么不同的方法写这个吗。我不知道为什么我会出现这个错误。请指导我,我使用的是spark 2.0 kalyan

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题