![](https://i-blog.csdnimg.cn/blog_migrate/d755ccc00d6b241152c2f11179aaf152.png)
![](https://i-blog.csdnimg.cn/blog_migrate/f0c90f01a84a85fdc0492ca1e40deb54.png)
![](https://i-blog.csdnimg.cn/blog_migrate/8ec2043d3943df858e59127b864ede9a.png)
hive> create database ods;
OK
Time taken: 0.102 seconds
hive> create database dwd;
OK
Time taken: 0.017 seconds
hive> create database dwm;
OK
Time taken: 0.031 seconds
hive> create database dws;
OK
Time taken: 0.017 seconds
hive> create database ads;
OK
Time taken: 0.018 seconds
hive> create database dim;
OK
Time taken: 0.031 seconds
hive> show databases;
OK
ads
default
dim
dwd
dwm
dws
ods
Time taken: 0.005 seconds, Fetched: 7 row(s)
[root@master soft]# cd ctyun/
[root@master ctyun]# ls
dim_usertag_msk_m ods_ddr ods_oidd
dwd_merge ods_dpi ods_wcdr
hive> use ods;
OK
Time taken: 0.046 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS ods.ods_oidd(
> mdn string comment '手机号码'
> ,start_time string comment '业务开始时间'
> ,county_id string comment '区县编码'
> ,longi string comment '经度'
> ,lati string comment '纬度'
> ,bsid string comment '基站标识'
> ,grid_id string comment '网格号'
> ,biz_type string comment '业务类型'
> ,event_type string comment '事件类型'
> ,data_source string comment '数据源'
> )
> comment 'oidd位置数据表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> location '/data/tour/ods/ods_oidd';
OK
Time taken: 0.13 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS ods.ods_wcdr (
> mdn string comment '手机号码'
> ,start_time string comment '业务开始时间'
> ,county_id string comment '区县编码'
> ,longi string comment '经度'
> ,lati string comment '纬度'
> ,bsid string comment '基站标识'
> ,grid_id string comment '网格号'
> ,biz_type string comment '业务类型'
> ,event_type string comment '事件类型'
> ,data_source string comment '数据源'
> )
> comment 'wcdr位置数据表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> location '/data/tour/ods/ods_wcdr';
OK
Time taken: 0.037 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS ods.ods_ddr(
> mdn string comment '手机号码'
> ,start_time string comment '业务开始时间'
> ,county_id string comment '区县编码'
> ,longi string comment '经度'
> ,lati string comment '纬度'
> ,bsid string comment '基站标识'
> ,grid_id string comment '网格号'
> ,biz_type string comment '业务类型'
> ,event_type string comment '事件类型'
> ,data_source string comment '数据源'
> )
> comment 'ddr位置数据表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> location '/data/tour/ods/ods_ddr';
OK
Time taken: 0.035 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS ods.ods_dpi(
> mdn string comment '手机号码'
> ,start_time string comment '业务开始时间'
> ,county_id string comment '区县编码'
> ,longi string comment '经度'
> ,lati string comment '纬度'
> ,bsid string comment '基站标识'
> ,grid_id string comment '网格号'
> ,biz_type string comment '业务类型'
> ,event_type string comment '事件类型'
> ,data_source string comment '数据源'
> )
> comment 'dpi位置数据表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> location '/data/tour/ods/ods_dpi';
OK
Time taken: 0.042 seconds
hive> show tables;
OK
ods_ddr
ods_dpi
ods_oidd
ods_wcdr
Time taken: 0.012 seconds, Fetched: 4 row(s)
hive> load data local inpath '/usr/local/soft/ctyun/ods_oidd/day_id=20180503/*' into table ods_oidd partition(day_id=20180503);
Loading data to table ods.ods_oidd partition (day_id=20180503)
Partition ods.ods_oidd{day_id=20180503} stats: [numFiles=10, totalSize=789852206]
OK
Time taken: 9.502 seconds
hive> select count(*) from ods_oidd;
OK
5196414
Time taken: 28.53 seconds, Fetched: 1 row(s)
hive> load data local inpath '/usr/local/soft/ctyun/ods_wcdr/day_id=20180503/*' into table ods_wcdr partition(day_id=20180503);
Loading data to table ods.ods_wcdr partition (day_id=20180503)
Partition ods.ods_wcdr{day_id=20180503} stats: [numFiles=10, totalSize=790241502]
OK
Time taken: 7.215 seconds
hive> load data local inpath '/usr/local/soft/ctyun/ods_ddr/day_id=20180503/*' into table ods_ddr partition(day_id=20180503);
Loading data to table ods.ods_ddr partition (day_id=20180503)
Partition ods.ods_ddr{day_id=20180503} stats: [numFiles=10, totalSize=784969334]
OK
Time taken: 6.652 seconds
hive> load data local inpath '/usr/local/soft/ctyun/ods_dpi/day_id=20180503/*' into table ods_dpi partition(day_id=20180503);
Loading data to table ods.ods_dpi partition (day_id=20180503)
Partition ods.ods_dpi{day_id=20180503} stats: [numFiles=10, totalSize=784530290]
OK
Time taken: 7.367 seconds
hive> use dwd;
OK
Time taken: 0.013 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS dwd.dwd_res_regn_mergelocation_msk_d (
> mdn string comment '手机号码'
> ,start_time string comment '业务开始时间'
> ,county_id string comment '区县编码'
> ,longi string comment '经度'
> ,lati string comment '纬度'
> ,bsid string comment '基站标识'
> ,grid_id string comment '网格号'
> ,biz_type string comment '业务类型'
> ,event_type string comment '事件类型'
> ,data_source string comment '数据源'
> )
> comment '位置数据融合表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS ORC
> location '/data/tour/dwd/dwd_res_regn_mergelocation_msk_d';
OK
Time taken: 0.062 seconds
hive> alter table dwd.dwd_res_regn_mergelocation_msk_d add partition(day_id=20180503);
OK
Time taken: 0.041 seconds
hive> insert into table dwd.dwd_res_regn_mergelocation_msk_d partition(day_id="20180503")
> select mdn
> ,start_time
> ,county_id
> ,longi
> ,lati
> ,bsid
> ,grid_id
> ,biz_type
> ,event_type
> ,data_source
> from ods.ods_oidd
> where day_id = "20180503"
> union all
> select mdn
> ,start_time
> ,county_id
> ,longi
> ,lati
> ,bsid
> ,grid_id
> ,biz_type
> ,event_type
> ,data_source
> from ods.ods_wcdr
> where day_id = "20180503"
> union all
> select mdn
> ,start_time
> ,county_id
> ,longi
> ,lati
> ,bsid
> ,grid_id
> ,biz_type
> ,event_type
> ,data_source
> from ods.ods_dpi
> where day_id = "20180503"
> union all
> select mdn
> ,start_time
> ,county_id
> ,longi
> ,lati
> ,bsid
> ,grid_id
> ,biz_type
> ,event_type
> ,data_source
> from ods.ods_ddr
> where day_id = "20180503";
hive> use dwm;
OK
Time taken: 0.009 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS dwm.dwm_staypoint_msk_d (
> mdn string comment '用户手机号码'
> ,longi string comment '网格中心点经度'
> ,lati string comment '网格中心点纬度'
> ,grid_id string comment '停留点所在电信内部网格号'
> ,county_id string comment '停留点区县'
> ,duration string comment '机主在停留点停留的时间长度(e' ),lTime-eTim
> ,grid_first_time string comment '网格第一个记录位置点时间(秒级)'
> ,grid_last_time string comment '网格最后一个记录位置点时间(秒级)'
> )
> comment '停留点表'
> PARTITIONED BY (
> day_id string comment '天分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS ORC
> location '/data/tour/dwm/dwm_staypoint_msk_d';
OK
Time taken: 0.037 seconds
hive> add jar /usr/local/soft/jars/HiveUDF-1.0.jar;
Added [/usr/local/soft/jars/HiveUDF-1.0.jar] to class path
Added resources: [/usr/local/soft/jars/HiveUDF-1.0.jar]
hive> create temporary function get_points as 'ctyun.udf.getPointsUDF';
OK
Time taken: 0.009 seconds
hive> create temporary function dateBetweenUDF as 'ctyun.udf.dateBetweenUDF';
OK
Time taken: 0.004 seconds
hive> create temporary function calLength as 'ctyun.udf.calLength';
OK
Time taken: 0.005 seconds
hive> create temporary function get_city_or_prov_id as 'ctyun.udf.getCityIdOrProvID';
OK
Time taken: 0.002 seconds
hive> insert into table dwm.dwm_staypoint_msk_d partition(day_id=20180503)
> select t1.mdn
> ,get_points(grid_id)[0] as longi
> ,get_points(grid_id)[1] as lati
> ,t1.grid_id
> ,t1.county_id
> ,dateBetweenUDF(t1.grid_first_time,t1.grid_last_time) as duration
> ,t1.grid_first_time
> ,t1.grid_last_time
> from (
> select mdn
> ,grid_id
> ,county_id
> ,min(split(start_time,',')[0]) as grid_first_time
> ,max(split(start_time,',')[1]) as grid_last_time
> from dwd.dwd_res_regn_mergelocation_msk_d
> where day_id="20180503"
> group by mdn, grid_id, county_id
> )t1;
hive> CREATE EXTERNAL TABLE IF NOT EXISTS dim.dim_usertag_msk_m (
> mdn string comment '手机号大写MD5加密'
> ,name string comment '姓名'
> ,gender string comment '性别,1男2女'
> ,age string comment '年龄'
> ,id_number string comment '证件号码'
> ,number_attr string comment '号码归属地'
> ,trmnl_brand string comment '终端品牌'
> ,trmnl_price string comment '终端价格'
> ,packg string comment '套餐'
> ,conpot string comment '消费潜力'
> ,resi_grid_id string comment '常住地网格'
> ,resi_county_id string comment '常住地区县'
> )
> comment '用户画像表'
> PARTITIONED BY (
> month_id string comment '月分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS PARQUET
> location '/data/tour/dim/dim_usertag_msk_m';
OK
Time taken: 0.041 seconds
hive> alter table dim.dim_usertag_msk_m add partition(month_id=201805);
OK
Time taken: 0.046 seconds
hive> load data local inpath '/usr/local/soft/ctyun/dim_usertag_msk_m/month_id=201805/*' into table dim.dim_usertag_msk_m partition(month_id=201805);
Loading data to table dim.dim_usertag_msk_m partition (month_id=201805)
Partition dim.dim_usertag_msk_m{month_id=201805} stats: [numFiles=10, totalSize=91725546]
OK
Time taken: 1.932 seconds
hive> CREATE EXTERNAL TABLE IF NOT EXISTS dws.dws_county_tourist_msk_d (
> mdn string comment '手机号大写MD5加密'
> ,source_county_id string comment '游客来源区县'
> ,d_county_id string comment '旅游目的地县代码'
> ,d_stay_time double comment '游客在该县停留的时间长度(小时)'
> ,d_max_distance double comment '游客本次出游距离'
> )
> comment '旅游应用专题数据县级别-天'
> PARTITIONED BY (
> day_id string comment '日分区'
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS PARQUET
> location '/data/tour/dws/dws_county_tourist_msk_d';
OK
Time taken: 0.041 seconds
hive> insert into table dws.dws_county_tourist_msk_d partition(day_id="20180503")
> select ttt1.mdn
> ,ttt1.source_county_id
> ,ttt1.d_county_id
> ,ttt1.d_stay_time
> ,ttt1.d_max_distance
> from(
> select mdn
> ,resi_county_id as source_county_id
> ,county_id as d_county_id
> ,sum(duration) as d_stay_time
> ,max(calLength(tt1.grid_id,tt1.resi_grid_id)) as d_max_distance
> from(
> select t1.mdn
> ,t1.grid_id
> ,t1.county_id
> ,t1.duration
> ,t2.resi_county_id
> ,t2.resi_grid_id
> from (
> select mdn
> ,grid_id
> ,county_id
> ,duration
> from dwm.dwm_staypoint_msk_d
> where day_id='20180503'
> ) t1 join(
> select mdn
> ,resi_county_id
> ,resi_grid_id
> from dim.dim_usertag_msk_m
> where month_id='201805'
> ) t2 on t1.mdn = t2.mdn
> ) tt1 group by tt1.mdn,tt1.county_id,tt1.resi_county_id
> )ttt1 where d_stay_time > 180 and d_max_distance > 10000
> ;
hive> select * from dws_county_tourist_msk_d
> where day_id=20180503
> limit 10;
hive> select t1.d_county_id
> ,count(*) as d_county_cnt
> from (
> select d_county_id
> from dws.dws_county_tourist_msk_d
> where day_id="20180503"
> ) t1 group by t1.d_county_id
> order by d_county_cnt desc limit 20;
hive> select t1.d_county_id
> ,t2.gender
> ,count(*) as d_county_gender_cnt
> from(
> select mdn
> ,d_county_id
> from dws.dws_county_tourist_msk_d
> where day_id="20180503"
> ) t1 left join (
> select mdn
> ,gender
> from dim.dim_usertag_msk_m
> where month_id=20180503
> ) t2 on t1.mdn = t2.mdn
> group by t1.d_county_id,t2.gender;
hive> select t1.d_county_id
> ,t2.age
> ,count(*) as d_county_age_cnt
> from(
> select mdn
> ,d_county_id
> from dws.dws_county_tourist_msk_d
> where day_id="20180503"
> ) t1 left join (
> select mdn
> ,age
> from dim.dim_usertag_msk_m
> where month_id=20180503
> ) t2 on t1.mdn = t2.mdn
> group by t1.d_county_id,t2.age;
hive> select t1.d_county_id
> ,t2.number_attr
> ,count(*) as d_county_number_attr_cnt
> from(
> select mdn
> ,d_county_id
> from dws.dws_county_tourist_msk_d
> where day_id="20180503"
> ) t1 left join (
> select mdn
> ,number_attr
> from dim.dim_usertag_msk_m
> where month_id=20180503
> ) t2 on t1.mdn = t2.mdn
> group by t1.d_county_id,t2.number_attr;