spark代码连接hive_spark SQL学习（spark连接hive）

最新推荐文章于 2024-08-04 00:33:27 发布

weixin_39531229

最新推荐文章于 2024-08-04 00:33:27 发布

阅读量217

点赞数

文章标签： spark代码连接hive

本文链接：https://blog.csdn.net/weixin_39531229/article/details/111539951

版权

spark 读取hive中的数据

scala> import org.apache.spark.sql.hive.HiveContext

import org.apache.spark.sql.hive.HiveContext

scala> val hiveContext = new HiveContext(sc)

//hive中的feigu数据库中表stud_info

scala> val stud_infoRDD = hiveContext.sql("select * from feigu.stud_info").rdd

scala> stud_infoRDD.take(5).foreach(line => println("code:"+line(0)+";name:"+line(1)))

code:stud_code;name:stud_name

code:2015101000;name:王进

code:2015101001;name:刘海

code:2015101002;name:张飞

code:2015101003;name:刘婷

spark载入数据到hive

两个文件

hadoop@master:~/wujiadong$ cat spark_stud_info.txt

wujiadong,26

ji,24

sun,27

xu,25

hadoop@master:~/wujiadong$ cat spark_stud_score.txt

wujiadong,90

ji,100

sun,99

xu,99

scala代码

scala> import org.apache.spark.sql.hive.HiveContext

scala> val hiveContext = new HiveContext(sc)

scala> hiveContext.sql("drop table if exists wujiadong.spark_stud_info")

scala> hiveContext.sql("create table if not exists wujiadong.spark_stud_info(name string,age int) row format delimited fields terminated by ','")

scala> hiveContext.sql("load data local inpath '/home/hadoop/wujiadong/spark_stud_info.txt' into table wujiadong.spark_stud_info");

scala> hiveContext.sql("drop table if exists wujiadong.spark_stud_score")

scala> hiveContext.sql("create table if not exists wujiadong.spark_stud_score(name string,score int) row format delimited fields terminated by ','")

scala> hiveContext.sql("load data local inpath '/home/hadoop/wujiadong/spark_stud_score.txt' into table wujiadong.spark_stud_score");

然后到hive中查询是否导入成功

hive> select * from spark_stud_info;

wujiadong26

ji24

sun27

xu25

Time taken: 0.178 seconds, Fetched: 4 row(s)

hive> select * from spark_stud_score;

wujiadong90

ji100

sun99

xu99

Time taken: 0.212 seconds, Fetched: 4 row(s)

//将两张表进行连接查询大于99分的

scala> val df = hiveContext.sql("select sss.name,sss.score from wujiadong.spark_stud_info ssi join wujiadong.spark_stud_score sss on ssi.name=sss.name where sss.score > 99")

scala> df.show()

17/03/06 22:30:37 INFO FileInputFormat: Total input paths to process : 1

17/03/06 22:30:38 INFO FileInputFormat: Total input paths to process : 1

+----+-----+

|name|score|

+----+-----+

| ji| 100|

+----+-----+

//将df中数据保存到表result_stu表中

scala> hiveContext.sql("drop table if exists wujiadong.result_stud")

scala> df.saveAsTable("wujiadong.result_stu")

//然后针对表result_stu直接创建dataframe

//Hive中查看

hive> select * from result_stu;

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".

SLF4J: Defaulting to no-operation (NOP) logger implementation

SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.

ji100

Time taken: 0.252 seconds, Fetched: 1 row(s)