hive怎么修复表数据 hive误删内部表如何找回

目录

写在最前：

1、Hive 与 HDFS 之间的关系

2、Hive 与关系型数据库的区别

一、通过 HDFS 的垃圾回收站恢复

二、通过快照恢复

1、允许快照

2、创建快照：

3、删除快照

4、重命名快照

5、获取可快照的目录信息

6、获取快照差异报告

7、结果

8、使用案例

三、通过源数据恢复到 Hive

1、Hive 数据来源分析

2、日志数据恢复

3、业务数据恢复

4、中间层数据恢复

5、商家端及其它 BI 数据恢复

---

写在最前：

1、Hive 与 HDFS 之间的关系

1. Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的 sql 查询功能，可以将 sql 语句转换为 MapReduce 任务进行运行。其优点是学习成本低，可以通过类 SQL 语句快速实现简单的 MapReduce 统计，不必开发专门的 MapReduce 应用，十分适合数据仓库的统计分析。
2. Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
3. Hadoop Distributed File System，简称 HDFS，是一个分布式文件系统。HDFS 有着高容错性（fault-tolerent）的特点，并且设计用来部署在低廉的（low-cost）硬件上。

2、Hive 与关系型数据库的区别

使用 Hive 的命令行接口，感觉很像操作关系数据库，但是 Hive 和关系数据库还是有很大的不同，以下从宏观的角度比较 Hive 和关系型数据库的区别：

1. Hive 和关系数据库存储文件的系统不同，Hive 使用的是 hadoop 的 HDFS（hadoop 的分布式文件系统），关系数据库则是服务器本地的文件系统；
2. Hive 使用的计算模型是 mapreduce，而关系数据库则是自己设计的计算模型；
3. 关系数据库都是为实时查询的业务进行设计的，而 Hive 则是为海量数据做数据挖掘设计的，实时性很差；实时性的区别导致 Hive 的应用场景和关系数据库有很大的不同；
4. Hive 很容易扩展自己的存储能力和计算能力，这个是继承 Hadoop 的，而关系数据库在这个方面要比数据库差很多。

一、通过 HDFS 的垃圾回收站恢复

使用这种方式的前提是在 HDFS 上面开启 trash 功能，默认是没有开启的。interval 的默认值为 0，单位是分钟。只需要 Hadoop 的配置文件 core-site.xml 中添加下面的内容：

<!--Enable Trash -->
<property>
    <name>fs.trash.interval</name>
    <value>1440</value>
    <description>Number of minutes between trash checkpoints.
    If zero, the trash feature is disabled.
    </description>
</property>

添加好上述内容后，不需要重启后台程序，直接就会生效。

执行删除操作后，会先将文件移动到当前操作用户的 .Trash/Current 目录下面（eg: /user/hadoop/.Trash/Current），举个例子：

# 通过 scribe 用户上传一个测试文件 #
[root@yz-bi-web00 ~]# su - scribe
[scribe@yz-bi-web00 ~]$ cd scribe_log_treater_metl/lib/
[scribe@yz-bi-web00 lib]$ ll
total 67148
-rw-rw-r-- 1 scribe scribe 34401742 May 15 16:30 LogExtractor.tar.gz
...。。
[scribe@yz-bi-web00 lib]$ hdfs dfs -put /home/scribe/scribe_log_treater_metl/lib/LogExtractor.tar.gz /user/scribe/
 
# 上传成功查看 #
[scribe@yz-bi-web00 lib]$ hdfs dfs -ls /user/scribe/
Found 4 items
drwx------   - scribe hadoop          0 2020-05-19 08:00 /user/scribe/.Trash
-rw-r--r--   3 scribe hadoop   34401742 2020-05-19 12:27 /user/scribe/LogExtractor.tar.gz
...。。
 
# 切换用户（为了测试效果），通过 hadoop 用户执行删除操作 #
[scribe@yz-bi-web00 lib]$ exit
logout
[root@yz-bi-web00 ~]# su - hadoop
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/scribe/
Found 4 items
drwx------   - scribe hadoop          0 2020-05-19 08:00 /user/scribe/.Trash
-rw-r--r--   3 scribe hadoop   34401742 2020-05-19 12:27 /user/scribe/LogExtractor.tar.gz
...。。
 
# 删除 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -rm -r -f /user/scribe/LogExtractor.tar.gz
20/05/19 12:30:50 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 1440 minutes, Emptier interval = 0 minutes.
Moved: 'hdfs://yz-xxxx-nn1:9000/user/scribe/LogExtractor.tar.gz' to trash at: hdfs://yz-xxxx-nn1:9000/user/hadoop/.Trash/Current
 
