南大通用GBase 8a数据库基于审计日志的冷温热数据分析-之“解决方案”

数据生命周期管理是项目中常见问题，一般解决方案为：根据数据表创建时间和使用时间，计算出数据表的使用频率，从而将数据表分类为冷、温、热数据，以便客户进行数据清理或数据迁移。

但当表数据量较多时，该类信息无法快速或者直接从集群元数据中获取。因此需要通过程序多维度地提取元数据中的信息，梳理出客户业务中常用、不常用的数据表，并经过加工整合归类分析，最终解决该类问题。

解决方案

1、新建表test.splist_bryan，作为自建笛卡尔积cross join时的伪参数表。

2、新建存储过程，对伪参数表test.splist_bryan进行初始化操作。插入100行行号。

注：这里假定每个sql 中涉及到的表不会超过100个（一般也不会超过）。如果有类似场景需要单行转多行，且拆分出的行数最大超过100个，在如下存储过程还可以再修改添加。

3、新建表test1 对audit_log表进行分类汇总统计。start_time是表最近访问时间，tbl是对应的表名（表名列表）。

注：此处audit_log是根据集群中每个节点中的gbase.audit_log汇总而来的express引擎表，这里只为描述方便， 未作引用。

跨引擎迁移表内数据需要打开_gbase_query_path参数。

集群新audit_log表各字段含义：

thread_id   ，线程号,同processlist中的ID；

taskid      ，全局唯一的任务号；

start_time  ，开始执行的时间；

end_time    ，SQL执行结束的时间；

user_host   ，登陆的用户名和host；

user        ，用户名；

host_ip     ，用户登录端IP地址；

query_time  ，执行的时间；

rows        ，返回结果集行数；

db          ，执行的当前数据库名；

table_list  ，涉及表，格式：``.``[,...];

sql_text    ，sql 内容；

sql_type    ，标识SQL类型，DDL，DML，DQL，OTHERS；

sql_command ，sql命令类型，如SELECT、UPDATE，INSERT，LOAD等；

algorithms  ，涉及的算子，比如JOIN、WHERE、GROUP、HAVING等；

status      ，标识SQL执行成功还是失败，SUCCESS，FAILED；

conn_type   ，用户登陆方式(CAPI、ODBC、JDBC、ADO)。

4、新建表test2，通过corssjoin自建笛卡尔积实现一行变多行；通过substring_index函数实现对串的不同部分按照行号进行截取，进而统计出每张表最近的访问时间；结果需要过滤掉系统库的表。

5、以下结果显示，test2表中，每张表最近的使用日期。每张表占用一行，无重复表名。这样就可以根据这个日期，针对表进行使用热度分析。