Spark Hadoop执行SQL报错：extraneous input 'CREATE'预期{<EOF>, ';'}

问题描述 #

在Spark Hadoop环境中执行SQL查询时触发解析错误：extraneous input 'CREATE' expecting {<EOF>, ';'}，但该SQL在临时查询手册（adhoc query book）中可正常运行。

报错栈信息 #

Traceback (most recent call last):
  File "pinnability_calibration_reporting.py", line 67, in <module>
    run()
  File "pinnability_calibration_reporting.py", line 63, in run
    spark_session.sql(query)
  File "/data/nvme2n1/nm-local-dir/usercache/appcache/application_1699031624646_295484/container_e82_1699031624646_295484_01_000015/pyspark.zip/pyspark/sql/session.py", line 723, in sql
  File "/data/nvme2n1/nm-local-dir/usercache/appcache/application_1699031624646_295484/container_e82_1699031624646_295484_01_000015/py4j-0.10.9.2-src.zip/py4j/java_gateway.py", line 1309, in __call__
  File "/data/nvme2n1/nm-local-dir/usercache/appcache/application_1699031624646_295484/container_e82_1699031624646_295484_01_000015/pyspark.zip/pyspark/sql/utils.py", line 117, in deco
pyspark.sql.utils.ParseException: 
extraneous input 'CREATE' expecting {<EOF>, ';'}(line 3, pos 8)

原SQL关键错误片段 #

DROP TABLE IF EXISTS pinnability.calibration_summary_reporting_v2_2023_12_06;
CREATE TABLE pinnability.calibration_summary_reporting_v2_2023_12_06 (
    SELECT ... -- 后续UNION查询省略
);

错误原因 #

1. 多语句执行限制：Spark SQL默认不允许在单个spark_session.sql()调用中执行多条独立SQL语句（如DROP + CREATE），而临时查询工具通常支持多语句批量执行。
2. CREATE TABLE语法错误：原SQL使用了CREATE TABLE (...) SELECT ...的非标准格式，Spark SQL的CTAS（Create Table As Select）语法应为CREATE TABLE ... AS SELECT ...，不需要用括号包裹查询语句。

解决步骤 #

方案1：拆分语句+修正语法（推荐） #

将DROP和CREATE语句分开执行，同时修正CTAS语法：

# 执行DROP语句
spark_session.sql("DROP TABLE IF EXISTS pinnability.calibration_summary_reporting_v2_2023_12_06;")

# 修正后的CREATE TABLE语句
create_sql = """
CREATE TABLE pinnability.calibration_summary_reporting_v2_2023_12_06 AS
SELECT dt, DECODE(`PASSED_IN_DATA/pinnability__context__modelId`, 'utf-8') AS model_id,
    'is_repin' AS head_name,
    AVG(`ATTACHED_DATA/pinnability__online_pin__calibratedRepinScore`) AS avg_score,
    COUNT_IF(`LABEL/is_repin` = TRUE) AS positives,
    COUNT(*) AS impressions,
    CAST(COUNT_IF(`LABEL/is_repin` = TRUE) AS DOUBLE) / CAST(COUNT(*) AS DOUBLE) AS empirical_rate,
    AVG(`ATTACHED_DATA/pinnability__online_pin__calibratedRepinScore`)/(CAST(COUNT_IF(`LABEL/is_repin` = TRUE) AS DOUBLE) / CAST(COUNT(*) AS DOUBLE)) AS calibration
FROM pinnability.calibrationlabelswithfeatures
WHERE
    `ATTACHED_DATA/pinnability__online_pin__calibratedRepinScore` IS NOT NULL
    AND dt = '2023-12-06'
GROUP BY 1, 2

UNION ALL

-- 其余UNION查询部分保持不变
SELECT dt, DECODE(`PASSED_IN_DATA/pinnability__context__modelId`, 'utf-8') AS model_id,
    'is_click' AS head_name,
    ...
GROUP BY 1, 2
"""
# 执行CREATE语句
spark_session.sql(create_sql)

方案2：开启多语句支持（谨慎使用） #

如果需要保留多语句批量执行，可在创建SparkSession时添加以下配置，同时仍需修正CREATE TABLE的语法错误：

from pyspark.sql import SparkSession

spark_session = SparkSession.builder \
    .appName("PinnabilityCalibration") \
    .config("spark.sql.parser.allowMultipleQueries", "true") \
    .config("spark.sql.parser.multiline", "true") \
    .getOrCreate()

注意事项 #

• spark.sql.parser.allowMultipleQueries配置在Spark 2.0+可用，开启后存在SQL注入风险，生产环境谨慎使用。
• 临时查询工具可能兼容非标准SQL语法，但Spark SQL严格遵循ANSI SQL规范，需确保语法符合Spark要求。

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