原创

clickhouse导入原始nginx日志数据

温馨提示:
本文最后更新于 2023年05月11日,已超过 312 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我

原始日志数据

139.224.56.94 - - [03/Dec/2022:16:01:01 +0800] "GET /output/zw/updateConsumeMoney?ad_num=1740763056491527&date=2022-12-03&cost=0&balance=0&sign=09a77e8f235f45b087a2d15a1b77f430 HTTP/1.0" 200 20 "-" "-"
49.93.209.3 - - [03/Dec/2022:16:01:01 +0800] "GET /index/Quickapi1/read?ref=read&id=1029393&bookid=3109 HTTP/2.0" 200 14547 "https://quickapp.cn/com.hmydb.zzxd/29/page-frame.html" "Mozilla/5.0 (Linux; Android 12; ELS-AN00 Build/HUAWEIELS-AN00;)AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/18.0.1025 Mobile Safari/537.36 hap/1102/huawei com.huawei.fastapp/12.6.1.304 com.hmydb.zzxd/2.0.9 ({\x22packageName\x22:\x22unknown\x22,\x22type\x22:\x22unknown\x22,\x22extra\x22:\x22{}\x22})"
139.224.56.94 - - [03/Dec/2022:16:01:02 +0800] "GET /output/zw/updateConsumeMoney?ad_num=1740763057082376&date=2022-12-03&cost=0&balance=0&sign=321a5fc2b7470896ca9e0991b3dcff7c HTTP/1.0" 200 0 "-" "-"
171.214.147.1 - - [03/Dec/2022:16:01:02 +0800] "GET /index/Quickapi1/getGender HTTP/2.0" 200 26 "https://quickapp.cn/com.hmydb.zzxd/29/page-frame.html" "Mozilla/5.0 (Linux; Android 12; NOH-AN00 Build/HUAWEINOH-AN00;)AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/18.0.1025 Mobile Safari/537.36 hap/1102/huawei com.huawei.fastapp/12.6.1.304 com.hmydb.zzxd/2.0.9 ({\x22type\x22:\x22shortcut\x22,\x22packageName\x22:\x22com.huawei.android.launcher\x22,\x22extra\x22:{\x22isNative\x22:false,\x22scene\x22:\x22api\x22}})"
139.224.56.94 - - [03/Dec/2022:16:01:02 +0800] "GET /output/zw/updateConsumeMoney?ad_num=1740763057797133&date=2022-12-03&cost=0&balance=0&sign=fd36a029809d9d4ddf45da2e9544ad69 HTTP/1.0" 200 0 "-" "-"

创建表:

 CREATE TABLE nginx_log (
               remote_addr String,
               time_local String,
               request String,
               status String,
               body_bytes_sent String,
               http_referer String,
               http_user_agent String
           ) ENGINE = Log;

导入命令:

INSERT INTO nginx_log FROM INFILE 'nginx.log'
           SETTINGS
             format_regexp = '([0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+) - - \\[([0-9]+\\/[a-z,A-Z]+\\/[0-9]+:[0-9]+:[0-9]+:[0-9]+ \\+[0-9]+)\\] "(.+?)" ([0-9]+) ([0-9]+) "(.+?)" "(.+?)"' , format_regexp_skip_unmatched = 1
           FORMAT Regexp
正文到此结束
本文目录