flask做视频网站,松江做网站多少钱,网络营销运营策划,跨境电商选品目录
1.数据仓库的数据来源为业务数据库#xff08;mysql#xff09; 初始化脚本 init_book_result.sql
2.通过sqoop将mysql中的业务数据导入到大数据平台#xff08;hive#xff09; 导入mysql数据到hive中
3.通过hive进行数据计算和数据分析 形成数据报表
4.再通过sq…目录
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 CHARSETutf8;-- 创建图书表
CREATE TABLE t_book_info(book_id varchar(100) not null,book_name varchar(100) not null,type_id varchar(100) not null
)DEFAULT CHARSETutf8;-- 创建图书类别表
CREATE TABLE dim_books_type(type_id varchar(100) not null,type_name varchar(100) not null
)DEFAULT CHARSETutf8;-- 创建借书表
CREATE TABLE t_borrow_order(user_id varchar(100) not null,book_id varchar(100) not null,create_time varchar(100) not null
)DEFAULT CHARSETutf8;-- 用户信息表插入数据
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 CHARSETutf8;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_idt2.book_idinner join dim_books_type t3 on t2.type_idt3.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 \0015.使用FineReport制作数据报表
