[7] 编写Python脚本将Hive的运算结果保存到MySQL数据库中(1) 摩西莫西 ITeye技术网站

[7] 编写Python脚本将Hive的运算结果保存到MySQL数据库中(1) - 摩西莫西 - ITeye技术网站

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

博客分类：
Hive
脚本 MySQL Python SQL
编写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)