大数据开发之Spark SQL/Hive实用函数分享

字符串函数
1. concat

对字符串进行拼接:concat(str1, str2, ..., strN) ,参数:str1、str2...是要进行拼接的字符串。

, 

 (, );

2. concat_ws

在拼接的字符串中间添加某种分隔符:concat_ws(sep, [str | array(str)]+)。

参数1:分隔符,如 - ;参数2:要拼接的字符串(可多个)

(, , );

3. encode

设置编码格式:encode(str, charset)。

参数1:要进行编码的字符串 ;参数2:使用的编码格式,如UTF-8

(, );

4. decode

转码:decode(bin, charset)。

参数1:进行转码的binary ;参数2:使用的转码格式,如UTF-8

((, ), );

5. format_string / printf

格式化字符串:format_string(strfmt, obj, ...)

(, , );

6. initcap / lower / upper

initcap:将每个单词的首字母转为大写,其他字母小写。单词之间以空白分隔。

upper:全部转为大写。

lower:全部转为小写。

();

 ();

 ();

7. length

返回字符串的长度。

();

8. lpad / rpad

返回固定长度的字符串,如果长度不够,用某种字符进行补全。

lpad(str, len, pad):左补全

rpad(str, len, pad):右补全

注意:如果参数str的长度大于参数len,则返回的结果长度会被截取为长度为len的字符串

(, , );

 (, , );

 (, , );

9. trim / ltrim / rtrim

去除空格或者某种字符。

trim(str) / trim(trimStr, str):首尾去除。

ltrim(str) / ltrim(trimStr, str):左去除。

rtrim(str) / rtrim(trimStr, str):右去除。

();

 (, )  ;

10. regexp_extract

正则提取某些字符串

(, , );

11. regexp_replace

正则替换

(, , );

12. repeat

repeat(str, n):复制给定的字符串n次

(, );

13. instr / locate

返回截取字符串的位置。如果匹配的字符串不存在,则返回0

()         .

 (, );

 (, );

14. space

在字符串前面加n个空格

select concat(space(2), "A");

15. split

split(str, regex):以某字符拆分字符串 split(str, regex)

[,]
 (, );

16. substr / substring_index

(, );

 (, );

 (, , );

 .
 (, , );

17. translate

替换某些字符为指定字符

(, , );

JSON函数
1. get_json_object

(, );

2. from_json

.   (
 (,, (,))
);

这个方法可以给json定义一个Schema,这样在使用时,就可以直接使用a.k这种方式了,会简化很多。

3. to_json

(())  ;

时间函数
1. current_date / current_timestamp

获取当前时间

;

 ;

2. 从日期时间中提取字段/格式化时间
1)year、month、day、dayofmonth、hour、minute、second

();

2)dayofweek(1 = Sunday, 2 = Monday, ..., 7 = Saturday)、dayofyear

();

3)weekofyear(date)

(: ):    { (.) }

 ();

4)trunc

截取某部分的日期,其他部分默认为01。第二个参数: YEAR、YYYY、YY、MON、MONTH、MM

(, );

 (, );

5)date_trunc

参数:YEAR、YYYY、YY、MON、MONTH、MM、DAY、DD、HOUR、MINUTE、SECOND、WEEK、QUARTER

::
 ( ,);

6)date_format

按照某种格式格式化时间

(, );

3. 日期时间转换

1)unix_timestamp

返回当前时间的unix时间戳。

();

 (, );

2)from_unixtime

将unix epoch(1970-01-01 00:00:00 UTC)中的秒数转换为以给定格式表示当前系统时区中该时刻的时间戳的字符串。

select from_unixtime(1609257600, "yyyy-MM-dd HH:mm:ss");

3)to_unix_timestamp

将时间转化为时间戳。

(, );

4)to_date / date

将时间字符串转化为date。

();
 ();

5)to_timestamp

将时间字符串转化为timestamp。

select to_timestamp("2020-12-30 12:30:00");

6)quarter

