15、Spark Sql（一），生成DataFrame的方式

weixin_34353714發表於2018-03-04
SparkSQL

一、一些基本概念

1、Shark
　　Shark是基於Spark計算框架之上且相容Hive語法的Sql執行引擎，由於底層計算採用了Spark，效能比MapReduce的Hive普遍快2倍以上，當資料全部load到記憶體的話，快10倍以上。
2、相對於Shark，Spark Sql的優勢
　　（1）Spark Sql產生的根本原因，其完全脫離了Hive的限制
　　（2）Spark Sql支援查詢原生的RDD，這點極為關鍵。RDD是Spark平臺的核心概念，是Spark能高效處理大資料的各種場景的基礎
　　（3）能夠在Scaka中寫Sql語句，支援簡單的Sql語法檢查，能在Scala中寫Hive語句訪問Hive，並將結果取回作為RDD使用
3、DataFrame
　　（1）分散式資料容器
　　（2）像傳統的資料庫二維表格，除了資料外，還掌握資料的結構資訊，即Schema
　　（3）支援巢狀資料型別（struct，array和map）

二、Spark Sql優化

1、謂詞下推
2、將表資料比較小的表自動廣播
SELECT table1.name，table2.score
FROM table1 JOIN table2 ON (table1.id=table2.id)
WHERE table1.age>50 AND table2.score>90

4369144-d055471cdc201584.png
image.png

三、Spark Sql第一個程式

public class DataFrameOpsJsonRdd {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf().setAppName("dataFrameOpsJsonRdd").setMaster("local");
        JavaSparkContext jsc = new JavaSparkContext(conf);
        SQLContext sqlContext = new SQLContext(jsc);

        // 建立一個本地集合
        List<String> nameList = Arrays.asList(
                "{'name':'zhangsan', 'age':55}",
                "{'name':'lisi', 'age':30}",
                "{'name':'lisisi', 'age':30}",
                "{'name':'wangwu', 'age':19}");
        List<String> scoreList = Arrays.asList(
                "{'name':'zhangsan', 'score':100}",
                "{'name':'lisi', 'score':99}");
        // 並行化成一個RDD， 現在RDD中元素格式是json格式
        JavaRDD<String> nameRDD = jsc.parallelize(nameList);
        JavaRDD<String> scoreRDD = jsc.parallelize(scoreList);

        // 通過json格式的RDD建立dataframe
        DataFrame nameDF = sqlContext.read().json(nameRDD);
        DataFrame scoreDF = sqlContext.read().json(scoreRDD);

//        DataFrame resultDF = nameDF.join(scoreDF, nameDF.col("name").$eq$eq$eq(scoreDF.col("name")))
//                .select(nameDF.col("name"), nameDF.col("age"), scoreDF.col("score"));
//        resultDF.show();
        // 把dataframe的內容註冊成一張臨時表
        nameDF.registerTempTable("name");
        scoreDF.registerTempTable("score");
        String sql = "select name.name, name.age, score.score from name join score on (name.name = score.name)";
        sqlContext.sql(sql).show();
    }
}

resultDF.show();完了之後，資料才會載入到記憶體中
總結：建立DataFrame的方式
1、通過json格式的檔案建立（json格式的檔案不能巢狀，如果是巢狀需要自己去解析好了，再用Spark Sql）
2、通過json格式的RDD建立（RDD中的資料是String型別，但資料是json格式）
3、通過一個普通的RDD（非json格式）建立
3.1、反射的方式
　　（1）自定義的類必須是public
　　（2）自定義的類必須是可序列化的
　　（3）RDD轉成DataFrame的時候，DataFrame中列的順序會根據自定義類中的欄位名按照字典順序進行排序
　　（4）從DataFrame中取值的時候要注意列的順序，getAs("列名")
　　（5）不建議使用，如果以後想增加新的列，或者修改已有的列的資訊，麻煩

public class RDD2DataFrameByReflect {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf().setAppName("RDD2DataFrameByReflect").setMaster("local");
        JavaSparkContext jsc = new JavaSparkContext(conf);
        SQLContext sqlContext = new SQLContext(jsc);

