IDEA+MapReduce+Hive综合实践——搜狗日志查询分析

心已赠人 2024-04-01 19:34 85阅读 0赞

####  ####

#### 1.下载数据源 ####

打开搜狗实验室链接：[搜狗搜索引擎 - 上网从搜狗开始][-]，由于搜狗实验室链接打不开了，所有这里自己制作数据（[阿里云盘分享][Link 1]）进行实验。

**SogouQ.txt:**

![d61b17cf96584e47804984081a5d6fc5.png][]

**2.上传下载文件至HDFS**

2.1将下载的文件通过FinalShell工具上传到Linux系统

![c956191020fc40c9aeda391d7f8c0d1f.png][]

2.2查看SogouQ.txt并上传到HDFS

![50220bc471db4209a3400e3cac64f620.png][]

可以用tail命令查看解压文件最后3行的数据

tail -3 SogouQ.txt

![c62ca8b3832f40239c4ec7b52cb38fb9.png][]

查询词为中文，这里编码按GBK查出来是乱码，编码时指定为‘UTF-8’可避免乱码。数据格式如前面的说明:

访问时间 用户ID \[查询词\] 该URL在返回结果中的排名 用户点击的顺序号 用户点击的URL

上传至HDFS：

hdfs dfs -put SogouQ.txt /

![8196b1627fac477a83c180f1c7344142.png][]

**3. 数据清洗**

因为原始数据中有些行的字段数不为6，且原始数据的字段分隔符不是Hive表规定的逗号','，所以需要对原始数据进行数据清洗。

通过编写MapReduce程序完成数据清洗：（打包运行）

（1）将不满足6个字段的行删除

（2）将字段分隔符由不等的空格变为逗号‘,’分隔符

idea新建Maven工程：MRHiveLog

![285e683a98514d4c9db8aa8361a1ea73.png][]

新建工程目录结构如下：

![46f8a7e0befc4700a671642a76b35452.png][]

修改pom.xml文件

在</project>一行之前添加如下语句

<build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-shade-plugin</artifactId>
                    <version>3.1.0</version>
                    <executions>
                        <execution>
                            <phase>package</phase>
                            <goals>
                                <goal>shade</goal>
                            </goals>
                            <configuration>
                                <transformers>
                                    <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                        
                                        <mainClass>com.wang.App</mainClass>
                                    </transformer>
                                </transformers>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>

添加依赖：在 </dependencies>一行之前添加如下语句:

<dependencies>
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-common</artifactId>
                <version>2.7.3</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-client</artifactId>
                <version>2.7.3</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-hdfs</artifactId>
                <version>2.7.3</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-mapreduce-client-core</artifactId>
                <version>2.7.3</version>
            </dependency>
        </dependencies>

新建SogouMapper类:

package com.wang;
    
    import org.apache.hadoop.io.LongWritable;
    import org.apache.hadoop.io.NullWritable;
    import org.apache.hadoop.io.Text;
    import org.apache.hadoop.mapreduce.Mapper;
     
    import java.io.IOException;
     
    //                                         k1   ,    v1,   k2   ,  v2
    public class SogouMapper extends Mapper<LongWritable,Text,Text,NullWritable> {
     
        @Override
        /**
         * 在任务开始时，被调用一次。且只会被调用一次。
         */
        protected void setup(Context context) throws IOException, InterruptedException {
            super.setup(context);
        }
     
        @Override
        protected void map(LongWritable k1, Text v1, Context context) throws IOException, InterruptedException {
            //避免乱码
            //数据格式：20111230000005  57375476989eea12893c0c3811607bcf 奇艺高清  1  1  http://www.qiyi.com/
            String data = new String(v1.getBytes(),0,v1.getLength(),"utf8");
            
            //split("\\s+") \\s+为正则表达式，意思是匹配一个或多个空白字符，包括空格、制表、换页符等。
            //参考：http://www.runoob.com/java/java-regular-expressions.html
            String words[] = data.split("\\s+");
            
            //判断数据如果不等于6个字段，则退出程序
            if(words.length != 6){
                return;//return语句后不带返回值，作用是退出该程序的运行  https://www.cnblogs.com/paomoopt/p/3746963.html
            }
            //用逗号代替空白字符
            String newData = data.replaceAll("\\s+",",");
            //输出
            context.write(new Text(newData),NullWritable.get());
        }
     
        @Override
        /**
         * 在任务结束时，被调用一次。且只会被调用一次。
         */
        protected void cleanup(Context context) throws IOException, InterruptedException {
            super.cleanup(context);
        }
    }

