spark sql连接mysql数据库查询乱码,解决方案
- 现象:spark sql 连接数据库能查非中文ok,查询汉字就没结果,也不报错
- 原因:中文乱码
- 解决方案: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|
+---+----+---+---+---+