林子雨编著《大数据基础编程、实验和案例教程(第2版)》教材第13章的代码

大数据学习路线图

林子雨编著《大数据基础编程、实验和案例教程(第2版)》(教材官网)教材中的命令行和代码,在纸质教材中的印刷效果不是很好,可能会影响读者对命令行和代码的理解,为了方便读者正确理解命令行和代码或者直接拷贝命令行和代码用于上机实验,这里提供全书配套的所有命令行和代码。
查看教材所有章节的代码

第13章 大数据课程综合实验案例

教材第202页

(温馨提示:代码框上方的复制代码按钮,也就是“两张A4纸图标”,用鼠标点击复制代码按钮,就可以把代码框中的代码复制到粘贴板,粘贴到其他地方。但是,有的浏览器可能不支持该功能)

cd /home/hadoop/下载
ls
cd /usr/local
ls
sudo mkdir bigdatacase
#这里会提示你输入当前用户(本教程是hadoop用户名)的密码
#下面给hadoop用户赋予针对bigdatacase目录的各种操作权限
sudo chown -R hadoop:hadoop ./bigdatacase
cd bigdatacase
#下面创建一个dataset目录,用于保存数据集
mkdir dataset
#下面就可以解压缩user.zip文件
cd ~  //表示进入hadoop用户的目录
cd 下载
ls
unzip user.zip -d /usr/local/bigdatacase/dataset
cd /usr/local/bigdatacase/dataset
ls

教材第203页

head -5 raw_user.csv
cd /usr/local/bigdatacase/dataset
#下面删除raw_user中的第1行
sed -i '1d' raw_user.csv
#上面的1d表示删除第1行,同理,3d表示删除第3行,nd表示删除第n行
#下面删除small_user中的第1行
sed -i '1d' small_user.csv
#下面再用head命令去查看文件的前5行记录,就看不到字段名称这一行了
head -5 raw_user.csv
head -5 small_user.csv

教材第204页

cd /usr/local/bigdatacase/dataset
vim pre_deal.sh
#!/bin/bash
#下面设置输入文件,把用户执行pre_deal.sh命令时提供的第一个参数作为输入文件名称
infile=$1
#下面设置输出文件,把用户执行pre_deal.sh命令时提供的第二个参数作为输出文件名称
outfile=$2
#注意,最后的$infile> $outfile必须跟在}’这两个字符的后面
awk -F "," 'BEGIN{
srand();
        id=0;
        Province[0]="山东";Province[1]="山西";Province[2]="河南";Province[3]="河北";Province[4]="陕西";Province[5]="内蒙古";Province[6]="上海市";
        Province[7]="北京市";Province[8]="重庆市";Province[9]="天津市";Province[10]="福建";Province[11]="广东";Province[12]="广西";Province[13]="云南"; 
        Province[14]="浙江";Province[15]="贵州";Province[16]="新疆";Province[17]="西藏";Province[18]="江西";Province[19]="湖南";Province[20]="湖北";
        Province[21]="黑龙江";Province[22]="吉林";Province[23]="辽宁"; Province[24]="江苏";Province[25]="甘肃";Province[26]="青海";Province[27]="四川";
        Province[28]="安徽"; Province[29]="宁夏";Province[30]="海南";Province[31]="香港";Province[32]="澳门";Province[33]="台湾";
    }
    {
        id=id+1;
        value=int(rand()*34);       
        print id"\t"$1"\t"$2"\t"$3"\t"$5"\t"substr($6,1,10)"\t"Province[value]
    }' $infile> $outfile

教材第205页

cd /usr/local/bigdatacase/dataset
bash ./pre_deal.sh small_user.csv user_table.txt
head -10 user_table.txt

教材第206页

cd /usr/local/hadoop
./sbin/start-dfs.sh
jps
cd /usr/local/hadoop
./bin/hdfs dfs -mkdir -p /bigdatacase/dataset

教材第207页

