为了将数据仓库设计过程中excel中设计的物理模型高效转换成标准的Hive建表语句，我用 python开发了如下的工具
createDdlSql.py:
功能：实现将excel中的物理模型转换成建表语句文件
输入：当前目录文件名为“数据模型.xls”或“数据模型.xlsx”的excel，模型结尾必须要有数据检验两行
输出：当前目录建表语句文件
python
1 ''' 2 --*************************************************************** 3 --*脚本名称: createSqlDdl 4 --*功能: excel模型-》Ddl_表名.sql 5 --*输入数据:数据模型.xls or 数据模型.xlsx 6 --*输出数据：Ddl_表名.sql 7 --*作者: guominghuang 8 --*更新时间： 2020-5-15 15:00 9 --*补充说明： 可能因为numpy和pandas版本问题报错 TypeError: int() argument must be a string, a bytes-like object or a number, not '_NoValueType' 10 重装 numpy和pandas 或者 更换脚本运行环境 11 --*************************************************************** 12 13 --*版本控制：版本号 提交人 提交日期 提交内容 14 -- V1.0 guominghuang 2020-5-15 新增上线 15 ''' 16 import os 17 import numpy as np 18 import pandas as pd 19 from pandas import DataFrame 20 #HDFS数据仓库路径 21 # HDFS_DIR = "'hdfs://bigdata:9000/tc_dev/" 22 HDFS_DIR = "'hdfs://cdh02:8020/dw/" 23 24 25 ''' 26 转换函数 27 ''' 28 def transform(file): 29 excel = pd.read_excel(file,None) 30 sheetNames = excel.keys() 31 for sheetName in sheetNames: 32 # if sheetName.startswith("ods") or sheetName.startswith("dwd") or sheetName.startswith("dws") or sheetName.startswith("dws") or sheetName.startswith("ads") or sheetName.startswith("dim"): 33 if sheetName.startswith("ods"): 34 df= DataFrame(pd.read_excel(file,sheetName)) 35 #获取表名和表注释 36 tableName = str(df.columns.values[1]) 37 tableComment = df.iloc[0][1] 38 #删除第一行和第二行无用数据 39 df.drop([0,1],inplace=True) 40 #删除空行 41 df.dropna(axis='index', how='all',inplace=True) 42 #取出分区字段行 43 partitionRow = np.array(df[-3:-2]).tolist()[0] 44 45 46 # 获取分区字段名 47 partitionFieldName = partitionRow[0] 48 # 获取分区字段类型 49 partitionFieldType = partitionRow[1] 50 # 获取分区字段注释 51 #partitionFieldComment = partitionRow[columns[2]] 52 53 #判断是否存在分区字段"partition_date" 54 if partitionFieldName != "partition_date": 55 # 删除无用数据校验2行，获得所有字段 56 dfSub3row = df[:-2] 57 else: 58 # 删除无用数据校验2行和分区字段1行，获得所有字段 59 dfSub3row = df[:-3] 60 61 #获取列名 62 columns = df.columns.values 63 #获取字段名 64 fieldName = dfSub3row[columns[0]] 65 #获取字段类型 66 fieldType = dfSub3row[columns[1]] 67 #获取字段注释 68 fieldComment = dfSub3row[columns[2]] 69 70 sql = "CREATE EXTERNAL TABLE "+ tableName + " (\n" 71 #计数保证','格式 72 count = 0 73 # fieldName是Series类型，需要按key取值 74 #遍历所有字段 75 for i in fieldName.keys(): 76 #如果字段备注为空 77 if pd.isnull(fieldComment[i]): 78 newFieldComment = '' 79 else: 80 newFieldComment = fieldComment[i] 81 82 if count == 0: 83 sql += " " + str(fieldName[i]) + " " \ 84 + str(fieldType[i]) + " COMMENT \'" + str(newFieldComment) + "\'" + "\n" 85 else: 86 sql += " " + ','+ str(fieldName[i]) + " " \ 87 + str(fieldType[i]) + " COMMENT \'" + str(newFieldComment) + "\'" + "\n" 88 89 count += 1 90 91 # 如果表备注为空 92 if pd.isnull(tableComment): 93 tableComment = '' 94 95 #判断是否存在分区字段"partition_date" 96 if partitionFieldName != "partition_date": 97 sql = sql + ")" + "\n" + "COMMENT '" + tableComment + "'" + "\n" \ 98 + "row format delimited fields terminated by '|'" + "\n"\ 99 + "STORED AS ORC" + "\n" \ 100 + "LOCATION" + '\n' \ 101 + HDFS_DIR + str(tableName[0:3]) + "/" + tableName + "'\n" + ";" 102 else: 103 sql = sql + ")" + "\n" + "COMMENT '" + tableComment + "'" + " PARTITIONED BY(" + "\n" + partitionFieldName \ 104 + " " + partitionFieldType + ")" + "\n" \ 105 + "row format delimited fields terminated by '|'" + "\n" \ 106 + "STORED AS ORC" + "\n" \ 107 + "LOCATION" + '\n' \ 108 + HDFS_DIR + str(tableName[0:3]) + "/" + tableName + "'\n" + ";" 109 110 print(sql) 111 112 #创建Ddl文件 113 114 fileName = "Ddl_" + sheetName + ".sql" 115 #创建文件对象,覆盖写方式 116 fileObject = open(fileName,'w') 117 try: 118 #创建文件 119 fileObject.write(sql) 120 finally: 121 #关闭文件对象 122 fileObject.close() 123 124 125 126 #主函数 127 if __name__ == '__main__': 128 #获取当前目录下数据模型文件 129 file_list = os.listdir('.') 130 for file in file_list: 131 if(file.startswith('数据模型')): 132 #生成建表文件 133 transform(file)
输入模型excel范例
输出：
1 CREATE EXTERNAL TABLE ods_ykt_base_term_yy_f ( 2 ecode string COMMENT '企业代码' 3 ,term_id int COMMENT '终端编号' 4 ,term_name string COMMENT '终端名称' 5 ,term_addr string COMMENT '终端地址,如果是以太网设备就为实际的IP地址' 6 ,dpt_code string COMMENT '商户部门代码,不可重复' 7 ,account_code int COMMENT '一卡通系统的科目代码' 8 ,dscrp string COMMENT '一卡通系统的科目描述' 9 ,isuse int COMMENT '是否使用' 10 ,extended_term_addr int COMMENT '扩展终端编号' 11 ,pos_code string COMMENT '设备运营唯一编号' 12 ,type_id int COMMENT '终端类型编号' 13 ,sam_card_no bigint COMMENT '卡号,没有sam卡为0' 14 ,communication_mode int COMMENT '通讯方式' 15 ,all_dpt_code string COMMENT '终端所属部门编号' 16 ,update_flag string COMMENT '更新状态' 17 ,update_time string COMMENT '更新时间' 18 ,down_time string COMMENT '读取时间' 19 ,ver string COMMENT '版本号' 20 ) 21 COMMENT '终端信息表' PARTITIONED BY( 22 partition_date string) 23 STORED AS PARQUET 24 LOCATION 25 'hdfs://dse.host.hz.io:8020/jkd_dw/ods/ods_ykt_base_term_yy_f' 26 ;