使用Spark读取.xlsx时遇AnalysisException格式不兼容问题求助

问题说明 #

尝试用Spark读取存储在Blob Storage的.xlsx文件，代码如下：

spark_df3 = spark.read.format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .option("inferSchema", True) \
    .option("dataAddress", "MyList") \
    .load("https://url_from_blob_storage")

执行后抛出错误：

AnalysisException: Incompatible format detected.

已确认使用的crealytics.spark版本为0.13.5，format参数无误。用Pandas（xlrd引擎）可正常读取该文件，但因业务需求需直接用Spark DataFrame写入SQL表，此方法无法满足。

完整错误堆栈 #

---------------------------------------------------------------------------
AnalysisException                         Traceback (most recent call last)
<command-1235026629993988> in <module>
     21 # <class 'pyspark.pandas.frame.DataFrame'>
     22 # <class 'pyspark.sql.dataframe.DataFrame'>
---> 23 spark_df3 = spark.read.format("com.crealytics.spark.excel") \
     24     .option("header", "true") \
     25     .option("inferSchema", True) \

/databricks/spark/python/pyspark/sql/readwriter.py in load(self, path, format, schema, **options)
    156         self.options(**options)
    157         if isinstance(path, str):
---> 158             return self._df(self._jreader.load(path))
    159         elif path is not None:
    160             if type(path)!= list:

/databricks/spark/python/lib/py4j-0.10.9.1-src.zip/py4j/java_gateway.py in __call__(self, *args)
   1302 
   1303         answer = self.gateway_client.send_command(command)
-> 1304         return_value = get_return_value(
   1305             answer, self.gateway_client, self.target_id, self.name)
   1306 

/databricks/spark/python/pyspark/sql/utils.py in deco(*a, **kw)
    121                 # Hide where the exception came from that shows a non-Pythonic
    122                 # JVM exception message.
---> 123                 raise converted from None
    124             else:
    125                 raise

AnalysisException: Incompatible format detected.

A transaction log for Databricks Delta was found at `You must use
'format("delta")' when reading and writing to a delta table.`,
but you are trying to read from `https://url_from_blob_storage` using format("com.crealytics.spark.excel"). You must use
'format("delta")' when reading and writing to a delta table.

To disable this check, SET spark.databricks.delta.formatCheck.enabled=false

问题根源与解决办法 #

从错误堆栈的详细信息可知，目标路径下存在Delta Lake的事务日志文件，Spark会自动检测到该日志并强制要求使用Delta格式读取，而非指定的Excel格式。

方案1：修正文件路径 #

检查https://url_from_blob_storage是否指向了一个Delta表目录，而非单个.xlsx文件。如果是路径错误，修正为实际的Excel文件完整路径即可。

方案2：禁用Delta格式检查 #

若确认路径正确，且确实需要读取该路径下的Excel文件，可通过以下方式禁用Delta格式检查：

• 在Spark中执行配置语句：

SET spark.databricks.delta.formatCheck.enabled=false

• 或者在初始化SparkSession时添加配置：

spark = SparkSession.builder \
    .appName("ReadExcelFile") \
    .config("spark.databricks.delta.formatCheck.enabled", "false") \
    .getOrCreate()

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