Exception：
Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the "BoneCP" plugin to create a ConnectionPool gave an error : The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
Solution:
1、$HIVE_HOME/conf/hive-site.xml中增加关于 hive.metastore.uris 的配置信息，如下：
<property>
<name>hive.metastore.uris</name>
<value>thrift://namenode1:9083</value>
<description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property> 2、执行:$HIVE_HOME/bin/hive --service metastore,启动元数据存储服务； 3、将$HIVE_HOME/conf/hive-site.xml拷贝至$SPARK_HOME/conf/目录下； 4、启动spark-shell进行验证:$SPARK_HOME/bin/spark-shell --master namenode1:7077或spark-sql -> show databases. Note:
1. 当在Intellij IDE中编写Spark SQL程序时（val hiveContext = new HiveContext(sc); import hiveContext.sql; sql("show databases")），打包成相应的.jar文件，并利用如下脚本将任务提交到Spark集群运行时，Spark默认采用derby进行metastore，即元数据的存储；当再次在不同目录下执行该任务时，之前创建的数据库或表数据无法获取，有点即用即删的感觉。故要想访问Hive下的元数据，首先需要将Hive目录下的配置文件中的hive-site.xml文件放到Spark目录下的配置文件中，让Spark集群执行程序时能识别进入Hive元数据的路径，然后启动上述服务（hive --service metastore）即可访问Hive相应数据。 2.
/**
* An instance of the Spark SQL execution engine that integrates with data stored in Hive.
* Configuration for Hive is read from hive-site.xml on the classpath.
*/
class HiveContext(sc: SparkContext) extends SQLContext(sc) { .................................... }
3.
Use HiveContext instead. It will still create a local metastore if one is not specified. However, note that the default directory is ./metastore_db, not ./metastore
测试程序如下：
package com.husor.Hive
import org.apache.spark.{SparkContext, SparkConf}
import org.apache.spark.sql.hive.HiveContext
/* Spark SQL执行时的sql是临时的，即用即删 **/
/**
* Created by kelvin on 2015/1/27.
*/
object Recommendation {
def main(args: Array[String]) {
println("Test is starting......")
if (args.length < ) {
System.err.println("Usage:HDFS_OutputDir <Directory>")
System.exit()
}
//System.setProperty("hadoop.home.dir", "d:\\winutil\\")
val conf = new SparkConf().setAppName("Recommendation")
val spark = new SparkContext(conf)
val hiveContext = new HiveContext(spark)
import hiveContext.sql
/*sql("create database if not exists baby")
val databases = sql("show databases")
databases.collect.foreach(println)*/
sql("use baby")
/*sql("CREATE EXTERNAL TABLE if not exists origin_orders (oid string, uid INT, gmt_create INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' LOCATION '/beibei/order'")
sql("CREATE EXTERNAL TABLE if not exists items (iid INT, pid INT, title string, cid INT, brand INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' LOCATION '/beibei/item'")
sql("CREATE EXTERNAL TABLE if not exists order_item (oid string, iid INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' LOCATION '/beibei/order_item'")
sql("create table if not exists test_orders(oid string, uid INT, gmt_create INT)")
sql("create table if not exists verify_orders(oid string, uid INT, gmt_create INT)")
sql("insert OVERWRITE table test_orders select * from origin_orders where gmt_create <= 1415635200")
sql("insert OVERWRITE table verify_orders select * from origin_orders where gmt_create > 1415635200")
val tables = sql("show tables")
tables.collect.foreach(println)*/
sql("SET spark.sql.shuffle.partitions = 5")
val olderTime = System.currentTimeMillis()
val userOrderData = sql("select i.pid, o.uid, o.gmt_create from items i " +
"join order_item oi " +
"on i.iid = oi.iid " +
"join test_orders o " +
"on oi.oid = o.oid")
userOrderData.take().foreach(println)
val newTime = System.currentTimeMillis()
println("Consume Time: " + (newTime - olderTime))
userOrderData.saveAsTextFile(args())
spark.stop()
println("Test is Succeed!!!")
}
}