apache-sparkhiveapache-spark-sqlcloudera-quickstart-vm

Connec to Hive from Apache Spark


I have a simple program that I'm running on Standalone Cloudera VM. I have created a managed table in Hive , which I want to read in Apache spark, but the initial connection to hive is not being established. Please advise.

I'm running this program in IntelliJ, I have copied hive-site.xml from my /etc/hive/conf to /etc/spark/conf, even then the spark-job is not connecting to Hive metastore

 public static void main(String[] args) throws AnalysisException {
         String master = "local[*]";

         SparkSession sparkSession = SparkSession
                 .builder().appName(ConnectToHive.class.getName())
                 .config("spark.sql.warehouse.dir", "hdfs://quickstart.cloudera:8020/user/hive/warehouse")
                 .enableHiveSupport()
                 .master(master).getOrCreate();

         SparkContext context = sparkSession.sparkContext();
         context.setLogLevel("ERROR");

         SQLContext sqlCtx = sparkSession.sqlContext();

         HiveContext hiveContext = new HiveContext(sparkSession);
         hiveContext.setConf("hive.metastore.warehouse.dir", "hdfs://quickstart.cloudera:8020/user/hive/warehouse");

         hiveContext.sql("SHOW DATABASES").show();
         hiveContext.sql("SHOW TABLES").show();

         sparkSession.close();
     }

The output is as below, where is expect to see "Employee table" , so that I can query. Since I'm running on Standa-alone , hive metastore is in Local mySQL server.

 +------------+
 |databaseName|
 +------------+
 |     default|
 +------------+

 +--------+---------+-----------+
 |database|tableName|isTemporary|
 +--------+---------+-----------+
 +--------+---------+-----------+

jdbc:mysql://127.0.0.1/metastore?createDatabaseIfNotExist=true is the configuration for Hive metastore

 hive> show databases;
 OK
 default
 sxm
 temp
 Time taken: 0.019 seconds, Fetched: 3 row(s)
 hive> use default;
 OK
 Time taken: 0.015 seconds
 hive> show tables;
 OK
 employee
 Time taken: 0.014 seconds, Fetched: 1 row(s)
 hive> describe formatted employee;
 OK
 # col_name             data_type               comment             

 id                     string                                      
 firstname              string                                      
 lastname               string                                      
 addresses              array<struct<street:string,city:string,state:string>>                       

 # Detailed Table Information        
 Database:              default                  
 Owner:                 cloudera                 
 CreateTime:            Tue Jul 25 06:33:01 PDT 2017     
 LastAccessTime:        UNKNOWN                  
 Protect Mode:          None                     
 Retention:             0                        
 Location:              hdfs://quickstart.cloudera:8020/user/hive/warehouse/employee     
 Table Type:            MANAGED_TABLE            
 Table Parameters:       
    transient_lastDdlTime   1500989581          

 # Storage Information       
 SerDe Library:         org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe  
 InputFormat:           org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat    
 OutputFormat:          org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat   
 Compressed:            No                       
 Num Buckets:           -1                       
 Bucket Columns:        []                       
 Sort Columns:          []                       
 Storage Desc Params:        
    serialization.format    1                   
 Time taken: 0.07 seconds, Fetched: 29 row(s)
 hive> 

