hive locations 导出

select tbls.TBL_NAME,tbls.SD_ID,SDS.LOCATION from tbls,SDS where tbls.SD_ID=SDS.SD_ID and tbls.TBL_NAME="user_loss_analysis_04 ";

hive mysql172.16.17.100数据库查找 库 表 分区 location 信息
SELECT tmp2.NAME, tmp2.TBL_NAME,tmp1.COLUMN_NAME,tmp1.TYPE_NAME FROM (SELECT sds.`SD_ID`,cv.`COLUMN_NAME`,cv.`TYPE_NAME` FROM `COLUMNS_V2` cv LEFT JOIN SDS sds ON cv.`CD_ID`=sds.`CD_ID`) tmp1 right JOIN (SELECT dbs.`NAME`,tbls.`TBL_NAME`,tbls.`SD_ID`FROM `TBLS` tbls right JOIN (select DB_ID,NAME from `DBS` where NAME in('report_dw_mdp','report_ods_mdp')) dbs ON tbls.`DB_ID`=dbs.`DB_ID`) tmp2 ON tmp1.SD_ID=tmp2.SD_ID

select DBS.NAME,TBLS.TBL_NAME,PARTITIONS.PART_NAME,SDS.LOCATION from DBS,TBLS,PARTITIONS,SDS where DBS.DB_ID=TBLS.DB_ID and PARTITIONS.TBL_ID=TBLS.TBL_ID and SDS.SD_ID=PARTITIONS.SD_ID

python 脚本

#!/usr/bin/env python
#coding:utf-8
import  MySQLdb
import time
import urllib
import urllib2
talist=[
["dw_raw_mid","poi_daily"],
["dw_raw_mid","type10_monthly"],
["dw_raw_mid","type24_monthly"],
["dw_raw_mid","user_frequency_duration_daily"],
["dw_raw_mid","user_imei2imsi"],
["dw_raw_mid","user_type14_mid"],
["dw_raw_mid","boi_mid_matched"],
["dw_raw_mid","dw_user_app_upgrade_mid_daily"],
["dw_raw_mid","poi_daily_aggregate"],
["dw_raw_mid","poi_weekly"],
["dw_raw_mid","user_type6_mid_daily"],
["dw_raw_mid","wifimac_home_week"],
["dw_raw_mid","dw_user_applist_gid"],
["dw_raw_mid","dw_user_fullapplist_gid"],
["dw_raw_mid","dw_user_lbs_mid_lbs"],
["dw_raw_mid","dw_user_type27_mid_daily"],
["dw_raw_mid","dw_user_type27_mid_twoweek"],
["dw_raw_mid","type26_mid"],
["dw_raw_mid","wifimac_persona_gid_week"],
["dw_raw_mid","woi_weekly"],
["dw_raw_std","user_lbs_std_phonestate"],
["dw_raw_std","user_lbs_std_type11"],
["dw_raw_std","user_lbs_std_type11_hourly"],
["dw_raw_std","user_type31_std"],
["dw_raw_std","dw_user_type45_std"],
["dw_raw_std","user_type14_std"],
["dw_raw_std","user_type38_std"],
["dw_raw_std","user_type6_std"],
["dw_raw_std","dw_gezhi_cleaned_avro"],
["dw_raw_std","dw_user_login_std"],
["dw_raw_std","type31_deviceinfo_std"],
["dw_raw_std","user_lbs_std_beacon"],
["dw_raw_std","user_type14_mid"],
["report_ods_mdp","upload_bi_type24_hourly"],
["report_ods_mdp","upload_bi_type25"],
["report_ods_mdp","upload_bi_type27_hourly"],
["report_ods_mdp","upload_bi_type28"],
["report_ods_mdp","upload_bi_type29_hourly"],
["report_ods_mdp","upload_bi_type31"],
["report_ods_mdp","upload_bi_type31_hourly"],
["report_ods_mdp","upload_bi_type34"],
["report_ods_mdp","upload_bi_type37"],
["report_ods_mdp","upload_bi_type39_hourly"],
["report_ods_mdp","upload_bi_type40_hourly"],
["report_ods_mdp","upload_bi_type43_hourly"],
["report_ods_mdp","upload_bi_type46"],
["report_ods_mdp","upload_bi_type64_hourly"],
["report_ods_mdp","upload_bi_type69_hourly"],
["report_ods_mdp","upload_bi_type6_hourly"],
["report_ods_mdp","upload_bi_type73_hourly"],
["report_ods_mdp","upload_bi_type78_hourly"],
["report_ods_mdp","upload_bi_type88_hourly"],
["report_ods_mdp","user_login"],
["report_ods_mdp","user_login_clear_dw"],
["report_ods_mdp","user_login_hourly"],
["report_ods_mdp","user_login_mac"],
["report_ods_mdp","user_online"],
["report_ods_mdp","user_reg_add_phone_info_all_idfa"],
["report_ods_mdp","yarn_open_resource_manager"],
["report_ods_mdp","yarn_yunying_resource_manager"],
["report_ods_mdp","access_open_log"],
["report_ods_mdp","bj_gtmp_win"],
["report_ods_mdp","dw_wifibox_data"],
["report_ods_mdp","geshu_bi_type2_hourly"],
["report_ods_mdp","getui_website_log"],
["report_ods_mdp","gi_query"]]
try:   
        conn=MySQLdb.connect(host='XXXXX',user='root',passwd='xxxx',db='hive',port=3306)
        cur=conn.cursor()
        for i in range(len(talist)):
                sql="select DBS.NAME,TBLS.TBL_NAME,PARTITIONS.PART_NAME,SDS.LOCATION from DBS,TBLS,PARTITIONS,SDS where DBS.DB_ID=TBLS.DB_ID and PARTITIONS.TBL_ID=TBLS.TBL_ID and SDS.SD_ID=PARTITIONS.SD_ID and  DBS.NAME ='%s' and TBLS.TBL_NAME='%s'" %(talist[i][0],talist[i][1])
                #print sql
                print type(sql)
                cur.execute(sql)
        #qur_result=cur.fetchmany(20)
        #for record in qur_result:
        #       #list.append(record)
                list=cur.fetchall()
                for i in range(len(list)):
                        print list[i][0],list[i][1],list[i][2],list[i][3]
        #       print list        
        cur.close()
        conn.close()                     
except MySQLdb.Error,e:
     print 'mysql error msg:',e
#for i in range(len(list)):
#    print list[i][0],list[i][1],list[i][2],list[i][3]