DLA SQL分析函数：SQL语句审计与分析的利器

短命女 2022-01-16 09:35 246阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

## 1. 简介 ##

Data Lake Analytics最新release一组SQL内置函数，用来进行SQL语句的分析、信息提取，方便用户对SQL语句进行语法层面的审计、分析，可以应用于很多安全、数据库日志分析等场景。参考DLA（Data Lake Analytics）之前的文档，只要您的SQL语句文本保存在DLA支持的数据源系统中，就能使用DLA的SQL分析函数，方便、快捷的进行SQL语句的审计和分析，或者基于此构建相关应用程序和系统。

前提条件，您的SQL语句文本已经作为一个字符串、文本字段存储在DLA支持的数据源中，或者在统一格式的日志文件中，日志文件放到OSS中（应用自己上传、开源日志工具上传、日志服务投递、Datahub投递等），然后采用DLA进行SQL分析。

## 2. SQL分析函数详解 ##

所有提供的SQL分析函数都是scalar标量函数。SQL分析函数支持多种SQL方言，默认SQL方言是mysql。

<table> 
 <tbody> 
  <tr> 
   <th>SQL方言</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>mysql</td> 
  </tr> 
  <tr> 
   <td>postgresql</td> 
  </tr> 
  <tr> 
   <td>oracle</td> 
  </tr> 
  <tr> 
   <td>db2</td> 
  </tr> 
  <tr> 
   <td>sqlserver</td> 
  </tr> 
  <tr> 
   <td>hive</td> 
  </tr> 
  <tr> 
   <td>odps</td> 
  </tr> 
 </tbody> 
</table>

支持的函数列表：

<table> 
 <tbody> 
  <tr> 
   <th><strong>Name</strong></th> 
   <th><strong>Description</strong></th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>sql_export_columns</td> 
   <td>提取SQL语句中所有出现的列</td> 
  </tr> 
  <tr> 
   <td>sql_export_functions</td> 
   <td>提取SQL语句中所有出现的函数</td> 
  </tr> 
  <tr> 
   <td>sql_export_predicate_columns</td> 
   <td>提取SQL语句中所有出现的谓词条件表达式涉及的列</td> 
  </tr> 
  <tr> 
   <td>sql_export_predicates</td> 
   <td>提取SQL语句中所有出现的谓词条件表达式</td> 
  </tr> 
  <tr> 
   <td>sql_export_select_list_columns</td> 
   <td>提取SQL语句中SELECT子句表达式中出现的列（包括子查询）</td> 
  </tr> 
  <tr> 
   <td>sql_export_tables</td> 
   <td>提取SQL语句中所有出现的表</td> 
  </tr> 
  <tr> 
   <td>sql_format</td> 
   <td>对SQL语句进行格式化</td> 
  </tr> 
  <tr> 
   <td>sql_params</td> 
   <td>提取SQL语句中所有的literal值</td> 
  </tr> 
  <tr> 
   <td>sql_pattern</td> 
   <td>提取SQL语句参数化后的SQL pattern，literal用?代替</td> 
  </tr> 
  <tr> 
   <td>sql_pattern_hash</td> 
   <td>提取sqlText的SQL语句参数化后的SQL pattern，并生成hash值</td> 
  </tr> 
  <tr> 
   <td>sql_syntax_check</td> 
   <td>对sqlText进行语法检查</td> 
  </tr> 
 </tbody> 
</table>

### 2.1 sql\_export\_columns ###

函数调用形式：

*  sql\_export\_columns(sqlText)
 *  sql\_export\_columns(sqlText, dbType)
 *  sql\_export\_columns(sqlText, dbType, throwError)

参数说明：

<table> 
 <tbody> 
  <tr> 
   <th>参数名</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>sqlText</td> 
   <td>VARCHAR</td> 
   <td>必选</td> 
  </tr> 
  <tr> 
   <td>dbType</td> 
   <td>VARCHAR</td> 
   <td>可选。SQL方言，默认为mysql</td> 
  </tr> 
  <tr> 
   <td>throwError</td> 
   <td>BOOLEAN</td> 
   <td>可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null</td> 
  </tr> 
 </tbody> 
