SparkSQL函数操作

已于 2023-12-29 21:50:06 修改
阅读量7.2k 收藏 16
点赞数 33
文章标签： 系统架构 mapreduce eclipse 大数据 scala spark hive
于 2023-12-21 08:28:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HYSliuliuliu/article/details/134983613
版权

1.5 SparkSQL函数操作

1.5.1 函数的定义
SQL中函数，其实说白了就是各大编程语言中的函数，或者方法，就是对某一特定功能的封装，通过它可以完成较为复杂的统计。这里的函数的学习，就基于Hive中的函数来学习。
1.5.2 函数的分类

功能分类

数值

  1. round(x, [d]):对x保留d位小数，同时会四舍五入.

spark-sql> select round(0.33,1);
+-----------------+
| round(0.33, 1)  |
+-----------------+
| 0.3             |
+-----------------+

  1. floor(x):获取不大于x的最大整数.

spark-sql> select floor(-3.5);
+--------------+
| FLOOR(-3.5)  |
+--------------+
| -4           |
+--------------+

  1. ceil(x):获取不小于x的最小整数.

spark-sql> select ceil(-3.1);
+-------------+
| CEIL(-3.1)  |
+-------------+
| -3          |
+-------------+

  1. rand()：获取0到1之间的随机数，获取表中随机的两条记录

spark-sql> select * from test order by rand() limit 2;
+------+--------+
| tid  | tname  |
+------+--------+
| 1    | abc    |
+------+--------+
or
spark-sql> select *, rand() rand from test order by rand limit 2;
+------+--------+---------------------+
| tid  | tname  |        rand         |
+------+--------+---------------------+
| 1    | abc    | 0.5080723866817868  |
+------+--------+---------------------+

  1. 数学

取绝对值

spark-sql> select abs(-1);
+----------+
| abs(-1)  |
+----------+
| 1        |
+----------+

取幂次方

spark-sql> select pow(2,3);
+--------------------------------------------+
| pow(CAST(2 AS DOUBLE), CAST(3 AS DOUBLE))  |
+--------------------------------------------+
| 8.0                                        |
+--------------------------------------------+

平方根

spark-sql> select sqrt(16);
+---------------------------+
| SQRT(CAST(16 AS DOUBLE))  |
+---------------------------+
| 4.0                       |
+---------------------------+

条件

spark-sql> select if(1=2,100,200);
+--------------------------+
| (IF((1 = 2), 100, 200))  |
+--------------------------+
| 200                      |
+--------------------------+
1 row selected (0.06 seconds)
spark-sql> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select 
(case 1 
when 1 then "1 = 1" 
when 2 then "2 = 2" 
else "no" 
end);
+----------------------------------------------------+
| CASE WHEN (100 = 50) THEN tom WHEN (100 = 100) THEN mary ELSE tim END |
+----------------------------------------------------+
| mary                                               |
+----------------------------------------------------+

日期

  1. UNIX时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
举例：
spark-sql> select from_unixtime(1323308943,'yyyyMMdd');
+----------------------------------------------------+
| from_unixtime(CAST(1323308943 AS BIGINT), yyyyMMdd) |
+----------------------------------------------------+
| 20111208                                           |
+----------------------------------------------------+

  1. 获取当前UNIX时间戳函数: unix_timestamp

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
举例：
spark-sql> select unix_timestamp();
+----------------------------------------------------+
| unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss) |
+----------------------------------------------------+
| 1626902929                                         |
+----------------------------------------------------+

  1. 日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
spark-sql> select unix_timestamp('2011-12-07 13:01:03');
+----------------------------------------------------+
| unix_timestamp(2011-12-07 13:01:03, yyyy-MM-dd HH:mm:ss) |
+----------------------------------------------------+
| 1323234063                                         |
+----------------------------------------------------+

  1. 指定格式日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
