SparkSql 02 dataset表创建

写sql之前需要先创建SparkSession对象
基本格式:

// SparkSession对象
SparkSession sparkSession = SparkSession.builder()
        .appName("sql")
        .master("local")
        .getOrCreate();

创建Dataset(表)两种方式

代码示例:

package com.shsxt.spark.java;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

/**
 * Created by BF-Lone Silver Wind
 */
public class SqlTest {
    public static void main(String[] args) {
        // SparkSession对象
        SparkSession sparkSession = SparkSession.builder()
                .appName("sql")
                .master("local")
                .getOrCreate();
        //根据数据创建表
        //Dataset<Row> dataset = sparkSession.read().json("json");
        Dataset<Row> dataset = sparkSession.read().format("json").load("json");

        //打印 元数据
        dataset.printSchema();

        //打印表
        //默认按表字段的ascii 来排序
        dataset.show();
        
        //Spark自带api操作 不推荐使用
        //Dataset<Row> dataset1 = dataset.select("name", "age").where("age>18");
        //Dataset<Row> dataset1 = dataset.select(dataset.col( "name"),dataset.col( "age").plus(10)).where("age>18");

        //dataset1.show();
        
        //先创建临时表
        dataset.registerTempTable("jsonTable");
        //SparkSession对象调用sql方法直接输入sql查询
        Dataset<Row> rowDataset = sparkSession.sql("select name,age,age + 10  from jsonTable where age > 18");

        rowDataset.show();
    }
}

注意:

• 1.读取json格式两种方式
• 2.ds.show默认显示前20行，使用ds.show(行数)显示多行
• 3.ds.javaRDD/(scala ds.rdd) 将DataFrame转换成RDD
• 4.ds.printSchema()显示Dataset中的Schema信息
• 5.dataset自带的API 操作Dataset ，用的少
• 6.想使用sql查询，首先要将Dataset注册成临时表：dd.registerTempTable(“jtable”)，再使用sql,怎么使用sql? sparksession.sql(“sql语句”)
• 7.不能读取嵌套的json文件
• 8.ds加载过来之后将列按照ascii排序了

1. 通过已知json文件创建dataset表:

方式一:

//已知json格式文件读取"json"文件创建dataset
Dataset<Row> dataset = sparkSession.read().json("json");

方式二:

//填写文件格式并读取"json"文件创建dataset
Dataset<Row> dataset = sparkSession.read().format("json").load("json");

2. 通过json格式的RDD创建Dataset

//通过SparkSession对象创建SparkContext上下文对象
SparkContext sc = sparkSession.sparkContext();
//通过SparkContext上下文对象new一个JavaSparkContext对象
JavaSparkContext jsc = new JavaSparkContext(sc);

//json 1
JavaRDD<String> nameRDD = jsc.parallelize(Arrays.asList(
		"{'name':'zhangsan','age':\"18\"}",
		"{\"name\":\"lisi\",\"age\":\"19\"}",
		"{\"name\":\"wangwu\",\"age\":\"20\"}"
		));
//json 2
JavaRDD<String> scoreRDD = jsc.parallelize(Arrays.asList(
		"{\"name\":\"zhangsan\",\"score\":\"100\"}",
		"{\"name\":\"lisi\",\"score\":\"200\"}",
		"{\"name\":\"wangwu\",\"score\":\"300\"}"
		));

//Dataset表name
Dataset<Row> nameds = sparkSession.read().json(nameRDD);
//Dataset表score
Dataset<Row> scoreds = sparkSession.read().json(scoreRDD);

3. 非json格式的RDD创建Dataset

1). 通过反射的方式将非json格式的RDD转换成Dataset

• 自定义类要可序列化
• 自定义类的访问级别是Public
• RDD转成Dataset后会根据映射将字段按Assci码排序
• 将Dataset转换成RDD时获取字段两种方式,一种是ds.getInt(0)下标获取（不推荐使用 存储 修改值任意出错），另一种是ds.getAs(“列名”)获取（推荐使用）

/**
* 注意：
* 1.自定义类必须是可序列化的
* 2.自定义类访问级别必须是Public
* 3.RDD转成Dataset会把自定义类中字段的名称按assci码排序
*/
SparkSession sparkSession = SparkSession
        .builder()
        .appName("reflect")
        .master("local")
        .getOrCreate();

SparkContext sc = sparkSession.sparkContext();