</table>

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中所有出现的列，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_columns(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_export_columns(a.sql_text)                                                                                                                                                                                                                                                                                                                  |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.2 sql\_export\_functions ###

函数调用形式：

*  sql\_export\_functions(sqlText)
 *  sql\_export\_functions(sqlText, dbType)
 *  sql\_export\_functions(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中所有出现的函数名，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_functions(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +----------------------------------+
    | sql_export_functions(a.sql_text) |
    +----------------------------------+
    | min                              |
    +----------------------------------+

### 2.3 sql\_export\_predicate\_columns ###

函数调用形式：

*  sql\_export\_predicate\_columns(sqlText)
 *  sql\_export\_predicate\_columns(sqlText, dbType)
 *  sql\_export\_predicate\_columns(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中出现在谓词条件表达式中的列名，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_predicate_columns(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_export_predicate_columns(a.sql_text)                                                                                                                                                                                     |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | UNKNOWN.p_partkey,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.4 sql\_export\_predicates ###

函数调用形式：

*  sql\_export\_predicates(sqlText)
 *  sql\_export\_predicates(sqlText, dbType)
 *  sql\_export\_predicates(sqlText, dbType, compactValues)
 *  sql\_export\_predicates(sqlText, dbType, compactValues, throwError)

参数说明：

<table> 
 <tbody> 
  <tr> 
   <th>参数名</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>sqlText</td> 
   <td>VARCHAR</td> 
   <td>必选</td> 
  </tr> 
  <tr> 
   <td>dbType</td> 
   <td>VARCHAR</td> 
   <td>可选。SQL方言，默认为mysql</td> 
  </tr> 
  <tr> 
   <td>compactValues</td> 
   <td>BOOLEAN</td> 
   <td>可选。true时，谓词条件中值以数组的形式出现在返回值中</td> 
  </tr> 
  <tr> 
   <td>throwError</td> 
   <td>BOOLEAN</td> 
   <td>可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null</td> 
  </tr> 
 </tbody> 
</table>

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中所有出现的谓词条件表达式元素数组，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_predicates(a.sql_text, 'mysql', true)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_export_predicates(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                  |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | [["UNKNOWN","p_partkey","=",null],["UNKNOWN","ps_partkey","=",null],["UNKNOWN","s_suppkey","=",null],["UNKNOWN","ps_suppkey","=",null],["UNKNOWN","p_size","=",35],["UNKNOWN","p_type","LIKE","%NICKEL"],["UNKNOWN","s_nationkey","=",null],["UNKNOWN","n_nationkey","=",null],["UNKNOWN","n_regionkey","=",null],["UNKNOWN","r_regionkey","=",null],["UNKNOWN","r_name","=",["MIDDLE EAST","MIDDLE EAST"]],["UNKNOWN","ps_supplycost","IN",null]] |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.5 sql\_export\_select\_list\_columns ###

函数调用形式：

*  sql\_export\_select\_list\_columns(sqlText)
 *  sql\_export\_select\_list\_columns(sqlText, dbType)
 *  sql\_export\_select\_list\_columns(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中SELECT子句返回列中出现的列名列表（包括子查询），用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_select_list_columns(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_export_select_list_columns(a.sql_text)                                                                                                                 |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_supplycost |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.6 sql\_export\_tables ###

函数调用形式：

*  sql\_export\_tables(sqlText)
 *  sql\_export\_tables(sqlText, dbType)
 *  sql\_export\_tables(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>sqlText的SQL语句中所有出现的表名，用逗号分隔</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_export_tables(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +--------------------------------------+
    | sql_export_tables(a.sql_text)        |
    +--------------------------------------+
    | part,supplier,partsupp,nation,region |
    +--------------------------------------+

### 2.7 sql\_format ###

函数调用形式：

*  sql\_format(sqlText)
 *  sql\_format(sqlText, dbType)
 *  sql\_format(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>格式化sqlText的SQL语句</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_format(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_format(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
        , s_address, s_phone, s_comment
    FROM part, supplier, partsupp, nation, region
    WHERE p_partkey = ps_partkey
        AND s_suppkey = ps_suppkey
        AND p_size = 35
        AND p_type LIKE '%NICKEL'
        AND s_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        AND r_name = 'MIDDLE EAST'
        AND ps_supplycost IN (
            SELECT min(ps_supplycost)
            FROM partsupp, supplier, nation, region
            WHERE s_suppkey = ps_suppkey
                AND s_nationkey = n_nationkey
                AND n_regionkey = r_regionkey
                AND r_name = 'MIDDLE EAST'
        )
    ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
    LIMIT 100; |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.8 sql\_params ###

函数调用形式：

*  sql\_params(sqlText)
 *  sql\_params(sqlText, dbType)
 *  sql\_params(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>提取sqlText的SQL语句中的literal值</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_params(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +------------------------------------------------+
    | sql_params(a.sql_text)                         |
    +------------------------------------------------+
    | [35,"%NICKEL","MIDDLE EAST","MIDDLE EAST",100] |
    +------------------------------------------------+

### 2.10 sql\_pattern ###

函数调用形式：

*  sql\_pattern(sqlText)
 *  sql\_pattern(sqlText, dbType)
 *  sql\_pattern(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>提取sqlText的SQL语句参数化后的SQL pattern, literal值换成?</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_pattern(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_pattern(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
        , s_address, s_phone, s_comment
    FROM part, supplier, partsupp, nation, region
    WHERE p_partkey = ps_partkey
        AND s_suppkey = ps_suppkey
        AND p_size = ?
        AND p_type LIKE ?
        AND s_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        AND r_name = ?
        AND ps_supplycost IN (
            SELECT min(ps_supplycost)
            FROM partsupp, supplier, nation, region
            WHERE s_suppkey = ps_suppkey
                AND s_nationkey = n_nationkey
                AND n_regionkey = r_regionkey
                AND r_name = ?
        )
    ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
    LIMIT ?; |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

### 2.11 sql\_pattern\_hash ###

函数调用形式：

*  sql\_pattern\_hash(sqlText)
 *  sql\_pattern\_hash(sqlText, dbType)
 *  sql\_pattern\_hash(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>提取sqlText的SQL语句参数化后的SQL pattern，并生成hash值</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_pattern_hash(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +------------------------------+
    | sql_pattern_hash(a.sql_text) |
    +------------------------------+
    |           925870115679910184 |
    +------------------------------+

### 2.12 sql\_syntax\_check ###

函数调用形式：

*  sql\_syntax\_check(sqlText)
 *  sql\_syntax\_check(sqlText, dbType)
 *  sql\_syntax\_check(sqlText, dbType, throwError)

参数说明：

返回值:

<table> 
 <tbody> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </tbody> 
 <tbody> 
  <tr> 
   <td>BOOLEAN</td> 
   <td>对sqlText进行语法检查，1表示正确，0表示错误</td> 
  </tr> 
 </tbody> 
</table>

示例：

SELECT sql_syntax_check(a.sql_text)
    FROM (
    SELECT 'SELECT   s_acctbal, 
             s_name, 
             n_name, 
             p_partkey, 
             p_mfgr, 
             s_address, 
             s_phone, 
             s_comment 
    FROM     part, 
             supplier, 
             partsupp, 
             nation, 
             region 
    WHERE    p_partkey = ps_partkey 
    AND      s_suppkey = ps_suppkey 
    AND      p_size = 35 
    AND      p_type LIKE ''%NICKEL'' 
    AND      s_nationkey = n_nationkey 
    AND      n_regionkey = r_regionkey 
    AND      r_name = ''MIDDLE EAST'' 
    AND      ps_supplycost IN 
             ( 
                    SELECT min(ps_supplycost) 
                    FROM   partsupp, 
                           supplier, 
                           nation, 
                           region 
                    WHERE  s_suppkey = ps_suppkey 
                    AND    s_nationkey = n_nationkey 
                    AND    n_regionkey = r_regionkey 
                    AND    r_name = ''MIDDLE EAST'' ) 
    ORDER BY s_acctbal DESC, 
             n_name, 
             s_name, 
             p_partkey 
    LIMIT    100;' AS sql_text
    ) a;
    
    
    +------------------------------+
    | sql_syntax_check(a.sql_text) |
    +------------------------------+
    |                            1 |
    +------------------------------+

[原文链接][Link 1]  
本文为云栖社区原创内容，未经允许不得转载。

转载于:https://my.oschina.net/u/1464083/blog/3041269

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/0f9e8192628240389c9c2585a90b2bba.png
[Link 1]: https://yq.aliyun.com/articles/697947?utm_content=g_1000053955