纯文本分析方法，从复杂的sql查询语句中提取所有来源表名 |
您所在的位置：网站首页 › sql测试工具用文本吗为什么 › 纯文本分析方法，从复杂的sql查询语句中提取所有来源表名 |
纯文本分析方法，从复杂的sql查询语句中提取所有来源表名
|
如何从复杂的sql查询语句中提取所有来源表名
本文使用Python实现 一、背景前段时间开发了一个小工具用来检测一些sql脚本，其中有一个步骤需要将查询语句的各个来源表都提取出来。本来借助数据库是可以实现的，但该方法有局限性。所以我一直在想，能不能通过纯文本分析的方法来解决这个问题。 网上虽然能找到类似的解决方法，但基本都不是通用的脚本，只能解决某些句式，考虑的场景太少了，想要找到通用的脚本，只能靠自己写了。 最后经过一番费力的分析拆解，终于写出来一个相对来说比较通用的脚本。 二、题外话先说一下借助数据库可以怎么实现。 在oracle中，有个数据字典dba_dependences，可以查出某个对象（包、视图、表）所依赖的表和视图。假如我们想要借助数据库来实现这个功能，那么很简单，只要用sql查询语句先创建一个固定名字的视图，然后查询该视图的依赖的所有表，即可获得该查询语句的所有来源表。 但这种方式有2个弊端： 1、查询语句的来源表，必须是已经在数据库里建好的； 2、如果需要批量处理，频繁地连接数据库建视图然后查询依赖表，这个过程资源开销较大，中间需要等待的时间较长； 三、正题那么如何通过文本分析的方式来解决这个问题呢？ 1、先分析sql句式分析日常我们编写的sql查询语句，大概有哪些句式，通过归纳总结，针对这些句式去分析如何按步骤拆解，简化，统一。 通过分析，可以把日常的sql查询语句归纳为以下的多种句式 一.单表查询语句 --1、单表查询，没有过滤条件，没有分组条件 SELECT [列名] FROM [表名] --2、单表查询，有过滤条件，没有分组条件 SELECT [列名] FROM [表名] WHERE [过滤条件] --3、单表查询，没有过滤条件，有分组条件 SELECT [列名] FROM [表名] GROUP BY [分组条件] --4、单表查询，带子查询 SELECT [列名] FROM ([子查询语句]) GROUP BY [分组条件] 二.多表关联查询 --1、Oracle特有写法 SELECT [列名] FROM [表名1], [表名2], [表名3] WHERE [关联和过滤条件] --2.1、sql标准写法 SELECT [列名] FROM [表名1] JOIN [表名2] ON 表1.XX = 表2.XX --2.2、sql标准写法2 SELECT [列名] FROM [表名1] INNER JOIN [表名2] ON 表1.XX = 表2.XX --2.3、sql标准写法3 SELECT [列名] FROM [表名1] LEFT JOIN [表名2] ON 表1.XX = 表2.XX --2.4、sql标准写法4 SELECT [列名] FROM [表名1] RIGHT JOIN [表名2] ON 表1.XX = 表2.XX 三.多表关联查询嵌套子查询(上述所有情况把表名替换成子查询即可) 四.WITH临时表写法 --1.1、WITH单临时表，单表查询，无过滤条件，无分组条件 WITH TMP AS ( 子查询 ) SELECT [列名] FROM TMP; --1.2、WITH单临时表，单表查询，有过滤条件，无分组条件 WITH TMP AS ( 子查询 ) SELECT [列名] FROM TMP WHERE [过滤条件]; --1.3、WITH单临时表，单表查询，无过滤条件，有分组条件 WITH TMP AS ( 子查询 ) SELECT [列名] FROM TMP GROUP BY [分组条件]; --2、WITH多临时表， 单表查询... WITH TMP1 AS ( 子查询1 ), TMP2 AS ( 子查询2 ) SELECT [列名] FROM TMP1; --3、WITH多临时表， 多表关联查询, ORACLE写法 WITH TMP1 AS ( 子查询1 ), TMP2 AS ( 子查询2 ) SELECT [列名] FROM TMP1, TMP2 WHERE TMP1.XX = TMP2.XX(+) --（+）可有可无 --4、WITH多临时表， 多表关联查询, 标准sql写法 WITH TMP1 AS ( 子查询1 ), TMP2 AS ( 子查询2 ) SELECT [列名] FROM TMP1 JOIN TMP2 --JOIN 可能替换为LEFT JOIN , INNER JOIN , RIGHT JOIN ON TMP1.XX = TMP2.XX(+) 2、拟定处理流程，绘制流程图有了上面的句式，我们就可以思考如何按步骤来识别判断。 经过思考，上述的多种句式可以通过以下流程逐步拆解处理 按照上面的流程来编写，细节处理自己多考虑一下，最终写出的代码如下 #!/usr/bin/env python # -*- coding:utf-8 -*- # date: 2021/6/6 # filename: get_tables_from_sql # author: kplin import re def get_query_sql(file_path): '''读取文本中的sql''' with open(file_path, 'r', encoding='utf-8') as f: # 文本预处理： # 1、打开文件，获取文本内容，转换成大写，去除前后空格，去除换行符 content = f.read().upper().strip(' ').split('\n') content_list = [] for i in content: if not i.startswith('--') and i != '': # 2、去除单行注释 j = i if '--' not in i else i.replace(i, i.split('--')[0]) # 3、去除tab符 content_list.append(j.replace('\t', ' ')) # 4、双空格转为单空格 content = ' '.join(content_list).replace(' ', ' ').replace(' ', ' ') # 5、去除多行注释 comments = re.findall('/\*.*?\*/', content) for i in comments: content = content.replace(i, '') return content def with_query(query_sql): '''传进一个with语句，返回临时表名，子查询和主查询''' # 先把（+） 替换成###++###, 避免干扰判断, 后面再替换回来，把双空格转换成单空格 query_sql = query_sql.replace('(+)', '###++###').replace(' ', ' ').replace(' ', ' ') # 把AS (), 或者AS () , 或者AS ()SELECT 或者 AS () SELECT中间部分提取出来(这里分多个情况处理) tmp_querys = [] tmp_querys1 = re.findall(r'AS \(.*?\),', query_sql) if len(tmp_querys1) != 0: for i in tmp_querys1: query_sql = query_sql.replace(i, '') tmp_querys2 = re.findall(r'AS \(.*?\) ,', query_sql) if len(tmp_querys2) != 0: for i in tmp_querys2: query_sql = query_sql.replace(i, '') tmp_querys3 = re.findall(r'AS \(.*?\) SELECT', query_sql) if len(tmp_querys3) != 0: for i in tmp_querys3: i = i[:-7] query_sql = query_sql.replace(i, '') tmp_querys4 = re.findall(r'AS \(.*?\)SELECT', query_sql) if len(tmp_querys4) != 0: for i in tmp_querys4: i = i[:-6] query_sql = query_sql.replace(i, '') tmp_querys5 = re.findall(r'AS\(.*?\),', query_sql) if len(tmp_querys5) != 0: for i in tmp_querys5: query_sql = query_sql.replace(i, '') tmp_querys6 = re.findall(r'AS\(.*?\) ,', query_sql) if len(tmp_querys6) != 0: for i in tmp_querys6: query_sql = query_sql.replace(i, '') tmp_querys7 = re.findall(r'AS\(.*?\) SELECT', query_sql) if len(tmp_querys7) != 0: for i in tmp_querys7: i = i[:-7] query_sql = query_sql.replace(i, '') tmp_querys8 = re.findall(r'AS\(.*?\)SELECT', query_sql) if len(tmp_querys8) != 0: for i in tmp_querys8: i = i[:-6] query_sql = query_sql.replace(i, '') for i in [tmp_querys1, tmp_querys2, tmp_querys3, tmp_querys4, tmp_querys5, tmp_querys6, tmp_querys7, tmp_querys8]: if len(i) != 0: tmp_querys.extend(i) # 把AS ()中间的子查询提取，获得临时表的查询语句 sub_querys = [] for i in tmp_querys: for j in re.findall(r'AS \((.*?)\),', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS \((.*?)\) ,', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS \((.*?)\) SELECT', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS \((.*?)\)SELECT', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS\((.*?)\),', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS\((.*?)\) ,', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS\((.*?)\) SELECT', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) for j in re.findall(r'AS\((.*?)\)SELECT', i): sub_querys.append(j.strip(' ').replace('###++###', '(+)').replace(' ', ' ').replace(' ', ' ')) # 截取with和第一个select之间的部分，获取临时表名 tmp_names = query_sql[query_sql.index('WITH') + 4 : query_sql.index('SELECT')].strip(' ').split(' ') tmp_names = [i for i in tmp_names if i != ''] tmp_names = [i.strip(' ') for i in tmp_names] # 把剩下的select 到结尾部分截取出来，获得主查询语句 main_query = query_sql[query_sql.index('SELECT'):].strip(' ').replace(' ', ' ').replace(' ', ' ').replace('###++###', '(+)') sub_querys.append(main_query) return (tmp_names, sub_querys) def get_sub_query(query_sql): '''传进一条查询语句，返回主查询和所有子查询''' # 1、先去除(+)的干扰，去除前后空格，双空格替换为单空格 query_sql = query_sql.replace('(+)', '###++###').strip(' ').replace(' ', ' ').replace(' ', ' ') # 2、匹配()内的子查询 sub_querys = re.findall('\((.*?)\)', query_sql) sub_query_list = [] if len(sub_querys) != 0: for i in sub_querys: if ' FROM ' in i: # 括号内有FROM关键字才是子查询，不然函数可能也用括号包围 query_sql = query_sql.replace(i, '') i = i.replace('###++###', '(+)') sub_query_list.append(i) main_query = query_sql.replace('###++###', '(+)') sub_query_list.append(main_query) return sub_query_list def split_join(ret_list): join_list = [' LEFT JOIN ', ' RIGHT JOIN ', ' OUTER JOIN ', ' JOIN '] for i in ret_list: for j in join_list: if j in i: ret_list.remove(i) ret_list.extend(i.split(j)) return split_join(ret_list) return ret_list def get_tables_from_sql(content): # 开始SQL解析流程 with_query_dict = {'tmp_names': [], 'sub_querys': []} # 1、含有with关键字,则提取临时表查询语句和主查询语句 querys = [] if content.startswith('WITH'): # 处理with语句，取出临时表名，临时表查询语句，主查询语句(放在sub_querys的最后一个) tmp_names, sub_querys = with_query(content) # 把返回的结果记录在with_query_dict，方便后面取出判断是不是临时表名 with_query_dict['tmp_names'] = tmp_names with_query_dict['sub_querys'] = sub_querys # 把前面获取到的临时表查询语句和主查询语句传进去，进一步拆分出其中嵌套的子查询语句, 把拆分出来的sql先暂存到querys里面 for i in sub_querys: sub_query_list = get_sub_query(i) for j in sub_query_list: querys.append(j) # 2、如果不是with写法，可以直接拿content继续处理，否则从query_dict中获取临时表别名和临时表查询语句， else: sub_query_list = get_sub_query(content) for j in sub_query_list: querys.append(j) # 3、到这里就全都是单个查询语句，不嵌套子查询了. # 3.1、先判断有没有join， left join， right join, outer join这些关键字，有的代表是标准Sql的多表关联 sources_tables = [] for i in querys: for j in [' LEFT JOIN ', ' RIGHT JOIN ', ' OUTER JOIN ', ' JOIN ']: if j in i: # 3.1.1、代表该表是标准sql的多表关联写法, 有可能会关联多次，各种组合都有可能，查到一个切割一次，对结果继续遍历 # ，切割，直到最后没有了 ret = split_join([i]) # 获取到的ret第一个必然是有From的，并且From后是表名 if len(ret) > 0: for x in range(len(ret)): # 第一个肯定是带From的，取出from后的部分 if x == 0: s = ret[x].strip(' ').replace(' ', ' ').replace('()', '').replace('( )', '') table = s[s.index(' FROM ')+5:].strip(' ') # 可能带有别名，尝试按空格切，切得到就取前面部分，切不到就是没有别名 try: tmp_list = table.split(' ') sources_tables.append(tmp_list[0].strip(' ')) except: sources_tables.append(table) # 剩下的都是ON的，取出ON前面部分即可 else: s = ret[x].strip(' ').replace(' ', ' ').replace('()', '').replace('( )', '') table = s[:s.index(' ON ')].strip(' ') # 可能带有别名，尝试按空格切，切得到就取前面部分，切不到就是没有别名 try: tmp_list = table.split(' ') sources_tables.append(tmp_list[0].strip(' ')) except: sources_tables.append(table) # i 已经提取出源表名，直接从querys中移除 querys.remove(i) # 注意检测到一个就交给split_join函数做循环切割，这里不再判断 break # 3.1.2、代表该语句不是join写法，要么是oracle关联写法，要么是单表查询语句 if ' WHERE ' in i: # 3.1.2、获取FROM到WHERE之间的部分，有逗号代表是oracle多表关联 table_str = i[i.index(' FROM ') + 5: i.index(' WHERE ')+1].strip(' ').replace(' ', ' ').\ replace('()', '').replace('( )', '') if ',' in table_str: table_list = table_str.split(',') for t in table_list: t = t.strip(' ') try: tables = t.split(' ') # 切割成功代表它是有别名的，取第一个作为真实表名 sources_tables.append(tables[0]) except: # 切割不成功，代表它没有别名，直接添加 sources_tables.append(t) else: # 没有多表关联 try: tables = table_str.split(' ') # 切割成功代表它是有别名的，取第一个作为真实表名 sources_tables.append(tables[0]) except: # 切割不成功，代表它没有别名，直接添加 sources_tables.append(table_str) if ' GROUP BY ' in i and ' WHERE ' not in i: # 3.1.2、获取FROM到WHERE之间的部分，有逗号代表是oracle多表关联 table_str = i[i.index(' FROM ') + 5: i.index(' GROUP BY ') + 1].strip(' ').replace(' ', ' ').\ replace('()', '').replace('( )', '') if ',' in table_str: table_list = table_str.split(',') for t in table_list: t = t.strip(' ') try: tables = t.split(' ') # 切割成功代表它是有别名的，取第一个作为真实表名 sources_tables.append(tables[0]) except: # 切割不成功，代表它没有别名，直接添加 sources_tables.append(t) else: # 没有多表关联 try: tables = table_str.split(' ') # 切割成功代表它是有别名的，取第一个作为真实表名 sources_tables.append(tables[0]) except: # 切割不成功，代表它没有别名，直接添加 sources_tables.append(table_str) else: # 代表直接就是一句单查询语句，没有WHERE 和 Group by，直接截取FROM后面部分 table = i[i.index(' FROM ') + 6:].strip(' ') try: tables = table.split(' ') # 切割成功代表它是有别名的，取第一个作为真实表名 sources_tables.append(tables[0]) except: # 切割不成功，代表它没有别名，直接添加 sources_tables.append(table) # 对结果加工处理（对表名去重，去除分号，过滤掉表名长度在4 以下的, 可能是误提取的表名， 过滤掉with临时表写法的表名） sources_tables = [i.replace(';', '') for i in sources_tables] sources_tables = list(set([i for i in sources_tables if len(i)>4 and i not in with_query_dict['tmp_names']])) return sources_tables if __name__ == '__main__': file_path = r'test_case\join_query.sql' query_sql = get_query_sql(file_path) sources_tables = get_tables_from_sql(query_sql) print(sources_tables)4、上述脚本如何使用呢？ 只要将这里的文件路径替换为你的sql文件路径即可 该文本目前已通过以下的测试用例（逐个查询语句作为一个sql文件，都能准确提取出来源表名） 一.单表查询语句 --1、单表查询，没有过滤条件，没有分组条件 SELECT A.HW_CONTRACT_NUM, A.LAST_UPDATE_DATE, FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A; SELECT HW_CONTRACT_NUM, LAST_UPDATE_DATE, FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP; --2、单表查询，有过滤条件，没有分组条件 SELECT A.HW_CONTRACT_NUM, A.LAST_UPDATE_DATE FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A WHERE A.HW_CONTRACT_NUM = 'AAAAABBBBB' --3、单表查询，没有过滤条件，有分组条件 SELECT COUNT(1) FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A GROUP BY A.HW_CONTRACT_NUM; --4、单表查询，带子查询 SELECT SUM(AMOUNT) FROM (SELECT A.HW_CONTRACT_NUM, A.LAST_UPDATE_DATE FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A WHERE A.HW_CONTRACT_NUM = 'AAAAABBBBB') B GROUP BY B.HW_CONTRACT_NUM; 二.多表关联查询 --1、Oracle特有写法 SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A, DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B, DWAPP.DWL_EBG_CONTRACT_INFO_TMP3 C WHERE 1=1 AND A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM(+) AND A.HW_CONTRACT_NUM = C.HW_CONTRACT_NUM(+); --2.1、sql标准写法 SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM; --2.2、sql标准写法2 SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A INNER JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM; --2.3、sql标准写法3 SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A LEFT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM; --2.4、sql标准写法4 SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM; 三.多表关联查询嵌套子查询(上述所有情况把表名替换成子查询即可) 四.WITH临时表写法 --1.1、WITH单临时表，单表查询，无过滤条件，无分组条件 WITH TMP AS ( SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM ) SELECT HW_CONTRACT_NUM FROM TMP; --1.2、WITH单临时表，单表查询，有过滤条件，无分组条件 WITH TMP AS ( SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM ) SELECT HW_CONTRACT_NUM FROM TMP WHERE TO_CHAR(LAST_UPDATE_DATE)='2021/06/07'; --1.3、WITH单临时表，单表查询，无过滤条件，有分组条件 WITH TMP AS ( SELECT A.*, B.* FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A RIGHT JOIN DWAPP.DWL_EBG_CONTRACT_INFO_TMP2 B ON A.HW_CONTRACT_NUM = B.HW_CONTRACT_NUM ) SELECT SUM(AMOUNT) FROM TMP GROUP BY HW_CONTRACT_NUM; --2、WITH多临时表， 单表查询... WITH TMP1 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A ), TMP2 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B ) SELECT HW_CONTRACT_NUM FROM TMP1; --3、WITH多临时表， 多表关联查询, ORACLE写法 WITH TMP1 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A ), TMP2 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B ) SELECT C.HW_CONTRACT_NUM, D.CUST_NUM FROM TMP1 C, TMP2 D WHERE C.XX = D.XX(+); --（+）可有可无 --4、WITH多临时表， 多表关联查询, 标准sql写法 WITH TMP1 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP A ), TMP2 AS ( SELECT * FROM DWAPP.DWL_EBG_CONTRACT_INFO_TMP1 B ) SELECT C.HW_CONTRACT_NUM, D.CUST_NUM FROM TMP1 C JOIN TMP2 D --JOIN 可能替换为LEFT JOIN , INNER JOIN , RIGHT JOIN ON C.XX = D.XX(+); 6、缺陷说一下这个脚本的缺陷： 1、如果表别名的长度在4个字符以上（不含4），将有可能被误识别为真实的来源表名； 2、如果来源表名的长度小于或等于4个字符，会被当做表别名过滤掉； 最后，感谢一下曾文佑同学，百忙之中帮忙测试并发现了其中的bug。 |
例如这样的sql文本： 注意，sql文本中只允许有一句select 语句，可以包含子查询，表关联，但只能有一句。 
跑出来的结果： 
【本文地址】
今日新闻 |
推荐新闻 |