Hive中的DDL操作

参考文章：https://www.cnblogs.com/qingyunzong/p/8723271.html

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableCreate/Drop/TruncateTable

首先引入什么是DDL、DML、DCL：

DDL（data manipulation language）	它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。
DML（data definition language）	DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，它们大多在建立表时使用。
DCL （Data Control Language）	是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

一、库操作

1、创建库

语法结构：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

　　[COMMENT database_comment]　　　　　　//关于数据块的描述

　　[LOCATION hdfs_path]　　　　　　　　　　//指定数据库在HDFS上的存储位置

　　[WITH DBPROPERTIES (property_name=property_value, ...)];　　　　//指定数据块属性

创建库的方式：

（1）创建普通的数据库

> create database db_name;

> show databases;                //可看到库已建好

（2）创建库的时候检查存与否

> create database if not exists db_name;

（3）创建库的时候带注释

> create database if not exists db_name comment '表名';

> desc database db_name                //查看数据库的基本信息

（4）创建带属性的库

> create database if not exists db_name with dbproperties(creator='Mark_Frank', date=2021-07-15');

2、查看库

查看库的方式：

（1）查看有哪些数据库

> show databases;

（2）显示数据库的详细属性信息

> desc database [extended] db_name;

例：desc database extended t3;

（3）查看正在使用哪个库

> select current_database();

（4）查看创建库的详细语句

> show create database db_name;

3、删除库

说明：

删除库操作：

> drop database db_name;

> drop database if not exists db_name;

默认情况下，hive不允许删除包含表的数据库，两种解决方法：

1、手动删除库下所有表，然后删除库

2、使用cascade关键字

> drop database if not exists db_name cascade;

默认情况下就是 restrict drop database if exists myhive，强制删除。

示例：

（1）删除不含表的数据库

> show tables in db_name;

> drop database db_name;

> show databases;

（2）删除含有表的数据库

> drop database if exists db_name cascade;            //注意：exists后面为数据库名

> show databases;

4、切换库

语法：

> use db_name;

二、表操作：

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]

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。
• EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）。
• LIKE 允许用户复制现有的表结构，但是不复制数据。
• COMMENT 可以为表与字段增加描述。
• PARTITIONED BY 指定分区。
• 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 确定表的具体的列的数据。 

• STORED AS 　　

        SEQUENCEFILE         //序列化文件　　

        | TEXTFILE                  //普通的文本文件格式　　

        | RCFILE　　              //行列存储相结合的文件　　

        | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式　　

        如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

• LOCATION 指定表在HDFS的存储路径，如果不指定，则按照默认的规则存储在磨人的仓库路径中。

如果一份数据已经存储在HDFS上，且要被多个用户或者客户端使用，最好创建外部表，反之，最好创建内部表。

示例：

（1）创建默认的内部表

> create table student(id int, name string, sex string, age int,department string) row format delimited fields terminated by ",";

> desc student;

 （2）外部表

> create external table student_ext (id int, name string, sex string, age int, department string) row format delimited fields terminated by "," location "/hive/student";

（3）分区表

> create external table student_ptn(id int, name string, sex string, age int, department string)

> partitioned by (city string)

> row format delimited fields terminated by ","

> location "/hive/student_ptn";

添加分区：

> alter table student_ptn add partition(city="beijing");

> alter table student_ptn add partition(city="shenzhen");

如果某张表是分区表，则每个分区的定义其实就表现为这张表的数据存储目录下的一个子目录；分区表的数据文件一定要存储在某个分区中，而不能直接存储在表中。

（4）分桶表

> create external table student_bck(id int, name string, sex string, age int, department string)

> clustered by (id) sorted by (id asc, name desc) into 4 buckets

> row format delimited fields terminated by ","

> location "/hive/student_bck";

（5）使用CTAS创建表

首先，在student表中导入数据：

> load data local inpath "/home/hadoop/student.txt" into table student;

然后，使用CTAS创建表（将一个查询SQL的结果创建一个表进行存储）：

> create table student_ctas as select * from student where id<95012;

（6）复制表结构

> create table student_copy like student;

注意：如果在table的前面没有加external关键字，那么复制出来的新表，无论如何都是内部表；如果在table的前面加了external关键字，那么复制出来的新表，无论如何都是外部表。

2、查看表

查看表列表：

（1）查看当前使用的数据库中有哪些表

> show tables;

（2）查看非当前使用的数据库中有哪些表

> show tables in myhive;      //myhive为其他数据库

（3）查看数据库中以xxx开头的表

> show tables like 'student_c*';

查看表的信息：

（1）简略信息

> desc student;

（2）详细信息（格式不友好）：

> desc extended student;  

（3）详细信息（格式友好）：

> desc formatted student; 

查看表的分区信息：

> show partitions student_ptn;

查看表的详细建表语句：

> show create table student_ptn;

3、修改表

修改表名：

> alter table student rename to new_student;

> show table;

修改字段定义：

（1）增加一个字段

> alter table new_student add columns (score int);

> desc new_student;

（2）删除一个字段

不支持删除字段。

（3）替换所有字段

> alter table new_student replace columns (id int, name string, address string);

添加分区：

• 静态分区

> alter table student_ptn add partition(city="weihai");                //添加一个

> alter table student_ptn add partition(city="beijing") paertition(city="shanghai") partition(city="kunming");                //添加多个

• 动态分区

先向student_ptn中插入数据：

> load data local inpath "/home/hadoop/student.txt" into table student_ptn partition(city="beijing");

再把这张表直接插入到另一张表student_ptn_age中，并实现sex为动态分区（不指定到底是哪种性别，让系统自己分配决定）。

首先创建student_ptn_age并指定分区为age：

> create table student_ptn_age (id int, name string, sex string, department string) partitioned by (age int);

从student_ptn表中查询数据并插入到student_ptn_age表中：

> insert overwrite table student_ptn_age partition(age)

select id, name, sex, department, age from student_ptn;

修改分区：

修改分区常指修改分区的数据存储目录。

在添加分区时直接指定：

> alter table student_ptn add if not exists partition(city="beijing")

location "/student_ptn_beijing" partition(city="cc") location "/student_cc";

 修改已经指定好的分区的数据存储目录：

> alter table student_ptn partition (city="beijing") set location "/student_ptn_beijing";

此时原先的分区文件夹仍然存在，只是再往分区添加数据时，只会添加到新的分区目录。

删除分区：

> alter table student_ptn drop partition (city='beijing');

> show partitions student_ptn;        //可看到分区已删除

4、删除表

> drop table new_student;

> show tables;               //可看到表已删除

5、清空表

> truncate table student_ptn;

总结：

查看数据库列表	show database; show database like 'stu*';
查看数据表	show tables; show tables in db_name;
查看数据表的建表语句	show create table table_name;
查看hive函数列表	show functions;
查看hive表的分区	show partitions table table_name; show partitions table table_name partition(city='beijing');
查看表的详细信息（元数据信息）	desc table_name; desc extended table_name; desc formatted table_name;
查看数据库的详细属性信息	desc database db_name; desc database extended db_name;
清空数据表	truncate table table_name;