devbox
秋刀鱼在做梦
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

数据洞察：从零到一的数据仓库与Navicat连接全攻略【实训Day04】[完结篇]

作者：秋刀鱼在做梦 | 2024-07-13 01:59:42

赞

踩

数据洞察：从零到一的数据仓库与Navicat连接全攻略【实训Day04】[完结篇]

一、数据分析

1 实现数据仓库(在hadoop101上)

1) 创建jobdata数据库

# cd $HIVE_HOME 
# bin/hive 
hive>create database jobdata;
hive>use jobdata;

2) 创建原始职位数据事实表ods_jobdata_orgin(在hadoop101上)

  1. create table ods_jobdata_origin(
  2. city string COMMENT '城市',
  3. salary array<String> COMMENT '薪资',
  4. company array<String> COMMENT '福利',
  5. kill array<String> COMMENT '技能')
  6. COMMENT '原始职位数据表'
  7. ROW FORMAT DELIMITED 
  8. FIELDS TERMINATED BY ',' 
  9. COLLECTION ITEMS TERMINATED BY '-' 
  10. STORED AS TEXTFILE;

3) 将HDFS上预处理的数据导入到ods_jobdata_orgin(在hadoop101上)

  1. hive>load data inpath '/JobData/output/part-r-00000' overwrite into table ods_jobdata_origin ;
  2. 				
  3. hive>select * from ods_jobdata_origin;

4) 创建职位数据明细表 ods_jobdata_detail(在hadoop101上)

  1. hive>create table ods_jobdata_detail(
  2. 			city string COMMENT '城市',
  3. 			salary array<String> COMMENT '薪资',
  4. 			company array<String> COMMENT '福利',
  5. 			kill array<String> COMMENT '技能',
  6. 			low_salary int COMMENT '低薪资',
  7. 			high_salary int COMMENT '高薪资',
  8. 			avg_salary double COMMENT '平均薪资')
  9. 			COMMENT '职位数据明细表'
  10. 			ROW FORMAT DELIMITED
  11. 			FIELDS TERMINATED BY ','
  12. 			STORED AS TEXTFILE;

5) 向职位数据明细表导入数据

  1. hive>insert overwrite table ods_jobdata_detail
  2. 				select
  3. 				city,salary,company,kill,salary[0],salary[1],(
  4. 				salary[0]+salary[1])/2
  5. 				from ods_jobdata_origin;

6) 创建临时表t_ods_tmp_salary(在hadoop101上)

  1. hive>create table t_ods_tmp_salary as
  2. 				select explode(ojo.salary) from
  3. 				ods_jobdata_origin ojo;

7) 创建工资处理表 t_ods_tmp_salary_dist(在hadoop101上)

  1. hive>create table t_ods_tmp_salary_dist as
  2. 				select case when col>=0 and col<=5 then '0-5'
  3. 				when col>=6 and col<=10 then '6-10'
  4. 				when col>=11 and col<=15 then '11-15'
  5. 				when col>=16 and col<=20 then '16-20'
  6. 				when col>=21 and col<=25 then '21-25'
  7. 				when col>=26 and col<=30 then '26-30'
  8. 				when col>=31 and col<=35 then '31-35'
  9. 				when col>=36 and col<=40 then '36-40'
  10. 				when col>=41 and col<=45 then '41-45'
  11. 				when col>=46 and col<=50 then '46-50'
  12. 				when col>=51 and col<=55 then '51-55'
  13. 				when col>=56 and col<=60 then '56-60'
  14. 				when col>=61 and col<=65 then '61-65'
  15. 				when col>=66 and col<=70 then '66-70'
  16. 				when col>=71 and col<=75 then '71-75'
  17. 				when col>=76 and col<=80 then '76-80'
  18. 				when col>=81 and col<=85 then '81-85'
  19. 				when col>=86 and col<=90 then '86-90'
  20. 				when col>=91 and col<=95 then '91-95'
  21. 				when col>=96 and col<=100 then '96-100'
  22. 				when col>=101 then '>101' end from
  23. 				t_ods_tmp_salary;

8) 创建福利标签临时表t_ods_tmp_company(在hadoop101上)

  1. hive>create table t_ods_tmp_company as
  2. 			select explode(ojo.company)
  3. 			from ods_jobdata_origin ojo;

