关于对象存储:使用ClickHouse分析COS的清单和访问日志

需要形容
在对接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/doc...

部署ClickHouse
ClickHouse的部署比较简单，参考官网：https://clickhouse.com/ 即可。这里以CentOS为例：sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.c...
sudo yum install -y clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server start
clickhouse-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/doc...

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_inventory

DESCRIBE TABLE bruins_inventory

Query 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/目录下，可能会导致非预期的后果！）

剖析数据
创立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_inventory
WHERE 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,size

FROM bruins_inventory
WHERE 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/doc...

日志荡涤针对存储在COS上的日志，日志荡涤服务可通过指定的检索条件，主动对上传至存储桶的日志文件进行内容过滤。

COS日志荡涤文档：https://cloud.tencent.com/doc...比方联合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 logqxanalyse

DESCRIBE TABLE logqxanalyse

Query 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 5

SELECT

remoteIp,reqMethod,userAgent

FROM logqxanalyse
WHERE 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的申请IPsVM-16-3-centos :) select top 5 count() as count,remoteIp from logqxanalyse group by remoteIp order by count desc

SELECT

count() AS count,remoteIp

FROM logqxanalyse
GROUP BY remoteIp
ORDER BY count DESC
LIMIT 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.)