大数据技术之Hive 第4章DDL数据定义

女爷i 2022-12-29 14:12 205阅读 0赞

### 第4章DDL数据定义 ###

*  序章
 *  4.1 创建数据库
 *  4.2 查询数据库
 *   *  4.2.1 显示数据库
     *  4.2.2 查看数据库详情
 *  4.3 修改数据库
 *  4.4 删除数据库
 *  4.5 创建表
 *   *  4.5.1 管理表
     *   *  2．案例实操
     *  4.5.2 外部表
     *  4.5.3 管理表与外部表的互相转换
 *  4.6 分区表
 *   *  4.6.2 分区表注意事项
 *  4.7 修改表
 *  4.7.1 重命名表
 *   *  4.7.2 增加、修改和删除表分区
     *  4.7.3 增加/修改/替换列信息
 *  4.8 删除表

# 序章 #

SQL语言共分为四大类：  
数据查询语言DQL，  
select \* from biao  
数据操纵语言DML，  
insert into  
update biao set  
delete from 表。  
数据定义语言DDL，  
create  
数据控制语言DCL。  
grent.

# 4.1 创建数据库 #

1）创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/\*.db。

> hive (default)> create database db\_hive;

2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

> hive (default)> create database db\_hive;  
> FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db\_hive already exists  
> hive (default)> create database if not exists db\_hive;

3）创建一个数据库，指定数据库在HDFS上存放的位置

> hive (default)> create database db\_hive2 location ‘/db\_hive2.db’;  
> ![图6-4 数据库存放位置][6-4]

# 4.2 查询数据库 #

## 4.2.1 显示数据库 ##

1．显示数据库

> hive> show databases;

2．过滤显示查询的数据库

> hive> show databases like ‘db\_hive\*’;  
> OK  
> db\_hive  
> db\_hive\_1

## 4.2.2 查看数据库详情 ##

1．显示数据库信息

> hive> desc database db\_hive;  
> OK  
> db\_hive hdfs://hadoop102:9000/user/hive/warehouse/db\_hive.db atguiguUSER

2．显示数据库详细信息，extended

> hive> desc database extended db\_hive;  
> OK  
> db\_hive hdfs://hadoop102:9000/user/hive/warehouse/db\_hive.db atguiguUSER

3.切换当前数据库

> hive (default)> use db\_hive;

# 4.3 修改数据库 #

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。**修改当前正在使用的数据库，要先退出使用**

> hive (default)> alter database db\_hive set dbproperties(‘createtime’=‘20170830’);

在hive中查看修改结果

> hive> desc database extended db\_hive;  
> db\_name comment location owner\_name owner\_type parameters  
> db\_hive hdfs://hadoop102:8020/user/hive/warehouse/db\_hive.db atguigu USER \{createtime=20170830\}

# 4.4 删除数据库 #

1．删除空数据库

> hive>drop database db\_hive2;

2．如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

> hive> drop database db\_hive;  
> FAILED: SemanticException \[Error 10072\]: Database does not exist: db\_hive  
> hive> drop database if exists db\_hive2;

3．如果数据库不为空，可以采用cascade命令，强制删除

> hive> drop database db\_hive;  
> FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db\_hive is not empty. One or more tables exist.)  
> hive> drop database db\_hive cascade;

# 4.5 创建表 #

1．建表语法