cd /usr/local/hadoop
./bin/hdfs dfs -put /usr/local/bigdatacase/dataset/user_table.txt /bigdatacase/dataset
cd /usr/local/hadoop
./bin/hdfs dfs -cat /bigdatacase/dataset/user_table.txt | head -10
service mysql start  #可以在Linux的任何目录下执行该命令
cd /usr/local/hive
./bin/hive   #启动Hive
hive> create database dblab;
hive> use dblab;

教材第208页

““hive
hive> CREATE EXTERNAL TABLE dblab.bigdata_user(id INT,uid STRING,item_id STRING,behavior_type INT,item_category STRING,visit_date DATE,province STRING) COMMENT 'Welcome to xmudblab!' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE LOCATION '/bigdatacase/dataset';

<pre><code><br />““hive
hive> use dblab; //使用dblab数据库
hive> show tables; //显示数据库中所有表
hive> show create table bigdata_user; //查看bigdata_user表的各种属性;

教材第209页

““hive
hive> desc bigdata_user;

<pre><code><br />““hive
hive> select * from bigdata_user limit 10;
hive> select behavior_type from bigdata_user limit 10;

““hive
hive> select behavior_type from bigdata_user limit 10; #查看前10位用户对商品的行为

<pre><code>### 教材第210页
““hive
hive> select visit_date, item_category from bigdata_user limit 20;

““hive
hive> select e.bh, e.it from (select behavior_type as bh, item_category as it from bigdata_user) as e limit 20;

<pre><code>### 教材第211页
““hive
hive> select count(*) from bigdata_user;

教材第212页

““hive
hive> select count(distinct uid) from bigdata_user;

<pre><code><br />““hive
hive>select count(*) from (select uid,item_id,behavior_type,item_category,visit_date,province from bigdata_user group by uid,item_id,behavior_type,item_category,visit_date,province having count(*)=1)a;

教材第213页

““hive
hive> select count(*) from bigdata_user where behavior_type='1' and visit_date<'2014-12-13' and visit_date>'2014-12-10';

<pre><code><br />““hive
hive> select count(distinct uid), day(visit_date) from bigdata_user where behavior_type=’4′ group by day(visit_date);

教材第214页

““hive
hive> select count(*) from bigdata_user where province='江西' and visit_date='2014-12-12' and behavior_type='4';

<pre><code><br />““hive
hive> select count(*) from bigdata_user where visit_date=’2014-12-11’and behavior_type=’4′;#查询有多少用户在2014-12-11购买了商品

““hive
hive> select count(*) from bigdata_user where visit_date ='2014-12-11';#查询有多少用户在2014-12-11点击了该店

<pre><code>### 教材第215页
““hive
hive> select count(*) from bigdata_user where uid=10001082 and visit_date=’2014-12-12′;#查询用户10001082在2014-12-12点击网站的次数

““hive
hive> select count(*) from bigdata_user where visit_date='2014-12-12';#查询所有用户在这一天点击该网站的次数

<pre><code><br />““hive
hive> select uid from bigdata_user where behavior_type=’4′ and visit_date=’2014-12-12′ group by uid having count(behavior_type=’4′)>5;#查询某一天在该网站购买商品超过5次的用户id

““hive
hive> create table scan(province STRING,scan INT) COMMENT 'This is the search of bigdataday' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;#创建新的数据表进行存储
hive> insert overwrite table scan select province,count(behavior_type) from bigdata_user where behavior_type='1' group by province;#导入数据
hive> select * from scan;#显示结果

<pre><code>### 教材第216页
““hive
hive> create table dblab.user_action(id STRING,uid STRING, item_id STRING, behavior_type STRING, item_category STRING, visit_date DATE, province STRING) COMMENT ‘Welcome to XMU dblab! ‘ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

cd /usr/local/hadoop
./bin/hdfs dfs -ls /user/hive/warehouse/dblab.db/

教材第217页

““hive
hive> INSERT OVERWRITE TABLE dblab.user_action select * from dblab.bigdata_user;

<pre><code><br />““hive
hive> select * from user_action limit 10;

mysql -u root -p 

教材第218页

mysql> show databases; #显示所有数据库
mysql> create database dblab; #创建dblab数据库
mysql> use dblab; #使用数据库
mysql>show variables like "char%";

教材第219页

