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

最新推荐文章于 2024-10-22 19:52:28 发布

原创最新推荐文章于 2024-10-22 19:52:28 发布 · 7.5k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#python #import #sql #query #mysql

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

80 篇文章

订阅专栏

本文介绍了一个Python脚本实例，展示了如何通过Thrift库连接Hive，执行添加jar文件和查询操作，并将Hive查询结果更新到MySQL表中的指定字段。实现了从Hive查询数据到MySQL的高效数据流转。

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)