ketingli1
commented
5 years ago Open iwuming opened 5 years ago
ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago insert overwrite into #app_da_ads_event_detail partition (month=substr('${yyyymmdd-1}',1,6), table_name=pt_column)
select fe.org_id as org_id,
dv.org_name as org_name,
dve.vehicle_ln as vehicle_ln,
dve.vehicle_id as vehicle_id,
fe.vehicle_vin as vehicle_vin,
fe.driver_id as driver_id,
ddr.driver_name as driver_name,
det.event_name as event_name,
if(det.score_type = 'NULL',-1,det.score_type) as score_type,
det.event_type as event_type,
from_unixtime(fe.event_begin_time) as start_time,
from_unixtime(fe.event_end_time) as end_time,
"" as event_location,
0 as event_deduct_marks,
"" as event_explain,
fe.speed as speed,
fe.begin_longitude / 1000000 as start_longitude, --经纬度转换
fe.end_longitude / 1000000 as end_longitude,
fe.begin_latitude / 1000000 as start_latitude,
fe.end_latitude / 1000000 as end_latitude,
if(fe.steer_state = 1, 1, 0) as left_steer_status, --转向状态转化
if(fe.steer_state = 2, 1, 0) as right_steer_status,
if(fe.braking_state is not null, fe.braking_state, 0) as brake_status, --制动状态
concat(fe.vehiclevin, '', event_begintime, '', fe.event_code) as event_id,
fe.journey_id as journey_id,
if(fce.start_time is null, '0', '1') as check_status, --核对状态 1评分数据 0不参与评分数据，默认1 人工模式参考打分
substr('${yyyymmdd-1}',1,6) as statistical_time,
current_timestamp() as gmt_create,
current_timestamp() as gmt_modify,
'1' as status,
concat('event_detailtxt', lpad(abs(hash_code(substr('${yyyymmdd-1}',1,6))) % 1023, 4, 0)) as t_name,
fe.event_code as event_code,
substr('${yyyymmdd-1}',1,6) as data_time,
substr('${yyyymmdd-1}',1,6) as month,
concat('eventdetail', lpad(abs(hash_code(substr('${yyyymmdd-1}',1,6))) % 1023, 4, 0)) as table_name
from (select
from #app_da_fct_event
where month = substr('${yyyymmdd-1}',1,6)
and from_unixtime(event_begin_time, 'yyyyMMdd') <= '${yyyymmdd-2}'
) fe
left join #app_da_dim_event_type det on fe.event_code = det.event_code --查询事件详细介绍
left join (select distinct driver_id, driver_name from #app_da_dim_driver) ddr
on fe.driver_id = ddr.driver_id --查询司机详细信息
left join (select distinct vehicle_vin, vehicle_ln, vehicle_id
from #app_da_dim_vehicle
) dve on fe.vehicle_vin = dve.vehicle_vin --查询车辆详细信息
left join (select from #app_da_dim_organization) dv on dv.org_id = fe.org_id
left join (select
from #app_da_fct_manual_score_event
where month = substr('${yyyymmdd-1}',1,6)) fce
on fe.org_id = fce.org_id and
fce.vehicle_vin = fe.vehicle_vin and
fce.event_code = fe.event_code and
unix_timestamp(fce.start_time) = fe.event_begin_time
where fe.month = substr('${yyyymmdd-1}',1,6) --查询事件列表信息
and fe.construct_type != 'violate' --违章事件不回流
UNION ALL -- 当天新增事件
select fe.org_id as org_id,
dv.org_name as org_name,
dve.vehicle_ln as vehicle_ln,
dve.vehicle_id as vehicle_id,
fe.vehicle_vin as vehicle_vin,
fe.driver_id as driver_id,
ddr.driver_name as driver_name,
det.event_name as event_name,
if(det.score_type = 'NULL',-1,det.score_type) as score_type,
det.event_type as event_type,
from_unixtime(fe.event_begin_time) as start_time,
from_unixtime(fe.event_end_time) as end_time,
"" as event_location,
0 as event_deduct_marks,
"" as event_explain,
fe.speed as speed,
fe.begin_longitude / 1000000 as start_longitude, --经纬度转换
fe.end_longitude / 1000000 as end_longitude,
fe.begin_latitude / 1000000 as start_latitude,
fe.end_latitude / 1000000 as end_latitude,
if(fe.steer_state = 1, 1, 0) as left_steer_status, --转向状态转化
if(fe.steer_state = 2, 1, 0) as right_steer_status,
if(fe.braking_state is not null, fe.braking_state, 0) as brake_status, --制动状态
concat(fe.vehiclevin, '', event_begintime, '', fe.event_code) as event_id,
fe.journey_id as journey_id,
'1' as check_status, --核对状态 1评分数据 0不参与评分数据，默认1 人工模式参考打分
substr('${yyyymmdd-1}',1,6) as statistical_time,
current_timestamp() as gmt_create,
current_timestamp() as gmt_modify,
'1' as status,
concat('event_detailtxt', lpad(abs(hash_code(substr('${yyyymmdd-1}',1,6))) % 1023, 4, 0)) as t_name,
fe.event_code as event_code,
substr('${yyyymmdd-1}',1,6) as data_time,
substr('${yyyymmdd-1}',1,6) as month,
concat('eventdetail', lpad(abs(hash_code(substr('${yyyymmdd-1}',1,6))) % 1023, 4, 0)) as table_name
from (select
from #app_da_fct_event
where month = substr('${yyyymmdd-1}',1,6)
and from_unixtime(event_begin_time, 'yyyyMMdd') = '${yyyymmdd-1}'
) fe
left join #app_da_dim_event_type det on fe.event_code = det.event_code --查询事件详细介绍
left join (select distinct driver_id, driver_name from #app_da_dim_driver) ddr
on fe.driver_id = ddr.driver_id --查询司机详细信息
left join (select distinct vehicle_vin, vehicle_ln, vehicle_id
from #app_da_dim_vehicle
) dve on fe.vehicle_vin = dve.vehicle_vin --查询车辆详细信息
left join (select * from #app_da_dim_organization) dv on dv.org_id = fe.org_id
where fe.month = substr('${yyyymmdd-1}',1,6) --查询事件列表信息
and fe.construct_type != 'violate' --违章事件不回流
;
ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago ketingli1
commented
5 years ago 
iwuming
commented
5 years ago java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/HBaseConfiguration
ketingli1
commented
5 years ago select 'init_data' as org_id,'init_data' as event_code,'init_data' as event_name,0 as deduct_marks,'init_data' as update_explain,'init_data' as operator,current_timestamp() as gmt_create,current_timestamp() as gmt_modify,1 as status
ketingli1
commented
5 years ago select 'init_data' as org_id,'init_data' as vehicle_vim,'init_data' as event_code,current_timestamp() as start_time
ketingli1
commented
5 years ago ketingli1
commented
5 years ago select regexp_replace('abcd\sas\as','\\',"\"")
ketingli1
commented
5 years ago ketingli1
commented
4 years ago set -e
source /home/hadoop/spark-2.2.2-bin-hadoop2.7/conf/spark-env.sh
/home/hadoop/spark-2.2.2-bin-hadoop2.7/bin/spark-submit --verbose \ --class com.icongtai.zebra.db.dwd.ManualScoreApplication \ --master yarn \ --deploy-mode cluster \ --num-executors "15" \ --executor-memory "5g" \ --executor-cores "2" \ --driver-memory "2g" \ --conf spark.yarn.am.waitTime=600s \ /home/hadoop/banma/driver-behavior-bigdata-source-1.0-SNAPSHOT.jar \ /ytdw/app ${yyyymm}
sh /home/hadoop/banma/resource/shell/datax/yt_shell/Move2.sh app_da_fct_vehicle_scoring ${yyyymm}
20190831.xlsx