I have figured out the problem and the solution.
The problem si that Hive reads parquet files in partitions by actual schema definition of the table and Impala (I assume) reads by position.
The table had some old partitions created under different schema, the column name were different. But the total number of columns and the position remained the same.
the old table
CREATE TABLE test ( event varchar(10), event_id int, event_time timestamp );
.. some partitions inserted, then the column renamed to event_name.
the actual table definition:
CREATE TABLE test ( event_name varchar(10), event_id int, event_time timestamp );
Now, if I select all partitions in Impala, the query returns all the data correctly. So I assume that Impala ignores the column names in parquet file and tries to access the first column as event_name with type varchar(10).
But SparkSQL and Beeline returns NULL for the partitions created with the old definition.
I downloaded the parquet files and evaluated the schema with parquet-tools and the columns are the old column names.
So to test whether the column was renamed in a table I created a simple script. It is important to run spark with mergeSchema true parameter, to read ALL the schema definitions from the table.
spark-shell --conf spark.sql.parquet.mergeSchema=true
import scala.util.matching.Regex
def test( tbl : String ) = {
val tb_md = sqlContext.sql("show create table "+tbl).collect()
val ddl = tb_md.map( x => x.get(0).toString ).mkString(" ")
val pattern = new Regex("""LOCATION\s*\'(.+)\'\s+TBLPROPERTIES""")
var loc:String = ""
try {
loc = (pattern findAllIn ddl).matchData.next.group(1)
} catch {
case e: Exception => //error
}
var d = sqlContext.read.parquet(loc )
val columns_parq = d.columns
println( columns_parq.toList )
println( "Table " + tbl + " has " + columns_parq.length + " columns.")
}
