Hadoop实验——NoSQL与关系型数据库的比较

云计算 来源:Tiny_16 20℃ 0评论

实验目的

  1. 理解四种数据库(MySQL,HBase,Redis,MongoDB)的概念以及不同点。
  2. 熟练使用四种数据库操作常用的Shell命令。
  3. 熟悉四种数据库操作常用的Java API。

实验平台

  • 操作系统:Ubuntu-16.04
  • Hadoop版本:2.6.0
  • JDK版本:1.8
  • IDE:Eclipse
  • HBase版本:1.2.3
  • MySQL版本:5.7.16
  • MongoDB版本:2.6.10
  • Redis:版本:3.0.6
  • IDE:Eclipse

数据库的安装

  1. MySQL的安装
    1. 更新APT
      sudo apt-get update
    2. 打开终端,安装mysql-server
      sudo apt-get install mysql-server
    3. 输入密码


       
    4. 安装mysql-client
      sudo apt-get install mysql-client

    5. 安装libmysqlclient-dev
      sudo apt-getinstall libmysqlclient-dev
    6. 测试是否安装成功
      sudo netstat -tap | grep mysql
      通过上述命令检查之后,如果看到有mysql 的socket处于 listen 状态则表示安装成功。
  2. Redis的安装
    1. 安装redis-server
      sudo apt-get install redis-server
    2. 测试是否安装成功
      sudo netstat -tap|grep redis
  3. MongoDB的安装
    1. 安装mongodb-server
      sudo apt-get install mongodb-server
    2. 测试是否安装成功
      sudo netstat -tap|grep mongod
  4. HBase已经安装过了(详见http://www.jianshu.com/p/9ac6a4878b07)

实验内容和要求

一,MySQL数据库操作:

student学生表
name English Math Computer
zhangsan 69 86 77
lisi 55 100 88
  1. 根据上面给出的表格,利用MySQL设计出student学生表格。
    1. 登陆MySQL(退出指令为quit)
      mysql -u root -p

      输入密码
    2. 创建数据库
      create database test;

       
    3. 使用数据库
      use test;

       
    4. 创建student表
      create table student(
      name varchar(30) not null,
      English tinyint unsigned not null,
      Math tinyint unsigned not null,
      Computer tinyint unsigned not null
      );

       
    5. 初始化数据
      insert into student values("zhangsan",69,86,77);

       

      insert into student values("lisi",55,100,88);

       
    6. 查看student表
      select * from student;

       
    7. 查看zhangsan的Computer成绩
      select name , Computer from student where name = "zhangsan";

       
    8. 修改lisi的Math成绩,改为95
      update student set Math=95 where name="lisi";

       
  2. 根据上面已经设计出的student表,通过JDBC操作MySQL
    1. 添加数据:Name:scofield English:45 Math:89 Computer:100
      Eclipse的使用
      1. 找到 File 菜单,选择 New -> Java Project

         
      2. 输入 Project name,然后Finish

         
      3. 点开项目,找到 src 文件夹,右键选择 New -> Class

         
      4. 输入 Package 和 Name,然后Finish

         
      5. 将jar包从主机拉到虚拟机中的Home

         
      6. 右键工程,选择 Properties ,然后在工程中导入外部jar包

         


         
      7. 写好Java代码(填上密码),右键选择 Run As -> Java Application,就可以在Console里看到结果了

         

        JAVA代码:
        package com.mysql;
        import java.sql.*;
        public class MysqlTest {
        static final String DRIVER = "com.mysql.jdbc.Driver";
        static final String DB = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        static final String USER = "root";
        static final String PASSWD = "";
        public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
           Class.forName(DRIVER);
           System.out.println("Connecting to a selected database...");
           conn = DriverManager.getConnection(DB, USER, PASSWD);
           stmt = conn.createStatement();
           String sql = "insert into student values('scofield',45,89,100)";
           stmt.executeUpdate(sql);
           System.out.println("Inserting records into the table successfully!");
        } catch (ClassNotFoundException e) {
           e.printStackTrace();
        } catch (SQLException e) {
           e.printStackTrace();
        } finally {
           if (stmt != null)
               try {
                   stmt.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           if (conn != null)
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
        }
        }
        }
      8. 插入数据之后,MySQL客户度查询结果如下

         
    2. 获取scofield的English成绩信息

      JAVA代码:

      package com.mysql;
      import java.sql.*;
      public class MysqlTest2 {
      
      static final String DRIVER = "com.mysql.jdbc.Driver";
      static final String DB = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
      static final String USER = "root";
      static final String PASSWD = "0822";
      
      public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName(DRIVER);
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB, USER, PASSWD);
            stmt = conn.createStatement();
            String sql = "select name,English from student where name='scofield' ";
            rs = stmt.executeQuery(sql);
            System.out.println("name" + "\t\t" + "English");
            while (rs.next()) {
                System.out.print(rs.getString(1) + "\t\t");
                System.out.println(rs.getInt(2));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
      }
      }

      Eclipse控制台输出如下:


       

二,HBase数据库操作:

