doris灰度建表
DROP TABLE IF EXISTS atp_route_report_province_line_statistics;
CREATE TABLE atp_route_report_province_line_statistics(
record_time string COMMENT '记录时间'
,department_route string COMMENT '组织全路径'
,department_id_route string COMMENT '组织ID全路径'
,organize_code string COMMENT '组织(最大)1级'
,organize_name string COMMENT '组织(最大)1级'
,business string COMMENT '业务部 2级'
,business_code string COMMENT '业务部编码 2级'
,area string COMMENT '大区/事业部 3级'
,area_code string COMMENT '大区/事业部 3级'
,dispatch_center string COMMENT '取派中心 4级'
,dispatch_center_code string COMMENT '取派中心编码 4级'
,part_area string COMMENT '分区 5级'
,part_area_code string COMMENT '分区 5级'
,part string COMMENT '区/分拨 6级'
,part_code string COMMENT '区/分拨 6级'
,department string COMMENT '取派部 7级'
,department_code string COMMENT '取派部 7级'
,community string COMMENT '片区 8级'
,community_code string COMMENT '片区 8级'
,origin_organize_id string COMMENT '始发组织ID 9级'
,origin_organize_name string COMMENT '始发组织名称 9级'
,total_line_count int COMMENT '总趟数'
,total_line_delayed int COMMENT '总晚发数'
,total_line_driving_timeout int COMMENT '总行驶超时数'
,total_waybill_count int COMMENT '总运单票数'
,total_waybill_late_count int COMMENT '总运单落货票数'
,total_plan_send_count int COMMENT '总正班应发票数'
,total_send_count int COMMENT '总正班实发票数'
,total_un_send_count int COMMENT '总正班未发票数'
,total_agent_total_cost DECIMAL(12,4) COMMENT '总代理费用'
,total_agent_list_weight DECIMAL(18,2) COMMENT '总代理单重量'
,total_average_weight DECIMAL(18,2) COMMENT '总平摊核载'
,label_code string COMMENT '标签CODE'
,load_rate_count_total int COMMENT '有效装载率趟次'
,total_avg_load_rate DECIMAL(18,4) COMMENT '装载率'
,etl_date string COMMENT '跑批时间'
)
DUPLICATE KEY(record_time, department_route)
DISTRIBUTED BY HASH(record_time, department_route, department_id_route) BUCKETS 5
;
DROP TABLE IF EXISTS atp_route_report_province_line_info;
CREATE TABLE atp_route_report_province_line_info (
record_time string COMMENT '记录时间'
,department_route string COMMENT '组织全路径'
,department_id_route string COMMENT '组织ID全路径'
,origin_organize_id string COMMENT '始发组织ID 9级'
,origin_organize_name string COMMENT '始发组织名称 9级'
,follow_fly_id BIGINT COMMENT '跟飞ID'
,car_model INT COMMENT '车型(tms_car_model)'
,transport_mode INT COMMENT '运输模式 tms_ff_agent_transportMode'
,agent_number string COMMENT '代理单号'
,chartered_car_task_number string COMMENT '任务编码(包车管理)-新增'
,specified_depart_time string COMMENT '规定发车时间'
,flight_takeoff_time string COMMENT '起飞时间'
,flight_landing_time string COMMENT '落地时间'
,starting_airport string COMMENT '始发机场'
,destination_airport string COMMENT '目的机场'
,agent_total_cost DECIMAL ( 12, 4 ) COMMENT '代理费用'
,agent_list_weight DECIMAL ( 18, 2 ) COMMENT '代理单重量'
,exception_type INT COMMENT '异常类型字典 tms_followfly_exception_type 10:晚发 20:晚到'
,exception_remark string COMMENT '异常内容'
,reason_type INT COMMENT '原因类型字典:tms_followfly_reason_type'
,reason_remark string COMMENT '原因详情'
,reason_analysis INT COMMENT '原因分析'
,task_number string COMMENT '包车任务编号 (RW180313-000811)'
,car_number string COMMENT '车牌号 来源包车调度中的车牌号'
,start_place_id BIGINT COMMENT '始发地点ID'
,start_place string COMMENT '始发地点'
,arrival_place_id BIGINT COMMENT '到达地点ID'
,arrival_place string COMMENT '到达地点'
,dest_origin_organize_name string COMMENT '目的组织名称'
,dest_origin_organize_id string COMMENT '目的组织ID'
,car_load DECIMAL ( 9, 2 ) COMMENT '车辆载重'
,car_task_number string COMMENT '任务编码'
,line_name string COMMENT '线路名'
,stop_station string COMMENT '经停地'
,is_delayed INT COMMENT '是否晚发1是/0否'
,is_driving_timeout INT COMMENT '是否行驶超时1是/0否'
,late_count INT COMMENT '落货票数'
,average_weight DECIMAL ( 18, 2 ) COMMENT '平摊核载'
,plan_send_count INT COMMENT '正班应发票数'
,send_count INT COMMENT '正班实发票数'
,un_send_count INT COMMENT '正班未发票数'
,effect_trip INT COMMENT '是否参与总趟次计算, 1-是参与 2-不参与'
,department_id BIGINT COMMENT '部门Id'
,department_name string COMMENT '部门名称'
,car_type INT COMMENT '车辆类别(10:内请包车 20:外请包车 30:合同包车)(tms_car_type)'
,exception_remark_time_out string COMMENT '晚到预警类型'
,reason_type_time_out INT COMMENT '晚到原因类型'
,reason_remark_time_out string COMMENT '晚到原因详情'
,reason_analysis_time_out INT COMMENT '晚到原因类型'
,is_flow_effect INT COMMENT '正班走货率有效趟次,1-有效,0-无效'
,load_rate DECIMAL ( 18, 4 ) COMMENT '装载率'
)
DUPLICATE KEY ( record_time, department_route )
DISTRIBUTED BY HASH ( record_time, department_route, department_id_route ) BUCKETS 5
;
DROP TABLE IF EXISTS atp_route_report_province_waybill_info;
CREATE TABLE atp_route_report_province_waybill_info(
record_time string COMMENT '记录时间'
,department_route string COMMENT '组织全路径'
,department_id_route string COMMENT '组织ID全路径'
,organize_code string COMMENT '组织(最大)1级'
,organize_name string COMMENT '组织(最大)1级'
,business string COMMENT '业务部 2级'
,business_code string COMMENT '业务部编码 2级'
,area string COMMENT '大区/事业部 3级'
,area_code string COMMENT '大区/事业部 3级'
,dispatch_center string COMMENT '取派中心 4级'
,dispatch_center_code string COMMENT '取派中心编码 4级'
,part_area string COMMENT '分区 5级'
,part_area_code string COMMENT '分区 5级'
,part string COMMENT '区/分拨 6级'
,part_code string COMMENT '区/分拨 6级'
,department string COMMENT '取派部 7级'
,department_code string COMMENT '取派部 7级'
,community string COMMENT '片区 8级'
,community_code string COMMENT '片区 8级'
,origin_organize_id string COMMENT '始发组织ID 9级'
,origin_organize_name string COMMENT '始发组织名称 9级'
,follow_fly_id bigint COMMENT '跟飞ID'
,chartered_car_task_number string COMMENT '任务编码(包车管理)-新增'
,operate_end_time string COMMENT '报单时间'
,operate_network_id bigint COMMENT '报单点部=操作人点部'
,operate_network string COMMENT '报单点部=操作人点部名称'
,network_id bigint COMMENT '收件地点部ID'
,network string COMMENT '收件地点部'
,service_mode string COMMENT '服务方式 次日达-20 隔日达-30 陆运件-40'
,freight_weight decimal(10, 4) COMMENT '计费重量'
,stowage_count bigint COMMENT '实配件数'
,consign_weight decimal(30, 2) COMMENT '托运重量'
,waybill_id bigint COMMENT '运单id'
,waybill_number string COMMENT '运单号'
,flight_number string COMMENT '配载航班'
,specified_depart_time string COMMENT '规定发车时间'
,flight_takeoff_time string COMMENT '起飞时间=实际发车时间'
,flight_landing_time string COMMENT '落地时间=到达时间'
,origin_place_id bigint COMMENT '最终始发地id'
,origin_place_name string COMMENT '最终始发地名字'
,starting_airport string COMMENT '始发机场'
,destination_airport string COMMENT '目的机场'
,dest_origin_organize_id string COMMENT '目的组织id'
,dest_origin_organize_name string COMMENT '目的组织名称'
,is_actual_send bigint COMMENT '正班走货率,运单是否已发 1-已发 2-未发'
,is_fall bigint COMMENT '是否落货 10-是 20-否'
,departure_unloading_time string COMMENT '出港卸货时间'
,origin_organize_id_extend string COMMENT '正班未发运单始发组织id'
,origin_organize_name_extend string COMMENT '正班未发运单始发组织名称'
,etl_date string COMMENT '跑批时间'
)
DUPLICATE KEY(record_time, department_route)
DISTRIBUTED BY HASH(record_time, department_route, department_id_route) BUCKETS 5
;
推数
#!/bin/bash
source ./config.sh
source /app/module/bigdata-schedule/conf/config.sh
year_=$1
month_=$2
day_=$3
kye_day=${year_}-${month_}-${day_}
log1=$(date -d'4 day ago "now"' +%Y-%m-%d)
echo ${log4}
echo "传入时间-------------------${log1}-----"
if [ ${envi} = "uat" ];then
doris_cmd="mysql -h xxx -P 9030 -u bigdata_report_rw -pxxx -B -N -e "
broker_name=broker1
echo '-------------------传入UAT参数---------------'
elif [ ${envi} = "gray" ];then
doris_cmd="mysql -h xxx -P 9030 -u bigdata_crm_rw -pxxx -B -N -e"
broker_name=hdfs_broker
echo '-------------------传入gray参数---------------'
elif [ ${envi} = "prod" ];then
doris_cmd="mysql -h ${doris_db_zngk_ip} -P ${doris_db_zngk_port} -u ${doris_db_zngk_user} -p${doris_db_zngk_pwd} -B -N -e"
broker_name=${doris_db_zngk_broker}
echo '-------------------传入prod参数---------------'
else
echo "------------------参数未传------------ "
fi
#1、删除doris数据 (避免数据重复)
echo "-----删除分区开始------"
$doris_cmd "TRUNCATE TABLE atp_route.atp_route_report_province_waybill_info; "
echo "-----删除分区结束------"
#2、 执行load数据操作
labelid_1="atp_route_report_province_waybill_info`date +%s`"
echo "-----打印label名开始------"
echo ${labelid_1}
echo "-----打印label名结束------"
load_sql_1="
use atp_route;
load label ${labelid_1}
(
data infile('hdfs://bigbigworld/user/hive/warehouse/out_atp_route.db/atp_route_report_province_waybill_info_all_fd_bak/part*')
into table atp_route_report_province_waybill_info
FORMAT AS 'ORC'
(
record_time
,department_route
,department_id_route
,organize_code
,organize_name
,business
,business_code
,area
,area_code
,dispatch_center
,dispatch_center_code
,part_area
,part_area_code
,part
,part_code
,department
,department_code
,community
,community_code
,origin_organize_id
,origin_organize_name
,follow_fly_id
,chartered_car_task_number
,operate_end_time
,operate_network_id
,operate_network
,network_id
,network
,service_mode
,freight_weight
,stowage_count
,consign_weight
,waybill_id
,waybill_number
,flight_number
,specified_depart_time
,flight_takeoff_time
,flight_landing_time
,origin_place_id
,origin_place_name
,starting_airport
,destination_airport
,dest_origin_organize_id
,dest_origin_organize_name
,is_actual_send
,is_fall
,departure_unloading_time
,origin_organize_id_extend
,origin_organize_name_extend
,etl_date
)
)
with broker '${broker_name}';"
echo "----------------'${broker_name}'-----"
#提交
$doris_cmd "${load_sql_1}"
[ $? -ne 0 ] && exit 1
#轮训查询导入状态 默认200分钟超时脚本返回失败,可根据数据量大小适当调整
for i in `seq 2000`;do
export_log=`$doris_cmd "use atp_route;show load where label = '$labelid_1'"`
state=`echo $export_log |awk '{print $3}'|tail -1`
if [ "$state" = "FINISHED" ];then
echo "load_sql_1 省际线路报表运单中间表导入成功"
break
elif [ "$state" = "CANCELLED" ];then
echo "load_sql_1 省际线路报表运单中间表导入失败,n${export_log}"
exit 1
elif [ "$state" = "" ];then
echo "load_sql_1 省际线路报表运单中间表参数有误"
exit 1
else
echo "load_sql_1 省际线路报表运单中间表导入中"
sleep 10s
continue
fi
echo "load_sql_1 省际线路报表运单中间表导入超时"
exit 1
done



