栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

doris灰度建表及shell脚本推数至doris

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

doris灰度建表及shell脚本推数至doris

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

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/956033.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号