从给定的日期/时间戳/字符串中提取季度。

();

4. 日期、时间计算

1)months_between(end, start)

返回两个日期之间的月数。参数1为截止时间,参数2为开始时间

(, );

2)add_months

返回某日期后n个月后的日期。

(, );

3)last_day(date)

返回某个时间的当月最后一天

();

4)next_day(start_date, day_of_week)

返回某时间后the first date基于specified day of the week。

参数1:开始时间。

参数2:Mon、Tue、Wed、Thu、Fri、Sat、Sun。

(, );

5)date_add(start_date, num_days)

返回指定时间增加num_days天后的时间

(, );

6)datediff(endDate, startDate)

两个日期相差的天数

(, );

7)关于UTC时间

(, )      ,          ,         .  ,    .

 (, ) ;

 (, )      ,       ,            .  ,    .

 (, );

常用的开窗函数

开窗函数格式通常满足:

function_name([argument_list])
OVER (
[PARTITION BY partition_expression,…]
[ORDER BY sort_expression, … [ASC|DESC]])

function_name: 函数名称,比如SUM()、AVG()

partition_expression:分区列

sort_expression:排序列

注意:以下举例涉及的表employee中字段含义:name(员工姓名)、dept_no(部门编号)、salary(工资)

1. cume_dist

如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows)。如果是降序排列,则统计:大于等于当前值的行数/总行数。用于累计统计。

举例:

1)统计小于等于当前工资的人数占总人数的比例 ,用于累计统计

,
 ,
 ,
 ()  (  )
 ;

2)按照部门统计小于等于当前工资的人数占部门总人数的比例

,
 ,
 ,
 ()  (     )
 ;

2. lead(value_expr[,offset[,default]])

用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)。

举例:按照部门统计每个部门员工的工资以及大于等于该员工工资的下一个员工的工资

,
 ,
 ,
 (, )  (     )
 ;

3. lag(value_expr[,offset[,default]])

与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)。

举例:按照部门统计每个部门员工的工资以及小于等于该员工工资的上一个员工的工资

,
 ,
 ,
 (, )  (     )
 ;

4. first_value

取分组内排序后,截止到当前行,第一个值。

举例:按照部门统计每个部门员工工资以及该部门最低的员工工资

,
 ,
 ,
 ()  (     )
 ;

5. last_value

取分组内排序后,截止到当前行,最后一个值。

举例:按部门分组,统计每个部门员工工资以及该部门最高的员工工资

,
 ,
 ,
 ()  (
         )
 ;

注意:

last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,表示当前行永远是最后一个值,需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

此外:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:为默认值,即当指定了ORDER BY从句,而省略了window从句 ,表示从开始到当前行(当前行永远是最后一个值)。

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:表示从当前行到最后一行。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:表示所有行。

n PRECEDING m FOLLOWING:表示窗口的范围是[(当前行的行数)- n, (当前行的行数)+ m] row。

6. rank

对组中的数据进行排名,如果名次相同,则排名也相同,但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行。RANK() 排序为 (1,2,2,4)。

7. dense_rank

dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。大数据培训当出现名次相同时,则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
DENSE_RANK() 排序为 (1,2,2,3)。

8. SUM/AVG/MIN/MAX

数据:

id        time                  pv1         2015-04-10      11         2015-04-11      31         2015-04-12      61         2015-04-13      31         2015-04-14      22         2015-05-15      82         2015-05-16      6

结果:

,
       ,
       ,
       () (     )  ,
       () (            )  ,
       () (  )  ,
       () (            )  ,
       () (            )  ,
       () (            )
 ;

9. NTILE

NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值。

NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。

如果切片不均匀,默认增加第一个切片的分布。

10. ROW_NUMBER

从1开始,按照顺序,生成分组内记录的序列。

比如,按照pv降序排列,生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多,比如获取分组内排序第一的记录。

SparkSQL函数算子以上函数都是可以直接在SQL中应用的。

