全链路数据治理-2

CREATE TABLE IF NOT EXISTS ods_log_info_d (
  ip STRING COMMENT 'ip',
  uid STRING COMMENT 'uid',
  time STRING COMMENT 'timeyyyymmddhh:mi:ss',
  status STRING COMMENT 'status',
  bytes STRING COMMENT 'bytes',
  region STRING COMMENT 'region',
  method STRING COMMENT 'method',
  url STRING COMMENT 'url',
  protocol STRING COMMENT 'protocol',
  referer STRING COMMENT 'referer',
  device STRING COMMENT 'device',
  identity STRING COMMENT 'identity'
)
PARTITIONED BY (
  dt STRING
);

CREATE TABLE IF NOT EXISTS dw_user_info_all_d (
  uid STRING COMMENT 'uid',
  gender STRING COMMENT 'gender',
  age_range STRING COMMENT 'age_range',
  zodiac STRING COMMENT 'zodiac',
  region STRING COMMENT 'region',
  device STRING COMMENT 'device',
  identity STRING COMMENT 'identity',
  method STRING COMMENT 'method',
  url STRING COMMENT 'url',
  referer STRING COMMENT 'referer',
  time STRING COMMENT 'timeyyyymmddhh:mi:ss'
)
PARTITIONED BY (
  dt STRING
);

CREATE TABLE IF NOT EXISTS rpt_user_info_d (
  uid STRING COMMENT 'uid',
  region STRING COMMENT 'uid',
  device STRING COMMENT 'device',
  pv BIGINT COMMENT 'pv',
  gender STRING COMMENT 'gender',
  age_range STRING COMMENT 'age_range',
  zodiac STRING COMMENT 'zodiac'
)
PARTITIONED BY (
  dt STRING
);

https://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/85298/cn_zh/1532163718650/ip2region.jar?spm=a2c4g.11186623.0.0.43df4d0dwSRLzd&file=ip2region.jar

INSERT OVERWRITE TABLE ods_log_info_d PARTITION (dt=${bdp.system.bizdate})
SELECT ip
  , uid
  , time
  , status
  , bytes 
  , getregion(ip) AS region --使用自定义UDF通过IP得到地域。
  , regexp_substr(request, '(^[^ ]+ )') AS method --通过正则把request差分为3个字段。
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_substr(request, '([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer --通过正则清晰refer，得到更精准的URL。
  , CASE
    WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' --通过agent得到终端信息和访问形式。
    WHEN TOLOWER(agent) RLIKE 'iphone' THEN 'iphone'
    WHEN TOLOWER(agent) RLIKE 'ipad' THEN 'ipad'
    WHEN TOLOWER(agent) RLIKE 'macintosh' THEN 'macintosh'
    WHEN TOLOWER(agent) RLIKE 'windows phone' THEN 'windows_phone'
    WHEN TOLOWER(agent) RLIKE 'windows' THEN 'windows_pc'
    ELSE 'unknown'
  END AS device
  , CASE
    WHEN TOLOWER(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN TOLOWER(agent) RLIKE 'feed'
    OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
    WHEN TOLOWER(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)'
    AND agent RLIKE '^[Mozilla|Opera]'
    AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') NOT RLIKE 'feed' THEN 'user'
    ELSE 'unknown'
  END AS identity
  FROM (
    SELECT SPLIT(col, '##@@')[0] AS ip
    , SPLIT(col, '##@@')[1] AS uid
    , SPLIT(col, '##@@')[2] AS time
    , SPLIT(col, '##@@')[3] AS request
    , SPLIT(col, '##@@')[4] AS status
    , SPLIT(col, '##@@')[5] AS bytes
    , SPLIT(col, '##@@')[6] AS referer
    , SPLIT(col, '##@@')[7] AS agent
  FROM ods_raw_log_d
  WHERE dt = ${bdp.system.bizdate}
) a;

INSERT OVERWRITE TABLE dw_user_info_all_d PARTITION (dt='${bdp.system.bizdate}')
SELECT COALESCE(a.uid, b.uid) AS uid
  , b.gender
  , b.age_range
  , b.zodiac
  , a.region
  , a.device
  , a.identity
  , a.method
  , a.url
  , a.referer
  , a.time
FROM (
  SELECT *
  FROM ods_log_info_d
  WHERE dt = ${bdp.system.bizdate}
) a
LEFT OUTER JOIN (
  SELECT *
  FROM ods_user_info_d
  WHERE dt = ${bdp.system.bizdate}
) b
ON a.uid = b.uid;

INSERT OVERWRITE TABLE rpt_user_info_d PARTITION (dt='${bdp.system.bizdate}')
SELECT uid
  , MAX(region)
  , MAX(device)
  , COUNT(0) AS pv
  , MAX(gender)
  , MAX(age_range)
  , MAX(zodiac)
FROM dw_user_info_all_d
WHERE dt = ${bdp.system.bizdate}
GROUP BY uid;

select * from rpt_user_info_d where dt=${bdp.system.bizdate} limit 10;