Hive的基本操作、创建内部表、创建外部表、创建分桶表、创建分区表 04

逃离我推掉我的手 2022-03-01 15:14 662阅读 0赞

# 1. 创建数据库 #

*  创建数据库不指定位置

create database if not exists myhive;

use myhive;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70]  
**hive的表存放位置模式是由hive-site.xml当中的一个属性指定的**

<name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>

*  创建数据库并指定hdfs存储位置

create database myhive2 location '/myhive2'

*  查看数据库详细信息

desc database myhive2;

*  查看数据库更多详细信息

desc databasse extended myhive2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 2]

# 2. 创建数据库表 #

## 2.1 管理表（内部表） ##

*  创建表初体验

use myhive;
    create table stu(id int, name string);
    insert into stu values (1,"zhangsan");
    select * from stu;

hive建表时候的字段类型  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 4]

*  创建表并指定字段之间的分隔符

create table if not exists stu2(id int, name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

*  根据查询结果创建表

create table stu3 as select * from stu2

![在这里插入图片描述][20190322215736589.png]

*  根据已存在的表结果创建表

create table stu4 like stu2

![在这里插入图片描述][20190322215831507.png]

*  查询表的类型

desc formatted stu2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 5]

## 2.2 外部表 ##

**外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉**

*  管理表和外部表的使用场景：  
    每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

### 2.1 分别创建老师与学生外部表， 并想表中加载数据 ###

*  老师表

create external table techer (t_id string,t_name string) row format delimited fields terminated by '\t';

*  学生表

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

![在这里插入图片描述][20190322220400336.png]  
![在这里插入图片描述][20190322220405348.png]

*  从本地文件系统向表中加载数据

load data local inpath '/export/servers/hivedatas/student.csv' into table student;

*  加载数据并覆盖已有数据

load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;

**外部表，删除表后，hdfs的数据依然存在，这是内部表和外部表的区别**

## 3. 分区表 ##

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了

*  创建分区表语法

create table score(s_id string, c_id string, s_score int) partitioned by(month string) row format delimited fields terminated by '\t';

*  创建一个表带多个分区

create table score2(s_id string, c_id string, s_score int) partitioned by (year string, month string, day string) row format delimited fields terminated by '\t';

*  加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score pattition(month='201806');

*  加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

*  多个分区联合查询使用union all 来实现

select * from score where month = '201806' union all select * from score where month = '201806';

*  查看分区

show  partitions  score;

*  添加一个分区

alter table score add partition(month='201805');

*  同时添加多个分区

alter table score add partition(month='201804') partition(month = '201803');

**注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹**

*  删除分区

alter table score drop partition(month = '201806');

## 4. 分通表 ##

*  开启hive的桶表功能

set hive.enforce.bucketing=true;

*  设置reduce的个数

set mapreduce.job.reduces=3;

*  创建桶表

create table course(c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

*  加载数据，**只能通过查询另一个表来加载到分通表中**  
    创建普通表：

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);

# 3. hive表中加载数据 #

## 3.1 直接向分区表中插入数据 ##

create table score3 like score;
    insert into table score3 partition(month ='201807') values ('001','002','100');

## 3.2 通过查询插入数据 ##

*  通过load方式加载数据

load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

*  通过查询方式加载数据

create table score4 like score;
    insert overwrite table score4 partition(month='201806') select s_id,c_id,s_score from score;

# 4. 清空表 #

truncate table score6;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70]: /images/20220301/ee8d9c18c4f24293829ac5e00dde76f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 1]: /images/20220301/6dcac75e647840729fa8d3d07e02c82d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 2]: /images/20220301/69718d0d254b446f94a427b7a8946876.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 3]: /images/20220301/7392c3e2caee430cb6ec77de9176fbf7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 4]: /images/20220301/fe74e4453ce14c43baef748b939fec9b.png
[20190322215736589.png]: /images/20220301/f4bc705c24074497a1e9503a68949cf7.png
[20190322215831507.png]: /images/20220301/aef1fb62ad474dfd94bf60a22f2b7b13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY0MTkwOQ_size_16_color_FFFFFF_t_70 5]: /images/20220301/b38071eada4240169f6bd416046a3166.png
[20190322220400336.png]: /images/20220301/fdde0c3b2a864917a9ac8464ffc13240.png
[20190322220405348.png]: /images/20220301/9756cfab04ee4bc09c1c64e379f1f20d.png