通过Spark读取csv文件时出现问题

Question

"[{"code": "100", "name": "CLS1", "type": "PRIMARY"}]"

Answer 1

您不能使用Spark CSV reader直接读取这样的列，因为无法区分作为json字符串一部分的逗号和列分隔符，而且出于同样的原因，您不能使用“作为引号字符。

要解决这个问题，您应该将CSV文件作为文本文件读取，将String行拆分为seq[String]，然后从此seq[String]重新创建列

例如，如果您有一个名为file.csv的文件，其内容如下：

column1, column2, column3, column4
1, "value1", "[{"code": "100", "name": "CLS1", "type": "PRIMARY"}]", 1.1
2, "value2", "[{"code": "200", "name": "CLS2", "type": "SECONDARY"}]", 1.2

import org.apache.spark.sql.functions.col
import sparkSession.implicits._

val finalDataframe = sparkSession.read.text("file.csv")
  .filter(col("value").notEqual("column1, column2, column3, column4"))
  .as[String]
  .map(splitRow)
  .withColumn("column1", col("value").getItem(0).cast("int"))
  .withColumn("column2", col("value").getItem(1).cast("string"))
  .withColumn("column3", col("value").getItem(2).cast("string"))
  .withColumn("column4", col("value").getItem(3).cast("float"))
  .drop("value")

其中splitrow函数接受字符串作为输入并返回seq[String]，每个字符串按顺序表示一个列值。

此方法的实现可以是：

def splitRow(row: String): Seq[String] = row.foldLeft(Accumulator())((acc, char) => char match {
  case ',' if !acc.openBracket => acc.copy(result = acc.buffer +: acc.result, buffer = "", previousIsHyphen = false)
  case '[' if acc.previousIsHyphen => acc.copy(buffer = "[", openBracket = true, previousIsHyphen = false)
  case '"' if !acc.openBracket => acc.copy(previousIsHyphen = true)
  case '"' if acc.buffer.last == ']' => acc.copy(openBracket = false)
  case _ => acc.copy(buffer = acc.buffer + char, previousIsHyphen = false)
}).flush()

使用以下case类作为累加器:

case class Accumulator(
  result: Seq[String] = Nil, 
  openBracket: Boolean = false, 
  buffer: String = "", 
  previousIsHyphen: Boolean = false
) {
  def flush(): Seq[String] = (buffer +: result).reverse
}

+-------+-------+------------------------------------------------------+-------+
|column1|column2|column3                                               |column4|
+-------+-------+------------------------------------------------------+-------+
|1      | value1|[{"code": "100", "name": "CLS1", "type": "PRIMARY"}]  |1.1    |
|2      | value2|[{"code": "200", "name": "CLS2", "type": "SECONDARY"}]|1.2    |
+-------+-------+------------------------------------------------------+-------+