Here's the deal. I can access hive table using spark-shell doing this
scala> val sqlc = new org.apache.spark.sql.hive.HiveContext(sc)
sqlc: org.apache.spark.sql.hive.HiveContext = org.apache.spark.sql.hive.HiveContext@4e782401
scala> val rs = sqlc.sql("select * from user_test")
scala> rs.show()
but when I try to run it from Java
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.Dataset;
public class Main {
public static void main(String[] args) {
SparkSession spark = SparkSession
.builder()
.appName("Java Spark SQL basic example")
.enableHiveSupport()
.master("local")
.getOrCreate();
Dataset<Row> sqlDF = spark.sql("describe user_test");
sqlDF.show();
}
}
I get user_table not found.
Exception in thread "main" org.apache.spark.sql.catalyst.analysis.NoSuchTableException: Table or view 'user_test' not found in database 'default';
Here is my spark/conf/hive-site.xml
<configuration>
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<property>
<name>hive.server2.enable.doAs</name>
<value>true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
<description>metadata is stored in a MySQL server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>MySQL JDBC driver class</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/saurab/hadoopec/hive/lib/hive-serde-2.1.1.jar</value>
</property>
<property>
<name>spark.sql.warehouse.dir</name>
<value>hdfs://saurab:9000/user/hive/warehouse</value>
</property>
<property>
<name>hive.metastore.uris</name>
<!--Make sure that <value> points to the Hive Metastore URI in your cluster -->
<value>thrift://saurab:9083</value>
<description>URI for client to contact metastore server</description>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10001</value>
<description>Port number of HiveServer2 Thrift interface.
Can be overridden by setting $HIVE_SERVER2_THRIFT_PORT
</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
<description>user name for connecting to mysql server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hivepassword</value>
<description>password for connecting to mysql server</description>
</property>
And my spark-env.sh
export SCALA_HOME=/home/saurab/hadoopec/scala
export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64
export SPARK_HOME=/home/saurab/hadoopec/spark
export SPARK_CLASSPATH=/home/saurab/hadoopec/hive/lib/mysql-connector-java-5.1.38.jar
export SPARK_CLASSPATH=/home/saurab/hadoopec/hive/conf
export HADOOP_CONF_DIR=/home/saurab/hadoopec/hadoop/etc/hadoop
export YARN_CONF_DIR=/home/saurab/hadoopec/hadoop/etc/hadoop
export SPARK_WORKER_DIR=/home/saurab/hadoopec/spark/work
export SPARK_LOG_DIR=/home/saurab/hadoopec/spark/log
#export SPARK_MASTER_IP=saurab
And Here's the stack trace I get when I run hive query
17/07/12 11:56:36 INFO Executor: Starting executor ID driver on host localhost
17/07/12 11:56:36 INFO Utils: Successfully started service 'org.apache.spark.network.netty.NettyBlockTransferService' on port 39819.
17/07/12 11:56:36 INFO NettyBlockTransferService: Server created on 10.10.10.149:39819
17/07/12 11:56:36 INFO BlockManager: Using org.apache.spark.storage.RandomBlockReplicationPolicy for block replication policy
17/07/12 11:56:36 INFO BlockManagerMaster: Registering BlockManager BlockManagerId(driver, 10.10.10.149, 39819, None)
17/07/12 11:56:36 INFO BlockManagerMasterEndpoint: Registering block manager 10.10.10.149:39819 with 873.0 MB RAM, BlockManagerId(driver, 10.10.10.149, 39819, None)
17/07/12 11:56:36 INFO BlockManagerMaster: Registered BlockManager BlockManagerId(driver, 10.10.10.149, 39819, None)
17/07/12 11:56:36 INFO BlockManager: Initialized BlockManager: BlockManagerId(driver, 10.10.10.149, 39819, None)
17/07/12 11:56:36 INFO SharedState: Warehouse path is '/user/hive/warehouse'.
17/07/12 11:56:36 INFO HiveUtils: Initializing HiveMetastoreConnection version 1.2.1 using Spark classes.
17/07/12 11:56:36 INFO deprecation: mapred.max.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.maxsize
17/07/12 11:56:36 INFO deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
17/07/12 11:56:36 INFO deprecation: mapred.committer.job.setup.cleanup.needed is deprecated. Instead, use mapreduce.job.committer.setup.cleanup.needed
17/07/12 11:56:36 INFO deprecation: mapred.min.split.size.per.rack is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.rack
17/07/12 11:56:36 INFO deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize
17/07/12 11:56:36 INFO deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node
17/07/12 11:56:36 INFO deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
17/07/12 11:56:36 INFO deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive
17/07/12 11:56:37 INFO HiveMetaStore: 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
17/07/12 11:56:37 INFO ObjectStore: ObjectStore, initialize called
17/07/12 11:56:37 INFO Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
17/07/12 11:56:37 INFO Persistence: Property datanucleus.cache.level2 unknown - will be ignored
17/07/12 11:56:40 INFO ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
17/07/12 11:56:41 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
17/07/12 11:56:41 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
17/07/12 11:56:41 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
17/07/12 11:56:41 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
17/07/12 11:56:41 INFO Query: Reading in results for query "org.datanucleus.store.rdbms.query.SQLQuery@0" since the connection used is closing
17/07/12 11:56:41 INFO MetaStoreDirectSql: Using direct SQL, underlying DB is DERBY
17/07/12 11:56:41 INFO ObjectStore: Initialized ObjectStore
17/07/12 11:56:42 INFO HiveMetaStore: Added admin role in metastore
17/07/12 11:56:42 INFO HiveMetaStore: Added public role in metastore
17/07/12 11:56:42 INFO HiveMetaStore: No user is added in admin role, since config is empty
17/07/12 11:56:42 INFO HiveMetaStore: 0: get_all_databases
17/07/12 11:56:42 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_all_databases
17/07/12 11:56:42 INFO HiveMetaStore: 0: get_functions: db=default pat=*
17/07/12 11:56:42 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_functions: db=default pat=*
17/07/12 11:56:42 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MResourceUri" is tagged as "embedded-only" so does not have its own datastore table.
17/07/12 11:56:42 INFO SessionState: Created local directory: /tmp/198303bf-643a-4d75-98b0-6c5198f09f36_resources
17/07/12 11:56:42 INFO SessionState: Created HDFS directory: /tmp/hive/saurab/198303bf-643a-4d75-98b0-6c5198f09f36
17/07/12 11:56:42 INFO SessionState: Created local directory: /tmp/saurab/198303bf-643a-4d75-98b0-6c5198f09f36
17/07/12 11:56:42 INFO SessionState: Created HDFS directory: /tmp/hive/saurab/198303bf-643a-4d75-98b0-6c5198f09f36/_tmp_space.db
17/07/12 11:56:42 INFO HiveClientImpl: Warehouse location for Hive client (version 1.2.1) is /user/hive/warehouse
17/07/12 11:56:42 INFO HiveMetaStore: 0: get_database: default
17/07/12 11:56:42 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_database: default
17/07/12 11:56:42 INFO HiveMetaStore: 0: get_database: global_temp
17/07/12 11:56:42 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_database: global_temp
17/07/12 11:56:42 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
17/07/12 11:56:42 INFO SparkSqlParser: Parsing command: describe user_test
17/07/12 11:56:45 INFO HiveMetaStore: 0: get_database: default
17/07/12 11:56:45 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_database: default
17/07/12 11:56:45 INFO HiveMetaStore: 0: get_table : db=default tbl=user_test
17/07/12 11:56:45 INFO audit: ugi=saurab ip=unknown-ip-addr cmd=get_table : db=default tbl=user_test
Exception in thread "main" org.apache.spark.sql.catalyst.analysis.NoSuchTableException: Table or view 'user_test' not found in database 'default';
at org.apache.spark.sql.catalyst.catalog.SessionCatalog.requireTableExists(SessionCatalog.scala:138)
I do have table named user_test on hdfs
