hive 查询注意问题

1)对于hive内置的列，不是自己建的，在查询的时候需要添加反引号` 比如：`_mt_message`，别在这里犯错误，

(2)南京的_mt_message是json的格式，所以可以直接使用：get_json_object(`_mt_message`,'$.Flag')

南京的：

代码块

 

 

 

 

 

 

{"EngineId":"90927395","GPSBrand":"伴车星","OwnerName":"南京壹健汽车租赁有限公司","FeePrintId":"757012490408","VehicleNo":"苏A88H7J","Address":320100,"GPSModel":"ET02D","TransDateStart":20170703,"CheckState":"1","GPSInstallDate":20151113,"TransAgency":"南京市道路运输管理局","FuelType":"A","CertifyDateB":20170703,"FixState":"1","CertifyDateA":20181228,"CompanyId":"33030009","FareType":"222-c-1-1","VehicleType":"小型汽车","RegisterDate":20180911,"TransArea":"南京","Flag":2,"Seats":5,"Brand":"荣威","PhotoId":"https://p0.meituan.net/dache/238553418911c6fb51cfa160211199222613832.jpg@1500h?token=1.1545927117.dache.a605eb5b9dfbf5c0ccaf6ea2ba4a8a01","CommercialType":1,"driverId":195139349,"VehicleColor":"白色","State":0,"Model":"i6","UpdateTime":20181228000337,"PlateColor":"1","VIN":"50D65D3FF6C241192","EngineDisplace":"1600","TransDateStop":20210701}

 

但是上海的不是：

代码块

 

 

 

 

 

 

3 shanghai{"EngineId":"90927395","GPSBrand":"伴车星","OwnerName":"南京壹健汽车租赁有限公司","FeePrintId":"757012490408","VehicleNo":"苏A88H7J","Address":320100,"GPSModel":"ET02D","TransDateStart":20170703,"CheckState":"1","GPSInstallDate":20151113,"TransAgency":"南京市道路运输管理局","FuelType":"A","CertifyDateB":20170703,"FixState":"1","CertifyDateA":20181228,"CompanyId":"33030009","FareType":"222-c-1-1","VehicleType":"小型汽车","RegisterDate":20180911,"TransArea":"南京","Flag":2,"Seats":5,"Brand":"荣威","PhotoId":"https://p0.meituan.net/dache/238553418911c6fb51cfa160211199222613832.jpg@1500h?token=1.1545927117.dache.a605eb5b9dfbf5c0ccaf6ea2ba4a8a01","CommercialType":1,"driverId":195139349,"VehicleColor":"白色","State":0,"Model":"i6","UpdateTime":20181228000337,"PlateColor":"1","VIN":"50D65D3FF6C241192","EngineDisplace":"1600","TransDateStop":20210701}

 

所以上海的在查询的话应该先截取，然后在get_json_object();

如下：

代码块

 

 

 

 

 

 

 select get_json_object(substr(`_mt_message`,11),'$.PhotoId') from log.qcs_regulation_shanghai where dt='20181228' and msgtype='baseInfoVehicle'   

 

 

 

 

 

 

 

select * from log.qcs_regulation_shanghai where dt='20181120' limit 10;

 

 

查询南京订单成功上传数据总量