Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

使用ClickHouse分析COS的清单和访问日志

云存储小天使 2022-08-03 17:06:55 阅读数:257 评论数:0 点赞数:0 收藏数:0

需求描述

在对接COS客户中,经常会遇到客户的一些COS分析需求,主要集中在两个方面:

1、COS Bucket的对象分析,比如:

前缀为xxx的对象的总大小

后缀为xxx的对象的总大小

xxx日期前的对象总大小

对象size在某个范围内的个数

2、COS Bucket的访问分析,比如:

xxx时间段内请求Topx的文件

xxx时间段内请求Topx的客户端IPs/Agents

xxx时间段内所有的GET/PUT请求,或指定request PATH

针对上述的客户需求,我们通常可以通过COS清单和COS的访问日志来分析,但COS清单或者日志的量通常都是比较大的,需要通过一个比较好的工具来完成分析任务,这里介绍下如何通过ClickHouse,来原生的分析存储在COS上的清单和日志文件。

ClickHouse是适用于OLAP场景的列式数据库系统,但使用原生接口分析存储在COS上的清单或日志文件时,并不能发挥出其列式存储的性能。若需要较高性能的复杂分析时,请使用数据导入的方式把COS上的清单或日志文件记录,导入到ClickHouse集群中分析。

COS数据导入请参考:https://cloud.tencent.com/document/product/1299/68046

部署ClickHouse

ClickHouse的部署比较简单,参考官网:https://clickhouse.com/ 即可。

这里以CentOS为例:

sudo yum install -y yum-utilssudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.reposudo yum install -y clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server startclickhouse-client # or "clickhouse-client --password" if you set up a password.

场景1:分析COS清单

在需要分析COS Bucket的对象时,我们通常通过拉取Bucket的清单来分析的方式,COS已经支持即时清单功能,在Bucket对象数较少的情况下,可以满足小时级生成COS Bucket的清单文件。

Bucket清单请参考:https://cloud.tencent.com/document/product/436/33702

  1. 创建ClickHouse表
    ClickHouse原生支持创建S3的外表,下面是基于COS清单文件,创建ClickHouse Table的示例:

[root@VM-16-3-centos ~]# clickhouse-client...VM-16-3-centos :) CREATE TABLE default.bruins_inventory(appid UInt64,bucket String,key String,size UInt64,LastModifiedDate String,etag String,storage_class String,IsMultipartUploaded String,Replicationstatus String,Tag String)ENGINE = S3('http://bruins-1253766168.cos.ap-shanghai.myqcloud.com/cosbucketinventory/1253766168/bruins/test-inventoryinstant20211230095714/data/*.csv.gz', 'xxxxxxxx', 'xxxxxxxxxxx', 'CSV', 'gzip')

VM-16-3-centos :) desc table bruins_inventory

DESCRIBE TABLE bruins_inventory

Query id: 55e88a06-63aa-4310-be02-eb1cdaee7e5f

┌─name────────────────┬─type───┬─defaulttype─┬─defaultexpression─┬─comment─┬─codecexpression─┬─ttlexpression─┐│ appid │ UInt64 │ │ │ │ │ ││ bucket │ String │ │ │ │ │ ││ key │ String │ │ │ │ │ ││ size │ UInt64 │ │ │ │ │ ││ LastModifiedDate │ String │ │ │ │ │ ││ etag │ String │ │ │ │ │ ││ storage_class │ String │ │ │ │ │ ││ IsMultipartUploaded │ String │ │ │ │ │ ││ Replicationstatus │ String │ │ │ │ │ ││ Tag │ String │ │ │ │ │ │└─────────────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘创建表时确保各个字段和COS清单里能对应上

创建表时指定目录下的所有csv.gz文件(多次清单文件都会放在data/目录下,可能会导致非预期的结果!)

  1. 分析数据
    创建ClickHouse的S3外表后,可以直接使用SQL语句来分析数据了,如下示例:后缀为'json'的对象的个数和总大小。

VM-16-3-centos :) select count(),formatReadableSize(sum(size)) from bruins_inventory where key like '%json';

SELECTcount(),formatReadableSize(sum(size))FROM bruins_inventoryWHERE key LIKE '%json'

Query id: 7bd74827-c9ff-4a90-a931-5703c4c3ae41

┌─count()─┬─formatReadableSize(sum(size))─┐│ 4 │ 5.59 KiB │└─────────┴───────────────────────────────┘

1 row in set. Elapsed: 0.989 sec.

VM-16-3-centos :) select key,size from bruins_inventory where key like 'pdd%' limit 2;

SELECTkey,sizeFROM bruins_inventoryWHERE key LIKE 'pdd%'LIMIT 2

Query id: 17d1fea8-8153-461a-9b4f-9cb886241d56

┌─key───────────────────────┬─size─┐│ pdd/subdir2/manifest.json │ 1698 ││ pdd/zshrc │ 4948 │└───────────────────────────┴──────┘

2 rows in set. Elapsed: 0.629 sec.

