Using Power BI to Access HetuEngine

Updated on 2024-11-29 GMT+08:00

View PDF

Use Power BI 1.2.0 as an example to describe how to access HetuEngine in a security cluster.

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

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
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
```
  NOTE:
  
  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.
```
  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.

Figure 2 Searching for ODBC
Choose Add > openLookeng ODBC 1.2.0 Driver > Finish.

Figure 3 Adding a driver
Enter the name and description as shown in the following figure and click Next.

Figure 4 Entering the name
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
  NOTE:
  - To obtain the IP addresses and port numbers of the HSFabric and HSBroker nodes, perform the following operations:
    
    Log in to FusionInsight Manager.
    
    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.
    
    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
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
Click Finish.

Figure 8 Completing the connection
To use Power BI for interconnection, choose Get data > All > ODBC > Connect.

Figure 9 Selecting ODBC
Select the data source to be added and click OK.

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

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

Figure 12 Successful connection

Parent topic: Using a Third-Party Visualization Tool to Access HetuEngine

Previous topic: Using Tableau to Access HetuEngine

Next topic: Using Yonghong BI to Access HetuEngine

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot