explode列转行函数的用法
select ~, col_name
from table_name
lateral view explode(split(~,',')) tmp_view as col_name
（…）里面是要分割的列，tmp_view 临时表 可以随便写， col_name 是要查询的列
where 条件要写在lateral view 后面，不然报错
select ~, col_name
from table_name
lateral view explode(...) tmp_view as col_name
where ....;
多行进行合并
concat_ws(‘,’,collect_set())
concat_ws(‘,’,collect_list())
数据
| name | type |
|---|---|
| aa | 1 |
| aa | 2 |
| aa | 2 |
| aa | 3 |
select
name,
concat_ws(',',collect_set(cast(type as string))) as type
from
collect_tmp_table
group by name;
结果：
| name | type |
|---|---|
| a | 1,2,3 |
select
name,
concat_ws(',',collect_list(cast(type as string))) as type
from
collect_tmp_table
group by name;
结果：
| name | type |
|---|---|
| a | 1,2,2,3 |
| collect_set()（对某列进行去重） | |
| collect_list()（对某列不进行去重） | |
| 必须保证要合并的行是string类型，不是的话用cast(col as string)转换 | |
| 他们两个都不进行排序，想要结果有顺序，可以先排好序再用，或者使用 distribute by + sort by 进行分组排序 |
select
name,
concat_ws(',',collect_list(cast(type as string))) as type
from
(select
name,
type
from collect_tmp_table
distribute by name
sort by type
) t
group by name;
json字符串解析之get_json_object与json_tuple
要解析的json
json_name
{"name":"Triumph,"sex":"man","age":"25"}
{"name":"Candy,"sex":"woman","age":"22"}
{"name":"jack,"sex":"man","age":"12"}
- get_json_object（presto：json_extract_scalar）函数的作用：
用来解析json字符串的一个字段。
例子：
select get_json_object(json_name,'$.name') as name,
get_json_object(json_name,'$.sex') as sex,
get_json_object(json_name,'$.age') as age
from table_name
- json_tuple函数的作用：
用来解析json字符串中的多个字段。
例子：
select a.json_name,
b.name,
b.sex,
b.age
from table_name a
lateral view json_tuple(json_name,'name', 'sex', 'age') b as name, sex, age;
distribute by的数据倾斜优化
distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法.
大部分情况都用于解决Map输出的文件大小不均,Reduce输出文件大小不均,小文件过多,文件超大等情况，动态分区过多,个别分区下文件大小不均,例如数据延迟等情况,如何控制不同分区下文件的个数。
insert overwrite table table_name partition(day)
select * from temp_name
distribute by day, pmod(cast(rand()*1000 as int),50);
这个sql，就是通过pmod(cast(rand()*1000 as int),50)对文件进行分区，防止按照day字段分区会出现的数据倾斜现象。
该数据会分为50块，对多少取模就分为多少块。
cast(a,b) 将a类型转换为b类型。
pmod(a,b) a对b 取模。
pmod(cast(rand()*1000 as int),50)和cast(rand()*1000 as int)%50是一样的。
Hive 日期时间操作
hive表结构和数据的复制
union 和 union all
如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来，但这两者从使用和效率上来说都有所不同。
1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。
union：选取不同的值，不允许重复值出现，按字段进行排序，效率低
select column_name(s) from table_name1
union
select column_name(s) from table_name2
union all：允许重复，不排序，随机插入，直接合并，效率高
select column_name(s) from table_name1
union all
select column_name(s) from table_name2
join 连接可以添加条件先对右部分表进行过滤再进行聚合
select
a.id, a.age, b.age
from
a join b on a.id=b.id and b.age>20
where a.age>20
先按b.age>20过滤b表，然后进行join聚合操作，再按a.age>20对聚合后的数据进行过滤
left join 和 right join 就不可用
聚合函数+窗口函数over()
OVER()：指定分析函数工作的数据窗口大小，决定了聚合函数的范围，这个数据窗口大小可能会随着行的变而变化，同时可以使用以下进行限定范围。
聚合操作时要注意null值
count(*) 包含null值，统计所有行数；
count(id) 不包含null值；
min 求最小值是不包含null，除非所有值都是null；
avg 求平均值也是不包含null
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from t_window;
如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合。
使用了order by子句,未使用window子句的情况下,默认从起点到当前行。
window子句：
- PRECEDING：往前
- FOLLOWING：往后
- CURRENT ROW：当前行
- UNBOUNDED：起点
- UNBOUNDED PRECEDING：表示从前面的起点
- UNBOUNDED FOLLOWING：表示到后面的终点
窗口函数：lag,lead,first_value,last_value
- lag
lag(col,n,~) 用于统计窗口内往上第n行值。
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
用法示例：
select
age,sex,
lag(age,2) over(partition by sex order by age desc) as rank
from
row_table;
-
lead(与lag相反）
lead(col,n,~) 用于统计窗口内往下第n行值。
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） -
first_value
返回相对于窗口中第一行的指定列的值。
用法示例：
select
id,age,sex,
first_value(id) over(partition by sex order by age desc) as rank
from
row_table;
- last_value
返回相对于窗口中最后一行的指定列的值。
rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING 用这个是窗口内所有的进行比较
select
id,age,sex,
first_value(id) over(partition by sex order by age desc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as rank
from
row_table;
注意：first_value 和 last_value 一起用有可能会出错，因为都是窗口内截止到当前行进行比较
row_number over()的使用
可以用first_value代替排序后取第一个
row_number() over()他的作用就是分组排序加上序号标记
row_number() over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（该编号在组内是连续并且唯一的)。
原表：
| id | age | sex |
|---|---|---|
| 1 | 10 | m |
| 2 | 14 | m |
| 3 | 16 | w |
| 4 | 8 | w |
| 5 | 20 | m |
| 6 | 18 | w |
select
id,age,sex,
row_number() over(partition by sex order by age desc) as rank
from
row_table;
结果：
| id | age | sex | rank |
|---|---|---|---|
| 4 | 8 | w | 1 |
| 3 | 16 | w | 2 |
| 6 | 18 | w | 3 |
| 1 | 10 | m | 1 |
| 2 | 14 | m | 2 |
| 5 | 20 | m | 3 |
| row_number() over(partition by sex order by age desc) as rank | |||
| 就相当于增加了一列(rank)，over()中partition by sex是按照sex分组，order by age desc按照年龄排序，然后row_number()在加上序号。 |
row_number()、rank()和dense_rank()
row_number：不管排名是否有相同的，都按照顺序
例如：1,2,3,4,5,…,n
rank：排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
例如：1,2,2,4,5,5,5,8,…,n
dense_rank：排名相同的名次一样，且后面名次不跳跃
例如：1,2,2,3,4,4,4,5,…,n
具体用法同上
在去重时能使用group by代替distinct就不要使用distinct
当一个表的数据量非常大的时候，会发现一个简单的count(distinct col_name)这种语句跑的特别慢，和直接运行count(col_name)的时间差了很多。
在能使用group by代替distinct就不要使用distinct
Hive在处理时产生了数据倾斜
使用distinct会将所有的col_name都shuffle到一个reducer里面，这就是数据倾斜，再看group by直接按列名分组，起多个reducer（动态分配，如果没设置reduce的个数），将数据分布到多台机器上执行，处理速度就快了。
由于没有设置Reduce的个数，Hive会根据数据的大小动态的指定Reduce大小，也可以手动设置
set mapred.reduce.tasks=300；
注意：设置了reduce的任务个数，distinct去重也是走一个reduce，所以会很慢。
设置map的数量提高运行速度
1.如果想增加map个数，则设置mapred.map.tasks 为一个较大的值。
2.如果想减小map个数，则设置mapred.min.split.size 为一个较大的值。
mapred.max.split.size是参考值
这个参数是设置map的任务的数量。
set mapred.map.tasks=2000;
这个参数是设置每个map的大小，数值越大，每个map的大小就越大，相应的map的总数量就减少了。
set mapred.max.split.size=102400;
left join on + 多条件与 where 区别
总体规则：先匹配，再筛选where条件。
left join如果在on时添加限制条件，对左边表进行约束的话是不会生效的，但是对右表会生效，会先筛选右边表的数据再和左表关联。
合理利用会减少查询的时间。
例一：
select * from table a left join table b on a.id=b.id and a.id is not null
where b.id is not null;
例二：
select * from table a left join table b on a.id=b.id and b.id is not null
where a.id is not null;
例一中a.id is not null不起作用，关联时跑的是a表的全数据
例二中b.id is not null起作用，关联时，先筛选b表的数据，然后再和a表进行关联
例一例二中的where都是在表关联完，然后再筛选数据
Mapreduce中Combiner的使用及误区
–该部分点标题，看大佬的总结！
避免科学计数法
cast(列名 as decimal(m,n))的方式避免科学计数法结果的出现。
m总共几位，n小数点后面保留几位。自动给四舍五入。
MSCK命令修复Hive表分区
我们平时通常是通过alter table add partition方式增加Hive的分区的，但有时候会通过HDFS put/cp命令往表目录下拷贝分区目录，如果目录多，需要执行多条alter语句，非常麻烦。Hive提供了一个"Recover Partition"的功能。
具体语法如下：
MSCK REPAIR TABLE table_name;
原理相当简单，执行后，Hive会检测如果HDFS目录下存在但表的metastore中不存在的partition元信息，更新到metastore中。
注意：为了让 MSCK 命令工作，分区的目录名必须是 /partition_name=partition_value/结构的，否则将无法添加分区。这时候你必须使用add partition命令了。
count(*) 和 count(1)和count(列名)区别
count( * )和 count(1) 不会忽略NULL
count(列名) 会忽略NULL
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
详细：count(*) 和 count(1)和count(列名)区别
显示表的列名
set hive.cli.print.header=true;
字符串的模糊匹配查询
返回值是第一个匹配的位置
hive：locate(); 字段在后，查找的字符在前
presto：strpos(); 字段在前，查找的字符在后
count(null) = 0
select count(if(a = "ds", a, null));
hive 四舍五入，向上取整，向下取整
-
向上取整 ceiling()
-
向下取整 floor()
-
四舍五入 round(a, b) – – 结果a 精确到小数点右 b位，或是左 -b位
-
四舍五入取整 cast(round(a, 0) as int) – – round至少保留一位小数。
-
舍弃小数取整 cast(a as int)
hive array_contains
coalesce
coalesce(col1,col2,…,coln)返回参数列表中的第一个非空col。
where条件筛选用or会扫全表，不能列裁剪
3057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
