[GitHub] spark issue #16928: [SPARK-18699][SQL] Put malformed tokens into a new field...

[HyukjinKwon <gi...@git.apache.org>]

Github user HyukjinKwon commented on the issue:

    https://github.com/apache/spark/pull/16928
  
    cc @cloud-fan, could I ask if you think matching it seems reasonable? I wrote some details below for you to track this issue.
    
    Currently, JSON produces
    
    ```scala
    Seq("""{"a": "a", "b" : 1}""").toDF().write.text("/tmp/path")
    val schema = StructType(
      StructField("a", IntegerType, true) ::
      StructField("b", StringType, true) :: 
      StructField("_corrupt_record", StringType, true) :: Nil)
    spark.read.schema(schema)
      .option("mode", "PERMISSIVE")
      .json("/tmp/path").show()
    ```
    
    ```scala
    +----+----+-------------------+
    |   a|   b|    _corrupt_record|
    +----+----+-------------------+
    |null|null|{"a": "a", "b" : 1}|
    +----+----+-------------------+
    ```
    
    whereas CSV produces
    
    ```scala
    Seq("""a,1""").toDF().write.text("/tmp/path")
    val schema = StructType(
      StructField("a", IntegerType, true) ::
      StructField("b", StringType, true) :: Nil)
    spark.read.schema(schema)
      .option("mode", "PERMISSIVE")
      .csv("/tmp/path").show()
    ```
    
    ```scala
    java.lang.NumberFormatException: For input string: "a"
    	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    	at java.lang.Integer.parseInt(Integer.java:580)
    ```
    
    To cut it short, the problem is, parsing itself is fine but when a value is unable to convert. In case of JSON, it fills the value in the column specified in `columnNameOfCorruptRecord` whereas CSV throws an exception even if it is `PERMISSIVE` mode.
    
    It seems there are two ways to fix this. 
    
    One is what @maropu initially suggested - permissively fills it as `null`. My worry here is losing the data.
    
    The other way I suggested here - matching it to JSON's one, storing the value in the column specified in `columnNameOfCorruptRecord`. However, note that I guess we will not get this column while inferring in CSV in most cases as I can't imagine the case when CSV itself is malformed.
    
    JSON produces this column when the JSON is malformed as below:
    
    ```scala
    Seq("""{"a": "a", "b" :""").toDF().write.text("/tmp/test123")
    spark.read.json("/tmp/test123").printSchema
    root
     |-- _corrupt_record: string (nullable = true)
    ```
    
    



---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org