Spark环境
spark-2.3.0
添加依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.22</version>
</dependency>
创建SparkSession
/**
* SparkSession
* 支持数据源:textFile,load,csv,json,text,format,jdbc
* @return
*/
public static SparkSession getSparkSession() {
return SparkSession
.builder()
.appName("SparkSQL")
.master("local[*]")
.getOrCreate();
}
访问Mysql方式1:
public static void test(){
String url="jdbc:mysql://localhost:3306/sparksql?user=root&password=123456";
String tableName="users";
SparkSession spark= SparkUtil.getSparkSession();
Map<String,String> map=new HashMap<>();
map.put("driver","com.mysql.jdbc.Driver");
map.put("url",url);
map.put("dbtable",tableName);
map.put("fetchSize","100");
//读取users信息
Dataset<Row> jdbcDF = spark.read()
.format("jdbc")
.options(map)
.load();
//读取users信息,保存到users_copy表
jdbcDF.write()
.format("jdbc")
.option("url", url)
.option("dbtable", "users_copy")
.save();
}
访问Mysql方式2:
public static void test2(){
String url="jdbc:mysql://localhost:3306/sparksql";
String tempTableName=" (select id,name from users) as u";
SparkSession spark= SparkUtil.getSparkSession();
Properties connectionProperties = new Properties();
connectionProperties.put("user", "root");
connectionProperties.put("password", "123456");
connectionProperties.put("isolationLevel","REPEATABLE_READ");
//读取users信息
Dataset<Row> jdbcDF2 = spark.read()
.jdbc(url, tempTableName, connectionProperties);
//读取users信息,保存到users1表
jdbcDF2.write()
.jdbc(url, "users1", connectionProperties);
}