mysql> CREATE TABLE `dblab`.`user_action` (`id` varchar(50),`uid` varchar(50),`item_id` varchar(50),`behavior_type` varchar(10),`item_category` varchar(50), `visit_date` DATE,`province` varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> exit;
<property>
        <name>hadoop.proxyuser.hadoop.hosts</name>
        <value>*</value>
</property>
<property>
        <name>hadoop.proxyuser.hadoop.groups</name>
        <value>*</value>
</property>
cd /usr/local/hive
./bin/hive --service hiveserver2 -hiveconf hive.server2.thrift.port=10000
sudo netstat -anp|grep 10000

教材第220页

import java.sql.*;
import java.sql.SQLException;

public class HivetoMySQL {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String driverName_mysql = "com.mysql.jdbc.Driver";
    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        }catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.exit(1);
        }
        Connection con1 = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hive", "hive");//后两个参数是用户名密码

        if(con1 == null)
            System.out.println("连接失败");
        else {
            Statement stmt = con1.createStatement();
            String sql = "select * from dblab.user_action";
            System.out.println("Running: " + sql);
            ResultSet res = stmt.executeQuery(sql);

            //InsertToMysql
            try {
                Class.forName(driverName_mysql);
                Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/dblab","root","root");
                String sql2 = "insert into user_action(id,uid,item_id,behavior_type,item_category,visit_date,province) values (?,?,?,?,?,?,?)";
                PreparedStatement ps = con2.prepareStatement(sql2);
                while (res.next()) {
                    ps.setString(1,res.getString(1));
                    ps.setString(2,res.getString(2));
                    ps.setString(3,res.getString(3));
                    ps.setString(4,res.getString(4));
                    ps.setString(5,res.getString(5));
                    ps.setDate(6,res.getDate(6));
                    ps.setString(7,res.getString(7));
                    ps.executeUpdate();
                }
                ps.close();
                con2.close();
                res.close();
                stmt.close();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        con1.close();
    }
}

教材第221页

mysql -u root -p

教材第222页

mysql> use dblab;
mysql> select * from user_action limit 10;
cd /usr/local/hadoop
./sbin/start-all.sh
cd /usr/local/hbase
./bin/start-hbase.sh

教材第223页

cd /usr/local/bigdatacase/dataset
/usr/local/hadoop/bin/hdfs dfs -get /user/hive/warehouse/dblab.db/user_action .
 #将HDFS上的user_action数据复制到本地当前目录,注意'.'表示当前目录
