05 hive 内部表与外部表

建表语法

create [external] table 表名(
  字段名 字段类型 ,
 ...
)partitioned by (分区字段名 字段类型)
clustered by (分桶字段名) [sort by 排序字段名 asc|desc] into 桶数量 buckets 
row format delimited
fields terminated by '分隔符'
stored as textfile
location 'hdfs://域名:8020/user/hive/warehouse/库名.db/表名'
tblproperties ('k'='v') ;
注意: 关键字顺序是从上到下从左到右,否则报错

表分类

内部表:又叫管理表或者托管表——MANAGED_TABLE，管理元数据和业务数据
Hive创建内部表时，会将数据移动到数据仓库指向的路径，hive管理数据的生命周期；

外部表:又叫非管理表或者非托管表——EXTERNAL_TABLE，只对元数据有管理权
Hive创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。
外部表无法使用truncate语句清空列表，drop只能删除元数据，HDFS中仍可访问文件，可使用location重新对原地址数据进行关联

查看表格式化信息: desc formatted 表名; 
	-- 内部表类型: MANAGED_TABLE
	-- 外部表类型: EXTERNAL_TABLE

内部表和外部表区别

一是管理范围的区别：内部表对元数据和表数据有绝对管理权，外部表只对元数据有管理权，
二是建表语句的区别：外部表需要使用external关键字进行建表
三是建表/复制表的区别：外部表不能使用create table…as…方式进行建表
四是删除的区别：外部表无法使用truncate语句清空表内数据，只能使用drop删除表元数据，而内部表使用drop会删除元数据与业务数据

ACID

-- 内部表(又叫管理表或者托管表)
create table stu1(
    id int,
    name string
);
-- 外部表(又叫非管理表,非托管表)
create external table stu2(
    id int,
    name string
);

-- 演示内外部表的区别
-- 删除内部表(管理表/托管表),会删除表相关的所有数据
insert into stu1 values(1,'张三');
drop table stu1;
-- 删除外部表,只删除了元数据,hdfs中业务数据保留
insert into stu2 values(1,'张三');
drop table stu2;
-- 再次建表后,可以使用location重新关联原来hdfs保留的业务数据
create external table stu22(
    id int,
    name string
)location '/user/hive/warehouse/hive1.db/stu2';

内部表基本操作

知识点:

创建内部表:

create table [if not exists] 内部表名(
字段名 字段类型 ,
字段名 字段类型 , …
)[row format
delimited fields terminated by ‘字段分隔符’] ;

复制内部表:
方式1: like方式复制表结构 方式2: as 方式复制表结构和数据

删除内部表: drop table 内部表名;
注意: 删除内部表效果是mysql中表相关元数据被删除,同时存储在hdfs中的业务数据本身也被删除

查看表格式化信息: desc formatted 表名;

注意: 还可以使用truncate清空内部表数据 格式: truncate table 内部表名;

示例:

-- 操作表的前提:先有库并使用它
create database hive2;
use hive2;
-- 一.内部表的创建和删除
-- 1.演示创建内部表
-- 建表方式1
create table inner_stu1(
    id int,
    name string
);
-- 插入数据
insert into inner_stu1 values(1,'张三');

-- 建表方式2: 复制表结构
create table inner_stu2 like inner_stu1;
-- 插入数据
insert into inner_stu2 values(1,'张三');

-- 建表方式3: 复制表结构和数据
create table inner_stu3 as
select * from inner_stu1;


-- 2.演示查看内部表结构详细信息
-- 内部表类型: MANAGED_TABLE
desc formatted inner_stu1;
desc formatted inner_stu2;
desc formatted inner_stu3;


-- 3.演示内部表的删除
-- 删除内部表
drop table inner_stu3;-- 元数据和业务数据均被删除
-- 清空内部数据
truncate table inner_stu2;
-- 注意: delete和update不能使用
delete from inner_stu1;-- 报错
update inner_stu1 set name = '李四'; -- 报错

外部表基本操作

知识点:

创建外部表:(关键字：external)

create external table 外部表名(
字段名 字段类型 ,
字段名 字段类型 , …
)[row format delimited fields terminated by ‘字段分隔符’] ;

复制表: like方式复制表结构

注意: 外部表无法使用create...as方式创建表

删除外部表: drop table 外部表名;

注意: 删除外部表效果是mysql中元数据被删除,但是存储在hdfs中的业务数据本身被保留

查看表格式化信息: desc formatted 表名;

注意: 外部表不能使用truncate清空数据本身

示例:

