clickhouse导入原始nginx日志数据

2023-02-23 08:54:29 浏览数 (1)

原始日志数据

代码语言：javascript复制
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 ({x22packageNamex22:x22unknownx22,x22typex22:x22unknownx22,x22extrax22: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 ({x22typex22:x22shortcutx22,x22packageNamex22:x22com.huawei.android.launcherx22,x22extrax22:{x22isNativex22:false,x22scenex22:x22apix22}})"
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 "-" "-"

创建表:

代码语言：javascript复制
 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;

导入命令:

代码语言：javascript复制
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
日志数据

0 人点赞