如何在HiveQL中筛选指定日期特定时间范围内的数据

核心实现逻辑 #

你已经通过acq_date字段做了分区级的日期过滤，这层过滤会触发分区裁剪，查询性能最优，只需在此基础上新增时段过滤条件即可：

• 保留acq_date的硬编码赋值位，直接替换为目标查询日期
• 从存储请求时间的event_header.published_timestamp字段中提取当日的时分秒部分，和你硬编码的起止时段做范围比对即可。同格式的时间字符串字典序与时间先后顺序完全一致，直接用>=和<=做比较即可，不需要额外做复杂类型转换。

注意：如果你的published_timestamp是13位毫秒级时间戳，需要先转成时间格式再提取时分秒；如果本身就是yyyy-MM-dd HH:mm:ss.SSS格式的字符串，直接截取第12位开始的12个字符就能拿到时段部分。

修改后的可直接使用的HiveQL代码 #

你既可以通过变量统一管理硬编码参数，也可以直接把值写死在WHERE条件中：

-- 硬编码参数区，直接替换成你需要的值即可，不需要变量可以直接把值写到WHERE对应位置
SET hivevar:target_date = '2022-07-07'; -- 目标查询日期
SET hivevar:start_time = '07:00:00.000'; -- 时段起始值
SET hivevar:end_time = '09:00:00.000'; -- 时段结束值

SELECT 
  http_message.request.method as http_verb, 
  http_message.request.headers["host"][0] as domain, 
  regexp_replace(http_message.request.headers["Referer"][0], concat("https://", http_message.request.headers["host"][0], "/"), "") as path, 
  http_message.response.headers["x-page-id"][0] as routing_key, 
  agent_info.user_agent as user_agent, 
  event_header.published_timestamp as request_timestamp
FROM prod_runtime_and_orchestration.edge_secure_access_log_event_v1
WHERE acq_date = ${hivevar:target_date}
  -- 时段过滤逻辑：提取时间戳里的时分秒部分，和硬编码时段做范围比对
  AND date_format(event_header.published_timestamp, 'HH:mm:ss.SSS') BETWEEN ${hivevar:start_time} AND ${hivevar:end_time}
  AND http_message.response.status=200 
  AND http_message.request.method = "GET" 
  AND (
    http_message.response.headers["x-page-id"][0] in ("page.Infosite.Information", "Homepage", "page.Search") 
    OR http_message.response.headers["x-page-id"][0] RLIKE ""
  )
LIMIT 100;

不同场景的适配写法 #

• 如果event_header.published_timestamp是固定格式yyyy-MM-dd HH:mm:ss.SSS的字符串，可以替换成性能更高的字符串截取写法，避免函数转换开销：

  AND substr(event_header.published_timestamp, 12, 12) BETWEEN '07:00:00.000' AND '09:00:00.000'
  

• 如果不需要精确到毫秒，仅按小时粒度过滤，可以直接用hour()函数简化写法，注意该函数返回0-23的整数，若要包含起始小时、排除结束小时，结束值需要减1，比如筛选7点到9点前的数据可以写为：

  AND hour(event_header.published_timestamp) BETWEEN 7 AND 8
  

内容的提问来源于stack exchange，提问作者Elaina Heraty