spark学习记录（十、SparkSQL）

一、介绍

• SparkSQL支持查询原生的RDD。 RDD是Spark平台的核心概念，是Spark能够高效的处理大数据的各种场景的基础。
• 能够在Scala中写SQL语句。支持简单的SQL语法检查，能够在Scala中写Hive语句访问Hive数据，并将结果取回作为RDD使用。

DataFrame也是一个分布式数据容器。与RDD类似，然而DataFrame更像传统数据库的二维表格，除了数据以外，还掌握数据的结构信息，即schema。同时，与Hive类似，DataFrame也支持嵌套数据类型（struct、array和map）。从API易用性的角度上 看， DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好，门槛更低。

DataFrame的底层封装的是RDD，只不过RDD的泛型是Row类型。

二、加载DataFrame方法 

添加依赖

        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.12</artifactId>
            <version>2.4.0</version>
        </dependency>

public class JavaExample {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local").setAppName("JavaExample");
        JavaSparkContext sc = new JavaSparkContext (conf);

        SQLContext sqlContext = new SQLContext(sc);
//        加载json文件
        Dataset<Row> json = sqlContext.read().format("json").load("C://json");
        Dataset<Row> json1 = sqlContext.read().json("C://json");
        /**
         * sqlContext读取json文件加载成DataFrame时，DataFrame的列会按照ASCII码排序
         * 写sql查询出的DataFrame会按照指定字段显示列
         * show()默认显示前20行数据，show(100)显示100行
         */
        //查询表内容
//        json.show();
        //查询表结构
//        json.printSchema();
        //select name,age from xxx where age >18
//        json.select("name","age").where(json.col("age").gt(18)).show();

        /**
         * 将DataFrame注册成临时表
         * 注意：t1表这张表既不在内存中也不在磁盘中，相当于一个指针指向源文件，底层操作解析Spark job读取源文件
         */
        json.registerTempTable("t1");
        sqlContext.sql("select name,age from t1 where age>18").show();

        //DataFrame转换成RDD,并获取第一列数据
        JavaRDD<Row> rdd = json.javaRDD();
        rdd.foreach(new VoidFunction<Row>() {
            public void call(Row row) throws Exception {
                System.out.println(row.get(0));
            }
        });

        sc.stop();
    }
}

读取json格式的文件创建DataFrame：

• json文件中的json数据不能嵌套json格式数据。
• DataFrame是一个一个Row类型的RDD，df.rdd()/df.javaRdd()。
• 可以两种方式读取json格式的文件。
• df.show()默认显示前20行数据。
• DataFrame原生API可以操作DataFrame（不方便）。
• 注册成临时表时，表中的列默认按ascii顺序显示列。

普通RDD转换为DataFrame

public class Person implements Serializable{

    private String id;
    private String name;
    private Integer age;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

//通过反射的方式将非json格式的RDD转换成DataFrame
public class JavaExample {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local").setAppName("JavaExample");
        JavaSparkContext sc = new JavaSparkContext (conf);

        SQLContext sqlContext = new SQLContext(sc);
        JavaRDD<String> lineRDD = sc.textFile("C:\\person.txt");
        /**
         * 注意：
         * 1.自定义类Person必须为public
         * 2.RDD转化为DataFrame会把自定义类字段名称按ASCII排序
         * 3.自定义类要实现序列化接口
         */
        JavaRDD<Person> personRDD = lineRDD.map(new Function<String, Person>() {
            public Person call(String s) throws Exception {
                Person p = new Person();
                p.setId(s.split(",")[0]);
                p.setName(s.split(",")[1]);
                p.setAge(Integer.valueOf(s.split(",")[2]));
                return p;
            }
        });

        Dataset<Row> dataFrame = sqlContext.createDataFrame(personRDD, Person.class);
        dataFrame.show();

