# 查看日志配置，不知道配置路径的话，可以执行 nginx -t
less /etc/nginx/nginx.conf

# 查看日志
cd /var/log/nginx;
ll

# 合并打包日志
cat access.log > nginx.log;
gunzip -c access.log*gz > nginx.log;
gzip nginx.log;
sz nginx.log.gz;

# 上传解压日志
rz;
gunzip nginx.log.gz;

-- 根据日志格式，使用正则序列化解析，一个（）是一个字段，注意转义
drop table if exists spider.nginx_log;
create table spider.nginx_log(
remote_addr STRING,
remote_user STRING,
time_local STRING,
request STRING,
status STRING,
body_bytes_sent STRING,
http_referer STRING,
http_user_agent STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = '(.*?) - (.*?) \\[(.*?)\\] "(.*?)" (\\d+) (\\d+) "(.*?)" "(.*?)" .*',
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s"
);

-- 加载数据
load data local inpath '/home/getway/nginx.log' into table spider.nginx_log;

-- 查看一下原始记录
select * from spider.nginx_log limit 10;

-- 通过 udf 获取 ip 所在省份，创建IP和省份的映射表
set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=52428800;
set hive.exec.mode.local.auto.input.files.max=10;

add file /home/getway/udf_baidu_api.py;

create table spider.dim_ip as 
select transform(remote_addr)
  USING 'python3 udf_baidu_api.py' AS (remote_addr, province)
from (
select remote_addr
from spider.nginx_log
group by remote_addr
) as cte;

-- 查看一下加工好的数据
select * from spider.dim_ip limit 10;

-- 数据清洗: 选择有用的字段，并通过 udf 解析 useragent，将数据写到新表 fact_nginx_log
set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=52428800;
set hive.exec.mode.local.auto.input.files.max=10;

add file /home/getway/udf_log_clean.py;

create table spider.fact_nginx_log as
select transform(a.remote_addr,
                 a.time_local,
                 b.province,
                 a.request,
                 a.http_user_agent
                 )
       USING 'python3 udf_log_clean.py' AS (remote_addr,
                                       time_local,
                                       province,
                                       request,
                                       device,
                                       os,
                                       browser)
from spider.nginx_log a
left join spider.dim_ip b on a.remote_addr = b.remote_addr
where a.request not rlike '\\.[css|js|woff|TTF|png|jpg|ico]';

-- 查看数据示例
select * from spider.fact_nginx_log limit 10;

python datax/bin/datax.py fact_nginx_log.json
python datax/bin/datax.py dim_ip.json 

-- 用户分布
select province, count(distinct remote_addr) from fact_nginx_log group by province;

-- 不同时段访问情况
select case when device='Spider' then 'Spider' else 'Normal' end, hour(time_local), count(1) 
from fact_nginx_log 
group by case when device='Spider' then 'Spider' else 'Normal' end, hour(time_local);

-- 最近7天访问情况
select case when device='Spider' then 'Spider' else 'Normal' end, DATE_FORMAT(time_local, '%Y%m%d'), count(1) 
from fact_nginx_log 
where time_local > date_add(CURRENT_DATE, interval - 7 day)
group by case when device='Spider' then 'Spider' else 'Normal' end, DATE_FORMAT(time_local, '%Y%m%d');

-- 用户端前10的设备
select device, count(1)
from fact_nginx_log
where device not in ('Other', 'Spider') -- 过滤掉干扰数据
group by device
order by 2 desc
limit 10

-- 搜索引擎爬虫情况
select browser, count(1) from fact_nginx_log where device = 'Spider' group by browser;