• 数据库：Hive转Presto（五）

    此篇将所有代码都补充完了，之前发现有的代码写错了，以这篇为准，以下为完整代码，如果发现我有什么考虑不周的地方，可以评论提建议，感谢。代码是想哪写哪，可能比较繁琐，还需要优化。

    1. import re
    2. import os
    3. import tkinter.filedialog
    4. from tkinter import *
    5.  
    6.  
    7. class Hive2Presto:
    8.     def __int__(self):
    9.         self.t_funcs = ['substr', 'nvl', 'substring', 'unix_timestamp'] + \
    10.                        ['to_date', 'concat', 'sum', 'avg', 'abs', 'year', 'month', 'ceiling', 'floor']
    11.         self.time_funcs = ['date_add', 'datediff', 'add_months', 'date_sub']
    12.         self.funcs = self.t_funcs + self.time_funcs
    13.         self.current_path = os.path.abspath(__file__)
    14.         self.dir = os.path.dirname(self.current_path)
    15.         self.result = []
    16.         self.error = []
    17.         self.filename = ''
    18.  
    19.     def main(self):
    20.         self.root = Tk()
    21.         self.root.config(bg='#ff741d')  # 背景颜色设置为公司主题色^_^
    22.         self.root.title('Hive转Presto')
    23.         self.win_width = 550
    24.         self.win_height = 500
    25.         self.screen_width = self.root.winfo_screenwidth()
    26.         self.screen_height = self.root.winfo_screenheight()
    27.         self.x = (self.screen_width - self.win_width) // 2
    28.         self.y = (self.screen_height - self.win_height) // 2
    29.         self.root.geometry(f'{self.win_width}x{self.win_height}+{self.x}+{self.y}')
    30.  
    31.         font = ('楷体', 11)
    32.  
    33.         self.button = Button(self.root, text='转换', command=self.trans, bg='#ffcc8c', font=font, anchor='e')
    34.         self.button.grid(row=0, column=0, padx=100, pady=10, sticky=W)
    35.  
    36.         self.file_button = Button(self.root, text='选择文件', command=self.choose_file, bg='#ffcc8c', font=font,
    37.                                   anchor='e')
    38.         self.file_button.grid(row=0, column=1, padx=0, pady=10, sticky=W)
    39.  
    40.         self.entry = Entry(self.root, width=65, font=font)
    41.         self.entry.insert(0, '输入Hive代码')
    42.         self.entry.grid(row=1, column=0, padx=10, pady=10, columnspan=2)
    43.         self.entry.bind('', self.delete_text)
    44.  
    45.         self.text = Text(self.root, width=75, height=20)
    46.         self.text.grid(row=2, column=0, padx=10, pady=10, columnspan=2)
    47.  
    48.         self.des_label = Label(self.root, text='可以复制结果，也有生成的文件，与选取的文件同文件夹', bg='#ffcc8c',
    49.                                font=('楷体', 10))
    50.         self.des_label.grid(row=3, column=0, padx=10, pady=10, columnspan=2)
    51.  
    52.         s = ''
    53.         for i in range(0, (n := len(self.funcs)), 4):
    54.             if i + 4 <= n:
    55.                 s += ','.join(self.funcs[i:i + 4]) + '\n'
    56.             else:
    57.                 s += ','.join(self.funcs[i:]) + '\n'
    58.         s = s[:-1]
    59.         self.des_label1 = Label(self.root, text=s, bg='#ffcc8c',
    60.                                 font=('楷体', 10))
    61.         self.des_label1.grid(row=4, column=0, padx=10, pady=10, columnspan=2)
    62.  
    63.         self.root.columnconfigure(0, minsize=10)
    64.         self.root.columnconfigure(1, minsize=10)
    65.         self.root.columnconfigure(0, pad=5)
    66.  
    67.         self.root.mainloop()
    68.  
    69.     def replace_func(self, s, res):
    70.         """
    71.         把搜索到函数整体取出来，处理括号中的参数
    72.         :param s:
    73.         :param res:
    74.         :return:
    75.         """
    76.         for f in res:
    77.             f1 = f.replace('\n', '').strip()
    78.             f1 = re.sub(r'(\(s*)', '(', f1)
    79.             # 搜索括号里的字符串
    80.             if re.findall(r'(\w+)\(', f1):
    81.                 func_name = re.findall(r'(\w+)\(', f1)[0].strip()
    82.             else:
    83.                 continue
    84.             try:
    85.                 if 'date_add' == func_name.lower():
    86.                     date, date_num = self.extact_func(f1, func_name)
    87.                     s_n = f"date_add('day',{date_num},cast(substr(cast{date} as varchar,1,10) as date))"
    88.                     s = s.replace(f, s_n)
    89.                 elif 'datediff' == func_name.lower():
    90.                     date1, date2 = self.extact_func(f1, func_name)
    91.                     s_n = f"date_add('day',{date2},cast(substr(cast{date1} as varchar,1,10) as date),cast(substr(cast{date1} as varchar),1,10) as date))"
    92.                     s = s.replace(f, s_n)
    93.                 elif 'nvl' == func_name.lower():
    94.                     s1, s2 = self.extact_func(f1, func_name)
    95.                     s_n = f"coalesce({s1},{s2})"
    96.                     s = s.replace(f, s_n)
    97.                 elif 'substr' == func_name.lower():
    98.                     date, start, end = self.extact_func(f1, func_name)
    99.                     s_n = f"substr(cast({date} as varchar),{start},{end}"
    100.                     s = s.replace(f, s_n)
    101.                 elif 'substring' == func_name.lower():
    102.                     date, start, end = self.extact_func(f1, func_name)
    103.                     s_n = f"substring(cast({date} as varchar),{start},{end}"
    104.                     s = s.replace(f, s_n)
    105.                 elif 'unit_timestamp' == func_name.lower():
    106.                     date = self.extact_func(f1, func_name)[0]
    107.                     s_n = f"to_unixtime(cast({date} as timestanp))"
    108.                     s = s.replace(f, s_n)
    109.                 elif 'to_date' == func_name.lower():
    110.                     date = self.extact_func(f1, func_name)[0]
    111.                     s_n = f"cast({date} as date)"
    112.                     s = s.replace(f, s_n)
    113.                 elif 'concat' == func_name.lower():
    114.                     res = self.extact_func(f1, func_name)[0]
    115.                     s_n = f'concat('
    116.                     for r in res:
    117.                         r = r.strip().replace('\n', '')
    118.                         s_n += f"cast({r} as varchar),"
    119.                     s_n = s_n[:-1] + ')'
    120.                     s = s.replace(f, s_n)
    121.                 elif 'sum' == func_name.lower():
    122.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    123.                         continue
    124.                     ss = self.extact_func(f1, func_name)[0]
    125.                     if 'if(' in ss.replace(' ', ''):
    126.                         continue
    127.                     s = self.func_trans(f, f1, func_name, ss, s)
    128.                 elif 'avg' == func_name.lower():
    129.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    130.                         continue
    131.                     ss = self.extact_func(f1, func_name)[0]
    132.                     if 'if(' in ss.replace(' ', ''):
    133.                         continue
    134.                     s = self.func_trans(f, f1, func_name, ss, s)
    135.                 elif 'abs' == func_name.lower():
    136.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    137.                         continue
    138.                     ss = self.extact_func(f1, func_name)[0]
    139.                     if 'if(' in ss.replace(' ', ''):
    140.                         continue
    141.                     s = self.func_trans(f, f1, func_name, ss, s)
    142.                 elif 'ceiling' == func_name.lower():
    143.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    144.                         continue
    145.                     ss = self.extact_func(f1, func_name)[0]
    146.                     if 'if(' in ss.replace(' ', ''):
    147.                         continue
    148.                     s = self.func_trans(f, f1, func_name, ss, s)
    149.                 elif 'floor' == func_name.lower():
    150.                     if 'unix_timestamp' in f1 or 'to_unixtime' in f1:
    151.                         continue
    152.                     ss = self.extact_func(f1, func_name)[0]
    153.                     if 'if(' in ss.replace(' ', ''):
    154.                         continue
    155.                     s = self.func_trans(f, f1, func_name, ss, s)
    156.                 elif 'year' == func_name.lower():
    157.                     date = self.extact_func(f1, func_name)[0]
    158.                     s_n = f"year(cast(substr(cast({date} as varchar,1,10) as date))"
    159.                     s = s.replace(f, s_n)
    160.                 elif 'month' == func_name.lower():
    161.                     date = self.extact_func(f1, func_name)[0]
    162.                     s_n = f"month(cast(substr(cast({date} as varchar,1,10) as date))"
    163.                     s = s.replace(f, s_n)
    164.                 elif 'date_sub' == func_name.lower():
    165.                     date, date_num = self.extact_func(f1, func_name)
    166.                     s_n = f"date_add('day',-{date_num},cast(substr(cast{date} as varchar,1,10) as date))"
    167.                     s = s.replace(f, s_n)
    168.             except:
    169.                 self.error.append(f"源代码中{func_name}函数参数输入可能有错误，具体为：{f1}")
    170.                 continue
    171.         if self.error:
    172.             self.entry.delete(0, END)
    173.             self.text.delete("1.0", END)
    174.             self.text.insert("end", f"{s}")
    175.             self.error.insert(0, '转换失败，有部分没有转成功\n')
    176.  
    177.             root_ex = Tk()
    178.             root_ex.title('错误')
    179.             win_width = 600
    180.             win_height = 200
    181.             screen_width = root_ex.winfo_screenwidth()
    182.             screen_height = root_ex.winfo_screenheight()
    183.             x = (screen_width - win_width) // 2
    184.             y = (screen_height - win_height) // 2
    185.             root_ex.geometry(f'{win_width}x{win_height}+{x}+{y}')
    186.             label_ex = Label(root_ex, text="\n".join(self.error), font=("楷体", 10))
    187.             label_ex.pack()
    188.             root_ex.mainloop()
    189.  
    190.         return s
    191.  
    192.     def func_trans(self, f, f1, func_name, ss, s):
    193.         if not ('+' in ss or '-' in ss or '*' in ss or '/' in ss):
    194.             date = self.extact_func(f1, func_name)[0]
    195.             s_n = f'{func_name}(cast{date} as double))'
    196.             s = s.replace(f, s_n)
    197.         else:
    198.             res1 = self.mysplit(f1)
    199.             s_n = f
    200.             n = len(s_n)
    201.             for item in res1:
    202.                 if any(c.isalpha() for c in item.replace(' ', '')):
    203.                     idxs = s_n.find(item)
    204.                     idxs = [idxs] if type(idxs) != list else idxs
    205.                     for idx in idxs:
    206.                         if idx + len(item) + 3 <= n:
    207.                             if not 'as' in s_n[idx:idx + len(item) + 4]:
    208.                                 s_n = re.sub(rf'\b{item}\b', f'cast({item} as double)', s_n)
    209.                         else:
    210.                             s_n = re.sub(rf'\b{item}\b', f'cast({item} as double)', s_n)
    211.                     s = s.replace(f, s_n)
    212.         return s
    213.  
    214.     def choose_file(self):
    215.         """
    216.         如果代码太多，从text中输入会很卡，直接选择代码文件输入会很快
    217.         :return:
    218.         """
    219.         self.filename = tkinter.filedialog.askopenfilename()
    220.         if '/' in self.filename:
    221.             self.filename = self.filename.replace('/', '\\')
    222.         self.entry.delete(0, END)
    223.         self.entry.insert(0, self.filename)
    224.  
    225.     def findvar(self, ss):
    226.         """
    227.         搜索与计算有关的字段
    228.         :param ss:
    229.         :return:
    230.         """
    231.         global r1
    232.         b = ['+', '-', '*', '/', '=', '!=', '>', '<', '<=', '>=', '<>']
    233.         result1 = []
    234.         result2 = []
    235.         result1_n = []
    236.         result2_n = []
    237.         res_ops = []
    238.         res1_ops = []
    239.         res_adj = []
    240.         res1_adj = []
    241.         for op in b:
    242.             s_temp1 = ss.replace('\n', ' ')
    243.             s_temp2 = ss.replace('\n', ' ')
    244.             s_temp3 = ss.replace('\n', ' ')
    245.             if op == '/' or op == '=':
    246.                 op = op
    247.             elif op == '+' or op == '-' or op == '*' or op == '>' or op == '<':
    248.                 op = f'\\{op[0]}'
    249.             else:
    250.                 op = f'\\{op[0]}\\{op[1]}'
    251.             parttern = f'\s*-*\d+\s*{op}\s*\w+|' + f'\s*-*\d+\.\s*{op}\s*\w+\.\s*\w+|' \
    252.                        + f'\s*\w+\.\s*\w+\s*{op}\s*\w+\.\s*\w+|' + f'\s*\w+\s*{op}\s*\w+\.\s*\w+|' \
    253.                        + f'\s*\w+\.\s*\w+\s*{op}\s*\w+|' + f'\s*\w+\s*{op}\s*\w+'
    254.             parttern1 = f'\s*\)+\s*{op}\s*\w+|' + f'\s*\)+\s*{op}\s*\w+\.\s*\w+|' \
    255.                         + f'\s*\w+\s*{op}\s*\(+|' + f'f\s*\w+\.\s*{op}\s*\(+'
    256.             parttern2 = f'\s*\w+\s*{op}\s*\w+|' + f'\s*\w+\s*{op}\s*\w+\.\s*\w+|' \
    257.                         + f'\s*\w+\s*{op}\s*\w+|' + f'f\s*\w+\.\s*{op}\s*\w+'
    258.             while True:
    259.                 res = re.findall(parttern, s_temp1)
    260.                 if not res:
    261.                     break
    262.                 result2.extend(res)
    263.                 for r in res:
    264.                     r1 = r.replace(' ', '').split(f'op')
    265.                     result1.append(r1)
    266.                     res_ops.append(f'{op}')
    267.                     res_adj.append(False)
    268.                 s_temp1 = s_temp1.replace(f'{r1[0]}', '')
    269.  
    270.             # 搜索带括号的计算
    271.             if op == '+' or op == '-' or op == '*' or op == '/':
    272.                 while True:
    273.                     res = re.findall(parttern1, s_temp2)
    274.                     if not res:
    275.                         break
    276.                     result2.extend(res)
    277.                     for r in res:
    278.                         r1 = r.replace(' ', '').split(f'{op}')
    279.                         result1.append(r1)
    280.                         res_ops.append(f'{op}')
    281.                         res_adj.append(False)
    282.                     tem = r1[0] if r1[0].strip() not in ['(', ')'] else r1[1]
    283.                     s_temp2 = s_temp2.replace(f'{tem}', '')
    284.             else:
    285.                 res = re.findall(parttern2, s_temp3)
    286.                 result2.extend(res)
    287.                 for r in res:
    288.                     r1 = r.replace(' ', '').split(f'{op}')
    289.                     result1.append(r1)
    290.                     res_ops.append(f'{op}')
    291.                     res_adj.append(True)
    292.  
    293.             str_ = re.findall(r'\'([^\']*)\'', ss)
    294.             str_ = list(set(str_))
    295.             str_ = [v.rstrip(' \n') for v in str_]
    296.             for i, fun in enumerate(result1):
    297.                 flag = 0
    298.                 for item in fun:
    299.                     if any(item.strip() in v for v in str_) or any(item.strip() == v for v in self.t_funcs):
    300.                         break
    301.                     flag += 1
    302.                     if flag == 2 and result1[i] not in result1_n:
    303.                         result1_n.append(result1[i])
    304.                         result2_n.append(result2[i])
    305.                         res1_ops.append(res_ops[i])
    306.                         adj = result1[i][0] in self.time_funcs or result1[i][0] in self.time_funcs
    307.                         res1_adj.append(adj)
    308.             if result1_n:
    309.                 z = zip(result1_n, result2_n, res1_ops, res1_adj)
    310.                 z1 = sorted(z, key=lambda x: len(x[1].replace(' ', '')), reverse=True)
    311.                 result1_n, result2_n, res1_ops, res1_adj = zip(*z1)
    312.             return result1_n, result2_n, res1_ops, res1_adj
    313.  
    314.     def mysplit(self, s):
    315.         """
    316.         分割字段
    317.         :param s:
    318.         :return:
    319.         """
    320.         s = s.strip().replace(')', '').replace('(', '')
    321.         b = ['+', '-', '*', '/']
    322.         res = [s]
    323.         result = []
    324.         for op in b:
    325.             n_res = []
    326.             for item in res:
    327.                 n_res.extend(item.split(op))
    328.             res = n_res
    329.         for item in res:
    330.             if ' as ' not in item:
    331.                 result.append(re.findall(r'^[\w+_*]+$', item.replace(' ', ''))[0])
    332.         result = list(set(res))
    333.         return result
    334.  
    335.     def extact_func(self, s, func_name):
    336.         res = []
    337.         s = s[:-1].replace(f'{func_name}(', '', 1)
    338.         com_idx = [i for i, v in enumerate(s) if v == ',']
    339.         jd_com_idx = []
    340.         for i in com_idx:
    341.             s1 = s[0:i]
    342.             if s1.count('(') == s1.count(')'):
    343.                 jd_com_idx.append(i)
    344.         jd_com_idx.append(len(s))
    345.         jd_com_idx.insert(0, -1)
    346.         for i in range(1, len(jd_com_idx)):
    347.             res.append(s[jd_com_idx[i - 1] + 1:jd_com_idx[i]])
    348.         return res
    349.  
    350.     def sort_funcs(self, li):
    351.         li = sorted(li, key=lambda x: x.count('('), reverse=True)
    352.         li_n = []
    353.         for l in li:
    354.             li_n.append(l)
    355.         return li_n
    356.  
    357.     def delete_text(self, event):
    358.         self.entry.delete(0, END)
    359.         self.filename = ''
    360.  
    361.     def trans(self):
    362.         if self.filename:
    363.             data = open(self.filename, 'r', encoding='utf-8').readlines()
    364.             self.folder_path = os.path.dirname(self.filename)
    365.             file_res = self.folder_path + r'\hive转presto_res.sql'
    366.             os.startfile(f'{self.folder_path}')
    367.         else:
    368.             data = self.entry.get().split('\n')
    369.             file_res = self.dir + r'\hive转presto_res.sql'
    370.         data_n = []
    371.         for s in data:
    372.             if not s.rstrip(' \n'):
    373.                 continue
    374.             if '”' in s:
    375.                 s = s.replace('“', '')
    376.             if '，' in s:
    377.                 s = s.replace('，', ',')
    378.             if '（' in s:
    379.                 s = s.replace('（', '(')
    380.             if '）' in s:
    381.                 s = s.replace('）', ')')
    382.             if (idx := s.find('--')) == -1:
    383.                 data_n.append(s + '\n')
    384.             else:
    385.                 data_n.append(s[:idx] + '\n')
    386.  
    387.         data = ''.join(data_n)
    388.         res1, res2, ops, adj = self.findvar(data)
    389.         for i, ss in enumerate(res1):
    390.             s_n = res2[i]
    391.             s_n1 = res2[i]
    392.             s_n2 = res2[i]
    393.             s_t = res2[i]
    394.             flag = 0
    395.             for elem in ss:
    396.                 elem1 = elem.replace(' ', '')
    397.                 if any(c.isalpha() for c in elem1):
    398.                     if ops[i] in ['=', '!=', '>', '<', '<=', '>=', '<>']:
    399.                         if adj[i]:
    400.                             if elem1 not in self.time_funcs:
    401.                                 s_n = re.sub(rf'\b{elem}\b', f'cast(substr({elem1},1,10) as date', s_n)
    402.                                 continue
    403.                             else:
    404.                                 continue
    405.                         if any(op in s_t for op in ['+', '-', '*', '/']):
    406.                             s_n = re.sub(rf'\b{elem}\b', f'cast({elem1} as double)', s_n)
    407.                         else:
    408.                             s_n = re.sub(rf'\b{elem}\b', f'cast({elem1} as varchar)', s_n)
    409.                     else:
    410.                         if elem.strip() not in ['(', ')']:
    411.                             s_n = re.sub(rf'\b{elem}\b', f'cast({elem1} as double)', s_n)
    412.                     flag += 1
    413.             data = data.replace(res2[i].strip(), s_n)
    414.             if flag == 2:
    415.                 if any(op in s_t for op in ['+', '-', '*', '/']):
    416.                     s_n1 = re.sub(rf'\b{ss[0]}\b', f'cast({ss[0]} as double)', s_n)
    417.                     s_n2 = re.sub(rf'\b{ss[1]}\b', f'cast({ss[1]} as double)', s_n)
    418.                 else:
    419.                     s_n1 = re.sub(rf'\b{ss[0]}\b', f'cast({ss[0]} as varchar)', s_n)
    420.                     s_n2 = re.sub(rf'\b{ss[1]}\b', f'cast({ss[1]} as varchar)', s_n)
    421.                 data = data.replace(s_n1, s_n)
    422.                 data = data.replace(s_n2, s_n)
    423.  
    424.         self.error = []
    425.         self.result = []
    426.         for func_name in self.funcs:
    427.             r = [m.start() for m in re.finditer(func_name, data.lower())]
    428.             for idx in r:
    429.                 n = 1
    430.                 while True:
    431.                     s = data[idx:idx + n]
    432.                     if (s.count(')') == s.count('(') and s.count(')') != 0) and idx + n > len(data):
    433.                         break
    434.                     n += 1
    435.                 if s not in self.result and s.rstrip(' \n')[len(func_name)] == '(':
    436.                     self.result.append(s)
    437.         self.result = self.sort_funcs(self.result)
    438.         res = self.replace_func(data, self.result)
    439.  
    440.         res_new = []
    441.         for r in res.split('\n'):
    442.             if r.rstrip(' \n'):
    443.                 res_new.append(r)
    444.         res_new = '\n'.join(res_new)
    445.         self.text.delete("1.0", END)
    446.         self.text.insert("end", f'{res_new}')
    447.         with open(file_res, 'w', encoding='utf-8') as f:
    448.             f.write(res_new)
    449.  
    450.  
    451. if __name__ == '__main__':
    452.     pro = Hive2Presto()
    453.     pro.__int__()
    454.     pro.main()

    效果如下所示：

  • 相关阅读:
    Numerical Calculation 数值计算
    MySQL约束之check
    Rethinking the Inception Architecture for Computer Vision--Christian Szegedy
    Ubuntu 20.04上安装Datalog Disassembly工具的过程
    python自我学习 二 05 下载图片链接
    Springboot 基本概念
    《Unity3D高级编程之进阶主程》第一章 C#要点技术(五) 排序算法
    关于Synchronized你了解多少？
    MySQL常用语句一章解读数据库的底层原理
    【app篇】写个简单的BLE调试app，练练手，同时为后续调试ESP32 BLE做个支持
  • 原文地址：https://blog.csdn.net/Sanfenpai6/article/details/133804941