Spark SQL循环执行时出现WHEN语法解析错误求助

DevOps 小满

2026-7-26

问题描述

单项目的SQL命令可正常执行：

CREATE GROUP testGroup_databricks WITH USER `bju.asd@xxx.com`;
CREATE OR REPLACE VIEW main.deltalake_db.Customers_Log_Table_RLS AS
 SELECT *
 FROM main.deltalake_db.Customers_Log_Table
 WHERE
   CASE
     WHEN is_account_group_member('users') AND requestProjectUrlName = "power-ops-dev" THEN TRUE 
     ELSE FALSE 
   END;

但批量处理的Python循环代码执行时，出现WHEN处的语法解析错误：

target_database = "main.deltalake_db"
distinctprojects = spark.sql("SELECT DISTINCT requestProjectUrlName FROM main.deltalake_db.Customers_Log_Table")

for project in distinctprojects.collect():
    project_name = project['requestProjectUrlName']
    view_name = f"view_{project_name.replace(' ', '_').replace('-', '_')}"
    groupname = f"users_{view_name}"
    create_group = f"""
    CREATE GROUP {groupname} with USER `bju.asd@xxx.com`
    """
    create_view_query = f"""
    CREATE OR REPLACE VIEW {target_database}.{view_name} AS
    SELECT * FROM {target_database}.Customers_Log_Table
    WHERE 
      CASE
        WHEN is_account_group_member('{groupname}') AND requestProjectUrlName = '{project_name}' THEN TRUE 
        ELSE FALSE
    """ 
    try: 
        spark.sql(create_view_query)
        print(f"View {view_name} created successfully.")
    except Exception as e:
        print(f"Error creating view {view_name}: {e}")

错误原因

CASE语句未闭合：原单项目SQL中用END结束CASE逻辑，但循环代码里的CASE只写了ELSE FALSE，缺少最后的END，导致SQL语法不完整，触发解析错误。
未执行创建组的语句：代码中仅定义了create_group字符串，未调用spark.sql(create_group)执行组创建操作。
潜在SQL注入风险：如果project_name或groupname包含单引号，会直接破坏SQL语法结构。

修复后的代码

target_database = "main.deltalake_db"
distinctprojects = spark.sql("SELECT DISTINCT requestProjectUrlName FROM main.deltalake_db.Customers_Log_Table")

for project in distinctprojects.collect():
    project_name = project['requestProjectUrlName']
    # 转义变量中的单引号，避免SQL语法错误
    escaped_project_name = project_name.replace("'", "\\'")
    view_name = f"view_{project_name.replace(' ', '_').replace('-', '_')}"
    groupname = f"users_{view_name}"
    escaped_groupname = groupname.replace("'", "\\'")
    
    # 执行创建组的SQL
    create_group = f"""
    CREATE GROUP {escaped_groupname} WITH USER `bju.asd@xxx.com`;
    """
    try:
        spark.sql(create_group)
        print(f"Group {groupname} created successfully.")
    except Exception as e:
        print(f"Error creating group {groupname}: {e}")
    
    # 修复CASE语句，添加END闭合，同时转义变量
    create_view_query = f"""
    CREATE OR REPLACE VIEW {target_database}.{view_name} AS
    SELECT * FROM {target_database}.Customers_Log_Table
    WHERE 
      CASE
        WHEN is_account_group_member('{escaped_groupname}') AND requestProjectUrlName = '{escaped_project_name}' THEN TRUE 
        ELSE FALSE
      END;
    """ 
    try: 
        spark.sql(create_view_query)
        print(f"View {view_name} created successfully.")
    except Exception as e:
        print(f"Error creating view {view_name}: {e}")

内容的提问来源于stack exchange，提问作者ZZZSharePoint