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:
- 获取数据源
wget
(1)登陆MySQL,建立空数据库
mysql -u root -p
输入密码,默认为“123456”,然后在mysqlshell中创建数据库,如下。
create database coursesel;
(2)导入数据
use coursesel;
source coursesel.sql;
(3)查看数据是否导入
showtables;
- 实验运行环境配置
(1)相关库包下载
python3 -m pip install pymysql
python3 -m pip install thrift
python3 -m pip install hbase-thrift
(2)python连接hbase需要使用thrift,且需要将thrift生成的和覆盖python hbase库对应的文件。
thrift生成的和文件获取方式如下:
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
- 启动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形式,例如“: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.使用创建Client对象,连接上HBase后,创建表结构:
cf1 = ColumnDescriptor(name='stuInfo')
cf2 = ColumnDescriptor(name='Grades')
client.createTable('courseGrade',[cf1, cf2])
使用ColumnDescripto方法描述了一个列族,第一个参数为列族名,还可以增加其他参数,比如设置最大保存版本数maxVersions。使用createTable方法创建表,第一个参数为表名,第二个为列族列表。
4.接下来向列族中插入数据,使用mutateRow方法插入一个逻辑行,对应多个列:
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