【sql】 hive-sql的小知识点
1)删除分区表里面的某个分区
防止一天跑两遍数据的时候,insert into会倒入二倍的数据的情况
Alter table lirui_a drop if exists partition(dt =’”””+yesterday+”””’)
----------------------------------------------------------
2)hive sql 分析数据的命令
Analyze table lirui_a compute statistics;
输出结果:
Table dev.dev_brand_brand2alias stats: [numFiles=1, numRows=3371, totalSize=124146, rawDataSize=120776]
这里的rawDataSize是指原始数据的大小,totalSize是指占用HDFS存储空间大小。
-----------------------------------------------------------
3)删除某个表里面的数据,但是不删除表结构
Truncate table 表名;
------------------------------------------------------------
7、sql随机取数5条
方法一:效率比较低:select * from lirui_a order by rand() limit 5
方法二:最常用的是查询max(id) * rand() 来随机取数
------------------------------------------------------------
4)删除表格中符合条件的行
insert overwrite table 表名 select * from 表名 where xxxxx;
//注意!!! xxxx是你需要保留的数据的查询结果
-------------------------------------------------------------
5)not like 是like的逆运算
用法: Not like表示不包含的匹配,和like相反,但是用法不是A not like B,而是not A like B
---------------------------------------------------------------
6)regexp 正则 item_name 中含有下面任何字符,即被搞出来
item_name REGEXP '湖人|火箭|雷霆|骑士|字母歌|科比球衣|詹姆斯球衣|哈登球衣|勇士球衣|开拓者|凯尔特人|76人|篮球球衣|NBA球衣|奇才球衣|公牛球衣|邓肯|快船|艾弗森|韦德|老詹球衣|库里球衣|雄鹿队|热火队|篮球服|篮球训练服|老鹰球衣|篮球球衣|NBA球衣|快船球衣|乔丹球衣'
--
set hive.exec.dynamic.partition.mode = nonstrict;
drop table dev.brand_lirui_teshuzifu_rand;
create table dev.brand_lirui_teshuzifu_rand as
select
*
from
(
select
*
from
(
select
item_sku_id,
item_id,
item_name,
row_number() over(partition by item_id) rank
from
gdm.table1
where
sku_valid_flag = 1
and item_valid_flag = 1
and sku_status_cd = 3001
and item_name REGEXP '⒜|⒝|⒞|⒟|⒠|⒡|⒢|⒣|⒤|⒥|⒦|⒧|⒨|⒩|⒪|⒫|⒬|⒭|⒮|⒯|⒰|⒱|⒲|⒳|⒴|⒵'
and dt = '2019-04-21'
)
t
where
rank = 1
)
t1
order by
rand() limit 10000;
----------------------------------------------------------------
7)sql,concat函数、concat_ws函数、concat_group函数
Concat() 函数 – 用于将多个字符串连接成一个字符串,
用法:CONCAT(str1,str2) ,如有任何一个参数为NULL ,则返回值为 NULL。可以有一个或多个参数
例如:数据表Info作为示例,其中SELECT id,name FROM info LIMIT 1;的返回结果为
+----+--------+
| id | name |
+----+--------+
| 1 | BioCyc |
+----+--------+
Select concat(id,’,’,name) as con from info limit 1;
+----------+
| con |
+----------+
| 1,BioCyc |
+----------+
Concat_ws() 语法:concat_ws(separator,str1,str2,……)
如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。
Select concat_ws(‘-’,id,name) as con_ws from info limit 1;
+----------+
| con_ws |
+----------+
| 1_BioCyc |
+----------+
cast
用法:cast(value as type)
功能:将某个列的值显示的转化为某个类型
---------------------------------------------------------------
8) 创建一个相同内容的表
create table dev.brand_no_liruilirui like app.app_brand_no_auth_test
dev.dev_brand_no_auth_test_old : 483733
荣v3 -- app. app_brand_no_auth_test : 483733
----------------------------------------------------------------
9)collect_list/collect_set (列转行)
Hive中collect相关函数有collect_list和collect_set.
这两个函数都是将某一列转换成一个数组返回,不同的是collect_list不去重而collect_set去重。
例如eg:lirui_table
user zimubianhao
张三 A1
张三 A1
张三 A8
李四 B1
李四 B2
王五 C1
按用户分组,统计出每个用户拥有的字母编号。
Select user,collect_list(zimubianhao) from lirui_table group by user
张三 [“A1”,” A1”,” A8”]
李四 [“B1”,” B2”]
王五 [“C1”]
若是collect_set 则会去重数组。
还可以利用collect来突破group by的限制,Hive中在group by查询的时候要求出现在select后面的列都必须是出现在group by后面的,即select列必须是作为分组依据的列,但是有的时候我们想根据A进行分组然后随便取出每个分组中的一个B,代入到这个实验中就是按照用户进行分组,然后随便拿出一个他看过的视频名称即可
Select user,collect_list(zimubianhao)[0] from lirui_table group by user
张三 A1
李四 B1
王五 C1
----------------------------------------------------------------------
8、载入表格
load data local inpath '/home/mart_ipd/lirui/testdata/dev_brand_cate2productword.txt' into table dev_brand_cate2productword;
-----------------------------------------------------------------------
9、hive-sql order by、sort by 、distribute by 区别用法
- Order by 会对全部数据进行全局排序,只“叫醒”一个reducer干活,不管来多少数据,都只启动一个reducer来处理,因此,order by适合小数据排序,不适合大数据。
- sort by 局部排序,sort by具有分身功能,sort by会根据数据量的大小启动一到多个reducer来干活,并且,她会在进入reducer之前为每一个reducer产生一个待排序文件,目的是提高全局排序的效率。
- distribute by 可以控制map结果的分发,它会将相同字段的map输出分发到一个reducer节点上做处理,也就是,某种情况下,我们需要控制某个特定行到某个reducer中,这种操作为后续可能发生的聚合做准备。
eg:
hive > from records2
> select year,temperature
> distribute by year
> sort by year ASC,temperature DESC;
1949 111
1949 78
1950 22
1950 0
1950 -11
Distribute by 通常和sort by 一起使用,distribute by 一般放在前面,比如上面的例子,distribute by 将带处理的数据,按照年份从map端分发出去,这样sort by就可以局部排序聚合了。否则的话,sort by就需要全局排序,这不是他擅长的。
- 若是sort by和distribute by 所用到的列相同,则可以缩写为cluster by 以便同时制定两者的列。
Hive > from records2
> select year,temperature
> cluster by year;
---------------------------------------------------
10、hiving
Hiving 子句对group by子句设置条件的方式与where和select的交互方式类似。Where搜索条件在进行分组操作之前应用,而hiving搜索条件进行分组操作之后应用,hiving语法与where类似,但是hiving可以包含聚合函数,hiving语句可以引用选择列表中显示的任意项。
Hive > select brand_name_alias,count(*) c from dev.dev_lirui group by brand_name_alias hiving c > 1;
------------------------------------------------
11、sql – if
If(条件,A,B)
如果条件满足,则输出A,若是条件不满足,则输出B
select A.id ,if(
count(*) >1, #判断条件,颜色数量
max(A.total) - min(A.total), #颜色多的产品数量 - 颜色少的产品数量
max(A.total) #产品数量
) dis
from (
select id,col,sum(num) total from chanpin group by id,col
) A
group by A.id;
----------------------------------------------
12、COALESCE
返回第一个非null的值
COALESCE(null,0) 返回0
COALESCE(1,0) 返回1
COALESCE(null,1) 返回1
--------------------------------------------