图书馆数据仓库

目次
1.数据仓库的数据来源为业务数据库（mysql）
       初始化脚本
   init_book_result.sql
2.通过sqoop将mysql中的业务数据导入到大数据平台（hive）
 导入mysql数据到hive中
3.通过hive举行数据计算和数据分析 形成数据报表
4.再通过sqoop将数据报表导出到mysql 
5.使用FineReport制作数据报表

---

1.数据仓库的数据来源为业务数据库（mysql）

        包含 图书表 t_book_info,
                借书表 t_borrow_info,  
                用户表 t_user_info  38条数据
                图书类别表 dim_books_type 5条
       初始化脚本

        init_mysql.sql
        

1. -- 设置sql_mode
2. set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
4. -- 创建数据库library
5. create database library;
7. -- 切换数据库
8. use library;
10. -- 创建用户信息表
11. CREATE TABLE t_user_info(
12.         user_id        varchar(100) not null,
13.         user_name      varchar(100) not null,
14.         sex            varchar(10)  not null,
15.         age            int not null
16. )DEFAULT CHARSET='utf8';
18. -- 创建图书表
19. CREATE TABLE t_book_info(
20.         book_id        varchar(100) not null,
21.         book_name            varchar(100) not null,
22.         type_id        varchar(100) not null
23. )DEFAULT CHARSET='utf8';
26. -- 创建图书类别表
27. CREATE TABLE dim_books_type(
28.         type_id    varchar(100) not null,
29.         type_name  varchar(100) not null
30. )DEFAULT CHARSET='utf8';
32. -- 创建借书表
33. CREATE TABLE t_borrow_order(
34.         user_id        varchar(100) not null,
35.         book_id   varchar(100) not null,
36.         create_time    varchar(100) not null
37. )DEFAULT CHARSET='utf8';
39. -- 用户信息表插入数据
40. insert into t_user_info values ('114', '王小名', '男', 22);
41. insert into t_user_info values ('115', '张美丽', '女', 27);
42. insert into t_user_info values ('116', '李华', '男', 30);
43. insert into t_user_info values ('117', '陈晓红', '女', 35);
44. insert into t_user_info values ('118', '赵伟', '男', 24);
45. insert into t_user_info values ('119', '周小燕', '女', 29);
46. insert into t_user_info values ('120', '吴强', '男', 33);
47. insert into t_user_info values ('121', '郑丽', '女', 26);
48. insert into t_user_info values ('122', '王刚', '男', 28);
49. insert into t_user_info values ('123', '黄芳', '女', 31);
50. insert into t_user_info values ('124', '刘涛', '男', 36);
51. insert into t_user_info values ('125', '杨静', '女', 25);
52. insert into t_user_info values ('126', '张杰', '男', 32);
53. insert into t_user_info values ('127', '陈莉', '女', 23);
54. insert into t_user_info values ('128', '赵勇', '男', 38);
55. insert into t_user_info values ('129', '周敏', '女', 30);
56. insert into t_user_info values ('130', '吴浩', '男', 29);
57. insert into t_user_info values ('131', '郑薇', '女', 34);
58. insert into t_user_info values ('132', '王林', '男', 27);
59. insert into t_user_info values ('133', '黄梅', '女', 26);
60. insert into t_user_info values ('134', '刘军', '男', 37);
61. insert into t_user_info values ('135', '杨慧', '女', 25);
62. insert into t_user_info values ('136', '张明', '男', 33);
63. insert into t_user_info values ('137', '陈霞', '女', 31);
64. insert into t_user_info values ('138', '赵海', '男', 28);
65. insert into t_user_info values ('139', '周兰', '女', 36);
66. insert into t_user_info values ('140', '吴飞', '男', 24);
67. insert into t_user_info values ('141', '郑敏', '女', 27);
68. insert into t_user_info values ('142', '王磊', '男', 35);
69. insert into t_user_info values ('143', '黄蓉', '女', 30);
70. insert into t_user_info values ('144', '刘丽', '女', 26);
71. insert into t_user_info values ('145', '杨勇', '男', 32);
72. insert into t_user_info values ('146', '张华', '男', 29);
73. insert into t_user_info values ('147', '陈敏', '女', 34);
74. insert into t_user_info values ('148', '赵丽', '女', 31);
75. insert into t_user_info values ('149', '周刚', '男', 25);
76. insert into t_user_info values ('150', '吴丽', '女', 37);
77. insert into t_user_info values ('151', '郑强', '男', 28);
78. insert into t_user_info values ('152', '王芳', '女', 33);
79. insert into t_user_info values ('153', '黄伟', '男', 27);
80. insert into t_user_info values ('154', '刘静', '女', 36);
81. insert into t_user_info values ('155', '杨华', '男', 24);
82. insert into t_user_info values ('156', '张敏', '女', 31);
83. insert into t_user_info values ('157', '陈军', '男', 30);
84. insert into t_user_info values ('158', '赵敏', '女', 26);
85. insert into t_user_info values ('159', '周强', '男', 35);
86. insert into t_user_info values ('160', '吴红', '女', 32);
87. insert into t_user_info values ('161', '郑丽', '女', 29);
88. insert into t_user_info values ('162', '王伟', '男', 34);
89. insert into t_user_info values ('163', '黄静', '女', 31);
90. insert into t_user_info values ('164', '刘涛', '男', 25);
91. insert into t_user_info values ('165', '杨敏', '女', 37);
92. insert into t_user_info values ('166', '张勇', '男', 28);
93. insert into t_user_info values ('167', '陈芳', '女', 33);
94. insert into t_user_info values ('168', '赵军', '男', 27);
95. insert into t_user_info values ('169', '周丽', '女', 36);
96. insert into t_user_info values ('170', '吴华', '男', 24);
98. commit;
101. -- 图书表插入数据
102. -- Book Type: 散文随笔 (a)
103. insert into t_book_info values ('a001', '珠江潮汐美', 'a');
104. insert into t_book_info values ('a002', '晨曦的低语', 'a');
105. insert into t_book_info values ('a003', '山间小路', 'a');
106. insert into t_book_info values ('a004', '秋叶之歌', 'a');
107. insert into t_book_info values ('a005', '夜色中的琴声', 'a');
108. insert into t_book_info values ('a006', '城市边缘的诗人', 'a');
109. insert into t_book_info values ('a007', '冬日里的暖阳', 'a');
110. insert into t_book_info values ('a008', '海边的沉思', 'a');
111. -- book type: 世界名著 (b)
112. insert into t_book_info values ('b001', '悲惨世界', 'b');
113. insert into t_book_info values ('b002', '百年孤独', 'b');
114. insert into t_book_info values ('b003', '双城记', 'b');
115. insert into t_book_info values ('b004', '战争与和平', 'b');
116. insert into t_book_info values ('b005', '简爱', 'b');
117. insert into t_book_info values ('b006', '飘', 'b');
118. insert into t_book_info values ('b007', '堂吉诃德', 'b');
119. insert into t_book_info values ('b008', '呼啸山庄', 'b');
120. -- book type: 少儿童书 (c)
121. insert into t_book_info values ('c001', '小王子的星球', 'c');
122. insert into t_book_info values ('c002', '魔法森林的秘密', 'c');
123. insert into t_book_info values ('c003', '海底两万里', 'c');
124. insert into t_book_info values ('c004', '勇敢的小火车头', 'c');
125. insert into t_book_info values ('c005', '神奇的种子', 'c');
126. insert into t_book_info values ('c006', '月亮上的兔子', 'c');
127. insert into t_book_info values ('c007', '彩虹桥下的秘密', 'c');
128. insert into t_book_info values ('c008', '会说话的石头', 'c');
129. -- book type: 历史小说 (d)
130. insert into t_book_info values ('d001', '三国演义', 'd');
131. insert into t_book_info values ('d002', '水浒传', 'd');
132. insert into t_book_info values ('d003', '大明王朝', 'd');
133. insert into t_book_info values ('d004', '清朝末年', 'd');
134. insert into t_book_info values ('d005', '大唐盛世', 'd');
135. insert into t_book_info values ('d006', '宋朝风云', 'd');
136. insert into t_book_info values ('d007', '明朝那些事儿', 'd');
137. insert into t_book_info values ('d008', '清朝宫廷秘史', 'd');
138. -- book type: 国学入门 (e)
139. insert into t_book_info values ('e001', '论语解读', 'e');
140. insert into t_book_info values ('e002', '道德经注释', 'e');
141. insert into t_book_info values ('e003', '易经初探', 'e');
142. insert into t_book_info values ('e004', '诗经选读', 'e');
143. insert into t_book_info values ('e005', '孟子精讲', 'e');
144. insert into t_book_info values ('e006', '庄子心解', 'e');
145. insert into t_book_info values ('e007', '大学中庸', 'e');
146. insert into t_book_info values ('e008', '孝经新解', 'e');
148. commit;
150. -- 图书类别表插入数据
151. insert into dim_books_type values('a','散文随笔');
152. insert into dim_books_type values('b','世界名著');
153. insert into dim_books_type values('c','少儿童书');
154. insert into dim_books_type values('d','历史小说');
155. insert into dim_books_type values('e','国学入门');
157. commit;
159. -- 借书表插入数据
160. insert into t_borrow_order values('114','a002','2022-11-08 09:23:54');
161. insert into t_borrow_order values('115','e002','2022-11-08 09:23:54');
162. insert into t_borrow_order values('114','b003','2022-11-08 09:23:54');
163. insert into t_borrow_order values('116','d002','2022-11-08 09:23:54');
164. insert into t_borrow_order values('114','c001','2022-11-08 09:23:54');
165. insert into t_borrow_order values('115','a005','2022-11-08 09:23:54');
166. insert into t_borrow_order values('117','b004','2022-11-08 09:23:54');
167. insert into t_borrow_order values('118','a007','2022-11-08 09:23:54');
168. insert into t_borrow_order values('118','a004','2022-11-08 09:23:54');
169. insert into t_borrow_order values('119','e003','2022-11-08 09:23:54');
170. insert into t_borrow_order values('119','d001','2022-11-08 09:23:54');
171. insert into t_borrow_order values('120','a002','2022-11-08 09:23:54');
172. insert into t_borrow_order values('120','a004','2022-11-08 09:23:54');
173. insert into t_borrow_order values('121','d005','2022-11-08 09:23:54');
174. insert into t_borrow_order values('123','b006','2022-11-08 09:23:54');
175. insert into t_borrow_order values('124','a002','2022-11-08 09:23:54');
176. insert into t_borrow_order values('125','e004','2022-11-08 09:23:54');
177. insert into t_borrow_order values('126','b002','2022-11-08 09:23:54');
178. insert into t_borrow_order values('127','a003','2022-11-08 09:23:54');
179. insert into t_borrow_order values('124','d002','2022-11-08 09:23:54');
180. insert into t_borrow_order values('122','b001','2022-11-08 09:23:54');
181. insert into t_borrow_order values('128','a001','2022-11-08 09:23:54');
182. insert into t_borrow_order values('129','a006','2022-11-08 09:23:54');
183. insert into t_borrow_order values('125','d008','2022-11-08 09:23:54');
184. insert into t_borrow_order values('123','e008','2022-11-08 09:23:54');
185. insert into t_borrow_order values('120','a005','2022-11-08 09:23:54');
186. insert into t_borrow_order values('130','b007','2022-11-08 09:23:54');
187. insert into t_borrow_order values('131','a007','2022-11-08 09:23:54');
188. insert into t_borrow_order values('132','a008','2022-11-08 09:23:54');
189. insert into t_borrow_order values('133','e004','2022-11-08 09:23:54');
190. insert into t_borrow_order values('135','a004','2022-11-08 09:23:54');
191. insert into t_borrow_order values('133','d003','2022-11-08 09:23:54');
192. insert into t_borrow_order values('136','a003','2022-11-08 09:23:54');
193. insert into t_borrow_order values('138','a004','2022-11-08 09:23:54');
194. insert into t_borrow_order values('139','d001','2022-11-08 09:23:54');
195. insert into t_borrow_order values('133','e001','2022-11-08 09:23:54');
196. insert into t_borrow_order values('133','a002','2022-11-08 09:23:54');
197. insert into t_borrow_order values('132','e002','2022-11-08 09:23:54');
198. insert into t_borrow_order values('131','a003','2022-11-08 09:23:54');
199. insert into t_borrow_order values('141','b003','2022-11-08 09:23:54');
200. insert into t_borrow_order values('140','e003','2022-11-08 09:23:54');
201. insert into t_borrow_order values('142','a005','2022-11-08 09:23:54');
202. insert into t_borrow_order values('142','d005','2022-11-08 09:23:54');
203. insert into t_borrow_order values('146','b006','2022-11-08 09:23:54');
204. insert into t_borrow_order values('144','d006','2022-11-08 09:23:54');
205. insert into t_borrow_order values('148','d007','2022-11-08 09:23:54');
206. insert into t_borrow_order values('144','e003','2022-11-08 09:23:54');
207. insert into t_borrow_order values('142','a003','2022-11-08 09:23:54');
208. insert into t_borrow_order values('143','e003','2022-11-08 09:23:54');
209. insert into t_borrow_order values('149','d004','2022-11-08 09:23:54');
210. insert into t_borrow_order values('150','a005','2022-11-08 09:23:54');
211. insert into t_borrow_order values('151','a005','2022-11-08 09:23:54');
212. insert into t_borrow_order values('151','a002','2022-11-08 09:23:54');
213. insert into t_borrow_order values('154','d003','2022-11-08 09:23:54');
214. insert into t_borrow_order values('153','d002','2022-11-08 09:23:54');
215. insert into t_borrow_order values('156','a002','2022-11-08 09:23:54');
216. insert into t_borrow_order values('155','d003','2022-11-08 09:23:54');
217. insert into t_borrow_order values('157','a004','2022-11-08 09:23:54');
218. insert into t_borrow_order values('158','d005','2022-11-08 09:23:54');
219. insert into t_borrow_order values('159','a005','2022-11-08 09:23:54');
220. insert into t_borrow_order values('154','c006','2022-11-08 09:23:54');
221. insert into t_borrow_order values('153','d007','2022-11-08 09:23:54');
222. insert into t_borrow_order values('152','c004','2022-11-08 09:23:54');
223. insert into t_borrow_order values('154','a004','2022-11-08 09:23:54');
224. insert into t_borrow_order values('151','d003','2022-11-08 09:23:54');
225. insert into t_borrow_order values('152','a002','2022-11-08 09:23:54');
226. insert into t_borrow_order values('162','c003','2022-11-08 09:23:54');
227. insert into t_borrow_order values('161','a001','2022-11-08 09:23:54');
228. insert into t_borrow_order values('166','d002','2022-11-08 09:23:54');
229. insert into t_borrow_order values('163','a002','2022-11-08 09:23:54');
230. insert into t_borrow_order values('167','c003','2022-11-08 09:23:54');
231. insert into t_borrow_order values('169','a005','2022-11-08 09:23:54');
232. commit;

