离线数据开发流程小案例-图书馆业务数据 - ToB企服应用市场:ToB评测及商务社交产业平台

[jxd@102 lend_out]$ file -i lend_out_2019.csv
lend_out_2019.csv: text/plain; charset=unknown-8bit
vim 文件后，:set fileencoding=utf-8 设置文件的编码
[jxd@102 lend_out]$ file -i lend_out_2019.csv
lend_out_2019.csv: text/plain; charset=utf-8

复制代码

sed -i '1d' <fileName>

复制代码

create database shanghai_edu location '/warehouse/shanghai_edu';
// 创建时间维度表
CREATE EXTERNAL TABLE `dim_date`
(
`date_key` STRING COMMENT 'id',
`date_ymd` STRING COMMENT '日期 yy-MM-dd',
`date_ch` STRING COMMENT '中文日期',
`dim_year` STRING COMMENT '年',
`quarter_id` TINYINT COMMENT '季度数字',
`quarter_ch` STRING COMMENT '中文季度',
`year_month` STRING comment '年月',
`dim_month` STRING comment '月份',
`dim_day` STRING COMMENT '一年中的第几天',
`dim_month_day` STRING COMMENT '一个月中的第几天',
`dim_week` STRING COMMENT '一年中的第几周星期一是星期的第一天',
`year_week` STRING COMMENT '年周',
`dim_week1` STRING COMMENT '一年中的第几周星期天是星期的第一天',
`dim_year1` STRING COMMENT '对应 dim_week1 的年份',
`dim_weekday` STRING COMMENT '一周的中的第几天',
`weekday_ch` STRING COMMENT '星期几',
`holiday_name` STRING COMMENT '节假日名称',
`is_day_off` TINYINT COMMENT '是否放假 1表示放假，0表示不放假 2表示调班'
) COMMENT '时间维度表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
location '/warehouse/shanghai_edu/dim/dim_date';
load data local inpath '/opt/project/shanghai_edu/data/dim/dim_date.csv' into table shanghai_edu.dim_date;
// 创建分场馆维度表
CREATE EXTERNAL TABLE if not exists shanghai_edu.dim_sub_library
(
`sub_library_or_pickup_location` STRING COMMENT 'sub_library 或 pickup_location',
`sub_library_location_name` STRING COMMENT '分场馆所在地址'
) COMMENT '分场馆维度表'
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/dim/dim_sub_library';
// 创建一张存储格式为 csv 的临时表
CREATE TABLE shanghai_edu.csv_table
(
sub_library_or_pickup_location STRING,
sub_library_location_name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
// 将数据导入到临时表
load data local inpath '/opt/project/shanghai_edu/data/dim/dim_sublibrary.csv' into table shanghai_edu.csv_table;
INSERT overwrite TABLE shanghai_edu.dim_sub_library
SELECT * FROM shanghai_edu.csv_table;
drop table shanghai_edu.csv_table;
// 创建入馆表
CREATE external table `ods_enter`
(
`university_id` varchar(100) comment '学校代码 10280',
`patron_id` varchar(100) comment '读者ID',
`student_grade` varchar(100) comment '学生年级',
`patron_dept` varchar(100) comment '读者所在院系',
`patron_type` varchar(100) comment '读者类型',
`visit_time` varchar(100) comment '入馆时间',
`visit_sub_library` varchar(100) comment '入馆地点'
)
COMMENT '入馆表'
partitioned by (`year` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = """,
"escapeChar" = "\"
)
location '/warehouse/shanghai_edu/ods/ods_enter';
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2013.csv' into table shanghai_edu.ods_enter partition (year = '2013');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2014.csv' into table shanghai_edu.ods_enter partition (year = '2014');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2015.csv' into table shanghai_edu.ods_enter partition (year = '2015');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2016.csv' into table shanghai_edu.ods_enter partition (year = '2016');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2017.csv' into table shanghai_edu.ods_enter partition (year = '2017');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2018.csv' into table shanghai_edu.ods_enter partition (year = '2018');
load data local inpath '/opt/project/shanghai_edu/data/enter/enter_2019.csv' into table shanghai_edu.ods_enter partition (year = '2019');
// 创建外借表
CREATE external TABLE `ods_lend_out`
(
`university_id` varchar(100) comment '学校代码 10280',
`item_id` varchar(100) comment '单册唯一记录号',
`loan_date` varchar(100) comment '外借日期',
`sub_library` varchar(100) comment '图书所在分馆/馆藏地',
`due_date` varchar(100) comment '到期日期',
`loan_hour` varchar(100) comment '外借时间',
`due_hour` varchar(100) comment '到期时间',
`returned_date` varchar(100) comment '归还日期',
`item_status` varchar(100) comment '单册状态',
`returned_hour` varchar(100) comment '归还时间',
`last_renew_date` varchar(100) comment '最后续借日期',
`renewal_no` varchar(100) comment '续借次数',
`recall_date` varchar(100) comment '催还日期',
`item_call_no` varchar(100) comment '单册索书号',
`recall_due_date` varchar(100) comment '催还后应还日期',
`author` varchar(100) comment '图书作者',
`publish_year` varchar(100) comment '图书出版年',
`press` varchar(100) comment '图书出版社',
`title` varchar(100) comment '图书题名',
`ISBN` varchar(100) comment 'isbn',
`patron_id` varchar(100) comment '读者ID',
`patron_type` varchar(100) comment '读者类型',
`student_grade` varchar(100) comment '学生年级',
`id` varchar(100) comment 'id',
`patron_dept` varchar(100) comment '读者所在院系'
)
COMMENT '外借表'
partitioned by (`year` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = """,
"escapeChar" = "\"
)
location '/warehouse/shanghai_edu/ods/ods_lend_out';
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2013.csv' into table shanghai_edu.ods_lend_out partition (year = '2013');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2014.csv' into table shanghai_edu.ods_lend_out partition (year = '2014');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2015.csv' into table shanghai_edu.ods_lend_out partition (year = '2015');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2016.csv' into table shanghai_edu.ods_lend_out partition (year = '2016');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2017.csv' into table shanghai_edu.ods_lend_out partition (year = '2017');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2018.csv' into table shanghai_edu.ods_lend_out partition (year = '2018');
load data local inpath '/opt/project/shanghai_edu/data/lend_out/lend_out_2019.csv' into table shanghai_edu.ods_lend_out partition (year = '2019');
// 创建预约表
CREATE external table ods_subscribe
(
`university_id` varchar(100) comment '学校代码 10280',
`open_date` varchar(100) comment '预约日期',
`open_hour` varchar(100) comment '预约时间',
`end_request_date` varchar(100) comment '预约兴趣期结束日期',
`request_date` varchar(100) comment '预约兴趣期开始日期',
`end_hold_date` varchar(100) comment '预约保留日期',
`hold_date` varchar(100) comment '预约满足日期',
`pickup_location` varchar(100) comment '取书点',
`item_status` varchar(100) comment '单册状态',
`sub_library` varchar(100) comment '图书所在分馆/馆藏地',
`fulfilled` varchar(100) comment '预约需求是否满足',
`item_call_no` varchar(100) comment '单册索书号',
`item_id` varchar(100) comment '单册唯一记录号',
`author` varchar(100) comment '图书作者',
`publish_year` varchar(100) comment '图书出版年',
`press` varchar(100) comment '图书出版社',
`title` varchar(100) comment '图书题名',
`ISBN` varchar(100) comment '图书 ISBN 号',
`patron_id` varchar(100) comment '读者 ID',
`patron_type` varchar(100) comment '读者类型',
`student_grade` varchar(100) comment '学生年级',
`id` varchar(100) comment 'id',
`patron_dept` varchar(100) comment '读者所在院系'
) COMMENT '预约表'
partitioned by (`year` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = """,
"escapeChar" = "\"
)
location '/warehouse/shanghai_edu/ods/ods_subscribe';
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2013.csv' into table shanghai_edu.ods_subscribe partition (year = '2013');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2014.csv' into table shanghai_edu.ods_subscribe partition (year = '2014');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2015.csv' into table shanghai_edu.ods_subscribe partition (year = '2015');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2016.csv' into table shanghai_edu.ods_subscribe partition (year = '2016');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2017.csv' into table shanghai_edu.ods_subscribe partition (year = '2017');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2018.csv' into table shanghai_edu.ods_subscribe partition (year = '2018');
load data local inpath '/opt/project/shanghai_edu/data/subscribe/subscribe_2019.csv' into table shanghai_edu.ods_subscribe partition (year = '2019');

复制代码

CREATE EXTERNAL TABLE if not exists shanghai_edu.adm_lo_cnt_time_with_type_dd
(
`patron_type` STRING COMMENT '读者类型',
`lend_out_cnt` bigint COMMENT '借出数',
`avg_lend_out_cnt` double comment '平均借出数',
`min_duration` double comment '最小借出时间',
`avg_duration` double comment '平均借出时间',
`max_duration` double comment '最大借出时间'
) COMMENT '外借次数、外借时间和用户类型的关系'
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/adm/adm_lo_cnt_time_with_type_dd';
insert overwrite table shanghai_edu.adm_lo_cnt_time_with_type_dd
select patron_type
, count(*) lend_out_cnt
, round(count(*) / count(DISTINCT (patron_id)), 2) avg_lend_out_cnt
, round(min((unix_timestamp(returned_date, 'yyyyMMdd') - unix_timestamp(loan_date, 'yyyyMMdd')) /
(60 * 60 * 24)), 2) min_duration -- 单位为天
, round(AVG((unix_timestamp(returned_date, 'yyyyMMdd') - unix_timestamp(loan_date, 'yyyyMMdd')) /
(60 * 60 * 24)), 2) avg_duration
, round(MAX((unix_timestamp(returned_date, 'yyyyMMdd') - unix_timestamp(loan_date, 'yyyyMMdd')) /
(60 * 60 * 24)), 2) max_duration
from shanghai_edu.ods_lend_out
group by patron_type;

复制代码

CREATE EXTERNAL TABLE if not exists shanghai_edu.adm_ent_subscribe_cnt_with_type
(
`patron_type` STRING COMMENT '读者类型',
`enter_cnt` bigint COMMENT '总入馆次数/预约次数',
`avg_lend_out_cnt` double comment '平均入馆次数/平均预约次数'
) COMMENT '入馆次数、预约次数和读者类型的关系'
partitioned by (stat_type STRING COMMENT '统计类型(enter/subscribe)')
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/adm/adm_ent_subscribe_cnt_with_type';
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table shanghai_edu.adm_ent_subscribe_cnt_with_type partition (stat_type)
select patron_type,
count(*) enter_cnt,
round(count(*) / count(DISTINCT (patron_id)), 2) avg_enter_cnt,
'enter' stat_type
from shanghai_edu.ods_enter
group by patron_type
union all
select patron_type,
count(*) sub_cnt,
round(count(*) / count(DISTINCT (patron_id)), 2) avg_sub_cnt,
'subscribe' stat_type
from shanghai_edu.ods_subscribe
group by patron_type
;

复制代码

CREATE EXTERNAL TABLE if not exists shanghai_edu.adm_subscribe_no_satisfy_lib_year_stat
(
`no_fulfilled_ratio` double COMMENT '预约不满足率',
`sub_library_location_name` string COMMENT '分馆位置',
`subscribe_year` string comment '预约年份'
) COMMENT '各分馆各年份的预约不满足率统计表'
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/adm/adm_subscribe_no_satisfy_lib_year_stat';
insert overwrite table shanghai_edu.adm_subscribe_no_satisfy_lib_year_stat
select round(sum(if(fulfilled = '不满足', 1, 0)) / count(1), 4) no_fulfilled_ratio
, sub_library_location_name
, subscribe_year
from (
// @formatter:off
select
CASE
WHEN fulfilled = 'N' THEN '不满足'
WHEN fulfilled = 'Y' THEN '满足'
ELSE '不知道'
END AS fulfilled
,coalesce(b.sub_library_location_name, '-') sub_library_location_name
,coalesce(substr(open_date, 1, 4), '-') subscribe_year
from shanghai_edu.ods_subscribe a
left join shanghai_edu.dim_sub_library b
on a.sub_library = b.sub_library_or_pickup_location
// @formatter:on
) tb_tmp
GROUP BY sub_library_location_name, subscribe_year
with cube;

复制代码

CREATE EXTERNAL TABLE if not exists shanghai_edu.adm_subscribe_no_satisfy_top
(
`ISBN` string COMMENT 'isbn',
`cnt` bigint COMMENT '预约不到次数'
) COMMENT '预约不到的 top10 书籍'
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/adm/adm_subscribe_no_satisfy_top';
insert overwrite table shanghai_edu.adm_subscribe_no_satisfy_top
select
ISBN
,count(1) cnt
from shanghai_edu.ods_subscribe
where fulfilled = 'N' and ISBN != ''
group by ISBN
order by cnt desc
limit 10
;

复制代码

CREATE FUNCTION parseHour AS 'com.jxd.udf.ParseHour' USING JAR 'hdfs:///udf/yelp-udf-1.0-SNAPSHOT.jar';
reload function;
show functions;
drop function if exists formatVisitTime;
DESCRIBE FUNCTION formatVisitTime;
CREATE FUNCTION formatVisitTime AS 'com.jxd.udf.FormatVisitTime' USING JAR 'hdfs:///udf/yelp-udf-1.0-SNAPSHOT.jar';
CREATE EXTERNAL TABLE if not exists shanghai_edu.adm_enter_time_stat
(
`holiday_name` string COMMENT '节日名称',
`visit_hour` string COMMENT '入馆时间(小时)',
`cnt` bigint comment '入馆数量'
) COMMENT '入馆时间分布统计表'
STORED AS PARQUET
LOCATION '/warehouse/shanghai_edu/adm/adm_enter_time_stat';
// @formatter:off
insert overwrite table shanghai_edu.adm_enter_time_stat
select holiday_name
, visit_hour
, count(1) cnt
from
(
select if(b.holiday_name is not null and b.holiday_name != '', b.holiday_name, '非节假日') holiday_name
, parseHour(a.visit_time) visit_hour
from shanghai_edu.ods_enter a
left join shanghai_edu.dim_date b on formatVisitTime(a.visit_time) = b.date_key
) tb_tmp
GROUP BY holiday_name, visit_hour
with cube;
;

复制代码

hadoop fs -mkdir /udf
hadoop fs -put yelp-udf-1.0-SNAPSHOT.jar /udf/

复制代码

CREATE FUNCTION upper_case_udf AS 'com.jxd.udf.UpperCaseUDF' USING JAR 'hdfs:///udf/yelp-udf-1.0-SNAPSHOT.jar';

复制代码

RELOAD FUNCTION;

复制代码

DESCRIBE FUNCTION <function_name>;

复制代码

DROP FUNCTION upper_case_udf;

复制代码

DROP FUNCTION upper_case_udf;

复制代码

CREATE FUNCTION upper_case_udf AS 'com.jxd.udf.UpperCaseUDF' USING JAR 'hdfs:///udf/yelp-udf-1.0-SNAPSHOT.jar';

复制代码

bin/sqoop export \
--connect "jdbc:mysql://node112:3306/shanghai_edu?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456 \
--table adm_lo_cnt_time_with_type_dd \
--num-mappers 1 \
--hcatalog-database shanghai_edu \
--hcatalog-table adm_lo_cnt_time_with_type_dd
--hive-partition-key <partition_key> \
--warehouse-dir <hdfs_path_to_hive_table>

复制代码

bin/sqoop export \
--connect "jdbc:mysql://node112:3306/shanghai_edu?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456 \
--table adm_ent_subscribe_cnt_with_type \
--num-mappers 1 \
--hcatalog-database shanghai_edu \
--hcatalog-table adm_ent_subscribe_cnt_with_type
--hive-partition-key stat_type \
--warehouse-dir /warehouse/shanghai_edu/adm/adm_ent_subscribe_cnt_with_type

复制代码

yum install convmv

复制代码

convmv -f gbk -t utf-8 --notest -r ./

复制代码