cat ./user_action/* | head -10   #查看前10行数据
cat ./user_action/00000* > user_action.output #将00000*文件复制一份重命名为user_action.output,*表示通配符
head user_action.output  #查看user_action.output前10行
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List; 
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.*;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes; 
public class ImportHBase extends Thread {
    public Configuration config;
    public Connection conn;
    public Table table;
    public Admin admin;
    public ImportHBase() {
        config = HBaseConfiguration.create();
//      config.set("hbase.master", "master:60000");
//      config.set("hbase.zookeeper.quorum", "master");
        try {
            conn = ConnectionFactory.createConnection(config);
            admin = conn.getAdmin();
            table = conn.getTable(TableName.valueOf("user_action"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    } 
    public static void main(String[] args) throws Exception {
        if (args.length == 0) {       //第一个参数是该jar所使用的类,第二个参数是数据集所存放的路径
            throw new Exception("You must set input path!");
        }
        String fileName = args[args.length-1];  //输入的文件路径是最后一个参数
        ImportHBase test = new ImportHBase();
        test.importLocalFileToHBase(fileName);
    }
    public void importLocalFileToHBase(String fileName) {
        long st = System.currentTimeMillis();
        BufferedReader br = null;
        try {
            br = new BufferedReader(new InputStreamReader(new FileInputStream(
                    fileName)));
            String line = null;
            int count = 0;
            while ((line = br.readLine()) != null) {
                count++;
                put(line);
                if (count % 10000 == 0)
                    System.out.println(count);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally { 
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                table.close(); // must close the client
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        long en2 = System.currentTimeMillis();
        System.out.println("Total Time: " + (en2 - st) + " ms");
    }
    @SuppressWarnings("deprecation")
    public void put(String line) throws IOException {
        String[] arr = line.split("\t", -1);
        String[] column = {"id","uid","item_id","behavior_type","item_category","date","province"};

        if (arr.length == 7) {
            Put put = new Put(Bytes.toBytes(arr[0]));// rowkey
            for(int i=1;i<arr.length;i++){
                put.addColumn(Bytes.toBytes("f1"), Bytes.toBytes(column[i]),Bytes.toBytes(arr[i]));
            }
            table.put(put); // put to server
        }
    }
    public void get(String rowkey, String columnFamily, String column,
            int versions) throws IOException {
        long st = System.currentTimeMillis();
        Get get = new Get(Bytes.toBytes(rowkey));
        get.addColumn(Bytes.toBytes(columnFamily), Bytes.toBytes(column));
        Scan scanner = new Scan(get);
        scanner.readVersions(versions);
        ResultScanner rsScanner = table.getScanner(scanner);
        for (Result result : rsScanner) {
            final List<Cell> list = result.listCells();
            for (final Cell kv : list) {
                System.out.println(Bytes.toStringBinary(kv.getValueArray()) + "\t"
                        + kv.getTimestamp()); // mid + time
            }
        }
        rsScanner.close();
        long en2 = System.currentTimeMillis();
        System.out.println("Total Time: " + (en2 - st) + " ms");
    }
}

教材第页226

hbase> truncate 'user_action'
/usr/local/hadoop/bin/hadoop jar /usr/local/bigdatacase/hbase/ImportHBase.jar ImportHBase /usr/local/bigdatacase/dataset/user_action.output

教材第227页

habse> scan 'user_action',{LIMIT=>10}  #只查询前面10行

教材第229页

sudo vim /etc/apt/sources.list
deb http://mirrors.aliyun.com/ubuntu/ xenial main restricted universe multiverse  
deb http://mirrors.aliyun.com/ubuntu/ xenial-security main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ xenial-updates main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ xenial-backports main restricted universe multiverse

教材第230页

sudo apt-get update
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 51716619E084DAB9
sudo apt-get install r-base
R
>q()
> install.packages('RMySQL')
sudo apt-get install libmariadb-client-lgpl-dev
R

教材第231页

> install.packages('RMySQL')

教材第232页

> install.packages('ggplot2')
> install.packages('devtools')
sudo apt-get install libssl-dev
sudo apt-get install libssh2-1-dev
sudo apt-get install libcurl4-openssl-dev
sudo apt-get install libxml2-dev
> devtools::install_github('taiyun/recharts')
>?sort

教材第233页

service mysql start
mysql -u root -p
mysql> use dblab;
mysql> select * from user_action limit 10;
>library(RMySQL)
>conn <- dbConnect(MySQL(),dbname='dblab',username='root',password='hadoop',host="127.0.0.1",port=3306)
>user_action <- dbGetQuery(conn,'select * from user_action')

教材第234页

>summary(user_action$behavior_type)
>summary(as.numeric(user_action$behavior_type))
>library(ggplot2)
>ggplot(user_action,aes(as.numeric(behavior_type)))+geom_histogram()
>temp <- subset(user_action,as.numeric(behavior_type)==4) # 获取子数据集
>count <- sort(table(temp$item_category),decreasing = T) #排序
>print(count[1:10]) # 获取第1到10个排序结果

教材第235页

>month <- substr(user_action$visit_date,6,7)  # visit_date变量中截取月份
>user_action <- cbind(user_action,month)  # user_action增加一列月份数据
>ggplot(user_action,aes(as.numeric(behavior_type),col=factor(month)))+geom_histogram()+facet_grid(.~month)

教材第236页

>library(recharts)
>rel <- as.data.frame(table(temp$province))
>provinces <- rel$Var1
>x = c()
>for(n in provinces){
>x[length(x)+1] = nrow(subset(temp,(province==n)))
>}
>mapData <- data.frame(province=rel$Var1,count=x, stringsAsFactors=F) # 设置地图信息
>eMap(mapData, namevar=~province, datavar = ~count) #画出中国地图