Spark SQL DataFrame -- 数据帧

---

1. DataFrame简介

DataFrame是一个分布式数据集合，它被组织成命名列。从概念上讲，它相当于具有良好优化技术的关系表。

DataFrame可以从不同来源的数组构造，例如Hive表，结构化数据文件，外部数据库或现有RDD。这个API是为现代大数据和数据科学应用程序设计的，从Python的R ProgrammingandPandas中的DataFrame中获得灵感。

2. DataFrame特性

• 能够将单个节点集群上的大小为Kb到Pb的数据处理为大型集群。
• 支持不同的数据格式(Avro，csv，弹性搜索和Cassandra)和存储系统(HDFS，HIVE表，mysql等)。
• 通过Spark SQL Catalyst优化器(树变换框架)的最先进的优化和代码生成。
• 可以通过Spark-Core轻松地与所有大数据工具和框架集成。
• 提供用于Python，Java，Scala和R编程的API。

3. DataFrame 与RDD 的区别

3.1 功能上比较

3.2 数据存储结构上比较

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

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

4. DataFrame的创建

4.1 版本1.X

4.1.1 版本1.X创建DataFrame

1. 使用JSON数据创建

   1) JSON 文件

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

		SQLContext sqlContext = new SQLContext(sc);	
		DataFrame df = sqlContext.read().json("hdfs://spark1:9000/students.json");
		//DataFrame df = sqlContext.read().format("json").load("sparksql/json"); 

   2) 通过json格式的RDD创建DataFrame

		SQLContext sqlContext = new SQLContext(sc);
		JavaRDD<String> nameRDD = sc.parallelize(Arrays.asList(
		    "{\"name\":\"zhangsan\",\"age\":\"18\"}",
		    "{\"name\":\"lisi\",\"age\":\"19\"}",
		    "{\"name\":\"wangwu\",\"age\":\"20\"}"
		));
		JavaRDD<String> scoreRDD = sc.parallelize(Arrays.asList(
		"{\"name\":\"zhangsan\",\"score\":\"100\"}",
		"{\"name\":\"lisi\",\"score\":\"200\"}",
		"{\"name\":\"wangwu\",\"score\":\"300\"}"
		));
		
		DataFrame namedf = sqlContext.read().json(nameRDD);
		DataFrame scoredf = sqlContext.read().json(scoreRDD);
	-------------操作---------------------
		namedf.registerTempTable("name");
		scoredf.registerTempTable("score");
		
		DataFrame result = sqlContext.sql("select name.name,name.age,score.score from name,score where name.name = score.name");
		result.show();

2. 非json格式的RDD创建DataFrame（重要）

   1) 通过反射的方式将非json格式的RDD转换成DataFrame（不建议使用）
    - 涉及模式-反射
    - 自定义类要可序列化
    - 自定义类的访问级别是Public
    - RDD转成DataFrame后会根据映射将字段按Assci码排序
    - 将DataFrame转换成RDD时获取字段两种方式,一种是df.getInt(0)下标获取（不推荐使用），另一种是df.getAs(“列名”)获取（推荐使用）