# 通过 hadoop 用户删除后，会将文件移动到 /user/hadoop/.Trash/Current 目录下面，而且是删除文件在 HDFS 上的绝对路径 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/hadoop/.Trash/Current/user/scribe
Found 1 items
-rw-r--r--   3 scribe hadoop   34401742 2020-05-19 12:27 /user/hadoop/.Trash/Current/user/scribe/LogExtractor.tar.gz
 
# 恢复 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -mv /user/hadoop/.Trash/Current/user/scribe/LogExtractor.tar.gz /user/scribe/
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/scribe/
Found 4 items
drwx------   - scribe hadoop          0 2020-05-19 08:00 /user/scribe/.Trash
-rw-r--r--   3 scribe hadoop   34401742 2020-05-19 12:27 /user/scribe/LogExtractor.tar.gz
...。。
 
# 如果确定要删除的文件，直接将文件或目录 drop 掉，不放到 trash 里面，删除的时候使用参数-skipTrash #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -rm -r -f -skipTrash /user/scribe/LogExtractor.tar.gz
Deleted /user/scribe/LogExtractor.tar.gz
[hadoop@yz-bi-web00 ~]$

二、通过快照恢复

Hadoop 从 2.1 版本后开始支持 HDFS 快照（SnapShot）功能：

• 快照创建瞬时性：除去 inode 的查询时间，算法消耗 0（1）复杂度；
• 只有在对快照修改时才会消耗额外内存：内存使用 0（M），M 是被修改的文件或者目录数；
• DateNode 的 block 不被复制：快照文件记录 block 列表和文件大小，不做数据的拷贝复制；
• 快照不会对正常 HDFS 操作产生影响：所有修改都按时间倒序排序，因此当前数据总能被访问到，快照数据是根据与当前数据进行变更部分的差值计算得来的。

一个可以快照的目录最多可以允许同时 65536 个快照同时存在，嵌套的可快照目录目前还不允许。

管理员操作：

1、允许快照

[root@yz-bi-web00 ~]# su - hadoop
[hadoop@yz-bi-web00 ~]$ hdfs  dfsadmin  -allowSnapshot  <path>
## <path> 即想创建快照的目录的路径。通过上述命令将一个目录变成可快照的目录
## 恢复 Hive 数据，必须事先把 /user/hive/warehouse 目录变为可快照目录，并且定期创建新快照

2、创建快照：

一般使用普通用户操作，此用户需要有操作可快照目录的权限，最好是该目录的 owner。管理员可以进行任何操作。

[hadoop@yz-bi-web00 ~]$ hdfs  dfs  -createSnapshot  <path>  [<snapshotName>]
## <path> 可快照目录的路径，<snapshotName> 快照的名称，可以不写，默认会生成一个格式为 's'yyyyMMdd-HHmmss.SSS

3、删除快照

[hadoop@yz-bi-web00 ~]$ hdfs  dfs -deleteSnapshot  <path>  <snapshotName>
## <path> 可快照目录的路径 ，<snapshotName> 快照的名称

4、重命名快照

[hadoop@yz-bi-web00 ~]$ hdfs  dfs  -renameSnapshot  <path>  <oldname>   <newname>
<path> 可快照目录的路径，<oldname>  老名字， <newname> 新名字

5、获取可快照的目录信息

[hadoop@yz-bi-web00 ~]$ hdfs lsSnapshottableDir

6、获取快照差异报告

[hadoop@yz-bi-web00 ~]$ hdfs  snapshotDiff  <path> <fromSnapshot>  <toSnapshot>
<path> 可快照目录的路径，<fromSnapshot> 源快照名，<toSnapshot> 目的快照名

7、结果

+	文件或目录被创建
-	文件或目录被删除
M	文件或目录被修改
R	文件或目录被重命名

8、使用案例

# 切换为 hadoop 用户操作 #
[root@yz-bi-web00 ~]# su - hadoop
# 创建一个目录 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -mkdir /user/snapshot
# 将目录变成可快照的目录 #
[hadoop@yz-bi-web00 ~]$ hdfs dfsadmin -allowSnapshot /user/snapshot
Allowing snaphot on /user/snapshot succeeded
  
# 获取可快照目录信息 #
[hadoop@yz-bi-web00 ~]$ hdfs  lsSnapshottableDir
drwxr-xr-x 0 hadoop hadoop 0 2020-05-19 16:31 1 65536 /user/snapshot
# 上传一个测试文件到可快照的目录 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -put /home/hadoop/LogExtractor.tar.gz /user/snapshot/
# 查看 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/snapshot/
Found 1 items
-rw-r--r--   3 hadoop hadoop   34401742 2020-05-19 16:01 /user/snapshot/LogExtractor.tar.gz
  
