包含hivegroup_concat的词条

本篇文章给大家谈谈hivegroup_concat，以及对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、hive关于行转列，列转行的使用
• 2、Hive常用查询函数
• 3、大数据之-HIVE入门（十四）

hive关于行转列，列转行的使用

行转列：

定义：就是把形如

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

的数据转换为

id ------------- name

1，2，3，4 --------- a

这里把四行的数据卜颂瞎变成了由一行显示，这就是行转列。

首先要用到的是collect_set或者collect_list，前者去重，后者不去重，一般配合group使用，能把形如：

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

转换为

id --------- name

["1","2","3","4"] --------- a

然后需要用到的是concat_ws函数，这个函数需要传递指定分隔符，以及字符串或者字符串数组。能起到把多个字符串拼接到一起的作用。

形如：

id --------- name

["1","2","3","4"] --------- a

通过concat_ws(',',[1,2,3,4])转换为

id --------- name

1，2，3，4 --------- a

连起来就完成了行转列

一般sql为:

列转行

定义：就是把形如

id ------------- name

1，2，3，4 --------- a

的数据樱败转换为

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

这里把一行的数据变成了由四行显示，这就是列转行。

首先要用到split函数，这个行数可以把字符串按照指定的规则切分为字符串数组。

形如：

id --------- name

1，2，3，4 --------- a

通过split('1,2,3,4',',')转换为

id --------- name

["1","2","3","4"] --------- a

然后是explode函数，这个行数可以把数组的每个元素转换为一行。

形如

id

["1","2","3","4"]

通过explode(["1","2","3","4"] )转换为

id

1

2

3

4

最后为了拼接其它字段，还需要用到lateral view把explode获得的行当虚拟表来用型空。

使用方法为 lateral view explode(split(["1","2","3","4"] , ',')) tmptable as new_id ;

其中new_id元素为转换为行后的字段名。

把以上综合起来就完成了列转行

一般sql为:

[img]

Hive常用查询函数

函数说明：

NVL ：给值为 NULL 的数据赋值，

它的格式是

NVL( string1, replace_with)。

它的功能是如果string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参数都为 NULL ，则返回NULL。

date_format:格式化时间

date_add:时间跟天数相加

date_sub:时间跟天数相减，类似与add，使用一个就ok

select date_sub('2019-06-29',5);

datediff:两个时间相减

select datediff('2019-06-29','2019-06-24');

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。

分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL， 返回值也将为 NULL。

这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-',str1,str2) from XX; //str为string类型的列名

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，

产生 array 类芹毁型字段。

练习：把星座和血型一样的人归类到一起

第一步转换：

白羊座,A 孙悟空

射手座,A 大海

白羊座,B 宋宋

白羊座,A 猪八戒

射手座,A 凤姐

合并销前

EXPLODE(col)：将hive 一嫌斗备列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW //侧写

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

练习：将电影分类中的数组数据展开

select explode(category) from movie_info;

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；

/ 在over(里面使用) /

CURRENT ROW：当前行；

n PRECEDING：往前 n 行数据；

n FOLLOWING：往后 n 行数据；

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDEDFOLLOWING 表示到后面的终点；

/* 在over外面使用*/

LAG(col,n)：往前第n 行数据；

LEAD(col,n)：往后第 n 行数据；

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，

对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

数据：

一、查询在 2017 年 4 月份购买过的顾客及总人数

group分组一组一个值，over给每一条数据独立开窗

查询顾客的购买明细及购买总额

要将 cost 按照日期进行累加

先排序

再累加

查询顾客上次的购买时间

下次的购买时间

查询前 20%时间的订单信息

RANK() 排序相同时会重复，总数不会变，可以并列

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

练习：计算每门学科成绩排名

计算每门学科成绩排名

大数据之-HIVE入门（十四）

当join时有一个或多个小表可以装载进内存时可以使用mapjoin完成。

第一种方法是可以加上mapjoin指示

第二种方法是设置 set hive.auto.convert.join=true;来让hive自动优化。同时可以指定

set hive.auto.convert.join.noconditionaltask = true; --默认开启

set hive.auto.convert.join.noconditionaltask.size = 10000000; --默认10M，可以根据需求做调整。

hive.mapjoin.smalltable.filesize= 2500000；--早期hive版本小表文件大小设置默认25M。

当数据量比较大启动mapjoin后会造成问题请关闭

set hive.map.aggr = true; //是否在 Map 端进行聚合，默认为 True ；

set hive.groupby.mapaggr.checkinterval = 100000000; //在 Map 端进行聚合操作的条目数目

1、在关联操作前尽量减小数据集,能先聚合的先聚合、能过滤的先过滤（如设置查询条件、合理设置分枣凳雹区，有分区必须设置分区范围）。

2、关联时数据类型要做到一致，如果不一致请用cast先转换类型。

3、慎用count(distinct) ，容易产生数据倾斜，可以先group by 再count。

4、减少小文件，合理设置输入文件大小、合理设置map job 、reduce job数。

set hive.merge.mapredfiles=true；--设置合并map文件标识。

set mapred.max.split.size=100000000; --设置最大输入文件大小，大于此数值都会进行拆分。

set mapred.min.split.size.per.node=100000000;--设置每个节点可处理的最小值。

set mapred.min.split.size.per.rack=100000000;--设粗晌置每个机架可处理的最小值。

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; – 执行前进行小文件合并。

set mapred.reduce.tasks=10; -- 设置reduce的数量

set hive.exec.reducers.bytes.per.reducer=1073741824 --设置每个reduce所处理的数据大小

5、选取字段避免用select * ，只引用你要用的字段，如select a.uid,a.price。

6、关联值有null值的情况下，可以将null值过滤出来单独处理或者将null值随机赋值。当存在某key有热点问题，也可以同样处理。

7、合理设置数据模型、文件存储格式有利于查询效率优化。凳帆

8、善用union all 合并对于同一个表的查询，有利于整体提高效率。

9、合理使用中间临时表，数据量巨大时，如统计一年的数据，可先小规模聚合如按月聚合生成中间表，最后再合并统计出结果。

10、有order by 要限制输出条数。

11、合理设置并行查询

set hive.exec.parallel= true ； --以开启并发执行。

set hive.exec.parallel.thread.number= 10 ; //同一个sql允许最大并行度，默认为8。

关于hivegroup_concat和的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。