hive处理脏数据

INSERT OVERWRITE table ods.js_beauty_tmp
SELECT *
from ods.js_beauty_tmp
WHERE map_lat != ‘’
AND map_lng != ‘’
AND map_lat IS NOT NULL
AND map_lng IS NOT NULL
AND map_lat != 0
AND map_lng != 0
AND map_lat not like ‘-%’
AND map_lng not like ‘-%’
and city != ‘北京’
and city != ‘大同’;

select length(2.02106280816214E+23) from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 ;
select contact_id from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 and LENGTH(contact_id)<contact_id;
select contact_id from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 and contact_id regexp “[0-9]{16,24}”;
select contact_id from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 and contact_id regexp “[0-9a-zA-Z]{16,24}”;
–AND contact_id not like ‘2.+%’
select contact_id from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 and contact_id not like ‘2.E+%’;

select contact_id from dc_dwa.dwa_d_labour_contact_hotlinebt where month_id=202106 and day_id=28 and contact_id not like ‘E+%’;