CODEWITHSUNDEEP
pythonapache-spark-sql
Shuo Xu
Shuo Xu

Reputation: 51

ParseException in SparkSQL

I have the following code:

query = f""" SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) + {pDimensionkey} AS ibfPhaseAssignmentKey, Base.* FROM ( SELECT DISTINCT Phase.lxOid AS 'PhaseID' , TAName.mxFullname AS 'Phase Assignee' , DurationAttr.lxVal AS 'Phase Estimated Duration' , EstStartDate.lxVal AS 'Phase Estimated Start Date' , EstFinishDate.lxVal AS 'Phase Estimated Finish Date' , ActStartDate.lxVal AS 'Phase Actual Start Date' , ActEndDate.lxVal AS 'Phase Actual Completion Date' , relatedTask.lxOid AS 'Related Object ID' , pr.ProjectId AS 'Related Project ID' FROM lippertus_Enoviasilver.lxBO_49243908 Phase LEFT JOIN lippertus_Enoviasilver.mxBusType PhaseType ON PhaseType.mxOid = Phase.lxType AND Phase.lxType = '1071955709' LEFT JOIN lippertus_Enoviasilver.lxRO_49243908 TARO ON Phase.lxOid = TARO.lxToId AND TARO.lxType = '1968973284' LEFT JOIN lippertus_Enoviasilver.lxBO_49243908 TA ON TARO.lxFromId = TA.lxOid LEFT JOIN lippertus_Enoviasilver.mxUser TAName ON TA.lxName = TAName.mxName LEFT JOIN lippertus_Enoviasilver.mxBusType TAType ON TA.lxType = TAType.mxOid LEFT JOIN lippertus_Enoviasilver.lxReal_49243908 DurationAttr ON Phase.lxOid = DurationAttr.lxOid AND DurationAttr.lxType = '-668419160' LEFT JOIN lippertus_Enoviasilver.lxDate_49243908 EstStartDate ON Phase.lxOid = EstStartDate.lxOid AND EstStartDate.lxType = '-668414286' LEFT JOIN lippertus_Enoviasilver.lxDate_49243908 EstFinishDate ON Phase.lxOid = EstFinishDate.lxOid AND EstFinishDate.lxType = '-668416845' LEFT JOIN lippertus_Enoviasilver.lxDate_49243908 ActStartDate ON Phase.lxOid = ActStartDate.lxOid AND ActStartDate.lxType = '-308477270' LEFT JOIN lippertus_Enoviasilver.lxDate_49243908 ActEndDate ON Phase.lxOid = ActEndDate.lxOid AND ActEndDate.lxType = '1454168401' LEFT JOIN lippertus_Enoviasilver.lxRO_49243908 PhaseRel ON Phase.lxOid = PhaseRel.lxFromId LEFT JOIN lippertus_Enoviasilver.lxBO_49243908 relatedTask ON PhaseRel.lxToId = relatedTask.lxOid LEFT JOIN lippertus_Enoviasilver.lxRO_49243908 phasesRO ON phasesRO.lxToId = phase.lxOid LEFT JOIN lippertus_Enoviasilver.mxRelType projectRelType ON projectRelType.mxOid = phasesRO.lxType AND projectRelType.mxName = 'Subtask' LEFT JOIN empower.Project pr ON phasesRO.lxFromId = pr.ProjectId) Base"""

source_query = spark.sql(query)

which is giving me an ParseException issue saying:

mismatched input ''PhaseID'' expecting {')', ',', 'CLUSTER', 'DISTRIBUTE', 'EXCEPT', 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LATERAL', 'LIMIT', 'ORDER', 'MINUS', 'SORT', 'UNION', 'WHERE', 'WINDOW', '-'}(line 4, pos 31)

== SQL ==

    SELECT ROW_NUMBER() OVER (ORDER BY  (SELECT 1)) + 0 AS ibfPhaseAssignmentKey, Base.* FROM  (
    SELECT DISTINCT 
            Phase.lxOid AS 'PhaseID'

-------------------------------^^^ , TAName.mxFullname AS 'Phase Assignee' , DurationAttr.lxVal AS 'Phase Estimated Duration'

I'm not sure if it's something related to the single quotation mark or whatever. Can someone help me out here? Thanks

I tried to use ''' or ' to replace single quotation mark but none worked out...

Upvotes: 0

Views: 37

Answers (1)

Dimitris
Dimitris

Reputation: 11

you should use unquoted column alias like PhaseID , Try to change: Phase.lxOid AS 'PhaseID' to Phase.lxOid AS PhaseID

and similarly for the other column aliases in your query.

You can also use backticks to specify alias but not in common practice. It will change your line to Phase.lxOid ASPhaseID`

NB : I highly recommend you to validate the query by executing it on a development database before running it on production to make sure that it returns the expected results and doesn't cause any issues :-)

Thanks

Best Regards

Upvotes: 1

Related Questions