=Start=

缘由：

简单整理一下前段时间做的用Hive SQL从SQL查询语句中提取表名的内容，方便后面有需要的时候参考（之前记录过用Python和Java从SQL中提取表名、列名的功能，但这里出于方便起见——不想写UDF，准备直接在ETL里用Hive SQL来实现）。

正文：

参考解答：

简单快速的一个实现

-- 从mysql查询语句中提取表名

,substring_index(split(lower(query_sql),'from\\s{1,}|where')[1],' ',1) as tbl_name

,split(split(lower(query_sql),'from\\s{1,}|where')[1],'\\s{1,}')[0] as tbl_name

-- 已知问题：
-- 对于有 join 等多表连接的无法将所有表名都拿到；
-- 对于整体语句中存在注释语句的无法很好识别（除非是所有注释语句都放到了最后）；

完整的提取表名的思路

/*
从完整的SQL语句字符串中提取表名的思路和步骤

1. 先用正则表达式将SQL查询中的注释行替换为空，生成真正有效的SQL语句，为后面的表名提取排除干扰
2. 对没有多表连接的查询进行提取（可以保证提取的准确率）；
3. 再对当前已知的几种表连接方式尝试提取，可以将提取出来的结果放在多行中，方便后面的关联匹配；
4. 有数据库名的也用一个字段提取出来备着；
5. 增加几个字段用来给SQL打标签（所有列/where条件/limit数量）；
*/

/*
根据抽样的情况来看，SQL语句中：
from 之后的第一个字段是 table1
join 之后的第一个字段是 table2/3/...
正常情况下只用考虑 2 个表关联的情况，因为 3 个或以上表关联的毕竟是少数，不过可以统计一下比率，方便最后做决策
*/


-- todo: add some tag of sql, such as has */where/limit/--
-- 有几种方案在一列里面表示多个信息，比如：
-- 数字 1111111 每一位用1/0来表示这个位置上的目标字符串是否存在，判断起来是用取模等方式，但还没有完全想好
-- 字符串 abcdef 每一个不同的字符表示希望查找的目标字符串是否存在，判断起来也很方便，直接 locate/findstr/like 就行

分析过程中的一些代码片段

-- select.+?\n
-- 尝试用换行符进行切分，然后对每一个结果进行判断，不过这个只对部分SQL才能生效，和具体的SQL编写习惯有关(生产环境中各种可能性都有可能出现，只要它符合基本的规范原理就行，所以根本上还是从语法解析上着手是最靠谱的)
lateral view explode(split(query_sql,'\\n{1,}')) pi as sql1

-- 如何判断一个SQL语句中是否包含表连接的操作
-- 当前想到的3个判断条件是 join/on/from之后where之前有没有逗号
and (query_sql rlike '\\s{1,}join\\s{1,}' or query_sql rlike '\\s{1,}on\\s{1,}' or (query_sql like '%where%' and split(lower(query_sql),'from\\s{1,}|where\\s{1,}')[1] like '%,%'))


-- 切换处理思路，先将SQL语句做格式化处理，然后再通过split等方式进行切分提取，比如先将连续的多个空白符替换成空格
regexp_replace(query_sql,'\\s{1,}',' ')


-- 先用正则表达式将SQL查询中的注释行替换为空，生成真正有效的SQL语句，效果很好
,regexp_replace(query_sql,'--.+?\\n{1,}','') as real_sql

一个完整的从SQL提取表名的样例

-- 下面的SQL通过多层子查询，前期通过剔除注释语句和统一空白符进行规范化处理，然后通过对 from|join 等和表在一起的关键字进行 split 切分
-- 从而得到多个以表开头的字符串，借助 explode 将一行转成多行，然后再提取表名，并用 where 条件对无效切分数据进行排除
-- 最终得到的数据格式为SQL中使用到的每一个表为一行数据，方便后面做关联分析
SELECT
`user_name`
,`time_str`
,join_tag
,join_tag2
,join_cnt
,tbl_x
,tbl_namex
,real_sql
,`query_sql`
,`request_body`
FROM
(SELECT
`user_name`
,`time_str`
,case
when real_sql like '% join %' then 1
when real_sql like '% on %' then 2
else 0
end as join_tag
,if(split(real_sql,'from ')[1] like '%,%',1,0) as join_tag2
,(length(real_sql) - length(replace(real_sql,' join ',''))) / length(' join ') as join_cnt
,tbl_x
,substring_index(tbl_x,' ',1) as tbl_namex
,real_sql
,`query_sql`
,`request_body`
FROM
(SELECT
-- `dt`,
`user_name`
,`time_str`
,lower(regexp_replace(regexp_replace(query_sql,'--.+?\\n{1,}',''),'\\s{1,}',' ')) as real_sql
,`query_sql`
,`request_body`
-- todo: add some tag of sql, such as has */where/limit/--
-- ,if(query_sql like '%--%',1,0) as tag_comment
FROM
log.mysql_search_op
WHERE
dt between '20230315' and '20230315'
and query_sql like '%--%'
)x1
lateral view explode(split(real_sql,' from | join ')) pi as tbl_x
)x2
where join_tag > 0 and tbl_namex not in ('','select')
LIMIT 1000
;

参考链接：

如何用Python从SQL中提取出涉及到的表名、列名？
https://ixyzero.com/blog/archives/4404.html

How to extract tables names in a SQL script?
https://stackoverflow.com/questions/49773059/how-to-extract-tables-names-in-a-sql-script

如何用Java从SQL中提取出涉及到的表名、列名？
https://ixyzero.com/blog/archives/4405.html

MySQL多表join连接查询
https://ixyzero.com/blog/archives/4211.html

Hive SQL学习整理
https://ixyzero.com/blog/archives/4245.html

SQL 查询并不是从 SELECT 开始的(SQL queries don’t start with SELECT)
https://mp.weixin.qq.com/s/5BF12rIs1QXQpX4vpKkL0Q

=END=