Hive基本操作

---

一，DDL操作

　　1，创建表

　　创建内部表　

create table if not exists mytable(sid int, sname string )
row format delimited fields terminated by  ','  stored as textfile;

　　创建内部表

create table if not exists pageview(pageid int, page_url string )
row format delimited  fields terminated by ','
location 'hdfs://192.168.184.131:9000/user/hive/warehouse/';

　　创建分区表      分表就是在加入数据前，对表进行相应需求的分开存储。

create table if exists invites (id int , name string  )
partitioned by (ds string)
row format delimited fields terminated by ','  lines terminated by '\n' stored as textfile;

 　　创建分桶表　

//开启分桶
set hive.enforce.bucketing = true;

　　对于每一个表或者是分区，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive是针对某一列进行分桶。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中。分桶的好处是可以获得更高的查询处理效率。使取样更高效。

　　当从桶表中进行查询时，hive会根据分桶的字段进行计算分析出数据存放的桶中，然后直接到对应的桶中去取数据，这样做就很好的提高了效率。

1 create table student (id int , name string )
2 clustered by (id) sorted by(name) into 4 buckets
3 row format delimited fields terminated by ',';

 

　　2，修改表

　　添加分区

alter table student_p add partition(part='a') partition (part='b');

　　删除分区

alter table student_p drop partition(part='a');

　　重命名表

alter table table_name rename to new_table_name;

　　增加列

alter table student add columns (sex  string);

　　更新列

alter table student replace columns (id int ,age int ,name string);

　　3，显示命令

　　show tables显示表

　　show databases显示数据库

　　show partitions显示分区表的分区

　　show functions显示方法

　　desc extended t_name;

　　desc formatted table_name;

 

二，DML操作

　　1，Load  只是单纯的复制/移动操作，将数据文件移动到Hive表相对应的位置

　　加载相对路径的数据

load data local  inpath  'buckets.txt' into table student_p partition(stat_data='20131231');

　　加载绝对路径数据

load data local inpath '/root/app/datafile/buckets.txt'  into table student_p partition(stat_data='20131230');

　　加载包含模式数据

load data local inpath 'hdfs://192.168.184.131:9000/user/hive/warehouse/student/stat_data=20131230/buckets.txt' 

into table student_p partition(stat_data='20131229');

　　overwrite关键字

load data local inpath 'buckedts.txt'  overwrite into table student  partition(stat_data='20131229');

　　2，Insert

　　基本模式插入（非分表插入）

insert into table student3 (key1,key2,key3) values('11','wodsa','222');

　　基本模式插入（分表插入）

insert into table student3 partition(type='xiaomi') select id,name,price from student3 where type='huawei';

　　高级语法，多条插入

from  student
insert overwrite table  student  partition(stat_data='20140102')
select id,age,name where stat_data='201401229'
insert overwrite table  student  partition(stat_data='20140103')
select id,age,name where  stat_data='20140125';

　　自动分区模式

set hive.exec.dynamic.partition=true; //使用动态分区
set hive.exec.dynamic.partition.mode=nonstrict;//无限制模式 

insert into table student1 partition(stat_data)
select id,age,name,stat_data from student where stat_data='20140101';

 

　　3，导出表数据

　　单行导出到本地

insert overwrite local directory '/home/data/student' select *from student3;

　　多行导出到本地

from student_p
insert overwrite local directory '/home/data/student1' select Sno,Sname,Sage
insert overwrite local directory '/home/data/student2' select Sno,Sname,Sage;

　　导出数据到HDFS

insert into directory 'hdfs://server1:9000/user/hive'
select * from student1;

 

　　4,Select

　　order by ，order by 会对输入做全局排序

select *from good order by price desc;

　　sort by不是全局排序，其在数据进入reducer前完成排序

select *from good sort by price desc;　

　　distribute by根据distribute by指定的内容将数据分到同一个reducer

   　Cluster by 除了具有Distribute by的功能外，还会对该字段在进入reduce之前进行排序。

 

三，Hive Join

　　1，Hive支持等值连接，外连接，内连接，左右连接，支持多与2个表的连接，不支持非等值的连接。

　　2，join 时，每次 map/reduce 任务的逻辑:reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。

　　3，LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况：

　　右连接-->右表的所有数据和左表的关联数据。

select a.id,a.name,b.id,b.ordernum,b.price from customers a 
right outer join orders b on a.id = b.cid;

　　左连接-->左表的所有数据和右表的关联数据。

hive (hive1)> select a.id,a.name,b.id,b.ordernum,b.price from customers a 
left  join orders b on a.id = b.cid;

　　内连接-->两表的关联数据。

select a.id,a.name,b.id,b.ordernum,b.price from customers a 
join orders b on a.id = b.cid;

　　4，Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。多连接时为了防止左表数据为null，影响右表的连接。

 

---