举例：
spark-sql> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
+----------------------------------------------------+
| unix_timestamp(20111207 13:01:03, yyyyMMdd HH:mm:ss) |
+----------------------------------------------------+
| 1323234063                                         |
+----------------------------------------------------+

  1. 日期时间转日期函数: to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：
spark-sql> select to_date('2011-12-08 10:03:01');
+-------------------------------+
| to_date(2011-12-08 10:03:01)  |
+-------------------------------+
| 2011-12-08                    |
+-------------------------------+

  1. 日期转年函数: year

语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
spark-sql> select year('2011-12-08 10:03:01');
+------------------------------------------+
| year(CAST(2011-12-08 10:03:01 AS DATE))  |
+------------------------------------------+
| 2011                                     |
+------------------------------------------+

  1. 日期转月函数: month

语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
spark-sql> select month('2011-12-08 10:03:01');
+-------------------------------------------+
| month(CAST(2011-12-08 10:03:01 AS DATE))  |
+-------------------------------------------+
| 12                                        |
+-------------------------------------------+

  1. 日期转天函数: day

语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
spark-sql> select day('2011-12-08 10:03:01');
+-----------------------------------------+
| day(CAST(2011-12-08 10:03:01 AS DATE))  |
+-----------------------------------------+
| 8                                       |
+-----------------------------------------+

  1. 日期转小时函数: hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