> CREATE \[EXTERNAL\] TABLE \[IF NOT EXISTS\] table\_name  
> \[(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 row\_format\]  
> \[STORED AS file\_format\]  
> \[LOCATION hdfs\_path\]

***row format delimited fields terminated by " "***

2．字段解释说明

> （1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。  
> （2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。  
> （3）COMMENT：为表和列添加注释。  
> （4）PARTITIONED BY创建分区表  
> （5）CLUSTERED BY创建分桶表  
> （6）SORTED BY不常用  
> （7）ROW FORMAT  
> DELIMITED \[FIELDS TERMINATED BY char\] \[COLLECTION ITEMS TERMINATED BY char\]  
> \[MAP KEYS TERMINATED BY char\] \[LINES TERMINATED BY char\]  
> | SERDE serde\_name \[WITH SERDEPROPERTIES (property\_name=property\_value, property\_name=property\_value, …)\]  
> 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。  
> SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。  
> （8）STORED AS指定存储文件类型  
> 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）  
> 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。  
> （9）LOCATION ：指定表在HDFS上的存储位置。  
> （10）LIKE允许用户复制现有的表结构，但是不复制数据。

建表(默认是内部表)

> create table trade\_detail(id bigint, name string) row format delimited fields terminated by ‘,’  
> create table trade\_detail(id bigint, account string, income double, expenses double, time string) row format delimited fields terminated by ‘\\t’;

建分区表

> create table td\_part(id bigint, account string, income double, expenses double, time string) partitioned by (logdate string) row format delimited fields terminated by ‘\\t’;

建外部表

> create external table td\_ext(id bigint, account string, income double, expenses double, time string) row format delimited fields terminated by ‘\\t’ location ‘/td\_ext’;

7.创建分区表  
普通表和分区表区别：有大量数据增加的需要建分区表

> create table book (id bigint, name string) partitioned by (pubdate string) row format delimited fields terminated by ‘\\t’;

分区表加载数据

> load data local inpath ‘./book.txt’ overwrite into table book partition (pubdate=‘2010-08-22’);  
> load data local inpath ‘/root/data.am’ into table beauty partition (nation=“USA”);  
> select nation, avg(size) from beauties group by nation order by avg(size);

## 4.5.1 管理表 ##

1．理论  
默认创建的表都是所谓的管理表，有时也被称为**内部表**。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

### 2．案例实操 ###

（1）普通创建表

> create table if not exists student2(  
> id int, name string  
> )  
> row format delimited fields terminated by ‘\\t’  
> stored as textfile  
> location ‘/user/hive/warehouse/student2’;

（2）根据查询结果创建表（查询的结果会添加到新创建的表中）

> create table if not exists student3 as select id, name from student;

（3）根据已经存在的表结构创建表

> create table if not exists student4 like student;

（4）查询表的类型

> hive (default)> desc formatted student2;  
> Table Type: MANAGED\_TABLE

## 4.5.2 外部表 ##

1．理论  
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。  
2．管理表和外部表的使用场景  
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。  
3．案例实操  
分别创建部门和员工外部表，并向表中导入数据。  
（1）原始数据  
![在这里插入图片描述][20201223092645658.png]  
dept.txt 模拟数据

> 10 ACCOUNTING 1700  
> 20 RESEARCH 1800  
> 30 SALES 1900  
> 40 OPERATIONS 1700

emp.txt 模拟数据

> 7369 SMITH CLERK 7902 1980-12-17 800.00 20  
> 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30  
> 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30  
> 7566 JONES MANAGER 7839 1981-4-2 2975.00 20  
> 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30  
> 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30  
> 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10  
> 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20  
> 7839 KING PRESIDENT 1981-11-17 5000.00 10  
> 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30  
> 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20  
> 7900 JAMES CLERK 7698 1981-12-3 950.00 30  
> 7902 FORD ANALYST 7566 1981-12-3 3000.00 20  
> 7934 MILLER CLERK 7782 1982-1-23 1300.00 10

（2）建表语句  
创建部门表

> create external table if not exists default.dept(  
> deptno int,  
> dname string,  
> loc int  
> )  
> row format delimited fields terminated by ‘\\t’;

创建员工表

> create external table if not exists default.emp(  
> empno int,  
> ename string,  
> job string,  
> mgr int,  
> hiredate string,  
> sal double,  
> comm double,  
> deptno int)  
> row format delimited fields terminated by ‘\\t’;

查看创建的表

> hive (default)> show tables;  
> OK  
> tab\_name  
> dept  
> emp

向外部表中导入数据  
导入数据

> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept;  
> hive (default)> load data local inpath ‘/opt/module/datas/emp.txt’ into table default.emp;  
> 查询结果  
> hive (default)> select \* from emp;  
> hive (default)> select \* from dept;

查看表格式化数据

> hive (default)> desc formatted dept;  
> Table Type: EXTERNAL\_TABLE

## 4.5.3 管理表与外部表的互相转换 ##

只能用单引号，严格区分大小写，如果不是完全符合，那么只会添加kv 而不生效  
（1）查询表的类型

> hive (default)> desc formatted student2;  
> Table Type: MANAGED\_TABLE

（2）修改内部表student2为外部表

> alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);

（3）查询表的类型

> hive (default)> desc formatted student2;  
> Table Type: EXTERNAL\_TABLE

（4）修改外部表student2为内部表

> alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

（5）查询表的类型

> hive (default)> desc formatted student2;  
> Table Type: MANAGED\_TABLE

\*\*\*注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！\*\*\*

# 4.6 分区表 #

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

4.6.1 分区表基本操作  
1．引入分区表（需要根据日期对日志进行管理）

> /user/hive/warehouse/log\_partition/20170702/20170702.log  
> /user/hive/warehouse/log\_partition/20170703/20170703.log  
> /user/hive/warehouse/log\_partition/20170704/20170704.log

2．创建分区表语法

> hive (default)> create table dept\_partition(  
> deptno int, dname string, loc string  
> )  
> partitioned by (month string)  
> row format delimited fields terminated by ‘\\t’;

3．加载数据到分区表中

> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month=‘201709’);  
> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month=‘201708’);  
> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month='201707’);

![在这里插入图片描述][20201223093410538.png]  
![在这里插入图片描述][20201223093414493.png]  
4．查询分区表中数据  
单分区查询  
hive (default)> select \* from dept\_partition where month=‘201709’;  
多分区联合查询 union（排序） or in 三种方式

