Hive DDL数据定义语言

  DDL (Data Definition Language) 数据定义语言。DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。建库建表并不会经常建，所以 DML 比 DDL 更重要。

1.创建数据库

Ⅰ.建库语句

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

字段介绍： ([ ] 中括号中内容为创建库时的可选项)
 
  1.COMMENT：数据库描述
 
  2.LOCATION：指定数据库存放在 HDFS 的目录，默认存储在 hive 所指定的地址 /user/hive/warehouse/目录下，HDFS中数据库通常统一为 *.db。比如创建了一个 test库，在HDFS中默认地址为：/user/hive/warehouse/test.db
 
  3.WITH DBPROPERTIES：DB数据库属性，设置数据库属性信息，以使用 key=value 格式添加，比如：创建时间、创建人、修改人 等信息。不常用

---

注意：默认创建的库，存储在 Hive 默认路径下，如果通过 LOCATION 属性修改库所在的位置。如果指定库存储在 HDFS 根目录下。建议是 location '/库名.db'，而不是 location '/'。此处存储在根目录下，如果不指定库名.db，那么在 HDFS 根目录下是看不到这个库名的文件夹的，如果在该库中创建表，以表名命名的文件夹会直接在 根目录下，导致数据比较混乱，一般不建议这么干！！！

Ⅱ.查询数据库

先创建一个 testdb 数据库
建库语句：create database if not exists testdb;

1.查看已有的数据库

show databases;  (查询所有的数据库)
 
show databases like 'test*';  (过滤test开头的数据库)

2.使用数据库

use testdb;

3.显示数据库信息

desc database testdb;  (显示数据库基本信息，不包括dbproperties内容)
 
desc database extended testdb;  (显示数据库详细信息，包括dbproperties内容)

Ⅲ.修改数据库dbproperties属性

  用户可以使用 alter database 库名 命令，为某个数据库的 DBPROPERTIES 设置键-值对属性值，来描述这个数据库的属性信息。（数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。hive 暂不支持数据库重命名操作，仅支持修改dbproperties和数据库的用户或权限;）

Ⅲ.删除数据库

1.删除空数据库

drop database testdb;
 
drop database if exists testdb; (如果删除的数据库不确定存在与否，最好采用 if exists 判断一下数据库是否存在)

2.删除非空数据库，采用 cascade 命令强制删除

drop database testdb; cascade;

注意：在手工操作删除库后，MySQL元数据中的元数据信息会被删除，HDFS中相对应的文件夹也会被删除。

2.创建表

Ⅰ.建表语句

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
[LIKE table_name]

字段介绍：([ ] 中括号中内容为创建表时的可选项)
 
1.EXTERNAL：
  external 关键字，可以允许用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和HDFS数据会被一起删除；而外部表只删除元数据，不删除HDFS中实际的数据。（Hive中的表，分为外部表和内部表）
 
2.COMMENT col_comment：
  为表中的每一列属性字段备注，即：列备注
 
3.COMMENT table_comment：
  为表添加备注，即：表备注
 
4.PARTITIONED BY：
  创建分区表，请参考：分区表介绍
 
5.CLUSTERED BY：
  创建分桶表，请参考：分桶表介绍
 
6.SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS：
  对桶中的一个或多个列另外排序，这个也和分桶表有关系，分桶表会指定按照哪个字段进行分桶，以及分几个桶（INTO num_buckets BUCKETS），不常用。在 分桶表中有介绍。请参考：分桶表介绍
 
7.ROW FORMAT：
  用于定义行的格式，如：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。如果没有指定 ROWFORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
 
  SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。
 
8.STORED AS file_format：
  指定存储文件类型。file_format 就类似于 text，lzo，snappy 压缩方式，就是文件格式，按什么存储。Hive对应的是MR，MR可以使用压缩，那么Hive就可以指定压缩格式。默认的是text文本文件
 
  常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、ORCFILE（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE
 
7.LOCATION ：
  指定表在 HDFS 上的存储位置。
 
8.TBLPROPERTIES ：
  对表加一些属性，类似于 DBPROPERTIES，对表加一些属性。
 
9.AS select_statement：
  select_statement 意为 select 筛选语句。Hive 建表时，可以根据另外一张表查询出来的结果来建表。比如A表有10个字段，现在我需要建个表，只要5个字段，但是5个字段都是A表中的，可以根据 create table xx as select id,name,age,address,mobile from user; 用这种方式建表，通过查询的方式建表，同时还附带查询出来的数据哦！！！
 