spark-sql> select hour('2011-12-08 10:03:01');
+-----------------------------------------------+
| hour(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-----------------------------------------------+
| 10                                            |
+-----------------------------------------------+

  1. 日期转分钟函数: minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
spark-sql> select minute('2011-12-08 10:03:01') ;
+-------------------------------------------------+
| minute(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-------------------------------------------------+
| 3                                               |
+-------------------------------------------------+

  1. 日期转秒函数: second

语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
spark-sql>  select second('2011-12-08 10:03:01');
+-------------------------------------------------+
| second(CAST(2011-12-08 10:03:01 AS TIMESTAMP))  |
+-------------------------------------------------+
| 1                                               |
+-------------------------------------------------+

  1. 日期转周函数: weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
spark-sql> select weekofyear('2011-12-08 10:03:01') ;
+------------------------------------------------+
| weekofyear(CAST(2011-12-08 10:03:01 AS DATE))  |
+------------------------------------------------+
| 49                                             |
+------------------------------------------------+

  1. 日期比较函数: datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：
spark-sql> select datediff('2012-12-08','2012-05-09');
+----------------------------------------------------+
| datediff(CAST(2012-12-08 AS DATE), CAST(2012-05-09 AS DATE)) |
+----------------------------------------------------+
| 213                                                |
+----------------------------------------------------+

  1. 日期增加函数: date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：
spark-sql> select date_add('2012-12-08',10);
+-----------------------------------------+
| date_add(CAST(2012-12-08 AS DATE), 10)  |
+-----------------------------------------+
| 2012-12-18                              |
+-----------------------------------------+

  1. 日期减少函数: date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：
spark-sql> select date_sub('2012-12-08',10);
+-----------------------------------------+
| date_sub(CAST(2012-12-08 AS DATE), 10)  |
+-----------------------------------------+
| 2012-11-28                              |
+-----------------------------------------+

字符串

注意：数据库中的字符串索引从1开始，而不是0

  1. length（str） 返回字符串str的长度

spark-sql> select length(123);
+------------------------------+
| length(CAST(123 AS STRING))  |
+------------------------------+
| 3                            |
+------------------------------+

  1. instr(str, substr),作用等同于str.indexOf(substr)

spark-sql> select instr("www.qfnb.com","b");
+-------------------------+
| instr(www.qfnb.com, b)  |
+-------------------------+
| 8                       |
+-------------------------+

  1. substr substring(str, pos[, len]):从str的pos位置开始，截取子字符串，截取len的长度，如果不传len，截取余下所有。

spark-sql> select substr("www.qfnb.com",5);
+--------------------------------------+
| substr(www.qfnb.com, 5, 2147483647)  |
+--------------------------------------+
| qfnb.com                             |
+--------------------------------------+

  1. substring_index(str, delim, count):将字符串str使用delim进行分割，返回强count个使用delim拼接的子字符串。

spark-sql> select substr("www.qfnb.com",".",2);
+------------------------------------------+
| substr(www.qfnb.com, CAST(. AS INT), 2)  |
+------------------------------------------+
| ww                                       |
+------------------------------------------+

  1. concat(str1, str2)拼接字符串

spark-sql> select concat("www.qfnb.com",".",2);
+---------------------------------------------+
| concat(www.qfnb.com, ., CAST(2 AS STRING))  |
+---------------------------------------------+
| www.qfnb.com.2                              |
+---------------------------------------------+

  1. concat_ws(separator, str1, str2):使用指定分隔符来拼接字符串

spark-sql> select concat_ws("www.qfnb.com",".",2);
+------------------------------------------------+
| concat_ws(www.qfnb.com, ., CAST(2 AS STRING))  |
+------------------------------------------------+
| .www.qfnb.com2                                 |
+------------------------------------------------+

统计函数

略

特殊

  • array:返回数组 collect_set：返回一个元素不重复的set集合 collect_list：返回一个元素可重复的list集合 split(str, regex):使用regex分隔符将str进行切割，返回一个字符串数组 explode(array):将一个数组，转化为多行    cast(type1 as type2)：将数据类型type1的数据转化为数据类型type2 eg：使用sql方式统计WordCount

select 
   tmp.word, 
 count(1) counts 
from (
  select 
        explode(split(line, "\\s+")) word 
  from test_wc
) tmp 
group by tmp.word 
order by counts desc, tmp.word;

实现方式分类

  • UDF(User Defined function)用户自定义函数 一路输入，一路输出，比如year，date_add, instr

  • UDAF(User Defined aggregation function)用户自定义聚合函数 多路输入，一路输出,常见的聚合函数，count、sum、collect_list

  • UDTF(User Defined table function)用户自定义表函数 一路输入，多路输出，explode

  • 开窗函数 Row_Number() --->分组topN的求解

select
  tmp.* 
from (
  select
    name,
    age,
    married,
    height,
    row_number() over(partition by married order by height) rank
  from teacher
) tmp
where tmp.rank < 3
1.5.3 自定义函数

概述

当系统提供的这些函数，满足不了咱们的需要的话，就只能进行自定义相关的函数，一般自定义的函数两种，UDF和UDAF。

UDF

一路输入，一路输出，完成就是基于scala函数。

通过模拟获取字符串长度的udf来学习自定义udf操作。

代码实现

object _01SparkSQLUDFOps {
    def main(args: Array[String]): Unit = {
        // 创建执行入口
        val spark = SparkSession.builder()
                    .master("local[*]")
                    .appName("SparkSQLUDF")
                    .getOrCreate()
        // 导入隐式转换及构建数据
        import spark.implicits._
        val rdd = spark.sparkContext.parallelize(List(
            "songhaining",
            "yukailu",
            "liuxiangyuan",
            "maningna"
        ))
        //使用sparksession进行udf和udaf的注册
//        spark.udf.register[Int, String]("myLen", (str:String) => myStrLength(str))
//        spark.udf.register[Int, String]("myLen", str => myStrLength(str))
        spark.udf.register[Int, String]("myLen", myStrLength)
        // 创建表
        val df = rdd.toDF("name")
        df.createOrReplaceTempView("test")
​
        //求取每个字符串的长度
        val sql =
            """
              |select
              |  name,
              |  length(name) nameLen,
              |  myLen(name) myNameLen
              |from test
            """.stripMargin
        spark.sql(sql).show()
​
        spark.stop()
    }
    //自定义udf
    def myStrLength(str:String):Int = str.length
}

Guff_hys_python数据结构,大数据开发学习,python实训项目-CSDN博客

Guff_hys
关注
  • 33
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
sparksql自定义函数
SunnyRivers
05-29 132
Spark SQL UDF（也称为用户定义函数）是Spark SQL&DataFrame最有用的功能，它扩展了Spark内置功能。在本文中，我将解释什么是UDF？为什么我们需要它，以及如何使用Java、Scala示例在DataFrame和SQL上创建和使用它。注意：UDF是最昂贵的操作，因此只有在必要时才使用它们。UDF又称用户定义函数，如果你有使用SQL的经历，那么UDF对你来说并不是什么新鲜事，因为大多数传统的RDBMS数据库都支持用户定义函数。Spark UDF与这些类似。
sparksql常用计算函数
Moonset1995的博客
01-14 4838
常用函数
Spark_SQL函数定义（定义UDF函数、使用窗口函数）
2202_75347029的博客
10-25 1797
无论Hive还是SparkSQL分析处理数据时，往往需要使用函数，SparkSQL模块本身自带很多实现公共功能的函数，在pyspark.sql.functions中。SparkSQL与Hive一样支持定义函数:UDF和UDAF，尤其是UDF函数在实际项目中使用最为广泛。Hive中自定义函数有三种类型：第一种：UDF（User-Defined_-function）函数一对一的关系，输入一个值经过函数以后输出一个值；
Spark SQL 内置函数
梁辰兴的博客
06-16 2496
Spark SQL内置了大量的函数，位于API org.apache.spark.sql.functions中。这些函数主要分为10类：UDF函数、聚合函数、日期函数、排序函数、非聚合函数、数学函数、混杂函数、窗口函数、字符串函数、集合函数，大部分函数与Hive中相同。当Spark SQL提供的内置函数不能满足查询需求时，用户可以根据自己的业务编写自定义函数（User Defined Functions，UDF），然后在Spark SQL中调用。
SparkSQL与Hive整合 、SparkSQL函数操作
m0_51388399的博客
05-07 669
在使用聚合函数后，会将多行变成一行，而over()开窗函数其实就是给每个分组的数据，按照其排序的顺序，打上一个分组内的行号，直接将所有列信息显示出来。SQL中函数，其实说白了就是各大编程语言中的函数，或者方法，就是对某一特定功能的封装，通过它可以完成较为复杂的统计。当系统提供的这些函数，满足不了我们的需要的话，就只能进行自定义相关的函数，一般自定义的函数两种，UDF和UDAF。2)开窗函数：over()开窗函数是按照某个字段分组，然后查询出另一字段的前几个的值，相当于分组取topN。
SparkSQL函数定义——UDF函数，窗口函数
m0_55674689的博客
12-24 3641
SparkSQL函数定义——UDF函数，窗口函数
PySpark | SparkSQL函数 | SparkSQL运行流程
liujiesxs的博客
06-28 1197
SparkSQL函数定义，SparkSQL整合Hive
SparkSQL之函数解析
m0_49620121的博客
01-30 1368
! ! expr - Logical not. 不的意思 Examples: > SELECT ! true; false > SELECT ! false; true > SELECT ! NULL; NULL Since: 1.0.0 != expr1 != expr2 - Returns true if expr1 is not equal to expr2, or false otherwise. 如果 expr1 不等于 expr2 则返回 true，否则返回 fa
SparkSQL 实现窗口函数
小叮当的博客
02-22 786
开窗函数的引入是为了既显示聚合前的数据，又显示聚合后的数据。即在每一行的最后一列添加聚合函数的结果。开窗用于为定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用Group BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。聚合函数和开窗函数的区别：聚合函数是多行变一行；如果要显示其他列必须加入到group by中。开窗函数是一行变多行；不需要加group by就可以将所有信息显示出来。开窗函数分类：聚合开窗函数：聚合函数(col) + OVER()
Spark SQL操作JSON字段的小技巧
09-09
主要给大家介绍了关于Spark SQL操作JSON字段的小技巧，文中通过示例代码介绍的非常详细，对大家学习或者使用spark sql具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧。
HadoopCon_2015_SparkSQL:HadoopCon 2015上的Spark SQL教程
04-30
在本教程中，您将学习如何使用SQLContext（HiveContext）初始化Spark SQL，操作DataFrame，导入数据，用户定义的函数以及操作cache（）。 例如， 滑梯 这是的链接 Python API Spark 1.4.1： from pyspark . sql ...
HadoopLearning：完整的大数据基础学习教程，包含最基础的centos，maven。大数据主要包含hdfs，mr，yarn，hbase，kafka，scala，sparkcore，sparkstreaming，sparksql。
02-03
RowKey设计，协处理，Shell访问，Java API访问和Hbase性能调优ElasticSearch入门，概念基础，基础原理，索引，映射，搜索，聚合和性能优化Scala入门，函数，类，对象，特征，模式匹配，常用类和方法，异常处理，编程...
大数据与云计算教程课件 优质大数据课程 34.SparkSQL（共15页）.pptx
12-18
21.Pig模式与函数 22.Zookeeper 23.Zookeeper服务 24.使用Zookeeper构建应用 25.Sqoop 26.深入Sqoop的导入 27.深入Sqoop导出 28.Flume 29.Kafka 30.Kafka开发 31.Strom 32.Spark入门之Scala 。。。
精品课程推荐 大数据与云计算教程课件 优质大数据课程 21.Pig模式与函数（共64页）.pptx
12-18
大数据与云计算教程课件 优质大数据课程 21.Pig模式与函数（共64页）.pptx 大数据与云计算教程课件 优质大数据课程 22.Zookeeper（共28页）.pptx 大数据与云计算教程课件 优质大数据课程 23.Zookeeper服务（共47页）...
2024山软创新实训：软件系统架构
lyh20021209的博客
05-30 860
本文着重介绍本应用：基于开源LLM的易学大模型软件系统的架构。在经过2个月的探索、选型、实验、开发后，我们团队终于把整个系统的各块拼图搭建了起来，现在剩下的是集成、评测、优化和部署的工作。
2024年5月系统架构设计师综合知识真题
Nanki_的博客
05-26 1247
系统架构评估 微服务架构特点，优势和缺点:7分。 质量属性及场景填空:6个空，6分。 质量属性场景描述六要素:12分【刺激源，刺激，制品，环境，响应，响应度量】 Redis: 基于MySQL锁的缺点:9分【性能瓶颈，复杂性，可靠性问题，扩展性差，一致性问题】 分布式Redis锁的死锁的情况，其他分布式锁:10分 redis zset命令:6分【ZADD，ZCARD，ZSCORE，ZRANGE】 UML 序列图的哪三种消息和概念【同步，异步，返回】 序列图补全填空 系统分析设计过程中两种交互图的选取
系统架构设计师【第6章】: 数据库设计基础知识 (核心总结)
最新发布
数据知道的博客
05-30 1528
介绍数据库技术 的发展历程以及数据模型、主流的关系数据库、数据库设计的步骤与方法、新型数据库 NoSQL。
系统架构设计师【第5章】: 软件工程基础知识 (核心总结)
数据知道的博客
05-30 1394
按照常见的软件开发阶段划分，分别讨论需求、分析、设计、编码和测试等环节中的常见方法和技术
sparksql show函数
05-16
`show()`函数是Spark SQL中的一个操作，用于将DataFrame或Dataset的内容以表格的形式展示出来。它接受一个可选的参数，即展示的行数，如果未指定，则默认展示前20行。 示例： ```scala val df = spark.read.format("csv").option("header", "true").load("path/to/file.csv") df.show() // 默认展示前20行 df.show(50) // 展示前50行 ``` `show()`函数还可以接受一个Boolean类型的参数，用于控制结果的输出方式。如果参数为true，则结果以ASCII表格的形式输出，否则以纯文本的形式输出。默认为true。 示例： ```scala // 以ASCII表格的形式输出结果 df.show(true) // 以纯文本的形式输出结果 df.show(false) ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Guff_hys

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值