那么如果是在Spark SQL的DataFrame/DataSet的算子中调用,可以参考DataFrame/DataSet的算子以及org.apache.spark.sql.functions._下的函数:

(0)

相关推荐

  • javascript 数组 对象的一些方法记录

    记录一下常用的数组和对象的一些方法 数组 push() 数组后添加元素 // 作用:把一个元素或多个元素,从数组后面添加到数组里面: // 参数:添加的数据 // 返回:添加后的数组的长度: let ...

  • 第15天:Python 输入输出

    在前几篇文章中,我们其实已经接触了 Python 的输入输出功能,本篇文章中我们再来详细学习一下. 1 格式化输出 Python 输出值的方式有两种:表达式语句和 print 函数(文件对象的输出使用 ...

  • Crawler:关于爬虫的简介、安装、使用方法之详细攻略

    Crawler:关于爬虫的简介.安装.使用方法之详细攻略 爬虫简介 1.在爬取一些简单的(没有反爬机制的)静态网页时,一般采取的策略是:选中目标(所谓的url链接),观察结构(链接结构,网页结构),构 ...

  • 爬虫精进6

    ---------如有疑问,欢迎交流指正-------- 第6关 练习-储存电影信息-参考 第一步:分析问题,明确结果 问题需求就是把豆瓣TOP250里面的 序号/电影名/评分/推荐语/链接 都爬取下 ...

  • 新增函数concat和老函数concatenate的用法

    很多时候我们都会用文本连接符&进行连接运算,今天就说2个关于文本连接的函数,一个是concatenate,一个是concat.虽然它们写法上有点相似,但是差别还是挺大的.concatenate ...

  • 大数据开发之Spark入门

    什么是Spark? ·大数据的电花火石. ·Spark类似于MapReduce的低延迟的交互式计算框架. ·Spark是UC Berkeley AMPLab开发的是一种计算框架,分布式资源工作交由集群 ...

  • 大数据开发之Spark 基础入门学习

    集群相关 Cluster Manager指的是在集群上获取资源的外部服务,为每个spark application在集群中调度和分配资源的组件,目前有三种类型: Standalone:Spark 原生 ...

  • 大数据开发之Yarn和Spark UI界面获取的方法

    一.Yarn 以获取Yarn界面队列信息为例: 1. 接口(HTTP Request) http://ip:port/ws/v1/cluster/scheduler ip和port:Yarn Reso ...

  • 大数据开发之Hive应用分析

    Hive架构 1.用户接口:Client CLI(hive shell) JDBC(java访问hive) WEBUI(浏览器访问hive) 2.元数据(Metastore)-数据库 元数据包括:表名 ...

  • 大数据开发之Hive解析Json数组

    在Hive中会有很多数据是用Json格式来存储的,如开发人员对APP上的页面进行埋点时,会将多个字段存放在一个json数组中,因此数据平大数据培训台调用数据时,要对埋点数据进行解析.接下来就聊聊Hiv ...

  • 大数据开发之HDFS分布式文件存储系统详解

    HDFS(Hadoop Distributed File System)分布式文件存储系统,主要为各类分布式计算框架如Spark.MapReduce等提供海量数据存储服务,同时HBase.Hive底层 ...

  • 大数据开发之Hadoop集群安装教程

    配置文件的修改 注意:以下所有操作都在node01主机进行. 1.1 hadoop-env.sh 1.介绍 文件中设置的是Hadoop运行时需要的环境变量.JAVA_HOME是必须设置的,即使我们当前 ...

  • 大数据开发之HDFS的API操作过程

    创建maven工程并导入jar包 <repositories> <repository> <id>cloudera</id> <url>ht ...

  • 大数据开发之Hadoop生态6 大核心组件

    Hadoop 第一代分布式存储计算框架 Hadoop是一个分布式系统基础架构,由Apache基金会开发,它允许用户在不了解分布式底层细节的情况下,开发分布式程序,充分利用集群的威力高速运算和存储.Ha ...