azure - 如何识别此 Pyspark 编码问题 - 输入时没有可行的替代方案

Question

尝试在 Azure Synapse pyspark 中运行以下代码并收到解析错误，Synapse 似乎不接受双括号，有人知道如何修复它吗？

def curated_report(entity_name):
    sqlstr ="WITH Participant_Name \
    AS (SELECT \
    CASEID, \
    PARTICIPANTID, \
    LASTWRITTEN, \
    PARTICIPANT, \
    FIRSTNAME, \
    MIDDLENAME, \
    LASTNAME \
    FROM (SELECT \
    ab.CASEID, \
    ab.PARTICIPANTID, \
    ab.DYNAMICDATATYPE, \
    ab.DYNAMICEVIDENCEVALUE, \
    ab.LASTWRITTEN \
    FROM a.ev ab \
    INNER JOIN (SELECT \
    PARTICIPANTID, \
    MAX(LASTWRITTEN) AS MAXDATE \
    FROM a.bd \
    where TYPE in ( 'PDC001' ) \
    GROUP BY PARTICIPANTID) cd \
    ON ab.PARTICIPANTID = cd.PARTICIPANTID \
    AND ab.LASTWRITTEN = cd.MAXDATE \
    GROUP BY ab.CASEID, \
    ab.PARTICIPANTID, \
    ab.DYNAMICDATATYPE, \
    ab.DYNAMICEVIDENCEVALUE, \
    ab.LASTWRITTEN) AS SOURCE \
    PIVOT(max(DYNAMICEVIDENCEVALUE) \
    FOR DYNAMICDATATYPE IN (PARTICIPANT, \
    FIRSTNAME, \
    MIDDLENAME,\
    LASTNAME) \
    )AS RESULT) \         <----*this line seems to be causing error*
    SELECT* \
    FROM PARTICIPANT_NAME"
    df = spark.sql(sqlstr)
    return df


*solved.

Answer 1

ParseException:no viable alternative at input 'WITH Participant_Name AS (SELECT ...

从查询中删除 as RESULT。对于CTE表，给出CTE表名称就足够了。

错误屏幕截图我尝试使用类似的脚本重现此内容并得到相同的错误。

• 为了避免此错误，我从查询中删除了别名 as Result，结果执行成功。

更正的代码

sqlstr ="WITH Participant_Name \
    AS (SELECT \
    CASEID, \
    PARTICIPANTID, \
    LASTWRITTEN, \
    PARTICIPANT, \
    FIRSTNAME, \
    MIDDLENAME, \
    LASTNAME \
    FROM (SELECT \
    ab.CASEID, \
    ab.PARTICIPANTID, \
    ab.DYNAMICDATATYPE, \
    ab.DYNAMICEVIDENCEVALUE, \
    ab.LASTWRITTEN \
    FROM enhanced.BDMCASEEVIDENCE ab \
    INNER JOIN (SELECT \
    PARTICIPANTID, \
    MAX(LASTWRITTEN) AS MAXDATE \
    FROM enhanced.BDMCASEEVIDENCE \
    where EVIDENCETYPE in ( 'PDC0000258' ) \
    GROUP BY PARTICIPANTID) cd \
    ON ab.PARTICIPANTID = cd.PARTICIPANTID \
    AND ab.LASTWRITTEN = cd.MAXDATE \
    GROUP BY ab.CASEID, \
    ab.PARTICIPANTID, \
    ab.DYNAMICDATATYPE, \
    ab.DYNAMICEVIDENCEVALUE, \
    ab.LASTWRITTEN) AS SOURCE \
    PIVOT(max(DYNAMICEVIDENCEVALUE) \
    FOR DYNAMICDATATYPE IN ('PARTICIPANT', \
    'FIRSTNAME', \
    'MIDDLENAME',\
    'LASTNAME') \
    )) \         
    SELECT* \
    FROM PARTICIPANT_NAME"
    df = spark.sql(sqlstr)