App.lava

package com.wang;
    
    
    import org.apache.hadoop.conf.Configuration;
    import org.apache.hadoop.fs.Path;
    import org.apache.hadoop.io.NullWritable;
    import org.apache.hadoop.io.Text;
    import org.apache.hadoop.mapreduce.Job;
    import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
    import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
    
    
    /**
     * 数据清洗器 主类
     *
     */
    public class App
    {
        public static void main( String[] args ) throws Exception {
            Configuration conf = new Configuration();
    
            Job job = Job.getInstance(conf);
            job.setJarByClass(App.class);
    
            //指定map输出
            job.setMapperClass(SogouMapper.class);
            job.setMapOutputKeyClass(Text.class);
            job.setMapOutputValueClass(NullWritable.class);
    
            //指定reduce的输出
            job.setOutputKeyClass(Text.class);
            job.setMapOutputValueClass(NullWritable.class);
    
            //指定输入、输出
            FileInputFormat.setInputPaths(job,new Path(args[0]));
            FileOutputFormat.setOutputPath(job,new Path(args[1]));
    
            //提交job，等待结束
            job.waitForCompletion(true);
    
        }
    }

**打包工程:**

![bb1a590831bb4e19b77c967eda0a06c5.png][]

上传到Linux: FinalShell工具:

![19de8c58bb6b4a2eab37a8c9eef99289.png][]

**3.运行jar包**

hadoop jar MRHiveLog-1.0-SNAPSHOT.jar /SogouQ.txt /oneday

![c2a4378fcdf04733a23e02e13ae7d1a0.png][]

查看输出结果

hdfs dfs -ls /oneday

![4f4780e6d01941c48b582c674933bb31.png][]

查看输出文件最后10行数据：

hdfs dfs -tail /oneday/part-r-00000

![0e59a8bb3f054590886dae3f60de8d4f.png][]

进入hive：

![3a46666097bf45558c6dcd3f19266539.png][]

创建表:

create table sogoulog_1(accesstime string,useID string,keyword string,no1 int,clickid int,url string) row format delimited fields terminated by ',';

![05780b82930a456fa31bace453cda24a.png][]

将MapReduce清洗后的数据导入Hive

load data inpath 'Oneday/part-r-00000' into table sogoulog_1;

使用SQL查询满足条件的数据（只显示前10条）

select * from sogoulog_1 where no1=2  limit 10;

![1357083f342f44c386009ab58644490f.png][]

完成。

[-]: http://www.sogou.com/labs/resource/q.php
[Link 1]: https://www.aliyundrive.com/s/9y7rk7AuZWC
[d61b17cf96584e47804984081a5d6fc5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0cfc2e8d4dcc430eb3cd04e8f0c21664.png
[c956191020fc40c9aeda391d7f8c0d1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/85554f00ae3445f7bae859e64be60ff8.png
[50220bc471db4209a3400e3cac64f620.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f98a91aef60b4942b27b5333f1aac922.png
[c62ca8b3832f40239c4ec7b52cb38fb9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/dcead43693ec45d7a117bb5a0d19d475.png
[8196b1627fac477a83c180f1c7344142.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d30c79582fe24181beb95d71e7a88c3a.png
[285e683a98514d4c9db8aa8361a1ea73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d9e902c9ed7c40e0ad504f564a494316.png
[46f8a7e0befc4700a671642a76b35452.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7abeb40ba4bc47209aac9ea0dca1f048.png
[bb1a590831bb4e19b77c967eda0a06c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/57ed8c6ccea743a48e72c42c7501a75e.png
[19de8c58bb6b4a2eab37a8c9eef99289.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/25a54140c7fb468c8d81c60df8bf91bb.png
[c2a4378fcdf04733a23e02e13ae7d1a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3ca7cf8eb38a4bb2bdd5b3b7c78c3b08.png
[4f4780e6d01941c48b582c674933bb31.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5c8991aab29344198e0d4a9a49e3ecef.png
[0e59a8bb3f054590886dae3f60de8d4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/706ed229da5a486bb4ffa77f80ab3382.png
[3a46666097bf45558c6dcd3f19266539.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/eba732686be64e85bc534e9317ce6455.png
[05780b82930a456fa31bace453cda24a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/cd87dbede4124cda99653eb742dad485.png
[1357083f342f44c386009ab58644490f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/23afaec90cf643d5b66b629f820e38b0.png