# 创建快照 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -createSnapshot /user/snapshot import-data
Created snapshot /user/snapshot/.snapshot/import-data
# 查看 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/snapshot/.snapshot/import-data
Found 1 items
-rw-r--r--   3 hadoop hadoop   34401742 2020-05-19 16:01 /user/snapshot/.snapshot/import-data/LogExtractor.tar.gz
  
# 将 LogExtractor.tar.gz 文件删除
[hadoop@yz-bi-web00 ~]$ hdfs dfs -rm -r -f /user/snapshot/LogExtractor.tar.gz
20/05/19 16:29:03 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 1440 minutes, Emptier interval = 0 minutes.
Moved: 'hdfs://yz-xxxx-nn1:9000/user/snapshot/LogExtractor.tar.gz' to trash at: hdfs://yz-xxxx-nn1:9000/user/hadoop/.Trash/Current
  
# 误删除后就可以使用快照目录进行恢复 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -cp -ptopax /user/snapshot/.snapshot/import-data/LogExtractor.tar.gz /user/snapshot
20/05/19 16:31:43 WARN hdfs.DFSClient: DFSInputStream has been closed already
# 查看 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -ls /user/snapshot/
Found 1 items
-rw-r--r--   3 hadoop hadoop   34401742 2020-05-19 16:01 /user/snapshot/LogExtractor.tar.gz
  
# 删除快照 #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -deleteSnapshot /user/snapshot import-data
  
# 如果确定要删除的文件，直接将文件或目录 drop 掉，不放到 trash 里面，删除的时候使用参数-skipTrash #
[hadoop@yz-bi-web00 ~]$ hdfs dfs -rm -r -f -skipTrash /user/snapshot
Deleted /user/snapshot

三、通过源数据恢复到 Hive

以上两种回收站和快照均无法恢复时，再根据实际情况分析来恢复 Hive 数据。

1、Hive 数据来源分析

首先要搞清楚 Hive 中数据来源，在 XXXX，数据主要来源可分为四部分：

• 日志数据：利用 Scribe 对各日志源机器的日志进行汇聚后，通过 Python 对汇聚端日志的监视、下载、解析、上传至 Hive 库；
• 业务数据：均来源于 MySQL，通过 Sqoop + Python + Shell 的管理脚本来实现上传至 Hive 库；
• 中间层数据：通过 XXXX 数据分析平台的调度任务处理上传至 Hive 库；
• 商家端及其它 BI 数据：主要通过 Shell 管理脚本，Python 辅助解析来上传至 Hive 库；

2、日志数据恢复

只要 Scribe 日志汇聚端日志在，就能通过 Python 日志处理 METL 系统 恢复所有日志数据。

重新部署 METL 时需要检查 Hadoop 作业模块是否正常，若命令行 Hadoop 作业抛错，则需要检查代码，检查上传到 Hadoop 的代码包，可尝试本地打包上传到 HDFS 指定目录。

[root@yz-bi-web00 ~]# su - scribe
[scribe@yz-bi-web00 ~]$ hdfs dfs -put /home/scribe/scribe_log_treater_metl/lib/LogExtractor.tar.gz /user/scribe/lib/

以下是命令行检查 Hadoop 作业命令：

/hadoop/hadoop/bin/hadoop jar /hadoop/hadoop/share/hadoop/tools/lib/hadoop-streaming-2.7.1.jar \
-Dmapreduce.output.fileoutputformat.compress=true \
-Dmapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzopCodec \
-Dmapreduce.job.name=[LogExtract][gz_xxxx_v_log][10.20.0.102_v_xxxx_nginx-2020-05-14_00066_parselog_10.20.0.102] \
-libjars /home/scribe/scribe_log_treater_metl/bin/../lib/NoSeparatorTextOutputFormat-1.0.jar \
-archives hdfs://yz-xxxx-nn1:9000/user/scribe/lib/LogExtractor.tar.gz#LogExtractor \
-mapper 'LogExtractor/run.sh ./core/template_parse_script.py gz-xxxx_v_log' \
-input /user/scribe/data/gz_xxxx_v_log/10.20.0.102_v_xxxx_nginx-2020-05-14_00066 \
-output /user/scribe/data/gz_xxxx_v_log/10.20.0.102_v_xxxx_nginx-2020-05-14_00066_parselog_10.20.0.102_temp \
-outputformat com.lehe.data.NoSeparatorTextOutputFormat \
-numReduceTasks 0 \
-lazyOutput

