大数据生态系统-Hive基本操作

大数据生态系统-Hive基本操作

---

学习目标

• 记忆Hive的HQL基本操作
• 记忆Hive的内部表和外部表区别
• 记忆分区表的使用

---

1-Hive HQL基本操作

• 创建数据库

CREATE DATABASE test;

• 显示所有数据库

SHOW DATABASES;

• 创建表

CREATE TABLE student(classNo string, stuNo string, score int) row format delimited
fields terminated by ',';

• row format delimited fields terminated by ‘,’ 指定了字段的分隔符为逗号，所以load数据的时候，load的文本也要为逗号，否则加载后为NULL。hive只支持单个字符的分隔符，hive默认的分隔符是\001

• 将数据load到表中

  • 在本地文件系统创建一个如下的文本文件：/root/tmp/student.txt

C01,N0101,82
C01,N0102,59
C01,N0103,65
C02,N0201,81
C02,N0202,82
C02,N0203,79
C03,N0301,56
C03,N0302,92
C03,N0306,72

load data local inpath '/root/tmp/student.txt'overwrite into table student;

• 这个命令将student.txt文件复制到hive的warehouse目录中，这个目录由hive.metastore.warehouse.dir配置项设置，默认值为/user/hive/warehouse。Overwrite选项将导致Hive事先删除student目录下所有的文件, 并将文件内容映射到表中。 Hive不会对student.txt做任何格式处理，因为Hive本身并不强调数据的存储格式。
• 查询表中的数据 跟SQL类似

hive>select * from student;

• 分组查询group by和统计 count

hive>select classNo,count(score) from student where score>=60 group by classNo;

从执行结果可以看出 hive把查询的结果变成了MapReduce作业通过hadoop执行

---

2-Hive的内部表和外部表

• 案例

  • 创建一个外部表student2

CREATE EXTERNAL TABLE student2 (classNo string, stuNo string, score int) row format
delimited fields terminated by ',' location '/root/tmp/student';

• 装载数据

load data local inpath '/root/tmp/student.txt' overwrite into table student2;

• 显示表信息

desc formatted table_name;

• 删除表查看结果

drop table student;

• 再次创建外部表 student2

• 不插入数据直接查询查看结果

select * from student2;

---

3-分区表

3.1什么是分区表

• 随着表的不断增大，对于新纪录的增加，查找，删除等(DML)的维护也更加困难。对于数据库中的超大型表，可以通过把它的数据分成若干个小表，从而简化数据库的管理活动，对于每一个简化后的小表，我们称为一个单个的分区。
• hive中分区表实际就是对应hdfs文件系统上独立的文件夹，该文件夹内的文件是该分区所有数据文件。
• 分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。
• 分类的标准就是分区字段，可以一个，也可以多个。
• 分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

3.2创建分区表

tom,4300
jerry,12000
mike,13000
jake,11000
rob,10000

 create table employee (name string,salary bigint) partitioned by (date1 string) row
  format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;

• 查看表的分区

show partitions employee;

• 添加分区

alter table employee add if not exists partition(date1='2018-12-01');

• 加载数据到分区

load data local inpath '/root/tmp/employee.txt' into table employee partition(date1='2018-12-01');

• 如果重复加载同名文件，不会报错，会自动创建一个*_copy_1.txt
• 外部分区表即使有分区的目录结构, 也必须要通过hql添加分区, 才能看到相应的数据

hadoop fs -mkdir /user/hive/warehouse/emp/dt=2018-12-04
hadoop fs -copyFromLocal /root/tmp/employee.txt /user/hive/warehouse/test.db/emp/dt=2018-12-04/employee.txt

- 此时查看表中数据发现数据并没有变化, 需要通过hql添加分区

alter table employee add if not exists partition(dt='2018-12-04');

- 此时再次查看才能看到新加入的数据

• 关于分区表的几点说明
• 分区字段不是数据文件中的列数据
  • 分区字段的值仅仅是一个目录名
  • 查看数据时, hive会自动添加分区列
  • 支持多级分区也就是多级子目录

动态分区

• 在写入数据时自动创建分区(包括目录结构)
• 创建表

create table employee2 (name string,salary bigint) partitioned by (date1 string) row
format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;

• 导入数据

insert into table employee2 partition(date1) select name,salary,date1 from employee;

• 使用动态分区需要设置参数

set hive.exec.dynamic.partition.mode=nonstrict;

---

4-总结

• HQL与SQL基本相同，需要注意建表的时候指定分隔符
• 内部表外部表区别：外部表数据可以在HDFS的任何位置，删除表时只删除元数据，内部表数据必须在指定位置，删除表时数据元数据一同删除
• 分区表就是在表文件夹下的子文件夹，通过分区表可以减少数据查询范围，提高查询效率，分区表中分区字段需要在建表时指定，增加新的分区字段值需要通过alter table partition来指定