HiveSQL常用数据处理语句

大数据学习 专栏收录该内容
6 篇文章 0 订阅

 

HiveSQL运行优化参数配置

HiveSQL常用数据处理语句

HiveSQL中复杂数据类型操作

 

        Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language),因此很容易将 Hive 理解为数据库。其实从结构上来看,Hive 和数据库除了拥有类似的查询语言,再无类似之处。

目录

Hive中的库表基础信息查看

Hive中增删改查操作

Hive中分区操作

正则表达式替换函数 Regexp_replace

正则表达式解析函数 Regexp_extract

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;
--ABAdefg

select 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;
--abcde

select regexp_extract('abcdefg','a(b)(.*?)(e)',1) as res;
--b

select regexp_extract('abcdefg','a(b)(.*?)(e)',2) as res;
--cd

select regexp_extract('abcdefg','a(b)(.*?)(e)',3) as res;
--e

select regexp_extract('honeymoon', 'hon(.*?)(moon)', 0);
--honeymoon

select regexp_extract('honeymoon', 'hon(.*?)(moon)', 1);
--ey

select regexp_extract('honeymoon', 'hon(.*?)(moon)', 2);
--moon

select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0),  
--x=18abc

select 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    1795054

select 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的数据类型要求是string

split 切割函数
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str,会返回分割后的字符串数组
select split(order_value, ',') from table_name;
--1715131 2105395 1758844 981085 2444143

select 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,968412

select 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 ) rank 

partition by:类似hive的建表,分区的意思;
order by :排序,默认是升序,加desc降序;
这里按字段a分区,对计算项b进行降序排序

 

 

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值