Hadoop学习笔记（18）Hive的数据定义和数据操作

一、Hive的数据定义（数据定义语言DDL）

1、数据库

1.1、创建数据库

• （1）Hive中数据库的概念本质上仅仅是表的一个目录或者命名空间。
• （2）如果用户没有显式指定数据库，那么将会使用默认的数据库default。
• （3）创建一个数据库

create database test_02; 

• （4）如果test_db数据库存在，则会抛出异常

create database if not exists test_02; 

if not exists指在执行创建之前先进行数据库是否存在判断。

• （5）创建数据库并增加数据库描述信息

create database if not exists test_02 comment '测试数据库'; 

1.2、查看数据库

• （1）查看Hive中所有的数据库

show databases;

• （2）使用正则表达式（模糊查询），查看Hive中的数据库

show databases like 'te.*';

1.3、使用数据库（进入数据库中）

• （1）use命令用于将某个数据库设置为用户当前的工作数据库。

use test_02;

1.4、查看数据库信息

• （1）查看数据库的结构信息。数据库名称、在HDFS的路径、用户等信息

describe database test_02;

• （2）Hive会为每个数据库创建一个目录（因为数据库本质上仅仅是表的一个目录或者命名空间）。数据库中的表将会以这个数据库目录的子目录形式存储。
  • 数据库的目录位于hive-default.xml.template文件中的属性hive.metastore.warehouse.dir所指定的顶层目录之后。

[admin@master ~]$ cd apache-hive-1.2.2-bin
[admin@master apache-hive-1.2.2-bin]$ cd conf
[admin@master conf]$ vim hive-default.xml.template

• Hive将会对应地创建一个HDFS目录/user/hive/warehouse/test_02.db。注意：数据库的文件目录以.db结尾。

hive> dfs -ls hdfs://master:9000/user/hive/warehouse;
Found 6 items
drwxr-xr-x   - admin supergroup          0 2021-03-20 10:44 hdfs://master:9000/user/hive/warehouse/sougou.db
drwxr-xr-x   - admin supergroup          0 2021-03-22 00:09 hdfs://master:9000/user/hive/warehouse/struct_test
drwxr-xr-x   - admin supergroup          0 2021-03-22 01:04 hdfs://master:9000/user/hive/warehouse/test_01.db
drwxr-xr-x   - admin supergroup          0 2021-04-10 23:58 hdfs://master:9000/user/hive/warehouse/test_02.db
drwxr-xr-x   - admin supergroup          0 2021-04-11 00:11 hdfs://master:9000/user/hive/warehouse/test_03.db
drwxr-xr-x   - admin supergroup          0 2021-04-11 00:17 hdfs://master:9000/user/hive/warehouse/test_04.db
hive> 

1.5、删除数据库

• （1）删除数据库

drop database test_02;

drop database if exists test_02;

• （2）Hive不允许用户删除一个包含有表的数据库。故需要先删除表，才能再删除数据库

hive> drop table table_02;
OK
Time taken: 0.71 seconds
hive> drop database if exists test_02;
OK
Time taken: 0.05 seconds

• （3）当非要强行删除时，删除命令的最后加上关键字cascade，可以使Hive自行先删除数据库中的表

drop database if exists test_02 cascade;

2、表

2.1、创建表（默认创建的表都属于管理表）

• （1）创建表

create table if not exists test_02.table_01(name string,age int) row format delimited fields terminated by '\t' location '/test_02/table_01' 

create table if not exists test_02.table_01(name string,age int) row format delimited fields terminated by '\t'; 

• （2）location是指定表目录
• （3）row format delimited
• （4）fields terminated by ‘\t’
• （5）创建空表后，在HDFS中/user/hive/warehouse是默认的“数据仓库”路径地址，test_02.db是数据库目录，table_01是表目录，要插入表数据，即向表目录table_01灌入数据即可。
• （6）默认情况下，Hive总是将创建的表的目录放置在这个表所属的数据库目录中。

2.2、创建管理表（内部表、临时表）

• （1）管理表也称为内部表、内表、临时表。
• （2）对于管理表而言，表的数据的生命周期由Hive来决定。（而外部表则相反）
• （3）Hive默认情况下会将这些表的数据存储在由配置文件hive-default.xml.template中的属性hive.metastore.warehouse.dir所指定的目录下。
• （4）当删除一个管理表时，Hive也会删除这个表中的数据。即表的元数据、表中的数据都会被删。
• （5）管理表不方便与其他工作共享数据。

