目錄
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
????????
-- 設置sql_mode
set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';-- 創建數據庫library
create database library;-- 切換數據庫
use library;-- 創建用戶信息表
CREATE TABLE t_user_info(user_id varchar(100) not null,user_name varchar(100) not null,sex varchar(10) not null,age int not null
)DEFAULT CHARSET='utf8';-- 創建圖書表
CREATE TABLE t_book_info(book_id varchar(100) not null,book_name varchar(100) not null,type_id varchar(100) not null
)DEFAULT CHARSET='utf8';-- 創建圖書類別表
CREATE TABLE dim_books_type(type_id varchar(100) not null,type_name varchar(100) not null
)DEFAULT CHARSET='utf8';-- 創建借書表
CREATE TABLE t_borrow_order(user_id varchar(100) not null,book_id varchar(100) not null,create_time varchar(100) not null
)DEFAULT CHARSET='utf8';-- 用戶信息表插入數據
insert into t_user_info values ('114', '王小名', '男', 22);
insert into t_user_info values ('115', '張美麗', '女', 27);
insert into t_user_info values ('116', '李華', '男', 30);
insert into t_user_info values ('117', '陳曉紅', '女', 35);
insert into t_user_info values ('118', '趙偉', '男', 24);
insert into t_user_info values ('119', '周小燕', '女', 29);
insert into t_user_info values ('120', '吳強', '男', 33);
insert into t_user_info values ('121', '鄭麗', '女', 26);
insert into t_user_info values ('122', '王剛', '男', 28);
insert into t_user_info values ('123', '黃芳', '女', 31);
insert into t_user_info values ('124', '劉濤', '男', 36);
insert into t_user_info values ('125', '楊靜', '女', 25);
insert into t_user_info values ('126', '張杰', '男', 32);
insert into t_user_info values ('127', '陳莉', '女', 23);
insert into t_user_info values ('128', '趙勇', '男', 38);
insert into t_user_info values ('129', '周敏', '女', 30);
insert into t_user_info values ('130', '吳浩', '男', 29);
insert into t_user_info values ('131', '鄭薇', '女', 34);
insert into t_user_info values ('132', '王林', '男', 27);
insert into t_user_info values ('133', '黃梅', '女', 26);
insert into t_user_info values ('134', '劉軍', '男', 37);
insert into t_user_info values ('135', '楊慧', '女', 25);
insert into t_user_info values ('136', '張明', '男', 33);
insert into t_user_info values ('137', '陳霞', '女', 31);
insert into t_user_info values ('138', '趙海', '男', 28);
insert into t_user_info values ('139', '周蘭', '女', 36);
insert into t_user_info values ('140', '吳飛', '男', 24);
insert into t_user_info values ('141', '鄭敏', '女', 27);
insert into t_user_info values ('142', '王磊', '男', 35);
insert into t_user_info values ('143', '黃蓉', '女', 30);
insert into t_user_info values ('144', '劉麗', '女', 26);
insert into t_user_info values ('145', '楊勇', '男', 32);
insert into t_user_info values ('146', '張華', '男', 29);
insert into t_user_info values ('147', '陳敏', '女', 34);
insert into t_user_info values ('148', '趙麗', '女', 31);
insert into t_user_info values ('149', '周剛', '男', 25);
insert into t_user_info values ('150', '吳麗', '女', 37);
insert into t_user_info values ('151', '鄭強', '男', 28);
insert into t_user_info values ('152', '王芳', '女', 33);
insert into t_user_info values ('153', '黃偉', '男', 27);
insert into t_user_info values ('154', '劉靜', '女', 36);
insert into t_user_info values ('155', '楊華', '男', 24);
insert into t_user_info values ('156', '張敏', '女', 31);
insert into t_user_info values ('157', '陳軍', '男', 30);
insert into t_user_info values ('158', '趙敏', '女', 26);
insert into t_user_info values ('159', '周強', '男', 35);
insert into t_user_info values ('160', '吳紅', '女', 32);
insert into t_user_info values ('161', '鄭麗', '女', 29);
insert into t_user_info values ('162', '王偉', '男', 34);
insert into t_user_info values ('163', '黃靜', '女', 31);
insert into t_user_info values ('164', '劉濤', '男', 25);
insert into t_user_info values ('165', '楊敏', '女', 37);
insert into t_user_info values ('166', '張勇', '男', 28);
insert into t_user_info values ('167', '陳芳', '女', 33);
insert into t_user_info values ('168', '趙軍', '男', 27);
insert into t_user_info values ('169', '周麗', '女', 36);
insert into t_user_info values ('170', '吳華', '男', 24);commit;-- 圖書表插入數據
-- Book Type: 散文隨筆 (a)
insert into t_book_info values ('a001', '珠江潮汐美', 'a');
insert into t_book_info values ('a002', '晨曦的低語', 'a');
insert into t_book_info values ('a003', '山間小路', 'a');
insert into t_book_info values ('a004', '秋葉之歌', 'a');
insert into t_book_info values ('a005', '夜色中的琴聲', 'a');
insert into t_book_info values ('a006', '城市邊緣的詩人', 'a');
insert into t_book_info values ('a007', '冬日里的暖陽', 'a');
insert into t_book_info values ('a008', '海邊的沉思', 'a');
-- book type: 世界名著 (b)
insert into t_book_info values ('b001', '悲慘世界', 'b');
insert into t_book_info values ('b002', '百年孤獨', 'b');
insert into t_book_info values ('b003', '雙城記', 'b');
insert into t_book_info values ('b004', '戰爭與和平', 'b');
insert into t_book_info values ('b005', '簡愛', 'b');
insert into t_book_info values ('b006', '飄', 'b');
insert into t_book_info values ('b007', '堂吉訶德', 'b');
insert into t_book_info values ('b008', '呼嘯山莊', 'b');
-- book type: 少兒童書 (c)
insert into t_book_info values ('c001', '小王子的星球', 'c');
insert into t_book_info values ('c002', '魔法森林的秘密', 'c');
insert into t_book_info values ('c003', '海底兩萬里', 'c');
insert into t_book_info values ('c004', '勇敢的小火車頭', 'c');
insert into t_book_info values ('c005', '神奇的種子', 'c');
insert into t_book_info values ('c006', '月亮上的兔子', 'c');
insert into t_book_info values ('c007', '彩虹橋下的秘密', 'c');
insert into t_book_info values ('c008', '會說話的石頭', 'c');
-- book type: 歷史小說 (d)
insert into t_book_info values ('d001', '三國演義', 'd');
insert into t_book_info values ('d002', '水滸傳', 'd');
insert into t_book_info values ('d003', '大明王朝', 'd');
insert into t_book_info values ('d004', '清朝末年', 'd');
insert into t_book_info values ('d005', '大唐盛世', 'd');
insert into t_book_info values ('d006', '宋朝風云', 'd');
insert into t_book_info values ('d007', '明朝那些事兒', 'd');
insert into t_book_info values ('d008', '清朝宮廷秘史', 'd');
-- book type: 國學入門 (e)
insert into t_book_info values ('e001', '論語解讀', 'e');
insert into t_book_info values ('e002', '道德經注釋', 'e');
insert into t_book_info values ('e003', '易經初探', 'e');
insert into t_book_info values ('e004', '詩經選讀', 'e');
insert into t_book_info values ('e005', '孟子精講', 'e');
insert into t_book_info values ('e006', '莊子心解', 'e');
insert into t_book_info values ('e007', '大學中庸', 'e');
insert into t_book_info values ('e008', '孝經新解', 'e');commit;-- 圖書類別表插入數據
insert into dim_books_type values('a','散文隨筆');
insert into dim_books_type values('b','世界名著');
insert into dim_books_type values('c','少兒童書');
insert into dim_books_type values('d','歷史小說');
insert into dim_books_type values('e','國學入門');commit;-- 借書表插入數據
insert into t_borrow_order values('114','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('115','e002','2022-11-08 09:23:54');
insert into t_borrow_order values('114','b003','2022-11-08 09:23:54');
insert into t_borrow_order values('116','d002','2022-11-08 09:23:54');
insert into t_borrow_order values('114','c001','2022-11-08 09:23:54');
insert into t_borrow_order values('115','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('117','b004','2022-11-08 09:23:54');
insert into t_borrow_order values('118','a007','2022-11-08 09:23:54');
insert into t_borrow_order values('118','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('119','e003','2022-11-08 09:23:54');
insert into t_borrow_order values('119','d001','2022-11-08 09:23:54');
insert into t_borrow_order values('120','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('120','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('121','d005','2022-11-08 09:23:54');
insert into t_borrow_order values('123','b006','2022-11-08 09:23:54');
insert into t_borrow_order values('124','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('125','e004','2022-11-08 09:23:54');
insert into t_borrow_order values('126','b002','2022-11-08 09:23:54');
insert into t_borrow_order values('127','a003','2022-11-08 09:23:54');
insert into t_borrow_order values('124','d002','2022-11-08 09:23:54');
insert into t_borrow_order values('122','b001','2022-11-08 09:23:54');
insert into t_borrow_order values('128','a001','2022-11-08 09:23:54');
insert into t_borrow_order values('129','a006','2022-11-08 09:23:54');
insert into t_borrow_order values('125','d008','2022-11-08 09:23:54');
insert into t_borrow_order values('123','e008','2022-11-08 09:23:54');
insert into t_borrow_order values('120','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('130','b007','2022-11-08 09:23:54');
insert into t_borrow_order values('131','a007','2022-11-08 09:23:54');
insert into t_borrow_order values('132','a008','2022-11-08 09:23:54');
insert into t_borrow_order values('133','e004','2022-11-08 09:23:54');
insert into t_borrow_order values('135','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('133','d003','2022-11-08 09:23:54');
insert into t_borrow_order values('136','a003','2022-11-08 09:23:54');
insert into t_borrow_order values('138','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('139','d001','2022-11-08 09:23:54');
insert into t_borrow_order values('133','e001','2022-11-08 09:23:54');
insert into t_borrow_order values('133','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('132','e002','2022-11-08 09:23:54');
insert into t_borrow_order values('131','a003','2022-11-08 09:23:54');
insert into t_borrow_order values('141','b003','2022-11-08 09:23:54');
insert into t_borrow_order values('140','e003','2022-11-08 09:23:54');
insert into t_borrow_order values('142','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('142','d005','2022-11-08 09:23:54');
insert into t_borrow_order values('146','b006','2022-11-08 09:23:54');
insert into t_borrow_order values('144','d006','2022-11-08 09:23:54');
insert into t_borrow_order values('148','d007','2022-11-08 09:23:54');
insert into t_borrow_order values('144','e003','2022-11-08 09:23:54');
insert into t_borrow_order values('142','a003','2022-11-08 09:23:54');
insert into t_borrow_order values('143','e003','2022-11-08 09:23:54');
insert into t_borrow_order values('149','d004','2022-11-08 09:23:54');
insert into t_borrow_order values('150','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('151','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('151','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('154','d003','2022-11-08 09:23:54');
insert into t_borrow_order values('153','d002','2022-11-08 09:23:54');
insert into t_borrow_order values('156','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('155','d003','2022-11-08 09:23:54');
insert into t_borrow_order values('157','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('158','d005','2022-11-08 09:23:54');
insert into t_borrow_order values('159','a005','2022-11-08 09:23:54');
insert into t_borrow_order values('154','c006','2022-11-08 09:23:54');
insert into t_borrow_order values('153','d007','2022-11-08 09:23:54');
insert into t_borrow_order values('152','c004','2022-11-08 09:23:54');
insert into t_borrow_order values('154','a004','2022-11-08 09:23:54');
insert into t_borrow_order values('151','d003','2022-11-08 09:23:54');
insert into t_borrow_order values('152','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('162','c003','2022-11-08 09:23:54');
insert into t_borrow_order values('161','a001','2022-11-08 09:23:54');
insert into t_borrow_order values('166','d002','2022-11-08 09:23:54');
insert into t_borrow_order values('163','a002','2022-11-08 09:23:54');
insert into t_borrow_order values('167','c003','2022-11-08 09:23:54');
insert into t_borrow_order values('169','a005','2022-11-08 09:23:54');
commit;
???init_book_result.sql
-- 設置sql_mode
set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';-- 創建數據庫result,并進行切換
create database book_result;use book_result;-- 創建圖書類別瀏覽量表
CREATE TABLE t_books_type_borrow_count(type_name varchar(100) not null,borrow_count int not null
)DEFAULT CHARSET='utf8';
2.通過sqoop將mysql中的業務數據導入到大數據平臺(hive)
在hive中建立映射的數據庫
init_hive.sql
create database library;
use library;-- 創建用戶信息表create table t_user_info (user_id STRING comment "用戶id", user_name STRING comment "用戶姓名", sex STRING comment "用戶姓名", age INT comment "用戶性別"
)
comment "用戶信息表"
row format delimited fields terminated by ','
stored as textfile;-- 創建圖書表create table t_book_info (book_id STRING comment "圖書id", book_name STRING comment "書名", type_id STRING comment "類別id"
)
comment "圖書表"
row format delimited fields terminated by ','
stored as textfile;-- 創建圖書類別表create table dim_books_type (type_id STRING comment "類別id", type_name STRING comment "類別名"
)
comment "圖書類別表"
row format delimited fields terminated by ','
stored as textfile;-- 創建借書表create table t_borrow_order (user_id STRING comment "用戶id", book_id STRING comment "圖書id", create_time STRING comment "創建時間"
)
comment "借書表"
row format delimited fields terminated by ','
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
sqoop import \
--connect jdbc:mysql://bigdata004:3306/library \
--username root \
--password root123 \
--table dim_books_type \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "," \
--hive-overwrite \
--hive-table library.dim_books_type
3.通過hive進行數據計算和數據分析 形成數據報表
--切換數據庫
use library;--創建圖書類別借閱表
--從借書表中獲取圖書id
--從圖書表中獲取圖書id的類別id
--從類別表中獲取類別名
--表中顯示 類名 和 該類名的總數()create table if not exists library.dws_borrow_books_type_count
asselect t3.type_name,count(t2.type_id) as borrow_count from (select book_id from t_borrow_order) t1inner join t_book_info t2 on t1.book_id=t2.book_idinner join dim_books_type t3 on t2.type_id=t3.type_idgroup 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"
-- sqoop導出數據到mysqlsqoop export \
--connect jdbc:mysql://bigdata004:3306/book_result \
--username root \
--password root123 \
--table t_books_type_borrow_count \
--num-mappers 1 \
--export-dir /user/hive/warehouse/library.db/dws_borrow_books_type_count \
--input-fields-terminated-by "\001"