搭建一个离线数仓——从原始日志到 BI 报表

1
2
3
4
5
6
7
8
9
CREATE EXTERNAL TABLE ods.nginx_log_raw (
  raw_line STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
LOCATION '/warehouse/ods/nginx_log_raw/';

-- 加载当天分区
ALTER TABLE ods.nginx_log_raw ADD PARTITION (dt='2026-06-14');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE dwd.page_view_detail (
  ip          STRING,
  user_id     STRING,
  event_time  TIMESTAMP,
  url         STRING,
  status      INT,
  is_bot      BOOLEAN
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

INSERT OVERWRITE TABLE dwd.page_view_detail PARTITION (dt='2026-06-14')
SELECT
  regexp_extract(raw_line, '^([\\d.]+)', 1)                          AS ip,
  regexp_extract(raw_line, '- ([^ ]+) \\[', 1)                       AS user_id,
  from_unixtime(unix_timestamp(
    regexp_extract(raw_line, '\\[([^\\]]+)\\]', 1),
    'dd/MMM/yyyy:HH:mm:ss Z'))                                        AS event_time,
  regexp_extract(raw_line, '"GET ([^ ]+)', 1)                         AS url,
  CAST(regexp_extract(raw_line, '" \\d+ (\\d+)', 1) AS INT)          AS status,
  raw_line LIKE '%Bot%'                                              AS is_bot
FROM ods.nginx_log_raw
WHERE dt = '2026-06-14'
  AND raw_line LIKE '%GET%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE dws.page_pv_uv_daily (
  url         STRING,
  pv          BIGINT,
  uv          BIGINT
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

INSERT OVERWRITE TABLE dws.page_pv_uv_daily PARTITION (dt='2026-06-14')
SELECT
  url,
  COUNT(*)                    AS pv,
  COUNT(DISTINCT user_id)     AS uv
FROM dwd.page_view_detail
WHERE dt = '2026-06-14'
  AND is_bot = false
  AND status = 200
GROUP BY url;

1
2
3
4
5
6
7
SELECT
  dt,
  SUM(pv) AS total_pv,
  COUNT(DISTINCT user_id) AS total_uv  -- 需从 DWD 层算全站 UV
FROM dws.page_pv_uv_daily
WHERE dt = '2026-06-14'
GROUP BY dt;

1
2
3
SELECT COUNT(DISTINCT user_id) AS total_uv
FROM dwd.page_view_detail
WHERE dt = '2026-06-14' AND is_bot = false AND status = 200;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE ads.daily_site_report (
  dt          STRING,
  total_pv    BIGINT,
  total_uv    BIGINT,
  top_url     STRING,
  top_url_pv  BIGINT
);

INSERT OVERWRITE TABLE ads.daily_site_report
SELECT
  '2026-06-14' AS dt,
  COUNT(*) AS total_pv,
  COUNT(DISTINCT user_id) AS total_uv,
  MAX(url) AS top_url,  -- 简化示例，实际用窗口函数
  MAX(pv) AS top_url_pv
FROM (
  SELECT url, user_id, COUNT(*) OVER() AS dummy
  FROM dwd.page_view_detail
  WHERE dt = '2026-06-14' AND is_bot = false
) t;