Spark SQL 函数操作

Spark 内置函数

使用Spark SQL中的内置函数对数据进行分析，Spark SQL API不同的是，DataFrame中的内置函数操作的结果是返回一个Column对象，而DataFrame天生就是”A distributed collection of data organized into named columns.”,这就为数据的复杂分析建立了坚实的基础并提供了极大的方便性，例如说，我们在操作DataFrame的方法中可以随时调用内置函数进行业务需要的处理，这之于我们构建附件的业务逻辑而言是可以极大的减少不必须的时间消耗（基于上就是实际模型的映射），让我们聚焦在数据分析上，这对于提高工程师的生产力而言是非常有价值的Spark 1.5.x开始提供了大量的内置函数，
还有max、mean、min、sum、avg、explode、size、sort_array、day、to_date、abs、acros、asin、atan
总体上而言内置函数包含了五大基本类型：

• 聚合函数，例如countDistinct、sumDistinct等；
• 集合函数，例如sort_array、explode等
• 日期、时间函数，例如hour、quarter、next_day
• 数学函数，例如asin、atan、sqrt、tan、round等；
• 开窗函数，例如rowNumber等
• 字符串函数，concat、format_number、rexexp_extract
• 其它函数，isNaN、sha、randn、callUDF

Hive 下的 单行单词统计

select t.wd ,count(t.wd) as count from (select explode(split(line," ")) as wd from word) t group by t.wd;

在编写程序代码的时候如果调用函数那么需要注意的是要导入functions

import org.apache.spark.sql.types.{DataTypes, StructType}import org.apache.spark.{SparkConf, SparkContext}import org.apache.spark.sql.{Row, SQLContext, functions}object SparkSQLFunctionOps {  def main(args: Array[String]): Unit = {    val conf = new SparkConf().setAppName("SparkSQLFunctionOps").setMaster("local");    val sc = new SparkContext(conf)    val sqlContext = new SQLContext(sc)    val linesRDD = sc.textFile("E:/test/scala/sql-rdd-source.txt")    val rowRDD = linesRDD.map(line => {      val splits = line.split(",")      Row(splits(0).trim.toInt,splits(1).trim,splits(2).trim.toInt,splits(3).trim.toInt)    })    val structType = StructType(Array(      DataTypes.createStructField("id",DataTypes.IntegerType,true),      DataTypes.createStructField("name",DataTypes.StringType,true),      DataTypes.createStructField("age",DataTypes.IntegerType,true),      DataTypes.createStructField("height",DataTypes.IntegerType,true)    ))    val df = sqlContext.createDataFrame(rowRDD,structType)    df.registerTempTable("person")    df.show()    /**      * 接下来对df中的数据进行查询      * 第一个查询年龄的最大值,平均值      * height的总身高      * */    sqlContext.sql("select avg(age) from person").show()    sqlContext.sql("select max(age) from person").show()    sqlContext.sql("select sum(height) from person").show()    sc.stop()  }}

Java 版本

public class SparkSQLFunctionJava {public static void main(String[] args) {    SparkConf conf = new SparkConf( ).setAppName(SparkSQLFunctionJava.class.getSimpleName()).setMaster("local");    JavaSparkContext sc = new JavaSparkContext(conf);    SQLContext sqlContext = new SQLContext(sc);   JavaRDD<String> linesRDD = sc.textFile("E:/test/scala/sql-rdd-source.txt");    JavaRDD<Row> rowRDD = linesRDD.map(new Function<String, Row>() {        @Override        public Row call(String s) throws Exception {            String splits[] = s.split(",");            return  RowFactory.create(Integer.valueOf(splits[0].trim()),splits[1].trim(),Integer.valueOf(splits[2].trim()),Integer.valueOf(splits[3].trim()));        }    });    StructField structFields[] = new StructField[4];    structFields[0] = DataTypes.createStructField("id",DataTypes.IntegerType,true);    structFields[1] = DataTypes.createStructField("name",DataTypes.StringType,true);    structFields[2] = DataTypes.createStructField("age",DataTypes.IntegerType,true);    structFields[3] = DataTypes.createStructField("height",DataTypes.IntegerType,true);    StructType structType = new StructType(structFields);    DataFrame dataFrame = sqlContext.createDataFrame(rowRDD, structType);    dataFrame.show();    dataFrame.registerTempTable("person");    /**     * 接下来对df中的数据进行查询     * 第一个查询年龄的最大值,平均值     * height的总身高     * */    sqlContext.sql("select avg(age) from person").show();    sqlContext.sql("select max(age) from person").show();    sqlContext.sql("select sum(height) from person").show();    sc.close();}}

修改Spark运行日志级别

cp log4j.properties.template log4j.properties
vim conf/log4j.properties 将INFO 修改为ERROR级别
需要重启Spark集群，使其生效

HIVE 设置列结构显示

set hive.cli.print.header=true;

Spark SQL 开窗函数

1、Spark 1.5.x版本以后，在Spark SQL和DataFrame中引入了开窗函数，比如最经典的就是我们的row_number()，可以让我们实现分组取topn的逻辑。