场景2:分析COS访问日志COS的访问日志的默认分隔符是空格,这个我还没找到直接导入ClickHouse的方法。另外COS日志的字段较多,并不是每个都是客户期望的,如果都导入ClickHouse的话,会有更大的负载。

基于这个考虑,可以先使用COS的日志清洗功能,来选择出自己关注的字段,再导入ClickHouse分析。

COS各字段含义参考:https://cloud.tencent.com/document/product/436/16920

  1. 日志清洗
    针对存储在COS上的日志,日志清洗服务可通过指定的检索条件,自动对上传至存储桶的日志文件进行内容过滤。

COS日志清洗文档:https://cloud.tencent.com/document/product/436/54234

比如结合COS日志的内容,我们选择自己感兴趣的字段,做一轮清洗。其中第2步的日志清洗配置如下:

自定义的SQL表达式如下:

select s.4, s.5, s.6, s.7, s.8, s.12, s.13, s.14, s.15, s.19 from cosobjects

第3步的投递配置,推荐配置清洗后的文件存储在其他Bucket下,或者当前Bucket的其他前缀下,和COS的原生日志区分开。

  1. 创建ClickHouse表
    基于COS清洗后的日志目录,就可以创建原生的ClickHouse表了,如下所示:

VM-16-3-centos :) CREATE TABLE default.logqxanalyse(eventTime String,eventSource String,eventName String,remoteIp String,userSecretKeyId String,reqPath String,reqMethod String,userAgent String,rresHttpCode UInt32,resTotalTime UInt32)ENGINE = S3('http://bruins-1253766168.cos.ap-shanghai.myqcloud.com/coslogqingxi/cos-access-log/2022/06/22/*.csv', 'xxxxxx', 'xxxxxxx', 'CSV')

VM-16-3-centos :) desc table logqxanalyse

DESCRIBE TABLE logqxanalyse

Query id: 8b9c0f3c-da50-4282-83ca-9db2c03c2b64

┌─name────────────┬─type───┬─defaulttype─┬─defaultexpression─┬─comment─┬─codecexpression─┬─ttlexpression─┐│ eventTime │ String │ │ │ │ │ ││ eventSource │ String │ │ │ │ │ ││ eventName │ String │ │ │ │ │ ││ remoteIp │ String │ │ │ │ │ ││ userSecretKeyId │ String │ │ │ │ │ ││ reqPath │ String │ │ │ │ │ ││ reqMethod │ String │ │ │ │ │ ││ userAgent │ String │ │ │ │ │ ││ rresHttpCode │ UInt32 │ │ │ │ │ ││ resTotalTime │ UInt32 │ │ │ │ │ │└─────────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

10 rows in set. Elapsed: 0.002 sec.创建表格的字段与第1步中日志清洗选择的字段要一一对应!

  1. 分析数据
    现在就可以基于需求执行SQL语句分析了,比如:

查找请求为PUT的请求

VM-16-3-centos :) select remoteIp,reqMethod,userAgent from logqxanalyse where eventName like 'PUT%' limit 5

SELECTremoteIp,reqMethod,userAgentFROM logqxanalyseWHERE eventName LIKE 'PUT%'LIMIT 5

Query id: cf8d1bdb-755e-4058-94af-47c9652d6b16

┌─remoteIp──────┬─reqMethod─┬─userAgent───────────┐│ 11.185.33.189 │ PUT │ cos-go-sdk-v5/0.7.3 │└───────────────┴───────────┴─────────────────────┘┌─remoteIp──────┬─reqMethod─┬─userAgent────────────────┐│ 9.3.76.197 │ PUT │ cos-nodejs-sdk-v5-2.9.12 ││ 11.160.40.246 │ PUT │ - │└───────────────┴───────────┴──────────────────────────┘┌─remoteIp──────┬─reqMethod─┬─userAgent────────────────┐│ 9.3.76.197 │ PUT │ cos-nodejs-sdk-v5-2.9.12 ││ 9.142.175.253 │ PUT │ - │└───────────────┴───────────┴──────────────────────────┘

5 rows in set. Elapsed: 1.500 sec.查询请求次数Top 5的请求IPs

VM-16-3-centos :) select top 5 count() as count,remoteIp from logqxanalyse group by remoteIp order by count desc

SELECTcount() AS count,remoteIpFROM logqxanalyseGROUP BY remoteIpORDER BY count DESCLIMIT 5

Query id: c21c676a-221b-4150-ab85-723fc8a7ef71

┌─count─┬─remoteIp───────┐│ 520 │ 180.153.219.32 ││ 214 │ 180.153.219.16 ││ 152 │ 9.3.88.110 ││ 110 │ 172.17.16.3 ││ 29 │ 100.67.79.78 │└───────┴────────────────┘

5 rows in set. Elapsed: 4.020 sec. Processed 1.47 thousand rows, 465.24 KB (364.96 rows/s., 115.74 KB/s.)最后更新于 2022-07-08 17:02

版权声明

飞链云3D数字艺术品
30万现金开奖等你来领