flask做视频网站贵州建设厅网站在建工程查询

当前位置： 首页 > news >正文

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: 少儿童书 © 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制作数据报表