On this page

Java Sample Code

Updated on 2022-09-14 GMT+08:00

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);
    NOTE:
    • 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.

    NOTE:

    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();
                }
            }
        }

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback