乐趣区

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

jiezi

需要形容
在对接 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/ 目录下，可能会导致非预期的后果！）

  1. 剖析数据
    创立 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.)

退出移动版