Hive 恢复 drop table 数据

前言

工作中处理hive数据的时候, 没有控制好权限, 手滑执行了一条drop table 语句, 把一个生产环境的大表给删了. 隐藏掉心里的惊涛海浪, 我故作镇定地打开搜索引擎寻找解决方案. 天无绝人之路, 解决方案确实存在!

由于hive底层实际上是依赖HDFS, drop 操作实际上是HDFS的一次rm操作, 所以利用 HDFS 的 Trash 回收站功能可以实现数据的恢复.

这里需要强调一下, 数据得以恢复需要满足以下3个前提 (OS: 老子真是幸运啊~):

HDFS 开启了Trash (回收站) 功能, 一般对于线上生产环境这是必备的;
删除数据使用的是 drop 操作, hive 中使用truncate命令将表截断的话，它是不会进回收站的，是没办法恢复的。这个跟oracle truncate有点类似的。
被删除的表的建表语句有备份. 如果是 text 类型表, 有表结构即可. 但如果是分区分桶表或ORC表, 需有与原来完全一致的建表语句, 否则数据载入会有问题.

HDFS Trash功能

对于线上生产环境的HDFS，开启回收站功能是必不可少的。该功能类似于linux系统的回收站设计，HDFS会为每个用户创建一个专属的回收站目录（/user/${user.name}/.Trash），用户删除文件时，实际上是被移动到了回收站目录。用于预防当用户误删HDFS上的数据时，能够及时从回收站恢复这些数据。

HDFS 开启 Trash 功能的配置在 core-site.xml 中:

<name>fs.trash.interval</name>
<value>0</value>
<description>Number of minutes after which the checkpoint gets deleted.  
  If zero, the trash feature is disabled. 
  This option may be configured both on the server and the client. 
  If trash is disabled server side then the client side configuration is checked. 
  If trash is enabled on the server side then the value configured on the server is used 
  and the client configuration value is ignored.
</description>

描述：单位(minute)，回收站数据判断是否需要清理的检查周期，默认值为0 (如果集群未自定义设置且fs.trash.interval大于0，则 fs.trash.checkpoint.interval=${fs.trash.interval})

<name>fs.trash.checkpoint.interval</name>
<value>0</value>
<description>Number of minutes between trash checkpoints. 
  Should be smaller or equal to fs.trash.interval. 
  If zero, the value is set to the value of fs.trash.interval.
  Every time the checkpointer runs it creates a new checkpoint out of current 
  and removes checkpoints created  more than fs.trash.interval minutes ago.
</description>

Hive 表数据恢复

确定 HDFS 已配置 Trash 后, 一个比较直接的想法就是使用 hdfs dfs -mv 将文件恢复至原来的位置即可. 实际上, 这样在 hive 中无法读取数据. 因为表被 drop 掉以后, 在mysql的元数据库中已经没有数据了，那就得需要将这些数据的信息重新写入到mysql中, 即重新建表。

原路径恢复

将回收站中的数据 cp 到原位置

hdfs dfs -cp /user/hive/.Trash/Current/hive/warehouse/default.db/t_ods_order /hive/warehouse/default.db/t_ods_order

然后连接 hive 重新建表 (表结构、分区分桶以及存储格式与原来保持一致):

CREATE TABLE t_ods_order(
  serverid decimal(10,0) DEFAULT NULL COMMENT '  ', 
  ordersno decimal(10,0) DEFAULT NULL COMMENT '  ', 
  orderdate decimal(10,0) DEFAULT NULL COMMENT '  ', 
  etl_fl_nm string DEFAULT NULL COMMENT '  ', 
  ppn_tmstamp timestamp DEFAULT NULL COMMENT '  ', 
  busi_date DATE DEFAULT NULL COMMENT '  '
)
PARTITIONED BY RANGE (busi_date) ( 
  PARTITION partition_200909 VALUES LESS THAN ('2009-10-01'), 
  PARTITION partition_200910 VALUES LESS THAN ('2009-11-01'), 
  PARTITION partition_200911 VALUES LESS THAN ('2009-12-01'), 
CLUSTERED BY (serverid)
INTO 6 BUCKETS
STORED AS ORC
LOCATION '/hive/warehouse/default.db/t_ods_order'
TBLPROPERTIES ("transactional"="true");
;

完成建表后数据恢复成功.

使用外表恢复(推荐)

由于建表时指定 location 要求建表用户和 location 对应目录的 owner 一致, 这意味着要使用较高权限对 HDFS 中的生产数据进行操作, 这意味着比较大的风险, 且对于一个健全的数据仓库团队来说, 也意味着更复杂的流程.

一个相对安全的方案是, 在较低权限用户状态下使用外表将待恢复数据导入, 然后在通过 sql 将外表数据 insert 进入正式表, 这一过程中, 仅在重建正式表时涉及到较高权限用户的操作, 并不直接操作HDFS.

将回收站中的数据 cp 到临时位置:

hdfs dfs -cp /user/hive/.Trash/Current/hive/warehouse/default.db/t_ods_order /tmp/t_ods_order_recover

连接 hive 创建外表 (指定location为临时位置):

CREATE EXTERNAL TABLE t_ods_order_recover(
  serverid decimal(10,0) DEFAULT NULL COMMENT '  ', 
  ordersno decimal(10,0) DEFAULT NULL COMMENT '  ', 
  orderdate decimal(10,0) DEFAULT NULL COMMENT '  ', 
  etl_fl_nm string DEFAULT NULL COMMENT '  ', 
  ppn_tmstamp timestamp DEFAULT NULL COMMENT '  ', 
  busi_date DATE DEFAULT NULL COMMENT '  '
)
PARTITIONED BY RANGE (busi_date) ( 
  PARTITION partition_200909 VALUES LESS THAN ('2009-10-01'), 
  PARTITION partition_200910 VALUES LESS THAN ('2009-11-01'), 
  PARTITION partition_200911 VALUES LESS THAN ('2009-12-01'), 
CLUSTERED BY (serverid)
INTO 6 BUCKETS
STORED AS ORC
LOCATION '/tmp/t_ods_order_recover'
TBLPROPERTIES ("transactional"="true");
;

使用高权限用户重新创建正式表 (不指定location):

CREATE TABLE t_ods_order(
  serverid decimal(10,0) DEFAULT NULL COMMENT '  ', 
  ordersno decimal(10,0) DEFAULT NULL COMMENT '  ', 
  orderdate decimal(10,0) DEFAULT NULL COMMENT '  ', 
  etl_fl_nm string DEFAULT NULL COMMENT '  ', 
  ppn_tmstamp timestamp DEFAULT NULL COMMENT '  ', 
  busi_date DATE DEFAULT NULL COMMENT '  '
)
PARTITIONED BY RANGE (busi_date) ( 
  PARTITION partition_200909 VALUES LESS THAN ('2009-10-01'), 
  PARTITION partition_200910 VALUES LESS THAN ('2009-11-01'), 
  PARTITION partition_200911 VALUES LESS THAN ('2009-12-01'), 
CLUSTERED BY (serverid)
INTO 6 BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true");
;

从外表向正式表恢复数据:

INSERT INTO TABLE t_ods_order PARTITION (busi_date)
SELECT * FROM t_ods_order_recover;

浅尝记