PySpark读取含逗号字段的CSV拆分异常，求正确读取方案

我的数据文件中列值包含逗号，示例数据如下：

example.com', 'example Technologies is a leading provider of sophisticated electronic components, instruments & communications products, including defense electronics, data acquisition & communications equipment for airlines and business aircraft, monitoring and control instruments for industrial and environmental applications and components, and subsystems for wireless and satellite communications. The example Solution No matter what challenge you face, example has a solution. The diverse segments of example Technologies Incorporated bring decades of experience to bear on every project, working in cooperation to develop leading edge technologies. Our Markets We serve niche market segments where performance, precision and reliability are critical. Our customers include major industrial and communications companies, government agencies, aerospace prime contractors and general aviation companies.', 'http://www.example.com

我希望在Spark Shell中读取该文件时，忽略字段内的逗号（最后一列保持不变），得到包含domain、description、url三列的结果。尝试了以下代码：

df = spark.read.format("csv").option("sep", ',').option("quote", '"').option("escape", '"').option("inferSchema", "true").option("header", "true").load('1k.csv').rdd.toDF()

但读取后数据被错误拆分为多列，输出如下：

df.show()
+--------------------+------------------------------------------------------------------------------------+--------------------------------------+------------------------------+-------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------+-------------------------+
|       example.com'| 'example Technologies is a leading provider of sophisticated electronic components| instruments & communications products| including defense electronics| data acquisition & communications equipment for airlines and business aircraft| monitoring and control instruments for industrial and environmental applications and components| and subsystems for wireless and satellite communications. The example Solution No matter what challenge you face| example has a solution. The diverse segments of example Technologies Incorporated bring decades of experience to bear on every project| working in cooperation to develop leading edge technologies. Our Markets We serve niche market segments where performance| precision and reliability are critical. Our customers include major industrial and communications companies| government agencies| aerospace prime contractors and general aviation companies.'| 'http://www.example.com|
+--------------------+------------------------------------------------------------------------------------+--------------------------------------+------------------------------+-------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------------------------------------+-------------------------+

df.printSchema()
root
 |-- example.com': string (nullable = true)
 |--  'example Technologies is a leading provider of sophisticated electronic components: string (nullable = true)
 |--  instruments & communications products: string (nullable = true)
 |--  including defense electronics: string (nullable = true)
 |--  data acquisition & communications equipment for airlines and business aircraft: string (nullable = true)
 |--  monitoring and control instruments for industrial and environmental applications and components: string (nullable = true)
 |--  and subsystems for wireless and satellite communications. The example Solution No matter what challenge you face: string (nullable = true)
 |--  example has a solution. The diverse segments of example Technologies Incorporated bring decades of experience to bear on every project: string (nullable = true)
 |--  working in cooperation to develop leading edge technologies. Our Markets We serve niche market segments where performance: string (nullable = true)
 |--  precision and reliability are critical. Our customers include major industrial and communications companies: string (nullable = true)
 |--  government agencies: string (nullable = true)
 |--  aerospace prime contractors and general aviation companies.': string (nullable = true)
 |--  'http://www.example.com: string (nullable = true)

我期望得到包含domain、description、url三列的正确输出，请问问题出在哪？怎么解决？

1. 引号配置不匹配：你的数据用**单引号(')**包裹字段，但代码中配置的是双引号(")作为quote和escape参数，Spark无法识别单引号包裹的字段，导致把字段内的逗号当成列分隔符拆分。
2. 错误启用表头选项：你的数据没有表头行，但代码设置了header=true，Spark会把第一行数据当成表头，导致列名混乱且数据错位。
3. 多余的rdd.toDF()：这一步完全没必要，反而可能破坏原DataFrame的结构。

1. 修正CSV读取参数 #

将quote和escape参数改为单引号，关闭header选项，移除多余的rdd.toDF()：

val df = spark.read
  .format("csv")
  .option("sep", ",")
  .option("quote", "'")  // 匹配数据中的单引号包裹符
  .option("escape", "'") // 处理字段内可能的单引号转义（如果有的话）
  .option("inferSchema", "true")
  .option("header", "false") // 数据无表头
  .load("1k.csv")

2. 重命名列到期望的名称 #

因为没有表头，Spark会默认生成_c0、_c1、_c2这样的列名，需要手动重命名为domain、description、url：

val renamedDf = df.toDF("domain", "description", "url")

3. 清理字段首尾的单引号（可选） #

如果需要去掉字段前后的单引号，可以用trim或正则替换：

import org.apache.spark.sql.functions._

val cleanedDf = renamedDf
  .withColumn("domain", trim(col("domain"), "'"))
  .withColumn("description", trim(col("description"), "'"))
  .withColumn("url", trim(col("url"), "'"))

执行cleanedDf.show(truncate=false)会输出正确的三列数据：

+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|domain     |description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |url                  |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
|example.com|example Technologies is a leading provider of sophisticated electronic components, instruments & communications products, including defense electronics, data acquisition & communications equipment for airlines and business aircraft, monitoring and control instruments for industrial and environmental applications and components, and subsystems for wireless and satellite communications. The example Solution No matter what challenge you face, example has a solution. The diverse segments of example Technologies Incorporated bring decades of experience to bear on every project, working in cooperation to develop leading edge technologies. Our Markets We serve niche market segments where performance, precision and reliability are critical. Our customers include major industrial and communications companies, government agencies, aerospace prime contractors and general aviation companies.|http://www.example.com|
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+

执行cleanedDf.printSchema()会看到正确的三列结构：

root
 |-- domain: string (nullable = true)
 |-- description: string (nullable = true)
 |-- url: string (nullable = true)

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