利用python将hive查询结果保存到mysql

最新推荐文章于 2024-06-02 20:23:59 发布

jiedushi

最新推荐文章于 2024-06-02 20:23:59 发布

阅读量7.4k

点赞数

分类专栏： hadoop+hive+hbase 文章标签： python import sql query mysql

本文链接：https://blog.csdn.net/jiedushi/article/details/6578261

版权

hadoop+hive+hbase 专栏收录该内容

80 篇文章 1 订阅

订阅专栏

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)
     for item in clinet.fetchAll()
	   update_sql= " update  " + tableName + " set " + column + " = " + item + " where id = '" + date + "'"

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

	   transport.close()

       except Thrift.TException, tx:

	   print '%s' % (tx.message)