Hive学习（四）Hive的DDL操作

目录

1、DDL 操作

1.1、库操作

1.1.1、创建库

1.1.2、查看库

1.1.3、删除库

1.1.4、切换库

1.2、表操作

1.2.1、创建表

1.2.2、查看表

1.2.3、修改表

1.2.4、删除表

1.2.5、清空表

21.3、其他辅助命令

正文：

1、DDL 操作

1.1、库操作

1.1.1、创建库

语法结构：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
　　[COMMENT database_comment]　　　　　　//关于数据块的描述
　　[LOCATION hdfs_path]　　　　　　　　　　//指定数据库在HDFS上的存储位置
　　[WITH DBPROPERTIES (property_name=property_value, ...)];　　　　//指定数据块属性

默认地址：/user/hive/warehouse/db_name.db/table_name/partition_name/…

创建库的使用方式：

1、 创建普通库

hive> create database potter;
OK
Time taken: 2.284 seconds
hive> show databases;
OK
default
myhive
potter
Time taken: 0.034 seconds, Fetched: 3 row(s)
hive> 

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

0: jdbc:hive2://potter2:10000> create database if not exists t1;
No rows affected (1.413 seconds)
0: jdbc:hive2://potter2:10000> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| myhive         |
| potter         |
| t1             |
+----------------+
4 rows selected (0.244 seconds)
0: jdbc:hive2://potter2:10000>

3、 创建库的时候带注释

0: jdbc:hive2://potter2:10000> create database if not exists t2 comment 'learning hive';
No rows affected (0.387 seconds)
0: jdbc:hive2://potter2:10000> desc database t2;
+----------+----------------+------------------------------------------+-------------+-------------+-------------+
| db_name  |    comment     |                 location                 | owner_name  | owner_type  | parameters  |
+----------+----------------+------------------------------------------+-------------+-------------+-------------+
| t2       | learning hive  | hdfs://myha01/user/hive/warehouse/t2.db  | potter      | USER        |             |
+----------+----------------+------------------------------------------+-------------+-------------+-------------+
1 row selected (0.244 seconds)
0: jdbc:hive2://potter2:10000> 

4、 创建带属性的库

0: jdbc:hive2://potter2:10000> create database if not exists t3 with dbproperties('creator'='hadoop','date'='888');
No rows affected (0.447 seconds)
0: jdbc:hive2://potter2:10000>

0: jdbc:hive2://potter2:10000> desc database extended t3;
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
| db_name  | comment  |                 location                 | owner_name  | owner_type  |         parameters          |
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
| t3       |          | hdfs://myha01/user/hive/warehouse/t3.db  | potter      | USER        | {date=888, creator=hadoop}  |
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
1 row selected (0.219 seconds)
0: jdbc:hive2://potter2:10000> 

1.1.2、查看库

1、查看有哪些数据库

0: jdbc:hive2://potter2:10000> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| myhive         |
| potter         |
| t1             |
| t2             |
| t3             |
+----------------+
6 rows selected (0.255 seconds)
0: jdbc:hive2://potter2:10000> 

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

语法：desc database [extended] dbname;

示例：desc database extended myhive;

0: jdbc:hive2://potter2:10000> desc database extended t3;
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
| db_name  | comment  |                 location                 | owner_name  | owner_type  |         parameters          |
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
| t3       |          | hdfs://myha01/user/hive/warehouse/t3.db  | potter      | USER        | {date=888, creator=hadoop}  |
+----------+----------+------------------------------------------+-------------+-------------+-----------------------------+
1 row selected (0.219 seconds)
0: jdbc:hive2://potter2:10000> 

3、查看正在使用哪个库

0: jdbc:hive2://potter2:10000> select current_database();
+----------+
|   _c0    |
+----------+
| default  |
+----------+
1 row selected (7.572 seconds)
0: jdbc:hive2://potter2:10000> 

4、查看创建库的详细语句

0: jdbc:hive2://potter2:10000> show create database t3;
+----------------------------------------------+
|                createdb_stmt                 |
+----------------------------------------------+
| CREATE DATABASE `t3`                         |
| LOCATION                                     |
|   'hdfs://myha01/user/hive/warehouse/t3.db'  |
| WITH DBPROPERTIES (                          |
|   'creator'='hadoop',                        |
|   'date'='888')                              |
+----------------------------------------------+
6 rows selected (0.228 seconds)
0: jdbc:hive2://potter2:10000>

1.1.3、删除库

删除库操作：

drop database dbname;

drop database if exists dbname;

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

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

2、 使用 cascade 关键字

drop database if exists dbname cascade;

默认情况下就是 restrict

drop database if exists myhive ==== drop database if exists myhive restrict

示例：

删除不包含表的数据库：

0: jdbc:hive2://potter2:10000> show tables in potter;
+-----------+
| tab_name  |
+-----------+
+-----------+
No rows selected (0.271 seconds)
0: jdbc:hive2://potter2:10000> drop database potter;
No rows affected (2.641 seconds)
0: jdbc:hive2://potter2:10000> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| myhive         |
| t1             |
| t2             |
| t3             |
+----------------+
5 rows selected (0.315 seconds)
0: jdbc:hive2://potter2:10000> 

删除包含表的数据库：

0: jdbc:hive2://potter2:10000> drop database if exists t3 cascade;
No rows affected (0.372 seconds)
0: jdbc:hive2://potter2:10000> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| myhive         |
| t1             |
| t2             |
+----------------+
4 rows selected (0.262 seconds)
0: jdbc:hive2://potter2:10000> 

1.1.4、切换库

切换库操作：

语法：use database_name；

实例：use myhive;

0: jdbc:hive2://potter2:10000> select current_database();
+----------+
|   _c0    |
+----------+
| default  |
+----------+
1 row selected (1.143 seconds)
0: jdbc:hive2://potter2:10000> use myhive;
No rows affected (0.219 seconds)
0: jdbc:hive2://potter2:10000> select current_database();
+---------+
|   _c0   |
+---------+
| myhive  |
+---------+
1 row selected (0.303 seconds)
0: jdbc:hive2://potter2:10000> 

1.2、表操作

1.2.1、创建表

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]

详情请参见：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

2、 建表语句相关解释

CREATE TABLE：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），如果不存在，则会自动创建该目录。Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。（经典面试问题）

外部