NoSQLHadoop实验——NoSQL与关系型数据库的可比

试行目的

  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的安装

  2. 更新APT
    sudo apt-get update

  3. 开拓终端,安装mysql-server
    sudo apt-get install mysql-server

  4. 输入密码

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

  2. 安装libmysqlclient-dev
    sudo apt-get install libmysqlclient-dev

  3. 测试是否安装成功
    sudo netstat -tap | grep mysql
    经过上述命令检查之后,如果见到有mysql 的socket处于 listen
    状态则表示安装成功。

  4. ##### Redis的安装

  5. 安装redis-server
    sudo apt-get install redis-server

  6. 测试是否安装成功
    sudo netstat -tap|grep redis

  7. ##### MongoDB的安装

  8. 安装mongodb-server
    sudo apt-get install mongodb-server

  9. 测试是否安装成功
    sudo netstat -tap|grep mongod

  10. ##### HBase已经设置过了(详见http://www.jianshu.com/p/9ac6a4878b07

试行内容和须求

一,MySQL数据库操作:

<div align = center>student学生表 </div>

name English Math Computer
zhangsan 69 86 77
lisi 55 100 88
  1. ##### 依照地点给出的报表,利用MySQL设计出student学生表格。

  2. 登陆MySQL(退出指令为quit)
    mysql -u root -p

    输入密码

  3. 创办数据库
    create database test;

  4. 利用数据库
    use test;

  5. 创建student表

create table student(
    name varchar(30) not null,
    English tinyint unsigned not null,
    Math tinyint unsigned not null,
    Computer tinyint unsigned not null
    );
  1. 先导化数据
    insert into student values("zhangsan",69,86,77);
`insert into student values("lisi",55,100,88);`  
  1. 查看student表
    select * from student;

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

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

  4. ##### 根据上边已经筹划出的student表,通过JDBC操作MySQL

  5. ###### 添加数据:Name:scofield English:45 Math:89 Computer:100

Eclipse的使用

  1. 找到 File 菜单,选择 New -> Java Project
  1. 输入 Project name,然后Finish
  1. 点开项目,找到 src 文件夹,右键采取 New -> Class
  1. 输入 Package 和 Name,然后Finish
  1. 将jar包从主机拉到虚拟机中的Home
  1. 右键工程,选取 Properties ,然后在工程中导入外部jar包
  1. 写好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();
               }
       }
   }
}

  1. 插入数据之后,MySQL客户度查询结果如下
  1. ###### 获取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数据库操作:

<div align = center>student学生表 </div>

name score:English score:Math score:Computer
zhangsan 69 86 77
lisi 55 100 88
  1. ##### 依据上边给出的报表,用Hbase Shell方式设计student学生表格。

  2. 启动 Hadoop

    • 进去 Hadoop 主文件夹
      cd /usr/local/hadoop/
    • 拉开 Hadoop 相关进程
      sbin/start-dfs.sh
    `sbin/start-yarn.sh`  
  1. 启动 HBase
    • 进去HBase主文件夹
      cd /usr/local/hbase/
    • 打开HBase相关进度
      bin/start-hbase.sh
  2. 进入 Hbase Shell(退出指令为quit

  3. 创建表student表

  1. 初始化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'
  1. 查看student表
    scan 'student'

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

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

  4. ##### 按照地点已经安顿出的student表,用Hbase API操作MySQL

  5. ###### 添加数据: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();
       }
   }
}

  1. 插入数据之后,HBase Shell查询结果如下
  1. ###### 获取scofield的English成绩新闻

JAVA代码:

package com.hbase;
import java.io.IOException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.CellUtil;
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.Get;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.Table;
public class HbaseTest2 {
    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 {
            getData("student", "scofield", "score", "English");
        } catch (IOException e) {
            e.printStackTrace();
        }
        close();
    }

    public static void getData(String tableName, String rowKey,
            String colFamily, String col) throws IOException {
        Table table = connection.getTable(TableName.valueOf(tableName));
        Get get = new Get(rowKey.getBytes());
        get.addColumn(colFamily.getBytes(), col.getBytes());
        Result result = table.get(get);
        showCell(result);
        table.close();
    }

    public static void showCell(Result result) {
        Cell[] cells = result.rawCells();
        for (Cell cell : cells) {
            System.out.println("RowName:" + new String(CellUtil.cloneRow(cell))
                    + " ");
            System.out.println("Timetamp:" + cell.getTimestamp() + " ");
            System.out.println("column Family:"
                    + new String(CellUtil.cloneFamily(cell)) + " ");
            System.out.println("row Name:"
                    + new String(CellUtil.cloneQualifier(cell)) + " ");
            System.out.println("value:" + new String(CellUtil.cloneValue(cell))
                    + " ");
        }
    }

    public static void close() {
        try {
            if (admin != null) {
                admin.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (IOException 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来表示三个键值属于同一个报表)

  2. 启航Redis客户端(退出指令为quit)
    redis-cli

  3. 安插上述表格

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
  1. 输出zhangsan和lisi的信息
    hgetall student.zhangsan
`hgetall student.lisi`  
  1. 查看zhangsan的Computer成绩
    hget student.zhangsan Computer

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

  3. ##### 根据上面已经安顿出的student表,通过jedis操作Redis

  4. ###### 添加数据:

scofield:{
English: 45
Math: 89
Computer: 100
}

  1. 点开项目,找到 src 文件夹,右键选择 New -> Class
  1. 输入 Package 和 Name,然后Finish
  1. 将jar包从主机拉到虚拟机中的Home
  1. 右键工程,选用 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());
       }
   }
}

  1. 插入数据之后,Redis客户度查询结果如下
  1. ###### 获取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集合

  2. 初始MongoDB客户端(退出指令为quit();)
    mongo

  3. 创建student数据库,
    use student

  4. 概念数组

var stus=[
      {"name":"zhangsan","scores":{"English":69,"Math":86,"Computer":77}},        
      {"name":"lisi","score":{"English":55,"Math":100,"Computer":88}} ]
  1. 插入到数据库
    db.student.insert(stus)

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

  3. 询问zhangsan 的拥有战表(只突显score列)
    db.student.find({"name":"zhangsan"},{"_id":0,"name":0})

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

  5. ##### 按照上边已经规划出的student集合,通过JDBC操作MongoDB

  6. ###### 添加数据:

English:45  Math:89 Computer:100
{
“name”: “scofield”,
“score”: {
“English”: 45,
“Math”: 89,
“Computer”: 100
}
}

  1. 点开项目,找到 src 文件夹,右键选择 New -> Class
  1. 输入 Package 和 Name,然后Finish
  1. 将jar包从主机拉到虚拟机中的Home
  1. 右键工程,采用 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<Document> collection = mongoDatabase
               .getCollection("student");
       Document document = new Document("name", "scofield").append(
               "score",
               new Document("English", 45).append("Math", 89).append(
                       "Computer", 100));
       List<Document> documents = new ArrayList<Document>();
       documents.add(document);
       collection.insertMany(documents);
       System.out.println("文档插入成功");
   }
}

  1. 插入数据之后,MongoDB客户度查询结果如下
  1. ###### 获取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<Document> collection = mongoDatabase.getCollection("student");
        MongoCursor<Document>  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控制台出口如下:

网站地图xml地图