spark hive数据导出到mysql 以及和 mysql进行表连接查询数据准备

65 阅读 0 评论 43 点赞

我是靠谱客的博主文静蛋挞，这篇文章主要介绍spark hive数据导出到mysql 以及和 mysql进行表连接查询数据准备，现在分享给大家，希望可以做个参考。

数据准备

data/department.txt

复制代码

1
2
3
4
1	技术部
2	运营部
3	市场部
4	财务部

data/employee.txt

复制代码

1
2
3
4
5
6
7
1	1	方海亮	30
2	1	何胜强	35
3	1	林洪敏	32
4	2	丁泽林	27
5	2	李元元	25
6	3	王小飞	28
7	4	刘亦亭	31

bin/spark-sql

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create database if not exists hadoop14;
use hadoop14;
create table department (
id int, name string
)
row format delimited fields terminated by 't';
;
create table employee (
id int, department int, name string, age int
)
row format delimited fields terminated by 't';
;
load data local inpath 'data/department.txt' into table department;
load data local inpath 'data/employee.txt' into table employee;

mysql连接准备工作

复制代码

1
2
3
4
5
6

val properties = new Properties()
properties.put("user","hive_test")
properties.put("password", "123456")
val connectUrl = "jdbc:mysql://mustafa-PC:3306/hive_test"
val targetTable = "department"

hive保存到mysql

复制代码

1
2
3
4
5
6
7

spark
.read
.table("hadoop14.department")
.write
.mode(SaveMode.Overwrite)
.jdbc(connectUrl, targetTable, properties)

hive和mysql表连接

复制代码

1
2
3
4

val df: DataFrame = spark.read.jdbc(connectUrl, targetTable, properties)
df.createOrReplaceTempView("department")
var resultDf = spark.sql("select e.name as employee_name, e.age as employee_age, d.name as department_name from hadoop14.employee e join department d on e.department = d.id")

保存操作结果

复制代码

1
2
3
resultDf.cache()
resultDf.write.mode(SaveMode.Overwrite).saveAsTable("hadoop14.employee_result")
resultDf.write.format("json").format("parquet").mode(SaveMode.Overwrite).save("/user/mustafa/employee_result2")