hive的常用函数、方法、处理数据

hive的常用函数、常用方法、处理数据、农场案例

Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

hive特点：可扩展性，延展性，容错性

hive基本架构：

hive的基本使用方法

启动一个hive交互shell

bin/hive
hive>

或者

hiveserver2

hive启动优化

加速启动hive，在hive-site.xml添加

<!-- hiveserver2的高可用参数，开启此参数可以提高hiveserver2的启动速度 -->
<property>
    <name>hive.server2.active.passive.ha.enable</name>
    <value>true</value>
</property>

常用方法

导入数据

load data local inpath '/root/tenet/family.txt' into table family_members;

常用函数

条件运算

1、 if函数,返回类型T，当条件testCondition为True时，返回valueTure；

select if(1 = 2, 100, 200);

>>200

2、非空查找函数:COALESCE,返回类型T，返回参数中的第一个非空值；如果所有都为NULL，那么返回NULL。

select coalesce(null, '100', '50');
>>100

3、条件判断函数：CASE
返回类型：T
描述：如果 a 等于 b，那么返回 c；如果 a 等于 d，那么返回 e；否则返回 f。

select case 100 when 50 then 'tom' when 100 then 'jack' else 'mary' end 
>>jack

常用内置函数

类型转换函数:cast

create table test1(id string,birthday string,salary string)
row format delimited fields terminated by '\t';

select id,cast(birthday as date) as bir, salary from t_fun ;

数学运算函数

round：四舍五入函数

select round(5.4);   ## 5  四舍五入
select round(5.1345,3) ;  ##5.135

ceil：向上取整函数

select ceil(5.4) ; // select ceiling(5.4) from dual;   ## 6  向上取整

floor：向下取整函数

select floor(5.4);  ## 5  向下取整

abs：绝对值函数

select abs(-5.4) ;  ## 5.4  绝对值

max(),min()聚合函数

select max(age) from t_person;    #聚合函数
select min(age) from t_person;    #聚合函数

字符串函数

substr:截取子串

substr(string str, int start)   ## 截取子串
substring(string str, int start)
示例：select substr("abcdefg",2) from dual;

substr(string, int start, int len) 
substring(string, int start, int len)
示例：select substr("abcdefg",2,3) from dual;

concat:拼接字符

concat(string A, string B...)  ## 拼接字符串
示例：select concat("ab","xy") from dual;  ## abxy
select concat_ws(".","192","168","33","44") from dual; ## 192.168.33.44

length(string A):字符长度函数

select length("192.168.33.44") from dual;  ## 13

split(string str, string pat)

示例：select split("192.168.33.44",".") from dual; 错误的，因为.号是正则语法中的特定字符
select split("192.168.33.44","\\.") from dual;

大小写转换函数

upper(string str) ##转大写
lower(string str)

日期函数

1、UNIX：时间戳转日期函数，转化 UNIX 时间戳（从1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式。

select from_unitime(1590000000,'yyyy-MM-dd');
>>2020-05-21

2、unix_timestamp：获取当前UNIX时间戳函数：unix_timestamp,转换格式为 “yyyy-MM-dd HH:mm:ss” 的日期到 UNIX 时间戳。如果转换失败，则返回 NULL。

select unix_timestamp('20200521 02:40:00', 'yyyyMMdd HH:mm:ss');
>>1590000000

3、to_date:日期时间转日期函数，返回日期时间字段中的日期部分

select to_date('2020-05-21 02:40:00');
>>2020-05-21

4、weekofyear：日期转周函数 weekofyear(string date) 返回类型：int ，返回日期在该年的周数。

select weekofyear('2020-05-21 02:40:00');
>>21

5、datediff:日期比较函数：datediff(string enddate, string startdate) ,返回类型：int, 返回结束日期减去开始日期的天数。

select datediff('2020-05-21', '2020-01-01');
>>141

6、date_add:日期增加函数：date_add(string startdate, int days), 返回类型：string, 返回开始日期 startdate 增加 days 天后的日期。

select date_add('2020-05-21', 10);
>>2020-05-31

7、date_sub：日期减少函数：date_sub(string startdate, int days) ， 返回类型：string， 返回开始日期 startdate 减少 days 天后的日期。

