Hive开发总结与问题复盘

一、今日头条广告预警、暂停以及加量

• 开始时间：11-22
• 预计完成时间： 12-21

二、中间遇到的问题

1、业务需求方面

• 由于广告预警，加量，每小时监控，需要的是每小时跑一次数据，而由于之前的load_toutiao_data的job的接口拉取的还是每天汇总计算好的数据，没能对头条开发者技术文档仔细研读，导致业务理解出现偏差，后来与市场部进行多次沟通交流，最终确定新的prd需求。需要每天的每个小时的明细数据，改动接口传参，重新dump头条的广告明细数据。
• 由于之前只是对头条广告明细数据，和广告数据进行了每小时拉取，没有对广告组数据每小时处理，数据产生偏差比较大。
• 广告暂停是每天跑一次，自然而然用到的是每天的数据，之前没能有深刻理解，使用的每小时的明细数据进行计算聚合，导致数据计算少，发现后及时修改逻辑。
• 当初自己理解为广告暂停是每天跑一次，用的是昨天的一整天的历史数据。后来又和市场再三确认，更改为计算的每个小时的明细数据然后进行累计计算。
  因为计算到当前时间的48小时内的累计消耗，后又进行重构更改job。耗费了大量的时间

2、SQL方面

• 首先是自己没能深刻认识Group By和Distinct的应用，把维度和指标的字段都进行group by分组，导致二次犯错。后经老师傅指导批评，重新学习SQL语法，sql方面问题GROUP BY和DISTINCT的使用错误，需要深刻反思。

  dm_toutiao_adstat_hourly是存放的广告计划明细小时数据，需要按照广告计划ad_id和stat_datetime小时标签来进行分组后，然后计算相关指标。

  --插入数据
  ALTER TABLE  hdp_drq_dw_db.da_toutiao_adstat_hourly drop PARTITION(cal_dt =${today},hour='$bash{date +%H -d '${todayDateTime}'}') ;
  insert overwrite table  hdp_drq_dw_db.da_toutiao_adstat_hourly partition (cal_dt =${today},hour='$bash{date +%H -d '${todayDateTime}'}')
  select
  ad_id
  ,stat_datetime
  ,sum(cost) as cost
  ,sum(convert) as convert
  ,sum(case when convert<=0.0 then 0 else cost end)/sum(case when convert<=0.0 then 1 else convert end) as convert_cost
  ,max(advertiser_id) as advertiser_id
  ,max(campaign_id) as campaign_id
  from hdp_drq_dw_db.dm_toutiao_adstat_hourly
  where cal_dt = ${today} and hour='$bash{date +%H -d '${todayDateTime}'}'
  group by
  ad_id
  ,stat_datetime
  ;

• 头条公司账户，广告计划成本，广告计划状态明细汇总中间表，进行关联的时候出现错误，导致数据发散。

  反思是业务逻辑理解出现问题，需要的是ad_id,advertiser_id,campaign_id三者的关系理解。首先是账户advertiser_id对应多个campagin_id，然后campaign_id对应多个广告ad_id。再者，之前是用广告组每天的数据表进行关联，由于广告的开启，暂停等状态受广告组限制，所以广告组也需要使用每小时的明细数据来进行业务逻辑处理，筛选出广告组状态为开启状态status=’CAMPAIGN_STATUS_ENABLE’

  --插入数据
  alter table hdp_drq_dw_db.dm_toutiao_company_ad_adstat_hourly drop PARTITION(cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime} 1 hour ago'}',hour='$bash{date +%H -d '${todayDateTime} 1 hour ago'}') ;
  insert overwrite table hdp_drq_dw_db.dm_toutiao_company_ad_adstat_hourly PARTITION(cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime} 1 hour ago'}',hour='$bash{date +%H -d '${todayDateTime} 1 hour ago'}')
  select
  c.company
  ,c.name as company_name
  ,a.ad_id
  ,b.os
  ,a.advertiser_id
  ,b.status
  ,b.opt_status
  ,b.start_time
  ,b.name
  ,a.cost
  ,a.convert_cost
  ,b.budget
  ,b.budget_mode
  ,a.convert
  ,c.operate_flag
  ,c.monitor_flag
  ,a.stat_datetime
  from hdp_drq_dw_db.da_toutiao_adstat_hourly a
  left outer join  hdp_drq_dw_db.dm_toutiao_ad_hourly b on a.ad_id=b.ad_id and b.cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime}'}' and b.hour='$bash{date +%H -d '${todayDateTime}'}'
  left outer join hdp_drq_dw_db.dw_toutiao_account c on a.advertiser_id=c.advertiser_id and c.cal_dt=${dealDate}
  /*此处关联后发现数据量明显变少，具体问题待排查，可能是load_toutiao_data_hourly拉取的广告组数据不完整。
  正常逻辑是需要用广告组id是关联的，然后只有在广告组开启的状态下，才能有广告计划的开启关闭意义。
  */
  --left outer join hdp_drq_dw_db.dm_toutiao_campaign_hourly d on a.campaign_id=d.campaign_id and d.cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime}'}' and d.hour='$bash{date +%H -d '${todayDateTime}'}'
  where a.cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime} 1 hour ago'}' and a.hour='$bash{date +%H -d '${todayDateTime} 1 hour ago'}'
  and
  a.stat_datetime<='$bash{date -d"${todayDateTime} 1 hour ago" +"%F %H:%M:%S"}'
  --and d.status='CAMPAIGN_STATUS_ENABLE'
  ;

