数据仓库物理模型建表工具

为了将数据仓库设计过程中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‘
;