数据仓库技术及应用（内部表与外部表）

一、内部表

1.Hive内部表的定义

内部表(managed table): 未被external修饰的表，也叫管理表。

2.创建内部表

# 创建内部表
create table student1(
id int,
name string)
row format delimited fields terminated by '\t'
stored as textfile;

 3.查看表格式化数据

# 查看表格式化数据
desc formatted student1;

4.上传表数据并查询表数据存储在HDFS的文件

# 上传表数据并查询表数据存储在HDFS的文件
dfs -put /opt/module/data/student.txt /user/hive/warehouse/temp.db/student1;

# 查询表存储数据（DATA）
select * from student1;

 5.查询表的元数据（METADATA），元数据在mysql

select * from tbls;

6.删除 表并查看表data和metadata

drop table student1;
# mysql 查询
select * from tbls;

注意：登录到MYSQL数据库中    在hive下 退出 （exit或Ctrl + C）

# mysql -u用户名 -p密码
mysql -uroot -phadoop

进入到mysql中 ，然后use hive

二、外部表

1.Hive外部表的定义

外部表(external table):被external修饰的表。

2.创建外部表

# 创建外部表
create external table student2(
id int,
name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/tmp/student';

3.查看表格式化数据 

desc formatted student2;

4.上传表数据并查询表数据存储在HDFS的文件

# 上传表数据并查询表数据存储在HDFS的文件
dfs -put /opt/module/data/student.txt /tmp/student;

5.查看表数据DATA 

# 查询表数据DATA
select * from students;
# mysql 查询表的元数据
select * from tbls;

 6.删除表并查看表data和metadata

# 删除表
drop table student2;
# 查看data未被删除
# 查看metadata已被删除
select * from tbls;

三、区别

内部表&外部表

内部表：删除表会删除表的元数据(metadata)和表数据（data）。

外部表：删除表会删除表的元数据(metadata)，但不会删除表数据（data）。

四、数据管理方式 

数据(data)都存储在HDFS上，内部表由Hive自身管理，外部表数据由HDFS管理；

元数据(metadata)都存储在mysql上，由mysql管理。

五、内外部表的使用场景

内部表被drop后，表的data和metadata都会被删除；外部表被drop后，表的metadata会被删除，但是data不会被删除。如果不小心删掉外部表，重新建表location到数据存放在HDFS的相应路径即可。

使用场景：

（1）外部表：因其指删除表时不会删除HDFS上的数据，安全性相对较高，且指定目录的特性，适合数据需要共享或者数据的处理需要 hive和其他工具一起处理的场景。

（2）内部表：对原始数据或比较重要的中间数据进行建表存储。

六、快速映射表

1.创建表的时候指定分隔符

create [external] table 表名(字段名 字段类型) row format delimited fields terminated by “分隔符”;

2.加载数据:如果加上local则是在linux上加载数据

load data [local] inpath ‘结构化数据文件’ into table 表名;

七、insert插入数据

从其他表查询数据’追加’插入到当前表中: insert into [table] 表名 select 语句;

从其他表查询数据’覆盖’插入到当前表中: insert overwrite table 表名 select 语句。

insert追加数据

# 创建一个search_log_cp表,然后从search_log查询数据插入到新表中
create table search_log_cp(
    dt string,
    uid string,
    word string,
    url string
)row format delimited
fields terminated by '\t';

# 从search_log表中查所有数据,直接插入到search_log_cp表
insert into table search_log_cp select * from search_log;
# 查看数据
select * from search_log_cp;

insert覆盖数据

# 若search_log表中数据改变了,要求把最新的数据更新到search_log_cp表中
insert overwrite table search_log_cp select * from search_log;
# 查看数据
select * from search_log_cp;

一星陨落，黯淡不了星空灿烂；一花凋零，荒芜不了整个春天!(＾－＾)V