spark-shell: 通过spark sql 操作hive

/************************************

• spark sql 连接hive
  **************************************/

//1) 前置条件
#1.拷贝hive-site.xml到spark/conf下，拷贝mysql-connector-java-xxx-bin.jar到hive/lib下
#2.开启hive元数据服务：hive --service metastore # 此项需要手动启动
#3.开启hadoop服务：sh $HADOOP_HOME/sbin/start-all.sh
#4.开启spark服务：sh $SPARK_HOME/sbin/start-all.sh
#5.进入spark-shell：spark-shell
#6.scala操作hive(spark-sql)

//2) 简单操作
val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)
sqlContext.sql(“use sparktest”)
sqlContext.sql(" SELECT * FROM student").collect().foreach(println)

sqlContext.sql("CREATE TABLE IF NOT EXISTS tst1 (key INT, value STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ ")
sqlContext.sql(“show tables”).show
sqlContext.sql(“insert into tst1 values(1,‘1’);”) //这句不可用

//3) 从外部数据load -> 导入临时表 -> 插入历史表
import sqlContext.implicits._

//3.1 读取数据
case class Person(name:String, age: Int)
val dataFile=sc.textFile(“hdfs://localhost:9000/data/tst20191027/sparkSQL/sparksql20191027.txt”)
val DFpeople=dataFile.map(_.split(",")).map(p=>Person(p(0),p(1).trim.toInt)).toDF()

//3.2 整理临时数据表
DFpeople.show()
DFpeople.registerTempTable(“spark_tmp_people”)
sqlContext.sql(“use sparktest”)
sqlContext.sql(“select * from spark_tmp_people”).collect.foreach(println)
sqlContext.sql(“select * from spark_tmp_people”).show

//3.3 通过临时表间接插入hive 表
sqlContext.sql(“select * from sparktest.hive_people”).show
sqlContext.sql("CREATE TABLE IF NOT EXISTS sparktest.hive_people (name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ ")
sqlContext.sql(“insert into table hive_people select * from spark_tmp_people”)
sqlContext.sql(“select * from sparktest.hive_people”).show
sqlContext.sql(“desc formatted sparktest.hive_people”).show

//3.4 插入分区表
sqlContext.sql(“select * from sparktest.people_with_partition”).show
sqlContext.sql(“insert into sparktest.people_with_partition partition(year=‘2019’) select name,age from spark_tmp_people”)
sqlContext.sql(“select * from sparktest.people_with_partition”).show

//4) 从外部数据load ->转入dataframe -> 并直接导入HIVE表
//建立对象
case class Person(name:String, age: Int)
val dataFile=sc.textFile(“hdfs://localhost:9000/data/tst20191027/sparkSQL/sparksql20191027.txt”)
val DFpeople=dataFile.map(_.split(",")).map(p=>Person(p(0),p(1).trim.toInt)).toDF()

// 建表，插入数据
sqlContext.sql("CREATE TABLE IF NOT EXISTS sparktest.hive_people_1 (name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ ")
DFpeople.insertInto(“sparktest.hive_people_1”)
sqlContext.sql(“select * from sparktest.hive_people_1”).show

//5) 通过hive load 命令直接导入数据
sqlContext.sql(“create table if not exists sparktest.tb2(name string,age int) row format delimited fields terminated by ’ ’ lines terminated by ‘\n’”)
sqlContext.sql(“desc sparktest.tb2”).show
sqlContext.sql(“select * from sparktest.tb2”).show;
sqlContext.sql(“load data local inpath ‘/home/hadoop/app/tst/prg/tb4.txt’ into table sparktest.tb2”);
sqlContext.sql(“select * from sparktest.tb2”).show;