        JavaRDD<String> lines = jsc.textFile("Peoples.txt");
        // 拆開每一行的資料，賦值給Person
        JavaRDD<Person> personRDD = lines.map(new Function<String, Person>() {
            private static final long serialVersionUID = 1L;

            @Override
            public Person call(String lines) throws Exception {
                String[] split = lines.split(",");
                Person person = new Person();
                person.setId(Integer.valueOf(split[0]));
                person.setName(split[1]);
                person.setAge(Integer.valueOf(split[2]));
                return person;
            }
        });
        DataFrame dataFrame = sqlContext.createDataFrame(personRDD, Person.class);
        dataFrame.registerTempTable("personTable");
        DataFrame resultDataFrame = sqlContext.sql("select * from personTable where age > 7");
        resultDataFrame.show();

        // 把DataFrame轉成Person型別的RDD
        JavaRDD<Row> rowRDD = resultDataFrame.javaRDD();
        JavaRDD<Person> pRDD = rowRDD.map(new Function<Row, Person>() {
            private static final long serialVersionUID = 1L;

            @Override
            public Person call(Row row) throws Exception {
                int id = row.getAs("id");
                String name = row.getAs("name");
                int age = row.getAs("age");
                Person p = new Person(id, name, age);
                return p;
            }
        });
        pRDD.foreach(new VoidFunction<Person>() {
            @Override
            public void call(Person person) throws Exception {
                System.out.println("person:" + person);
            }
        });
    }
}

3.2、動態建立Schema方式將一個普通的RDD變成DataFrame（可以將列的資訊放到外部儲存中，修改的時候只需要去修改配置檔案或資料庫即可）

public class RDD2DataFrameByProgrammatically {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf().setAppName("RDD2DataFrameByProgrammatically").setMaster("local");
        JavaSparkContext jsc = new JavaSparkContext(conf);
        SQLContext sqlContext = new SQLContext(jsc);

        JavaRDD<String> lines = jsc.textFile("Peoples.txt");
        // 拆分每條記錄
        JavaRDD<Row> rowRDD = lines.map(new Function<String, Row>() {
            private static final long serialVersionUID = 1L;

            @Override
            public Row call(String line) throws Exception {
                String[] split = line.split(",");
                return RowFactory.create(Integer.valueOf(split[0]), split[1], Integer.valueOf(split[2]));
            }
        });
        // 動態構造DataFrame的後設資料，
        // 一般而言，有多少列以及每列的具體型別可能來自於Json，也可能來自於DB
        ArrayList<StructField> structFields = new ArrayList<StructField>();
        // 我們可以將每列的資訊儲存到外部檔案或者資料庫中
        // 在建立schema的時候去外部檔案或者資料庫中查詢
        // 修改每一列的列名以及列的型別的時候就變的非常方便了
        structFields.add(DataTypes.createStructField("id", DataTypes.IntegerType, true));
        structFields.add(DataTypes.createStructField("name", DataTypes.StringType, true));
        structFields.add(DataTypes.createStructField("age", DataTypes.IntegerType, true));
        // 構建StructType，用於最後DataFrame後設資料的描述
        StructType schema = DataTypes.createStructType(structFields);
        // 基於已有的MetaData以及RDD<Row> 來構造DataFrame
        DataFrame dataFrame = sqlContext.createDataFrame(rowRDD, schema);
        dataFrame.registerTempTable("persons");
        DataFrame resultDF = sqlContext.sql("select * from persons where age > 6");
        resultDF.show();

        JavaRDD<Row> resultRDD = resultDF.javaRDD();
        resultRDD.foreach(new VoidFunction<Row>() {
            private static final long serialVersionUID = 1L;
            @Override
            public void call(Row row) throws Exception {
                int id = row.getInt(0);
                String name = row.getString(1);
                int age = row.getInt(2);
                System.out.println("id:" + id + " name:" + name + " age:" + age);
            }
        });
        jsc.stop();
    }
}

4.1、通過讀取parquet格式的檔案建立

