spark多数据源hive和mysql联合使用

1、需要将mysql驱动放入spark的jars包下面

spark-2.6.0-bin-2.6.0-cdh5.16.2/jars/

2、准备数据
2.1、mysql库

2.2、准备hive数据

3、编写代码

public class MoreDatabasesSpark {
    public static void main(String[] args) {
        SparkSession session = SparkSession.builder().appName("SparkSessionApp")
                .master("spark://172.10.70.196:7077")
                .config("hive.metastore.uris", "thrift://172.10.70.196:9083")
                //直接连接hive
                .enableHiveSupport()
                .getOrCreate();
//
        Dataset peopleData = session.sql("select * from test_db.helloword");
        peopleData.registerTempTable("helloword");
        Dataset sexData = session.read().format("jdbc").option("url","jdbc:mysql://172.10.70.196:3306/bank_a?createDatabaseIfNotExist=true&useSSL=true&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8")
                .option("dbtable", "test_data")
                .option("user", "admin")
                .option("password", "admin")
                .option("driver","com.mysql.cj.jdbc.Driver")
                .load();
        sexData.registerTempTable("sex");
        session.sql("select a.id,a.name,b.memo from helloword a left join sex b on a.id = b.val").show();
        session.stop();
    }
}

4、将打包好的jar包上传服务器，并且运行

./spark-submit --class org.example.SparkSqlToHive /usr/local/src/test/SparkTest-1.0-SNAPSHOT.jar

5、运行结果