Help Center/ MapReduce Service/ Developer Guide (LTS)/ HetuEngine Development Guide (Normal Mode)/ Application Development/ Java Sample Code/ Querying the Execution Progress and Status of an SQL Statement Using JDBC
Updated on 2024-08-10 GMT+08:00

Querying the Execution Progress and Status of an SQL Statement Using JDBC

Description

This section describes how to use JDBC to connect to HetuEngine, and assemble and send the SQL statements to HetuEngine for execution.
import io.XXX.jdbc.XXXResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;

public class JDBCExampleStatementProgressPercentage{

    private static Properties properties = new Properties();
    public static Connection connection = null;
    public static ResultSet result = null;
    public static PreparedStatement statement = null;
    private static void init() throws ClassNotFoundException {
        properties.setProperty("user", "YourUserName");
        properties.setProperty("SSL", "false");
        Class.forName("io.XXX.jdbc.XXXDriver");
    }

    /**
      * Program entry
      *
      * @param args no need program parameter
      */
    public static void main(String[] args) {
        String url = "jdbc:XXX://192.168.1.130:29861,192.168.1.131:29861/hive/default?serviceDiscoveryMode=hsbroker";
        try {
            init();
            String sql = "show tables";
            connection = DriverManager.getConnection(url, properties);
            statement = connection.prepareStatement(sql.trim());
            result = statement.executeQuery();

            XXXResultSet rs = (XXXResultSet) result;
            new Thread() {
                public void run() {
                    Timer timer = new Timer();
                    //The SQL statement is executed after 3 seconds and is executed every 2 seconds
                    timer.schedule(new TimerTask() {
                        @Override
                        public void run() {
                            double statementProgressPercentage = rs.getProgressPercentage().orElse(0.0);
                            System.out.println("The Current Query Progress Percentage is " + statementProgressPercentage*100 + "%");
                            if("FINISHED".equals(rs.getStatementStatus().orElse(""))) {
                                System.out.println("The Current Query Progress Percentage is 100%");
                                timer.cancel();
                                Thread.currentThread().interrupt();
                            }
                        }
                    }, 3000, 2000);
                }
            }.start();

            ResultSetMetaData resultMetaData = result.getMetaData();
            int colNum = resultMetaData.getColumnCount();
            for (int j = 1; j <= colNum; j++) {
                try {
                    System.out.print(resultMetaData.getColumnLabel(j) + "\t");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            while (result.next()) {
                for (int j = 1; j <= colNum; j++) {
                    System.out.print(result.getString(j) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Table 1 describes the parameters in the preceding code.

Table 1 Parameter description

Parameter

Description

url

jdbc:XXX://HSBroker1_IP:HSBroker1_Port,HSBroker2_IP:HSBroker2_Port,HSBroker3_IP:HSBroker3_Port/catalog/schema?serviceDiscoveryMode=hsbroker

NOTE:
  • xxx: driver name, which is subjective to the real-world code you use.
  • catalog and schema indicate the names of the catalog and schema to be connected to the JDBC client, respectively.
  • HSBroker_IP:HSBroker_Port: indicates the HSBroker URL. Use commas (,) to separate multiple URLs, for example, 192.168.81.37:2181,192.168.195.232:2181,192.168.169.84:2181.

    On FusionInsight Manager, choose Cluster > Services > HetuEngine and click Instance to obtain the service IP addresses of all HSBroker instances. On the Configurations page, search for server.port to obtain the port number of HSBroker.

user

Username for accessing HetuServer, that is, the username of the machine-machine user created in the cluster.

SSL

Indicates whether to use the HTTPS connection. The default value is false.

getStatementStatus()

Execution status of an SQL statement, which can be RUNNING, FAILED, FINISHED, QUEUEED, WAITING_FOR_RESOURCES, DISPATCHING, PLANNING, STARTING, RESCHEDULING, RESUMING, or FINISHING.

getProgressPercentage()

Execution progress of an SQL statement. The value range is 0 to 1.