9) 创建技能标签临时表t_ods_tmp_kill(在hadoop101上)

  1. hive>create table t_ods_tmp_kill as
  2. 			select explode(ojo.kill)
  3. 			from ods_jobdata_origin ojo;

10) 创建技能维度表t_ods_kill(在hadoop101上)

  1. hive>create table t_ods_kill(
  2. 			every_kill String comment '技能标签',
  3. 			count int comment '词频')
  4. 			COMMENT '技能标签词频统计'
  5. 			ROW FORMAT DELIMITED
  6. 			fields terminated by ','
  7. 			STORED AS TEXTFILE;

11)创建福利维度表t_ods_company(在hadoop101上)

  1. hive>create table t_ods_company(
  2. 			every_company String comment '福利标签',
  3. 			count int comment '词频')
  4. 			COMMENT '福利标签词频统计'
  5. 			ROW FORMAT DELIMITED
  6. 			fields terminated by ','
  7. 			STORED AS TEXTFILE;

12)创建城市维度表t_ods_city(在hadoop101上)

  1. hive>create table t_ods_city(
  2. 			every_city String comment '城市',
  3. 			count int comment '词频')
  4. 			COMMENT '城市统计'
  5. 			ROW FORMAT DELIMITED
  6. 			fields terminated by ','
  7. 			STORED AS TEXTFILE;

13) 职位区域分析

  1. hive>insert overwrite table t_ods_city
  2. 			select city,count(1)
  3. 			from ods_jobdata_origin group by city;
  4. 			
  5. hive>select *  from t_ods_city sort by count desc;

14) 创建薪资维度表

  1. hive>create table t_ods_salary(
  2. 			every_partition String comment '薪资分布',
  3. 			count int comment '聚合统计')
  4. 			COMMENT '薪资分布聚合统计'
  5. 			ROW FORMAT DELIMITED
  6. 			fields terminated by ','
  7. 			STORED AS TEXTFILE;

15) 职位薪资分析(全国薪资分布情况)

  1. hive>insert overwrite table t_ods_salary
  2. 			select `_c0`,count(1)
  3. 			from t_ods_tmp_salary_dist group by `_c0`;
  4. 			
  5. hive>select * from t_ods_salary sort by count desc;

16) 职位薪资分析(薪资的平均值、中位数和众数)

  1. hive>select avg(avg_salary) from ods_jobdata_detail;
  2. 			
  3. hive>select avg_salary,count(1) as cnt from ods_jobdata_detail group by avg_salary order by cnt desc limit 1;
  4. 				
  5. hive>select percentile(cast(avg_salary as BIGINT),0.5) from ods_jobdata_detail;

17) 职位薪资分析(各城市平均薪资待遇)

  1. hive>select city,count(city),round(avg(avg_salary),2) as cnt
  2. 				from ods_jobdata_detail
  3. 				group by city order by cnt desc;

18) 公司福利分析

  1. hive>insert overwrite table t_ods_company
  2. 				select col,count(1)
  3. 				from t_ods_tmp_company group by col;
  4. 				
  5. hive>select every_company,count
  6. 				from t_ods_company
  7. 				sort by count desc limit 10;

systemctl status mysqld.service 查看状态

二、连接navicat

           

1.建表

  1. create table t_city_count(
  2. city varchar(30) DEFAULT NULL,
  3. count int(5) DEFAULT NULL)
  4. ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. create table t_salary_dist(
  2. salary varchar(30) DEFAULT NULL,
  3. count int(5) DEFAULT NULL)
  4. ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. create table t_company_count(
  2. company varchar(30) DEFAULT NULL,
  3. count int(5) DEFAULT NULL)
  4. ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. create table t_kill_count(
  2. kills varchar(30) DEFAULT NULL,
  3. count int(5) DEFAULT NULL)
  4. ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.启动sqoop传输文件，把hive数据库传输到mysql数据库

  1. bin/sqoop export \
  2. --connect jdbc:mysql://hadoop101:3306/JobData?characterEncoding=UTF-8 \
  3. --username root \
  4. --password 123456 \
  5. --table t_city_count \
  6. --columns "city,count" \
  7. --fields-terminated-by ',' \
  8. --export-dir /user/hive/warehouse/jobdata.db/t_ods_city;

