数据准备

data/department.txt

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

data/employee.txt

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

bin/spark-sql

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连接准备工作

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

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

hive和mysql表连接

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")

保存操作结果

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")

最后

以上就是文静蛋挞为你收集整理的spark hive数据导出到mysql 以及和 mysql进行表连接查询数据准备的全部内容，希望文章能够帮你解决spark hive数据导出到mysql 以及和 mysql进行表连接查询数据准备所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。