spark sql连接mysql数据库查询乱码,解决方案

spark sql连接mysql数据库查询乱码,解决方案

1. 现象:spark sql 连接数据库能查非中文ok,查询汉字就没结果,也不报错
2. 原因:中文乱码
3. 解决方案:useUnicode=true&characterEncoding=utf-8

代码:

val conf = new SparkConf().setAppName("").setMaster("local")
val spark = SparkSession.builder().config(conf).getOrCreate()

import spark.implicits._

//jdbc
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "1234")

//创建连接读取
val df = spark.read.jdbc("jdbc:mysql://localhost:3306/b16?useUnicode=true&characterEncoding=utf-8", "emp", prop)

df.createOrReplaceTempView("e")
spark.sql("select * from e ").show()
spark.sql("select * from e where name = '李四'  ").show()

运行结果:

+---+----+---+---+---+
| id|name|sex|age|sal|
+---+----+---+---+---+
|  1|   a|  男| 12|200|
|  2|  李四|  女| 14| 60|
|  3|  王五|  男| 13|200|
|  4|  赵六|  男| 15|  5|
+---+----+---+---+---+

+---+----+---+---+---+
| id|name|sex|age|sal|
+---+----+---+---+---+
|  2|  李四|  女| 14| 60|
+---+----+---+---+---+