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

云存储

发布于 2022-07-27 09:41:22

6490

发布于 2022-07-27 09:41:22

文章被收录于专栏：用户5909132的专栏

需求描述

在对接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-clientsudo /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/cos_bucket_inventory/1253766168/bruins/test-inventory_instant_20211230095714/data/*.csv.gz', 'xxxxxxxx', 'xxxxxxxxxxx', 'CSV', 'gzip')               VM-16-3-centos :) desc table bruins_inventoryDESCRIBE TABLE bruins_inventoryQuery id: 55e88a06-63aa-4310-be02-eb1cdaee7e5f┌─name────────────────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐│ 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/目录下，可能会导致非预期的结果！）

2. 分析数据

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

VM-16-3-centos :) select count(),formatReadableSize(sum(size)) from bruins_inventory where key like '%json';SELECT   count(),   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;SELECT    key,    sizeFROM bruins_inventoryWHERE key LIKE 'pdd%'LIMIT 2Query 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的原生日志区分开。

2. 创建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/cos_log_qingxi/cos-access-log/2022/06/22/*.csv', 'xxxxxx', 'xxxxxxx', 'CSV')                                    VM-16-3-centos :) desc table logqxanalyseDESCRIBE TABLE logqxanalyseQuery id: 8b9c0f3c-da50-4282-83ca-9db2c03c2b64┌─name────────────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐│ 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步中日志清洗选择的字段要一一对应！

3. 分析数据

现在就可以基于需求执行SQL语句分析了，比如：

查找请求为PUT的请求

VM-16-3-centos :) select remoteIp,reqMethod,userAgent from logqxanalyse where eventName like 'PUT%' limit 5SELECT    remoteIp,    reqMethod,    userAgentFROM logqxanalyseWHERE eventName LIKE 'PUT%'LIMIT 5Query 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 descSELECT    count() AS count,    remoteIpFROM logqxanalyseGROUP BY remoteIpORDER BY count DESCLIMIT 5Query 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-26，如有侵权请联系 cloudcommunity@tencent.com 删除

对象存储