This feature is used to restrict the execution of SQL statements (both DDL and DML can be restricted).

Supports per-user configuration of SQL interception rules, such as using regular expressions to match and intercept SQL, or using supported rules for interception.

Creating and Managing Rules

Creating Rules

For more syntax on creating rules, please refer to CREATE SQL BLOCK RULE.

  • sql: Matching rule (based on regular expression matching, special characters need to be escaped), optional, default value is “NULL”.
  • sqlHash: SQL hash value for exact matching. This value will be printed in fe.audit.log, optional. This parameter and SQL are mutually exclusive, default value is “NULL”.
  • partition_num: Maximum number of partitions a scan node will scan, default value is 0L.
  • tablet_num: Maximum number of tablets a scan node will scan, default value is 0L.
  • cardinality: Rough number of rows scanned by a scan node, default value is 0L.
  • global: Whether it is globally effective (for all users), default is false.
  • enable: Whether to enable the blocking rule, default is true.

Example:

  1. CREATE SQL_BLOCK_RULE test_rule1 
  2. PROPERTIES(
  3.   "sql"="select \\* from order_analysis",
  4.   "global"="false",
  5.   "enable"="true",
  6.   "sqlHash"=""
  7. );
  9. CREATE SQL_BLOCK_RULE test_rule2
  10. PROPERTIES(
  11.     "partition_num" = "30",
  12.     "cardinality"="10000000000",
  13.     "global"="false",
  14.     "enable"="true"
  15. )

SQL Interception - 图1note

Note: Do not include a semicolon at the end of the SQL statement.

Starting from version 2.1.6, SQL interception rules support external tables (tables in the External Catalog).

  • sql: Same as for internal tables.
  • sqlHash: Same as for internal tables.
  • partition_num: Same as for internal tables.
  • tablet_num: Limits the number of shards scanned for external tables. Different data sources have different definitions of shards. For example, file shards in Hive tables, incremental data shards in Hudi tables, etc.
  • cardinality: Same as for internal tables, limits the number of scanned rows. This parameter only takes effect when there are row count statistics for external tables (such as collected manually or automatically).

Binding Rules

Rules with global set to true are globally effective and do not need to be bound to specific users.

Rules with global set to false need to be bound to specific users. A user can be bound to multiple rules, and multiple rules are separated by ,.

  1. SET PROPERTY [FOR 'jack'] 'sql_block_rules' = 'test_rule1,test_rule2'

Viewing Rules

  • View the configured SQL blocking rules.

If no rule name is specified, all rules will be viewed. For specific syntax, please refer to SHOW SQL BLOCK RULE

  1. SHOW SQL_BLOCK_RULE [FOR RULE_NAME]
  • View rules bound to a user
  1. SHOW PROPERTY FOR user_name;

Modifying Rules

Allow modifications to each item such as sql/sqlHash/partition_num/tablet_num/cardinality/global/enable. For specific syntax, please refer to ALTER SQL BLOCK RULE

  • sql and sqlHash cannot be set simultaneously.

If a rule sets sql or sqlHash, the other property cannot be modified.

  • sql/sqlHash and partition_num/tablet_num/cardinality cannot be set simultaneously

For example, if a rule sets partition_num, then sql or sqlHash cannot be modified.

  1. ALTER SQL_BLOCK_RULE test_rule PROPERTIES("sql"="select \\* from test_table","enable"="true")
  1. ALTER SQL_BLOCK_RULE test_rule2 PROPERTIES("partition_num" = "10","tablet_num"="300","enable"="true")

Deleting Rules

Support deleting multiple rules simultaneously, separated by ,. For specific syntax, please refer to DROP SQL BLOCK RULE

  1. DROP SQL_BLOCK_RULE test_rule1,test_rule2

Triggering Rules

When we execute the SQL defined in the rules, an exception error will be returned, as shown below:

  1. mysql> select * from order_analysis;
  2. ERROR 1064 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: order_analysis_rule