pyhs2连接hive

84 阅读 0 评论 56 点赞

我是靠谱客的博主可靠小懒虫，最近开发中收集的这篇文章主要介绍pyhs2连接hive，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

  pip install pyhs2 

--------------------代码如下：

#!/usr/bin/env python

  # -*- coding: utf-8 -*- 

  # hive util with hiveserver2 

  import pyhs2 

  from pyhs2.error import Pyhs2Exception 

  import sys 

  from file_util import fileUtil 

  from config_util import config 

  default_encoding = 'utf-8' 

  if sys.getdefaultencoding() != default_encoding: 

  reload(sys) 

  sys.setdefaultencoding(default_encoding) 

  class hiveUtil: 

  def __init__(self): 

  self.conn = pyhs2.connect(host=config.hive_host, 

  port=config.hive_port, 

  authMechanism=config.hive_auth, 

  user=config.hive_user, 

  password=config.hive_passwd, 

  database=config.hive_db, 

)

  def query(self, sql): 

  with self.conn.cursor() as cursor: 

  cursor.execute(sql) 

  return cursor.fetch() 

  def queryNoResult(self, sql): 

  with self.conn.cursor() as cursor: 

  result=cursor.execute(sql) 

  return result 

  def close(self): 

  self.conn.close() 

  if __name__ == '__main__': 

  hiveClient=HiveClient() 

  hql_path = "/home/airflow/ebus/hql/SP_ODS_EBUS_CB_ACC_INF_DAY_INSERT.hql" 

  text=fileUtil.get_file_content(hql_path) 

  f=open(hql_path) 

  txt=f.read() 

  sql=txt.replace('${day_id}','20170818').replace('${past_day_id}','20170817') 

  sql_list=sql.split(";") 

  result=0 

  for hql in sql_list: 

  if hql.strip()!='': 

  print("hql=="+hql) 

  try: 

  hiveClient.queryNoResult(hql) 

  except Pyhs2Exception : 

  result=1 

  print(result) 

  hiveClient.close() 

-----------/home/airflow/ebus/hql/SP_ODS_EBUS_CB_ACC_INF_DAY_INSERT.hql 文件如下：

INSERT INTO TABLE bank.ODS_CBBNDIRT PARTITION(PARTITION_DATE = '${day_id}')
SELECT
TRIM(REGEXP_REPLACE(CORPORATION,'"','')) as CORPORATION,
TRIM(REGEXP_REPLACE(DAY_ID,'"','')) as DAY_ID,
TRIM(REGEXP_REPLACE(FK_CBBND_KEY,'"','')) as FK_CBBND_KEY,
TRIM(REGEXP_REPLACE(CB_IRT_ACT_DATE,'"','')) as CB_IRT_ACT_DATE,
CBIRT_DB_TIMESTAMP ,
CB_BASE_IRT ,
TRIM(REGEXP_REPLACE(FILLER,'"','')) as FILLER
FROM out.ODS_CBBNDIRT;