1/ DDL
1.1 内部表和外部表(external)
create table t_2(id int,name string,salary bigint,add string)
row format delimited
fields terminated by ',';
create external table t_3(id int,name string,salary bigint,add string)
row format delimited
fields terminated by ','
location '/aa/bb'
;
区别: 内部表的目录由hive创建在默认的仓库目录下:/user/hive/warehouse/....
意义: 通常,一个数据仓库系统,数据总有一个源头,而源头一般是别的应用系统产生的,
其目录无定法,为了方便映射,就可以在hive中用外部表进行映射;并且,就算在hive中把
这个表给drop掉,也不会删除数据目录,也就不会影响到别的应用系统;
2/ 分区关键字 PARTITIONED BY
create table t_4(ip string,url string,staylong int)
partitioned by (day string)
row format delimited
fields terminated by ',';
导入数据到不同的分区目录:
load data local inpath '/root/weblog.1' into table t_4 partition(day='2017-04-08');
load data local inpath '/root/weblog.2' into table t_4 partition(day='2017-04-09');
3/ 导入数据
3.1 将hive运行所在机器的本地磁盘上的文件导入表中
load data local inpath '/root/weblog.1' into[overwrite] table t_1;
3.2 将hdfs中的文件导入表中
load data inpath '/user.data.2' into table t_1;
不加local关键字,则是从hdfs的路径中移动文件到表目录中;
3.3 从别的表查询数据后插入到一张新建表中
create table t_1_jz
as
select id,name from t_1;
3.4 从别的表查询数据后插入到一张已存在的表中
加入已存在一张表:可以先建好:
然后从t_1中查询一些数据出来插入到t_1_hd中:
insert into table t_1_hd
select
id,name,add
from t_1
where add='handong';
4/ 导出数据
4.1 将数据从hive的表中导出到hdfs的目录中
insert overwrite directory '/aa/bb'
select * from t_1 where add='jingzhou';
4.2 将数据从hive的表中导出到本地磁盘目录中
insert overwrite local directory '/aa/bb'
select * from t_1 where add='jingzhou';
5/ HIVE的存储文件格式
HIVE支持很多种文件格式: SEQUENCE FILE | TEXT FILE | PARQUET FILE | RC FILE
试验:先创建一张表t_seq,指定文件格式为sequencefile
create table t_seq(id int,name string,add string)
stored as sequencefile;
然后,往表t_seq中插入数据,hive就会生成sequence文件插入表目录中
insert into table t_seq
select * from t_1 where add='handong';
6/ 修改表的分区
6.1 添加分区
alter table t_4 add partition(day='2017-04-10') partition(day='2017-04-11');
添加完成后,可以检查t_4的分区情况:
show partitions t_4;
+-----------------+--+
|
+-----------------+--+
| day=2017-04-08
| day=2017-04-09
| day=2017-04-10
| day=2017-04-11
+-----------------+--+
然后,可以向新增的分区中导入数据:
--可以使用load
load data local inpath '/root/weblog.3' into table t_4 partition(day='2017-04-10');
0: jdbc:hive2://hdp-nn-01:10000> select * from t_4 where day='2017-04-10';
+--------------+-----------------------+---------------+-------------+--+
|
+--------------+-----------------------+---------------+-------------+--+
| 10.300.33.6
| 30.20.33.7
| 30.80.33.6
| 30.60.33.8
| 30.100.33.6
| 30.100.33.9
+--------------+-----------------------+---------------+-------------+--+
--还可以使用insert
insert into table t_4 partition(day='2017-04-11')
select ip,url,staylong from t_4 where day='2017-04-08' and staylong>30;
6.2 删除分区
alter table t_4 drop partition(day='2017-04-11');
7/ 修改表的列定义
添加列:
alter table t_seq add columns(address string,age int);
全部替换:
alter table t_seq replace columns(id int,name string,address string,age int);
修改已存在的列定义:
alter table t_seq change userid uid string;
8/ 显示命令
show tables
show databases
show partitions
例子:
show functions
-- 显示hive中所有的内置函数
desc t_name;
-- 显示表定义
desc extended t_name;
-- 显示表定义的详细信息
desc formatted table_name;
-- 显示表定义的详细信息,并且用比较规范的格式显示
9/ DML
9.1
load
insert
插入单条数据: insert into table t_seq values('10','xx','beijing',28);
9.2 多重插入
假如有一个需求:
insert overwrite table t_4_st_lt_200 partition(day='1')
select ip,url,staylong from t_4 where staylong<200;
insert overwrite table t_4_st_gt_200 partition(day='1')
select ip,url,staylong from t_4 where staylong>200;
但是以上实现方式有一个弊端,两次筛选job,要分别启动两次mr过程,要对同一份源表数据进行两次读取
如果使用多重插入语法,则可以避免上述弊端,提高效率:源表只要读取一次即可
from t_4
insert overwrite table t_4_st_lt_200 partition(day='2')
select ip,url,staylong where staylong<200
insert overwrite table t_4_st_gt_200 partition(day='2')
select ip,url,staylong where staylong>200;
10.SELECT
JOIN
inner join
left join | left outer join
right join
full outer join
left semi join
select a.* from t_a a left semi join t_b b on a.id=b.id;
本查询中,无法取到右表的数据
老版本中,不支持非等值的join
在新版中:1.2.0后,都支持非等值join,不过写法应该如下:
select a.*,b.* from t_a a,t_b b where a.id>b.id;
不支持的语法:
11. HIVE的自定义函数功能
案例:
有如下原始数据:
1,zhangsan:18:beijing|male|it,2000
2,lisi:28:beijing|female|finance,4000
3,wangwu:38:shanghai|male|project,20000
原始数据由某个应用系统产生在hdfs的如下目录中: /log/data/2017-04-10/
需要放入hive中去做数据挖掘分析
可以先建一张外部表,跟原始数据所在的目录关联:
create external table t_user_info(id int,user_info string,salary int)
row format delimited
fields terminated by ','
location '/log/data/2017-04-10/';
select * from t_user_info; 可得到结果如下:
+-----------------+----------------------------------+---------------------+--+
| t_user_info.id
+-----------------+----------------------------------+---------------------+--+
| 1
| 2
| 3
+-----------------+----------------------------------+---------------------+--+
上表不方便做细粒度的分析挖掘,需要将user_info字段拆解成多个字段,用hive自带的函数不方便,
产生一个需求:自定义一个函数来实现拆解功能
自定义函数的步骤:
a、先开发一个java程序
public class UserInfoParser extends UDF {
}
b、将java程序打成jar包,上传到hive所在的机器上
c、在hive的命令行中输入以下命令,将程序jar包添加到hive运行时的classpath中:
hive> add jar /root/udf.jar;
d、在hive中创建一个函数名,映射到自己开发的java类:
hive> create temporary function uinfo_parse as 'com.doit.hive.udf.UserInfoParser'
e、接下来就可以使用自定义的函数uinfo_parse了
用函数拆解原来的字段,然后将结果保存到一张明细表中:
create table t_u_info
as
select
id,
uinf_parse(user_info,1) as uname,
uinf_parse(user_info,2) as age,
uinf_parse(user_info,3) as addr,
uinf_parse(user_info,4) as sexual,
uinf_parse(user_info,5) as hangye,
salary
from
t_user_info;
本文转载自公众号