hive用timestamp类型的时间与string类型比较大小时遇到的坑

SELECT 
    date_format(a.start_time, 'yyyy-MM-dd') AS `date`,
    COUNT(*) AS `排课课节数`,
    SUM(a.stu_num) AS `排课学生人次`,
    count(case when a.category=0 then 1 end) as `1v1课节数`,
    sum(case when a.category=0 then a.stu_num end) as `1v1学生人次`,
    count(case when a.category=7 then 1 end) as `小班课课节数`,
    sum(case when a.category=7 then a.stu_num end) as `小班课学生人次`,
    count(case when a.category in (1,2) then 1 end) as `大班课课节数`,
    sum(case when a.category in (1,2) then a.stu_num end) as `大班课学生人次`
FROM 
    epg_ods.zby_api_cloudclass_lesson a
WHERE 
    a.start_time > CURRENT_DATE()
     AND a.start_time < date_sub(CURRENT_DATE(),-8)
    AND a.deleted_at is NULL
GROUP BY date_format(a.start_time, 'yyyy-MM-dd')
ORDER BY `date` ASC;

报错

Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

想去yarn上看下日志,结果详细日志不显示
之后逐个条件去注释测试,后来发现将where的 a.start_time < date_sub(CURRENT_DATE(),0)条件注释掉就ok了

SELECT 
    date_format(a.start_time, 'yyyy-MM-dd') AS `date`,
    COUNT(*) AS `排课课节数`,
    SUM(a.stu_num) AS `排课学生人次`,
    count(case when a.category=0 then 1 end) as `1v1课节数`,
    sum(case when a.category=0 then a.stu_num end) as `1v1学生人次`,
    count(case when a.category=7 then 1 end) as `小班课课节数`,
    sum(case when a.category=7 then a.stu_num end) as `小班课学生人次`,
    count(case when a.category in (1,2) then 1 end) as `大班课课节数`,
    sum(case when a.category in (1,2) then a.stu_num end) as `大班课学生人次`
FROM 
    epg_ods.zby_api_cloudclass_lesson a
WHERE 
    a.start_time > CURRENT_DATE()
    --  AND a.start_time < date_sub(CURRENT_DATE(),-8)
    AND a.deleted_at is NULL
GROUP BY date_format(a.start_time, 'yyyy-MM-dd')
ORDER BY `date` ASC;

我想了一个方法就是将 a.start_time < date_sub(CURRENT_DATE(),0)这个条件转换成左边为date类型再去比较,发现成功了

SELECT 
    date_format(a.start_time, 'yyyy-MM-dd') AS `date`,
    COUNT(*) AS `排课课节数`,
    SUM(a.stu_num) AS `排课学生人次`,
    count(case when a.category=0 then 1 end) as `1v1课节数`,
    sum(case when a.category=0 then a.stu_num end) as `1v1学生人次`,
    count(case when a.category=7 then 1 end) as `小班课课节数`,
    sum(case when a.category=7 then a.stu_num end) as `小班课学生人次`,
    count(case when a.category in (1,2) then 1 end) as `大班课课节数`,
    sum(case when a.category in (1,2) then a.stu_num end) as `大班课学生人次`
FROM 
    epg_ods.zby_api_cloudclass_lesson a
WHERE 
    a.start_time > CURRENT_DATE()
      AND date(a.start_time) < date_sub(CURRENT_DATE(),-8)
    AND a.deleted_at is NULL
GROUP BY date_format(a.start_time, 'yyyy-MM-dd')
ORDER BY `date` ASC;

总结如下:hive用timestamp类型的时间与string类型比较大小时，需要将timestamp类型转成date类型再去比较。(方法不唯一,应该是只要能进行自动类型转换就可以比较)