Scenario

In Linux or Windows, you can connect to the DLI server using JDBC.

The job submitted by using JDBC to connect to DLI runs on the Spark engine.

Prerequisites

Before using JDBC, perform the following operations:

1. Getting authorized.

   DLI uses the Identity and Access Management (IAM) to implement fine-grained permissions for your enterprise-level tenants. IAM provides identity authentication, permissions management, and access control, helping you securely access to your public cloud resources.

   With IAM, you can use your public cloud account to create IAM users for your employees, and assign permissions to the users to control their access to specific resource types.

   Currently, roles (coarse-grained authorization) and policies (fine-grained authorization) are supported. For details about permissions and authorization operations, see the Data Lake Insight User Guide.

2. Create a queue. Set Queue Type to SQL Queue, which is the computing resource of the SQL job.
   You can create a queue on the Overview, SQL Editor, or Queue Management page.

   • In the upper right corner of the Dashboard page, click to create a queue.
   • To create a queue on the Queue Management page:
     1. In the navigation pane of the DLI management console, choose Queue Management.
     2. In the upper right corner of the Queue Management page, click to create a queue.
   • To create a queue on the SQL Editor page:
     1. On the top menu bar of the DLI management console, click SQL Editor.
     2. On the left pane of the displayed Job Editor page, click . Click to the right of Queues.
   

   If the user who creates the queue is not an administrator, the queue can be used only after being authorized by the administrator. For details about how to assign permissions, see the Data Lake Insight User Guide.

Procedure

1. Install JDK 1.7 or later on the computer where JDBC is installed, and configure environment variables.
2. Obtain the DLI JDBC driver package huaweicloud-dli-jdbc-<version>.zip by referring to Downloading the JDBC or ODBC Driver Package. Decompress the package to obtain huaweicloud-dli-jdbc-<version>-jar-with-dependencies.jar.
3. On the computer using JDBC, add huaweicloud-dli-jdbc-1.1.1-jar-with-dependencies.jar to the classpath path of the Java project.
4. DLI JDBC provides two authentication modes, namely, token and AK/SK, to connect to DLI. For details about how to obtain the token and AK/SK, see Performing Authentication.
5. Run the Class.forName() command to load the DLI JDBC driver.

   Class.forName("com.huawei.dli.jdbc.DliDriver");

6. Call the GetConnection method of DriverManager to create a connection.

   Connection conn = DriverManager.getConnection(String url, Properties info);

   JDBC configuration items are passed using the URL. For details, see Table 2. JDBC configuration items can be separated by semicolons (;) in the URL, or you can dynamically set the attribute items using the Info object. For details, see Table 3.

   Table 2 Database connection parameters

   Parameter	Description
   url	The URL format is as follows: jdbc:dli://<endPoint>/projectId? <key1>=<val1>;<key2>=<val2>... EndPoint indicates the DLI domain name. ProjectId indicates the project ID. To obtain the endpoint corresponding to DLI, see Regions and Endpoints. To obtain the project ID, log in to the public cloud, move the mouse on the account, and click My Credentials from the shortcut menu. Other configuration items are listed after ? in the form of key=value. The configuration items are separated by semicolons (;). They can also be passed using the Info object.
   Info	The Info object passes user-defined configuration items. If Info does not pass any attribute item, you can set it to null. The format is as follows: info.setProperty ("Attribute item", "Attribute value").

   Table 3 Attribute items

   Item	Mandatory	Default Value	Description
   queuename	Yes	-	Queue name of DLI.
   databasename	No	-	Name of a database.
   authenticationmode	No	token	Authentication mode. Currently, token- and AK/SK-based authentication modes are supported.
   accesskey	Yes	-	AK/SK authentication key. For details about how to obtain the key, see Performing Authentication.
   secretkey	Yes	-	AK/SK authentication key. For details about how to obtain the key, see Performing Authentication.
   regionname	This parameter must be configured if authenticationmode is set to aksk.	-	Region name. For details, see Regions and Endpoints.
   servicename	This parameter must be configured if authenticationmode is set to aksk.	-	Indicates the service name, that is, dli.
   token	This parameter must be configured if authenticationmode is set to token.	-	Token authentication. For details about the authentication mode, see Performing Authentication.
   charset	No	UTF-8	JDBC encoding mode.
   usehttpproxy	No	false	Whether to use the access proxy.
   proxyhost	This parameter must be configured if usehttpproxy is set to true.	-	Access proxy host.
   proxyport	This parameter must be configured if usehttpproxy is set to true.	-	Access proxy port.
   dli.sql.checkNoResultQuery	No	false	Whether to allow invoking the executeQuery API to execute statements (for example, DDL) that do not return results. Value false indicates that invoking of the executeQuery API is allowed. Value true indicates that invoking of the executeQuery API is not allowed.
   jobtimeout	No	300	End time of the job submission. Unit: second
   iam.endpoint	No. By default, the value is automatically combined based on regionName.	-	Endpoint. For details about regions, see Regions and Endpoints.
   obs.endpoint	No. By default, the value is automatically combined based on regionName.	-	Endpoint. For details about regions, see Regions and Endpoints.
   directfetchthreshold	No	1000	If the query result is less than or equal to the preset value, the getJobResult function is called to obtain the result. Otherwise, the exported result is the returned query result.

