hive修改字段类型为分区字段 hive中修改字段类型长度

蛋蛋 和 小智 今天又在“打情骂俏”，他们今天在谈论分区表和分桶表，走，我们去听听。

这天，蛋蛋去茶水间倒水，他把水杯放在饮水机下面，打开开关，一直盯着墙上的画在看，灵魂仿佛已经飞了出去。直到杯子的水都满出来，也没察觉。

这时，小智也去倒水，拍了一把蛋蛋，嘲讽道：“蛋总，你想啥呢，倒杯水都心不在焉？” 蛋蛋一脸尴尬，“前些天看了你写的 Hive SQL 语法，看到建表的时候，有好多种表类型，什么分区表和分桶表，想不明白它们到底有啥区别，实际有啥作用......”。

小智提高了三个音调：“蛋啊，你这种勤奋劲儿，让我很感动，来，咱们来探讨下” 小智把白板拖了过来，简单画了一个思维草图，“其实 Hive 就只有四种表类型”

分区表

“一件事情的存在必然有其意义，分区是为了解决什么问题？”，小智自问自答道，“ 从存在的意义来说，分区最重要的原因是为了更快的查询。

比如数据按天组织的话(通常是日志)，查询的时候，只需要把天作为分区条件，每次只查询指定范围的日期，底层也只返回指定日期的数据，会大大提高了效率。

从文件上来看，分区是 hdfs 的一个目录，可以指定多个分区，这样在插入数据的时候，hdfs 会产生多个目录。”

说完，小智打开了 hue ，熟练的敲了一个建表语句：

create table if not exists par_test(
 name string,
 nid int,
 phone string,
 ntime date
 )
 partitioned by (year string,month string) 
 row format delimited 
 fields terminated by "|"
 lines terminated by "\n"
 stored as textfile;

写完，顺便解释道：“这个表，声明了两个分区字段，year 和 month”。 既然声明了一个表，还需要往里面添加数据。

insert overwrite table par_test partition (year,month) 
select name,nid,phone,ntime,'2020','12' from origin_test;

这里，我们往分区表的 year = '2020' 和 month = '12' 写了一些数据进去。这里的 year 和 month 就是静态分区。 蛋蛋接着就问道：“既然有静态分区，是不是还会有动态分区，自动生成的那种？” “对是的，假如中国有50个省，每个省有50个市，每个市都有100个区，那我们都要使用静态分区要使用多久才能搞完。所有我们要使用动态分区。 当然，动态分区也不能无限制的创建。想象一下，万一程序员某天没睡醒，误使用了时间戳字段作为分区条件，那会产生巨多分区。 所以 Hive 默认是严格模式，也就是至少得有一个字段是静态的字段。 当然我们也可以修改为非严格模式。”

set hive.exec.dynamic.partition.mode=nonstrict  //分区模式，默认nostrict
set hive.exec.dynamic.partition=true      //开启动态分区,默认true
set hive.exec.max.dynamic.partitions=1000   //最大动态分区数,默认1000

-- 一个字段使用静态分区，一个字段使用动态分区
insert overwrite table par_test partition (year='2020',month) 
select name,nid,phone,ntime,month from origin_test;

-- 两个字段都使用动态分区
insert overwrite table par_test partition (year,month) 
select name,nid,phone,ntime,year,month from origin_test;

注意：动态分区的字段，需要按顺序放在 select 字段的最后面

外部表和管理表

讲到外部表，那么日志场景用的会很多。 通常服务器上会每时每刻产生日志，而这些日志会实时的发送到消息中间件，再通过 flume 等工具直接存储到 hdfs 上了，并没有 hive 什么事。 但 hive 能把 hdfs 的文件映射成一张表，那么这种表就是外部表。 从技术上来说，被 external 关键字修饰的就是外部表(external table)，未被 external 修饰的表是管理表(managed table)。