• 去重汇总广告计划的da层表da_toutiao_adstat_hourly

  --插入数据
  ALTER TABLE  hdp_drq_dw_db.da_toutiao_adstat_hourly drop PARTITION(cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime} 1 hour ago'}',hour='$bash{date +%H -d '${todayDateTime} 1 hour ago'}') ;
  insert overwrite table  hdp_drq_dw_db.da_toutiao_adstat_hourly partition (cal_dt = '$bash{date +%Y-%m-%d -d '${todayDateTime} 1 hour ago'}',hour='$bash{date +%H -d '${todayDateTime} 1 hour ago'}')
  select 
  a.ad_id
  ,a.stat_datetime
  ,sum(a.cost) as cost
  ,sum(a.convert) as convert
  ,case when sum(a.convert)=0 then 0 else sum(a.cost)/sum(a.convert) end as convert_cost
  ,max(a.advertiser_id) as advertiser_id
  ,max(a.campaign_id) as campaign_id
  from (
  			--由于存在数据重复，所以需要用ad_id,stat_datetime,inventory字段进行group by去重
  			select 
  			ad_id
  			,stat_datetime
  			,inventory
  			,max(cost) as cost
  			,max(convert) as convert
  			,max(advertiser_id) as advertiser_id
  			,max(campaign_id) as campaign_id
  			from hdp_drq_dw_db.dm_toutiao_adstat_hourly
      		--dm_toutiao_adstat_hourly表当前时间的数据前一个小时的数据肯定是完整的。
  			where cal_dt = ${today} and hour='$bash{date +%H -d '${todayDateTime}'}'
      		--此处逻辑至关重要，直接过滤1 hour ago的数据存到前一个小时分区。
  			and stat_datetime<='$bash{date -d"${todayDateTime} 1 hour ago" +"%F %H:%M:%S"}'
  			group by
  			ad_id
  			,stat_datetime
  			,inventory ) a
  group by
  a.ad_id
  ,a.stat_datetime
  ;

• 每小时监控的MiniReport的某些数据指标需要为整数，或者为百分数保留两位小数

  拉取头条返回的数据里的convert_cost是不能使用的，需要我们自己去计算

  使用convert_cost=sum(cost)/sum(convert)

  由于convert可能会有0的情况，所以需要处理convert_cost为0

,case when sum(COALESCE(`convert`,0))=0 then 0.0 else cast((sum(COALESCE(cost,0))/sum(COALESCE(`convert`,0))) as decimal(10,2)) end as convert_cost

 select
 --广告计划的convert转化数必须为整数，所以要转为为int
 ,cast(round(a.convert,0) as int) as convert
--其他的数据指标保留2位小数
 ,round(a.convert_cost,2) as convert_cost
 --当日-昨日差值
 ,round(a.cost-b.cost,2) as sub_cost
 ,cast(round(a.convert,0) as int)-cast(round(b.convert,0) as int) as sub_convert
 ,round(a.convert_cost-b.convert_cost,2) as sub_convert_cost
 --转化日环比
 --环比计算公式=（当前的数量/昨天的数量-1）×100%
 --日环比为百分数，需要处理
 ,case when b.convert=0 then '0.0%' else   concat(cast (cast((a.convert/b.convert-1)*100 as decimal(18,2)) as string),'%')  end as convert_rhb
 --转化成本日环比
 ,case when b.convert_cost=0 then '0.0%' else  concat(cast(cast((a.convert_cost/b.convert_cost-1)*100 as decimal(18,2)) as string),'%') end as convert_cost_rhb
 from xxx_table      
 ;

• 补跑历史的数据问题

  load_toutiao_data_hourly只需要跑一次就能把历史的全量数据给拉取下来

  dump的时候也是只需要dump一次，然后再dm层表，把所有的数据都insert进行就可以。这样就是24个小时分区都是有历史24小时的消耗明细数据。然后da层表利用stat_datetime进行过滤到小时分区的数据就可以。

  dump的job，从192.168.1.1:1700服务器上拉取txt数据文件，是每一个txt文件都会去建立连接，然后MR当初底层设置默认是200多个连接。直接dump的job设置为并行跑，一次性选择跑多个历史会refused connection，原因是192.168.1.1:1700服务器限制连接数不够了。

3、接口方面