student学生表
name score:English score:Math score:Computer
zhangsan 69 86 77
lisi 55 100 88
  1. 根据上面给出的表格,用Hbase Shell模式设计student学生表格。
    1. 启动 Hadoop
      • 进入 Hadoop 主文件夹
        cd /usr/local/hadoop/
      • 开启 Hadoop 相关进程
        sbin/start-dfs.sh

         

        sbin/start-yarn.sh

         
    2. 启动 HBase
      • 进入HBase主文件夹
        cd /usr/local/hbase/
      • 开启HBase相关进程
        bin/start-hbase.sh

         
    3. 进入 Hbase Shell(退出指令为quit

       
    4. 创建表student表

       
    5. 初始化student表
      put 'student','zhangsan','score:English','69'
      put 'student','zhangsan','score:Math','86'
      put 'student','zhangsan','score:Computer','77'
      put 'student','lisi','score:English','55'
      put 'student','lisi','score:Math','100'
      put 'student','lisi','score:Computer','88'

       
    6. 查看student表
      scan student

       
    7. 查询zhangsan 的Computer成绩
      get 'student','zhangsan','score:Computer'

       
    8. 修改lisi的Math成绩,改为95
      put 'student','lisi','score:Math','95'

       
  2. 根据上面已经设计出的student表,用Hbase API操作MySQL
    1. 添加数据:Name:scofield English:45 Math:89 Computer:100
      1. 点开项目,找到 src 文件夹,右键选择 New -> Class

         
      2. 输入 Package 和 Name,然后Finish

         
      3. 右键工程,选择 Properties ,然后在工程中导入外部jar包

         

        JAVA代码:
        package com.hbase;
        import java.io.IOException;
        import org.apache.hadoop.conf.Configuration;
        import org.apache.hadoop.hbase.HBaseConfiguration;
        import org.apache.hadoop.hbase.TableName;
        import org.apache.hadoop.hbase.client.Admin;
        import org.apache.hadoop.hbase.client.Connection;
        import org.apache.hadoop.hbase.client.ConnectionFactory;
        import org.apache.hadoop.hbase.client.Put;
        import org.apache.hadoop.hbase.client.Table;
        public class HbaseTest {
        public static Configuration configuration;
        public static Connection connection;
        public static Admin admin;
        public static void main(String[] args) {
        configuration = HBaseConfiguration.create();
        configuration.set("hbase.rootdir", "hdfs://localhost:9000/hbase");
        try {
           connection = ConnectionFactory.createConnection(configuration);
           admin = connection.getAdmin();
        } catch (IOException e) {
           e.printStackTrace();
        }
        try {
           insertRow("student", "scofield", "score", "English", "45");
           insertRow("student", "scofield", "score", "Math", "89");
           insertRow("student", "scofield", "score", "Computer", "100");
        } catch (IOException e) {
           e.printStackTrace();
        }
        close();
        }
        public static void insertRow(String tableName, String rowKey,
           String colFamily, String col, String val) throws IOException {
        Table table = connection.getTable(TableName.valueOf(tableName));
        Put put = new Put(rowKey.getBytes());
        put.addColumn(colFamily.getBytes(), col.getBytes(), val.getBytes());
        table.put(put);
        table.close();
        }
        public static void close() {
        try {
           if (admin != null) {
               admin.close();
           }
           if (null != connection) {
               connection.close();
           }
        } catch (IOException e) {
           e.printStackTrace();
        }
        }
        }
      4. 插入数据之后,HBase Shell查询结果如下

         
    2. 获取scofield的English成绩信息

      JAVA代码:

      package com.mysql;
      import java.sql.*;
      public class MysqlTest2 {
      
      static final String DRIVER = "com.mysql.jdbc.Driver";
      static final String DB = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
      static final String USER = "root";
      static final String PASSWD = "0822";
      
      public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName(DRIVER);
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB, USER, PASSWD);
            stmt = conn.createStatement();
            String sql = "select name,English from student where name='scofield' ";
            rs = stmt.executeQuery(sql);
            System.out.println("name" + "\t\t" + "English");
            while (rs.next()) {
                System.out.print(rs.getString(1) + "\t\t");
                System.out.println(rs.getInt(2));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
      }
      }

      Eclipse控制台输出如下:


       

三,Redis数据库操作:

student 键值对:

