Java Sample Code

Function Description

The JDBC API of the user-defined client is used to submit a data analysis task and return the results.

Sample Code

1. Define an SQL statement. SQL must be a single statement and cannot contain ";". Example:

   ArrayList<String> sqlList = new ArrayList<String>();
   sqlList.add("CREATE TABLE CHILD (NAME STRING, AGE INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','");
   sqlList.add("LOAD DATA INPATH '/home/data' INTO TABLE CHILD");
   sqlList.add("SELECT * FROM child");
   sqlList.add("DROP TABLE child");
   executeSql(url, sqlList);

   

   • The data file in the sample project must be placed in the home directory of the HDFS.
   • Ensure that the user and user group of the data file are the same as those of the created table.

2. Build JDBC URL.
   

   In HA mode, the host and port of the URL must be ha-cluster.

   For a normal cluster, you need to change the 67th and 68th lines (as shown in the following) of the com.huawei.bigdata.spark.examples.ThriftServerQueriesTest class in the sample code from

   StringBuilder sb = new StringBuilder("jdbc:hive2://ha-cluster/default"

   + securityConfig);

   to StringBuilder sb = new StringBuilder("jdbc:hive2://ha-cluster/default").

   String HA_CLUSTER_URL = "ha-cluster";
   StringBuilder sb = new StringBuilder("jdbc:hive2://" + HA_CLUSTER_URL + "/default;");
   String url = sb.toString();

3. Load the Hive JDBC driver.

   Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();

4. Obtain the JDBC connection, execute the HiveQL statement, return the queried column name and results to the console, and close the JDBC connection.

   In network congestion, configure a timeout interval for a connection between the client and JDBCServer to avoid a client suspension due to timeless wait of the return result from the server. The configuration method is as follows:

   Before using the DriverManager.getConnection method to obtain the JDBC connection, add the DriverManager.setLoginTimeout(n) method to configure a timeout interval. n indicates the timeout interval for waiting for the return result from the server. The unit is second, the type is Int, and the default value is 0 (indicating never timing out).

   static void executeSql(String url, ArrayList<String> sqls) throws ClassNotFoundException, SQLException {
           try {
               Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();
           } catch (Exception e) {
               e.printStackTrace();
           }
           Connection connection = null;
           PreparedStatement statement = null;
   
           try {
               connection = DriverManager.getConnection(url);
               for (int i = 0 ; i < sqls.size(); i++) {
                   String sql = sqls.get(i);
                   System.out.println("---- Begin executing sql: " + sql + " ----");
                   statement = connection.prepareStatement(sql);
                   ResultSet result = statement.executeQuery();
                   ResultSetMetaData resultMetaData = result.getMetaData();
                   Integer colNum = resultMetaData.getColumnCount();
                   for (int j = 1; j < colNum; j++) {
                       System.out.println(resultMetaData.getColumnLabel(j) + "\t");
                   }
                   System.out.println();
   
                   while (result.next()) {
                       for (int j = 1; j < colNum; j++){
                           System.out.println(result.getString(j) + "\t");
                       }
                       System.out.println();
                   }
                   System.out.println("---- Done executing sql: " + sql + " ----");
               }
   
   
           } catch (Exception e) {
               e.printStackTrace();
           } finally {
               if (null != statement) {
                   statement.close();
               }
               if (null != connection) {
                   connection.close();
               }
           }
       }