Hive 的DDL数据定义（增删改）

Hive 的DDL数据定义

一、数据库操作

官网： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDLCreateTableCreate/Drop/TruncateTable

1、创建数据库

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

hive（default）> create database demodb01;

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

hive（default）> create database if not exists demodb01;

③ 创建数据库，并写入注释

create detabase if not exists  demodb01 comment "create hivedb named demodb01";

④ 创建带属性的库

create database if not exists dbname with dbproperties ('a'='aaa','b'='bbb'); 

create database if not exists myhive with dbproperties ('a'='aaa','b'='bbb'); 

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

hive（default）> create database demodb location '/demodb.db';

2、查询数据库

① 查看正在使用的库：select current_database(); 

② 查看数据库列表：show databases;

③ 切换数据库：use demodb01;

④ 过滤查看数据库：

•  show databases like 'demodb01';
• show databases like '%db%';
• show databases like 'demodb%';
• show databases like '*db*';
• show databases like '*db*';

⑤ 显示数据库信息：

• desc database demodb01;  
• desc database extended demodb01;

3、修改数据库

用户可以使用alter database 命令为某个数据库的 dbproperties 设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

如：alter database demodb01 dpproperties (createtime=20191203);

4、删除数据库

默认情况不允许删除有表的数据库 restrict ，但可以使用cascade强制删除。

• drop database demodb01 [restrict];
• drop database if exists demodb01 [restrict];
• drop database if exists demodb01 cascade; #强制删除

二、表操作

官网： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDLCreateTableCreate/Drop/TruncateTable

1、创建表

基本注释：

---

create table：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，用IF NOT EXISTS选项来忽略这个异常。
external关键字创建外部表，在建表的同时指定一个指向实际数据的路径（location），如果不存在，则会自动创建该目录。
Hive创建内部表时，会将数据移动到数据仓库指向的路径；创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

外部表和内部表的选择： 1、 如果数据已经存储在 HDFS 上了，然后需要使用 Hive 去进行分析，并且该份数据还 有可能要使用其他的计算引擎做计算之用，请使用外部表 2、 如果一份数据仅仅只是使用 Hive 做统计分析，那么可以使用内部表 。
location 路径指定选择：不管使用内部表和外部表，表的数据存储路径都是可以通过 location 指定的！！！！！！ 推荐方式： 1、创建内部表的时候，最好别指定 location，就存储在默认的仓库路径； 2、如果要指定外部路径，那么请创建该表为外部表 。

---

partitioned by 在 Hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要 的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入 partition 概念。分区表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下， 分区是以字段的形式在表结构中存在，通过 desc table 命令可以查看到字段存在，但是 该字段不存放实际的数据内容，仅仅是分区的表示。 注意：分区表是不能直接存储数据文件的。必须要放置在分区目录下 

分区建表分为 2 种： 一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录； 一种是多分区，表文件夹下出现多文件夹嵌套模式。

---

LIKE：允许用户复制现有的表结构，但是不复制数据。 （不会复制是内部表还是外部表这个属性） 示例1：create table tableA like tableB;（创建一张 tableA 空表复制 tableB 的结构，不管 tableB 是内部表或是外部表， tableA 都是内部表） 。示例2 :  create external table if not exists tableA like tableB; (创建一张 tableA 空表复制 tableB 的结构，不管 tableB 是内部表或是外部表， tableA 都是外部表)。

---

COMMENT：可以为表与字段增加描述信息，企业环境中推荐每个字段加注释。 

---

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 TEXTFILE | SEQUENCEFILE | RCFILE 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，默认也是 textFile 格式，可以通 过执行命令 set hive.default.fileformat;，进行查看，如果数据需要压缩，使用 STORED AS SEQUENCEFILE。 

• A、默认格式 TextFile，数据不做压缩，磁盘开销大，数据解析开销大。可结合 gzip、bzip2 使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive 不会对数据进行切 分，从而无法对数据进行并行操作  
• B、SequenceFile 是 Hadoop API 提供的一种二进制文件支持，文件内容是以序列化的 kv 对象来组织的，其具有使用方便、可分割、可压缩的特点。SequenceFile 支持三种压缩 选择：NONE，RECORD，BLOCK。Record 压缩率低，一般建议使用 BLOCK 压缩  
• C、RCFILE 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个 record 在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利 于数据压缩和快速的列存取。相比 TEXTFILE 和 SEQUENCEFILE，RCFILE 由于列式存储方式， 数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次 写入、多次读取，因此，整体来看，RCFILE 相比其余两种格式具有较明显的优

---

CLUSTERED BY 对于每一个表（table）或者分区，Hive 可以进一步组织成桶，也就是说桶是更为细 粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后 除以桶的个数求余的方式决定该条记录存放在哪个桶当中。 把表（或者分区）组织成桶（Bucket）有两个理由： （1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能 利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以 使用 Map 端连接（Map Join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一 个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操 作就可以，可以大大较少 JOIN 的数据量。 （2）使取样（Sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段， 如果能在数据集的一小部分数据上试运行查询，会带来很多方便。  

