[jira] [Created] (SPARK-30559) Spark 2.4.4 - spark.sql.hive.caseSensitiveInferenceMode does not work with Hive

["Ori Popowski (Jira)" <ji...@apache.org>]

Ori Popowski created SPARK-30559:
------------------------------------

             Summary: Spark 2.4.4 - spark.sql.hive.caseSensitiveInferenceMode does not work with Hive
                 Key: SPARK-30559
                 URL: https://issues.apache.org/jira/browse/SPARK-30559
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 2.4.4
         Environment: EMR 28.1 with Spark 2.4.4, Hadoop 2.8.5 and Hive 2.3.6
            Reporter: Ori Popowski


Spark SQL's spark.sql.hive.caseSensitiveInferenceMode=INFER_ONLY and INFER_AND_SAVE do not work as intended. They were supposed to infer a case-sensitive schema from the underlying files, but they do not work.
 # INFER_ONLY never works: it will always user lowercase column names from Hive metastore schema
 # INFER_AND_SAVE only works the second time spark.sql("SELECT …") is called (the first time it writes the schema to TBLPROPERTIES in the metastore and subsequent calls read that schema, so they do work)

h3. Expected behavior (according to SPARK-19611)

INFER_ONLY - infer the schema from the underlying files

INFER_AND_SAVE - infer the schema from the underlying files, save it to the metastore, and read it from the metastore on any subsequent calls
h2. Reproduce
h3. Prepare the data
h4. 1) Create a Parquet file

 
{code:java}
scala> List(("a", 1), ("b", 2)).toDF("theString", "theNumber").write.parquet("hdfs:///t"){code}
 
h4. 2) Inspect the Parquet files

 
{code:java}
$ hadoop jar parquet-tools-1.11.0.jar cat -j hdfs:///t/part-00000-….snappy.parquet
{"theString":"a","theNumber":1}
$ hadoop jar parquet-tools-1.11.0.jar cat -j hdfs:///t/part-00001-….snappy.parquet
{"theString":"b","theNumber":2}{code}
 

We see that they are saved with camelCase column names.
h4. 3) Create a Hive table

 
{code:java}
hive> CREATE EXTERNAL TABLE t(theString string, theNumber int)
 > ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
 > STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
 > OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
 > LOCATION 'hdfs:///t';{code}
 
h3. Reproduce INFER_ONLY bug

 
h4. 3) Read the table in Spark using INFER_ONLY

 
{code:java}
$ spark-shell --master local[*] --conf spark.sql.hive.caseSensitiveInferenceMode=INFER_ONLY{code}
 

 
{code:java}
scala> spark.sql("SELECT * FROM default.t").columns.foreach(println)

thestring
thenumber
{code}
h4. Conclusion

When INFER_ONLY is set, column names are lowercase always.
h3. Reproduce INFER_AND_SAVE bug
h4. 1) Run the for first time
{code:java}
$ spark-shell --master local[*] --conf spark.sql.hive.caseSensitiveInferenceMode=INFER_AND_SAVE{code}
 

 
{code:java}
scala> spark.sql("select * from default.t").columns.foreach(println)
thestring
thenumber{code}
 

We see that column names are lowercase
h4. 2) Run for the second time

 
{code:java}
scala> spark.sql("select * from default.t").columns.foreach(println)
theString
theNumber{code}
 

We see that the column names are camelCase
h4. Conclusion

When INFER_AND_SAVE is set, column names are lowercase on first call and camelCase on subsquent calls.

 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

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