利用Python将Hive查询结果保存到MySQL

三棱球

2011-08-24

Python脚本连接hive获取返回值代码

#!/usr/bin/env python
import sys
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
try:
transport = TSocket.TSocket('localhost', 10000)
transport = TTransport.TBufferedTransport(transport)
protocol = TBinaryProtocol.TBinaryProtocol(transport)
client = ThriftHive.Client(protocol)
transport.open()
client.execute('ADD jar /home/soft/Hadoop/hive-0.7.0/lib/hive-contrib-0.7.0.jar')
query = '''
select count(1) from apilog '''
client.execute(query)
row = client.fetchOne()
print row
transport.close()
except Thrift.TException, tx:
print '%s' % (tx.message)

此脚本支持add jar/file

用户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)