Hive-2 Hive的DDL操作

DDL是数据库模式定义语言，是用于描述数据库中要存储的现实世界实体的语言。

数据库DDL操作

创建数据库

hive > create database myhiv;
或者
hive > create database if not exists db_hive;
或者 
 create database if not exists testnew 
 comment "testDB" 
 location '/hiveTestDB'
 with dbproperties('creator'='ZS');

数据库在HDFS上的默认存储路径是/user/hive/warehouse/数据库名.db

显示所有数据库

hive> show databases;

模糊查询数据库

hive> show databases like 'db_hive*';

查看数据库详情

hive> desc database extended db_hive;

切换当前数据库

hive > use db_hive;

删除数据库

#删除为空的数据库
hive> drop database db_hive;

#如果删除的数据库不存在，最好采用if exists 判断数据库是否存在
hive> drop database if exists db_hive;

#如果数据库中有表存在，这里需要使用cascade强制删除数据库
hive> drop database if exists db_hive cascade;

官网：数据库DDL

表DDL操作

建表语法

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] row format delimited fields terminated by “分隔符”
[STORED AS file_format]
[LOCATION hdfs_path]

字段解释说明

• CREATE TABLE 创建一个指定名字的表

• EXTERNAL 创建一个外部表，在建表的同时指定一个指向实际数据的路径**（LOCATION）**，指定表的数据保存在哪里

• COMMENT 为表和列添加注释

• PARTITIONED BY 创建分区表

• CLUSTERED BY 创建分桶表

• SORTED BY 按照字段排序（一般不常用）

• ROW FORMAT 指定每一行中字段的分隔符,hive默认分隔符是’\001’，非打印字符

  • row format delimited fields terminated by ‘\t’

• STORED AS 指定存储文件类型

  • 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、ORCFILE（列式存储格式文件）
  • 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE

• LOCATION 指定表在HDFS上的存储位置。

官网地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

删除表

• 只删除元数据 truncate table，只能用来删除内部表，否则报错

# Currently the target table should be native/managed
# table or an exception will be thrown.
TRUNCATE [TABLE] table_name 
[PARTITION partition_spec];
#example:
truncate table stu;

• 同时删除元数据和表数据 drop table

#按条件删除部分数据
insert overwrite table stu select * from employee_table
 where id>'100'

DROP TABLE [IF EXISTS] table_name [PURGE];
-- (Note: PURGE available in Hive 0.14.0 and later)
#example:
drop table if exists stu;

• 也可以用insert overwrite+where的方法来删除部分和全部数据(表还在)

创建内部表

内部表类型为为Managed_Table

1. 使用标准语句直接建表，通过insert into像表中插入数据

不建议使用，每个insert语句转换成mr后会生成一个小文件。通过大量的insert into语句插入数据会产生大量的小文件

create table stu(id int, name string);
insert into stu(id,name) values(1,"zhangsan");
insert into stu(id,name) values(2,"lisi");

2. 查询建标法，通过AS查询完成建表，将子查询的结果存入到新表里。表中有数据

create table if not exists hivetest.stu1 as select id, name from stu;

3. like建标法，通过like和已经存在的表结构创建表。表中没有数据

create table if not exists hivetest.stu1 as select id, name from stu;

4. 查询表的类型

hive > desc formatted hivetest.stu;

创建外部表

• 外部表需要制定其他的HDFS路径的数据加载到表中来，因此hive表不认为自己独占这份数据，删除表的时候只会删除元数据表，不会删除HDFS中的表数据
• 创建外部表的时候需要external关键字
• 和内部表一样，location字段可以指定，也可以不指定
  • 指定就是数据存放的具体位置，使用put+location方式创建表和加载数据需要再使用msck repair建立hive元数据和HDFS上的表数据的映射(见后面的列子)
  • 不指定就是默认位置：user/hive/warehouse

create external table myhive.teacher (t_id string, t_name string) 
row format delimited fields terminated by '\t';

向外部表中加载数据

1. 外部表也可以向内部表一样通过insert方式插入数据，不推荐
2. 通过load加载数据到外部表(load方式也可以加载数据到内部表)

• 通过load+local关键字从本地文件系统加载数据，本地数据不会消失，类似拷贝

load data local inpath 'hivedatas/teacher.csv' into table hivetest.teacher;

• 通过load从HDFS加载数据，加载源的HDFS数据会消失，类似剪切

load data inpath '/hivedatas' overwrite into table hivetest.teacher;

msck repair举例

• 创建外部分区表，并指定文件数据存放目录

hive (myhive)> create external table score4(s_id string, c_id string, s_score int) 
partitioned by (day string) 
row format delimited fields terminated by '\t' 
location '/scoredatas';

• 进行表的修复，说白了就是建立我们表与我们数据文件之间的一个关系映射()

hive (myhive)> msck repair table score4;

• 修复成功之后即可看到数据已经全部加载到表当中去了

内部表和外部表的相互转换

1. 内部表转外部表

#将stu内部表改为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');

2. 外部表转内部表

#把teacher外部表改为内部表
alter table teacher set tblproperties('EXTERNAL'='FALSE');

内部表和外部表的区别

1. 建表时的语法区别
   • 外部表创建时需要使用指定关键字external
2. 执行删除表操作后的区别
   • 删除内部表会同时删除metastore（一般使用MySQL存储）中的元数据和HDFS上的表数据
   • 删除外部表只会删除metastore（一般使用MySQL存储）中的元数据，HDFS上的表数据还在

内部表和外部表的应用实际

总体上讲，一般确定表只有自己使用，非公用的情况下使用内部表。除了自己，还有别人使用的时候用外部表。实际中，外部表应用居多

• 一般外部表在数仓中的ODS层(贴源层)中使用
• 一般内部表在数仓中的DW(细节层)中使用