#!/usr/bin/env python#-*- coding:utf8 -*-#从mysql中提取hive建表语句
importos,sysimportfileinputimportdatetimeimportmysql.connector
reload(sys)
sys.setdefaultencoding("utf8")defhive_create_table():
conn= mysql.connector.connect(host="192.168.xxx.xxx",user='hive',passwd='123456',database='hive',charset='utf8')
mycursor=conn.cursor()#获取DB_ID
select_DB_ID = "select DB_ID from DBS;"mycursor.execute(select_DB_ID)
result_DB_ID=mycursor.fetchall()
fo= open("create_tab.sql", "w")for dir_DB_ID inresult_DB_ID :#获取数据库名
DB_ID = str(dir_DB_ID)[1:].split(',')[0]print(DB_ID)
select_DB_NAME= "select NAME from DBS where DB_ID="+DB_ID+";"
print(select_DB_NAME )
mycursor.execute(select_DB_NAME)
result_DB_NAME=mycursor.fetchone()
fo.write("\n===========数据库:"+str(result_DB_NAME).split('\'')[1]+"===========\n")
DBname=str(result_DB_NAME).split('\'')[1]print '数据库名字:' +DBnameprint(result_DB_NAME)#获取表名
select_table_name_sql = "select TBL_NAME from TBLS where DB_ID="+DB_ID+";"mycursor.execute(select_table_name_sql)
result_table_names=mycursor.fetchall()for table_name inresult_table_names :
fo.write("\nCREATE TAB