Hadoop 上Hive 的操作

数据dept表的准备：

--创建dept表
CREATE TABLE dept(
  deptno int, 
  dname string, 
  loc string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  STORED AS textfile;

数据文件准备：

vi detp.txt
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

数据表emp准备：

CREATE TABLE emp(
  empno int, 
  ename string, 
  job string, 
  mgr int, 
  hiredate string, 
  sal int, 
  comm int, 
  deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS textfile;

表emp数据准备：

vi emp.txt
7369,SMITH,CLERK,7902,1980-12-17,800,null,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,null,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
7788,SCOTT,ANALYST,7566,1987-04-19,3000,null,20
7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23,1100,null,20
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-02,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10

把数据文件装到表里

load data local inpath '/home/hadoop/tmp/dept.txt' overwrite into table dept;
load data local inpath '/home/hadoop/tmp/emp.txt' overwrite into table emp;

查询语句

select d.dname,d.loc,e.empno,e.ename,e.hiredate from dept d join emp e on e.deptno = d.deptno ;
  * 可以看到走的是map reduce 程序

二、Hive分区
hive分区的目的
  * hive为了避免全表扫描，从而引进分区技术来将数据进行划分。减少不必要数据的扫描，从而提高效率。

hive分区和mysql分区的区别
  * mysql分区字段用的是表内字段；而hive分区字段采用表外字段。
 

hive的分区技术
  * hive的分区字段是一个伪字段，但是可以用来进行操作。
  * 分区字段不进行区分大小写
  * 分区可以是表分区或者分区的分区，可以有多个分区


hive分区根据
  * 看业务，只要是某个标识能把数据区分开来。比如：年、月、日、地域、性别等



分区关键字
  * partitioned by(字段)


分区本质
  * 在表的目录或者是分区的目录下在创建目录，分区的目录名为指定字段=值

创建分区表：

create table if not exists u1( id int, name string, age int ) partitioned by(dt string) row format delimited fields terminated by ' ' 
stored as textfile;

数据准备：

[hadoop@master tmp]$ more u1.txt 
1 xm1 16
2 xm2 18
3 xm3 22

加载数据：

load data local inpath '/home/hadoop/tmp/u1.txt'  into table  u1 partition(dt="2018-10-14");

查询：

hive> select * from u1;
OK
1       xm1     16      2018-10-14
2       xm2     18      2018-10-14
3       xm3     22      2018-10-14
Time taken: 5.919 seconds, Fetched: 3 row(s)

 查询分区：

hive> select * from u1 where dt='2018-10-15';
OK
1       xm1     16      2018-10-15
2       xm2     18      2018-10-15
3       xm3     22      2018-10-15
Time taken: 0.413 seconds, Fetched: 3 row(s)

Hive的二级分区

创建表u2

create table if not exists u2(id int,name string,age int)
partitioned by(month int,day int) row format delimited fields terminated by ' ' stored as textfile;

导入数据：

load data local inpath '/home/hadoop/tmp/u2.txt' into table u2 partition(month=9,day=14);

 数据查询：

hive> select * from u2;
OK
1       xm1     16      9       14
2       xm2     18      9       14
Time taken: 0.303 seconds, Fetched: 2 row(s)

分区修改：

查看分区：

hive>  show partitions u1;
OK
dt=2018-10-14
dt=2018-10-15

增加分区：

 > alter table u1 add partition(dt="2018-10-16");
  OK

查看新增加的分区：

hive>  show partitions u1;
OK
dt=2018-10-14
dt=2018-10-15
dt=2018-10-16
Time taken: 0.171 seconds, Fetched: 3 row(s)

删除分区：

hive> alter table u1 drop partition(dt="2018-10-15");
Dropped the partition dt=2018-10-15
OK
Time taken: 0.576 seconds
hive>  select * from u1 ;
OK
1       xm1     16      2018-10-14
2       xm2     18      2018-10-14
3       xm3     22      2018-10-14
Time taken: 0.321 seconds, Fetched: 3 row(s)

 三、hive动态分区

 hive配置文件hive-site.xml 文件里有配置参数：

hive.exec.dynamic.partition=true;                 是否允许动态分区
hive.exec.dynamic.partition.mode=strict/nostrict; 动态区模式为严格模式
​    strict:  严格模式，最少需要一个静态分区列(需指定固定值)
​    nostrict:非严格模式，允许所有的分区字段都为动态。
hive.exec.max.dynamic.partitions=1000;        允许最大的动态分区
hive.exec.max.dynamic.partitions.pernode=100; 单个节点允许最大分区

创建动态分区表

动态分区表的创建语句与静态分区表相同，不同之处在与导入数据，静态分区表可以从本地文件导入，但是动态分区表需要使用from…insert into语句导入。

create table if not exists u3(id int,name string,age int) partitioned by(month int,day int) 
row format delimited fields terminated by ' ' stored as textfile;

导入数据，将u2表中的数据加载到u3中：

from u2
insert into table u3 partition(month,day)
select id,name,age,month,day;

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
解决方法：

要动态插入分区必需设置hive.exec.dynamic.partition.mode=nonstrict
hive> set hive.exec.dynamic.partition.mode;
hive.exec.dynamic.partition.mode=strict
hive>  set hive.exec.dynamic.partition.mode=nonstrict;

然后再次插入就可以了

查询：

hive> select * from u3;
OK
1       xm1     16      9       14
2       xm2     18      9       14
Time taken: 0.451 seconds, Fetched: 2 row(s)

hive分桶

分桶目的作用
  * 更加细致地划分数据；对数据进行抽样查询，较为高效；可以使查询效率提高

  * 记住，分桶比分区，更高的查询效率。
分桶原理关键字
  * 分桶字段是表内字段，默认是对分桶的字段进行hash值，然后再模于总的桶数，得到的值则是分区桶数。每个桶中都有数据，但每个桶中的数据条数不一定相等。
     bucket
     clustered by(id) into 4 buckets
分桶的本质
  * 在表目录或者分区目录中创建文件。

分桶案例
  * 分四个桶

create table if not exists u4(id int, name string, age int) partitioned by(month int,day int)
clustered by(id) into 4 buckets row format delimited fields terminated by ' ' stored as textfile;

对分桶的数据不能使用load的方式加载数据，使用load方式加载不会报错，但是没有分桶的效果。

为分桶表添加数据，需要设置set hive.enforce.bucketing=true;

首先将数据添加到u2表中

1 xm1 16
2 xm2 18
3 xm3 22
4 xh4 20
5 xh5 22
6 xh6 23
7 xh7 25
8 xh8 28
9 xh9 32

load data local inpath '/home/hadoop/tmp/u2.txt' into table u2 partition(month=9,day=14);

加载到桶表中：

from u2 insert into table u4 partition(month=9,day=14) select id,name,age  where month = 9  and day = 14;

2019-03-31 15:43:26,755 Stage-1 map = 0%,  reduce = 0%
2019-03-31 15:43:34,241 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.85 sec
2019-03-31 15:43:41,681 Stage-1 map = 100%,  reduce = 25%, Cumulative CPU 1.95 sec
2019-03-31 15:43:45,855 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 3.21 sec
2019-03-31 15:43:47,927 Stage-1 map = 100%,  reduce = 75%, Cumulative CPU 4.35 sec
2019-03-31 15:43:48,959 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5.35 sec
MapReduce Total cumulative CPU time: 5 seconds 350 msec
Ended Job = job_1554061731326_0001
Loading data to table db_hive.u4 partition (month=9, day=14)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 5.35 sec   HDFS Read: 20301 HDFS Write: 405 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 350 msec

加载日志可以看到有：Map: 1 Reduce: 4

对分桶进行查询：tablesample(bucket x out of y on id)
  *  x:表示从哪个桶开始查询
  *  y:表示桶的总数,一般为桶的总数的倍数或者因子。
  *  x不能大于y。

hive> select * from u4;
OK
8       xh8     28      9       14
4       xh4     20      9       14
9       xh9     32      9       14
5       xh5     22      9       14
1       xm1     16      9       14
6       xh6     23      9       14
2       xm2     18      9       14
7       xh7     25      9       14
3       xm3     22      9       14
Time taken: 0.148 seconds, Fetched: 9 row(s)

    > select * from u4 tablesample(bucket 1 out of 4 on id);
OK
8       xh8     28      9       14
4       xh4     20      9       14
Time taken: 0.149 seconds, Fetched: 2 row(s)
hive> select * from u4 tablesample(bucket 2 out of 4 on id);
OK
9       xh9     32      9       14
5       xh5     22      9       14
1       xm1     16      9       14
Time taken: 0.069 seconds, Fetched: 3 row(s)
hive> select * from u4 tablesample(bucket 1 out of 2 on id);
OK
8       xh8     28      9       14
4       xh4     20      9       14
6       xh6     23      9       14
2       xm2     18      9       14
Time taken: 0.089 seconds, Fetched: 4 row(s)
hive> select * from u4 tablesample(bucket 1 out of 8 on id) where age > 22;
OK
8       xh8     28      9       14
Time taken: 0.075 seconds, Fetched: 1 row(s)

随机查询：

select * from u4 order by rand() limit 3;

OK
1       xm1     16      9       14
3       xm3     22      9       14
6       xh6     23      9       14
Time taken: 20.724 seconds, Fetched: 3 row(s)  --走map reduce任务

    > select * from u4 tablesample(3 rows);
OK
8       xh8     28      9       14
4       xh4     20      9       14
9       xh9     32      9       14
Time taken: 0.073 seconds, Fetched: 3 row(s)

hive> select * from u4 tablesample(30 percent);
OK
8       xh8     28      9       14
4       xh4     20      9       14
9       xh9     32      9       14
Time taken: 0.058 seconds, Fetched: 3 row(s)

    > select * from u4 tablesample(3G);
OK
8       xh8     28      9       14
4       xh4     20      9       14
9       xh9     32      9       14
5       xh5     22      9       14
1       xm1     16      9       14
6       xh6     23      9       14
2       xm2     18      9       14
7       xh7     25      9       14
3       xm3     22      9       14
Time taken: 0.069 seconds, Fetched: 9 row(s)

hive> select * from u4 tablesample(3K);
OK
8       xh8     28      9       14
4       xh4     20      9       14
9       xh9     32      9       14
5       xh5     22      9       14
1       xm1     16      9       14
6       xh6     23      9       14
2       xm2     18      9       14
7       xh7     25      9       14
3       xm3     22      9       14
Time taken: 0.058 seconds, Fetched: 9 row(s)

* 分区与分桶的对比
* 分区使用表外的字段，分桶使用表内字段
* 分区可以使用load加载数据，而分桶就必须要使用insert into方式加载数据
* 分区常用；分桶少用

hive数据导入

* load从本地加载
* load从hdfs中加载
* insert into方式加载
* location指定
* like指定，克隆
* ctas语句指定(create table as)
* 手动将数据copy到表目录

hive数据导出
* insert into方式导出
* insert overwrite local directory:导出到本地某个目录
* insert overwrite directory:导出到hdfs某个目录

导出到文件

hive -S -e “use gp1801;select * from u2” > /home/out/02/result