2.3、创建外部表

• （1）创建外部表

create external table if not exists test_02.table_02(name string,age int) row format delimited fields terminated by '\t'; 

• （2）external
• （3）对该表插入数据后。当删除该表后，表不存在了，但数据还存在HDFS中。即表的元数据会被删了，而表中的数据不会删。
• （4）对于外部表而言，表的数据的生命周期不受Hive来决定。
• （5）示例如下：
  先sougou库创建一个table_sougou外部表

hive> create external table if not exists sougou.table_sougou(ts string,uid string,keyword string,rank int,orders int,url string) row format delimited fields terminated by '\t';

由本地向数据库的table_sougou表写入数据

hive> load data local inpath '/home/admin/sogou.500w' overwrite into table sougou.table_sougou;

2.4、显示数据库中的表

use test_02;
show tables;

use test_02;
show tables like 'ta.*';

show tables in test_02;

show tables in test_02 like 'ta.*';

2.5、删除表

注：一般情况hive不支持delete
删除表

drop table test_02.table_01;

2.6、修改表

注：一般情况hive不支持update
修改表名

hive> alter table test_02.table_03 rename to test_02.table_09;

修改表结构（增加列）

hive> alter table test_02.table_09 add columns(sex string,birthday date comment 'birthday date');

2.7、创建分区表

• 1、分区表分为静态分区表和动态分区表。
  比如：有4个分区字段year、month、day、hour

  • （1）静态分区时，4个字段的值必须得到满足，如果不满足，可能分区就不成功了。所以静态分区一般不适用在生产环境。
  • （2） 动态分区时，分区可以随意按照1个或2个或3个或4个字段分区。

• 2、动态分区表

  • （1）开启动态分区功能

hive> set hive.exec.dynamic.partition=true;

• （2）所有分区都是动态的（动态分区的模式，默认值strict，不允许分区列全部是动态的；nonstrict允许分区列全部是动态的）

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

• （3）设置最大动态分区个数

hive> set hive.exec.max.dynamic.partitions.pernode=1000;

• 3、分区表
  （1）将时间字段拆分并拼接，添加年、月、日、小时字段；

[admin@master ~]$ vim sougou-log-extend.sh

[admin@master ~]$ bash sougou-log-extend.sh /home/admin/sogou.500w/sogou.500w.utf8 /home/admin/sogou.500w/sogou.500w.utf8.ext 

（2）在sougou库创建一个带4个扩展字段（year、month、day、hour）的table_sougou_par外部表

hive> create external table if not exists sougou.table_sougou_par(ts string,uid string,keyword string,rank int,orders int,url string,year int,month int,day int,hour int) row format delimited fields terminated by '\t';

（3）由本地向数据库的table_sougou_par表写入数据

hive> load data local inpath '/home/admin/sogou.500w/sogou.500w.utf8.ext' overwrite into table sougou.table_sougou_par;

（4）sougou库创建一个分区表

hive> create external table if not exists sougou.table_partition(ts string,uid string,keyword string,rank int,orders int,url string) partitioned by(year int,month int,day int,hour int) row format delimited fields terminated by '\t';

（5）向分区表插入数据

hive> insert overwrite table sougou.table_partition partition(year,month,day,hour) select * from sougou.table_sougou_par;

二、Hive数据操作（数据操作语言DML）

1、加载数据的方式

1.1、由本地向数据库的表写入数据

hive> load data local inpath '/home/admin/table_02.txt' into table test_02.table_02;

overwrite指重写数据（全删全上）

hive> load data local inpath '/home/admin/table_02.txt' overwrite into table test_02.table_02;

1.2、put命令上传数据文本到表目录（前提是知道表的目录）

当已得知table_03在HDFS的表目录是

hive> dfs -ls /user/hive/warehouse/test_02.db/table_03;

那么通过命令实现上传

[admin@master ~]$ hadoop fs -put ./table_03.txt /user/hive/warehouse/test_02.db/table_03

最终结果可以查看到文件以及表数据查询

1.3、insert插入数据

overwrite重写数据（全删全上）。注意：overwrite指重写数据，当表table_04没有数据时，正常写入数据，当表table_04有数据时，则会把表table_04数据删掉后，再把表table_02数据写入。

hive> insert overwrite table test_02.table_04 select * from test_02.table_02;

1.4、参考目标表创建表并且写入数据

hive> create table test_02.table_05 as select * from test_02.table_02;