• 外部表的数据由 hdfs 管理，而内部表的数据由 hive 管理。
• 内部表数据存储的位置是hive.metastore.warehouse.dir(默认：/user/hive/warehouse)，外部表数据的存储位置由自己制定(如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里)；
• 删除内部表会直接删除元数据(metadata)及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；
• 对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复(MSCK REPAIR TABLE table_name)

说完，小智又打开了 hue 写了一个建表语句：“这是一个内部表”

create table t1(
    id      int
   ,name    string
   ,hobby   array<string>
   ,add     map<String,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
;

这是一个外部表，表数据放在了 /user/t2 下

create external table t2(
    id      int
   ,name    string
   ,hobby   array<string>
   ,add     map<String,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/user/t2'
;

如果删除了外部表，那么 /user/t2 的数据是不会被删除掉的。而删除了内部表，内部表所在的 hdfs 目录就被删除了。

分桶表

蛋蛋又开始满脸愁容，问道：“既然有了分区表，为什么还要分桶表，这两者有什么区别？” 小智笑了一下，“对，分区提供了一个隔离数据和优化查询的便利方式，但是，并不是所有的数据集都可形成合理的分区。 假设一个表的一级分区是 dt，二级分区是 user_id，那么这种划分方式可能导致太多的小分区，如果使用动态分区，创建超多的目录，hdfs 爸爸肯定就要炸了。 所以分桶表，是将一个完整的数据集分成若干部分。它存在的意义是：一是提高 join 查询的效率；二是利于抽样。 分桶表的实质，就是对分桶的字段做了hash 然后存放到对应文件中，也就是说向分桶表中插入数据的时候必然要执行一次MAPREDUCE,所以分桶表的数据只能通过从结果集查询插入的方式进行导入。 蛋蛋又问道：“为什么分桶表，可以优化 join查询的效率？” 小智耐心的说：“桶给表加上了额外的结构，在进行某些查询的时候可以利用这个结构进行高效的查询； 例如：对于两个数据表，某两列都做了桶划分，可以使用map端的join高效的完成join(桶和桶之间的join，大大减少了join的次数)。 对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper 知道右边表内相匹配的行在对应的桶内。 因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接” 蛋蛋摸了摸后脑勺，“可能我对于 MapReduce 的原理还不是很了解，后面我去学习一下” 小智点了点头，“我们尝试着建立一个分桶表。”

create table bck_student(
  id int,
  name string,
  sex string, 
  age int,
  department string) 
  clustered by(sex) into 2 buckets 
  row format delimited 
  fields terminated  by ",";

主要注意的地方就是  clustered by(sex) into 2 buckets ，这里声明了对 sex 分桶，并且分成 2 份。

-- 创建一个临时表
create table student(
  id int,
  name string,
  sex string, 
  age int,
  department string)  
  row format delimited 
  fields terminated  by ",";
 
-- 向临时表中加载数据
load data local inpath "/home/hadoop/student.dat"  into table student;

-- 临时查询一下，是否导入数据成功
select * from sutdent;

对源数据的 sex 字段做 hash ，并把数据插入到 目标表中

set hive.enforce.bucketing=true;
set mapreduce.job.reduces=2;

-- 插入
insert into table bck_student 
select id,name,sex,age,department 
from student 
distribute by sex;

可以在 hdfs 目录上查看一下结果，会把原始数据集分成2份文件来存储。 蛋蛋去 hdfs 上查看了一下文件，果然被分成了两份，说了一句：“贼秒！”

总结

小智看着时间，已经是中午11点20了，肚子饿的咕咕叫，蛋蛋今天也仿佛有拨开云雾见晴天的感觉。 “我来总结一下今天学到的新东西。 今天对 Hive 的表类型有了更加充分的认识，在不同的场景我们应该使用不同类型的表。 如果数据是多个表共享的，可以使用外部表。 如果数据是按照某种规律来组织的，使用分区表更好一点。 如果表的数据量超多，又有多表关联的场景，那么可以使用分桶表，来优化 join 查询。” 说完，哥俩愉快的出(GAO)门(JI)了。