数据仓库物理模型建表工具
为了将数据仓库设计过程中excel中设计的物理模型高效转换成标准的Hive建表语句,我用 python开发了如下的工具
createDdlSql.py:
功能:实现将excel中的物理模型转换成建表语句文件
输入:当前目录文件名为“数据模型.xls”或“数据模型.xlsx”的excel,模型结尾必须要有数据检验两行
输出:当前目录建表语句文件
python
‘‘‘ --*************************************************************** --*脚本名称: createSqlDdl --*功能: excel模型-》Ddl_表名.sql --*输入数据:数据模型.xls or 数据模型.xlsx --*输出数据:Ddl_表名.sql --*作者: guominghuang --*更新时间: 2020-5-15 15:00 --*************************************************************** --*版本控制:版本号 提交人 提交日期 提交内容 -- V1.0 guominghuang 2020-5-15 新增上线 ‘‘‘ import os import numpy as np import pandas as pd from pandas import DataFrame #HDFS数据仓库路径 HDFS_DIR = "‘hdfs://dse.host.hz.io:8020/jkd_dw/" ‘‘‘ 转换函数 ‘‘‘ def transform(file): excel = pd.read_excel(file,None) sheetNames = excel.keys() for sheetName in sheetNames: if sheetName.startswith("ods") or sheetName.startswith("dwd") or sheetName.startswith("dws") or sheetName.startswith("dws") or sheetName.startswith("ads") or sheetName.startswith("dim"): #if sheetName.startswith("ods_tg_edu_b"): df= DataFrame(pd.read_excel(file,sheetName)) #获取表名和表注释 tableName = df.columns.values[1] tableComment = df.iloc[0][1] #删除第一行和第二行无用数据 df.drop([0,1],inplace=True) #删除空行 df.dropna(axis=‘index‘, how=‘all‘,inplace=True) #取出分区字段行 partitionRow = np.array(df[-3:-2]).tolist()[0] # 获取分区字段名 partitionFieldName = partitionRow[0] # 获取分区字段类型 partitionFieldType = partitionRow[1] # 获取分区字段注释 #partitionFieldComment = partitionRow[columns[2]] #判断是否存在分区字段"partition_date" if partitionFieldName != "partition_date": # 删除无用数据校验2行,获得所有字段 dfSub3row = df[:-2] else: # 删除无用数据校验2行和分区字段1行,获得所有字段 dfSub3row = df[:-3] #获取列名 columns = df.columns.values #获取字段名 fieldName = dfSub3row[columns[0]] #获取字段类型 fieldType = dfSub3row[columns[1]] #获取字段注释 fieldComment = dfSub3row[columns[2]] sql = "CREATE EXTERNAL TABLE "+ tableName + " (\n" #计数保证‘,‘格式 count = 0 # fieldName是Series类型,需要按key取值 #遍历所有字段 for i in fieldName.keys(): if count == 0: sql += " " + fieldName[i] + " " 75 + fieldType[i] + " COMMENT \‘" + fieldComment[i] + "\‘" + "\n" else: sql += " " + ‘,‘+ fieldName[i] + " " 78 + fieldType[i] + " COMMENT \‘" + fieldComment[i] + "\‘" + "\n" count += 1 #判断是否存在分区字段"partition_date" if partitionFieldName != "partition_date": sql = sql + ")" + "\n" + "COMMENT ‘" + tableComment + "‘" + "\n" + "STORED AS PARQUET" + "\n" + "LOCATION" + ‘\n‘ 85 + HDFS_DIR + tableName[0:3] + "/" + tableName + "‘\n" + ";" else: sql = sql + ")" + "\n" + "COMMENT ‘" + tableComment + "‘" + " PARTITIONED BY(" + "\n" + partitionFieldName 88 + " " + partitionFieldType + ")" + "\n" + "STORED AS PARQUET" + "\n" + "LOCATION" + ‘\n‘ 89 + HDFS_DIR + tableName[0:3] + "/" + tableName + "‘\n" + ";" # print(sql) #创建Ddl文件 fileName = "Ddl_" + sheetName + ".sql" #创建文件对象,覆盖写方式 fileObject = open(fileName,‘w‘) try: #创建文件 fileObject.write(sql) finally: #关闭文件对象 fileObject.close() #主函数 if __name__ == ‘__main__‘: #获取当前目录下数据模型文件 file_list = os.listdir(‘.‘) for file in file_list: if(file == ‘数据模型.xls‘) or (file == ‘数据模型.xlsx‘): #生成建表文件 transform(file)
输入模型excel范例
输出:
CREATE EXTERNAL TABLE ods_ykt_base_term_yy_f ( ecode string COMMENT ‘企业代码‘ ,term_id int COMMENT ‘终端编号‘ ,term_name string COMMENT ‘终端名称‘ ,term_addr string COMMENT ‘终端地址,如果是以太网设备就为实际的IP地址‘ ,dpt_code string COMMENT ‘商户部门代码,不可重复‘ ,account_code int COMMENT ‘一卡通系统的科目代码‘ ,dscrp string COMMENT ‘一卡通系统的科目描述‘ ,isuse int COMMENT ‘是否使用‘ ,extended_term_addr int COMMENT ‘扩展终端编号‘ ,pos_code string COMMENT ‘设备运营唯一编号‘ ,type_id int COMMENT ‘终端类型编号‘ ,sam_card_no bigint COMMENT ‘卡号,没有sam卡为0‘ ,communication_mode int COMMENT ‘通讯方式‘ ,all_dpt_code string COMMENT ‘终端所属部门编号‘ ,update_flag string COMMENT ‘更新状态‘ ,update_time string COMMENT ‘更新时间‘ ,down_time string COMMENT ‘读取时间‘ ,ver string COMMENT ‘版本号‘ ) COMMENT ‘终端信息表‘ PARTITIONED BY( partition_date string) STORED AS PARQUET LOCATION ‘hdfs://dse.host.hz.io:8020/jkd_dw/ods/ods_ykt_base_term_yy_f‘ ;
相关推荐
jasonchen 2020-09-07
wujun 2020-08-03
chognzhihongseu 2020-06-28
cyydjt 2020-06-25
meiseeny 2020-06-11
SignalDu 2020-05-28
张小涛涛 2020-05-06
弱思 2020-04-27
tutubuchicao 2020-04-20
weiguoxin 2020-04-10
天涯莺歌 2020-03-08
swazerz 2020-02-03
KANSYOUKYOU 2020-01-29
lichunan 2020-01-09
xiyouiOS 2014-06-12
zjyzz 2014-05-29
ruancw 2019-12-17