Using DBeaver to Access Phoenix

Prerequisites

• DBeaver 6.3.5 has been installed. You can download the DBeaver installation package by clicking https://dbeaver.io/files/6.3.5/dbeaver-ce-6.3.5-x86_64-setup.exe.
• An MRS 3.1.0 cluster, with HBase installed and Kerberos authentication disabled, has been created.
• The HBase client has been installed.
• JDK 1.8.0_x has been installed.

Procedure

1. Add the bin directory of JDK 1.8.0_ x, for example, C:\Program Files\Java\jdk1.8.0_121\bin, to the dbeaver.ini file in the DBeaver installation directory.
   Figure 1 Adding the bin directory of JDK
   

2. Download the Phoenix software package from https://archive.apache.org/dist/phoenix/apache-phoenix-5.0.0-HBase-2.0/bin/apache-phoenix-5.0.0-HBase-2.0-bin.tar.gz and decompress it to obtain phoenix-5.0.0-HBase-2.0-client.jar.
3. Download the hbase-site.xml file from Client installation directory/HBase/hbase/conf on the node where the client is installed. Use the compression software to open the phoenix-5.0.0-HBase-2.0-client.jar file obtained in 2 and drag hbase-site.xml to the JAR file.
4. Open DBeaver. On the navigation pane, choose File > New > DBeaver > Database Connection.
   Figure 2 Creating a database connection
   

5. Click Next. In the Select your database dialog box, select Apache Phoenix and click Next.
   Figure 3 Selecting a database
   

6. Click Edit Driver Settings.
   Figure 4 Edit Driver Settings
   

7. Click Add File and select the prepared phoenix-5.0.0-HBase-2.0-client.jar file. If there are multiple driver packages, delete them and retain only added phoenix-5.0.0-HBase-2.0-client.jar.
   Figure 5 Deleting original driver packages
   
   Figure 6 Adding the Phoenix JAR file
   

8. Click Find Class and select org.apache.phoenix.jdbc.PhoenixDriver for Driver class.
   Figure 7 Loading a driver class
   

9. Add the ZooKeeper Base Path.
   1. Log in to FusionInsight Manager and choose Cluster > Services > HBase. On the Dashboard tab page that is displayed, click the link next to HMaster WebUI to access the HBase web UI. Search for ZooKeeper Base Path and obtain its value. As shown in the following figure, the value of ZooKeeper Base Path is /hbase.
      Figure 8 Viewing the value of ZooKeeper Base Path
      
   2. Add a colon (:) and the ZooKeeper Base Path value, that is, :/hbase to the end of the original URL for URL Template and click OK.
      Figure 9 Configuring URL Template
      

10. Configure EIPs. If the network between local Windows hosts and the cluster is disconnected, configure an EIP for each HBase node and ZooKeeper node, and add the mapping between the EIPs of all nodes and the host domain names to the hosts file on the local Windows hosts. An example is as follows:

    

    

    If Windows ECSs are used and they can communicate with the cluster, you do not need to configure EIPs.

11. Log in to FusionInsight Manager, choose Cluster > Services > ZooKeeper, and click the Instance tab.

    Select a node and enter the EIP of the node in Host. (If a Windows ECS is selected and it can communicate with the cluster properly, enter the service IP address of the ECS.)

    Figure 10 Configuring Host
    

12. Click Test Connection. If the information shown in Figure 11 is displayed, the interconnection is successful. Click OK.
    Figure 11 Connection Test dialog box
    

13. Log in to the node where the HBase client is installed as the client installation user and run the following commands to create the MY_NS namespace:

    cd Client installation directory

    source bigdata_env

    hbase shell

    create_namespace "MY_NS"

14. Open DBeaver and choose SQL Editor > New SQL Editor to run related SQL statements.
    1. Enter the following commands in the editor and choose SQL Editor > Execute SQL Statement to create the TEST table in the DEFAULT namespace:

       CREATE TABLE IF NOT EXISTS TEST (id VARCHAR PRIMARY KEY, name VARCHAR);

       UPSERT INTO TEST(id,name) VALUES ('1','jamee');

    2. Enter the following commands in the editor and choose SQL Editor > Execute to create the TEST table in the MY_NS namespace and inset data to the namespace:

       CREATE TABLE IF NOT EXISTS MY_NS.TEST (id integer not null primary key, name varchar);

       UPSERT INTO MY_NS.TEST VALUES(1,'John');

       UPSERT INTO MY_NS.TEST VALUES(2,'Tom');

       UPSERT INTO MY_NS.TEST VALUES(3,'Manson');

       UPSERT INTO MY_NS.TEST VALUES(4,'Aurora');

15. Right-click the connection name, click Refresh, and click on the left of the connection name to view the tables created in DEFAULT and MY_NS.
    Figure 12 Viewing tables