关于序列化问题:
    1.反序列化时serializable 版本号不一致时会导致不能反序列化。
    2.子类中实现了serializable接口，父类中没有实现，父类中的变量不能被序列化,序列化后父类中的变量会得到null。
    注意：父类实现serializable接口,子类没有实现serializable接口时，子类可以正常序列化
    3.被关键字transient修饰的变量不能被序列化。
    4.静态变量不能被序列化，属于类，不属于方法和对象，所以不能被序列化。
    另外：一个文件多次writeObject时，如果有相同的对象已经写入文件，那么下次再写入时，只保存第二次写入的引用，读取时，都是第一次保存的对象。

	/**
	* 注意：
	* 1.自定义类必须是可序列化的
	* 2.自定义类访问级别必须是Public
	* 3.RDD转成DataFrame会把自定义类中字段的名称按assci码排序
	*/
	SparkConf conf = new SparkConf();
	conf.setMaster("local").setAppName("RDD");
	JavaSparkContext sc = new JavaSparkContext(conf);
	SQLContext sqlContext = new SQLContext(sc);
	JavaRDD<String> lineRDD = sc.textFile("sparksql/person.txt");
	JavaRDD<Person> personRDD = lineRDD.map(new Function<String, Person>() {
	
	    /**
	    * 
	    */
	    private static final long serialVersionUID = 1L;
	
	    @Override
	    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;
	    }
	});
	/**
	* 传入进去Person.class的时候，sqlContext是通过反射的方式创建DataFrame
	* 在底层通过反射的方式获得Person的所有field，结合RDD本身，就生成了DataFrame
	*/
	DataFrame df = sqlContext.createDataFrame(personRDD, Person.class);
	df.show();
	df.registerTempTable("person");
	sqlContext.sql("select  name from person where id = 2").show();
	
	/**
	* 将DataFrame转成JavaRDD
	* 注意：
	* 1.可以使用row.getInt(0),row.getString(1)...通过下标获取返回Row类型的数据，但是要注意列顺序问题---不常用
	* 2.可以使用row.getAs("列名")来获取对应的列值。
	* 
	*/
	JavaRDD<Row> javaRDD = df.javaRDD();
	JavaRDD<Person> map = javaRDD.map(new Function<Row, Person>() {
	
	    /**
	    * 
	    */
	    private static final long serialVersionUID = 1L;
	
	    @Override
	    public Person call(Row row) throws Exception {
	            Person p = new Person();
	            //p.setId(row.getString(1));
	            //p.setName(row.getString(2));
	            //p.setAge(row.getInt(0));
	
	            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>() {
	    
	    /**
	    * 
	    */
	    private static final long serialVersionUID = 1L;
	
	    @Override
	    public void call(Person t) throws Exception {
	          System.out.println(t);
	    }
	});
	
	sc.stop();
-----------------------------scala----------------------------
	val conf = new SparkConf()
	conf.setMaster("local").setAppName("rddStruct")
	val sc = new SparkContext(conf)
	val sqlContext = new SQLContext(sc)
	val lineRDD = sc.textFile("./sparksql/person.txt")
	val rowRDD = lineRDD.map { x => {
	  val split = x.split(",")
	  RowFactory.create(split(0),split(1),Integer.valueOf(split(2)))
	} }
	 
	val schema = StructType(List(
	  StructField("id",StringType,true),
	  StructField("name",StringType,true),
	  StructField("age",IntegerType,true)
	))
	 
	val df = sqlContext.createDataFrame(rowRDD, schema)
	df.show()
	df.printSchema()
	sc.stop()

2) 动态创建Schema将非json格式的RDD转换成DataFrame（建议使用）

	SparkConf conf = new SparkConf();
	conf.setMaster("local").setAppName("rddStruct");
	JavaSparkContext sc = new JavaSparkContext(conf);
	SQLContext sqlContext = new SQLContext(sc);
	JavaRDD<String> lineRDD = sc.textFile("./sparksql/person.txt");
	/**
	 * 转换成Row类型的RDD
	 */
	JavaRDD<Row> rowRDD = lineRDD.map(new Function<String, Row>() {
	
	    /**
	     * 
	     */
	    private static final long serialVersionUID = 1L;
	
	    @Override
	    public Row call(String s) throws Exception {
	          return RowFactory.create(//这里字段顺序一定要和下边 StructField对应起来
	                String.valueOf(s.split(",")[0]),
	                String.valueOf(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);
	DataFrame df = sqlContext.createDataFrame(rowRDD, schema);
	
	df.show();
	
	
	    JavaRDD<Row> javaRDD = df.javaRDD();
	        javaRDD.foreach(new VoidFunction<Row>() {
	
	            /**
	             *
	             */
	            private static final long serialVersionUID = 1L;
	
	            @Override
	            public void call(Row row) throws Exception {//Row类型的RDD
	                System.out.println(row.getString(0));
	            }
	        })
	
	sc.stop();
------------------------------scala-------------------------------
	val conf = new SparkConf()
	conf.setMaster("local").setAppName("rddStruct")
	val sc = new SparkContext(conf)
	val sqlContext = new SQLContext(sc)
	val lineRDD = sc.textFile("./sparksql/person.txt")
	val rowRDD = lineRDD.map { x => {
	  val split = x.split(",")
	  RowFactory.create(split(0),split(1),Integer.valueOf(split(2)))
	} }
	 
	val schema = StructType(List(
	  StructField("id",StringType,true),
	  StructField("name",StringType,true),
	  StructField("age",IntegerType,true)
	))
	 
	val df = sqlContext.createDataFrame(rowRDD, schema)
	df.show()
	df.printSchema()
	sc.stop()

3. 通过Hive表中数据创建

     Hive与Spark库捆绑为HiveContext，它继承自SQLContext。 使用HiveContext，您可以在HiveMetaStore中创建和查找表，并使用HiveQL在其上写入查询。 没有现有Hive部署的用户仍然可以创建HiveContext。 当未由hive-site.xml配置时，上下文会自动在当前目录中创建名为metastore_db的元数据库和名为warehouse的文件夹。
     参考以下案例使用Hive表完成雇员记录。 所有记录的数据都在名为employee.txt的文本文件中。 这里，我们将首先初始化HiveContext对象。 使用它，我们将创建一个表，使用HiveQL语言将雇员记录数据加载到其中，并对其应用一些查询。

	val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)
	
	//使用HiveQL创建表 
	sqlContext.sql("CREATE TABLE IF NOT EXISTS employee(id INT, name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'")
	//使用HiveQL将数据加载到表中 
	sqlContext.sql("LOAD DATA LOCAL INPATH 'employee.txt' INTO TABLE employee")
	//我们可以在表中执行任何类型的SQL查询。使用以下命令可以使用HiveQL select查询获取所有记录。
	val result = sqlContext.sql("FROM employee SELECT id, name, age")
	
	result.show()

5. 读取parquet文件创建DataFrame

Parquet是一种柱状格式，由许多数据处理系统支持。 具有柱状存储的优点如下 :
  1、列存储限制IO操作。
  2、列式存储可以获取您需要访问的特定列。
  3、列式存储占用更少的空间。
  4、列式存储提供更好的摘要数据，并遵循类型特定的编码。
  Spark SQL提供对读取和写入自动捕获原始数据模式的镶木地板文件的支持。 像JSON数据集一样，parquet文件遵循相同的过程。

注意：

• 可以将DataFrame存储成parquet文件。保存成parquet文件的方式有两种

1) df.write().mode(SaveMode.Overwrite).format("parquet").save("./sparksql/parquet");
2) df.write().mode(SaveMode.Overwrite).parquet("./sparksql/parquet");

• SaveMode指定文件保存时的模式。
     Overwrite：覆盖
     Append：追加
     ErrorIfExists：如果存在就报错
     Ignore：如果存在就忽略

	SparkConf conf = new SparkConf();
	conf.setMaster("local").setAppName("parquet");
	JavaSparkContext sc = new JavaSparkContext(conf);
	SQLContext sqlContext = new SQLContext(sc);
	JavaRDD<String> jsonRDD = sc.textFile("sparksql/json");
	
	DataFrame df = sqlContext.read().json(jsonRDD);
	/**
	 * 将DataFrame保存成parquet文件，SaveMode指定存储文件时的保存模式
	 * 保存成parquet文件有以下两种方式：
	 */
	df.write().mode(SaveMode.Overwrite).format("parquet").save("./sparksql/parquet");
	df.write().mode(SaveMode.Overwrite).parquet("./sparksql/parquet");
	df.show();
	/**
	 * 加载parquet文件成DataFrame    
	 * 加载parquet文件有以下两种方式：    
	 */
	
	DataFrame load = sqlContext.read().format("parquet").load("./sparksql/parquet");
	load = sqlContext.read().parquet("./sparksql/parquet");
	load.show();
	
	sc.stop()
------------------scala------------------------------------------
	
	val conf = new SparkConf()
	 conf.setMaster("local").setAppName("parquet")
	 val sc = new SparkContext(conf)
	 val sqlContext = new SQLContext(sc)
	 val jsonRDD = sc.textFile("sparksql/json")
	 val df = sqlContext.read.json(jsonRDD)
	 df.show()
	  /**
	  * 将DF保存为parquet文件
	  */
	df.write.mode(SaveMode.Overwrite).format("parquet").save("./sparksql/parquet")
	 df.write.mode(SaveMode.Overwrite).parquet("./sparksql/parquet")
	 /**
	  * 读取parquet文件
	  */
	 var result = sqlContext.read.parquet("./sparksql/parquet")
	 result = sqlContext.read.format("parquet").load("./sparksql/parquet")
	 result.show()
	 sc.stop()

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

	SparkConf conf = new SparkConf();
	conf.setMaster("local").setAppName("mysql");
	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://192.168.179.4:3306/spark");
	options.put("driver", "com.mysql.jdbc.Driver");
	options.put("user", "root");
	options.put("password", "123456");
	options.put("dbtable", "person");
	DataFrame person = sqlContext.read().format("jdbc").options(options).load();
	person.show();
	person.registerTempTable("person");
	
	/**
	 * 第二种方式读取MySql数据表加载为DataFrame
	 */
	DataFrameReader reader = sqlContext.read().format("jdbc");
	reader.option("url", "jdbc:mysql://192.168.179.4:3306/spark");
	reader.option("driver", "com.mysql.jdbc.Driver");
	reader.option("user", "root");
	reader.option("password", "123456");
	reader.option("dbtable", "score");
	DataFrame score = reader.load();
	score.show();
	score.registerTempTable("score");
	
	DataFrame result = 
	sqlContext.sql("select person.id,person.name,score.score from person,score where person.name = score.name");
	result.show();
	/**
	 * 将DataFrame结果保存到Mysql中
	 */
	Properties properties = new Properties();
	properties.setProperty("user", "root");
	properties.setProperty("password", "123456");
	result.write().mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.179.4:3306/spark", "result", properties);
	
	sc.stop();

--------------------scala-------------------------------------------------------------
	val conf = new SparkConf()
	conf.setMaster("local").setAppName("mysql")
	val sc = new SparkContext(conf)
	val sqlContext = new SQLContext(sc)
	/**
	 * 第一种方式读取Mysql数据库表创建DF
	 */
	val options = new HashMap[String,String]();
	options.put("url", "jdbc:mysql://192.168.179.4:3306/spark")
	options.put("driver","com.mysql.jdbc.Driver")
	options.put("user","root")
	options.put("password", "123456")
	options.put("dbtable","person")
	val person = sqlContext.read.format("jdbc").options(options).load()
	person.show()
	person.registerTempTable("person")
	/**
	 * 第二种方式读取Mysql数据库表创建DF
	 */
	val reader = sqlContext.read.format("jdbc")
	reader.option("url", "jdbc:mysql://192.168.179.4:3306/spark")
	reader.option("driver","com.mysql.jdbc.Driver")
	reader.option("user","root")
	reader.option("password","123456")
	reader.option("dbtable", "score")
	val score = reader.load()
	score.show()
	score.registerTempTable("score")
	val result = sqlContext.sql("select person.id,person.name,score.score from person,score where person.name = score.name")
	result.show()
	/**
	 * 将数据写入到Mysql表中
	 */
	val properties = new Properties()
	properties.setProperty("user", "root")
	properties.setProperty("password", "123456")
	result.write.mode(SaveMode.Append).jdbc("jdbc:mysql://192.168.179.4:3306/spark", "result", properties)
	 
	sc.stop()

4.1.2 版本1.X操作DataFrame

		// 打印DataFrame中所有的数据（select * from ...），df.show()默认显示前20行数据。
		/**
		 * 显示 DataFrame中的内容，默认显示前20行。如果现实多行要指定多少行show(行数)
		 * 注意：当有多个列时，显示的列先后顺序是按列的ascii码先后显示。
		 */
		df.show();
		// 打印DataFrame的元数据（Schema）
		df.printSchema();
		// 查询某列所有的数据
		df.select("name").show();  
		// 查询某几列所有的数据，并对列进行计算
		df.select(df.col("name"), df.col("age").plus(1)).show();
		// 根据某一列的值进行过滤
		df.filter(df.col("age").gt(18)).show();
		// 根据某一列进行分组，然后进行聚合
		df.groupBy(df.col("age")).count().show();
---------------------------------------------------------------------------------------
		 /**
		  * DataFrame可以转换成RDD
		  */
		 RDD<Row> rdd = df.rdd();
--------------------------------------------------------------------------------------		 
		  /**
		  * 将DataFrame注册成临时的一张表，这张表临时注册到内存中，是逻辑上的表，不会雾化到磁盘
		  */
		 df.registerTempTable("jtable");
	        
		 DataFrame sql = sqlContext.sql("select age,count(1) from jtable group by age");
		 DataFrame sql2 = sqlContext.sql("select * from jtable");
        
 		 sc.stop();

4.2 版本2.X

4.2.1 版本2.X创建DataFrame

在1.X中需要创建一个SQLContext，而在2.X中则变成了先创建一个SparkSession:

val spark = SparkSession
      .builder()
      .appName("Spark SQL basic example")
      .enableHiveSupport()
      //.config("spark.some.config.option", "some-value")
      .getOrCreate()

使用csv文件,spark2.0+之后的版本可用

	//首先初始化一个SparkSession对象
	val spark = org.apache.spark.sql.SparkSession.builder
	        .master("local")
	        .appName("Spark CSV Reader")
	        .getOrCreate;
	
	//然后使用SparkSessions对象加载CSV成为DataFrame
	val df = spark.read
	        .format("com.databricks.spark.csv")
	        .option("header", "true") //reading the headers
	        .option("mode", "DROPMALFORMED")
	        .load("csv/file/path"); //.csv("csv/file/path") //spark 2.0 api
	
	df.show()

Spark API演变

---

致谢

SparkSQL初始和创建DataFrame的几种方式
Spark创建DataFrame的三种方法
Spark-SQL之DataFrame创建
Spark SQL教程
反射模式