---

LOCATION：指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指 定就在默认的仓库路径。  最佳实践：  如果创建内部表请不要指定 location ，如果创建表时要指定 location，请创建外部表。  

---

Hive建表示例：

Hive建表示例代码	注释
create external table if not exists demo05  (viewTime int,userid bigint,page_url string,referrer_url string,  ip string comment "ip address of the user")  comment "this is the page view table"  partitioned by (dt string,country string)  clustered by (userid) sorted by (viewTime asc) into 31 buckets  row format delimited  fields terminated by '\t'  collection items terminated by ","  map keys terminated by ":"  lines terminated by "\n"  stored as textfile  location "/hive_external_demo05";	create external table 创建一个外部表 字段及类型，... ip string comment "字段注释" comment "表注释" partitioned by(创建分区字段) clustered by (分桶字段)sorted by (排序字段)into 分桶数 buckets 行格式分隔 字段分割："\t" --MAP STRUCT 和 ARRAY 的分隔符："," -- MAP中的key与value的分隔符：":" 行以“\n”结尾 存储为文本文件 存储位置
desc formatted demo05;	查看表结构
create table if not exists demo06 (id int comment '编号',name string comment '姓名')  comment "这是一个内部表"  row format delimited fields terminated by ','  stored as textfile;	创建一个内部表
create external table if not exists demo07 (id int ,name string comment 'The name field')  comment "this is the external table"  row format delimited fields terminated by ','  lines terminated by '\n'  stored as textfile  location "/hive_external_demo07";	创建一个外部表
create table demo08_ptn (id int, name string)  partitioned by (sex string)   row format delimited fields terminated by ','  stored as textfile;   load  data  local  inpath '/home/hdp/mingxing.txt'  overwrite into table demo08_ptn partition(sex='boy');    load  data  local  inpath '/home/hdp/mingxing.txt'  overwrite into table demo08_ptn partition(sex='girl');   show partitions demo08_ptn;	创建一个分区表；   插入男分区数据；   插入女分区数据；   查询表分区；
create table demo09_buck (Sno int ,Sname string, Sex string,Sage int,Sdept string)  clustered by(Sno) sorted by(Sno desc) into 4 buckets  row format delimited fields terminated by ',';   insert into demo09_buck select id as sno , name as sname, sex as sex, age as sage , department as sdept from student;	创建一个分桶表；   往分桶表插入数据： 切忌不能手动导入数据， 必须使用 insert….select….形式
create table new_table like mytable;  create external table if not exists new_table like mytable;	拷贝表结构到新的内部表，mytable可以是内部表或外表； 拷贝表结构到新的外部表，mytable可以是内部表或外表；

2、修改表

① 重命名表：ALTER TABLE table_name RENAME TO new_table_name;

② 修改表属性：

•  语法结构： ALTER TABLE table_name SET TBLPROPERTIES table_properties; table_properties: (property_name = property_value, property_name = property_value, ... )  
• 实例：alter table table_name set tblproperties ('comment' = 'my new students table'); 不支持修改表名及表的数据存储目录 

③ 修改 SerDe 信息：

• 语法结构： ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; 
• ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; 

serde_properties  : (property_name = property_value, property_name = property_value, ... )

•  实例： 更改列分隔符：alter table student set  serdeproperties ('field.delim' = '-'); 

④ 增加/删除/改变/替换列 ：

•  ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
• ALTER TABLE name CHANGE COLUMNS c_name new_name new_type [FIRST|AFTER c_name]
• ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

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

⑤ 增加/删除分区：

1. 增加分区语法结构： ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...   
2. 删除分区语法结构： ALTER TABLE table_name DROP partition_spec, partition_spec, ... 

partition_spec : PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)  

• 添加/修改/删除分区示例： 
• ALTER TABLE student_p ADD partition(part='a') partition(part='b');  // 不指定分区路径，默认路径
•  ALTER TABLE student_p ADD IF NOT EXISTS  partition(part='bb') location '/myhive_bb' partition(part='cc') location '/myhive_cc';  // 指定分区路径
• ALTER TABLE student_p partition (part='bb') SET location '/myhive_bbbbb'; // 修改分区路径示例
• ALTER TABLE student_p DROP if exists partition(part='aa'); // 删除分区示例
• ALTER TABLE student_p DROP if exists partition(part='aa') if exists partition(part='bb'); // 删除多个分区示例  
• alter table student_p partition (part='aa') enable no_drop; //防止分区被删除：
• alter table student_p partition (part='aa') enable offline; //防止分区被查询：enable 和 disable 是反向操作 

⑥ 删除/清空表：

• drop table if exists mytable; //删除表
• TRUNCATE TABLE table_name [PARTITION partition_spec]; //清空表语法
• truncate table student; //清空表
• truncate table student_ptn partition(city=’beijing’);  //清空分区表

3、其他辅助命令