在这个目录下执行上面的命令

  1. bin/sqoop export \
  2. --connect jdbc:mysql://hadoop101:3306/JobData?characterEncoding=UTF-8 \
  3. --username root \
  4. --password 123456 \
  5. --table t_salary_dist \
  6. --columns "salary,count" \
  7. --fields-terminated-by ',' \
  8. --export-dir /user/hive/warehouse/jobdata.db/t_ods_salary;
  1. bin/sqoop export \
  2. --connect jdbc:mysql://hadoop101:3306/JobData?characterEncoding=UTF-8 \
  3. --username root \
  4. --password 123456 \
  5. --table t_company_count \
  6. --columns "company,count" \
  7. --fields-terminated-by ',' \
  8. --export-dir /user/hive/warehouse/jobdata.db/t_ods_company;

  1. bin/sqoop export \
  2. --connect jdbc:mysql://hadoop101:3306/JobData?characterEncoding=UTF-8 \
  3. --username root \
  4. --password 123456 \
  5. --table t_kill_count \
  6. --columns "kills,count" \
  7. --fields-terminated-by ',' \
  8. --export-dir /user/hive/warehouse/jobdata.db/t_ods_kill;

3.添加依赖

pom.xml添加内容

  1. <dependencies>
  2.     <!--1.阿里云json处理jar包-->
  3.     <dependency>
  4.       <groupId>org.codehaus.jettison</groupId>
  5.       <artifactId>jettison</artifactId>
  6.       <version>1.5.4</version>
  7.     </dependency>
  8.  
  9.     <!--2.spring-context-->
  10.     <dependency>
  11.       <groupId>org.springframework</groupId>
  12.       <artifactId>spring-context</artifactId>
  13.       <version>5.2.5.RELEASE</version>
  14.     </dependency>
  15.  
  16.     <!--3.spring-beans-->
  17.     <dependency>
  18.       <groupId>org.springframework</groupId>
  19.       <artifactId>spring-beans</artifactId>
  20.       <version>5.2.5.RELEASE</version>
  21.     </dependency>
  22.  
  23.     <!--4.spring-webmvc 网站设计-->
  24.     <dependency>
  25.       <groupId>org.springframework</groupId>
  26.       <artifactId>spring-webmvc</artifactId>
  27.       <version>5.2.5.RELEASE</version>
  28.     </dependency>
  29.  
  30.     <!--5.spring-jdbc连接数据库-->
  31.     <dependency>
  32.       <groupId>org.springframework</groupId>
  33.       <artifactId>spring-jdbc</artifactId>
  34.       <version>5.2.5.RELEASE</version>
  35.     </dependency>
  36.  
  37.     <!--6.spring-aspects是AOP切面编程-->
  38.     <dependency>
  39.       <groupId>org.springframework</groupId>
  40.       <artifactId>spring-aspects</artifactId>
  41.       <version>5.2.5.RELEASE</version>
  42.     </dependency>
  43.  
  44.     <!--7.spring-jms异步消息通信-->
  45.     <dependency>
  46.       <groupId>org.springframework</groupId>
  47.       <artifactId>spring-jms</artifactId>
  48.       <version>5.2.5.RELEASE</version>
  49.     </dependency>
  50.  
  51.     <!--8.spring-context-support框架扩展模块-->
  52.     <dependency>
  53.       <groupId>org.springframework</groupId>
  54.       <artifactId>spring-context-support</artifactId>
  55.       <version>5.2.5.RELEASE</version>
  56.     </dependency>
  57.  
  58.     <!--9.mybatis框架-->
  59.     <dependency>
  60.       <groupId>org.mybatis</groupId>
  61.       <artifactId>mybatis</artifactId>
  62.       <version>3.5.1</version>
  63.     </dependency>
  64.  
  65.  
  66.     <!--10.mybatis-spring-->
  67.     <dependency>
  68.       <groupId>org.mybatis</groupId>
  69.       <artifactId>mybatis-spring</artifactId>
  70.       <version>1.3.1</version>
  71.     </dependency>
  72.  
  73.     <!--11.mybatis分页插件-->
  74.     <dependency>
  75.       <groupId>com.github.miemiedev</groupId>
  76.       <artifactId>mybatis-paginator</artifactId>
  77.       <version>1.2.15</version>
  78.     </dependency>
  79.  
  80.     <!--12.mysql驱动模块-->
  81.     <dependency>
  82.       <groupId>mysql</groupId>
  83.       <artifactId>mysql-connector-java</artifactId>
  84.       <version>5.1.9</version>
  85.     </dependency>
  86.  
  87.     <!--13.阿里巴巴数据库连接池-->
  88.     <dependency>
  89.       <groupId>com.alibaba</groupId>
  90.       <artifactId>druid</artifactId>
  91.       <version>1.2.16</version>
  92.       <exclusions>
  93.         <exclusion>
  94.           <groupId>com.alibaba</groupId>
  95.           <artifactId>jconsole</artifactId>
  96.         </exclusion>
  97.         <exclusion>
  98.           <groupId>com.alibaba</groupId>
  99.           <artifactId>tools</artifactId>
  100.         </exclusion>
  101.       </exclusions>
  102.     </dependency>
  103.  
  104.     <!--14.jsp的标准标签库-->
  105.     <dependency>
  106.       <groupId>javax.servlet</groupId>
  107.       <artifactId>jstl</artifactId>
  108.       <version>1.2</version>
  109.     </dependency>
  110.  
  111.     <!--15.servlet的标准API-->
  112.     <dependency>
  113.       <groupId>javax.servlet</groupId>
  114.       <artifactId>servlet-api</artifactId>
  115.       <version>2.5</version>
  116.     </dependency>
  117.  
  118.     <!--16.jsp的标准API-->
  119.     <dependency>
  120.       <groupId>javax.servlet.jsp</groupId>
  121.       <artifactId>jsp-api</artifactId>
  122.       <version>2.1</version>
  123.     </dependency>
  124.  
  125.     <!--17.测试包-->
  126.     <dependency>
  127.       <groupId>junit</groupId>
  128.       <artifactId>junit</artifactId>
  129.       <version>4.12</version>
  130.       <scope>test</scope>
  131.     </dependency>
  132.     <!--18.处理JSON数据类型-->
  133.     <dependency>
  134.       <groupId>com.fasterxml.jackson.core</groupId>
  135.       <artifactId>jackson-databind</artifactId>
  136.       <version>2.10.1</version>
  137.     </dependency>
  138.  
  139.     <!--19.面向切面编程的功能模块-->
  140.     <dependency>
  141.       <groupId>org.aspectj</groupId>
  142.       <artifactId>aspectjweaver</artifactId>
  143.       <version>1.9.22</version>
  144.     </dependency>
  145.   </dependencies>
  1. <build>
  2.     <finalName>JobData-Web</finalName>
  3.     <!--资源搜索设置-->
  4.     <resources>
  5.       <!--1.搜索java文件夹中类型-->
  6.       <resource>
  7.         <directory>src/main/java</directory>
  8.         <includes>
  9.           <include>**/*.properties</include>
  10.           <include>**/*.xml</include>
  11.         </includes>
  12.         <filtering>false</filtering>
  13.       </resource>
  14.       <!--2.搜索resources文件夹的类型-->
  15.       <resource>
  16.         <directory>src/main/resources</directory>
  17.         <includes>
  18.           <include>**/*.properties</include>
  19.           <include>**/*.xml</include>
  20.         </includes>
  21.       </resource>
  22.     </resources>
  23.  
  24.     <!--插件管理-->
  25.     <plugins>
  26.       <plugin>
  27.         <groupId>org.apache.maven.plugins</groupId>
  28.         <artifactId>maven-compiler-plugin</artifactId>
  29.         <version>3.1</version>
  30.         <configuration>
  31.           <source>1.8</source>
  32.           <target>1.8</target>
  33.           <encoding>UTF-8</encoding>
  34.         </configuration>
  35.       </plugin>
  36.     </plugins>
  37.  
  38.   </build>

4.db.properties添加内容

  1. jdbc.driver=com.mysql.jdbc.Driver
  2. jdbc.url=jdbc:mysql://hadoop101:3306/JobData?characterEncoding=utf8
  3. jdbc.username=root
  4. jdbc.password=123456

5.echarts-view.js添加内容

6.下载tomcata

将其放在根盘符下,解压缩tomcat到其目录E:\apache-tomcat-8.5.61

7.重启tomcat

删除上方红框内容

8.查看报错内容

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'cityServiceImpl': Unsatisfied dependency expressed through field 'mapper'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'cn.aust.mapper.CityMapper' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}

出现该问题解决方案

最终效果展示：

Day04:完结~

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/817117
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号