python实现mysql数据导入HBase

• 原理

以学生课程成绩为例，将实体和实体间的关系都存在MySQL数据库中，实体有学生和课程，分别对应学生信息表studentInfo和课程信息表courseInfo，实体之间的关系为选课及成绩，对应成绩表gradeInfo，这三张表的结构如图所示。

如果还是以三张表的形式存储数据到HBase中并没有任何意义，因为HBase有列族的概念，可以将三张表的数据整合到HBase的一张表中，HBase中表的逻辑结构如图所示。

HBase表将MySQL三张表的数据聚合到一张表中，studentInfo表映射到HBase的StuInfo列族，gradeInfo和courseInfo表信息映射到Grades列族中，使用HBase列族形式将数据整合到一起，查询起来更加方便，同时对出现大量空值的场景，可以节约大量的存储空间。

• 所需环境

OS：Ubuntu16.04
Python3
Mysql
MongoDB：v3.6

• 准备：源数据准备以及实验环境配置

1. 获取数据源

wget http://10.90.3.2/HUP/NoSQL/WinQSB/coursesel.sql

（1）登陆MySQL，建立空数据库

mysql -u root -p

输入密码，默认为“123456”，然后在mysqlshell中创建数据库，如下。

create database coursesel;

（2）导入数据

use coursesel;
source coursesel.sql;

（3）查看数据是否导入

show tables;

2. 实验运行环境配置

（1）相关库包下载

python3 -m pip install pymysql
python3 -m pip install thrift
python3 -m pip install hbase-thrift

（2）python连接hbase需要使用thrift，且需要将thrift生成的hbase.py和ttypes.py覆盖python hbase库对应的文件。

thrift生成的hbase.py和ttypes.py文件获取方式如下：

wget http://10.90.3.2/HUP/NoSQL/WinQSB/Hbase.py
wget http://10.90.3.2/HUP/NoSQL/WinQSB/ttypes.py

然后使用以下命令，替换hbase库中的这两个文件，使用命令如下：

cp Hbase.py /home/ubuntu/.local/lib/python3.5/site-packages/hbase/Hbase.py
cp ttypes.py /home/ubuntu/.local/lib/python3.5/site-packages/hbase/ttypes.py

3. 启动hbase以及thrift服务器

（1）启动hadoop

cd /opt/hadoop/sbin/  
hadoop namenode -format
./start-all.sh

（2）启动hbase

cd /opt/hbase-1.2.6/bin/
./start-hbase.sh

（3）启动thrift服务器

./hbase-daemon.sh start thrift

一：读取MySQL数据

1．Python连接MySQL需要用到pymysql库包，

import pymysql

如果提示找不到，输入pip3 install pymysql 安装第三方库

2．编写操作MySQL代码

pymysql.connect("localhost", "root", "", "courseSel")

其中第一个参数localhost为本地数据库，如果远程数据库可使用ip:port形式，例如“10.100.9.33：3306”。接下来两个参数分别为连接数据库的用户名和密码，最后一个为用到的具体数据库名。
因为本例中一个学生可能选择了多门课程，所以在插入HBase时，需针对单个学生的所有选课信息进行操作，因此要先从studentInfo表中获取学生的基本信息，代码如下：

cursor.execute("SELECT * FROM studentInfo")

stuInfo = cursor.fetchall()

fatchall方法获取查询的结果，返回的sutInfo为list结构，存储多行数据。然后针对每个学生从gradeInfo和courseInfo表中获取课程信息，如下所示：

for row in stuInfo:

    #根据学号查询该学生所选课程的相关信息

    sqlCourse = "SELECT courseInfo.课程名,gradeInfo.成绩 " \

                "FROM studentInfo,courseInfo,GradeInfo " \

                "WHERE studentInfo.学号=GradeInfo.学号 " \

                "and courseInfo.课程号=GradeInfo.课程号 and studentInfo.学号='%d'" %(id)

    cursor1.execute(sqlCourse)

    courses = cursor1.fetchall()

经过此查询后可以获取每个学生的选课信息和成绩，显示结果如下：
学生信息：(1, ‘张俊’, 20, 1)
选课：((‘大数据导论’, 87), (‘NoSQL原理’, 90), (‘python’, 89))
学生信息：(2, ‘李莉’, 19, 0)
选课：((‘NoSQL原理’, 92), (‘python’, 90))
学生信息：(3, ‘王琦’, 18, 0)
选课：((‘NoSQL原理’, 88), (‘python’, 70))
学生信息：(4, ‘赵岸’, 19, 1)
选课：((‘大数据导论’, 88), (‘python’, 90))

二：插入HBase

1．Python连接HBase需要使用到thrift服务，下载安装并启动后，在python中import相应的库包：

from thrift.transport import TSocket

from hbase import Hbase

from hbase.ttypes import *

2．导入需要的库后，进行HBase的连接，以下代码显示了连接HBase数据库以及创建表：

transport = TSocket.TSocket('host', 9090)

protocol = TBinaryProtocol.TBinaryProtocol(transport)

client = Hbase.Client(protocol)

transport.open()

其中Tsocket方法中第一个参数host为HBase服务器地址，9090为HBase启动的默认端口号。

3．使用HBase.Client创建Client对象，连接上HBase后，创建表结构：

#定义列族

cf1 = ColumnDescriptor(name='stuInfo')

cf2 = ColumnDescriptor(name='Grades')

client.createTable('courseGrade', [cf1, cf2])

使用ColumnDescripto方法描述了一个列族，第一个参数为列族名，还可以增加其他参数，比如设置最大保存版本数maxVersions。使用createTable方法创建表，第一个参数为表名，第二个为列族列表。

4．接下来向列族中插入数据，使用mutateRow方法插入一个逻辑行，对应多个列：

#插入HBase courseGrade表的stuInfo列族

mutations = [Mutation(column="stuInfo:name", value = name),

            Mutation(column="stuInfo:age", value = str(age)),

            Mutation(column="stuInfo:sex", value = str(sex))]

client.mutateRow('courseGrade', str(id), mutations)

mutateRow方法第一个参数为文本类型的表名，第二个参数为文本类型的行键，第三个参数为文本类型的列值列表，后面还可以设置json格式的可选属性。同样的方式将学生的选课信息插入Grades列族：

mutations = [Mutation(column="Grades:'%s'"%(courseName), value=str(score))]

client.mutateRow('courseGrade', str(id), mutations)

Grades列族中以courseName为列名，成绩score为具体单元格的值。

5．查询数据
获取某个学生所选课程的成绩，以下示例表示获取学号为1的学生的所有选课信息：

client.getRow('courseGrade','1')

client.get('courseGrade','1', ‘StuInfo:name’)

get和getRow方法必须设定表名和行键，第一个参数为表名，第二参数为行键，HBase中所有数据类型均为字符型。getRow方法只能获取一个逻辑行的数据，并且必须指定行键，因此如果想根据学生姓名获取学生的选课信息可以使用scan方法：

scan = TScan()
scan.columns =['stuInfo']
afilter = "valueFilter(=,'substring:李莉')"
scan.filterString = afilter
scanner =client.scannerOpenWithScan("courseGrade",scan,None)
result = client.scannerGetList(scanner,4)

附代码

wget http://10.90.3.2/HUP/NoSQL/WinQSB/hbase_py.py