现在python使用越来越方便了，当我们前期做数仓项目的时候，需要批量从mysql数据库，批量创建hive，并修改对应的类型，如果一个个创建耗时又费力，python提供如下方法，可以从mysql数据直接读取所有建表语句，批量创建对应的hive表，极大提高开发效率。

# 导入pymysql模块 import pymysql import numpy as np # 连接database conn = pymysql.connect( host="服务ip", user="用户名", password="密码", database="数据库名" # 获取游标 cursor = conn.cursor() # 查询所有表名 cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = '库名' and table_name= '表名'") tables = cursor.fetchall() # 遍历所有表 for table in tables: # 获取表名 table_name = table[0] # 查询表结构 cursor.execute("SELECT column_name, data_type FROM information_schema.COLUMNS WHERE table_name = '{}' AND table_schema = '库名'".format(table_name)) columns = cursor.fetchall() # print(columns) array1 = np.array(columns) # print(len(array1)) count = len(array1) field_trans_type = {'varchar': 'string', 'double': 'DOUBLE', 'char': 'STRING', 'float': 'DOUBLE', 'int': 'BIGINT', 'datetime': 'STRING', 'time': 'STRING', 'boolean': 'BOOLEAN', 'int32': 'INT', 'uint32': 'INT', 'decimal': 'decimal', 'timestamp': 'STRING', 'bigint': 'BIGINT', 'string': 'STRING'} mysql_sql = "CREATE TABLE IF NOT EXISTS {} (".format(table_name) for column in columns: column_name = column[0] column_type = column[1] mysql_sql += "{} as {}, \n".format(column_name, field_trans_type.get(column_type)) mysql_sql = mysql_sql[:-1] + """\netl_time as time\n) partitioned by (dt string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' STORED AS orc LOCATION '/warehouse/ods/{}' TBLPROPERTIES ("orc.compress"="SNAPPY");""" print(mysql_sql) # 关闭游标和连接 cursor.close() conn.close()