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、运行结果