3

I am using Hive Metastore in EMR.
I am able to query the table manually through HiveSQL or SparkSQL.
But When i use the same table in Spark Job, it says Table or view not found

File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/sql/utils.py", line 69, in deco pyspark.sql.utils.AnalysisException: 
  u"Table or view not found: `logan_test`.`salary_csv`; line 1 pos 21;
'Aggregate [unresolvedalias(count(1), None)]
+- 'UnresolvedRelation `logan_test`.`salary_csv`

Here is my full code 

from pyspark import SparkContext, HiveContext
from pyspark import SQLContext
from pyspark.sql import SparkSession

sc = SparkContext(appName = "test")
sqlContext = SQLContext(sparkContext=sc)
sqlContext.sql("select count(*) from logan_test.salary_csv").show()
print("done..")

I submitted my job as below to use hive catalog tables.

spark-submit test.py --files /usr/lib/hive/conf/hive-site.xml

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
logan
  • 7,946
  • 36
  • 114
  • 185

2 Answers2

2

You imported HiveContext but initialized standard SQLContext which doesn't support Hive queries. It should be:

sqlContext = HiveContext(sparkContext=sc)
Alper t. Turker
  • 34,230
  • 9
  • 83
  • 115
2

Looks like you're using Spark 2, therefore SQLContext and HiveContext should be replaced with SparkSession.sql() after you enableHiveSupport()

And instead of .sql(), you can use SparkSession.table() to get a DataFrame of the entire table, then follow it with a count(), then whatever other queries you want. 

from pyspark.sql import SparkSession

spark = SparkSession.builder.enableHiveSupport().appName("Hive Example").getOrCreate()
salary_csv = spark.table("logan_test.salary_csv")
print(salary_csv.count())
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
  • your answer is smart so +1; but i got what i was looking for in another answer. – logan Dec 20 '17 at 04:43
  • what is benifit of using dataframe instead of using sql() ? – logan Dec 20 '17 at 04:44
  • 1
    HIveContext is deprecated, you shouldn't rely on it. Sql function returns you a dataframe anyway. The advantage here is your dataframe is a lazy object of the whole table, not just the count of the rows. – OneCricketeer Dec 20 '17 at 05:30
  • Thanks. So whats the best way to execute an HIVE SQL in spark ? some cases i want to write my own sql and run it not only dataframe. – logan Dec 20 '17 at 19:12
  • 1
    `spark.sql("SELECT count(*) from logan_test.salary_csv")` will work fine. The `.sql()` method is just another way to use the DataFrame API... Both compile to a similar execution plan – OneCricketeer Dec 20 '17 at 23:47
  • great ! just last query, my job fails if there is partition path available. How to disable ``spark.sql.hive.verifyPartitionPath`` in job ? – logan Dec 21 '17 at 22:26
  • It defaults to false. But, you might want to post that as a new question including the error. https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala#L391 – OneCricketeer Dec 21 '17 at 22:28
  • ok. here you go.. https://stackoverflow.com/questions/47933705/spark-sql-fails-if-there-is-no-specified-partition-path-available – logan Dec 21 '17 at 22:45
  • if i use straight away spark.sql("SELECT count(*) from logan_test.salary_csv") , it says table does nt exist. I believe we need to register the table name before processing ?? – logan Dec 22 '17 at 00:00
  • If you didn't enable Hive support, and configure Spark to read the hive site XML, then it's not reading the table from the metastore – OneCricketeer Dec 22 '17 at 00:09