• 首先是ToutiaoAPI.getAdReport接口的更改

  需要改group by传参，按照时间，id，广告位分组，获取每个小时的明细数据。

  if ("hourly".equals(flag)) {
              request.put("group_by", "[\"STAT_GROUP_BY_FIELD_ID\",\"STAT_GROUP_BY_FIELD_STAT_TIME\",\"STAT_GROUP_BY_INVENTORY\"]");
              request.put("time_granularity", "STAT_TIME_GRANULARITY_HOURLY");
          }

• 拉取数据的接口更改

  由于每小时跑的接口，需要大量调用toutiaoAPI，串行运行会导致job拉取时间过长，一个小时跑出结果可能不行，影响后续逻辑计算的job，后改为多线程并行调度，初步使用newFixedThreadPool(30)，30个core线程数来调用，进行代码优化。但是跑的时候，后来查看log日志，出现调用头条API接口too frequently太频繁。提工单和头条技术人员请教后，每秒并发最大量为20个。后调整线程池核心线程为10个，防止再次出现错误。

• 并发拉取的时候，发现数据出现重复问题，以及数据没能保证所有线程池所有线程分配的任务都进行完毕再退出，直接调用executor.shutdown()，后来进行重新研究并本地测试线程池使用，在业务逻辑中加入。

  public void saveAdStats(String date, String flag) throws Exception {
      for (ToutiaoAccount toutiaoAccount : accounts) {
         ...
          executor.submit(() -> {
             //业务逻辑
          });
      }
      executor.shutdown();
      //如果没有终止，让线程等待100ms
      while(!executor.isTerminated()) {
          Thread.sleep(100);
      }
  }

• 为了保证数据准确无误，则da层表存的数据是’$bash{date +%Y-%m-%d -d ‘${todayDateTime} 1 hour ago’}’的数据。需要单独处理23的数据，来作为第二天00点来单独进行拉取。也就是job当前的运行时间1 hour ago，实际存的00点到当前时间1 hour ago的数据集。接口需要处理这种特殊情况

  • 首先是load_toutiao_data_hourly的job的入口传参时间

  $bash{date +%Y-%m-%d-%H -d '${todayDateTime}'}

  • 需要单独处理的saveAdStat2File接口的业务逻辑代码

    /**
             * getAdReport接口只支持日期为yyyy-MM-dd格式，需要处理
             */
            if ("hourly".equals(flag)) {
                String hour = date.substring(11, 13);
                date = date.substring(0, 10);
                //时间为00点的时候，去拉前一天的数据作为23点的数据
                if ("00".equals(hour)) {
                    //传入job参数的日期，防止补历史出错
                    date = Util.dateReduce(date,-1);
                }
            }

  • saveAdState2File接口的bug修改

    拉取头条广告的时候，只拉取的第一页的900条数据，可能会出现潜在性的bug，后改掉拉取totalPage的所有广告计划

    for (List<Long> arrayList : lists) {
        			//首先获取第一页的数据，来获取接口返回的totalpage。来把所有的广告计划数据都进行拉取下来
                JSONObject report = touTiaoAPI.getAdReport(advertiserId, date, date, 1, pageSize, arrayList, true, flag);
                JSONArray jsonArray = new JSONArray();
                if (report == null || report.isEmpty()) {
                    continue;
                }
                int code = Integer.parseInt(Optional.ofNullable(report.getString("code")).orElse("40001"));
        		//根据头条接口返回的状态码进行判断，如果大于0的都为异常情况
                if (code > 0) {
                    log.error("拉取头条广告计划明细数据异常：{}", report);
                } else {
                    //把拉取的第一页广告计划处理，然后存到array里
                    JSONArray objects = Optional.ofNullable(report.getJSONObject("data").getJSONArray("list")).orElse(new JSONArray());
                    jsonArray.addAll(objects);
                    //获取广告计划总页码
                    Integer totalPage = Optional.of(report.getJSONObject("data").getJSONObject("page_info").getIntValue("total_page")).orElse(1);				//如果有大于1页的数据，则需要拉取所有的
                    if (totalPage > 1) {
                        for (int i = 2; i <= totalPage; i++) {
                            report = touTiaoAPI.getAdReport(advertiserId, date, date, i, pageSize, arrayList, true, flag);
                            JSONArray data = Optional.ofNullable(report.getJSONObject("data").getJSONArray("list")).orElse(new JSONArray());
                            jsonArray.addAll(data);
                        }
                    }
                }

• 广告计划加量的接口逻辑

  自己理解和导师给的suggestion出现了偏差，在代码里计算判断加量的逻辑，犯了形而上学的错误，后更改为在Hive里新增一个newBudget，判断逻辑进行汇总计算。然后dump到MySQL数据库，进行查询后比较来进行是否加量。

4、页面方面

重新学习Thymeleaf，以及分页相关问题。

赏

请我喝杯咖啡吧

支付宝

微信

• 本文作者： Victor Dan
• 本文链接： https://victorblog.github.io/2019/12/11/开发总结与问题复盘/
• 版权声明： 本博客所有文章除特别声明外，均采用 Apache License 2.0 许可协议。转载请注明出处！