Doris SQL日志审计部署，以及sql收集输出kafka，后续血缘分析

Bertha 。 2022-09-15 00:59 1165阅读 0赞

## 1，介绍 ##

Doris 的审计日志插件是一个可选插件。用户可以在运行时安装或卸载这个插件，该插件可以将 FE 的审计日志定期的导入到指定 Doris 集群中，以方便用户通过 SQL 对审计日志进行查看和分析，这里的数据其实是Doris FE log目录下的 `fe.audit.log` 文件中的数据。

我们要做的是安装这个插件，然后我们可以通过doris sql去对应的表查询sql语句，也可以通过doris 目录下的fe.audit.log文件将日志接出之后写入外部操作。  
  通过filebeat数据接出到kafka，通过flink程序消费解析日志然后分析出血缘关系，这是我们最终的目的。

2，安装Doris 的审计日志插件

1）参考官网方式，编译得到 auditloader.zip 文件  
   [审计日志插件 | Apache Doris][_ Apache Doris]

2）修改 fe conf下的配置文件，添加参数，启用plugin框架  
       plugin\_enable = true

3）auditloader.zip文件上传到fe，解压到/plugins目录  
     在fe/plugins 目录下插件一个文件夹，然后auditloader.zip解压进这个文件夹，比如我这里创建文件夹auditdemo ，解压之后的效果：  
     ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16][]

4） plugin.conf 配置文件修改

plugin.conf 配置信息：

\# 批量的最大大小，默认为 50MB  
   max\_batch\_size=52428800  
  
   \# 批量加载的最大间隔，默认为 60 秒  
   max\_batch\_interval\_sec=60  
  
   \# 加载审计的 Doris FE 主机，默认为 127.0.0.1:8030。  
   \# 这应该是Stream load 的主机端口  
   frontend\_host\_port=127.0.0.1:8030  
  
   \# 审计表的数据库  
   database=doris\_audit\_db\_\_  
  
   \# 审计表名，保存审计数据  
   table=doris\_audit\_tbl\_\_  
  
   \# 用来连接doris的用户. 此用户必须对审计表具有 LOAD\_PRIV 权限.  
   user=root  
  
   \# 用来连接doris的用户密码  
   password=root

5）创建插件表

库为配置文件里面的配置 database=**doris\_audit\_db\_\_**

<table> 
 <tbody> 
 <tr> 
 <th> <pre>&nbsp;create&nbsp;table&nbsp;doris_audit_tbl__
&nbsp;(
&nbsp;&nbsp;&nbsp; query_id&nbsp;varchar(48)&nbsp;comment&nbsp;"查询唯一ID",
&nbsp;&nbsp;&nbsp; &nbsp;time&nbsp;datetime&nbsp;not&nbsp;null&nbsp;comment&nbsp;"查询开始时间",
&nbsp;&nbsp;&nbsp; client_ip&nbsp;varchar(32)&nbsp;comment&nbsp;"查询客户端IP",
&nbsp;&nbsp;&nbsp; &nbsp;user&nbsp;varchar(64)&nbsp;comment&nbsp;"查询用户名",
&nbsp;&nbsp;&nbsp; db&nbsp;varchar(96)&nbsp;comment&nbsp;"查询的数据库",
&nbsp;&nbsp;&nbsp; state&nbsp;varchar(8)&nbsp;comment&nbsp;"查询状态：EOF, ERR, OK",
&nbsp;&nbsp;&nbsp; query_time&nbsp;bigint&nbsp;comment&nbsp;"查询执行时间（毫秒）",
&nbsp;&nbsp;&nbsp; scan_bytes&nbsp;bigint&nbsp;comment&nbsp;"查询扫描的字节数",
&nbsp;&nbsp;&nbsp; scan_rows&nbsp;bigint&nbsp;comment&nbsp;"查询扫描的记录行数",
&nbsp;&nbsp;&nbsp; return_rows&nbsp;bigint&nbsp;comment&nbsp;"查询返回的结果记录数",
&nbsp;&nbsp;&nbsp; stmt_id&nbsp;int&nbsp;comment&nbsp;"SQL语句的增量ID",
&nbsp;&nbsp;&nbsp; is_query&nbsp;tinyint&nbsp;comment&nbsp;"这个是否是查询：1 or 0",
&nbsp;&nbsp;&nbsp; frontend_ip&nbsp;varchar(32)&nbsp;comment&nbsp;"执行这个语句的FE IP",
&nbsp;&nbsp;&nbsp; stmt&nbsp;varchar(5000)&nbsp;comment&nbsp;"原始语句，如果超过 5000 字节，则进行修剪"
&nbsp;)&nbsp;engine=OLAP
&nbsp;duplicate&nbsp;key(query_id,&nbsp;time,&nbsp;client_ip)
&nbsp;partition&nbsp;by&nbsp;range(time)&nbsp;()
&nbsp;distributed&nbsp;by&nbsp;hash(query_id)&nbsp;buckets&nbsp;1
&nbsp;properties(
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.time_unit"&nbsp;=&nbsp;"DAY",
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.start"&nbsp;=&nbsp;"-30",
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.end"&nbsp;=&nbsp;"3",
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.prefix"&nbsp;=&nbsp;"p",
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.buckets"&nbsp;=&nbsp;"1",
&nbsp;&nbsp;&nbsp; &nbsp;"dynamic_partition.enable"&nbsp;=&nbsp;"true",
&nbsp;&nbsp;&nbsp; &nbsp;"replication_num"&nbsp;=&nbsp;"3"
&nbsp;);</pre> </th> 
 </tr> 
 </tbody> 