复制代码

   init_book_result.sql

1. -- 设置sql_mode
2. set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
4. -- 创建数据库result,并进行切换
5. create database book_result;
7. use book_result;
10. -- 创建图书类别浏览量表
11. CREATE TABLE t_books_type_borrow_count(
12.         type_name        varchar(100) not null,
13.         borrow_count        int not null
14. )DEFAULT CHARSET='utf8';

复制代码

2.通过sqoop将mysql中的业务数据导入到大数据平台（hive）

在hive中创建映射的数据库
init_hive.sql

1. create database library;
2. use library;
4. -- 创建用户信息表
6. create table t_user_info (
7.     user_id STRING    comment "用户id",   
8.     user_name STRING  comment "用户姓名",
9.     sex STRING        comment "用户姓名",      
10.     age INT           comment "用户性别"
11. )
12. comment "用户信息表"
13. row format delimited fields terminated by ','
14. stored as textfile;
16. -- 创建图书表
18. create table t_book_info (
19.     book_id STRING    comment "图书id",   
20.     book_name STRING  comment "书名",
21.     type_id STRING    comment "类别id"
22. )
23. comment "图书表"
24. row format delimited fields terminated by ','
25. stored as textfile;
27. -- 创建图书类别表
29. create table dim_books_type (
30.     type_id STRING    comment "类别id",   
31.     type_name STRING  comment "类别名"
32. )
33. comment "图书类别表"
34. row format delimited fields terminated by ','
35. stored as textfile;
37. -- 创建借书表
39. create table t_borrow_order (
40.     user_id STRING     comment "用户id",   
41.     book_id STRING     comment "图书id",   
42.     create_time STRING comment "创建时间"
43. )
44. comment "借书表"
45. row format delimited fields terminated by ','
46. stored as textfile;