select date_sub('2020-05-21', 10);
>>2020-05-11

字符串函数

1、字符串长度函数：length
int

select length('abcedfg');

>>7

2、字符串反转函数：reverse
string

select reverse('abcedfg');

>>gfdecba

3、字符串连接函数：concat
string

select concat(‘abc’, 'def’, 'gh');

>>abcedfg

4、带分隔符字符串连接函数：concat_ws
string

select concat_ws(',', 'abc', 'def', 'gh');

>>abc,def,gh

5、字符串截取函数：substr | substring
string

返回字符串 A 从 start 位置到结尾的字符串

select substr('abcde', 3);

>>cde

select substring('abcde', -1);

>>e

1
6、字符串截取函数：substr | substring
string
返回字符串 A 从 start 位置开始，长度为 len 的字符串。

select substr('abcde', 3, 2);

>>cd

select substring('abcde', -2, 2);

>>de

7、字符串转大写函数：upper | ucase
string

select upper('Apple');

>>APPLE

8、字符串转小写函数：lower | lcase
string

select upper('Apple');

>>apple

9、去空格函数：trim
string
去除字符串两边的空格

select trim(' abc ');

>>abc

10、左边去空格函数：ltrim
string

select ltrim(' abc ');

>>abc[空格]

11、右边去空格函数：rtrim
string

select rtrim(' abc ');

[空格]abc

12、正则表达式替换函数：regexp_replace
语法：regexp_replace(string A, string B, string C)
操作类型：string

select regexp_replace('foobar', 'oo|ar', '');

>>fb

13、正则表达式解析函数：regexp_extract
语法：regexp_extract(string subject, string pattern, int index)
类型：string
将字符串 subject 按照 pattern 正则表达式的规则拆分，返回 index 指定的字符。

select regexp_extract('foothebar', 'foo(.*?)(bar)', 0);

>>foothebar

select regexp_extract('foothebar', 'foo(.*?)(bar)', 1);

>>the

select regexp_extract('foothebar', 'foo(.*?)(bar)', 2);

>>bar

hive农场案例-农产品价格数据分析

创建表

create external table farm(
name string,
price float,
crawl_time string,
market string,
provience string,
city string
)
row format delimited fields terminated by '\t';
load data local inpath '/root/farm.tsv' into table farm;


location '/farm_product';

（1）统计每个省份的农产品市场总数

当出现聚合函数的时候，除了聚合类之外的其他字段必须跟在group by

第一种方法:

select a.provience,count(*)counts from
(select provience,market 
from farm
group by provience,market)a
group by a.provience
having a.provience <> "" ;

select a.provience,count(*)counts from
(select provience,market 
from farm 
group by provience,market)a
group by a.provience 
having a.provience <> "";

第二种方法

select provience,count(distinct market)counts
from farm group by provience having provience <> "";

（2）统计山东省售卖蛤蜊的农产品市场占全省农产品市场的比例

select round(b.counts/a.sums,2) 
from(select count (distinct market) sums from farm where provience="山东")a,
(select count (distinct market) counts from farm where provience="山东" and name="蛤蜊")b;

(3) 统计每个省农产品种类总数，找出排名前3的省份

select provience,count(distinct name) counts
from farm
group by provience
order by counts desc limit 3 ;

（4）统计排名前3，的省份共问拥有的农产品类型

select C.bname , count(*) counts
from
(select b.provience bprovince ,b.name bname from
(select provience,count(distinct name)counts
from farm
group by provience
order by counts desc limit 3) a
join farm b on a.provience=b.provience
group by b.provience ,b.name) C
group by C.bname
having counts=3 ;

3的省份

select provience,count(distinct name) counts
from farm
group by provience
order by counts desc limit 3 ;

（4）统计排名前3，的省份共问拥有的农产品类型

select C.bname , count(*) counts
from
(select b.provience bprovince ,b.name bname from
(select provience,count(distinct name)counts
from farm
group by provience
order by counts desc limit 3) a
join farm b on a.provience=b.provience
group by b.provience ,b.name) C
group by C.bname
having counts=3 ;