湖南群呼诈骗识别记录

来源：互联网发布：win10 卸载软件灰色编辑：程序博客网时间：2024/04/30 09:13

表说明

tmp_inter_mapa

原始话单表，话单输入到这个表

CREATE  TABLE hn_dw.tmp_inter_mapa(  eventid string,   lac string,   ci string,   olac string,   oci string,   dlac string,   dci string,   firstlac string,   firstci string,   lastlac string,   lastci string,   callingnum string,   callednum string,   callingimsi string,   calledimsi string,   callingtmsi string,   calledtmsi string,   callingimei string,   calledimei string,   spckind string,   msccode string,   connoffset string,   answerdur string,   alertoffset string,   reldirect string,   reloffset string,   relcause string,   btime string,   etime string,   bsccode string)COMMENT 'ENTERPRISE INFORMATION OF hn_dw.TMP_INTER_MAPA'ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   LINES TERMINATED BY '\n' STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.SequenceFileInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'LOCATION  'hdfs://eb179:9000/user/hive/warehouse/hn_dw.db/tmp_inter_mapa'TBLPROPERTIES (  'created_at'='2013-11-28',   'transient_lastDdlTime'='1440063111',   'creator'='leon')

很多话单字段

inter_mapa_d

在话单原始表中加入日期，天分区：

CREATE  TABLE hn_dw.inter_mapa_d(  eventid string,   lac string,   ci string,   olac string,   oci string,   dlac string,   dci string,   firstlac string,   firstci string,   lastlac string,   lastci string,   callingnum string,   callednum string,   callingimsi string,   calledimsi string,   callingtmsi string,   calledtmsi string,   callingimei string,   calledimei string,   spckind string,   msccode string,   connoffset string,   answerdur string,   alertoffset string,   reldirect string,   reloffset string,   relcause string,   btime string,   etime string,   bsccode int)COMMENT 'ENTERPRISE INFORMATION OF hn_dw.TMP_INTER_MAPA'PARTITIONED BY (   record_day string)ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   LINES TERMINATED BY '\n' STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION  'hdfs://eb179:9000/user/hive/warehouse/hn_dw.db/inter_mapa_d'TBLPROPERTIES (  'numPartitions'='17',   'numFiles'='4323',   'created_at'='2013-11-28',   'transient_lastDdlTime'='1440999922',   'totalSize'='860020823083',   'numRows'='0',   'rawDataSize'='0',   'creator'='leon')

mid_inter_p1-9

p表，按照时间（begintime截取出的两个时间标签）、号码分组，统计出每组的呼叫次数，最前开始时间。
record_day分区

CREATE  TABLE hn_dw.mid_inter_mapa_p1(  time_tag1 string COMMENT 'from deserializer',   time_tag2 string COMMENT 'from deserializer',   calling string COMMENT 'from deserializer',   cnt int COMMENT 'from deserializer',   start_time string COMMENT 'from deserializer')COMMENT 'ENTERPRISE INFORMATION OF hn_dw.MID_INTER_MAPA_P'PARTITIONED BY (   record_day string)

mid_inter_d1-9

在全部话单字段的基础上添加time_tag1，和time_tag2。这俩是begintime截取出的字段，外加record_day 分区。

CREATE  TABLE hn_dw.mid_inter_mapa_d1(  time_tag1 string COMMENT 'from deserializer',   time_tag2 string COMMENT 'from deserializer',   eventid string COMMENT 'from deserializer',   lac string COMMENT 'from deserializer',   ci string COMMENT 'from deserializer',   olac string COMMENT 'from deserializer',   oci string COMMENT 'from deserializer',   dlac string COMMENT 'from deserializer',   dci string COMMENT 'from deserializer',   firstlac string COMMENT 'from deserializer',   firstci string COMMENT 'from deserializer',   lastlac string COMMENT 'from deserializer',   lastci string COMMENT 'from deserializer',   callingnum string COMMENT 'from deserializer',   callednum string COMMENT 'from deserializer',   callingimsi string COMMENT 'from deserializer',   calledimsi string COMMENT 'from deserializer',   callingtmsi string COMMENT 'from deserializer',   calledtmsi string COMMENT 'from deserializer',   callingimei string COMMENT 'from deserializer',   calledimei string COMMENT 'from deserializer',   spckind string COMMENT 'from deserializer',   msccode string COMMENT 'from deserializer',   connoffset string COMMENT 'from deserializer',   answerdur string COMMENT 'from deserializer',   alertoffset string COMMENT 'from deserializer',   reldirect string COMMENT 'from deserializer',   reloffset string COMMENT 'from deserializer',   relcause string COMMENT 'from deserializer',   btime string COMMENT 'from deserializer',   etime string COMMENT 'from deserializer',   bsccode int COMMENT 'from deserializer')COMMENT 'ENTERPRISE INFORMATION OF hn_dw.MID_INTER_MAPA_D'PARTITIONED BY (   record_day string)

d表只有一天的数据

mid_inter_dd11

dd表结构和d表一样，但是是一周的数据

0 0