Extracting MySQL to Hbase data

A simple and crude way to extract data from a MySQL database to the HBase implementation process:

Rowkey using the primary key id attribute of MySQL table as the ID of HBase

Code

####

#!/usr/bin/env python

#coding =utf-8

Import Sys

Reload (SYS)

Sys.setdefaultencoding (' Utf-8 ')

Import MySQLdb

Import Datetime,time

Sys.path.append ('/usr/lib/python2.6/site-packages/hbase ')

From Thrift Import Thrift

From Thrift.transport import Tsocket

From Thrift.transport import Ttransport

From Thrift.protocol import Tbinaryprotocol

From HBase import HBase

From hbase.ttypes Import *

Import CSV

From hbase.ttypes import Columndescriptor, Mutation, Batchmutation, Tregioninfo

From hbase.ttypes import IOError, alreadyexists

Def client_conn ():

Transport=tsocket.tsocket ("172.16.10.87", 9090)

Transport=ttransport.tbufferedtransport (transport)

Protocol=tbinaryprotocol.tbinaryprotocol (transport)

Client=hbase.client (Protocol)

Transport.open ()

Return client

If __name__== "__main__":

Client=client_conn ()

conn = MySQLdb.connect (host= "172.161.110.10", user= "Dlan", passwd= "root123", port=5029, db= ' Coolqi ', charset= ' UTF8 ')

cur = conn.cursor ()

Sql= "SELECT * from Ca_record where ca_time>=str_to_date (' 20170720 ', '%y%m%d ')"

Print SQL

Cur.execute (SQL)

Data=cur.fetchall ()

For K in Xrange (len (data)):

Datalist2=[]

ROWKEY=DATA[K][0]

Print RowKey

USER_ID=DATA[K][1]

CA_RESULT=DATA[K][2]

CA_TIME=DATA[K][3]

REAL_NAME=DATA[K][4]

ID_CARD=DATA[K][5]

SEX=DATA[K][6]

Datalist=[user_id,ca_result,ca_time,real_name,id_card,sex]

datalist1=["user_id", "Ca_result", "Ca_time", "Real_name", "Id_card", "sex"]

For j in Range (Len (DataList)):

Args=str (Datalist[j])

#print args

If Isinstance (ARGS,STR):

Mutations= "[Mutation (column=" + "'" +datalist1[j]+ ': ' +str (j) + "'" + "," + "value=" + "'" +str (args) + "')" + "]"

Datalist2.append (Mutations)

#print Datalist2

Client.mutaterow (' Ca_record ', str (rowKey), [Mutation (column=datalist1[j]+ ': ' +str (j), Value=str (args))])

#client. Mutaterows (' Ca_record ', datalist2)

# # #在for K in xrange (len data): You can use the enumerate () function to reduce the loop above.

This article is from the "Dbaspace" blog, make sure to keep this source http://dbaspace.blog.51cto.com/6873717/1950007

Extracting MySQL to Hbase data