如何修复历史数据❓主要就是修复 log_block_galaxy 表数据。

Create Table: CREATE TABLE `log_block_galaxy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` varchar(100) NOT NULL DEFAULT '',
  `block_name` varchar(100) NOT NULL DEFAULT '',
  `block_file_path_in_client` varchar(250) DEFAULT '',
  `block_file_path_in_server` varchar(250) DEFAULT '',
  `block_parselog_file_in_server` varchar(250) DEFAULT '',
  `status` tinyint(4) DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `log_date` date DEFAULT '0000-00-00',
  `modify_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cleanlog` tinyint(4) DEFAULT '0',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `client_host` varchar(50) NOT NULL DEFAULT '',
  `client_user` varchar(50) NOT NULL DEFAULT '',
  `parse_file_hive_path` varchar(256) DEFAULT '',
  `md5_tag` varchar(50) DEFAULT '',
  `hive_table` varchar(50) DEFAULT '',
  `error_times` int(10) unsigned DEFAULT '0',
  `input_num` int(11) DEFAULT '0',
  `valid_num` int(11) DEFAULT '0',
  `abandon_num` int(11) DEFAULT '0',
  `server_user` varchar(50) DEFAULT NULL,
  `server_host` varchar(100) DEFAULT NULL,
  `send_to_kafka_status` int(11) DEFAULT '0' COMMENT '0:not send,1:sending,2:complete',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_group_block_name_client` (`group_id`,`block_name`,`client_host`),
  KEY `log_date` (`log_date`),
  KEY `idx_group_id_status_create_time` (`group_id`,`status`,`create_time`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.1 将对应的未处理的 block 的 status 重置为 0，error_times 重置为 0 即可重新自动进行 METL

mysql> update log_block_galaxy set error_times = 0, status = 0 where id 或者 mysql> update log_block_galaxy set error_times = 0, status = 0 where log_date	= ...
	in(...)
	between ... and ...
group_id：gz_xxxx_v_log、gz_xxxx_main_log、gz_xxxx_wap_log、gz_xxxx_utm_log、gz_xxxx_glk_log、xxxx_php_statistics_register_log

2.2 如果 MySQL 中记录缺失，举个例子以此形式插入，即可重新自动进行 METL

replace into log_block_galaxy(group_id,block_name,create_time,modify_time,log_date,block_file_path_in_client,status,last_update_time,client_host,client_user,error_times) values('gz_xxxx_v_log','v_xxxx_nginx-2020-05-12_00083','2020-05-12 00:00:01','2020-05-12 00:30:00','2020-05-12','/data/web/glk_xxxx_nginx/v_xxxx_nginx-2020-05-12_00083',0,'2020-05-12 00:30:00','10.20.0.102','scribe',0);

2.3 如果 MySQL 中记录缺失过多时也可以通过以下 Shell 脚本（replace_log_block_galaxy.sh）进行插入，然后重新自动进行 METL

#!/bin/bash
function mysql_conn(){
    mysql -u'wfwriter' -p'wFW#1^iPo16QPsd' -h10.20.2.22 -P3411
}
function mysql_e(){
    mysql -u'wfwriter' -p'wFW#1^iPo16QPsd' -h10.20.2.22 -P3411 -Ne "${1}"
}
replace_data='2020-05-12'
logfile_prefix='m_xxxx_nginx'
group_id_name='gz_xxxx_wap_log'
i=0
y=94
while (( $i <= $y ))
do
    lastfix=$(printf "%05d\n" $i)
    filename="${logfile_prefix}-${replace_data}_"${lastfix}
    is_replace=`mysql_e "use xxxx_bilog;select count(*) from log_block_galaxy where log_date='${replace_data}' and block_name='${filename}'"`
    if [[ 1 -eq "${backup_0k}" ]]; then
        echo "replace status: ${is_replace}"
    else
        echo "replace status: ${is_replace}"
        echo "use xxxx_bilog;replace into log_block_galaxy(group_id,block_name,create_time,modify_time,log_date,block_file_path_in_client,status,last_update_time,client_host,client_user,error_times) values('${group_id_name}','${filename}','${replace_data} 00:00:01','${replace_data} 00:30:00','${replace_data}','/data/web/${logfile_prefix}/${filename}',0,'${replace_data} 00:30:00','10.20.0.102','scribe',0);" | mysql_conn
    fi
    let "i++"
done

3、业务数据恢复

只要 MySQL 集群正常， Sqoop + Python + Shell 的管理脚本（SQOOP 部署及导入数据到 Hive 的实际应用）会根据 crontab 设置的脚本执行时间自动恢复 MySQL-Hive 配置的一一对应的表数据。

也可以随时手动强制进行某个已配置的数据导入 Hive 库。

服务器	服务	部署路径	任务级别	crontab 定期调用脚本	备注	状态
yz-bi-web01.lehe.com	Sqoop + Python	sqoop@10.20.2.24:/home/sqoop/sqoop	小时	5 * * * * sh /home/sqoop/sqoop/bin/sqoop_import_hour.sh > /tmp/sqoop_import_hour.log 2>&1 &	SQOOP 从 MySQL 导入到 Hive 之 Python 脚本	✅
			天	20 0 * * * sh /home/sqoop/sqoop/bin/sqoop_import_day.sh > /tmp/sqoop_import_day.log 2>&1 &		✅
	Sqoop + Shell	sqoop@10.20.2.24:/home/sqoop/mysql_hive:	天	13 5 * * * bash /home/sqoop/mysql_hive/bin/adx_mysql_hive_wf.sh -a all > /home/sqoop/mysql_hive/log/error.log 2>&1 &	SQOOP 通过 MyCat 从 MySQL 导入数据到 Hive	✅
可导入 Hadoop 的 MySQL 配置		HOST：10.20.2.22； PORT：3411； DATABASE：xxxx_bilog； TABLE：t_dolphin_stat_db_info
异常情况处理：手动的形式强制进行某个已配置的数据库的导入		cd /home/sqoop/sqoop && python bin/sqoop_import.py -t <hive_table_name>（通过 Sqoop + Python 的方式，将强制对 MySQL 中未进行分表操作的普通表进行一次 <mysql_table_name> 到 hive 中 <hive_table_name> 的导入操作）

特别注意01：MySQL 进行过分表操作的表重新导入 Hive（Hive 未进行分区）时，需要先在 Hive 上 drop table hive_tablename; 然后清理 SQOOP 通过 MyCat 从 MySQL 导入数据到 Hive 相关记录文件，直接删除就行，最后直接执行 bash /home/sqoop/mysql_hive/bin/adx_mysql_hive_wf.sh -a all 就可以了。

## （mysql -> hive）t_im_message -> ods_im_message、t_im_msg -> ods_im_msg
[root@yz-bi-web01 ~]# su - sqoop
[sqoop@yz-bi-web01 ~]$ hive
hive> drop table ods_im_message;
hive> drop table ods_im_msg;
hive> quit;
[sqoop@yz-bi-web01 ~]$ rm -rf mysql_hive/var/t_im_*
[sqoop@yz-bi-web01 ~]$ bash /home/sqoop/mysql_hive/bin/im_mysql_hive_wf.sh -a all > /home/sqoop/mysql_hive/log/error.log 2>&1 &

特别注意02：MySQL 中未进行分表操作的普通表导入 Hive 中需要进行分区时，即 SQOOP 从 MySQL 导入到 Hive 之 Python 脚本 的配置文件中含有 partition_keys=dt 的表，根据具体需求手动进行特殊处理。

## ods_pandora_guaranty_account
[sqoop@yz-bi-web01 ~]$ cat sqoop/conf/xxxx_order.conf
[ods_pandora_guaranty_account]
db=xxxx_order
rank=110
mytbname=t_pandora_guaranty_account
hivetbname=ods_pandora_guaranty_account
partition_keys=dt
owner=wufei
...。。
  
[sqoop@yz-bi-web01 ~]$ hive
hive> use default;
hive> insert overwrite table ods_pandora_guaranty_account partition (dt='2020-05-12') select account_id,balance,limit,mtime,shop_id,fast_amount from ods_pandora_guaranty_account where dt='2020-05-17';
hive> ...。。
hive> quit;
[sqoop@yz-bi-web01 ~]$

4、中间层数据恢复

中间数据主要依赖以上（日志数据、业务数据）必须先恢复。选定日期，重启执行 XXXX 数据分析平台的调度任务即可。注意各调度任务间的依赖关系，可通过调度任务的具体 HQL （运行详情）分析去恢复。

 

5、商家端及其它 BI 数据恢复

商家端及其它 BI 数据恢复主要依赖以上数据必须先恢复。根据 yz-bi-web00（脚本梳理）在 work 用户下 [work@yz-bi-web00 ~ ]$ crontab -l 执行对应脚本，恢复历史数据执行在执行脚本时传入具体日期（eg：2020-05-12）参数即可。

报表类数恢复据同理，选定日期，重启执行 XXXX 数据分析平台的调度任务即可，建议根据具体报表的具体 HQL（运行详情） 分析去恢复。