On this page

Procedure
Step 1: Install and Connect Kyuubi to DLI
Step 2: Install FineBI and the Data Connection Driver
Step 3: Connect FineBI to Kyuubi
Common Operations: Setting SQL Job Parameters

Show all

Help Center/ Data Lake Insight/ Best Practices/ Connecting BI Tools to DLI for Data Analysis/ Configuring FineBI to Connect to DLI Using Kyuubi for Data Query and Analysis

Configuring FineBI to Connect to DLI Using Kyuubi for Data Query and Analysis

Updated on 2025-02-26 GMT+08:00

View PDF

FineBI is an intelligent, visual tool that focuses on data analysis and visualization. It can connect to various data sources and transform intricate data into user-friendly charts and dashboards, allowing for swift and efficient data analysis.

Kyuubi is a distributed SQL query engine that provides standard SQL APIs. This allows you to easily access and analyze data stored on big data platforms.

By connecting FineBI to Kyuubi, you can use Kyuubi to access DLI for data query and analysis. This integration simplifies data access and provides unified data management and analysis, enabling you to gain deeper insights into data.

This section describes how to configure FineBI to connect to DLI using Kyuubi to access and analyze data in DLI.

Procedure

Figure 1 Process

Step 1: Install and Connect Kyuubi to DLI: Install and configure Kyuubi to ensure that Kyuubi can connect to DLI.
Step 2: Install FineBI and the Data Connection Driver: Install FineBI and the data connection driver.
Step 3: Connect FineBI to Kyuubi: Create a data connection in the BI tool and connect to Kyuubi using JDBC.

Step 1: Install and Connect Kyuubi to DLI

To access Kyuubi from an external network, ensure that an EIP is bound to the ECS and ports 10009 and 3309 are enabled in the inbound rules of the security group.

Install JDK.

Install JDK in your development environment before you can install and use Kyuubi.

Java SDKs require JDK 1.8 or later. To ensure compatibility with future versions, you are advised to use version 1.8.
1. Download the JDK.
  Download the JDK 1.8 installation package from Oracle official website and install it.
  
  In this example, jdk-8u261-linux-x64.tar.gz is used.
2. Upload the JDK package to the corresponding directory of the Linux server and run the extraction command. In this example, the package is uploaded to /usr/local.
```
sudo tar -xzf jdk-8u261-linux-x64.tar.gz -C /usr/local/
```
3. Configure environment variables.
  Add the following content to the .bashrc or .profile file:
```
export JAVA_HOME=/usr/local/jdk-1.8.0_261
export PATH=$PATH:$JAVA_HOME/bin
```
1. Apply the environment variables.
```
source ~/.bashrc
```
2. Run java -version to check whether the installation is successful. If the version number is displayed in the command output, the Java environment is successfully installed.
```
java version "1.8.0_261"
Java(TM) SE Runtime Environment (build 1.8.0_261-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.261-b12, mixed mode)
```
Install Kyuubi.
1. Download the Kyuubi installation package. Refer to Deploying Kyuubi to learn more how to install Kyuubi.
2. Extract the downloaded package.
```
tar -xzf kyuubi-{version}-bin.tar.gz
```
3. (Optional) Configure environment variables.
  Add the bin directory of Kyuubi to PATH environment variables to ensure that Kyuubi scripts can be called at any location.

Connect Kyuubi to DLI.

Add the DLI driver to the root directory of Kyuubi.
Place the driver in Kyuubi's root directory /externals/engines/jdbc.

Ensure that the user group and permissions of the plug-in are the same as those of other JAR files.

Modify the Kyuubi configuration file.

cd $KYUUBI_HOME/confvi kyuubi-defaults.conf

Table 1 describes the parameters.

