Using DBeaver to Access HetuEngine

Prerequisites

• The DBeaver has been installed properly. Download the DBeaver software from https://dbeaver.io/files/6.3.5/.
  

  Currently, DBeaver 5.x and 6.x are supported.

• A human-machine user has been created in the cluster. For details about how to create a user, see Creating a HetuEngine User.

Procedure

Method 1: Using ZooKeeper to access HetuEngine

1. Download the HetuEngine client.
   1. Log in to FusionInsight Manager.
   2. Choose Cluster > Services > HetuEngine > Dashboard.
   3. In the upper right corner of the page, choose More > Download Client and download the Complete Client to the local PC as prompted.
   4. Decompress the HetuEngine client package FusionInsight_Cluster_Cluster ID_ HetuEngine_Client.tar to obtain the JDBC file and save it to a local directory, for example, D:\test.
      

      Obtaining the JDBC file:

      Obtain the hetu-jdbc-*.jar file from the FusionInsight_Cluster_Cluster ID_HetuEngine_ClientConfig\HetuEngine\xxx\ directory.

      Note: xxx can be arm or x86.

2. Download the Kerberos authentication file of the HetuEngine user.
   1. Log in to FusionInsight Manager.
   2. Choose System > Permission > User.
   3. Locate the row that contains the target HetuEngine user, click More in the Operation column, and select Download Authentication Credential.
   4. Decompress the downloaded package to obtain the user.keytab and krb5.conf files.

3. Log in to the node where the HSBroker role is deployed in the cluster as user omm, go to the ${BIGDATA_HOME}/FusionInsight_Hetu_8.1.2.2/xxx_HSBroker/etc/ directory, and download the jaas-zk.conf and hetuserver.jks files to the local PC.
   

   The version 8.1.2.2 is used as an example. Replace it with the actual version number.

   Modify the jaas-zk.conf file as follows. keyTab is the keytab file path of the user who accesses HetuEngine, and principal is Username for accessing HetuEngine@Domain name in uppercase.COM.

   Client {
   com.sun.security.auth.module.Krb5LoginModule required
   useKeyTab=true
   keyTab="D:\\tmp\\user.keytab"
   principal="admintest@HADOOP.COM"
   useTicketCache=false
   storeKey=true
   debug=true;
   };

4. Add the host mapping to the local hosts file. The content format is as follows:

   Host IP address Host name

   Example: 192.168.23.221 192-168-23-221

   

   The local hosts file in a Windows environment is stored in, for example, C:\Windows\System32\drivers\etc.

5. Configure the DBeaver startup file dbeaver.ini.
   1. Add the Java path to the file.

      -VM
      C:\Program Files\Java\jdk1.8.0_131\bin

   2. Set the ZooKeeper and Kerberos parameters by referring to the following parameters. Replace the file paths with the actual paths.

      -Dsun.security.krb5.debug=true
      -Djava.security.auth.login.config=D:\tmp\jaas-zk.conf
      -Dzookeeper.sasl.clientconfig=Client
      -Dzookeeper.auth.type=kerberos
      -Djava.security.krb5.conf=D:\tmp\krb5.conf

      

      • The Greenwich Mean Time (GMT) is not supported. If the current time zone is GMT+, add -Duser.timezone=UTC to the dbeaver.ini file to change the time zone to UTC.
      • If DBeaver is started, restart the DBeaver software for the new configuration items in the dbeaver.ini file to take effect.

6. Start the DBeaver, right-click Database Navigator, and click Create New Connection.
7. Search for Presto in the search box and double-click the Presto icon.
8. Click Edit Driver Settings.
9. Set Class Name to io.prestosql.jdbc.PrestoDriver.
10. Enter the URL of HetuEngine in the URL Template text box.

    URL format: jdbc:presto://IP address of node 1 where the ZooKeeper service resides:2181,IP address of node 2 where the ZooKeeper service resides:2181,IP address of node 3 where the ZooKeeper service resides:2181/hive/default?serviceDiscoveryMode=zooKeeper&zooKeeperNamespace=hsbroker&zooKeeperServerPrincipal=zookeeper/hadoop.hadoop.com

    Example: jdbc:presto://192.168.8.37:2181,192.168.8.38:2181,192.168.8.39:2181/hive/default?serviceDiscoveryMode=zooKeeper&zooKeeperNamespace=hsbroker&zooKeeperServerPrincipal=zookeeper/hadoop.hadoop.com

11. Click Add File and select the obtained JDBC file obtained in 1.d.
12. Click Connection properties. On the Connection properties tab page, right-click and select Add new property. Set parameters by referring to Table 1.

    Table 1 Property information

    Parameter	Example Value
    KerberosPrincipal	zhangsan NOTE: Human-machine user created in the cluster. For details, see Creating a HetuEngine User.
    KerberosKeytabPath	D:\\user.keytab NOTE: You need to configure this parameter when using the keytab mode for access.
    KerberosRemoteServiceName	HTTP
    SSL	true
    deploymentMode	on_yarn
    tenant	default NOTE: The tenant to which the user belongs needs to be configured in the cluster.
    user	zhangsan NOTE: Human-machine user created in the cluster. For details, see Creating a HetuEngine User.
    password	zhangsan@##65331853 NOTE: Password set when a human-machine user is created in the cluster. For details, see Creating a HetuEngine User. You need to configure this parameter when using username and password for access.
    KerberosConfigPath	D:\\krb5.conf
    SSLTrustStorePath	D:\\hetuserver.jks

    Figure 1 shows an example of the parameter settings.

    Figure 1 Example of parameter settings
    

13. Click OK.
14. Click Finish. The HetuEngine is successfully connected.
    

    If a message is displayed indicating that you do not have the permission to view the table, configure the permission by referring to Configuring Permissions for Tables, Columns, and Databases.

Method 2: Using HSBroker to access HetuEngine

1. Obtain the JDBC JAR file by referring to 1.
2. Open DBeaver, choose Database > New Database Connection, search for PrestoSQL, and open it.
3. Click Edit Driver Settings and set parameters by referring to the following table.

   Table 2 Driver settings

   Parameter	Value	Remarks
   Class Name	io.prestosql.jdbc.PrestoDriver	/
   URL Template	URL of HetuEngine	URL format: jdbc:presto://<HSBrokerIP1:port1>,<HSBrokerIP2:port2>,<HSBrokerIP3:port3>/hive/default?serviceDiscoveryMode=hsbroker

4. Click Add File and upload the JDBC driver package obtained in 1.
5. Click Find Class. The driver class is automatically obtained. Click OK to complete the driver setting, as shown in Figure 2.
   Figure 2 Driver settings
   

6. On the Main tab page for creating a connection, enter the user name and password, and click Test Connection. After the connection is successful, click OK, and then click Finish.
   Figure 3 Creating a connection
   

7. After the connection is successful, the page shown in Figure 4 is displayed.
   Figure 4 Successful connection