Hive基本操作

一、概述

• Hive是建立在HDFS之上的数据仓库，所以Hive的数据全部存储在HDFS上。

• Hive的数据分为两部分，一部分是存在HDFS上的具体数据，一部分是描述这些具体数据的元数据信息，一般Hive的元数据存在MySQL上。

• Hive是类SQL语法的数据查询、计算、分析工具，执行引擎默认的是MapReduce，可以设置为Spark、Tez。

• Hive分内部表和外部表，外部表在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数 据会被一起删除，而外部表只删除元数据，不删除数据。

二、基本操作

hive命令行：$HIVE_HOME/bin./hive

2.1创建数据库
hive> create database IF NOT EXISTS test COMMENT ‘数据库注释’ LOCATION ‘/hivedata’ WITH DBPROPERTIES (‘creater’=‘k’,‘date’=‘20190229’)；

IF NOT EXISTS：如果不存在则创建

COMMENT：添加注释

LOCATION：指定hdfs存放路径

WITH DBPROPERTIES：添加自定义属性

2.2查询、删除数据库
hive> desc database extended test;
hive> drop database test CASCADE;
2.3数据库切换
hive> use test;
2.4常用数据类型
int：整型
bigint：长整型
float：浮点型
double：双精度
string：字符串
2.5创建表

hive> CREATE EXTERNAL TABLE ruozedata_person

    > (id int comment 'this is id', name string comment 'this id name' )

    > comment 'this is ruozedata_person'

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '\t'

  > location  '/user/hive/warehouse/test.db/emp';

EXTERNAL：创建外部表的关键字，默认是内部表

comment：添加注释，跟在字段后就是字段的注释，跟在表后就是表的注释

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’：指定加载数据的列分隔符为制表符

location：指定表数据存放路径
like方式创建表，复制表结构

hive> CREATE external table ruozedata_emp2 like emp location '/user/hive/warehouse/test.db/emp';

select方式创建表，可以顺带复制数据

hive> create table emp2 as select * from emp;
2.6 查看表结构
hive> desc formatted ruozedata_person;
2.7 修改表
修改表名：
hive> alter table ruozedata_person rename to person;
修改字段：
hive> CREATE TABLE test_change (a int, b int, c int);
// First change column a’s name to a1.
hive> ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1’s name to a2, its data type to string, and put it after column b.
hive> ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table’s structure is: b int, a2 string, c int.
// Then change column c’s name to c1, and put it as the first column.
hive> ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table’s structure is: c1 int, b int, a2 string.
// Add a comment to column a1
hive> ALTER TABLE test_change CHANGE a1 a1 INT COMMENT ‘this is column a1’;
2.8删除表
hive> drop table person;

三、insert数据

• insert方式插入数据(追加)
  hive> insert into emp2 select * from emp;
• insert方式插入数据(覆盖)
  hive> insert overwrite table emp2 select * from emp;
  注意：emp2和emp结构要一致，字段顺序要一致。
• insert into values
  hive> insert into a(id,name) values(1,‘ruoze’);
  注意：insert into values的方式不是直接写数据到原表上，而是新建临时表存储数据，然后把数据cp一份到目标表的路径里。

四、数据查询

4.1 group by

求每个部门的平均工资大于2000的部门

hive> select deptno, avg(salary) from emp group by deptno having avg(salary)>2000;

4.2 case when then

hive> select ename,salary,

    > case

    > when salary>1 and salary<=1000 then 'lower'

    > when salary>1000 and salary<=2000 then 'middle'

    > when salary>2000 and salary<=4000 then 'high'

    > else 'highest'

    > end

    > from emp;

4.3 join查询
inner join：两边都有的数据
full join：笛卡尔
left join：以左表为准
right join：以右表为准
4.4 分区表(静态分区)
create table test(no int,name string) partitioned by (dt int)
row format delimited fields terminated by ‘/t’;
4.5 数据插入静态分区表
insert into test partition(dt=2019) select no,name from test2;
4.6 数据插入静态分区表(覆盖)
insert overwrite table test partition(dt=2019) select no,name from test2;
4.7 动态分区
注意：insert into=insert into table，动态分区需要设置参数set hive.exec.dynamic.partition.mode=nonstrict，否则插入报错。动态分区和静态分区没有任何区别，除了以上设置和数据的录入方式
insert into test( no,name,dt) partition(dt=2019) select no,name,dt from test2;
动态分区要求分区字段的位置要放在select最后一个，如果是多级分区则按照顺序放到最后，不要求名称一致。
4.8 多级分区
与单分区的区别：partitioned by (dt int,dp int)