Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language),因此很容易将 Hive 理解为数据库。其实从结构上来看,Hive 和数据库除了拥有类似的查询语言,再无类似之处。
目录
Hive中的库表基础信息查看
--查看有哪些数据库
show databases;--查看当前数据库下有哪些表
show tables;--正则表达式显示表
show tables like '*cc*';--查看Hive支持函数
show functions;--查询表字段及注释
desc table_name;--查询建表语句
show create table_name;--查询表分区
show partitions table_name;--查看表的结构,字段,分区等情况
desc table_name;
Hive中增删改查操作
--给表重命名
alter table old_table_name rename to new_old_name;--清除表数据
truncate table table_name;--删除表
drop table table_name;
--创建员工1信息表,并指定行的格式
create table table_name(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
PARTITIONED by(inc_day string)
STORED AS ORC
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';--加载数据入员工表,local 表示数据从本地获取,未指定则是指hdfs,这是DML语法
LOAD DATA LOCAL INPATH '/home/hadoop/data/table_name_data.txt' OVERWRITE INTO TABLE table_name ;
--拷贝表结构
create table table_name_01 like table_name;
--拷贝表结构以及表数据,需要跑MR
create table table_name_01 as select * from table_name;
--创建外部表,用external修饰,其它语法与内部表一致
create external table table_name_01 like table_name;
--增加列字段(最后)
alter table table_name add columns (
name string comment '名字',
age int comment '年龄',
height float comment '身高');--改变列字段名称
alter table table_name change column old_column_name new_column_name column_type;--修改表字段注释
alter table table_name change [cloumn] col_old_name col_new_name column_type [conmment col_conmment] [first|after column_name];
alter table table_name change cloumn col_old_name col_new_name column_type conmment col_conmment;--增加一个字段并添加注释
alter table table_name add columns (col_name data_type comment 'col_comment');--删除列
alter table table_name replace columns (col_name data_type, col_name_2 data_type_2);--插入数据
insert into table_1 select * from table_2; --在table_1后追加数据
insert overwrite table table_1 select * from table_2; --先将table_1中数据清空,然后添加数据
Hive中分区操作
--增加分区
alter table table_name add if not exists partition (inc_day = '20190601');--删除分区
alter table table_name add if not exists partition (inc_day = '20190601');--分区 插入数据
insert into table_name partition (inc_day = '20190601')
select * from table_2;insert overwrite table table_name partition (inc_day = '20190601')
select * from table_2;
正则表达式替换函数 Regexp_replace
正则替换是常用的字符串替换函数
语法:regexp_replace(string subject, string pattern, string str)
参数说明:subject为被替换的字符串,pattern为正则表达式,str为替换正则表达式(匹配到的字符串)的字符串
描述:将字符串subject中,符合正则表达式pattern的字符串,替换为字符串str,然后将替换后的整个字符串返回select regexp_replace('abcdefg','abc','ABA') as res;
--ABAdefgselect regexp_replace('h234ney', '\\d+', 'o');
--honey
正则表达式解析函数 Regexp_extract
正则表达式解析函数 Regexp_extract
语法:regexp_extract(string subject, string pattern, int index)
参数说明:subject为被解析的字符串,pattern为正则表达式,index为正则表达式中捕获分组的序号,取值范围是0~n(n为捕获分组个数)。
其中,index取值为 0:返回pattern匹配到的整个结果;
index取值为1:返回pattern中第1个捕获分组匹配到的结果;
index取值为n:返回pattern中第n个捕获分组匹配到的结果;
index取值小于0或者大于n:报错。
描述:将字符串subject,按照pattern正则表达式的规则拆分,返回index指定的字符。select regexp_extract('abcdefg','a(b)(.*?)(e)',0) as res;
--abcdeselect regexp_extract('abcdefg','a(b)(.*?)(e)',1) as res;
--bselect regexp_extract('abcdefg','a(b)(.*?)(e)',2) as res;
--cdselect regexp_extract('abcdefg','a(b)(.*?)(e)',3) as res;
--eselect regexp_extract('honeymoon', 'hon(.*?)(moon)', 0);
--honeymoonselect regexp_extract('honeymoon', 'hon(.*?)(moon)', 1);
--eyselect regexp_extract('honeymoon', 'hon(.*?)(moon)', 2);
--moonselect regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0),
--x=18abcselect regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','^x=([a-z]+)([0-9]+)',0),
--x=a3&x
正则表达式由标准的元字符(metacharacters)所构成:
Hive中日期转换函数
unix_timestamp:返回当前或指定时间的时间戳
from_unixtime:将时间戳转为日期格式
current_date:当前日期
current_timestamp:当前的日期加时间
to_date:抽取日期部分
year:获取年
month:获取月
day:获取日
hour:获取时
minute:获取分
second:获取秒
weekofyear:当前时间是一年中的第几周
dayofmonth:当前时间是一个月中的第几天
months_between: 两个日期间的月份,前-后
add_months:日期加减月
datediff:两个日期相差的天数,前-后
date_add:日期加天数
date_sub:日期减天数
last_day:日期的当月的最后一天--时间戳函数
--日期转时间戳:从1970-01-01 00:00:00 UTC到指定时间的秒数
select unix_timestamp(); --获得当前时区的UNIX时间戳
select unix_timestamp('2017-09-15 14:23:00');
select unix_timestamp('2017-09-15 14:23:00','yyyy-MM-dd HH:mm:ss');
select unix_timestamp('20170915 14:23:00','yyyyMMdd HH:mm:ss');--时间戳转日期
select from_unixtime(1505456567);
select from_unixtime(1505456567,'yyyyMMdd');
select from_unixtime(1505456567,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'); --获取系统当前时间--获取当前日期: current_date
select current_date from dual
2017-09-15--日期时间转日期:to_date(string timestamp)
select to_date('2017-09-15 11:12:00') from dual;
2017-09-15--计算两个日期之间的天数: datediff
select datediff('2017-09-15','2017-09-01') from dual;
14--日期增加和减少: date_add/date_sub(string startdate,int days)
select date_add('2017-09-15',1) from dual;
2017-09-16
select date_sub('2017-09-15',1) from dual;
2017-09-14
--to_date:日期时间转日期函数
select to_date('2015-04-02 13:34:12');
输出:2015-04-02
--year:返回日期中的年
select year('2015-04-02 11:32:12');
输出:2015--month:返回日期中的月份
select month('2015-12-02 11:32:12');
输出:12--day:返回日期中的天
select day('2015-04-13 11:32:12');
输出:13--hour:返回日期中的小时
select hour('2015-04-13 11:32:12');
输出:11--minute:返回日期中的分钟
select minute('2015-04-13 11:32:12');
输出:32--second:返回日期中的秒
select second('2015-04-13 11:32:56');
输出:56--weekofyear:返回日期在当前周数
select weekofyear('2015-05-05 12:11:1');
输出:19--datediff:返回开始日期减去结束日期的天数
select datediff('2015-04-09','2015-04-01');
输出:8--date_sub:返回日期前n天的日期
select date_sub('2015-04-09',4);
输出:2015-04-05--date_add:返回日期后n天的日期
select date_add('2015-04-09',4);
输出:2015-04-13
Hive中行转列/列转行函数
列转行操作
concat(string s1, string s2, string s3)
这个函数能够把字符串类型的数据连接起来,连接的某个元素可以是列值。
如 concat( aa, ':', bb) 就相当于把aa列和bb列用冒号连接起来了,aa:bb。
cast
用法:cast(value as type)
功能:将某个列的值显示的转化为某个类型
例子:cast(age as string ) 将int类型的数据转化为了String类型
concat_ws(seperator, string s1, string s2...)
功能:制定分隔符将多个字符串连接起来,实现“列转行”
例子:常常结合group by与collect_set使用
collect_set():纵向转横向,将多行数写入一行,此方法会对该列去重
collect_list():作用同上,但是该方法不会对该列去重
此时,默认的数据之间的分割符是逗号(,),也可以使用concat_ws()方法来指定分隔符
user_id order_id
104399 1715131
104399 2105395
104399 1758844
104399 981085
104399 2444143
104399 1458638
104399 968412
104400 1609001
104400 2986088
104400 1795054select user_id,
concat_ws(',',collect_list(order_id)) as order_value
from table_name
group by user_id
;--结果(简写)
user_id order_value
104399 1715131,2105395,1758844,981085,2444143使用函数:concat_ws(',',collect_set(column))
说明:collect_list 不去重,collect_set 去重。 column的数据类型要求是stringsplit 切割函数
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str,会返回分割后的字符串数组
select split(order_value, ',') from table_name;
--1715131 2105395 1758844 981085 2444143select split(order_value, ',')[0] from table_name;
--1715131
行转列操作
lateral view用于和split、explode等UDTF一起使用的,能将一行数据拆分成多行数据,在此基础上可以对拆分的数据进行聚合,lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,lateral view在把结果组合,产生一个支持别名表的虚拟表。其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行。
user_id order_value
104408 2909888,2662805,2922438,674972,2877863,190237
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406 1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405 153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404 1815641,108556,3110738,2536910,1977293,424564
104403 253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402 3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401 814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400 1609001,2986088,1795054,429550,1812893
104399 1715131,2105395,1758844,981085,2444143,1458638,968412select user_id,order_value,order_id
from table_name
lateral view explode(split(order_value,',')) num as order_id
;--结果
user_id order_value order_id
104408 2909888,2662805,2922438,674972,2877863,190237 2909888
104408 2909888,2662805,2922438,674972,2877863,190237 2662805
104408 2909888,2662805,2922438,674972,2877863,190237 2922438
104408 2909888,2662805,2922438,674972,2877863,190237 674972
104408 2909888,2662805,2922438,674972,2877863,190237 2877863
104408 2909888,2662805,2922438,674972,2877863,190237 190237
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128 2982655
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128 814964
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128 1484250
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128 2323912
其他
case when函数
case
when os = 'android' then 'android'
when os = 'ios' then 'iPhone'
else 'PC'
end as os,
row_number()函数
语法:row_number() over (partition by 字段a order by 计算项b desc ) rankpartition by:类似hive的建表,分区的意思;
order by :排序,默认是升序,加desc降序;
这里按字段a分区,对计算项b进行降序排序