question Re: how to access the hive tables from spark-shell in Support Questions

how to access the hive tables from spark-shell

fnushelly — Fri, 16 Sep 2022 09:59:41 GMT

Hi,

I am trying to access the already existing table in hive by using spark shell

But when I run the instructions, error comes "table not found".

e.g. in hive table is existing name as "department" in default database.

i start the spark-shell and execute the following set of instructions.

import org.apache.spark.sql.hive.HiveContext
val sqlContext = new HiveContext(sc)
val depts = sqlContext.sql("select * from departments")
depts.collecat().foreach(println)

but it coudn't find the table.

Now My questions are:

1. As I know ny using HiveContext spark can access the hive metastore. But it is not doing here, so is there any configuration setup required? I am using Cloudera quickstart VM 5..5

2. As an alternative I created the table on spark-shell , load a data file and then performed some queries and then exit the spark shell.

3. even if I create the table using spark-shell, it is not anywhere existing when I am trying to access it using hive editor.

4. when i again start the spark-shell , then earlier table i created, was no longer existing, so exactly where this table and metadata is stored and all....

I am very much confused, because accroding to theortical concepts, it should go under the hive metastore.

Thanks & Regards

Re: how to access the hive tables from spark-shell

fnushelly — Tue, 26 Jan 2016 21:03:32 GMT

to connect to hive metastore you need to copy the hive-site.xml file into spark/conf directory. After that spark will be able to connect to hive metastore.
so run the following ommand after log in as root user

cp /usr/lib/hive/conf/hive-site.xml /usr/lib/spark/conf/

Re: how to access the hive tables from spark-shell

sofiane — Thu, 01 Sep 2016 21:00:32 GMT

Or you create a symbolic link to avoid file version syncing issues:

ln -s /usr/lib/hive/conf/hive-site.xml    /usr/lib/spark/conf/hive-site.xml

Re: how to access the hive tables from spark-shell

jack0188 — Wed, 04 Jan 2017 07:47:00 GMT

Still the issue is persisting,

What else can we do to make it work other than hive-site.xml

Re: how to access the hive tables from spark-shell

jack0188 — Sun, 15 Jan 2017 05:41:21 GMT

which version spark are you using?

assuming you are using 1.4v or higher.

import org.apache.spark.sql.hive.HiveContext
import sqlContext.implicits._
val hiveObj = new HiveContext(sc)

hiveObj.refreshTable("db.table") // if you have uograded your hive do this, to refresh the tables.

val sample = sqlContext.sql("select * from table").collect()
sample.foreach(println)

This has worked for me

Re: how to access the hive tables from spark-shell

RajeshMohapatra — Wed, 24 May 2017 12:17:46 GMT

Hi,

Did u fix this issue?

Re: how to access the hive tables from spark-shell

hadoopSparkZen — Thu, 29 Jun 2017 11:39:38 GMT

I have downloaded Cloudera quickstart 5.10 for VirtualBox.

But it's not loading hive data into spark

import org.apache.spark.sql.hive.HiveContext
import sqlContext.implicits._
val hiveObj = new HiveContext(sc)

hiveObj.refreshTable("db.table") // if you have uograded your hive do this, to refresh the tables.

val sample = sqlContext.sql("select * from table").collect()
sample.foreach(println)

Still i'm getting the error as table not found(It's not accessing metadata)

What should i do, Any one pls help me

Re: how to access the hive tables from spark-shell

miguelalonso — Tue, 25 Jul 2017 10:27:03 GMT

I'm having the same issue. I'm using CDH 5.10 with Spark on Yarn

Also, is there a way to incllude hive-site.xml through Cloudera Manager? At the moment I have a script to make sure that the symlink is there (and links to the correct hive-site.xml) in the whole cluster, but getting Cloudera Manager to do it for me would be easier, faster and less error prone.

Re: how to access the hive tables from spark-shell

Jeno — Mon, 23 Oct 2017 11:23:08 GMT

Hi!

On the last week i have resolved the same problem for Spark 2.

For this I've select the Hive Service dependance on the Spark 2 service Configuration page (Service-Wide Category):

After stale services was restarted Spark 2 started to works correctly.

Re: how to access the hive tables from spark-shell

Sentara — Wed, 08 Nov 2017 17:19:44 GMT

I am having the same issue and copying the hive-site.xml did not resolve the issue for me. I am not using spark2, but the v1.6 that comes with Cloudera 5.13 - and there is no spark/hive configuration setting. Was anyone else able to figure out how to fix this? Thanks!

Re: how to access the hive tables from spark-shell

Jeno — Thu, 09 Nov 2017 07:19:39 GMT

Hi!

Have you installed the appropriate Gateways on the server where these configuration settings are required?

Re: how to access the hive tables from spark-shell

Sentara — Thu, 09 Nov 2017 10:42:37 GMT

Yes, I have a spark gateway on the host and I copied hive-site.xml into /etc/spark/conf.

Re: how to access the hive tables from spark-shell

Jeno — Thu, 09 Nov 2017 11:29:17 GMT

On the Spark configuration page i dont have Hive checkbox too.

Try to install another version of Spark.

Re: how to access the hive tables from spark-shell

NehaTaishy — Mon, 21 May 2018 06:49:43 GMT

Try "select * from db.table" in line 3

Re: how to access the hive tables from spark-shell

Deepanjan — Tue, 16 Oct 2018 06:45:17 GMT

Hi,

I am trying to access the already existing table in hive by using pyspark

e.g. in hive table is existing name as "department" in default database.

err msg :-

18/10/15 22:01:23 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded.
18/10/15 22:02:35 WARN metastore.ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 1.1.0-cdh5.13.0
18/10/15 22:02:38 WARN metastore.ObjectStore: Failed to get database default, returning NoSuchObjectException

I checked the below files, they are same.

/usr/lib/hive/conf/hive-site.xml

/usr/lib/spark/conf/hive-site.xml

Any help on how to set up the HiveContext from pyspark is highly appreciated.

Re: how to access the hive tables from spark-shell

MichelleY — Mon, 18 Feb 2019 21:34:40 GMT

Hi there,

Just in case someone still needs the solution, here is what i tried and it works.

spark-shell --driver-java-options "-Dhive.metastore.uris=thrift://quickstart:9083"

I am using spark 1.6 with cloudera vm.

val df=sqlContext.sql("show databases")

df.show

You should be able to see all the databases in hive. I hope it helps.

Re: how to access the hive tables from spark-shell

patwa95 — Sat, 23 Feb 2019 10:41:44 GMT

I tried this. but its permission denied.
Can you please help

Re: how to access the hive tables from spark-shell

Vipuly2j — Mon, 13 Mar 2023 20:11:50 GMT

You are life saver, I have been struggling with this for 7-8 hours and my deadline to submit a case study was close. Thanks alot!!!