> hive (default)> select \* from dept\_partition where month=‘201709’  
> union  
> select \* from dept\_partition where month=‘201708’  
> union  
> select \* from dept\_partition where month=‘201707’;  
> \_u3.deptno \_u3.dname \_u3.loc \_u3.month  
> 10 ACCOUNTING NEW YORK 201707  
> 10 ACCOUNTING NEW YORK 201708  
> 10 ACCOUNTING NEW YORK 201709  
> 20 RESEARCH DALLAS 201707  
> 20 RESEARCH DALLAS 201708  
> 20 RESEARCH DALLAS 201709  
> 30 SALES CHICAGO 201707  
> 30 SALES CHICAGO 201708  
> 30 SALES CHICAGO 201709  
> 40 OPERATIONS BOSTON 201707  
> 40 OPERATIONS BOSTON 201708  
> 40 OPERATIONS BOSTON 201709

5．增加分区  
创建单个分区

> hive (default)> alter table dept\_partition add partition(month=‘201706’) ;  
> 同时创建多个分区 用空格分开  
> hive (default)> alter table dept\_partition add partition(month=‘201705’) partition(month=‘201704’);

6．删除分区

> 删除单个分区  
> hive (default)> alter table dept\_partition drop partition (month=‘201704’);  
> 同时删除多个分区 用逗号分开  
> hive (default)> alter table dept\_partition drop partition (month=‘201705’), partition (month=‘201706’);

7．查看分区表有多少分区

> hive> show partitions dept\_partition;

8．查看分区表结构

> hive> desc formatted dept\_partition;  
> \#Partition Information  
> \#col\_name data\_type comment  
> month string

## 4.6.2 分区表注意事项 ##

1．创建二级分区表

> hive (default)> create table dept\_partition2(  
> deptno int, dname string, loc string  
> )  
> partitioned by (month string, day string)  
> row format delimited fields terminated by ‘\\t’;

2．正常的加载数据

> （1）加载数据到二级分区表中  
> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table  
> default.dept\_partition2 partition(month=‘201709’, day=‘13’);  
> （2）查询分区数据  
> hive (default)> select \* from dept\_partition2 where month=‘201709’ and day=‘13’;

3．把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式  
（1）方式一：上传数据后修复

> 上传数据  
> hive (default)> dfs -mkdir -p  
> /user/hive/warehouse/dept\_partition2/month=201709/day=12;  
> hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept\_partition2/month=201709/day=12;

> 查询数据（查询不到刚上传的数据）  
> hive (default)> select \* from dept\_partition2 where month=‘201709’ and day=‘12’;  
> 执行修复命令  
> hive> msck repair table dept\_partition2;

> 再次查询数据  
> hive (default)> select \* from dept\_partition2 where month=‘201709’ and day=‘12’;
> 
> 2）方式二：上传数据后添加分区  
> 上传数据  
> hive (default)> dfs -mkdir -p  
> /user/hive/warehouse/dept\_partition2/month=201709/day=11;  
> hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept\_partition2/month=201709/day=11;

> 执行添加分区  
> hive (default)> alter table dept\_partition2 add partition(month=‘201709’,  
> day=‘11’);  
> 查询数据  
> hive (default)> select \* from dept\_partition2 where month=‘201709’ and day=‘11’;

> 3）方式三：上传数据后load数据到分区  
> 创建目录  
> hive (default)> dfs -mkdir -p  
> /user/hive/warehouse/dept\_partition2/month=201709/day=10;  
> 上传数据  
> hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table  
> dept\_partition2 partition(month=‘201709’,day=‘10’);  
> 查询数据  
> hive (default)> select \* from dept\_partition2 where month=‘201709’ and day=‘10’;

# 4.7 修改表 #

# 4.7.1 重命名表 #

1．语法  
ALTER TABLE table\_name RENAME TO new\_table\_name  
2．实操案例  
hive (default)> alter table dept\_partition2 rename to dept\_partition3;

## 4.7.2 增加、修改和删除表分区 ##

详 见4.6.1分区表基本操作。

## 4.7.3 增加/修改/替换列信息 ##

1．语法  
更新列

> ALTER TABLE table\_name CHANGE \[COLUMN\] col\_old\_name col\_new\_name column\_type \[COMMENT col\_comment\] \[FIRST|AFTER column\_name\]  
> 增加和替换列  
> ALTER TABLE table\_name ADD|REPLACE COLUMNS (col\_name data\_type \[COMMENT col\_comment\], …)

***注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。***

2．实操案例

> （1）查询表结构  
> hive> desc dept\_partition;  
> （2）添加列  
> hive (default)> alter table dept\_partition add columns(deptdesc string);  
> （3）查询表结构  
> hive> desc dept\_partition;  
> （4）更新列  
> hive (default)> alter table dept\_partition change column deptdesc desc int;  
> （5）查询表结构  
> hive> desc dept\_partition;  
> （6）替换列  
> hive (default)> alter table dept\_partition replace columns(deptno string, dname  
> string, loc string);  
> （7）查询表结构  
> hive> desc dept\_partition;

# 4.8 删除表 #

> hive (default)> drop table dept\_partition;

[6-4]: /images/20221120/d3c389592a3e4a9aacdbc5146a966c88.png
[20201223092645658.png]: /images/20221120/171199be5388402bb526daad3107b053.png
[20201223093410538.png]: /images/20221120/bbf558219d54459bb5d8b223f1fca2cd.png
[20201223093414493.png]: /images/20221120/ae69121448fe4d7e983452d244237b72.png