Hive queries failing with "Unable to fetch table test_table. Invalid method name: 'get_table_req'" with pyspark 3.0.0 & Hive 1.1.0

user_8895732 0
Viewed 5019

Digging into a POC for spark in a fairly new environment and checking spark capabilities but Having issues with running sql queries in pyspark terminal whereas Hive is working since we can query metadata.

Any idea whats happening here and how to resolve this?

$ pyspark --driver-class-path /etc/spark2/conf:/etc/hive/conf
>>> from pyspark.sql import SparkSession
>>> from pyspark.sql import Row
>>> spark = SparkSession \
...     .builder \
...     .appName("sample_query_test") \
... .enableHiveSupport() \
...     .getOrCreate()
>>> spark.sql("show tables in user_tables").show(5)
20/08/18 19:57:01 WARN conf.HiveConf: HiveConf of name hive.enforce.sorting does not exist
20/08/18 19:57:01 WARN conf.HiveConf: HiveConf of name hive.enforce.bucketing does not exist
20/08/18 19:57:01 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
+-----------+--------------------+-----------+
|   database|           tableName|isTemporary|
+-----------+--------------------+-----------+
|user_tables|              a_2019|      false|
|user_tables|abcdefgjeufjdsahh...|      false|
|user_tables|testtesttesttestt...|      false|
|user_tables|newnewnewnewnenwn...|      false|
|user_tables|blahblahblablahbl...|      false|
+-----------+--------------------+-----------+
only showing top 5 rows

>>> spark.sql("select count(*) from user_tables.test_table where date_partition='2020-08-17'").show(5)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/conda/lib/python3.7/site-packages/pyspark/sql/session.py", line 646, in sql
    return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
  File "/opt/conda/lib/python3.7/site-packages/pyspark/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py", line 1305, in __call__
  File "/opt/conda/lib/python3.7/site-packages/pyspark/sql/utils.py", line 137, in deco
    raise_from(converted)
  File "<string>", line 3, in raise_from
**pyspark.sql.utils.AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: Unable to fetch table test_spark_cedatatransfer. Invalid method name: 'get_table_req';**

Information on the cluster :

$ hive --version
Hive 1.1.0-cdh5.13.0
Subversion file:///data/jenkins/workspace/generic-package-ubuntu64-16-04/CDH5.13.0-Packaging-Hive-2017-10-04_10-50-44/hive-1.1.0+cdh5.13.0+1269-1.cdh5.13.0.p0.34~xenial -r Unknown
Compiled by jenkins on Wed Oct 4 11:46:53 PDT 2017

$ pyspark --version
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 3.0.0
      /_/

Using Scala version 2.12.10, OpenJDK 64-Bit Server VM, 1.8.0_252
Branch HEAD
Compiled by user ubuntu on 2020-06-06T11:32:25Z
Revision 3fdfce3120f307147244e5eaf46d61419a723d50
Url https://gitbox.apache.org/repos/asf/spark.git

$ hadoop version
Hadoop 2.6.0-cdh5.13.0
Subversion http://github.com/cloudera/hadoop -r 42e8860b182e55321bd5f5605264da4adc8882be
Compiled by jenkins on 2017-10-04T18:50Z
Compiled with protoc 2.5.0
From source with checksum 5e84c185f8a22158e2b0e4b8f85311
This command was run using /usr/lib/hadoop/hadoop-common-2.6.0-cdh5.13.0.jar

As its evident, I added hive conf to make sure same metastore is being used and while simple queries working the insert overwrite is failing!

cloudera pyspark apache-spark hive hadoop
2 Answers

I have run into the same issue, trying to use Spark 3.0.1 with HDP2.6.

Problem was resolved by removing all hive*.jar files from jars folder, and copying hive*jar from Spark2, shipped with HDP distribution.

user_31960240

Setting the following properties in spark-defaults.conf have worked for me:

spark.sql.hive.metastore.jars=maven
spark.sql.hive.metastore.version=1.2.1

spark.sql.catalogImplementation=hive
spark.sql.warehouse.dir=/user/hive/warehouse

Of course the last line may be customized, to the appropriate location. This path is for Cloudera (CDH).

These settings will instruct Spark to download and cache jar files in ~/.ivy2/jars for Hive 1.2.1. You may also choose to provide your own jars in an own path, but maven yield the most comfortable option.

See Spark configuartion properties for more details!

user_7733060
Related