HIVE大咖之路之库和表的操作

DDL - 库的操作

1️⃣建库

1️⃣建库语法

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

2️⃣实践建库

create database if not exists mydb   
comment "my first db"
with dbproperties("createtime"="2022-05-24");

2️⃣查看库

# 普通查看所有库
show databases;
# 查看指定的库
desc database mydb;
# 查看指定库并且查看库的详情
desc database extended mydb; 

3️⃣修改库

# 这个修改库只能修改一部分不算重要的东西，如创建时间和创作者
alter database mydb set dbproperties("createtime"="2020-05-20","author"="wyh");

4️⃣删除库

# 删除空库
drop database 库名; 
# 删除库(可空可不空)
drop database 库名 cascade ; 

DDL- 表的操作

1️⃣ 建表

1️⃣建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name   -- EXTERANL: 外部表
[(col_name data_type [COMMENT col_comment], ...)]  -- 列名 列类型 列描述信息  ....
[COMMENT table_comment] -- 表描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] -- 创建分区表指定分区字段  分区列名  列类型
[CLUSTERED BY (col_name, col_name, ...) -- 创建分桶表指定分桶字段   分桶列名
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  -- 指定分桶数
[ROW FORMAT delimited fields terminated by ... ] -- 指定一条数据字段与字段的分割符
[collection items terminated by  ... ] -- 指定集合元素与元素的分割符
[map keys terminated by ... ] -- 指定map的kv的分割符
[STORED AS file_format] -- 指定文件存储格式，默认为 textfile
[LOCATION hdfs_path] -- 指定表在hdfs中对应的路径
[TBLPROPERTIES (property_name=property_value, ...)] -- 指定表的属性
[AS select_statement] -- 基于某个查询建表

2️⃣实践建表

use mydb;
create table if not exists test1(
id int comment "this's id ",
name string,
sto string comment "this's xuehao"
)
comment "this 's table"
row format delimited fields terminated by ','
STORED as textfile 
TBLPROPERTIES("createtime"="2022-05-23") ;

#建外部表
create EXTERNAL table if not exists pier(
piername string comment "pierName",
pierCSDN string comment "qq_43325476");

2️⃣查看表

show tables
desc test1;
desc formatted test1; 

3️⃣修改表

#改变表为外部表
alter table 表名 set tblproperties('EXTERNAL'='TRUE');
#改变表为内部表注意引号的大小写区分
alter table 表名 set tblproperties('EXTERNAL'='FALSE');
#内部表和外部表的区别:内部表再删除时回同时删除hdfs上的数据，
#而外部表删除时不会删除hdfs上的数据，仅删除了数据库中的元数据。
#因此我们平时假如要使用什么表的话，个人建议学习时采用外部表比较好，不然“删库跑路”
# 修改表名
alter table 原表名 rename to 修改后的表名 ; 
# 修改单项表的数据名
alter table 表名 change column 原数据项名 更改名 数据项的数据类型;
# 添加数据项
alter table emptest add columns (数据项1 数据类型,数据项2 数据类型,……);
# 更改表
# 慎用，这个更改，时将原本的表覆盖新建表
alter table emptest replace columns (数据项1 数据类型,数据项2 数据类型,……);
# 更改表的默认分隔符
ALTER TABLE emptest SET SERDEPROPERTIES ('field.delim' = ':' , 'serialization.format'=':');

DML 数据操作语言

1️⃣数据导入

1️⃣load

 # 创建一个表并通过row format delimited fields terminated by指定分隔符为'/t'
create table student(id string, name string) row format delimited fields terminated by '\t';
#自己在某个路径下创建一个txt文件，建议$HIVE_HOME/datas/students.txt
#然后在其中输入一些数据，使用空格或者tab键隔开如：下面这些文件
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

# 导入student表的数据
load data local inpath '/opt/module/hive/datas/student.txt' into table student; 	

#local从本地进行追加插入
load data local inpath '/opt/module/hive/datas/student1.txt' into table student; 

#overwrite 覆盖并插入
load data local inpath '/opt/module/hive/datas/student2.txt' overwrite into table student; 

# 注意 这个插入这个数据会把数据从hdfs上原本目录中删除，相当于剪切到数据库里面去
load data inpath '/hivedatas/student.txt' into table student; 

2️⃣insert

# 直接插入数据
insert into student values(1017,'ss17'),(1018,'ss18'),(1019,'ss19');  
# 创建并插入数据
create table student2(id string, name string) row format delimited fields terminated by '\t';
# 基于查询结果插入数据
insert into student2 select id, name from student ;  

3️⃣as select

#这种方式就是直接将我们的查询结果创建到一张新表当中
#将student查询结果直接进行插入，不需要设置字段，本身查询字段作为表字段
create table student3 as select id, name from student ;

4️⃣location

#这种方法比较常用
#复习一下
#hdfs从本地建文件的方式
hadoop fs -mkdir path
#hdfs上传文件的方式
hadoop fs -put 文件名 path
#从hdfs中直接拿取文件采用分隔符建表
create table student4(id string, name string) 
row format delimited fields terminated by '\t'
location '/student4' ;

5️⃣import

#这个需要先将表使用export导出一般用户复制表集群间的迁移
import table emptest2 from '/user/pier';

2️⃣数据导出

1️⃣insert导出

#将表的结果导入到本地目录
insert overwrite local directory '/opt/module/hive/datas/insert-result' select * from  emptest ;

# insert保存到本地并指定文件分隔格式
insert overwrite local directory '/opt/module/hive/datas/insert-result' 
row format delimited fields terminated by ':'
select * from emptest ;

# insert上传到hdfs上并指定分隔符为":"
insert overwrite directory '/pier_db' row format delimited fields terminated by ":" select * from emptest;

如果文件中的值为null导出到本地为/N

2️⃣export导出

 export table default.student to
 '/user/hive/warehouse/export/student';
## export只能导到hdfs上不能导出到本地
# export和import主要用于两个Hadoop平台集群之间Hive表迁移。

3️⃣命令行导出

hive -e "select * from mydb.emptest" >$HIVE_HOME/datas/e.txt

3️⃣数据清除

# 这个不能清除外部表的内容
truncate table emptest2;

个人理解，内部表相当于一个内部人员，对数据是有数据管理权限，所以拥有最高权限。而外部人员删表可以，但是不能动我本身的数据，你只能有表面的拥有权，我的♥不是你的，哈哈哈。