=Start=

缘由：

去年的时候记录过一篇《如何提取SQL语句中有查询意义的数字ID字符串？》的文章，近期在写一个类似功能的ETL的时候又想到一些问题，在此记录一下，作为上一次思考的补充和扩展。

正文：

参考解答：

先说目标，提取出SQL语句中有查询意义的数字ID字符串主要是为了做异常分析——这个人查这个数字ID的次数、操作时间、时间跨度是否正常？是否符合正常工作需求？这个数字ID是否为一个有效的用户ID或是其他公司内部在用的数字ID？

分析的时候要尽可能的聚焦——比如我当前想分析的是用户ID的异常查询，我就主要关注 userid/user_id/uid 这些特征（以及其它代表用户ID含义的变量名）的查询；如果我当前想分析的是订单ID或是其他类型ID的异常查询，我就去关注内部和这类ID有关的变量命名规范、命名特征、取值范围等信息，然后针对性的提取和关联，再用于后面的统计。如果前期只管粗暴提取，而不进行较为精确的分析过滤的话，会对后面的统计分析造成较大影响和干扰。

明确目标，要聚焦，要精确——多不如精。

---

一些正常和不正常的SQL代码片段样例：

id=123
id='123'
id="123"

id = 123
id = '123'
id = "123"

id in (123)
id in ('123')
id in ("123")
id in ('123','456')

id in (select item_id from v2_base)
id in ( 'qw_24_1_76891', 'qw_24_1_74891')

---

Hive SQL中的一些要注意的问题：字符串字面量包含多种引号，不符合要求

处理建议：
检查 SQL 语句中的字符串字面量，确保只使用一种引号（单引号或双引号）。例如，将 'It's a test' 改为 "It's a test" 或 'It\'s a test'。

---

建议：在 Hive 中，优先使用“先定位上下文，再提取数字”的两步法，最稳定可靠。

下面直接说当前的解法吧，简单直接点。

1. 先做统一的格式化操作——移除注释，将连续的多个空白符号替换成一个空格符号，使用的是 regexp_replace 函数；
2. 然后用正则表达式把一些大概符合条件的SQL语句筛选出来；
3. 再用正则表达式对目标内容进行提取，这个提取是分别进行的，字符串等于的就直接拿数字ID，使用in语法的就先把括号里的内容都提取出来，再在外层进行切分并逐个提取；
4. 对提取出来的数字ID尝试进行left join匹配扩充相关字段方便后面分析；
5. 最后把那些针对特定数字ID有长期、大量查询的行为拿出来深入分析调查以确认是否正常。

-- SQL语句格式化处理+初步筛选
and lower(regexp_replace(regexp_replace(query_sql,'--.+?\\n{1,}',''),'\\s{1,}',' ')) rlike 'id\\s?(=|in)\\s?[^a-z ]'

WITH t1 AS (
    SELECT 'select * from tbl where userid = 123' AS sql_text UNION ALL
    SELECT 'select * from tbl where userid = ''456''' UNION ALL
    SELECT 'select * from tbl where userid in (123, 456, ''789'')' UNION ALL
    SELECT 'select * from tbl where userid in (211, 985, "110")' UNION ALL
    SELECT 'select * from tbl where user_id = 111'
)
SELECT 
    sql_text
    ,if(sql_text rlike 'id\\s?=', regexp_extract(sql_text,'id\\s?=\\s?[\']?(\\d{3,})[\']?',1), NULL) as id_equal
    ,if(sql_text rlike 'id\\s?=', regexp_extract(sql_text,"id\\s?=\\s?[']?(\\d{3,})[']?",1), NULL) as id_equal2
    ,if(sql_text rlike 'id\\s?in',regexp_extract(sql_text,'id\\s?in\\s?\\((.+?)\\)',1), NULL) as id_in
    ,split(regexp_replace(if(sql_text rlike 'id\\s?in',regexp_extract(sql_text,'id\\s?in\\s?\\((.+?)\\)',1), NULL),'[\' ]+',''),',|"') as id_list
FROM t1

---

如果想使用原生 Hive SQL 实现一些较为复杂的功能，最好的办法就是分成多个步骤进行处理（大概率涉及到一个或多个临时表），更为稳定可靠。

-- 先建立一个临时表缓存初步过滤和格式化处理之后的内容
with query_sql_tmp as (
select
    user_name,
    query_time,
    query_id,
    -- 格式化处理
    lower(regexp_replace(regexp_replace(query_sql,'--.+?\\n{1,}',''),'\\s{1,}',' ')) as query_sql
    -- 其他字段在此处先省略
from
data_platform.sql_query_detail
where
date = '${date}'
-- 初步过滤
and lower(regexp_replace(regexp_replace(query_sql,'--.+?\\n{1,}',''),'\\s{1,}',' ')) rlike 'id\\s?(=|in)\\s?[^a-z ]'
)


-- 单个id查询
select
    user_name,
    query_time,
    query_id,
    query_sql,
    1 as qid_cnt,
    regexp_replace(regexp_extract(query_sql, '(user_id|userid|uid)\\s+=\\s+[\']*(\\d+)', 2), '\'', '') as qid
from
query_sql_tmp
where
query_sql rlike '(user_id|userid|uid)\\s+='

union all

-- 多个id查询
select
    user_name,
    query_time,
    query_id,
    query_sql,
    size(in_list) as qid_cnt,
    qid
from (
        select
            user_name,
            query_time,
            query_id,
            query_sql,
            split(regexp_replace(regexp_extract(query_sql, '(user_id|userid|uid)\\s+in\\s+\\(([^\\)]+)\\)', 2), '\'| |"', ''),',') as in_list
        from
        query_sql_tmp
        where
        query_sql rlike '(user_id|userid|uid)\\s+in\\s+\\('
) t1
lateral view explode(in_list) tmp as qid

参考链接：

如何提取SQL语句中有查询意义的数字ID字符串？
https://ixyzero.com/blog/archives/5826.html

=END=