Using Power BI to Access HetuEngine

Prerequisites

• Power BI has been installed.
• The JDBC JAR file has been obtained. For details, see 1.
• A human-machine user, for example, hetu_user, has been created in the cluster. For details, see Creating a HetuEngine User. For clusters with Ranger authentication enabled, the Ranger permission must be added to user hetu_user based on service requirements. For details, see Adding a Ranger Access Permission Policy for HetuEngine.
• A compute instance has been created and is running properly. For details, see Creating a HetuEngine Compute Instance.

Procedure

1. Use the default configuration to install the hetu-odbc-win64.msi driver. Download the driver from https://download.openlookeng.io/.
   Figure 1 Downloading the driver
   

2. Configure data source driver.
   1. Run the following commands in the local command prompt to stop the ODBC service that is automatically started.

      cd C:\Program Files\openLooKeng\openLooKeng ODBC Driver 64-bit\odbc_gateway\mycat\bin

      mycat.bat stop

      If the following information is displayed, the ODBC service is stopped:

      wrapper  | Stopping the Mycat-server service...
      wrapper  | Mycat-server stopped.

   2. Replace the JDBC driver.

      Copy the JDBC JAR file obtained in 1 to the C:\Program Files\openLooKeng\openLooKeng ODBC Driver 64-bit\odbc_gateway\mycat\lib directory and delete the original hetu-jdbc-1.0.1.jar file from the directory.

   3. Edit the protocol prefix of the ODBC server.xml file.

      Change the property value of server.xml in the C:\Program Files\openLooKeng\openLooKeng ODBC Driver 64-bit\odbc_gateway\mycat\conf directory from <property name="jdbcUrlPrefix">jdbc:lk://</property> to

      <property name="jdbcUrlPrefix">jdbc:trino://</property>.

   4. Configure the connection mode of using the user name and password.
      Create a jdbc_param.properties file in a user-defined path, for example, C:\hetu, and add the following content to the file:

      user=admintest
      Password=Password

      

      user: indicates the username of the created human-machine user, for example, admintest.

      password: indicates the password of the created human-machine user. Configuration files containing authentication passwords pose security risks. Delete such files after configuration or store them securely.

   5. Run the following commands to restart the ODBC service:

      cd C:\Program Files\openLooKeng\openLooKeng ODBC Driver 64-bit\odbc_gateway\mycat\bin

      mycat.bat restart

      If the following information is displayed, the ODBC service is restarted:

      wrapper  | The Mycat-server service was not running.
      wrapper  | Starting the Mycat-server service...
      wrapper  | Mycat-server started.

      

      The ODBC service must be stopped each time the configuration is modified. After the modification is complete, restart the ODBC service.

3. On the Windows Control Panel, enter odbc to search for the ODBC management program.
   Figure 2 Searching for ODBC
   

4. Choose Add > openLookeng ODBC 1.2.0 Driver > Finish.
   Figure 3 Adding a driver
   

5. Enter the name and description as shown in the following figure and click Next.
   Figure 4 Entering the name
   

6. Configure parameters by referring to the following figure.
   1. Connect URL indicates the URL format of the ODBC connection for accessing the HetuEngine service. The HSFabric and HSBroker modes are supported.
      • HSFabric mode

        <HSFabricIP1:port1>,<HSFabricIP2:port2>,<HSFabricIP3:port3>/Catalog name/Schema name?serviceDiscoveryMode=hsfabric&tenant=Tenant name

        Example:

        192.168.8.37:29902,192.168.8.38:29902,192.168.8.39:29902/hive/default?serviceDiscoveryMode=hsfabric&tenant=default

      • HSBroker mode

        <HSBrokerIP1:port1>,<HSBrokerIP2:port2>,<HSBrokerIP3:port3>/Catalog name/Schema name?serviceDiscoveryMode=hsbroker&tenant=default

        Example:

        192.168.8.37:29860,192.168.8.38:29860,192.168.8.39:29860/hive/default?serviceDiscoveryMode=hsbroker&tenant=Tenant name

      

      • To obtain the IP addresses and port numbers of the HSFabric and HSBroker nodes, perform the following operations:
        1. Log in to FusionInsight Manager.
        2. Choose Cluster > Services > HetuEngine. Click the Instance tab to obtain the service IP addresses of all HSFabric or HSBroker instances. You can select one or more normal instances for connection.
        3. To obtain the port numbers, choose Cluster > Services > HetuEngine. Click Configurations then All Configurations.

           Search for gateway.port to obtain the HSFabric port number. The default port number is 29902 in security mode and 29903 in normal mode.

           Search for server.port to obtain the HSBroker port number. The default port number is 29860 in security mode and 29861 in normal mode.

      • If the connection fails, disable the proxy and try again.
      • The tenant parameter is optional. If it is not configured, a random tenant is used.
   2. Connect Config: Select the jdbc_param.properties file prepared in 2.d.
   3. User name: Enter the username for downloading the credential.
   Figure 5 Configuring the user name
   

7. Click Test DSN to test the connection. If the connection is successful and both Catalog and Schema contain content, the connection is successful. Click Next.
   Figure 6 Testing the connection
   
   Figure 7 Viewing the content
   

8. Click Finish.
   Figure 8 Completing the connection
   

9. To use Power BI for interconnection, choose Get data > All > ODBC > Connect.
   Figure 9 Selecting ODBC
   

10. Select the data source to be added and click OK.
    Figure 10 Adding a data source
    

11. Enter User name and Password of the user who downloads the credential, and click Connect.
    Figure 11 Entering the database username and password
    

12. After the connection is successful, all table information is displayed, as shown in Figure 12.
    Figure 12 Successful connection