[7] 编写Python脚本将Hive的运算结果保存到MySQL数据库中(1)

 

编写Python脚本将Hive的运算结果保存到MySQL数据库中(1)

很多情况下，需要将Hive中的运算结果保存到MySQL数据库中，可以通过简单的Python脚本来实现。

例子1：如果获取Hive查询语句的返回值

#encoding=utf-8

from hive_service import ThriftHive

from hive_service.ttypes import HiveServerException

from thrift import Thrift

from thrift.transport import TSocket

from thrift.transport import TTransport

from thrift.protocol import TBinaryProtocol

def hiveExe(sql):

try:

   transport = TSocket.TSocket('10.20.134.199', 10000) //Hive所在的服务器IP和使用的端口

   transport = TTransport.TBufferedTransport(transport)

   protocol = TBinaryProtocol.TBinaryProtocol(transport)

   client = ThriftHive.Client(protocol)

   transport.open()

   client.execute(sql)

   print "The return value is : " client.fetchOne() 

   transport.close()

except Thrift.TException, tx:

   print '%s' % (tx.message)

if __name__ == '__main__':

print hiveExe("select count(userid) as cnt from user_table where day ='20110120' ")

例子2：用户Hive查询结果的返回值更新MySQL指定表指定字段

def mysqlExe(sql):

        conn = MySQLdb.connect (host = "10.10.111.111",

                                   user = "user",

                                   passwd = "password",

                                   db = "database")

        cursor = conn.cursor ()

        cursor.execute (sql)

        cursor.close ()

        conn.close ()

def hiveExeUpdate(sql,db,tableName,column,date):

try:

   transport = TSocket.TSocket('10.20.134.199', 10000)

   transport = TTransport.TBufferedTransport(transport)

   protocol = TBinaryProtocol.TBinaryProtocol(transport)

   client = ThriftHive.Client(protocol)

   transport.open()

   client.execute(sql)

   update_sql= " update  " + tableName + " set " + column + " = " + client.fetchOne() + " where id = '" + date + "'"

   mysqlExe(update_sql) //执行一条SQL语句

   transport.close()

       except Thrift.TException, tx:

   print '%s' % (tx.message)