Reading Hive Tables in Spark Dataframe without header

user_8165879 0
Viewed 5071

I have the following Hive table:

select* from employee;
OK
abc     19      da
xyz     25      sa
pqr     30      er
suv     45      dr

when I read this in spark(pyspark): 

df = hiveCtx.sql('select* from spark_hive.employee')
df.show()
+----+----+-----+
|name| age| role|
+----+----+-----+
|name|null| role|
| abc|  19|   da|
| xyz|  25|   sa|
| pqr|  30|   er|
| suv|  45|   dr|
+----+----+-----+

I end up getting the headers in my spark DataFrame. Is there a simple way to remove it ?

Also, am I missing something while reading the table into the DataFrame (Ideally I shouldn't be getting the header right ?) ?

pyspark apache-spark hive
3 Answers

You have to remove header from the result. You can do like this:

scala> val df = sql("select * from employee")
df: org.apache.spark.sql.DataFrame = [id: int, name: string ... 1 more field]

scala> df.show
+----+----+----+
|  id|name| age|
+----+----+----+
|null|name|null|
|   1| abc|  19|
|   2| xyz|  25|
|   3| pqr|  30|
|   4| suv|  45|
+----+----+----+

scala> val header = df.first()
header: org.apache.spark.sql.Row = [null,name,null]

scala> val data = df.filter(row => row != header) 
data: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [id: int, name: string ... 1 more field]

scala> data.show
+---+----+---+
| id|name|age|
+---+----+---+
|  1| abc| 19|
|  2| xyz| 25|
|  3| pqr| 30|
|  4| suv| 45|
+---+----+---+

Thanks.

user_54141050

you can use skip.header.line.count for skip this header. You could also specify the same while creating the table. For example:

create external table testtable ( id int,name string, age int)
row format delimited .............
tblproperties ("skip.header.line.count"="1");

after that load the data and then check your query I hope you will get expected output.

user_66820760

Not the Most elegant way, but this worked with pyspark: 

rddWithoutHeader = dfemp.rdd.filter(lambda line: line!=header) 
dfnew = sqlContext.createDataFrame(rddWithoutHeader)
user_81658790
Related