JavaSparkContext jsc = new JavaSparkContext(sc);

JavaRDD<String> lineRDD = jsc.textFile("person.txt");

JavaRDD<Person> personRDD = lineRDD.map(
        new Function<String, Person>() {

    private static final long serialVersionUID = 1L;

    @Override
    public Person call(String line) throws Exception {
        Person p = new Person();

        p.setId(line.split(",")[0]);
        p.setName(line.split(",")[1]);
        p.setAge(Integer.valueOf(line.split(",")[2]));
        return p;
    }
});
/**
* 传入进去Person.class的时候，sqlContext是通过反射的方式创建DataFrame
* 在底层通过反射的方式获得Person的所有field，结合RDD本身，就生成了DataFrame
*/
Dataset<Row> dataFrame = sparkSession.createDataFrame(personRDD, Person.class);
        dataFrame.show();
        dataFrame.printSchema();
        dataFrame.registerTempTable("person");
Dataset sql = sparkSession.sql("select  name,id,age from person where id = 2");
        sql.show();
        
/**
* 将Dataset转成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(x-> System.out.println(x));

sc.stop();

2). 动态创建Schema将非json格式的RDD转换成Dataset

/**
 * 动态构建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> df = sparkSession.createDataFrame(rowRDD, schema);
df.show();
sc.stop();

4. 读取parquet文件创建Dataset

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

//方式1
ds.write().mode(SaveMode.Overwrite)format("parquet").save("parquet_table");

//方式2
ds.write().mode(SaveMode.Overwrite).parquet("parquet_table");

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

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

SparkSession sparkSession = SparkSession
                .builder()
                .appName("mysql")
                .master("local")
                .getOrCreate();/**
 * 第一种方式读取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");
Dataset<Row> person = sparkSession.read().format("jdbc").options(options).load(); 
person.show();
person.createOrReplaceTempView("person"); 
/**
 * 第二种方式读取MySql数据表加载为Dataset
 */
DataFrameReader reader = sparkSession.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");
Dataset<Row> score = reader.load();
score.show();
score.createOrReplaceTempView("score"); 

Dataset result = 
sparksession.sql("select person.id,person.name,score.score from person,score where person.name = score.name");
result.show();
/**
 * 将Dataset结果保存到Mysql中
 */
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "root");
result.write().mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.179.4:3306/spark", "result", properties);

sc.stop();

6. 读取Hive中的数据加载成Dataset

SparkSession sparkSession = SparkSession
                .builder()
                .master("local")
                .appName("hvie")
                //开启hive的支持，接下来就可以操作hive表了
                // 前提需要是需要开启hive metastore 服务
                .enableHiveSupport()
                .getOrCreate();

   sparkSession.sql("USE spark");
   sparkSession.sql("DROP TABLE IF EXISTS student_infos");
//在hive中创建student_infos表
   sparkSession.sql("CREATE TABLE IF NOT EXISTS student_infos (name STRING,age INT) row format delimited fields terminated by '\t' ");
        sparkSession.sql("load data local inpath '/root/student_infos' into table student_infos");
        //注意：此种方式，程序需要能读取到数据（如/root/student_infos），同时也要能读取到 metastore服务的配置信息。

sparkSession.sql("DROP TABLE IF EXISTS student_scores");
sparkSession.sql("CREATE TABLE IF NOT EXISTS student_scores (name STRING, score INT) row format delimited fields terminated by '\t'");
        sparkSession.sql("LOAD DATA "
				+ "LOCAL INPATH '/root/student_scores'"
				+ "INTO TABLE student_scores");



//		Dataset<Row> df = hiveContext.table("student_infos");//读取Hive表加载Dataset方式
        /**
         * 查询表生成Dataset
         */
		Dataset<Row> goodStudentsDF = sparkSession.sql("SELECT si.name, si.age, ss.score "
				+ "FROM student_infos si "
				+ "JOIN student_scores ss "
				+ "ON si.name=ss.name "
				+ "WHERE ss.score>=80");

		goodStudentsDF.registerTempTable("goodstudent");
        Dataset<Row>  result = sparkSession.sql("select * from goodstudent");
		result.show();
		
		/**
		 * 将结果保存到hive表 good_student_infos
		 */
    sparkSession.sql("DROP TABLE IF EXISTS good_student_infos");
goodStudentsDF.write().mode(SaveMode.Overwrite).saveAsTable("good_student_infos");

    sparkSession.stop();