相关文章推荐
长情的跑步机  ·  MFC ...·  1 年前    · 
睿智的砖头  ·  造成SQL ...·  1 年前    · 
根据数据字典自动生成sql建表语句

根据数据字典自动生成sql建表语句

在工作中,每次用EXCEL做完数仓建模,生成数据字典以后,还要一个个编写hive的建表语句,如果表比较少还行,但是如果是成百上千张表,手动建表就比较繁琐了。

之前有学过python,很久没用了,所以顺便就捡起来,写了一个自动化通过EXCEL生成sql建表语句的python脚本,请教了一个项目上以前做爬虫的同事,所以比较快的完成了。这里做一个记录分享。

EXCEL样式

为了方便演示,这里展示的是一个demo版的数据字典,内容比较简单,一个目录页,两个表单详情页


每个表单里,第一列第二行是表名,第二列第二行是表的中文备注,第三列是字段名,第四列是字段备注,第五列是数据类型,字段的最后一行如果有分区字段,则说明是分区表。


Python代码

下面直接上代码和详细的备注,复制到代码编辑器中更方便观看。

import xlrd #操作Excel的库
#Excel文件路径
file_name = input('请输入Excel文件路径:')
jb_name = input("输入脚本存放路径:")
#读取Excel文件 
excel = xlrd.open_workbook(r'%s'% file_name)  #输入绝对路径要加^,同目录下则直接输入,r识别转义符/,%s引用%后面的变量
#开始遍历sheet
for i in range(excel.nsheets): #.nsheets获取excel的sheet数量,确定循环次数
    #跳过第一个sheet目录
    if i != 0:
        #构建建表sql头,第一个%s定义表中文备注,第二个%s定义表名
        sql = '''\n\n--%s
CREATE TABLE IF NOT EXISTSC %s 
        sheet = excel.sheet_by_index(i)  #.sheet_by_index()通过索引查找,定位到此时遍历的是哪一张sheet
        table_name = sheet.row_values(1)[0]  #表名等于sheet的第2行,第1列(顺序是从0开始),.row_values定位到行
        table_comment = sheet.row_values(1)[1] #表备注等于sheet的第2行,第2列
        sql = sql % (table_comment,table_name) #给sql头变量填充值
        row_num = sheet.nrows #获取当前表单总行数
        if ('PART_' in sheet.row_values(row_num-1)[2] or 'part_' in sheet.row_values(row_num-1)[2]) and '分区' in sheet.row_values(row_num-1)[3]:
            row_num = row_num -1 #有分区就过滤最后一行,算出总行数
        for m in range(row_num): #确定需要循环的字段行数
            if m > 0: # 过滤掉第一行表头
                col_name = sheet.row_values(m)[2] #获取第m行,第3列的值作为字段名称
                col_type = sheet.row_values(m)[4] #获取第m行,第5列的值作为字段的数据类型
                col_comment = sheet.row_values(m)[3] #获取第m行,第4列的值作为字段备注
                if m == row_num -1:#当遍历到最后一行时
                    col_sql = "    %s %s COMMENT '%s'\n)\n" % (col_name,col_type,col_comment) #最后一行结尾带括号并换行
                else:
                    col_sql = "    %s %s COMMENT '%s',\n" % (col_name,col_type,col_comment) #每一行结尾带,号并换行
                sql = sql + col_sql
        sql = sql + "  COMMENT '%s'\n" % table_comment #给表添加备注
        #判断该表是否分区,分区表分区格式为parquet,非分区表为textfile
        if ('PART_' in sheet.row_values(sheet.nrows - 1)[2] or 'part_' in sheet.row_values(sheet.nrows - 1)[2]) and '分区' in sheet.row_values(sheet.nrows - 1)[3]:
            sql = sql + "    PARTITIONED BY (%s STRING COMMENT '%s')\n" % (sheet.row_values(sheet.nrows - 1)[2], sheet.row_values(sheet.nrows - 1)[3])
            sql = sql + "    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\036'\n"
            sql = sql + '    STORED AS PARQUET;\n\n'