</table>

6）启动插件,在mysql 客户端  
        ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

1.  安装一个本地 zip 文件插件：
    
         INSTALL PLUGIN FROM "/wyyt/software/doris-0.14.7/fe/plugins/auditdemo.zip";
2.  安装一个本地目录中的插件：
    
         INSTALL PLUGIN FROM "/wyyt/software/doris-0.14.7/fe/plugins/auditdemo/";

## 3，测试效果 ##

1）查看对应库的表对应表信息：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

2）查看fe目录下log目录下 fe.audit.log

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

## 4，将fe.audit.log文件日志输出 ##

1）filebeat-7.13.3-linux-x86\_64.tar.gz 下载安装   
  ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

2）配置文件，直接监控fe log目录下的fe.audit.log文件 将日志输出到kafka

## 5，通过程序解析log日志 ##

通过flink代码消费kafka的数据然后血缘解析，结果输出到mysql或者redis存储供前端页面查询。

1）指定消费文件

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

2）指定输出

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

3）启动命令

./filebeat -e -c filebeat.yml

4）运行效果

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 7][]

5）JSON格式数据写入kafka

<table> 
 <tbody> 
 <tr> 
 <th>{ "@timestamp": "2021-10-09T03:43:35.716Z", "@metadata": { "beat": "filebeat", "type": "_doc", "version": "7.13.3" }, "ecs": { "version": "1.8.0" }, "host": { "architecture": "x86_64", "name": "bi-524", "os": { "codename": "Core", "type": "linux", "platform": "centos", "version": "7 (Core)", "family": "redhat", "name": "CentOS Linux", "kernel": "3.10.0-1127.19.1.el7.x86_64" }, "id": "b521378110f947d896c25d753d4d472f", "containerized": false, "ip": [ "192.168.5.24", "fe80::250:56ff:fe90:8b62", "169.254.123.1", "fe80::42:52ff:fe91:14a4", "fe80::b061:35ff:fe7e:a765" ], "mac": [ "00:50:56:90:8b:62", "02:42:52:91:14:a4", "b2:61:35:7e:a7:65" ], "hostname": "bi-524" }, "log": { "offset": 46640, "file": { "path": "/wyyt/software/doris-0.14.7/fe/log/fe.audit.log" } }, "message": "2021-10-09 11:43:26,236 [query] |Client\u003d127.0.0.1:47609|User\u003droot|Db\u003ddefault_cluster:example_db|State\u003dEOF|Time\u003d12|ScanBytes\u003d0|ScanRows\u003d0|ReturnRows\u003d1|StmtId\u003d350|QueryId\u003de5f03872e37245e4-982ea3ae68173079|IsQuery\u003dtrue|feIp\u003d192.168.5.24|Stmt\u003d/* mysql-connector-java-5.1.41 ( Revision: 83c6dc41b96809df81444362933043b20a1d49d5 ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@language AS language, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout|CpuTimeMS\u003d0", "input": { "type": "log" }, "agent": { "type": "filebeat", "version": "7.13.3", "hostname": "bi-524", "ephemeral_id": "fc3ce98f-0ace-445b-878b-ad39f7ece367", "id": "b37c7155-80e0-4f95-a3d9-cd92023d1e14", "name": "bi-524" } }</th> 
 </tr> 
 </tbody> 
</table>

6，暂无 --后续程序消费kafka

7，接入血缘分析 --暂时还没完成

未完待续..............

参考峰哥文章：

[Apache Doris SQL 日志审计][Apache Doris SQL]

[_ Apache Doris]: https://doris.apache.org/master/zh-CN/extending-doris/audit-plugin.html#%E7%BC%96%E8%AF%91%E3%80%81%E9%85%8D%E7%BD%AE%E5%92%8C%E9%83%A8%E7%BD%B2
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/a82f68a6ad2446b0b20dd91889547911.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/ec0ce0661eb640f8911978228372e297.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/7111bb1d092f40d6b239d934eb124c7a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/afd0cece33e44c4a87b95e594cddba9e.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/a87bae01a7374aa5bef0ea3ede238298.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220828/e3b9e1a7b97d4bffb5339bf82ded7d40.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220828/bf1a038ab5a747538e87484738d4345d.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6buE55Oc54KW5ZWk6YWS6bit_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220828/5f30a4d25e854f7b9456005978668800.png
[Apache Doris SQL]: https://mp.weixin.qq.com/s/vGOZi7LrsKlnTwk1AUXW4g