0822-HUE元数据库的desktop_document2表研究
0822-HUE元数据库的desktop_document2表研究
作者：是灏
背景
由于HUE中的数据过多，导致HUE运行很缓慢，经过查询，发现HUE元数据库中的desktopdocument2表很大，有几十万条数据，经过查看表结构发现，desktopdocument2中有一个字段lastmodified，值是一个时间戳，为了减轻HUE的负担，直接删除了lastmodified小于三个月前的所有数据，结果登陆HUE后发现HUE的收藏夹无法打开，并且所有的查询记录都无法显示了。
以下仅针对Hive和Impala的SQL
查询历史和收藏夹
在hue的desktopdocument2表中，存放了所有用户的查询历史和收藏夹，这两者是通过ishistory来区分的，所以is_history=1的数据可以放心直接删除，不会有任何影响
所有ishistory=0的数据为用户收藏夹数据，这些数据中包括了文件夹和实际收藏的SQL。如果parentdirectoryid是null，这条数据代表了一个文件夹，实际收藏的数据的parentdirectoryid字段指向了parentdirectory_id所在行的主键id
我以lastmodified小于某个值去删除，会导致代表parentdirectoryid本身的行被删除（因为所有的根文件夹都是随着用户建立而建立的，所以lastmodified都很老），因此所有的收藏夹数据无法找到索引，导致收藏夹和Saved Queries无法正常显示。
恢复方法为执行以下SQL
select distinct parent_directory_id,owner_id from hue.desktop_document2 where is_history=flase and parent_directory_id is not null and parent_directory_id not in (select id from hue.desktop_document2 )
找到所有丢失的parentdirectoryid和对应的ownerid，重新插入desktopdocument2表即可。除了parentdirectoryid和ownerid以外的其余字段随便找一条代表parentdirectory行的值填入即可。uuid可以用uuid()函数生成。SQL如下
insert into desktop_document2 (id,name,description,uuid,type,data,extra,last_modified,version,is_history,owner_id,parent_directory_id,search,is_trashed,is_managed)
select parent_directory_id,"","",uuid(),"directory",{}","","2020-09-20 10:10:10","1,0,owner_id,null,null,false,false
from
select distinct parent_directory_id,owner_id from desktop_document2 where is_history=flase and parent_directory_id is not null and parent_directory_id not in (select id from hue.desktop_document2 ) a;
插入数据后，用户重新登入HUE，刷新后收藏夹和查询历史即恢复正常。
文收藏夹的回收站
经过上一步的操作，用户已经可以使用收藏夹，但是用户无法删除收藏夹的文件。经研究，收藏夹中删除的记录会放到name为.Trash对应的目录中去，如果用户没有对应的.Trash数据库记录，则删除文档的时候会报错，当时删除的时候把代表回收站的数据都删除了，所以用户无法删除收藏夹恢复方法为找到所有缺失的.Trash记录，重新插入数据库即可
SQL如下
insert into desktop_document2 (name,description,uuid,type,data,extra,last_modified,version,is_history,owner_id,parent_directory_id,search,is_trashed,is_managed)
select ".Trash","",uuid(),"directory","{}","","2020-09-20 10:10:10","1",0,owner_id,parent_directory_id,null,false,false
from
(
select id as parent_directory_id,owner_id from desktop_document2 where parent_directory_id is null
and owner_id not in (selet owner_id from desktop_document2 where name=".Trash")
) a
导出用户的收藏夹
前文已经说明hue将所有用户的收藏夹都放在desktop_document2表中，可以用以下方式导出用户的收藏夹
首先用
select * from auth_user where username="xxx";
找到用户名对应的ID，然后desktopdocument2表中所有ownerid等于改用户ID，并且ishistory是false的记录即为用户的收藏夹，用户的SQL在desktopdocument2表的data和search字段中都有保存，经试验发现search字段中的SQL保存不全，因此需要解析data字段得到用户收藏的SQL，参考SQL如下，由于用到了JSON解析函数，需要高版本的MYSQL才支持。
select name,description,type, REPLACE(
REPLACE(
JSON_EXTRACT(JSON_EXTRACT(data, '$.snippets'),'$[0].statement'),'\\r\\n', char(10)
), '\\n', char(10)
) as statement
from desktop_document2 where owner_id="xxx" and is_history=false