7. Create a Statement object, set related parameters, and submit Spark SQL to DLI.

   Statement statement = conn.createStatement();

   statement.execute("select * from tb1");

8. Obtain the result.

   ResultSet rs = statement.getResultSet();

9. Display the result.

   while (rs.next()) {
   int a = rs.getInt(1);
   int b = rs.getInt(2);
   }

10. Close the connection.

    conn.close();

Example

import java.sql.*;
import java.util.Properties;

public class DLIJdbcDriverExample {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        try {
            Class.forName("com.huawei.dli.jdbc.DliDriver");
            String url = "jdbc:dli://<endpoint>/<projectId>?databasename=db1;queuename=testqueue";
            Properties info = new Properties();
            info.setProperty("authenticationmode", "aksk");
            info.setProperty("regionname", "<real region name>");
            info.setProperty("accesskey", "<real ak>");
            info.setProperty("secretkey", "<real sk>")
            conn = DriverManager.getConnection(url, info);
            Statement statement = conn.createStatement();
            statement.execute("select * from tb1");
            ResultSet rs = statement.getResultSet();
            int line = 0;
            while (rs.next()) {
                line ++;
                int a = rs.getInt(1);
                int b = rs.getInt(2);
                System.out.println("Line:" + line + ":" + a + "," + b);
            }
            statement.execute("describe tb1");
            ResultSet rs1 = statement.getResultSet();
            line = 0;
            while (rs1.next()) {
                line ++;
                String a = rs1.getString(1);
                String b = rs1.getString(2);
                System.out.println("Line:" + line + ":" + a + "," + b);
            }
        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}

Enabling JDBC Requery

If the JDBC requery function is enabled, the system automatically requeries when the query operation fails.

• To avoid repeated data insertion, non-query statements do not support requery.
• This function is available in the JDBC driver package of 1.1.5 or later. To use this function, obtain the latest JDBC driver package.

To enable the requery function, add the attributes listed in Table 4 to the Info parameter.

Set JDBC parameters, enable the requery function, and create a link. The following is an example:

import java.sql.*;
import java.util.Properties;

public class DLIJdbcDriverExample {

    private static final String X_AUTH_TOKEN_VALUE = "<realtoken>";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        try {
            Class.forName("com.huawei.dli.jdbc.DliDriver");
            String url = "jdbc:dli://<endpoint>/<projectId>?databasename=db1;queuename=testqueue";
            Properties info = new Properties();
            info.setProperty("token", X_AUTH_TOKEN_VALUE);
info.setProperty(ConnectionResource.USE_RETRY_KEY, "true"); // Enable the requery function.
info.setProperty(ConnectionResource.RETRY_TIMES_KEY, "30000");// Requery interval (ms)
info.setProperty(ConnectionResource.RETRY_INTERVALS_KEY, "5");// Requery Times
            conn = DriverManager.getConnection(url, info);
            Statement statement = conn.createStatement();
            statement.execute("select * from tb1");
            ResultSet rs = statement.getResultSet();
            int line = 0;
            while (rs.next()) {
                line ++;
                int a = rs.getInt(1);
                int b = rs.getInt(2);
                System.out.println("Line:" + line + ":" + a + "," + b);
            }
            statement.execute("describe tb1");
            ResultSet rs1 = statement.getResultSet();
            line = 0;
            while (rs1.next()) {
                line ++;
                String a = rs1.getString(1);
                String b = rs1.getString(2);
                System.out.println("Line:" + line + ":" + a + "," + b);
            }
        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}