• 数据库：Hive转Presto（二）

    继续上节代码，补充了replace_func函数，

    1. import re
    2. import os
    3. from tkinter import *
    4.  
    5.  
    6. class Hive2Presto:
    7.     def __int__(self):
    8.         self.t_funcs = ['substr', 'nvl', 'substring', 'unix_timestamp'] + \
    9.                        ['to_date', 'concat', 'sum', 'avg', 'abs', 'year', 'month', 'ceiling', 'floor']
    10.         self.time_funcs = ['date_add', 'datediff', 'add_months']
    11.         self.funcs = self.t_funcs + self.time_funcs
    12.         self.current_path = os.path.abspath(__file__)
    13.         self.dir = os.path.dirname(self.current_path)
    14.         self.result = []
    15.         self.error = []
    16.         self.filename = ''
    17.  
    18.     def main(self):
    19.         self.root = Tk()
    20.         self.root.config(bg='#ff741d')  # 背景颜色设置为公司主题色^_^
    21.         self.root.title('Hive转Presto')
    22.         self.win_width = 550
    23.         self.win_height = 500
    24.         self.screen_width = self.root.winfo_screenwidth()
    25.         self.screen_height = self.root.winfo_screenheight()
    26.         self.x = (self.screen_width - self.win_width) // 2
    27.         self.y = (self.screen_height - self.win_height) // 2
    28.         self.root.geometry(f'{self.win_width}x{self.win_height}+{self.x}+{self.y}')
    29.  
    30.         font = ('楷体', 11)
    31.  
    32.         self.button = Button(self.root, text='转换', command=self.trans, bg='#ffcc8c', font=font, anchor='e')
    33.         self.button.grid(row=0, column=0, padx=100, pady=10, sticky=W)
    34.  
    35.         self.file_button = Button(self.root, text='选择文件', command=self.choose_file, bg='#ffcc8c', font=font,
    36.                                   anchor='e')
    37.         self.file_button.grid(row=0, column=1, padx=0, pady=10, sticky=W)
    38.  
    39.         self.entry = Entry(self.root, width=65, font=font)
    40.         self.entry.insert(0, '输入Hive代码')
    41.         self.entry.grid(row=1, column=0, padx=10, pady=10, columnspan=2)
    42.         self.entry.bind('', self.delete_text)
    43.  
    44.         self.text = Text(self.root, width=75, height=20)
    45.         self.text.grid(row=2, column=0, padx=10, pady=10, columnspan=2)
    46.  
    47.         self.des_label = Label(self.root, text='可以复制结果，也有生成的文件，与选取的文件同文件夹', bg='#ffcc8c',
    48.                                font=('楷体', 10))
    49.         self.des_label.grid(row=3, column=0, padx=10, pady=10, columnspan=2)
    50.  
    51.         s = ''
    52.         for i in range(0, (n := len(self.funcs)), 4):
    53.             if i + 4 <= n:
    54.                 s += ','.join(self.funcs[i:i + 4]) + '\n'
    55.             else:
    56.                 s += ','.join(self.funcs[i:]) + '\n'
    57.         s = s[:-1]
    58.         self.des_label1 = Label(self.root, text=s, bg='#ffcc8c',
    59.                                 font=('楷体', 10))
    60.         self.des_label1.grid(row=4, column=0, padx=10, pady=10, columnspan=2)
    61.  
    62.         self.root.columnconfigure(0, minsize=10)
    63.         self.root.columnconfigure(1, minsize=10)
    64.         self.root.columnconfigure(0, pad=5)
    65.  
    66.         self.root.mainloop()
    67.  
    68.     def replace_func(self, s, res):
    69.         """
    70.         把搜索到函数整体取出来，处理括号中的参数
    71.         :param s: 
    72.         :param res: 
    73.         :return: 
    74.         """
    75.         for f in res:
    76.             f1 = f.replace('\n', '').strip()
    77.             f1 = re.sub(r'(\s*)', '(', f1)
    78.             # 搜索括号里的字符串
    79.             if re.findall(r'(\w*)\(', f1):
    80.                 func_name = re.findall(r'(\w*)\(', f1)[0].strip()
    81.             else:
    82.                 continue
    83.             try:
    84.                 if 'date_add' == func_name.lower():
    85.                     date, date_num = self.extact_func(f1, func_name)
    86.                     s_n = f"date_add('day',{date_num},cast(substr(cast{date} as varchar,1,10) as date))"
    87.                     s = s.replace(f, s_n)
    88.                 elif 'datediff' == func_name.lower():
    89.                     date1, date2 = self.extact_func(f1, func_name)
    90.                     s_n = f"date_add('day',{date2},cast(substr(cast{date} as varchar,1,10) as date),cast(substr(cast{date1} as varchar),1,10) as date))"
    91.                     s = s.replace(f, s_n)
    92.                 elif 'nvl' == func_name.lower():
    93.                     s1, s2 = self.extact_func(f1, func_name)
    94.                     s_n = f"coalesce({s1},{s2})"
    95.                     s = s.replace(f, s_n)
    96.                 elif 'substr' == func_name.lower():
    97.                     date, start, end = self.extact_func(f1, func_name)
    98.                     s_n = f"substr(cast({date} as varchar),{start},{end}"
    99.                     s = s.replace(f, s_n)
    100.                 elif 'substring' == func_name.lower():
    101.                     date, start, end = self.extact_func(f1, func_name)
    102.                     s_n = f"substring(cast({date} as varchar),{start},{end}"
    103.                     s = s.replace(f, s_n)
    104.                 elif 'unit_timestamp' == func_name.lower():
    105.                     date = self.extact_func(f1, func_name)[0]
    106.                     s_n = f"to_unixtime(cast({date} as timestanp))"
    107.                     s = s.replace(f, s_n)
    108.                 elif 'to_date' == func_name.lower():
    109.                     date = self.extact_func(f1, func_name)[0]
    110.                     s_n = f"cast({date} as date)"
    111.                     s = s.replace(f, s_n)
    112.                 elif 'concat' == func_name.lower():
    113.                     res = self.extact_func(f1, func_name)[0]
    114.                     s_n = f'concat('
    115.                     for r in res:
    116.                         r = r.strip().replace('\n', '')
    117.                         s_n += f"cast({r} as varchar),"
    118.                     s_n = s_n[:-1] + ')'
    119.                     s = s.replace(f, s_n)
    120.                 elif 'sum' == func_name.lower():
    121.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    122.                         continue
    123.                     ss = self.extact_func(f1, func_name)[0]
    124.                     if 'if(' in ss.replace(' ', ''):
    125.                         continue
    126.                     s = self.func_trans(f, f1, func_name, ss, s)
    127.                 elif 'avg' == func_name.lower():
    128.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    129.                         continue
    130.                     ss = self.extact_func(f1, func_name)[0]
    131.                     if 'if(' in ss.replace(' ', ''):
    132.                         continue
    133.                     s = self.func_trans(f, f1, func_name, ss, s)
    134.                 elif 'abs' == func_name.lower():
    135.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    136.                         continue
    137.                     ss = self.extact_func(f1, func_name)[0]
    138.                     if 'if(' in ss.replace(' ', ''):
    139.                         continue
    140.                     s = self.func_trans(f, f1, func_name, ss, s)
    141.                 elif 'ceiling' == func_name.lower():
    142.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    143.                         continue
    144.                     ss = self.extact_func(f1, func_name)[0]
    145.                     if 'if(' in ss.replace(' ', ''):
    146.                         continue
    147.                     s = self.func_trans(f, f1, func_name, ss, s)
    148.                 elif 'floor' == func_name.lower():
    149.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    150.                         continue
    151.                     ss = self.extact_func(f1, func_name)[0]
    152.                     if 'if(' in ss.replace(' ', ''):
    153.                         continue
    154.                     s = self.func_trans(f, f1, func_name, ss, s)
    155.                 elif 'year' == func_name.lower():
    156.                     date = self.extact_func(f1, func_name)[0]
    157.                     s_n = f"year(cast(substr(cast({date} as varchar,1,10) as date))"
    158.                     s = s.replace(f, s_n)
    159.                 elif 'month' == func_name.lower():
    160.                     date = self.extact_func(f1, func_name)[0]
    161.                     s_n = f"month(cast(substr(cast({date} as varchar,1,10) as date))"
    162.                     s = s.replace(f, s_n)
    163.             except:
    164.                 self.error.append(f"源代码中{func_name}函数参数输入可能有错误，具体为：{f1}")
    165.                 continue
    166.         if self.error:
    167.             self.entry.delete(0,END)
    168.             self.text.delete("1.0",END)
    169.             self.text.insert("end",f"{s}")
    170.             self.error.insert(0,'转换失败，有部分没有转成功\n')
    171.             
    172.             root_ex = Tk()
    173.             root_ex.title('错误')
    174.             win_width = 600
    175.             win_height = 200
    176.             screen_width = root_ex.winfo_screenwidth()
    177.             screen_height = root_ex.winfo_screenheight()
    178.             x = (screen_width - win_width) // 2
    179.             y = (screen_height - win_height) // 2
    180.             root_ex.geometry(f'{win_width}x{win_height}+{x}+{y}')
    181.             label_ex = Label(root_ex,text="\n".join(self.error),font=("楷体",10))
    182.             label_ex.pack()
    183.             root_ex.mainloop()
    184.             
    185.             return s
    186.  
    187.     def func_trans(self, f, f1, func_name, ss, s):
    188.         pass
    189.  
    190.     def choose_file(self):
    191.         """
    192.         如果代码太多，从text中输入会很卡，直接选择代码文件输入会很快
    193.         :return:
    194.         """
    195.         pass
    196.  
    197.     def findvar(self, ss):
    198.         """
    199.         搜索与计算有关的字段
    200.         :param ss:
    201.         :return:
    202.         """
    203.         pass
    204.  
    205.     def mysplit(self, s):
    206.         """
    207.         分割字段
    208.         :param s:
    209.         :return:
    210.         """
    211.         pass
    212.  
    213.     def extact_func(self, s, func_name):
    214.         pass
    215.  
    216.     def delete_text(self, event):
    217.         pass
    218.  
    219.     def trans(self):
    220.         pass
    221.  
    222.  
    223. if __name__ == '__main__':
    224.     pro = Hive2Presto()
    225.     pro.__int__()
    226.     pro.main()

     

     

     

     

  • 相关阅读:
    单例设计模式
    语音识别数据的采集方法：基本流程&数据类型
    刷新页面，数据丢失
    Java EE——JVM基础知识
    MySQL——子查询和嵌套查询
    LocalDate、LocalTime、LocalDateTime常用方法
    高校云原生安全该如何构建？
    济南 章丘 科目三 资料 收集
    【Pytorch学习笔记】9.分类器的分类结果如何评估——使用混淆矩阵、F1-score、ROC曲线、PR曲线等（以Softmax二分类为例）
    【mid】sdp解析：奇怪的vector 引用
  • 原文地址：https://blog.csdn.net/Sanfenpai6/article/details/133387662