        //将DataFrame转换为JavaRDD
        JavaRDD<Row> javaRDD = dataFrame.javaRDD();
        JavaRDD<Person> map = javaRDD.map(new Function<Row, Person>() {
            public Person call(Row row) throws Exception {
                Person p = new Person();
                p.setId((String) row.getAs("id"));
                p.setName((String) row.getAs("name"));
                p.setAge((Integer) row.getAs("age"));
                return p;
            }
        });
        map.foreach(new VoidFunction<Person>() {
            public void call(Person person) throws Exception {
                System.out.println(person);
            }
        });

        sc.stop();
    }
}

//动态创建Schema将非json格式的RDD转换成DataFrame
public class JavaExample {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local").setAppName("JavaExample");
        JavaSparkContext sc = new JavaSparkContext (conf);

        SQLContext sqlContext = new SQLContext(sc);
        JavaRDD<String> lineRDD = sc.textFile("C:\\person.txt");

        JavaRDD<Row> rowRDD = lineRDD.map(new Function<String, Row>() {
            public Row call(String s) throws Exception {
                return RowFactory.create(
                        s.split(",")[0],
                        s.split(",")[1],
                        Integer.valueOf(s.split(",")[2])
                );
            }
        });
        /**
         * 动态构建DataFrame中的元数据
         */
        List<StructField> asList = Arrays.asList(
                DataTypes.createStructField("id",DataTypes.StringType,true),
                DataTypes.createStructField("name",DataTypes.StringType,true),
                DataTypes.createStructField("age",DataTypes.IntegerType,true)
        );
        StructType schema = DataTypes.createStructType(asList);
        Dataset<Row> dataFrame = sqlContext.createDataFrame(rowRDD,schema);
        dataFrame.show();

        sc.stop();
    }
}

读取parquet文件创建DataFrame

public class JavaExample {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local").setAppName("JavaExample");
        JavaSparkContext sc = new JavaSparkContext (conf);

        SQLContext sqlContext = new SQLContext(sc);
        JavaRDD<String> jsonRDD = sc.textFile("C:\\json");
        Dataset<Row> df = sqlContext.read().json(jsonRDD);
        /**
         * 将DataFormat保存成parquet文件，
         * SaveMode指定存储文件时的保存模式：
         * Overwrite：覆盖
         * Append：追加
         * ErrorIfExists：如果存在就报错
         * Ignore：如果存在就忽略
         */
        df.write().mode(SaveMode.Overwrite).parquet("C:\\parquet");

        /**
         * 加载parquet文件成DataFrame文件
         */
        Dataset<Row> parquet = sqlContext.read().parquet("C:\\parquet");
        parquet.show();

        sc.stop();
    }
}

读取JDBC中的数据创建DataFrame(MySql为例)

public class JavaExample {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local").setAppName("JavaExample");
        //配置join或聚合操作shuffle数据时的分区数量
        conf.set("spark.sql.shuffle.partitions","1");
        JavaSparkContext sc = new JavaSparkContext (conf);
        SQLContext sqlContext = new SQLContext(sc);

        /**
         * 第一种方法
         */
        Map<String, String> options = new HashMap<String, String>();
        options.put("url","jdbc:mysql://192.168.2.125:3306/mysql");
        options.put("driver","com.mysql.jdbc.Driver");
        options.put("user","root");
        options.put("password","123456");
        options.put("dbtable","t_waybill");

        Dataset<Row> load = sqlContext.read().format("jdbc").options(options).load();
        load.show();

        /**
         * 第二种方法
         */
        DataFrameReader reader = sqlContext.read().format("jdbc");
        reader.option("url","jdbc:mysql://192.168.2.125:3306/mysql");
        reader.option("driver","com.mysql.jdbc.Driver");
        reader.option("user","root");
        reader.option("password","123456");
        reader.option("dbtable","t_waybill");

        Dataset<Row> load1 = reader.load();
        load1.show();

        /**
         * 将DataFrame结果保存到mysql中
         */
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        /**
         * SaveMode：
         * Overwrite：覆盖
         * Append：追加
         * ErrorIfExists：如果存在就报错
         * Ignore：如果存在就忽略
         */
        load.write().mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.2.125:3306/mysql","t_waybill",properties);
        sc.stop();
    }
}