hive 操作

#coding:utf-8
import sys
import pyhs2

dbs = 'dw_raw_mid'
table="dw_user_app_install_mid_daily"
sql="show partitions dw_raw_mid.dw_user_app_install_mid_daily"
list1=[]
list12=[]
yuying=[]
with pyhs2.connect(host='XXXX',
                   port=10000,
                   authMechanism="PLAIN",
                   user='hive',
                   password='',
                   database=dbs) as conn:
     with conn.cursor() as cur:
            #Show databases
            #print cur.getDatabases()

#            cur.execute("set role admin")
            cur.execute(sql)

            #Fetch table results
            for i in cur.fetch():
                 print i
                 if "/" in i[0]:
                        finall=i[0].replace("/",",")
                        list1.append(("describe formatted " +dbs + "." + table + " partition" +"("+finall + ")"))
                 else:
                        list1.append(("describe formatted " +dbs + "." + table + " partition" +"("+i[0] + ")"))

#print list1

 

表加分区

 

#!/bin/env python2.7
#coding:utf-8
import sys
import pyhs2

dbs = 'report_dw_mdp'
table="dw_user_wide_table_gid"
sql="show partitions report_dw_mdp.dw_user_wide_table_gid"
list1=[]
list12=[]
yuying=[]
with pyhs2.connect(host='XXXXX',
                   port=10000,
                   authMechanism="PLAIN",
                   user='hive',
                   password='XXXXXX',
                   database=dbs) as conn:
     with conn.cursor() as cur:
            #Show databases
            #print cur.getDatabases()
            cur.execute("set role admin")
#            cur.execute("set role admin")
            cur.execute(sql)
            
            #Fetch table results
            for i in cur.fetch():
                 if "/" in i[0]:
                        finall=i[0].replace("/",",")
                        daily=finall.split(',')[0]
                        area=finall.split(',')[1]
                        day=daily.split('=')[1]
                        are=area.split('=')[1]
                        print "partition(day=%s,area=%s) location 'hdfs://getui-bi-storage/data_result/user/dw_user_lbs_std/lbs/%s/%s'" %(day,are,day,are)
        #                list1.append(("describe formatted " +dbs + "." + table + " partition" +"("+finall + ")"))
                 else:                        
        #               list1.append(("describe formatted " +dbs + "." + table + " partition" +"("+i[0] + ")"))
                        day=str(i[0]).split('=')[1]
                        print "partition(day=%s) location 'hdfs://getui-bi-calc/data_result/user/dw_user_wide_table_gid//%s'" %(day,day)

#!/bin/env python2.7

import sys
import pyhs2

dbs = ['log']
tables=[
"dw_app_gindex.dau_aggregate",
"dw_app_gindex.stock_aggregate",
"dw_app_gindex.mau_aggregate",
"report_dim_mdp.dim_phonemodel_attr",
"report_dim_mdp.dim_app_category",
"report_dw_mdp.imei2imsi",
"dw_raw_mid.dw_user_app_install_mid_daily",
"dw_raw_mid.dw_user_app_uninstall_mid_daily",
"report_dim_mdp.swap_lvw_imei_persona"
]
def hivesql():
        with pyhs2.connect(host='xxxx',
                   port=10000,
                   authMechanism="PLAIN",
                   user='hive',
                   password='xxx',
                   database='fangmt') as conn:
                with conn.cursor() as cur:
            #Show databases
            #print cur.getDatabases()
            
                        cur.execute("set role admin")
                        cur.execute("show tables")
     
            #Fetch table results
                        for i in range(len(tables)):
                                sql = "grant select on table {0} to user {1}".format(tables[i],"luxq")
                                print sql
                                cur.execute(sql)
#hivesql()
try:
        hivesql()
except Exception,e:
       print "faild"