Help Center> MapReduce Service> Component Operation Guide (LTS) (Paris Region)> Using HetuEngine> Using a Third-Party Visualization Tool to Access HetuEngine> Using PowerBI to Access HetuEngine
Updated on 2022-12-14 GMT+08:00
View PDF

Using PowerBI to Access HetuEngine

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

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

    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

    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

      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

      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.

  4. Choose Add > openLookeng ODBC 1.1 Driver > Finish.

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

  6. 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.

  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.

  8. Click Finish.

  9. To use PowerBI for interconnection, choose Get data > All > ODBC > Connect.

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

    Figure 2 Adding a data source

  11. (Optional) Enter User name and Password of the user who downloads the credential, and click Connect.

    Figure 3 Entering the database username and password

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

    Figure 4 Successful connection