-- 二.外部表的创建和删除
-- 1.外部的表创建
-- 建表方式1
create external table outer_stu1(
    id int,
    name string
);
-- 插入数据
insert into outer_stu1 values(1,'张三');

-- 建表方式2
create external table outer_stu2 like outer_stu1;
-- 插入数据
insert into outer_stu2 values(1,'张三');

-- 注意: 外部表不能使用create ... as 方式复制表
create external table outer_stu3 as
    select * from outer_stu1; -- 报错

-- 2.演示查看外部表结构详细信息
-- 外部表类型: EXTERNAL_TABLE
desc formatted outer_stu1;
desc formatted outer_stu2;


-- 3.演示外部表的删除
-- 删除表
drop table outer_stu2;
-- 注意: 外部表不能使用truncate关键字清空数据
truncate table outer_stu1; -- 报错
-- 注意: delete和update不能使用
delete from outer_stu1; -- 报错
update outer_stu1 set name = '李四'; -- 报错

查看/修改表

知识点:

查看所有表: show tables;
查看建表语句: show create table 表名;
查看表信息: desc 表名;
查看表结构信息: desc 表名; 
查看表格式化信息: desc formatted 表名;            注意: formatted能够展示详细信息

修改表名: alter table 旧表名 rename to 新表名
字段的添加: alter table 表名 add columns (字段名 字段类型);
字段的替换: alter table 表名 replace columns (字段名 字段类型 , ...);
字段名和字段类型同时修改: alter table 表名 change 旧字段名 新字段名 新字段类型;
	注意: 字符串类型不能直接改数值类型
	
修改表路径: alter table 表名 set location 'hdfs中存储路径';             注意: 建议使用默认路径
location:  建表的时候不写有默认路径/user/hive/warehouse/库名.db/表名,当然建表的时候也可以直接指定路径

修改表属性: alter table 表名 set tblproperties ('属性名'='属性值');     注意: 经常用于内外部表切换
内外部表类型切换:  外部表属性: 'EXTERNAL'='TRUE'   内部表属性: 'EXTERNAL'='FALSE'

示例:

-- 三.表的查看/修改操作
-- 验证之前的内外部表是否存在以及是否有数据,如果没有自己创建,如果有直接使用
select * from inner_stu1 limit 1;
select * from outer_stu1 limit 1;
-- 1.表的查看操作
-- 查看所有的表
show tables;
-- 查看建表语句
show create table inner_stu1;
show create table outer_stu1;
-- 查看表基本机构
desc inner_stu1;
desc outer_stu1;
-- 查看表格式化详细信息
desc formatted inner_stu1;
desc formatted outer_stu1;

-- 2.表的修改操作
-- 修改表名
-- 注意: 外部表只会修改元数据表名,hdfs中表目录名不会改变
alter table inner_stu1 rename to inner_stu;
alter table outer_stu1 rename to outer_stu;
-- 修改表中字段
-- 添加字段
alter table inner_stu add columns(age int);
alter table outer_stu add columns(age int);
-- 替换字段
alter table inner_stu replace columns(id int,name string);
alter table outer_stu replace columns(id int,name string);
-- 修改字段
alter table inner_stu change name sname varchar(100);
alter table outer_stu change name sname varchar(100);

-- 修改表路径(实际不建议修改)
-- 注意: 修改完路径后,如果该路径不存在,不会立刻创建,以后插入数据的时候自动生成目录
alter table inner_stu set location '/inner_stu';
alter table outer_stu set location '/outer_stu';

-- 修改表属性
-- 先查看类型
desc formatted inner_stu; -- MANAGED_TABLE
desc formatted outer_stu; -- EXTERNAL_TABLE
-- 内部表改为外部表
alter table inner_stu set tblproperties ('EXTERNAL'='TRUE');
-- 外部表改为内部表
alter table outer_stu set tblproperties ('EXTERNAL'='FALSE');
-- 最后再查看类型
desc formatted inner_stu; -- EXTERNAL_TABLE
desc formatted outer_stu; -- MANAGED_TABLE

默认分隔符

知识点:

创建表的时候,如果不指定分隔符,以后表只能识别默认的分隔符,键盘不好打印,展示形式一般为:\0001,SOH,^A,□

示例:

-- 默认分隔符: 创建表的时候不指定就代表使用默认分隔符
-- 1.创建表
create table stu(
    id int,
    name string
);
-- insert方式插入数据,会自动使用默认分隔符把数据连接起来
-- 2.插入数据
insert into stu values(1,'zhangsan');
-- 3.验证数据
select * from stu limit 1;
-- 当然也可以通过在hdfs中查看,默认分隔符是\0001,其他工具中也会展示为SOH,^A,口