Spark读取MySQL数据量过大,一直停在Added broadcast_0_piece0 in memory on cdh-master问题。
19/09/18 14:21:17 INFO storage.BlockManagerInfo: Added broadcast_0_piece0 in memory on cdh-slave01:38815 (size: 10.2 KB, free: 2004.6 MB)
1. 检查代码没有问题。
// 获取当天到 0 时时间, 如:2019-09-18 00:00:00
val today = DateUtils.formatTimeZone(DateUtils.getTodayDate)
// 获取40天前 0 时的时间, 如:2019-08-08 00:00:00
val lastFortyDay = DateUtils.formatTimeZone(DateUtils.getDate(-40))
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "root")
prop.put("driver", "com.mysql.jdbc.Driver")
val url = "jdbc:mysql://192.168.100.47:3306/origin?zeroDateTimeBehavior=convertToNull"
val table = "user"
// TIME:MySQL表中的日期时间字段,参数 $lastFortyDay:前40天,$today:今天
val df = spark.read.jdbc(url, table, prop)
df.where(s"`TIME` between '$lastFortyDay' and '$today'").show()
2. 设置executor数量,都会因为超时被kill,只剩一个继续读MySQL,但是一直没有进度。
sudo -uhdfs spark-submit \
--class com.sm.analysis.TestRead \
--master yarn \
--deploy-mode client \
--driver-memory 4G \
--driver-cores 4 \
--num-executors 8 \
--executor-memory 4G \
--executor-cores 5 \
--jars /usr/java/jdk1.8.0_211/lib/mysql-connector-java-5.1.47.jar \
--conf spark.default.parallelism=80 \
/data4/jars/original-analysis-1.0-SNAPSHOT.jar
3. 反复测试了好多次,基本能确定是数据量太大, MySQL进行了限制,通过修改 Spark 查询的并发度来降低单次查询数量并提高效率。
因为指定了TIME字段,所以采用 SparkSQL JDBC 调用函数的方式。
// 获取当天到 0 时时间, 如:2019-09-18 00:00:00
val today = DateUtils.formatTimeZone(DateUtils.getTodayDate)
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "root")
prop.put("driver", "com.mysql.jdbc.Driver")
val url = "jdbc:mysql://192.168.100.47:3306/origin?zeroDateTimeBehavior=convertToNull"
val table = "user"
val predicates = Array(
lastFortyDay -> lastThirtyDay,
lastThirtyDay -> lastTwentyDay,
lastTwentyDay -> lastTenDay,
lastTenDay -> today
).map {
case (start, end) =>
s"`TIME` between '$start' and '$end'"
}
val df = spark.read.jdbc(url, table, predicates, prop)
df.show()
4. 另外,其他提高并发度的方式。
val prop = new Properties()
prop.put("user", "root")
prop.put("password", "root")
prop.put("driver", "com.mysql.jdbc.Driver")
val url = "jdbc:mysql://192.168.100.47:3306/origin?zeroDateTimeBehavior=convertToNull"
val table = "user"
val columnName = "ID"
val lowerBound = 0
val upperBound = 100000
val numPartitions = 10
val df = spark.read.jdbc(url, table, columnName, lowerBound, upperBound, numPartitions, prop)
df.limit(10).show()
标签:00,jdbc,读取,val,mysql,prop,MySQL,put,Spark
来源: https://blog.csdn.net/lingeio/article/details/100992141