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

数据准备

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