1、参考代码脚本

# CreateTableSql.py
import re
class CreateTableSql():
    def __init__(self,sql):
        self.create_table_mysql=sql

    def analyse_mysql_create_table(self):
        """
        解析mysql建表语句：
        :param create_table_sql:
        :return: 字段名称、字段注释、字段类型、表注释
        """
        # 字段名称
        name_pattern = r"`(\w+)`"
        varname_list = re.findall(name_pattern, self.create_table_mysql)
        # 字段注释
        comment_pattern = r"COMMENT \'(.*)\'"
        varcomment_list = re.findall(comment_pattern, self.create_table_mysql)
        # 字段类型
        type_pattern = r"` (.*) COMMENT"
        type_list = re.findall(type_pattern, self.create_table_mysql)
        # 表注释
        tcomment_pattern = r"COMMENT='(.*)'"
        tcomment_list = re.findall(tcomment_pattern, self.create_table_mysql)
        return varname_list, varcomment_list, type_list, tcomment_list

    def change_type(self,cloumns_type):
        '''
        mysql字段类型转化成hive字段类型
        # type: mysql => hive sql
        '''
        if re.search(r'int', cloumns_type) != None:
            return 'BIGINT'
        elif re.search(r'decimal', cloumns_type) != None:
            return 'DOUBLE'
        elif re.search(r'varchar', cloumns_type) != None:
            return 'STRING'
        elif re.search(r'timestamp', cloumns_type) != None:
            return 'STRING'
        elif re.search(r'text', cloumns_type) != None:
            return 'STRING'
        elif re.search(r'datetime', cloumns_type) != None:
            return 'STRING'
        elif re.search(r'date', cloumns_type) != None:
            return 'STRING'
        elif re.search(r'char', cloumns_type) != None:
            return 'STRING'
        else:
            return cloumns_type

    def mysql_to_hsql(self):
        """
        生成Hive建表语句
        :return:
        """
        varname_list, varcomment_list, type_list, tcomment_list = self.analyse_mysql_create_table()
        hive_sql = "create table " + str(varname_list[0] + "(\n")
        for i in range(len(varname_list) - 2):
            if i != len(varname_list) - 3:
                hive_sql = hive_sql + "     " + str(varname_list[i + 1]) + " " + str(
                    self.change_type(type_list[i])) + " comment \"" + str(varcomment_list[i]) + "\",\n"
            else:
                hive_sql = hive_sql + "     " + str(varname_list[i + 1]) + " " + str(
                    self.change_type(type_list[i])) + " comment \"" + str(varcomment_list[i]) + "\"\n"
        hive_sql = hive_sql + ") comment \"" + str(tcomment_list[0]) + "\";"
        print(hive_sql)

2、CreateTableSql类使用说明:

from CreateTableSql import CreateTableSql

create_table_sql = """
    CREATE TABLE `members_infos` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
      `phone` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号',
      `wechat` varchar(30) NOT NULL DEFAULT '' COMMENT '微信号',
      `owner` varchar(10) NOT NULL COMMENT '归属人',
      `actual_amount` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '实际销售额',
      `complete_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '完成时间',
      `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3935 DEFAULT CHARSET=utf8 COMMENT='会员表';
"""

cts=CreateTableSql(create_table_sql)
cts.mysql_to_hsql()

create table members_infos(
     id BIGINT comment "主键id",
     user_id BIGINT comment "用户id",
     phone STRING comment "手机号",
     wechat STRING comment "微信号",
     owner STRING comment "归属人",
     actual_amount BIGINT comment "实际销售额",
     complete_time BIGINT comment "完成时间",
     created_at STRING comment "创建时间",
     updated_at STRING comment "更新时间"
) comment "会员表";