自己python实现mysql2hive 的表结构功能,在脚本后输入mysql_db,mysql_table,hive_db,hive_table和任意字符

最新推荐文章于 2023-04-16 16:45:27 发布

redfox1234567

最新推荐文章于 2023-04-16 16:45:27 发布

阅读量555

点赞数

分类专栏：工具文章标签： python mysql2hive

本文链接：https://blog.csdn.net/redfox1234567/article/details/78718091

版权

工具专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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



import  pymysql
import  sys

def get_table_info(mysql_db,mysql_table,hive_db,hive_table,ispartition = True):
  
    cols = []
    create_head = '''
create external table if not exists {0}.{1}('''.format(hive_db,hive_table)
    if ispartition:
        create_tail = r'''
partitioned by(dt string)
row format delimited fields terminated by '\001'
location '/hivetable/{0}';'''.format(hive_table)
    else:
        create_tail = r'''
row format delimited fields terminated by '\001'
location '/hivetable/{0}';'''.format(hive_table)
    connection=pymysql.connect(host='localhost',
                               user='root',
                               password='56789',
                               db=mysql_db,
                               port=3306,
                               charset='utf8'
                               )
    try:
    #获取一个游标
        with connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            sql='SHOW FULL FIELDS FROM  {0}'.format(mysql_table)
            cout=cursor.execute(sql) #返回记录条数
            try:
                for row in cursor:#cursor.fetchall()
                    # print(row)
                    # print (row['Type'])
                    cols.append(row['Field'])
                    if 'bigint' in row['Type']:
                        row['Type'] = "bigint"
                    elif 'int' in row['Type'] or 'tinyint' in row['Type'] or 'smallint' in row['Type'] or 'mediumint' in row['Type'] or 'integer' in row['Type']:
                        row['Type'] = "int"
                    elif 'double' in row['Type'] or 'float' in row['Type'] or 'decimal' in row['Type']:
                        row['Type'] = "double"
                    else:
                        row['Type'] = "string"
                    create_head += row['Field'] + ' '+ row['Type'] +' comment \'' + row['Comment'] + '\' ,\n'
            except:
                print('程序异常!')
    finally:
        connection.close()
       # print (create_head[:-2])
    create_str = create_head[:-2] + '\n' + ')'+ create_tail
    return cols,create_str # 返回字段列表与你建表语句

if __name__ == '__main__':
    mysql_db=sys.argv[1]
    mysql_table= sys.argv[2]
    hive_db = sys.argv[3]
    hive_table = sys.argv[4]
    if  (sys.argv[5]=='false') or (sys.argv[5]=='0'):
        ispartition = False
    else:
        ispartition = True
    cols, create_str = get_table_info(mysql_db,mysql_table,hive_db,hive_table,ispartition)

    # cols, create_str = get_table_info("date_dimension", "ods")
    # print(cols)
    print(create_str)
    create_str=create_str.encode("utf-8")
    document = open("/Users/wanghaiyang/Documents/testfile.txt", "w");
    document.write(create_str);
    document.close();