**Table 1** Kyuubi parameters
Parameter	Description	Mandatory	Example Value
kyuubi.engine.type	JDBC service type. Set it to dli.	Yes	jdbc
kyuubi.engine.jdbc.type	Engine type. Set it to dli.	Yes	dli
kyuubi.engine.jdbc.driver.class	Name of the driver class used to connect JDBC. Set it to com.huawei.dli.jdbc.DliDriver.	Yes	com.huawei.dli.jdbc.DliDriver
kyuubi.engine.jdbc.connection.url	URL used to connect JDBC. Format: jdbc:dli://{dliendpoint}/{projectId}.	Yes	jdbc:dli://{dliendpoint} /{projectId}
kyuubi.engine.jdbc.session.initialize.sql	Initialization SQL statements executed when a JDBC session is established.	No	select 1 If select 1 is displayed on the DLI management console, the initialization is successful.
kyuubi.frontend.protocols	Frontend protocol supported by Kyuubi. Kyuubi supports various frontend protocols, allowing you to interact with Kyuubi through different APIs.	Yes	mysql thrift_binary
kyuubi.engine.dli.schema.show.name	How the Kyuubi engine shows the schema name of data source APIs when you run show schemas or show databases. true: The DLI name is included as the prefix when the schema name is shown. false: The DLI name is not included when the schema name is shown. For example, if it is set to true and the DLI name is hive, the output is in the hive.default format when you run show schemas. If it is set to false, the output is in the default format.	No	true false
kyuubi.engine.dli.jdbc.connection.region	Region name.	Yes	regionname=ap-southeast-2
kyuubi.engine.dli.jdbc.connection.queue	DLI queue name.	Yes	dli_test
kyuubi.engine.dli.jdbc.connection.database	Default database name used when the Kyuubi engine connects to the DLI data source through JDBC.	Yes	tpch
kyuubi.engine.dli.jdbc.connection.ak	AK that acts as the authentication key. Set it if authenticationmode is aksk.	Yes	accesskey=your-access-key
kyuubi.engine.dli.jdbc.connection.sk	Region name. Set it if authenticationmode is aksk.	Yes	secretkey=your-secret-key
kyuubi.engine.dli.jdbc.connection.project	ID of the project where DLI resources are.	Yes	0b33ea2a7e0010802fe4c009bb05076d
kyuubi.engine.dli.sql.limit.time.sec	Execution duration limit of a SQL query. The default value is 600 seconds.	No	300
kyuubi.engine.dli.result.line.num.limit	Maximum number of data records returned for a SQL query. By default, 100,000 data records are returned. Setting it to -1 means that the number of returned data records is not limited.	Yes	50000
kyuubi.engine.dli.small.file.merge	Whether to enable automatic small file merging. The default value is false, indicating that the function is disabled. true: Enable false: Disable	Yes	true
kyuubi.engine.dli.bi.type	BI tool type. The options are fine, grafana, superset, tableau, power, dbt, and yongHong.	Yes	fine
kyuubi.engine.dli.boolean.type.to.int	Whether DLI's Boolean data is returned as 1/0 or true/false. If the BI tool type is grafana, set this parameter to true. true: returned as 1/0 (1: true; 0: false) false: returned as true/false. The default value is false.	No	false
kyuubi.engine.dli.set.conf.transform.to.annotation	Allows you to set set spark in SQL statements. Set it to true for Power BI, FineBI, Superset, and DBT.	No	true
kyuubi.engine.dli.set.conf.sql.suffix	Allows you to set set spark at the end of SQL statements. Set it to true for Power BI and DBT.	No	true
kyuubi.engine.dli.result.cache.enable	Whether to enable database table data caching. Once enabled, database table data is automatically cached. The default value is true. true: Enable false: Disable	No	true
kyuubi.engine.dli.cache.limit.line.num	Maximum number of data records that can be cached. By default, 100,000 data records are cached. Setting it to -1 means that the number of cached data records is not limited.	No	1000
kyuubi.engine.dli.cache.time.sec	Cache time. The default value is 1800 seconds.	No	1800
kyuubi.operation.incremental.collect	Kyuubi pre-loads select result data into the cache to accelerate data reading. You are advised to disable this function to avoid memory OOM issues when dealing with large volumes of data.	No	false Setting it to false indicates disabling preloading.
kyuubi.engine.jdbc.memory	Memory of the JDBC engine process. The default value is 1g. You are advised to set it to at least 5g to increase memory for the JDBC engine process.	No	5g

Quickly start Kyuubi.
Go to the root directory /bin of the ECS and run the following command to start Kyuubi:
```
cd /bin
./kyuubi start  restart
```
Once the start is successful, run SQL statements to check whether the connection between Kyuubi and DLI is normal.

(Optional) Configure the hosts file to speed up access to Kyuubi.

To make access to Kyuubi faster, you are advised to add the Kyuubi host IP address to the /etc/hosts file on the host.
1. Run ifconfig to check the IP address of the host.
  Figure 2 Checking the host IP address
2. Add the IP address to the /etc/hosts file.
  Figure 3 Adding the host IP address to the /etc/hosts file

Step 2: Install FineBI and the Data Connection Driver

Download and install FineBI.
1. Download the FineBI installation package.
2. Find the downloaded FineBI installation program file.
3. Double-click the installation program.
4. Follow the installation wizard to perform installation operations, including accepting the license agreement, selecting the installation type (typical or custom), and setting the installation directory.
Integrate the JDBC driver with FineBI.
1. Download the data driver. Download the Hive JDBC driver package. Version 2.6.23 is recommended.
2. Integrate the driver plug-in with FineBI.
  1. Open FineBI.
  2. Choose Data Connection > Data Connection Management.
  3. Click New Driver. In the driver list, select the driver in 2.a.

Step 3: Connect FineBI to Kyuubi

Open FineBI.
Choose Data Connection > Data Connection Management.
Click Add Data Source.
Select a database type in the data source wizard. In this example, Hadoop Hive is selected.
Configure database connection information.
- Data Connection Name: Enter a data connection name.
- Driver: Select the driver in 2.a.
- Database Name: Enter the DLI database name.
- Host: Enter the IP address of the host where Kyuubi is installed.
- Port: Enter the port used to access the Kyuubi host. The default port is 10009.
- Authentication Method: Select Username and Password for this example.
- Username: username used to access the Kyuubi database.
- Password: password used to access the Kyuubi database.
- Data Connection URL:
  Hive 0.11.0 or later:
```
jdbc:hive2://localhost:10009/databasename
```
```
Example: jdbc:hive2://100.xx.xxx.243:10009/tpch
```

Common Operations: Setting SQL Job Parameters

Make sure that the following parameters are enabled in the /conf/kyuubi-defaults.conf file of Kyuubi:
```
kyuubi.engine.dli.set.conf.transform.to.annotation=true
```

Add annotation parameters to the end of SQL statements.

set Parameter
Example:
set dli.sql.current.database=tpch
set dli.sql.shuffle.partitions=10

The execution result in the SQL Editor of DLI is as follows: The set parameters are changed to annotations and submitted to DLI for execution.

Figure 4 Viewing the configured parameters in the SQL Editor of DLI
Click to enlarge

Parent topic: Connecting BI Tools to DLI for Data Analysis

Previous topic: Configuring Power BI to Connect to DLI Using Kyuubi for Data Query and Analysis

Next topic: Configuring Tableau to Connect to DLI Using Kyuubi for Data Query and Analysis

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