大数据清洗流程

 

首先将result.txt文件放到到虚拟机

然后在dbeaver创建数据库连接，根据hive中一个jar包创建的数据库连接。

**创建数据库**

create database journal;

**切换到数据库**

use journal
--建立初始表

create table data(
`ip` string comment "城市",
`time` string comment "时间",
`day` string comment "天数",
`traffic` double comment "流量",
`type` string comment "类型 视频/文章",
`id` string comment "视频或者文章的id"
)
row format delimited
fields terminated by ','
lines terminated by '\n';


/*

这是SQL语句中用于定义CSV文件格式的语句。

• row format delimited：表示行格式是分隔的，即每一行的数据由多个字段组成，字段之间用特定的字符进行分隔。
• fields terminated by ','：表示字段之间使用逗号作为分隔符。
• lines terminated by ' '：表示行之间使用换行符作为分隔符。

综合起来，这段语句的意思是将数据按照逗号分隔的格式存储为CSV文件，每行数据以换行符结束

*/

 

--导入数据

load data local inpath '/home/hadoop/result.txt' into table data;

-- 数据清洗，将 10/Nov/2016:00:01:02 +0800 修改为 2016-11-10 00:01:03 形式

create table newdata as
select ip,
date_format(from_unixtime(unix_timestamp(`time`,'dd/MMM/yyyy:HH:mm:ss Z'), 'yyyy-MM-dd HH:mm:ss'),
'yyyy-MM-dd HH:mm:ss') as `time`,
day,
traffic,
type,
id
from data;

/*

• select: 这是SQL查询的关键字，用于指定要检索的数据。
• ip: 这是要从数据表中选择的一列，可能表示IP地址。
• date_format(from_unixtime(unix_timestamp(time,'dd/MMM/yyyy:HH:mm:ss Z'), 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') as time: 这是将"time"列转换为日期格式的函数。首先，使用unix_timestamp函数将"time"列中的字符串转换为Unix时间戳（以秒为单位），然后使用from_unixtime函数将Unix时间戳转换为日期时间格式。最后，使用date_format`函数将日期时间格式化为'yyyy-MM-dd HH:mm:ss'的形式。
• as time``: 这是给转换后的日期时间列起一个别名，方便后续引用。
• day, traffic, type, id: 这是从数据表中选择的其他列，分别表示天、流量、类型和ID。
• from data: 这是指定要从哪个表中检索数据的关键字，这里是"data"表

*/

```sql
--统计最受欢迎的视频/文章的Top10访问次数

CREATE TABLE top_visits AS
SELECT type, id, COUNT(*) AS visit_count
FROM newdata n
GROUP BY type, id
ORDER BY visit_count DESC
LIMIT 10;

**按照地市统计最受欢迎的Top10课程**

CREATE TABLE top_courses_by_city AS
SELECT ip , type, id, COUNT(*) AS visit_count
FROM newdata
GROUP BY ip, type, id
ORDER BY visit_count DESC
LIMIT 10;

*按照流量统计最受欢迎的Top10课程**

CREATE TABLE top_courses_by_traffic AS
SELECT type, id, SUM(traffic) AS total_traffic
FROM newdata
GROUP BY type, id
ORDER BY total_traffic DESC
LIMIT 10;

## 数据导入到mysql

然后在navicat创建数据库连接，ssh通过数据库服务器地址建立的连接。

**在mysql中创建数据库**

create database journal

**统计最受欢迎的视频/文章的Top10访问次数**

CREATE TABLE `top_visits` (
`type` varchar(20) DEFAULT NULL,
`id` varchar(20) DEFAULT NULL,
`visit_count` int(11) DEFAULT NULL
)

/*
  在Navicat中，DEFAULT NULL表示该字段的默认值为NULL。当插入新记录时，如果没有为该字段提供值，系统会自动将其设置为NULL。
*/

**导出，将hive数据库中的清洗结果导出至mysql**

**即dbeaver数据库中的清洗结果导出至navicat**

```在finalshell输入命令

bin/sqoop export \
--connect jdbc:mysql://node1:3306/journal \
--username root \
--password 123456 \
--table top_visits \
--export-dir /user/hive/warehouse/journal.db/top_visits --input-fields-terminated-by '\001' \
--columns 'type,id,visit_count'

 

**按照地市统计最受欢迎的Top10课程**

CREATE TABLE `top_courses_by_city` (
`ip` VARCHAR(255),
`type` VARCHAR(255),
`id` VARCHAR(255),
`visit_count` BIGINT
);

**导出**

bin/sqoop export \
--connect jdbc:mysql://node1:3306/journal \
--username root \
--password 123456 \
--table top_courses_by_city \
--export-dir /user/hive/warehouse/journal.db/top_courses_by_city --input-fields-terminated-by '\001' \
--columns 'ip,type,id,visit_count'

 

*按照流量统计最受欢迎的Top10课程**

**创建表**

CREATE TABLE `top_courses_by_traffic` (
`type` VARCHAR(255),
`id` VARCHAR(255),
`total_traffic` DOUBLE
);

 

**导出**

bin/sqoop export \
--connect jdbc:mysql://node1:3306/journal \
--username root \
--password 123456 \
--table top_courses_by_traffic \
--export-dir /user/hive/warehouse/journal.db/top_courses_by_traffic --input-fields-terminated-by '\001' \
--columns 'type,id,total_traffic'

/*

这是一个使用Sqoop工具从MySQL数据库导出数据的命令。具体解释如下：

1. bin/sqoop export: 表示执行Sqoop的导出操作。
2. --connect jdbc:mysql://node1:3306/journal: 指定要连接的MySQL数据库，其中node1是数据库服务器地址，3306是端口号，journal是要连接的数据库名。
3. --username root: 指定用于连接数据库的用户名，这里是root。
4. --password 123456: 指定用于连接数据库的密码，这里是123456。
5. --table top_courses_by_traffic: 指定要导出的表名，这里是top_courses_by_traffic。
6. --export-dir /user/hive/warehouse/journal.db/top_courses_by_traffic: 指定导出数据的存储路径，这里是Hive的HDFS目录/user/hive/warehouse/journal.db/top_courses_by_traffic。
7. --input-fields-terminated-by '\001': 指定输入字段的分隔符，这里是\001（ASCII码为1的字符）。
8. --columns 'type,id,total_traffic': 指定要导出的列，这里是type、id和total_traffic。　　*/