数据仓库工具 hive的入门（二）HQL操作之 -- DDL命令

**Hive命令实战操作之 – DDL命令

提示：本文章内容取自来源： 拉勾教育大数据高薪训练营

---

---

前言

提示：本文章对于初学者准备，希望对大家有所帮助。如果有什么建议和疑问，请留言给我，我会不断完成完善。

---

HQL操作之 – DDL命令

概念： DDL（data definition language），主要的命令有CREATE、ALTER、DROP等。
DDL主要是用在定义、修改数据库对象的结构 或 数据类型。

1.1、数据库操作

Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认(default)数据库；

*Hive的数据库名、表名均不区分大小写；
名字不能使用数字开头；
不能使用关键字，尽量不使用特殊符号；*

1.1.1、创建数据库语法

create database [IF NOT EXISTS] database_name

1.创建数据库，在HDFS上存储路为/user/hive/warehouse/*.db
hive (default)> create database mydb;
hive (default)> dfs -ls /user/hive/warehouse;

2.避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;

3. 创建数据库。添加备注，指定数据库在存放位置
hive (default)> create database if not exists mydb2
comment 'this is mydb2'
location '/user/hive/mydb2.db';

1.1.3、查看数据库

1.查看所有数据库
show databases;

2.查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

1.1.4、使用数据库

1.查看所有数据库
use database_name;

1.2、 建表语法

create [external]table [if not exists]table_name
[partition by (colName colType [comment col_comment])]
[clustered BY (colName, colName, ...)
[sorted by (col_name [ASC|DESC], ...)] into num_buckets
buckets]
[row format row_format]
[stored as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name
 LIKE existing_table_or_view_name
[LOCATION hdfs_path];

1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if not
   exists 规避。
2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
   删除内部表时，数据和表的定义同时被删除；
   删除外部表时，仅仅删除了表的定义，数据保留；
   在生产环境中，多使用外部表；
3. comment。表的注释
4. partition by。对表中数据进行分区，指定表的分区字段
5. clustered by。创建分桶表，指定分桶字段
6. sorted by。对桶中的一个或多个列排序，较少使用
7. AS。后面可以接查询语句，表示根据后面的查询结果创建表
8. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

1.2.1、内部表 & 外部表

在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部表。

默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external。

在删除内部表时，表的定义(元数据) 和 数据 同时被删除。
在删除外部表时，仅删除表的定义，数据被保留。

1.2.1.1、内部表

vim /home/hadoop/data/t1.dat

2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

1.创建内部表
create table t1(
 id int,
 name string,
hobby array<string>,
 addr map<string, string>
)
row format delimited          
fields terminated by ";"    ---字段之间以；为分割符
collection items terminated by ","   ---元素之间以，为分割符
map keys terminated by ":";   ---key与value之间以：为分割符

2.显示表的定义，显示的信息多，格式友好
desc formatted t1;

3.加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table
t1;
4. 查询数据
select * from t1;

5. 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;

6.删除表。表和数据同时被删除
drop table t1；

-- 再次查询数据文件，已经被删除

1.2.1.2、外部表

1.创建内部表
create external table t2(
 id int,
 name string,
hobby array<string>,
 addr map<string, string>
)
row format delimited          
fields terminated by ";"    ---字段之间以；为分割符
collection items terminated by ","   ---元素之间以，为分割符
map keys terminated by ":";   ---key与value之间以：为分割符

2.显示表的定义，显示的信息多，格式友好
desc formatted t2；

3.加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table
t2;
4. 查询数据
select * from t2;

5. 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t2;

6.删除表。表和数据同时被删除
drop table t2；

-- 再次查询数据文件，仍然存在

内部表与外部表的转换：

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;`

-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

小结

建表时：
如果不指定external关键字，创建的是内部表；
指定external关键字，创建的是外部表；

删表时：
删除外部表时，仅删除表的定义，表的数据不受影响
删除内部表时，表的数据和定义同时被删除

1.2.2、分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。

1.2.2.1、查看数据库

1.查看所有数据库
-- 创建表
create table if not exists t3(
 id    int
 ,name  string
 ,hobby  array<string>
 ,addr  map<String,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';

2. 加载数据进行分区。
load data local inpath "/home/hadoop/data/t1.dat" into table
t3 partition(dt="2020-06-01");
load data local inpath "/home/hadoop/data/t1.dat" into table
t3 partition(dt="2020-06-02");

3.查看分区
show partitions t3; 

4.新增分区并设置数据
增加多个分区，不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');
- 增加多个分区。加载数据
alter table t3 add
partition(dt='2020-06-07') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'

1.2.3、分桶表

分桶的原理：
MR中：key.hashCode % reductTask
Hive中：分桶字段.hashCode % 分桶个数

1.创建分桶表
create table course(
 id int,
 name string,
 score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "\t";
-- 创建普通表
create table course_common(
 id int,
 name string,
 score int
)
row format delimited fields terminated by "\t";

-- 普通表加载数据
load data local inpath '/home/hadoop/data/course.dat' into
table course_common;

-- 通过 insert ... select ... 给桶表加载数据
insert into table course select * from course_common;

-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

1.3、 修改表 & 删除表

-- 修改表名。rename
alter table course_common rename to course_common1;

-- 修改列名。change column
alter table course_common1 change column id cid int;

-- 修改字段类型。change column
alter table course_common1 change column cid cid string;

-- The following columns have types incompatible with the
existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是string不能转为int

-- 增加字段。add columns
alter table course_common1 add columns (common string);

-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1 replace columns(
 id string, cname string, score int);

-- 删除表
drop table course_common1;

总结

HQL DDL命令小结：

主要对象：数据库、表

表的分类：内部表。删除表时，同时删除元数据和表数据

外部表。删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表

分区表。按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能

分桶表。按照分桶字段，将表中数据分开。 分桶字段.hashCode % 分桶数据

主要命令：create、alter 、drop