复制代码

source /opt/sql/library/init_hive.sql;

 导入mysql数据到hive中

此次mysql与hive中的表名都雷同
sqoop import \
                                     虚拟机      端口号 mysql中的数据库名
--connect jdbc:mysql://bigdata004:3306/mall \  
--username root \
--password root123 \
           mysql中的表名
--table t_user_info \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
导入dim_books_type

2. sqoop import \
3. --connect jdbc:mysql://bigdata004:3306/library \
4. --username root \
5. --password root123 \
6. --table dim_books_type \
7. --num-mappers 1 \
8. --hive-import \
9. --fields-terminated-by "," \
10. --hive-overwrite \
11. --hive-table library.dim_books_type

复制代码

3.通过hive举行数据计算和数据分析 形成数据报表

2. --切换数据库
3. use library;
6. --创建图书类别借阅表
7. --从借书表中获取图书id
8. --从图书表中获取图书id的类别id
9. --从类别表中获取类别名
10. --表中显示 类名 和 该类名的总数（）
13. create table if not exists library.dws_borrow_books_type_count
14. as
15.         select t3.type_name,count(t2.type_id) as borrow_count from (select book_id from t_borrow_order) t1
16.         inner join t_book_info t2 on t1.book_id=t2.book_id
17.         inner join dim_books_type t3 on t2.type_id=t3.type_id
18.         group by t3.type_name;

复制代码

4.再通过sqoop将数据报表导出到mysql 

-- sqoop导出数据到mysql
sqoop export \
                                   主机名                 数据库
--connect jdbc:mysql://bigdata004:3306/result \
--username root \
--password root123 \
                    mysql上的表名
--table  t_city_sale_total \
--num-mappers 1 \
                    hdfs上的数据库位置
--export-dir /user/hive/warehouse/mall_bigdata.db/dws_sale_order_city_total \
--input-fields-terminated-by  "\001"

1. -- sqoop导出数据到mysql
3. sqoop export \
4. --connect jdbc:mysql://bigdata004:3306/book_result \
5. --username root \
6. --password root123 \
7. --table t_books_type_borrow_count \
8. --num-mappers 1 \
9. --export-dir /user/hive/warehouse/library.db/dws_borrow_books_type_count \
10. --input-fields-terminated-by "\001"

复制代码

5.使用FineReport制作数据报表

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。