devbox
小舞很执着
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

一行代码实现mysql建表语句格式化成hive建表语句_格式化表 语句

作者：小舞很执着 | 2024-07-14 19:10:41

赞

踩

格式化表 语句

前言

写这篇文章的目的就是想浅浅记录下日常工作中的小成就

背景

因为我们公司的数据平台在抽数据的时候无法自动生成ODS层hive格式的建表语句，而业务库的一些表字段又非常多，手动+excel修改耗时耗力，于是想通过一个Python脚本自动将mysql格式的建表语句转换成hive格式的。

转换前：

  1. CREATE TABLE `ai_warning_info` (
  2.   `id` varchar(40) NOT NULL COMMENT '主键',
  3.   `community_id` varchar(40) NOT NULL DEFAULT '' COMMENT '园区id',
  4.   `ai_warning_config_id` int(11) NOT NULL COMMENT '预警信息配置表主键id',
  5.   `warning_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '预警状态(1:待处理；2:已恢复；3:已处理（后台预警管理操作 直接处理/生成工单，则这条预警信息变成已处理）)',
  6.   `warning_content` varchar(500) NOT NULL DEFAULT '' COMMENT '预警内容',
  7.   `warning_rank` tinyint(4) NOT NULL DEFAULT '0' COMMENT '预警级别(1:严重;2:重要;3:一般)',
  8.   `warning_time` datetime DEFAULT NULL COMMENT '预警时间',
  9.   `intime` datetime DEFAULT NULL COMMENT '写入时间',
  10.   `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  11.   `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '说明',
  12.   `operate_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '预警处理方式1:直接处理；2:生成工单',
  13.   `operate_user_id` varchar(45) NOT NULL DEFAULT '' COMMENT '预警信息处理人id',
  14.   `operate_user_name` varchar(45) NOT NULL DEFAULT '' COMMENT '预警信息处理人name',
  15.   `operate_time` datetime DEFAULT NULL COMMENT '预警信息处理时间',
  16.   `order_id` varchar(40) DEFAULT '' COMMENT '工单id',
  17.   `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '工单状态 0缺省 1待接收 2处理中 3已处理 4已完成 5已关闭 6待处理',
  18.   `recovery_time` datetime DEFAULT NULL COMMENT '恢复时间',
  19.   `warning_trigger_id` varchar(45) NOT NULL DEFAULT '' COMMENT '触发预警事件的记录id',
  20.   `warning_type` tinyint(4) NOT NULL COMMENT '预警事件类型(1离园预警 2车辆违停 3消防通道占用)',
  21.   `work_order_identity` int(10) DEFAULT NULL COMMENT '新旧工单标识字段 1：老工单，2：新工单',
  22.   PRIMARY KEY (`id`) USING BTREE,
  23.   UNIQUE KEY `id_UNIQUE` (`id`) USING BTREE
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='ai预警信息';

转换后：

  1. CREATE TABLE IF NOT EXISTS ods_wspace_ai_warning_info_df (
  2.       id                             STRING COMMENT  '主键'
  3.      ,community_id                   STRING COMMENT  ' COMMENT '
  4.      ,ai_warning_config_id           STRING COMMENT  '预警信息配置表主键id'
  5.      ,warning_status                 STRING COMMENT  '预警状态(1:待处理；2:已恢复；3:已处理（后台预警管理操作 直接处理/生成工单，则这条预警信息变成已处理）)'
  6.      ,warning_content                STRING COMMENT  ' COMMENT '
  7.      ,warning_rank                   STRING COMMENT  '预警级别(1:严重;2:重要;3:一般)'
  8.      ,warning_time                   STRING COMMENT  '预警时间'
  9.      ,intime                         STRING COMMENT  '写入时间'
  10.      ,update_time                    STRING COMMENT  '更新时间'
  11.      ,remark                         STRING COMMENT  ' COMMENT '
  12.      ,operate_type                   STRING COMMENT  '预警处理方式1:直接处理；2:生成工单'
  13.      ,operate_user_id                STRING COMMENT  ' COMMENT '
  14.      ,operate_user_name              STRING COMMENT  ' COMMENT '
  15.      ,operate_time                   STRING COMMENT  '预警信息处理时间'
  16.      ,order_id                       STRING COMMENT  ' COMMENT '
  17.      ,order_status                   STRING COMMENT  '工单状态 0缺省 1待接收 2处理中 3已处理 4已完成 5已关闭 6待处理'
  18.      ,recovery_time                  STRING COMMENT  '恢复时间'
  19.      ,warning_trigger_id             STRING COMMENT  ' COMMENT '
  20.      ,warning_type                   STRING COMMENT  '预警事件类型(1离园预警 2车辆违停 3消防通道占用)'
  21.      ,work_order_identity            STRING COMMENT  '新旧工单标识字段 1：老工单，2：新工单'
  22. )
  23. COMMENT 'ai预警信息'
  24. PARTITIONED BY ( ds BIGINT )
  25. STORED AS PARQUET;"
  26.

转换脚本是我让我小姐妹写的，她起初发给我的是这样的：

  1. import os
  2. import re
  3. import traceback
  4.  
  5. import pandas as pd
  6.  
  7. pattern = r"[`']([^`']+)[`']"
  8. tradition_ = 'COMMENT'
  9. common_part = ' STRING COMMENT '
  10.  
  11.  
  12. def modify_row(row):
  13.     sql_row = row[0]
  14.     if 'CREATE TABLE' in sql_row:
  15.         return row
  16.     if 'COMMENT=' in sql_row:
  17.         comment = sql_row.split('COMMENT=')[-1].replace(';', '').replace(' ', '')
  18.         cut_sql = ')COMMENT ' + comment + ' PARTITIONED BY (As BIGINT) STORED AS PARQUET;'
  19.         row[0] = cut_sql
  20.         return row
  21.     cut_result = re.findall(pattern, sql_row)
  22.     if len(cut_result) > 1 and tradition_ in sql_row:
  23.         cut_sql = cut_result[0] + common_part + f"'{cut_result[-1]}'" + ','
  24.         row[0] = cut_sql
  25.     return row
  26.  
  27.  
  28. current_dir = os.getcwd()
  29. to_dir = os.path.join(current_dir, "results_dir")
  30. print('current_dir:', current_dir)
  31. print('results_dir:', to_dir)
  32. if not os.path.exists(to_dir):
  33.     os.mkdir(to_dir)
  34. try:
  35.     for file in os.listdir(current_dir):
  36.         if file.endswith(".xlsx") or file.endswith(".xls"):
  37.             file_path = os.path.join(current_dir, file)
  38.             to_file_path = os.path.join(to_dir, file)
  39.             df = pd.read_excel(file_path)
  40.             df.apply(modify_row, axis=1)
  41.             df.to_csv(to_file_path, index=False)
  42. except:
  43.     print(traceback.format_exc())

还给我解释了一下，真的太有爱了，啊哈哈哈哈

不过这个转换完是下面这样：

  1. CREATE TABLE `ai_warning_info` (
  2. id STRING COMMENT '主键',
  3. community_id STRING COMMENT ' COMMENT ',
  4. ai_warning_config_id STRING COMMENT '预警信息配置表主键id',
  5. warning_status STRING COMMENT '预警状态(1:待处理；2:已恢复；3:已处理（后台预警管理操作 直接处理/生成工单，则这条预警信息变成已处理）)',
  6. warning_content STRING COMMENT ' COMMENT ',
  7. warning_rank STRING COMMENT '预警级别(1:严重;2:重要;3:一般)',
  8. warning_time STRING COMMENT '预警时间',
  9. intime STRING COMMENT '写入时间',
  10. update_time STRING COMMENT '更新时间',
  11. remark STRING COMMENT ' COMMENT ',
  12. operate_type STRING COMMENT '预警处理方式1:直接处理；2:生成工单',
  13. operate_user_id STRING COMMENT ' COMMENT ',
  14. operate_user_name STRING COMMENT ' COMMENT ',
  15. operate_time STRING COMMENT '预警信息处理时间',
  16. order_id STRING COMMENT ' COMMENT ',
  17. order_status STRING COMMENT '工单状态 0缺省 1待接收 2处理中 3已处理 4已完成 5已关闭 6待处理',
  18. recovery_time STRING COMMENT '恢复时间',
  19. warning_trigger_id STRING COMMENT ' COMMENT ',
  20. warning_type STRING COMMENT '预警事件类型(1离园预警 2车辆违停 3消防通道占用)',
  21. work_order_identity STRING COMMENT '新旧工单标识字段 1：老工单，2：新工单',
  22.   PRIMARY KEY (`id`) USING BTREE,
  23.   UNIQUE KEY `id_UNIQUE` (`id`) USING BTREE
  24. )COMMENT 'ai预警信息' PARTITIONED BY (As BIGINT) STORED AS PARQUET;

然而。。。。

行吧 对齐看着也比较舒服

于是和姐妹吐槽，我在手动对齐

最后实在受不了了，改了姐妹的代码

对齐大概思路就是设置一个最大值减去字段长度再乘以空格，就可以对齐啦

顺便按照自己的需求又照猫画虎的改了下

  1. import os
  2. import re
  3. import traceback
  4.  
  5. import pandas as pd
  6.  
  7. pattern = r"[`']([^`']+)[`']"
  8. tradition_ = 'COMMENT'
  9. common_part = ' STRING COMMENT '
  10. #定义一个空字符
  11. space=' '
  12.  
  13. def modify_row(row):
  14.     sql_row = row[0]
  15.     if 'CREATE TABLE' in sql_row:
  16.         table = sql_row.split('`')[1]  
  17.         # 顺便把ods规范表名也拼好
  18.         cut_sql = 'CREATE TABLE IF NOT EXISTS ' + 'ods_wspace_'+table.replace(' ','') +'_df ('
  19.         row[0] = cut_sql 
  20.         return row
  21.     # 去掉不符合要求的语句行
  22.     if 'USING BTREE' in sql_row:
  23.         cut_sql =' '
  24.         row[0] = cut_sql 
  25.         return row
  26.     # 因为公司同步都要求string 就方便很多 直接按字符串类型拼
  27.     if 'COMMENT=' in sql_row:
  28.         comment = sql_row.split('COMMENT=')[-1].replace(';', '').replace(' ', '')
  29.         cut_sql = ')'+'\n'+'COMMENT ' + comment +'\n'+ 'PARTITIONED BY ( ds BIGINT )'+'\n'+'STORED AS PARQUET;'
  30.         row[0] = cut_sql
  31.         return row
  32.     cut_result = re.findall(pattern, sql_row)
  33.  
  34.     # 通过字段长度 对齐语句 
  35.     cut_len=len(cut_result[0])
  36.     # print('cut_len:',cut_len)  
  37.     if len(cut_result) > 1 and tradition_ in sql_row:
  38.         cut_sql = 5*space+','+cut_result[0] +(30-cut_len)*space+common_part +space+ f"'{cut_result[-1]}'" 
  39.         row[0] = cut_sql
  40.     return row
  41.  
  42.  
  43. current_dir = os.getcwd()
  44. to_dir = os.path.join(current_dir, "results_dir")
  45. print('current_dir:', current_dir)
  46. print('results_dir:', to_dir)
  47. if not os.path.exists(to_dir):
  48.     os.mkdir(to_dir)
  49. try:
  50.     for file in os.listdir(current_dir):
  51.         if file.endswith(".xlsx") or file.endswith(".xls"):
  52.             file_path = os.path.join(current_dir, file)
  53.             to_file_path = os.path.join(to_dir, file)
  54.             df = pd.read_excel(file_path)
  55.             df.apply(modify_row, axis=1)
  56.             df.to_csv(to_file_path, index=False)
  57. except:
  58.     print(traceback.format_exc())

姐妹说用pandas库 可能有bug 但她迟迟不帮我优化 反正我们一致认同能实现功能的代码就是好代码 哈哈哈哈哈 坐等她再帮我换个库写 反正 这段时间不用手敲建表语句了 开心开心

为啥说用一行代码实现呢，因为

命令行。。。 被骗了 我是标题党

后记

时隔一年没有跟新了 确实平常很躺平  工作中除了sql就是sql 很少学新的内容  就没有产出了 希望之后能多多更新文章吧 这篇就是一个好的开端   

by the way 今天是我入职满一年 下班下班 出去嗨喽

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号