10.LIKE table_name：
  LIKE 允许用户复制现有的表结构，但是不复制数据。
  eg：create table test1 like test;

Ⅱ.内部表 & 外部表

  Hive 中的表分为内部表和外部表。内部表又称为管理表(MANAGED_TABLE)，没有添加 EXTERNAL 关键字的表都是内部表。真正的类型应该叫做管理表

1.内部表(管理表)和外部表区别

• 管理表：删除时，元数据和 HDFS 数据都会被删除；
• 外部表：删除时，只有元数据会被删除，HDFS 数据不会被删除

2.何时用内部表(管理表)？何时用外部表？

  由于管理表在删除时，会同时将HDFS数据一并删除，所以不适合和其他工具（Spark、Flink、其他系统等）共享使用数据。如果数据除了 Hive 使用，还有其他系统使用，建议建成外部表。内部表利用场景相对较少，只有中间表、临时表等会用到内部表，其他表都会用外部表。

  外部表就更安全一些，防止误操作。表删了，数据还在，我们就可以通过建表语句的方式，创建一个元数据，然后 Hive 就又能操作数据，然后再去查询，就又能找到了。

3.内部表和外部表的转换

  如果建表时忘记指定，写入一段时间数据后发现了问题，本应该是外部表，结果建的却是管理表，该怎么办呢？这种情况下，我们也可以实现内部表和外部表之间的相互转换，省去了建表、拷贝数据等繁琐操作。

1.查询表的类型
 
  可通过命令查询，也可以直接在元数据库中的TBLS表中查看。命令如下：
 
  命令：
   hive (default)> desc formatted test;
   Table Type: MANAGED_TABLE
 
2.管理表 & 外部表转换
 
 1.管理表修改为外部表：alter table test set tblproperties('EXTERNAL'='TRUE');
 
 2.外部表修改为管理表：alter table test set tblproperties('EXTERNAL'='FALSE');
 
 注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

Ⅲ.show 查看已有表的建表语句

  在Hive中建表，我们可以通过一个简单的语句建表，比如create table test(id string); ，我们就可以创建一个 test 表，非常的简单，但是其他需要我们定义的项，我们没有定义则默认使用的都是默认值。

  可以使用命令show create table 表名;来查看默认的建表语句。如果没指定，使用的都是默认配置。
  

Ⅳ.修改表

1.重命名

语法：
 
 ALTER TABLE table_name RENAME TO new_table_name;

2.增加、修改、删除分区

创建分区表，请参考：分区表介绍

3.增加、修改、替换列信息

修改列信息，包括：1.修改列名  2.修改列类型
 
1.更新列
 
语法：
  ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
 
注意：
  在修改列类型时，①int 修改为 String 是允许的；②String 修改为 int 如果 String字段为汉字等，修改提示失败，如果修改成功，查询出来的就有NULL了，所以修改会失败；③如果只需要改列名，而不需要改类型，也需要根据语法，将原来的类型加上

 
2.增加列 & 替换列
 
语法：
  ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
 
注意：
  ADD 是代表新增一字段，字段位置在所有列后面（partition分区列之前），
  REPLACE 则是表示替换表中的所有字段。
 
  更新列使用的 CHANGE ，也可以看做是一种替换，CHANGE 是指定列名的方式修改，只对当前一个指定列生效；（即：如果表中有id和name两个字段，使用 change 将 name 字段 修改为 name1字段，则该表中有 id 和 name1 共2个字段了）
 
  替换列使用的 REPLACE，是以表为维度，对整个表生效，所有字段全部改掉。（即：如果表中有id和name两个字段，使用 replace 替换为 address字段，则该表中就只有一个address字段了）

3.删除列
 
  Hive 并没有提供删除列语法支持，那么如何删除列呢？？
 
  可以使用 REPLACE 来实现。比如表 test 中有 id(int)、name(string)、address(string) 三列，需要删除 address 列，使用 REPLACE 即可。语句：alter table test REPLACE columns (id int,name string);。
 
  在修改表时，操作的都是元数据，HDFS文件的数据是不会变化的。如果由2个字段再改为3个字段，address列依然还是会有数据的。

下一篇：Hive DML数据操纵语言

---

博主写作不易，加个关注呗

求关注、求点赞，加个关注不迷路 ヾ(◍°∇°◍)ﾉﾞ

我不能保证所写的内容都正确，但是可以保证不复制、不粘贴。保证每一句话、每一行代码都是亲手敲过的，错误也请指出，望轻喷 Thanks♪(･ω･)ﾉ