[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)
页:
[1]