一、SparkSQL介绍
SparkSQL 发展过程：Hive -> Shark->SparkSQL，SparkSQL产生的根本原因是其完全脱离了Hive的限制。
SparkSQL支持查询原生的RDD。 RDD是Spark平台的核心概念，是Spark能够高效的处理大数据的各种场景的基础。能够在Scala中写SQL语句。支持简单的SQL语法检查，能够在Scala中写Hive语句访问Hive数据，并将结果取回作为RDD使用。
- Spark on Hive（SparkSQL）： Hive只作为储存角色，Spark负责sql解析优化，执行。
Hive on Spark（Shark）：Hive即作为存储又负责sql的解析优化，Spark负责执行。
二、DataFrame/DataSet
DataFrame也是一个分布式数据容器。与RDD类似，然而DataFrame更像传统数据库的二维表格，除了数据以外，还掌握数据的结构信息，即schema。同时，与Hive类似，DataFrame也支持嵌套数据类型（struct、array和map）。从API易用性的角度上 看， DataFrame API提供的是一套高层的关系操作，比函数式的RDD API要更加友好，门槛更低。
DataFrame的底层封装的是RDD，只不过RDD的泛型是Row类型。
SparkSQL的数据源：
SparkSQL的数据源可以是JSON类型的字符串，JDBC,Parquent,Hive，HDFS等。
SparkSQL底层架构：
首先拿到sql后解析一批未被解决的逻辑计划，再经过分析得到分析后的逻辑计划，再经过一批优化规则转换成一批最佳优化的逻辑计划，再经过SparkPlanner的策略转化成一批物理计划，随后经过消费模型转换成一个个的Spark任务执行。
谓词下推（predicate Pushdown）：SparkSQL优化job，使用到了谓词下推
三、创建DataFrame的几种方式
3.1、读取json格式的文件
package com.lxk.sparksql.dataframe;
import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SQLContext;
/**
* 读取json格式的文件创建DataFrame
*
* 注意 ：json文件中不能嵌套json格式的内容
*
* 1.读取json格式两种方式 2.df.show默认显示前20行，使用df.show(行数)显示多行 3.df.javaRDD/(scala
* df.rdd) 将DataFrame转换成RDD 4.df.printSchema()显示DataFrame中的Schema信息
* 5.dataFram自带的API 操作DataFrame ，用的少
* 6.想使用sql查询，首先要将DataFrame注册成临时表：df.registerTempTable("jtable")，再使用sql,怎么使用sql?
* sqlContext.sql("sql语句") 7.不能读取嵌套的json文件 8.df加载过来之后将列按照ascii排序了
*
*/
public class CreateDFFromJsonFile {
public static void main(String[] args) {
SparkConf conf = new SparkConf();
conf.setMaster("local").setAppName("jsonfile");
SparkContext sc = new SparkContext(conf);
// 创建sqlContext
SQLContext sqlContext = new SQLContext(sc);
/**
* DataFrame的底层是一个一个的RDD RDD的泛型是Row类型。 以下两种方式都可以读取json格式的文件
*/
// DataFrame df =
// sqlContext.read().format("json").load("./sparksql/json");
DataFrame df = sqlContext.read().json("sparksql/json");
df.show();
/**
* 显示 DataFrame中的内容，默认显示前20行。如果现实多行要指定多少行show(行数)
* 注意：当有多个列时，显示的列先后顺序是按列的ascii码先后显示。
*/
// df.show(100);
/**
* DataFrame转换成RDD
*/
JavaRDD<Row> javaRDD = df.javaRDD();
javaRDD.foreach(row ->System.out.println(row));
/**
* 树形的形式显示schema信息
*/
//df.printSchema();
/**
* dataFram自带的API 操作DataFrame
*/
// df.select("name").show(); // select name from table
// df.select(df.col("name"),df.col("age").plus(10).alias("addage")).show(); // select name ,age+10 as addage from table
// df.select(df.col("name"),df.col("age")).where(df.col("age").gt(19)).show(); // select name ,age from table where age>19
// df.groupBy(df.col("age")).count().show(); // select age,count(*) from table group by age
/**
* 将DataFrame注册成临时的一张表，这张表相当于临时注册到内存中，是逻辑上的表，不会雾化到磁盘
*/
df.registerTempTable("jtable");
DataFrame sql = sqlContext.sql("select age,count(*) as gg from jtable group by age");
sql.show();
DataFrame sql2 = sqlContext.sql("select name,age from jtable");
sql2.show();
sc.stop();
}
}结果：
[20,zhangsan]
[null,lxk]
[18,wangwu]
[18,wangwu]
+----+---+
| age| gg|
+----+---+
|null| 1|
| 18| 2|
| 20| 1|
+----+---+
+--------+----+
| name| age|
+--------+----+
|zhangsan| 20|
| lxk|null|
| wangwu| 18|
| wangwu| 18|
+--------+----+3.2、读取json格式的RDD/DataSet
package com.lxk.sparksql.dataframe;
import java.util.Arrays;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.SQLContext;
/**
* 读取json格式的RDD创建DF
*/
public class CreateDFFromJsonRDD {
public static void main(String[] args) {
SparkConf conf = new SparkConf();
conf.setMaster("local").setAppName("jsonRDD");
JavaSparkContext sc = new JavaSparkContext(conf);
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);
namedf.show();
namedf.printSchema();
DataFrame scoredf = sqlContext.read().json(scoreRDD);
scoredf.show();
// 方式一，daframe原生api使用,SELECT t1.name,t1.age,t2.score from t1, t2 where
// t1.name = t2.name
/*namedf.join(scoredf, namedf.col("name").$eq$eq$eq(scoredf.col("name")))
.select(namedf.col("name"), namedf.col("age"), scoredf.col("score")).show();*/
// 方式二，注册成临时表使用
namedf.registerTempTable("name");
scoredf.registerTempTable("score");
/**
* 如果自己写的sql查询得到的DataFrame结果中的列会按照 查询的字段顺序返回
*/
DataFrame result = sqlContext
.sql("select name.name,name.age,score.score " + "from name join score " + "on name.name = score.name");
result.show();
sc.stop();
}
}结果：
+---+--------+
|age| name|
+---+--------+
| 18|zhangsan|
| 19| lisi|
| 20| wangwu|
+---+--------+
root
|-- age: string (nullable = true)
|-- name: string (nullable = true)
+--------+-----+
| name|score|
+--------+-----+
|zhangsan| 100|
| lisi| 200|
| wangwu| 300|
+--------+-----+
+--------+---+-----+
| name|age|score|
+--------+---+-----+
|zhangsan| 18| 100|
| lisi| 19| 200|
| wangwu| 20| 300|
+--------+---+-----+3.3.1、读取RDD创建DataFrame（CreateDFFromRDDWithReflect ）
package com.lxk.sparksql.dataframe;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.VoidFunction;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SQLContext;
/**
* 通过反射的方式将非json格式的RDD转换成DataFrame 注意：这种方式不推荐使用
*/
public class CreateDFFromRDDWithReflect {
public static void main(String[] args) {
/**
* 注意： 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>() {
@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
*/
DataFrame df = sqlContext.createDataFrame(personRDD, Person.class);
df.show();
df.printSchema();
df.registerTempTable("person");
DataFrame sql = sqlContext.sql("select name,id,age from person where id = 2");
sql.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(0));
// p.setName(row.getString(1));
// p.setAge(row.getInt(2));
p.setId(row.getAs("id") + "");
p.setName((String) row.getAs("name"));
p.setAge((Integer) row.getAs("age"));
return p;
}
});
map.foreach(person -> System.out.println(person));
sc.stop();
}
}结果：
+---+---+--------+
|age| id| name|
+---+---+--------+
| 18| 1|zhangsan|
| 19| 2| lxk|
| 20| 3| wangwu|
+---+---+--------+
root
|-- age: integer (nullable = true)
|-- id: string (nullable = true)
|-- name: string (nullable = true)
+----+---+---+
|name| id|age|
+----+---+---+
| lxk| 2| 19|
+----+---+---+
Person [id=1, name=zhangsan, age=18]
Person [id=2, name=lxk, age=19]
Person [id=3, name=wangwu, age=20]3.3.2、读取RDD创建DataFrame（CreateDFFromRDDWithStruct）
package com.lxk.sparksql.dataframe;
import java.util.Arrays;
import java.util.List;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.VoidFunction;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.RowFactory;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;
/**
* 动态创建Schema将非json格式RDD转换成DataFrame
* @author root
*
*/
public class CreateDFFromRDDWithStruct {
public static void main(String[] args) {
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>() {
@Override
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);
DataFrame df = sqlContext.createDataFrame(rowRDD, schema);
df.show();
// JavaRDD<Row> javaRDD = df.javaRDD();
// javaRDD.foreach(new VoidFunction<Row>() {
// @Override
// public void call(Row row) throws Exception {
// System.out.println(row.getString(0));
// }
// });
sc.stop();
}
}结果：
+---+--------+---+
| id| name|age|
+---+--------+---+
| 1|zhangsan| 18|
| 2| lxk| 19|
| 3| wangwu| 20|
+---+--------+---+3.4、读取parquet格式数据加载DataFrame
package com.lxk.sparksql.dataframe;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.SaveMode;
public class CreateDFFromParquet {
public static void main(String[] args) {
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);
// sqlContext.read().format("json").load("./spark/json");
// df.show();
/**
* 将DataFrame保存成parquet文件，
* SaveMode指定存储文件时的保存模式:
* Overwrite：覆盖
* Append:追加
* ErrorIfExists:如果存在就报错
* Ignore:如果存在就忽略
* 保存成parquet文件有以下两种方式：
*/
df.write().mode(SaveMode.Overwrite).format("parquet").save("./sparksql/parquet");
//df.write().mode(SaveMode.Ignore).parquet("./sparksql/parquet");
/**
* 加载parquet文件成DataFrame
* 加载parquet文件有以下两种方式：
*/
// DataFrame load = sqlContext.read().format("parquet").load("./sparksql/parquet");
DataFrame load = sqlContext.read().parquet("./sparksql/parquet");
load.show();
sc.stop();
}
}结果：
19/10/13 12:18:54 INFO CatalystWriteSupport: Initialized Parquet WriteSupport with Catalyst schema:
{
"type" : "struct",
"fields" : [ {
"name" : "age",
"type" : "long",
"nullable" : true,
"metadata" : { }
}, {
"name" : "name",
"type" : "string",
"nullable" : true,
"metadata" : { }
} ]
}
and corresponding Parquet message type:
message spark_schema {
optional int64 age;
optional binary name (UTF8);
}
+----+--------+
| age| name|
+----+--------+
| 20|zhangsan|
|null| lxk|
| 18| wangwu|
| 18| wangwu|
+----+--------+
3.5、读取Mysql中的数据加载成DataFrame
package com.lxk.sparksql.dataframe;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.DataFrameReader;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.SaveMode;
public class CreateDFFromMysql {
public static void main(String[] args) {
SparkConf conf = new SparkConf();
conf.setMaster("local").setAppName("mysql");
/**
* 配置join或者聚合操作shuffle数据时分区的数量
*/
conf.set("spark.sql.shuffle.partitions", "1");
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.18.104:3306/spark");
options.put("driver", "com.mysql.jdbc.Driver");
options.put("user", "root");
options.put("password", "123");
options.put("dbtable", "person");
DataFrame person = sqlContext.read().format("jdbc").options(options).load();
person.show();
person.registerTempTable("person1");
/**
* 第二种方式读取MySql数据表加载为DataFrame
*/
DataFrameReader reader = sqlContext.read().format("jdbc");
reader.option("url", "jdbc:mysql://192.168.18.104:3306/spark");
reader.option("driver", "com.mysql.jdbc.Driver");
reader.option("user", "root");
reader.option("password", "123");
reader.option("dbtable", "score");
DataFrame score = reader.load();
score.show();
score.registerTempTable("score1");
DataFrame result =
sqlContext.sql("select person1.id,person1.name,person1.age,score1.score "
+ "from person1,score1 "
+ "where person1.name = score1.name");
result.show();
/**
* 将DataFrame结果保存到Mysql中
*/
Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "123");
/**
* SaveMode:
* Overwrite：覆盖
* Append:追加
* ErrorIfExists:如果存在就报错
* Ignore:如果存在就忽略
*
*/
result.write().mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.18.104:3306/spark?useUnicode=true&characterEncoding=utf8", "person_score", properties);
System.out.println("----Finish----");
sc.stop();
}
}结果：
+---+----+---+
| id|name|age|
+---+----+---+
| 1| 张三| 14|
| 2| 李四| 27|
| 3| 王五| 20|
| 4| 麻六| 28|
+---+----+---+
+---+----+-----+
| id|name|score|
+---+----+-----+
| 1| 张三| 60|
| 2| 李四| 80|
| 3| 王五| 100|
| 4| 麻六| 95|
+---+----+-----+
+---+----+---+-----+
| id|name|age|score|
+---+----+---+-----+
| 1| 张三| 14| 60|
| 2| 李四| 27| 80|
| 3| 王五| 20| 100|
| 4| 麻六| 28| 95|
+---+----+---+-----+
3.6、读取Hive中的数据加载DataFrame
package com.lxk.sparksql.dataframe;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SaveMode;
import org.apache.spark.sql.hive.HiveContext;
/**
* 如果读取hive中数据，要使用HiveContext
* HiveContext.sql(sql)可以操作hive表，还可以操作虚拟的表
*
*/
public class CreateDFFromHive {
public static void main(String[] args) {
SparkConf conf = new SparkConf();
conf.setAppName("hive");
JavaSparkContext sc = new JavaSparkContext(conf);
//HiveContext是SQLContext的子类。
HiveContext hiveContext = new HiveContext(sc);
hiveContext.sql("USE spark");
hiveContext.sql("DROP TABLE IF EXISTS student_infos");
//在hive中创建student_infos表
hiveContext.sql("CREATE TABLE IF NOT EXISTS student_infos (name STRING,age INT) row format delimited fields terminated by '\t' ");
hiveContext.sql("load data local inpath '/root/test/student_infos' into table student_infos");
hiveContext.sql("DROP TABLE IF EXISTS student_scores");
hiveContext.sql("CREATE TABLE IF NOT EXISTS student_scores (name STRING, score INT) row format delimited fields terminated by '\t'");
hiveContext.sql("LOAD DATA "
+ "LOCAL INPATH '/root/test/student_scores'"
+ "INTO TABLE student_scores");
/**
* 查询表生成DataFrame
*/
// DataFrame df = hiveContext.table("student_infos");//第二种读取Hive表加载DF方式
DataFrame goodStudentsDF = hiveContext.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");
DataFrame result = hiveContext.sql("select * from goodstudent");
result.show();
/**
* 将结果保存到hive表 good_student_infos
*/
hiveContext.sql("DROP TABLE IF EXISTS good_student_infos");
goodStudentsDF.write().mode(SaveMode.Overwrite).saveAsTable("good_student_infos");
DataFrame table = hiveContext.table("good_student_infos");
Row[] goodStudentRows = table.collect();
for(Row goodStudentRow : goodStudentRows) {
System.out.println(goodStudentRow);
}
sc.stop();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