zhangsan:{
English: 69
Math: 86
Computer: 77
}
lisi:{
English: 55
Math: 100
Computer: 88
}
  1. 根据上面给出的键值对,利用哈希结构设计出上述表格。(键值可以用student.zhangsan,student.lisi来表示两个键值属于同一个表格)
    1. 启动Redis客户端(退出指令为quit)
      redis-cli

       
    2. 设计上诉表格
      hset student.zhangsan English 69
      hset student.zhangsan Math 86
      hset student.zhangsan Computer 77
      hset student.lisi English 55
      hset student.lisi Math 100
      hset student.lisi Computer 88

       
    3. 输出zhangsan和lisi的信息
      hgetall student.zhangsan

       

      hgetall student.lisi

       
    4. 查看zhangsan的Computer成绩
      hget student.zhangsan Computer

       
    5. 修改lisi的Math成绩,改为95
      hset student.lisi Math 95

       
  2. 根据上面已经设计出的student表,通过jedis操作Redis
    1. 添加数据:
      scofield:{
      English: 45
      Math: 89
      Computer: 100
      }
      1. 点开项目,找到 src 文件夹,右键选择 New -> Class

         
      2. 输入 Package 和 Name,然后Finish

         
      3. 将jar包从主机拉到虚拟机中的Home

         
      4. 右键工程,选择 Properties ,然后在工程中导入外部jar包

         

        JAVA代码:
        package com.redis;
        import java.util.Map;
        import redis.clients.jedis.Jedis;
        public class RedisTest {
        public static void main(String[] args) {
        Jedis jedis = new Jedis("localhost");
        jedis.hset("student.scofield", "English", "45");
        jedis.hset("student.scofield", "Math", "89");
        jedis.hset("student.scofield", "Computer", "100");
        Map<String, String> value = jedis.hgetAll("student.scofield");
        for (Map.Entry<String, String> entry : value.entrySet()) {
           System.out.println(entry.getKey() + ":" + entry.getValue());
        }
        }
        }
      5. 插入数据之后,Redis客户度查询结果如下

         
    2. 获取scofield的English成绩信息
      JAVA代码:
      package com.redis;
      import redis.clients.jedis.Jedis;
      public class RedisTest2 {
      public static void main(String[] args) {
        Jedis jedis = new Jedis("localhost");
        String value = jedis.hget("student.scofield", "English");
        System.out.println("scofield's English score is:    " + value);
      }
      }
      Eclipse控制台输出如下:

       

四,MongoDB数据库操作:

student文档如下:

{
“name”: “zhangsan”,
“score”: {
“English”: 69,
“Math”: 86,
“Computer”: 77
}
}
{
“name”: “lisi”,
“score”: {
“English”: 55,
“Math”: 100,
“Computer”: 88
}
}
  1. 根据上面给出的文档,用Mongo shell设计出student集合
    1. 启动MongoDB客户端(退出指令为quit();)
      mongo

       
    2. 创建student数据库,
      use student

       
    3. 定义数组
      var stus=[
      {"name":"zhangsan","scores":{"English":69,"Math":86,"Computer":77}},        
      {"name":"lisi","score":{"English":55,"Math":100,"Computer":88}} ]

       
    4. 插入到数据库
      db.student.insert(stus)

       
    5. 输出student的信息
      db.student.find().pretty()

       
    6. 查询zhangsan 的所有成绩(只显示score列)
      db.student.find({"name":"zhangsan"},{"_id":0,"name":0})

       
    7. 修改lisi的Math成绩,改为95
      db.student.update({"name":"lisi"}, {"$set":{"score.Math":95}} )

       
  2. 根据上面已经设计出的student集合,通过JDBC操作MongoDB
    1. 添加数据:
      English:45     Math:89    Computer:100
      {
      “name”: “scofield”,
      “score”: {
      “English”: 45,
      “Math”: 89,
      “Computer”: 100
      }
      }
      1. 点开项目,找到 src 文件夹,右键选择 New -> Class

         
      2. 输入 Package 和 Name,然后Finish

         
      3. 将jar包从主机拉到虚拟机中的Home

         
      4. 右键工程,选择 Properties ,然后在工程中导入外部jar包

         

        JAVA代码:
        package com.mongo;
        import java.util.ArrayList;
        import java.util.List;
        import org.bson.Document;
        import com.mongodb.MongoClient;
        import com.mongodb.client.MongoCollection;
        import com.mongodb.client.MongoDatabase;
        public class MongoTest {
        public static void main(String[] args) {
        MongoClient mongoClient = new MongoClient("localhost", 27017);
        MongoDatabase mongoDatabase = mongoClient.getDatabase("student");
        MongoCollection collection = mongoDatabase
               .getCollection("student");
        Document document = new Document("name", "scofield").append(
               "score",
               new Document("English", 45).append("Math", 89).append(
                       "Computer", 100));
        List documents = new ArrayList();
        documents.add(document);
        collection.insertMany(documents);
        System.out.println("文档插入成功");
        }
        }
      5. 插入数据之后,MongoDB客户度查询结果如下

         
    2. 获取scofield的English成绩信息
      JAVA代码:
      package com.mongo;
      import org.bson.Document;
      import com.mongodb.MongoClient;
      import com.mongodb.client.MongoCollection;
      import com.mongodb.client.MongoCursor;
      import com.mongodb.client.MongoDatabase;
      import com.mongodb.client.model.Filters;
      import static com.mongodb.client.model.Filters.eq;
      public class MongoTest2 {
      public static void main(String[] args) {
        MongoClient  mongoClient=new MongoClient("localhost",27017);
        MongoDatabase mongoDatabase = mongoClient.getDatabase("student");
        MongoCollection collection = mongoDatabase.getCollection("student");
        MongoCursor  cursor=collection.find( new Document("name","scofield")).
                projection(new Document("score",1).append("_id", 0)).iterator();
        while(cursor.hasNext())
            System.out.println(cursor.next().toJson());
      }
      }
      Eclipse控制台输出如下: