提取SQL中的limit限制数值

=Start=

缘由：

做敏感数据访问监控时有个方向就是查询SQL的分析，除了敏感表的查询需要注意之外，查询的大小和返回大小也是需要重点关注的地方，返回大小仅从SQL语句中无法分析和体现，这个需要在类似HiveServer2这样的中心服务层面或者更底层的各执行引擎上做日志打点才能记录到，这里主要说一下从SQL查询语句中如何粗略获取查询的大小。

正文：

参考解答：

字符串切分(也是正则) + 正则提取 结合使用，字符串切分可以根据切分的数组大小判断出现次数，正则提取比较简单直接。

1. 关键在于先理解 limit clause 的几种语法对应格式
2. 然后编写对应正则用来匹配和提取
3. 如果没有额外需求，其实用正则就能搞定，不需要写UDF，不过写UDF的好处在于外层SQL简洁一些

Limit用于对查询结果集数量进行限制；Offset 则常与 Limit 搭配，意为偏移，表示跳过一定数量的记录。

Limit 后面可接受一个参数或者两个参数：
当接受一个参数count时，表示返回的结果集最多有 count 条。
当使用两个参数时，第一个参数表示偏移量，即 offset，表示从几个记录后开始返回，第二个参数表示最大数量，即 count，表示最多返回几个记录。我们合起来可以理解为，跳过一定数量（offset）的记录，返回最多 count 个记录。

Offset 必须与 Limit 搭配使用，且二者都必须是单参数形式。Limit 和 Offset 搭配的作用等同于 Limit 使用双参数。offset是偏移量，Limit后面的count是最大数量。

Limit 和 Offset 常搭配用于数据分页，是很多分页工具的核心操作。
Limit 其实完全可以替代 Limit + Offset，但考虑到SQL语句的可读性，Limit + Offset的写法更好。

package com.ixyzero.hive.udf.utils;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;

/**
 * @author ixyzero
 * Created on 2023-03-28
 */

@Description(name = "getSqlLimitSize",
        value = "_FUNC_(str) - Get limit size from search sql string.",
        extended = "The value is returned as a string, or NULL if the argument was NULL.\n"
                + "Example:\n"
                + "  > SELECT _FUNC_('select * from tbl where col1 = 11 limit 1000;');\n"
                + "  '1000'\n")

//public class getSqlLimitSizeUDF {
public class getSqlLimitSizeUDF extends UDF {
    private static final Pattern commentaryPattern = Pattern.compile("/\\\\*.*?\\\\*/|--.*?", Pattern.DOTALL);
    private static final Pattern limitPattern = Pattern.compile("\\s+limit\\s+(\\d+)\\s*(,|offset)?\\s*(\\d*)\\s*", Pattern.CASE_INSENSITIVE);

    String ret = "";

    /**
     * Get limit size from search sql string.
     */
    public String evaluate(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return null;
        }
        sql = commentaryPattern.matcher(sql).replaceAll("").toLowerCase();

        try {
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;

            Matcher m = limitPattern.matcher(sql);
            if (m.find()) {
                if (m.group(2) == null) {
                    ret = m.group(1);
                } else if (m.group(2).equalsIgnoreCase("offset")) {
                    ret = m.group(1);
                } else {
                    ret = m.group(3);
                }
            } else {
                ret = "0";
            }

        } catch (JSQLParserException e) {
            // e.printStackTrace();
            return null;
        } catch (Exception e) {
            // e.printStackTrace();
            return null;
        }

        return ret;
    }

    public static void main(String[] args) {
        getSqlLimitSizeUDF getSqlLimitSize = new getSqlLimitSizeUDF();
        String str = "select * from tbl where col1 = 11 and col2 = 1001 LIMIT 1000 offset 2";
        System.out.println(str);
        System.out.println(getSqlLimitSize.evaluate(str));

        str = "select * from tbl where col1 = 11 and col2 = 1001 LIMIT 1000, 2";
        System.out.println(str);
        System.out.println(getSqlLimitSize.evaluate(str));

        str = "select * from (select * from tbl where col1 = 11 and col2 = 1001 LIMIT 1000)x1";
        System.out.println(str);
        System.out.println(getSqlLimitSize.evaluate(str));

        str = "select * from tbl where col1 = 11 and col2 = 1001";
        System.out.println(str);
        System.out.println(getSqlLimitSize.evaluate(str));
    }

}

regexp_extract(lower(regexp_replace(query_sql,'--.+?\\n{1,}','')), '\\s+limit\\s+(\\d+)\\s*(,|offset)?\\s*(\\d*)\\s*', 0) as limit_info,
size(split(lower(regexp_replace(query_sql,'--.+?\\n{1,}','')),'\\s{1,}limit\\s{1,}')) as limit_cnt,

/*
一句话简单总结：
limit 有 offset 的情况，前为数量，后为偏移（根据线上的SQL来看，用offset语法的不多，感觉应该是与写法比较麻烦有关）
limit 有 2个参数 的情况，前为偏移，后为数量
limit 有 1个参数 的情况，表示数量
*/

with t as (
  select '2020-12-28' as dt_start, '2021-01-12' as dt_end
)
,t2 as (select
idx, date_add(to_date(dt_start), idx) as dt
from t
lateral view posexplode(split(space(datediff(t.dt_end,t.dt_start)),' ')) pi as idx, empty
)

-- 当limit后面跟一个参数的时候，该参数表示要取的数据的数量
-- select idx,dt
-- from t2
-- limit 5
/*
idx    dt  
0    2020-12-28
1    2020-12-29
2    2020-12-30
3    2020-12-31
4    2021-01-01
*/

-- 当limit后面跟两个参数的时候，第一个数表示要跳过的数量，第二个数表示要取的数量
-- 下面这个case里面就是只返回2行
-- select idx,dt
-- from t2
-- limit 5,2
/*
idx    dt  
5    2021-01-02
6    2021-01-03
*/

-- OFFSET子句在开始返回行之前跳过偏移行。 OFFSET子句是可选的。 如果同时使用LIMIT和OFFSET子句，OFFSET会在LIMIT约束行数之前先跳过偏移行
-- limit 5 offset 2 这个例子的含义是从offset偏移为2开始，取接下来的5行
select idx,dt
from t2
limit 5 offset 2
/*
idx    dt  
2    2020-12-30
3    2020-12-31
4    2021-01-01
5    2021-01-02
6    2021-01-03
*/