public class ParquetLoadData {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf().setAppName("ParquetLoadData").setMaster("local");
        JavaSparkContext jsc = new JavaSparkContext(conf);
        SQLContext sqlContext = new SQLContext(jsc);
        // 從hdfs上讀取users.parquet
        DataFrame personDF = sqlContext.read().parquet("hdfs://master:9000/input/users.parquet");

        //測試時如果沒有users.parquet檔案，可以自己手動生成一個
//        List<Person> personList = new ArrayList<Person>();
//        personList.add(new Person(1, "xinxin1", 21));
//        personList.add(new Person(2, "xinxin2", 23));
//        personList.add(new Person(3, "xinxin2", 25));
//        JavaRDD<Person> personRDD = jsc.parallelize(personList);
//        DataFrame personDF = sqlContext.createDataFrame(personRDD, Person.class);
//        personDF.show();

        // 結果DataFrame內容寫回到hdfs
        personDF.write().format("parquet").mode(SaveMode.Overwrite)
.save("hdfs://master:9000/output/result.parquet");
    }
}

4.2、parquet格式的檔案能夠自動推斷分割槽
如：hdfs上檔案路徑為/users/gender=male/country=US/users.parquet，讀取內容如下


4369144-babc168ca2716c52.png
image.png

根據目錄名增加兩列：gender，country，防止暴力掃描全表
說明：（1）目錄格式必須為KV格式
　　（2）如何將txt檔案轉成parquet檔案？
　　　　RDD -> DataFrame -> write
　　　　RDD.saveAsHadoopFIle()

5、讀取mysql中資料建立一個DataFrame

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);
        // 方法1、分別將mysql中兩張表的資料載入為DataFrame
        /*
         * Map<String, String> options = new HashMap<String, String>();
         * options.put("url", "jdbc:mysql://hadoop1:3306/testdb");
         * options.put("driver", "com.mysql.jdbc.Driver");
         * options.put("user","spark");
         * options.put("password", "spark2016");
         * options.put("dbtable", "student_info");
         * DataFrame studentInfosDF = sqlContext.read().format("jdbc").options(options).load();
         *
         * options.put("dbtable", "student_score");
         * DataFrame studentScoresDF = sqlContext.read().format("jdbc") .options(options).load();
         */
        // 方法2、分別將mysql中兩張表的資料載入為DataFrame
        DataFrameReader reader = sqlContext.read().format("jdbc");
        reader.option("url", "jdbc:mysql://hadoop1:3306/testdb");
        reader.option("dbtable", "student_info");
        reader.option("driver", "com.mysql.jdbc.Driver");
        reader.option("user", "spark");
        reader.option("password", "spark2016");
        DataFrame studentInfosDF = reader.load();

        reader.option("dbtable", "student_score");
        DataFrame studentScoresDF = reader.load();
        // 將兩個DataFrame轉換為JavaPairRDD，執行join操作

        studentInfosDF.registerTempTable("studentInfos");
        studentScoresDF.registerTempTable("studentScores");

        String sql = "SELECT studentInfos.name,age,score "
                + "     FROM studentInfos JOIN studentScores"
                + "      ON (studentScores.name = studentInfos.name)"
                + "  WHERE studentScores.score > 80";

        DataFrame resultDF = sqlContext.sql(sql);
        resultDF.show();

        //使用forachPartition來優化
        resultDF.javaRDD().foreach(new VoidFunction<Row>() {

            private static final long serialVersionUID = 1L;

            @Override
            public void call(Row row) throws Exception {
                String sql = "insert into good_student_info values(" + "'" + String.valueOf(row.getString(0)) + "',"
                        + Integer.valueOf(String.valueOf(row.get(1))) + ","
                        + Integer.valueOf(String.valueOf(row.get(2))) + ")";

                Class.forName("com.mysql.jdbc.Driver");

                Connection conn = null;
                Statement stmt = null;
                try {
                    conn = DriverManager.getConnection("jdbc:mysql://hadoop1:3306/testdb", "spark", "spark2016");
                    stmt = conn.createStatement();
                    stmt.executeUpdate(sql);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                }
            }

        });

        /**
         * 將SparkContext 關閉，釋放資源
         */
        sc.close();
    }
}

相關文章