CODEWITHSUNDEEP
apache-sparkhiveapache-spark-sql
Vickyster
Vickyster

Reputation: 173

Error while trying to fetch table from remote hive2 server using spark

I'm trying to access tables from remote hive2 server from spark using the code below:

import org.apache.spark.SparkContext, org.apache.spark.SparkConf, org.apache.spark.sql._
import com.typesafe.config._
import java.io._
import org.apache.hadoop.fs._
import org.apache.spark.sql.hive.HiveContext
import org.apache.spark.sql.Row
import org.apache.spark.sql.SparkSession

object stack {
  def main(args: Array[String]) {
 val warehouseLocation = "/usr/hive/warehouse"
System.setProperty("javax.jdo.option.ConnectionURL","jdbc:mysql://sparkserver:3306/metastore?createDatabaseIfNotExist=true")
System.setProperty("javax.jdo.option.ConnectionUserName","hiveroot")
System.setProperty("javax.jdo.option.ConnectionPassword","hivepassword")
System.setProperty("hive.exec.scratchdir","/tmp/hive/${user.name}")
System.setProperty("spark.sql.warehouse.dir", warehouseLocation)
   // System.setProperty("hive.metastore.uris", "thrift://sparkserver:9083")
System.setProperty("javax.jdo.option.ConnectionDriverName","com.mysql.jdbc.Driver")
System.setProperty("hive.metastore.warehouse.dir","/user/hive/warehouse")


val spark = SparkSession.builder().master("local")
.appName("spark remote")
   // .config("javax.jdo.option.ConnectionURL","jdbc:mysql://sparkserver:3306/metastore?createDatabaseIfNotExist=true")
 .config("javax.jdo.option.ConnectionURL","jdbc:mysql://sparkserver:3306/metastore?createDatabaseIfNotExist=true")
  .config("javax.jdo.option.ConnectionUserName","hiveroot")
  .config("javax.jdo.option.ConnectionPassword","hivepassword")
  .config("hive.exec.scratchdir","/tmp/hive/${user.name}")
  .config("spark.sql.warehouse.dir", warehouseLocation)
//  .config("hive.metastore.uris", "thrift://sparkserver:9083")  
  .config("javax.jdo.option.ConnectionDriverName","com.mysql.jdbc.Driver")
  .config("hive.querylog.location","/tmp/hivequerylogs/${user.name}")
  .config("hive.support.concurrency","false")
  .config("hive.server2.enable.doAs","true")
  .config("hive.server2.authentication","PAM")
  .config("hive.server2.custom.authentication.class","org.apache.hive.service.auth.PamAuthenticationProvider")
  .config("hive.server2.authentication.pam.services","sshd,sudo")
  .config("hive.stats.dbclass","jdbc:mysql")
  .config("hive.stats.jdbcdriver","com.mysql.jdbc.Driver")
  .config("hive.session.history.enabled","true")
  .config("hive.metastore.schema.verification","false")
  .config("hive.optimize.sort.dynamic.partition","false")
  .config("hive.optimize.insert.dest.volume","false")
  .config("datanucleus.fixedDatastore","true")
  .config("hive.metastore.warehouse.dir","/user/hive/warehouse")
  .config("datanucleus.autoCreateSchema","false")
  .config("datanucleus.schema.autoCreateAll","true")
  .config("datanucleus.schema.validateConstraints","true")
  .config("datanucleus.schema.validateColumns","true")
  .config("datanucleus.schema.validateTables","true")       
  .config("fs.default.name","hdfs://sparkserver:54310")
  .config("dfs.namenode.name.dir","/usr/local/hadoop_tmp/hdfs/namenode")
  .config("dfs.datanode.name.dir","/usr/local/hadoop_tmp/hdfs/datanode")
  .enableHiveSupport()
  .getOrCreate()

import spark.implicits._
import spark.sql

sql("select * from sample.source").collect.foreach(println)
sql("select * from sample.destination").collect.foreach(println)
  }
}

Connection request to the meta-store is been refused by remote hive server.

ERROR:Failed to start hive-metastore.service: Unit hive-metastore.service not found

Thank you!

Upvotes: 0

Views: 312

Answers (2)

Raktotpal Bordoloi
Raktotpal Bordoloi

Reputation: 1057

Normally we don't need to point to remote metastore separately.

Hive-site.xml will have conf about pointing to metastore through jdbc internally.

The same conf can set as follows in program before initializing Hive-Context:

Give it a try.

System.setProperty("javax.jdo.option.ConnectionURL", "jdbc:mysql://<ip>/metastore?createDatabaseIfNotExist=true")
...("javax.jdo.option.ConnectionDriverName", "com.mysql.jdbc.Driver")
...("javax.jdo.option.ConnectionUserName", "mysql-user")
...("javax.jdo.option.ConnectionPassword", "mysql-passwd")

Upvotes: 1

Raktotpal Bordoloi
Raktotpal Bordoloi

Reputation: 1057

when you use this: .config("hive.metastore.uris", "hive2://hiveserver:9083"), hiveserver should be proper remote hive server's ip.

The conf hive.metastore.uris points to the hive-metastore service; and if you are running locally (in localhost) - and want remote-metastore; you need to start hive-metastore service separately.

`$HIVE_HOME/bin/hive --service metastore` -p 9083

Or - by-default, Hive uses local Hive-metastore; so in that case, you dont need to set any value for hive.metastore.uris

And - forgot to mention, the property you are setting - always uses thrift protocol - whether hiveserver1 or hiveserver2.

So, always use this:

.config("hive.metastore.uris", "thrift://hiveserver:9083")

Upvotes: 1

Related Questions