mongodb学习Hadoop玩转大数据

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

2016-12-08  本文已影响2649人  Tiny_16

实验目的

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

实验平台

数据库的安装

  1. MySQL的安装

  2. 更新APT
    sudo apt-get update

  3. 打开终端，安装mysql-server
    sudo apt-get install mysql-server

  4. 输入密码


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

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

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

  8. Redis的安装

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

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

  11. MongoDB的安装

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

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

  14. 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);
  2. 查看student表
    select * from student;
  3. 查看zhangsan的Computer成绩
    select name , Computer from student where name = "zhangsan";
  4. 修改lisi的Math成绩,改为95
    update student set Math=95 where name="lisi";
  5. 根据上面已经设计出的student表,通过JDBC操作MySQL
  6. 添加数据: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
  3. 启动 HBase
    • 进入HBase主文件夹
      cd /usr/local/hbase/
    • 开启HBase相关进程
      bin/start-hbase.sh
  4. 进入 Hbase Shell（退出指令为quit）

  5. 创建表student表


  6. 初始化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
  2. 查看zhangsan的Computer成绩
    hget student.zhangsan Computer
  3. 修改lisi的Math成绩,改为95
    hset student.lisi Math 95
  4. 根据上面已经设计出的student表,通过jedis操作Redis
  5. 添加数据:
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控制台输出如下:


上一篇下一篇

猜你喜欢

热点阅读