Configuring HetuEngine SQL Inspection

Scenario

You can configure rules for HetuEngine SQL inspection on FusionInsight Manager and configure rule parameters as you need.

Prerequisites

• The cluster client that contains the HetuEngine service has been installed in the /opt/hadoopclient directory.
• The HetuEngine service and compute instances are running properly.
• If Kerberos authentication has been enabled for the cluster, you need to create a HetuEngine user and grant related permissions to the user. In addition, you need to use Ranger to assign the user the permission to manage databases, tables, and columns of the data source.

Constraints

• The default dynamic validity period of a rule is 5 minutes.
• Interception and blocking rules will interrupt SQL queries, so you need to set parameters of these rules properly based on the site requirements.
• Blocking rules are controlled by session-level parameters of the system. To configure blocking rules, service users must have the set session permission.
• For static rule static_0003, the total number of joins in queries does not include Semi joins and Anti joins.
• When prompt rules are configured for dynamic_0001 and dynamic_0002, prompt messages are recorded only in logs and are not displayed on the client.
• The client and server send asynchronous requests. For blocking rule Running_0001, after the server blocks the requests, the message "Query is gone " may be displayed on the client. In this case, you can view logs to check whether the requests are blocked.

Procedure

1. Log in to FusionInsight Manager, click Cluster, and choose SQL Inspector. The SQL Inspector page is displayed.
2. Add rules for HetuEngine by referring to Adding an SQL Inspection.

   For details about the rules supported by the HetuEngine SQL engine, see MRS SQL Inspection Rules.

   For example, add a rule whose ID is static_0001 to check whether count distinct appears more than two times in the SQL statement. If so, the system displays a hint.

   Figure 1 Adding a HetuEngine SQL inspection rule
   

3. Log in to the node where the HetuEngine client is installed and run the following command to switch to the client installation directory:

   cd /opt/hadoopclient

   Run the following command to set environment variables:

   source bigdata_env

4. Log in to the HetuEngine client based on the cluster authentication mode.
   • In security mode, run the following command to authenticate the user and log in to the HetuEngine client:

     kinit hetu_test

     hetu-cli --catalog hive --tenant default --schema default

   • In normal mode, run the following command to log in to the HetuEngine client:

     hetu-cli --catalog hive --tenant default --schema default --user hetu_test

     

     hetu_test is a service user who has at least the tenant role specified by --tenant and cannot be an OS user.

5. Check whether the current rule takes effect.

   Run the following statement to create a table:

   CREATE TABLE table1(id int, name varchar,rank int);

   INSERT INTO table1 VALUES(10,'sachin',1),(45,'rohit',2),(46,'rohit',3),(18,'virat',4),(25,'dhawan',5);

   Run the following statement to query data:

   select count(distinct id),count(distinct id),count(distinct id),count(distinct id),count(distinct id),count(distinct id) from table1;

   If the number of times count distinct appears in the statement exceeds the threshold configured in 2, the following information is displayed:

   WARNING: Occurrence number of 'COUNT(DISTINCT XX)' (6) reaches the hint limitation (2)

   

   • If the action set in the rule is Intercept or Block, the following information may be displayed:

     Intercepted. Reason: Occurrence number of 'COUNT(DISTINCT XX)' (6) reaches the interception limitation (2)

   • You can query HetuEngine SQL inspection details in logs stored in hdfs://hacluster/hetuserverhistory/tenant/coordinator/application_ID/container_ID/yyyyMMdd/server.log.
   • If warning information is required for JDBC secondary development, add the following configuration for the JDBC application:

     statement = connection.prepareStatement(sql.trim());
     resultSet = statement.executeQuery();
     SQLWarning sqlWarning = statement.getWarnings();