通过数据库SQLServer生成Hive表

此代码是读取SQLServerSchema 生成Hive表，仅供参考。
需要注意的是：
env：是否为生产环境
source：是在hdfs哪里创建文件

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os
import  pymssql
import  re
import  sys
 

hosts = "45.34.5.6"
username = "xxxx"
password = "xxx"
database = "xxx"
env = 'pro'
schema = 'pro_ods'
source = 'nc_pro'

#table_list = sys.argv[1].split(",")

table_list = ['ic_saleout_b','ic_saleout_h']

def get_table_info(table,schema = schema,ispartition = False):
    '''
    #table =  为表名，mysql,hive表名一致
    #schema = 为hive中的库名
    #ispartition : 是否分区默认为分区
    '''
    cols = []
    create_head = '''
create external table if not exists {0}.{1}('''.format(schema,'ods_1_'+table )
    if ispartition:
        create_tail = r'''
partitioned by(inc_day string)
row format delimited fields terminated by '\001'
location '/{0}/{1}/{2}/{3}';'''.format(env,schema,source,'ods_1_'+table)
    else:
        create_tail = r'''
row format delimited fields terminated by '\001'
location '/{0}/{1}/{2}/{3}';'''.format(env,schema,source,'ods_1_'+table)
    connection=pymssql.connect(host=hosts,
                               user=username,
                               密码=password,
                               database=database,
                               charset='utf8'
                               )

    try:
    #获取一个游标
        with connection.cursor() as cursor:#connection.cursor(cursor=pymssql.cursors.DictCursor) as cursor:#
            #sql='SHOW FULL FIELDS FROM  {0}'.format(table)
            sql='select column_name,data_type from information_schema.columns where table_name = \'{0}\''.format(table)
            cout=cursor.execute(sql) #返回记录条数
            try:
                for row in cursor:#cursor.fetchall()
                    #print(type(row))
                    #print(row[0])
                    cols.append(row[0]) 
                    if 'bigint' in row[1]:
                        colunm_t = "bigint"
                    elif 'int' in row[1] or 'tinyint' in row[1] or 'bigint' in row[1] or 'smallint' in row[1] or 'mediumint' in row[1]: 
                        colunm_t = "bigint"
                    elif 'double' in row[1] or 'float' in row[1] or 'decimal' in row[1]:
                        colunm_t = "decimal"
                    else:
                        colunm_t = "string"
                    create_head += row[0] + '\t'+ colunm_t+ ',\n'     
            except Exception as e:
                print('程序异常!')  
                print(e)
    finally:
        connection.close()
    create_str = create_head[:-2] + '\n' + ')'+ create_tail
    return cols,create_str # 返回字段列表与你建表语句   
#cols,create_str = get_table_info(sys.argv[1])



for i in range(0,len(table_list)):
    cols,create_str = get_table_info(table_list[i])
    #print('===================='+table_list[i]+'======================')
    print(create_str)
    #print('\n\n\n\n')
    os.system('hive -e "' + create_str +'"')