Added Spark Logs

 log4j:WARN No appenders could be found for logger (org.apache.hadoop.util.Shell).
 log4j:WARN Please initialize the log4j system properly.
 log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
 Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
 17/07/25 11:38:30 INFO SparkContext: Running Spark version 2.1.0
 17/07/25 11:38:30 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
 17/07/25 11:38:30 INFO SecurityManager: Changing view acls to: cloudera
 17/07/25 11:38:30 INFO SecurityManager: Changing modify acls to: cloudera
 17/07/25 11:38:30 INFO SecurityManager: Changing view acls groups to: 
 17/07/25 11:38:30 INFO SecurityManager: Changing modify acls groups to: 
 17/07/25 11:38:30 INFO SecurityManager: SecurityManager: authentication disabled; ui acls disabled; users  with view permissions: Set(cloudera); groups with view permissions: Set(); users  with modify permissions: Set(cloudera); groups with modify permissions: Set()
 17/07/25 11:38:31 INFO Utils: Successfully started service 'sparkDriver' on port 55232.
 17/07/25 11:38:31 INFO SparkEnv: Registering MapOutputTracker
 17/07/25 11:38:31 INFO SparkEnv: Registering BlockManagerMaster
 17/07/25 11:38:31 INFO BlockManagerMasterEndpoint: Using org.apache.spark.storage.DefaultTopologyMapper for getting topology information
 17/07/25 11:38:31 INFO BlockManagerMasterEndpoint: BlockManagerMasterEndpoint up
 17/07/25 11:38:31 INFO DiskBlockManager: Created local directory at /tmp/blockmgr-eb1e611f-1b88-487f-b600-3da1ff8353db
 17/07/25 11:38:31 INFO MemoryStore: MemoryStore started with capacity 1909.8 MB
 17/07/25 11:38:31 INFO SparkEnv: Registering OutputCommitCoordinator
 17/07/25 11:38:31 INFO Utils: Successfully started service 'SparkUI' on port 4040.
 17/07/25 11:38:31 INFO SparkUI: Bound SparkUI to 0.0.0.0, and started at http://10.0.2.15:4040
 17/07/25 11:38:31 INFO Executor: Starting executor ID driver on host localhost
 17/07/25 11:38:31 INFO Utils: Successfully started service 'org.apache.spark.network.netty.NettyBlockTransferService' on port 41433.
 17/07/25 11:38:31 INFO NettyBlockTransferService: Server created on 10.0.2.15:41433
 17/07/25 11:38:31 INFO BlockManager: Using org.apache.spark.storage.RandomBlockReplicationPolicy for block replication policy
 17/07/25 11:38:31 INFO BlockManagerMaster: Registering BlockManager BlockManagerId(driver, 10.0.2.15, 41433, None)
 17/07/25 11:38:31 INFO BlockManagerMasterEndpoint: Registering block manager 10.0.2.15:41433 with 1909.8 MB RAM, BlockManagerId(driver, 10.0.2.15, 41433, None)
 17/07/25 11:38:31 INFO BlockManagerMaster: Registered BlockManager BlockManagerId(driver, 10.0.2.15, 41433, None)
 17/07/25 11:38:31 INFO BlockManager: Initialized BlockManager: BlockManagerId(driver, 10.0.2.15, 41433, None)
 17/07/25 11:38:32 INFO SharedState: Warehouse path is 'file:/home/cloudera/works/JsonHive/spark-warehouse/'.
 17/07/25 11:38:32 INFO HiveUtils: Initializing HiveMetastoreConnection version 1.2.1 using Spark classes.
 17/07/25 11:38:32 INFO deprecation: mapred.max.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.maxsize
 17/07/25 11:38:32 INFO deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
 17/07/25 11:38:32 INFO deprecation: mapred.committer.job.setup.cleanup.needed is deprecated. Instead, use mapreduce.job.committer.setup.cleanup.needed
 17/07/25 11:38:32 INFO deprecation: mapred.min.split.size.per.rack is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.rack
 17/07/25 11:38:32 INFO deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize
 17/07/25 11:38:32 INFO deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node
 17/07/25 11:38:32 INFO deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
 17/07/25 11:38:32 INFO deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive
 17/07/25 11:38:32 INFO HiveMetaStore: 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore
 17/07/25 11:38:32 INFO ObjectStore: ObjectStore, initialize called
 17/07/25 11:38:32 INFO Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
 17/07/25 11:38:32 INFO Persistence: Property datanucleus.cache.level2 unknown - will be ignored
 17/07/25 11:38:34 INFO ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
 17/07/25 11:38:35 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/25 11:38:35 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/25 11:38:35 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/25 11:38:35 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/25 11:38:35 INFO Query: Reading in results for query "org.datanucleus.store.rdbms.query.SQLQuery@0" since the connection used is closing
 17/07/25 11:38:35 INFO MetaStoreDirectSql: Using direct SQL, underlying DB is DERBY
 17/07/25 11:38:35 INFO ObjectStore: Initialized ObjectStore
 17/07/25 11:38:36 INFO HiveMetaStore: Added admin role in metastore
 17/07/25 11:38:36 INFO HiveMetaStore: Added public role in metastore
 17/07/25 11:38:36 INFO HiveMetaStore: No user is added in admin role, since config is empty
 17/07/25 11:38:36 INFO HiveMetaStore: 0: get_all_databases
 17/07/25 11:38:36 INFO audit: ugi=cloudera ip=unknown-ip-addr  cmd=get_all_databases   
 17/07/25 11:38:36 INFO HiveMetaStore: 0: get_functions: db=default pat=*
 17/07/25 11:38:36 INFO audit: ugi=cloudera ip=unknown-ip-addr  cmd=get_functions: db=default pat=* 
 17/07/25 11:38:36 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/25 11:38:36 INFO SessionState: Created local directory: /tmp/76258222-81db-4ac1-9566-1d8f05c3ecba_resources
 17/07/25 11:38:36 INFO SessionState: Created HDFS directory: /tmp/hive/cloudera/76258222-81db-4ac1-9566-1d8f05c3ecba
 17/07/25 11:38:36 INFO SessionState: Created local directory: /tmp/cloudera/76258222-81db-4ac1-9566-1d8f05c3ecba
 17/07/25 11:38:36 INFO SessionState: Created HDFS directory: /tmp/hive/cloudera/76258222-81db-4ac1-9566-1d8f05c3ecba/_tmp_space.db
 17/07/25 11:38:36 INFO HiveClientImpl: Warehouse location for Hive client (version 1.2.1) is file:/home/cloudera/works/JsonHive/spark-warehouse/
 17/07/25 11:38:36 INFO HiveMetaStore: 0: get_database: default
 17/07/25 11:38:36 INFO audit: ugi=cloudera ip=unknown-ip-addr  cmd=get_database: default   
 17/07/25 11:38:36 INFO HiveMetaStore: 0: get_database: global_temp
 17/07/25 11:38:36 INFO audit: ugi=cloudera ip=unknown-ip-addr  cmd=get_database: global_temp   
 17/07/25 11:38:36 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
 +------------+
 |databaseName|
 +------------+
 |     default|
 +------------+

 +--------+---------+-----------+
 |database|tableName|isTemporary|
 +--------+---------+-----------+
 +--------+---------+-----------+


 Process finished with exit code 0

UPDATE

/usr/lib/hive/conf/hive-site.xml was not in the classpath so it was not reading the tables, after adding it in the classpath it worked fine ... Since I was running from IntelliJ I have this problem .. in production the spark-conf folder will have link to hive-site.xml ...


Solution

  •  17/07/25 11:38:35 INFO MetaStoreDirectSql: Using direct SQL, underlying DB is DERBY
    

    This is a hint that you're not connected to the remote hive metastore (that you've set as MySQL), and the XML file is not correctly on your classpath.

    You can do it programmatically without XML before you make a SparkSession

    System.setProperty("hive.metastore.uris", "thrift://METASTORE:9083");
    

    How to connect to a Hive metastore programmatically in SparkSQL?