Using PowerBI to Access HetuEngine

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

View PDF

Prerequisites

PowerBI has been installed.
The JDBC JAR file has been obtained. For details, see 1.
A human-machine user has been created in the cluster. For details about how to create a user, see Creating a HetuEngine User.

Procedure

Use the default configuration to install hetu-odbc-win64.msi. Download link: https://openlookeng.io/en/download/.

Figure 1 Downloading the driver
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
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:presto://</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=admintest@##65331853
```
  NOTE:
  
  user: indicates the username of the created human-machine user, for example, admintest.
  
  password: indicates the password of the created human-machine user, for example, admintest@##65331853.
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
  
  NOTE:
  
  The ODBC service must be stopped each time the configuration is modified. After the modification is complete, restart the ODBC service.
On the Windows Control Panel, enter odbc to search for the ODBC management program.
Choose Add > openLookeng ODBC 1.1 Driver > Finish.
Enter the name and description as shown in the following figure and click Next.
Configure parameters by referring to the following figure. Obtain <HSBrokerIP1:port1>,<HSBrokerIP2:port2>,<HSBrokerIP3:port3>/hive/default?serviceDiscoveryMode=hsbroker for Connect URL by referring to 2. Select the jdbc_param.properties file prepared in 2.d for Connect Config. Set User name to the user name that is used to download the credential.
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.
Click Finish.
To use PowerBI for interconnection, choose Get data > All > ODBC > Connect.
Select the data source to be added and click OK.

Figure 2 Adding a data source
(Optional) Enter User name and Password of the user who downloads the credential, and click Connect.

Figure 3 Entering the database username and password
After the connection is successful, all table information is displayed, as shown in Figure 4.

Figure 4 Successful connection