SparkSQL 学习笔记----JDBC数据源实战

最新推荐文章于 2023-07-25 10:26:44 发布

PZ~浪味仙

最新推荐文章于 2023-07-25 10:26:44 发布

阅读量194

点赞数

分类专栏： Spark SQL

Spark 同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

SQL

12 篇文章 0 订阅

订阅专栏

1、使用jdbc数据源

Spark SQL支持使用JDBC从关系型数据库中读取数据。读取的数据又DataFrame表示，可以很方便地使用Spark Core提供的各种算子进行处理。

java版本

Map<String,String> options = new HashMap<String,String>();
options.put("url","jdbc:mysql://spark1:3306/testdb");
options.put("dbtable","students");
DataFrame jdbcDF = sqlContext.read().format("jdbc").options(options).load();

scala版本

val jdbcDF = sqlContext.read.format("jdbc").options(
Map("url" -> "jdbc:mysql://spark1:3306/testdb",
"dbtable" -> "students")).load()

2、实践：查询分数大于80分的学生信息

package pz.spark.study.sql;

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaPairRDD;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.PairFunction;
import org.apache.spark.api.java.function.VoidFunction;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;
import scala.Tuple2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCDataSource {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf().setAppName("JDBCDataSource").setMaster("local");
        JavaSparkContext sc = new JavaSparkContext(conf);
        SQLContext sqlContext = new SQLContext(sc);
        //分别将mysql中两张表的数据加载为DataFrame
        Map<String, String> options = new HashMap<String, String>();
        options.put("url", "jdbc:mysql://spark1:3306/testdb");
        options.put("dbtable", "student_infos");
        Dataset<Row> studentInfosDF = sqlContext.read().format("jdbc").options(options).load();

        options.clear();
        options.put("url", "jdbc:mysql://spark1:3306/testdb");
        options.put("dbtable", "student_scores");
        Dataset<Row> studentScoresDF = sqlContext.read().format("jdbc").options(options).load();

        //将两个DataFrame转换为JavaPairRDD，执行join操作
        JavaPairRDD<String, Tuple2<Integer, Integer>> studentsRDD = studentInfosDF.javaRDD().mapToPair(new PairFunction<Row, String, Integer>() {
            @Override
            public Tuple2<String, Integer> call(Row row) throws Exception {
                return new Tuple2<String, Integer>(row.getString(0), Integer.valueOf(String.valueOf(row.get(1))));
            }
        }).join(studentScoresDF.javaRDD().mapToPair(new PairFunction<Row, String, Integer>() {
            @Override
            public Tuple2<String, Integer> call(Row row) throws Exception {
                return new Tuple2<String, Integer>(String.valueOf(row.get(0)), Integer.valueOf(String.valueOf(row.get(1))));
            }
        }));

        //将JavaPairRDD转换为JavaRDD<Row>
        JavaRDD<Row> studentRowsRDD = studentsRDD.map(new Function<Tuple2<String, Tuple2<Integer, Integer>>, Row>() {
            @Override
            public Row call(Tuple2<String, Tuple2<Integer, Integer>> stringTuple2Tuple2) throws Exception {
                return RowFactory.create(stringTuple2Tuple2._1, stringTuple2Tuple2._2._1, stringTuple2Tuple2._2._2);
            }
        });
        //过滤出分数>80分的数据
        JavaRDD<Row> filteredStudentRowsRDD = studentRowsRDD.filter(new Function<Row, Boolean>() {
            @Override
            public Boolean call(Row row) throws Exception {
                if (row.getInt(2) > 80) {
                    return true;
                } else {
                    return false;
                }
            }
        });

        //转换为DataFrame
        List<StructField> structFields = new ArrayList<StructField>();

        structFields.add(DataTypes.createStructField("name",DataTypes.StringType,true));
        structFields.add(DataTypes.createStructField("age",DataTypes.IntegerType,true));
        structFields.add(DataTypes.createStructField("score",DataTypes.IntegerType,true));

        StructType structType = DataTypes.createStructType(structFields);
        Dataset<Row> studentsDF = sqlContext.createDataFrame(filteredStudentRowsRDD, structType);

        Row[] rows  = studentsDF.collect();
        for (Row row :rows) {
            System.out.println(row);
        }
        //将DataFrame中的数据保存到mysql表中
        /*options.put("dbtable", "good_student_infos");

        studentsDF.write().format("jdbc").options(options).save();*/
        //这种方式是企业中较常用的，有可能是插入mysql\hbase\redis缓存
        studentsDF.javaRDD().foreach(new VoidFunction<Row>() {
            @Override
            public void call(Row row) throws Exception {
                String sql = "insert into good_student_infos values("
                        +"'" + String.valueOf(row.getString(0)) + "',"
                        +Integer.valueOf(String.valueOf(row.get(1)))+ ","
                        +Integer.valueOf(String.valueOf(2)) + ")";
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = null;
                Statement stmt = null;
                try{
                    conn = DriverManager.getConnection("jdbc:mysql://spark1:3306/testb","","");
                    stmt = conn.createStatement();
                    stmt.executeUpdate(sql);
                }catch(Exception e){
                    e.printStackTrace();
                }finally{
                    if(stmt != null){
                        stmt.close();
                    }
                    if(conn != null){
                        conn.close();
                    }
                }
            }
        });
        sc.close();
    }
}