mysql 连接:
首先确认mysql的版本:执行查询语句
select version();
maven中配置mysql连接:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
版本最好与mysql版本保持一致,避免出现一些奇怪的问题
获取mysql连接:
//驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getMysqlConnection(String url,String userName,String password) throws SQLException {
Connection connection = DriverManager.getConnection(url,userName,password);
return connection;
}
spark任务提交:
spark2-submit \
--master yarn \
--deploy-mode cluster \
--class cn.xxx.SparkToMysql \
--conf spark.yarn.maxAppAttempts=1 \
--driver-class-path mysql-connector-java-8.0.21.jar \
SparkTest.jar
记得将mysql驱动包上传,
常见报错:
Unregistering ApplicationMaster with FAILED (diag message: User class threw exception: java.sql.SQLException: No suitable driver found for jdbc:mysql:
一般是由于没有将驱动包上传,或者spark任务提交的时候 没有配置 --driver-class-path
或者没有 写 Class.forName("com.mysql.jdbc.Driver");
其他方式连接
public static Dataset<Row> executeSql(SparkSession sparkSession, String url,String userName,String password, String sql) {
String table = "(" + sql + ") AS MYSQL_TABLE";
Map<String, String> options = new HashMap<>();
options.put("url",url);
options.put("driver","com.mysql.jdbc.Driver");
options.put("user", userName);
options.put("password", password);
options.put("dbtable", table);
return sparkSession.sqlContext().read().format("jdbc").options(options).load();
}