spark-sql建表语句限制_SparkSql的架构和DataSet

先来说下SparkSql中的DataSet。可能写的不大好，希望对你有帮助。

一、SparkSQL

SparkSQL完全脱离了Hive的限制。 SparkSQL支持查询原生的RDD。

能够在scala中写SQL语句。支持简单的SQL语法检查，能够在Scala中写Hive语句访问Hive数据，并将结果取回作为RDD使用。满足Spark的一栈式解决大数据处理的需求。

二、SparkSql底层架构流程

1、首先拿到sql后解析一批未被解决的逻辑计划，再经过分析得到分析后的逻辑计划

2、再经过一批优化规则转换成一批最佳优化的逻辑

3、再经过sparkplanner的策略转化成一批物理计划，随后经过消费模型准换成一个个的spark任务执行。

三、 Dataset与DataFrame

Dataset也是一个分布式数据容器，简单来说是类似二维表，Dataset里头存有schema数据结构信息和原生数据，Dataset的底层封装的是RDD，当RDD的泛型是Row类型的时候，我们也可以称它为DataFrame。即Dataset<Row> = DataFrame。

四、创建Dataset的几种方式

1、读取json格式的文件创建Dataset

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

3、通过非json格式的RDD创建Dataset（反射，动态创建）

4、读取parquet文件创建Dataset

5、读取JDBC中的数据创建Dataset

6、读取hive中的数据加载成Dataset

以下代码中的注释注意关注

1、读取json格式的文件创建Dataset

/**
 * 通过读json文件来创建Dataset
 */
public class JsonFile {
    public static void main(String[] args){

        SparkSession sparkSession= SparkSession.builder()
                .master("local").appName("sqltest").getOrCreate();

        Dataset<Row> load = sparkSession.read().format("json").load("data/json");
//        Dataset<Row> json = sparkSession.read().json("data/json");  //两种方式都可以

        //打印两种信息，schema信息和原始数据
        //字段信息按字典排序也就是按ascii排序
        load.printSchema();

        //原始数据默认打印20行
        load.show();

//        JavaRDD<Row> rowJavaRDD = load.javaRDD();
        //自带的原生API，不方便，一般不用
//        load.select(load.col("name")).show();
//        load.select("age").show();
//        load.select(load.col("age")).where(load.col("age").gt(20)).show();  //年龄大于20的

        //下面的table是哪个，所以要先注册表，会将dataset注册成一个内存临时表，这个临时表就代表上面的dataset表
        //是逻辑上的表，不会雾化到磁盘
//        load.registerTempTable("table");    //这两种方式都可以
        load.createOrReplaceTempView("table");  //最好用这个方法，记住这个就行
        //查询sql，然后结果，查看结果
        Dataset<Row> result = sparkSession.sql("select * from table where age > 20");

        result.show();

        sparkSession.stop();
    }
}

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

SparkSession sparkSession = SparkSession
                .builder()
                .appName("jsonrdd")
                .master("local")
                .getOrCreate(); 
SparkContext sc = sparkSession.sparkContext();
JavaSparkContext jsc = new JavaSparkContext(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"}"
));

Dataset<Row> nameds = sparkSession.read().json(nameRDD);
Dataset<Row> scoreds = sparkSession.read().json(scoreRDD); 
nameds.createOrReplaceTempView("nameTable");
scoreds.createOrReplaceTempView("scoreTable"); 
Dataset<Row> result =
                sparkSession.sql("select 
nameTable.name,nameTable.age,scoreTable.score "
							+ "from nameTable join scoreTable "
							+ "on nameTable.name = scoreTable.name"); result.show();
sparkSession.stop();

3、通过非json格式的RDD创建Dataset（反射，动态创建）

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

/**
* 注意：
* 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("data/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

SparkSession sparkSession = SparkSession
                .builder()
                .appName("schema")
                .master("local")
                .getOrCreate();

SparkContext sc = sparkSession.sparkContext();
JavaSparkContext jsc = new JavaSparkContext(sc);

JavaRDD<String> lineRDD = jsc.textFile("data/person.txt");
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(
                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);
Dataset<Row> df = sparkSession.createDataFrame(rowRDD, schema);
df.show();
sc.stop();

4、读取parquet文件创建Dataset

SparkSession sparkSession = SparkSession
                .builder()
                .appName("parquet")
                .master("local")
                .getOrCreate();

 Dataset<Row>  df = sparkSession.read().json("data/json");
/**
 * 将Dataset保存成parquet文件，SaveMode指定存储文件时的保存模式
 * 保存成parquet文件有以下两种方式：
 */
df.write().mode(SaveMode.Overwrite).format("parquet").save("./data/parquet");
df.write().mode(SaveMode.Overwrite).parquet("./data/parquet");
df.show();
/**
 * 加载parquet文件成DataFrame	
 * 加载parquet文件有以下两种方式：	
 */

Dataset load = sparksession.read().format("parquet").load("./data /parquet");

load = sparksession.read().parquet("./data /parquet");
load.show();

sc.stop();

5、读取JDBC中的数据创建Dataset1. (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();

五、储存Dataset

1.将DataSet存储为parquet文件。

2.将DataSet存储到JDBC数据库。

3.将DataSet存储到Hive表。