Spark SQL result different from Hive SQL result

Question

I performed the SQL statement, select cityroaddis from trip_db.tripTable where tripid='a0001' and day>'2020-09-09', in both hive shell and spark shell, but got totally different results.

The two results

Hive: cityroaddis	Spark: cityroaddis
0.0	null

Notice:

• I have specified the data type of cityroaddis as Double when creating the hive table
• There is no null value in cityroaddis column in Hive
• Only 0.3% of rows have such problem
• Not all columns have inconsistency between hive and spark (probably 15 out of 70)

Has anybody had such a problem before?

Answer 1

The problem was solved after I added these two configurations.

spark.sql.hive.convertMetastoreOrc=false
spark.sql.hive.convertMetastoreParquet=false

---

spark.sql.hive.convertMetastoreParquet ： When reading from and writing to Hive metastore Parquet tables, Spark SQL will try to use its own Parquet support instead of Hive SerDe for better performance. This behavior is controlled by the spark.sql.hive.convertMetastoreParquet configuration, and is turned on by default.

spark.sql.hive.convertMetastoreOrc: enables new ORC format to read/write Hive Tables.