Spark操作Mysql数据库

 

概述

Spark操作Mysql的方式有两种，一种是常见的JDBC（甚至mybatis），另一种是Spark SQL。

JDBC跟Spark没有关联，是Java语言特性，可以使用JDBC将结果从Mysql中读出来，再使用Spark进行相应的运算，使用JDBC相对比较简单。

Spark SQL相比JDBC的优势在于读取Mysql后的结果为DataFrame，可以直接使用Spark进行相应的运算。

可以根据需要选择，如果不必使用Spark进行相应运算，直接选择JDBC即可。

JDBC介绍

Java数据库连接（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

Mybatis也是可以的，但是Mybatis相对比较重量级，一般不使用。

JDBC具体可参考：https://blog.csdn.net/qq_22172133/article/details/81266048

代码示例如下：

读取和写入数据

public static List<RidingForm> selectByEndTime(long start, long end) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn= DriverManager.getConnection(Constants.DATASOURCE_URL, Constants.DATASOURCE_USER, Constants.DATASOURCE_PASSWORD);
        Statement statement = conn.createStatement();

        String sql = "SELECT *  FROM bdm_riding_form WHERE ( unix_timestamp(start_time) > " + start
                + " and unix_timestamp(end_time) < " + end + "  )";

        ResultSet rs = statement.executeQuery(sql);
        List<RidingForm> ridingForms = new ArrayList<>();

        while (rs.next()){
            RidingForm ridingForm = new RidingForm();

            ridingForm.setId(rs.getLong("id"));
            ridingForm.setLicencePlate(rs.getString("licence_plate"));
            ridingForm.setDestination(rs.getString("destination"));
            ridingForm.setStartTime(rs.getTimestamp("start_time").getTime()/1000);
            ridingForm.setEndTime(rs.getTimestamp("end_time").getTime()/1000);
            ridingForm.setDepLat(rs.getDouble("dep_lat"));
            ridingForm.setDepLon(rs.getDouble("dep_lon"));
            ridingForm.setDestLat(rs.getDouble("dest_lat"));
            ridingForm.setDestLon(rs.getDouble("dest_lon"));

            ridingForms.add(ridingForm);
        }

        statement.close();
        conn.close();

        return ridingForms;
    }

public static int insertLineUnconformResult(LineUnconform record) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn= DriverManager.getConnection(Constants.DATASOURCE_URL, Constants.DATASOURCE_USER, Constants.DATASOURCE_PASSWORD);
        Statement statement = conn.createStatement();

        String start = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.getStart());
        String end = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.getEnd());

        String sql = "insert into line_unconform_check (car_no,start,end,order_id) " +
                "values(" + "'" + record.getCarNo()+ "'" + "," +
                "'" + start + "'" + "," +
                "'" + end + "'" + "," +
                record.getOrderId() +
                ")";

        int row = statement.executeUpdate(sql);

        statement.close();
        conn.close();

        return row;
    }

Spark SQL介绍

Spark SQL是Spark用来处理结构化数据的一个模块，它提供了一个编程抽象叫做DataFrame，并且作为分布式SQL查询引擎的功能。

Spark SQL操作Mysql有多种代码实现方式，可参考https://www.cnblogs.com/wcgstudy/p/10984550.html

代码实例如下（两种读操作）：

private static List<String> readSQL1(SQLContext sqlContext) {
        DataFrameReader reader = sqlContext.read().format("jdbc");
        reader.option("url", "jdbc:mysql://localhost:10655/offical_cars");
        reader.option("driver", "com.mysql.cj.jdbc.Driver");
        reader.option("user", "root");
        reader.option("password", "123456");
        reader.option("dbtable", "bdm_vehicle");
        DataFrame score = reader.load();
        score.show();
        score.registerTempTable("bdm_vehicle");

        DataFrame result =
                sqlContext.sql("select licence_plate "
                        + "from bdm_vehicle "
                        + "where user = 1177");
        result.show();

        JavaRDD<Row> a = result.toJavaRDD();
        Iterator<Row> rows = a.toLocalIterator();
        List<String> carNos = new ArrayList<>();
        while (rows.hasNext()) {
            Row row = rows.next();
            String carNo = row.getString(0);
            carNos.add(carNo);
        }

        return carNos;
    }

private static List<String> readMySQL2(SQLContext sqlContext) {
        String url = "jdbc:mysql://localhost:10655/offical_cars?useUnicode=true&characterEncoding=UTF-8";
        String table = "bdm_vehicle";
        //增加数据库的用户名(user)密码(password),指定test数据库的驱动(driver)
        Properties connectionProperties = new Properties();
        connectionProperties.put("user","root");
        connectionProperties.put("password","123456");
        connectionProperties.put("driver","com.mysql.cj.jdbc.Driver");

        DataFrame jdbcDF = sqlContext.read().jdbc(url,table,connectionProperties).select("*");
        //显示数据
        jdbcDF.show();

        JavaRDD<Row> a = jdbcDF.toJavaRDD();
        Iterator<Row> rows = a.toLocalIterator();
        List<String> carNos = new ArrayList<>();
        while (rows.hasNext()) {
            Row row = rows.next();
            String carNo = row.getString(0);
            carNos.add(carNo);
        }

        return carNos;
    }