2、做一个案例进行topn的取值（利用Spark的开窗函数），不知道是否还有印象，我们之前在最早的时候，做过topn的计算，当时是非常麻烦的。但是现在用了Spark SQL之后，非常方便。

/**  * Spark SQL 开窗函数之 row_number  * */object SparkSQLOpenWindowFunction {  def main(args: Array[String]): Unit = {    val conf = new SparkConf().setAppName("SparkSQLOpenWindowFunction").setMaster("local")    val sc = new SparkContext(conf)    val sqlContext = new HiveContext(sc)    val linesRDD = sc.textFile("E:/test/scala/topn.txt")    val rowRDD = linesRDD.map(line =>{      val splits = line.split(" ")      Row(splits(0).trim,splits(1).trim.toInt)    })    val structType = DataTypes.createStructType(Array(      DataTypes.createStructField("class",DataTypes.StringType,true),      DataTypes.createStructField("score",DataTypes.IntegerType,true)    ))    val df = sqlContext.createDataFrame(rowRDD,structType)    df.registerTempTable("stu_score")    /**      * 查询操作      * 先按照class 进行分组,然后对每个class分组中的数据求出TOP3      * */     val topNDF = sqlContext.sql("select temp.* from (select *, row_number() over(partition by class order by score desc) rank from stu_score ) temp where temp.rank < 4")     topNDF.show()    sc.stop()  }}

Java版本

public class SparkSQLOpenWindowFunctionJava {public static void main(String[] args) {    SparkConf conf = new SparkConf().setAppName(SparkSQLOpenWindowFunctionJava.class.getSimpleName()).setMaster("local");    JavaSparkContext sc = new JavaSparkContext(conf);    HiveContext sqlContext = new HiveContext(sc);    JavaRDD<String> linesRDD = sc.textFile("E:/test/scala/topn.txt");    JavaRDD<Row> rowRDD = linesRDD.map(new Function<String, Row>() {        @Override        public Row call(String s) throws Exception {            return RowFactory.create(s.split(" ")[0].trim(),Integer.valueOf(s.split(" ")[1].trim()));        }    });    StructField structFields[] = new StructField[2];    structFields[0] = DataTypes.createStructField("class",DataTypes.StringType,true);    structFields[1] = DataTypes.createStructField("score",DataTypes.IntegerType,true);    StructType structType = new StructType(structFields);    DataFrame df = sqlContext.createDataFrame(rowRDD, structType);    df.registerTempTable("stu_score");    /**     * 查询操作     * 先按照class 进行分组,然后对每个class分组中的数据求出TOP3     * */    DataFrame dataFrame = null;    dataFrame = sqlContext.sql("select temp.* from (select *, row_number() over(partition by class order by score desc) rank from stu_score ) temp where temp.rank < 4");    dataFrame.show();    sc.close();}}

在spark-sql 下运行，

• 先在hive 中，建一张表，create table topn (class string,score int) row format delimited fields terminated by ’ ‘;
• 然后把数据导入表中， load data local inpath ‘/opt/data/spark/topn.txt’
• 然后就可以利用开窗函数进行分组排序了，select temp.x from (select *,row_number() over(partition class order by score desc) rank from topn) temp where temp.rank < 4;

UDF自定义函数

1、UDF：User Defined Function。用户自定义函数。
我们通常所说的UDF自定义函数，就是一对一的关系： 一个输入参数和一个输出参数
创建UDF的步骤：
1、先创建一个自定义的函数func
2、使用sqlContext.udf().register(“起个名字”, func _)
3、在我们的sql中直接使用就行了

---

object SparkSQLUDFOps {def main(args: Array[String]): Unit = {    val conf = new SparkConf().setAppName("SparkSQLUDFOps").setMaster("local")    val sc = new SparkContext(conf)    val sqlContext = new SQLContext(sc)    val linesRDD = sc.textFile("E:/test/scala/sql-rdd-source.txt")    val rowRDD = linesRDD.map(line => {        val splits = line.split(", ")        Row(splits(0).trim.toInt, splits(1).trim, splits(2).trim.toInt, splits(3).trim.toInt)    })    val structType = StructType(Array(        DataTypes.createStructField("id", DataTypes.IntegerType, true),        DataTypes.createStructField("name", DataTypes.StringType, true),        DataTypes.createStructField("age", DataTypes.IntegerType, true),        DataTypes.createStructField("height", DataTypes.IntegerType, true)    ))    val df = sqlContext.createDataFrame(rowRDD, structType)    df.registerTempTable("person")    //2、注册自定义的UDF    /**      * 这是两种注册的方式      */    sqlContext.udf.register("myLen", myLen _)    sqlContext.udf.register("len", (str:String, len:Int) => str.length > len)    //3、使用起来    sqlContext.sql("select id, name, myLen(name) as len from person where len(name, 5)").show()    sc.stop()}//1、创建一个自定义的函数，用于求出字符串的长度def myLen(str: String) = str.length

}