一百八十六、大数据离线数仓完整流程——步骤五、在Hive的DWS层建动态分区表并动态加载数据

一、目的

经过6个月的奋斗，项目的离线数仓部分终于可以上线了，因此整理一下离线数仓的整个流程，既是大家提供一个案例经验，也是对自己近半年的工作进行一个总结。

二、数仓实施步骤

（五）步骤五、在Hive的DWS层建动态分区表并动态加载数据

1、Hive的DWS层建库建表语句

--如果不存在则创建hurys_dc_dws数据库
create database if not exists hurys_dc_dws;
--使用hurys_dc_dws数据库
use hurys_dc_dws;

--1.1、转向比数据内部表——动态分区——转向流量——5分钟周期  dws_turnratio_volume_5min
create  table  if not exists dws_turnratio_volume_5min(
    device_no       string       comment '设备编号',
    create_time     timestamp    comment '创建时间',
    start_time      timestamp    comment '开始时间',
    name            string       comment '场景',
    direction       string       comment '雷达朝向',
    volume_sum      int          comment '指定时间段内通过路口的车辆总数',
    volume_left     int          comment '指定时间段内通过路口的左转车辆总数',
    volume_straight int          comment '指定时间段内通过路口的直行车辆总数',
    volume_right    int          comment '指定时间段内通过路口的右转车辆总数',
    volume_turn     int          comment '指定时间段内通过路口的掉头车辆总数'
)
comment '转向比数据表——动态分区——5分钟周期'
partitioned by (day string)   --分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
stored as orc                --表存储数据格式为orc
;

2、海豚执行DWS层建表语句工作流

对于刚部署的服务器，由于Hive没有建库建表、而且手动建表效率低，因此通过海豚调度器直接执行建库建表的.sql文件

（1）海豚的资源中心加建库建表的SQL文件

（2）海豚配置DWS层建表语句的工作流（不需要定时，一次就行）

3、海豚配置DWS层每日动态加载数据的工作流（指定分区名）

（1）海豚配置DWS层每日动态加载数据的工作流（需要定时，每日一次）

#! /bin/bash
source /etc/profile

nowdate=`date --date='0 days ago' "+%Y%m%d"`
yesdate=`date -d yesterday +%Y-%m-%d`

hive -e "
use hurys_dc_dws;

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=1000;
set hive.exec.max.dynamic.partitions=1500;

insert  overwrite  table  dws_evaluation_1hour  partition(day='$yesdate')
select
       dwd_ev.device_no,
       lane_no,
       cycle,
       create_time,
       concat(substr(create_time, 1, 14), '00:00') start_time,
       dwd_te.name,
       dwd_rc.direction,
       dwd_rl.lane_direction,
       dwd_ev.volume,
       queue_len_max,
       sample_num,
       stop_avg,
       delay_avg,
       stop_rate,
       travel_dist,
       travel_time_avg
from hurys_dc_dwd.dwd_evaluation as dwd_ev
    right join hurys_dc_dwd.dwd_radar_lane as dwd_rl
              on dwd_rl.device_no=dwd_ev.device_no and dwd_rl.lane_num=dwd_ev.lane_no
    right join hurys_dc_dwd.dwd_device_team as dwd_dt
              on dwd_dt.device_no=dwd_ev.device_no
    right join hurys_dc_dwd.dwd_team as dwd_te
              on dwd_te.id = dwd_dt.team_id
    right join hurys_dc_dwd.dwd_radar_config as dwd_rc
             on dwd_rc.device_no=dwd_ev.device_no
where dwd_ev.create_time is not null  and day= '$yesdate'
group by dwd_ev.device_no, lane_no, cycle, create_time, dwd_te.name, dwd_rc.direction, dwd_rl.lane_direction, dwd_ev.volume, queue_len_max, sample_num, stop_avg, delay_avg, stop_rate, travel_dist, travel_time_avg
"

（2）工作流定时任务设置（注意与其他工作流的时间间隔）

（3）注意点

3.3.1 动态加载数据的SQL需要指定分区名day='$yesdate'，只加载前一天的数据

剩余数仓部分，待续！