Skip to main content
BrianChan
Gold ⭐️
March 31, 2023
Solved

Spark cannot read Hive managed table

Hi all, I am practicing spark.

 

When using pyspark to query table in Hive, I can retrieve the data from an external table but query a internal table.

 

Here is the error message:

>>> spark.read.table("exams").count()
23/03/30 22:28:50 WARN conf.HiveConf: HiveConf of name hive.masking.algo does not exist
Hive Session ID = eb0a9583-da34-4c85-9a1b-db790d126fb1
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/opt/cloudera/parcels/CDH-7.1.8-1.cdh7.1.8.p0.30990532/lib/spark/python/pyspark/sql/readwriter.py", line 301, in table
return self._df(self._jreader.table(tableName))
File "/opt/cloudera/parcels/CDH-7.1.8-1.cdh7.1.8.p0.30990532/lib/spark/python/lib/py4j-0.10.7-src.zip/py4j/java_gateway.py",
File "/opt/cloudera/parcels/CDH-7.1.8-1.cdh7.1.8.p0.30990532/lib/spark/python/pyspark/sql/utils.py", line 69, in deco
raise AnalysisException(s.split(': ', 1)[1], stackTrace)
pyspark.sql.utils.AnalysisException: u'\nSpark has no access to table `default`.`exams`. Clients can access this table only ifMANAGEDINSERTWRITE,HIVEMANAGESTATS,HIVECACHEINVALIDATE,CONNECTORWRITE.\nThis table may be a Hive-managed ACID table, or require some other capability that Spark\ncurrently does not implement;'

 

I know that spark cannot read a ACID Hive table. it there any work around? 

 

Thanks in advance.

ChethanYM
ChethanYMAnswer
Gold ⭐️
March 31, 2023

Hi,

 

Can you use "spark.sql.htl.check=false" parameter in your spark job and give a try?

 

Regards,

Chethan YM

6 replies

ChethanYM
Gold ⭐️
ChethanYMAnswer
Gold ⭐️
March 31, 2023

Hi,

 

Can you use "spark.sql.htl.check=false" parameter in your spark job and give a try?

 

Regards,

Chethan YM

Translate
BrianChan
BrianChanAuthor
Gold ⭐️
April 3, 2023

@ChethanYM Thank you for your reply. 

 

I tried your suggestion by recreating the spark session

>>> conf = spark.sparkContext._conf.setAll([('spark.sql.htl.check','false'), ('mapreduce.input.fileinputformat.input.dir.recursive','true')])
>>> spark.sparkContext.stop()
>>> spark = SparkSession.builder.config(conf=conf).getOrCreate()

 

It works fine. Thank you very much.

Translate
kartheekb
Silver ⭐️
September 19, 2024

This solution worked for eliminating error , but data is not being fetched from table. 

empty data frame showing.

Translate
ggangadharan
Gold ⭐️
Gold ⭐️
August 30, 2024

You need to use Hive Warehouse Connector (HWC) to query Hive managed tables from Spark.

Ref - https://docs.cloudera.com/cdp-private-cloud-base/7.1.9/integrating-hive-and-bi/topics/hive_hivewarehouseconnector_for_handling_apache_spark_data.html

Translate