Configuring Yonghong BI to Connect to DLI for Data Query and Analysis

Preparations

• Environment requirements:
  • Yonghong BI has been installed.
• DLI JDBC driver:

  Download the JDBC driver huaweicloud-dli-jdbc-xxx-dependencies.jar from the DLI management console.

• Connection information:

  Table 1 Connection information

  Item	Description	How to Obtain
  DLI's AK/SK	AK/SK-based authentication refers to the use of an AK/SK pair to sign requests for identity authentication.	Obtaining an AK/SK
  DLI's endpoint address	Endpoint of a cloud service in a region.	Obtaining an Endpoint
  DLI's project ID	Project ID, which is used for resource isolation.	Obtaining a Project ID
  DLI's region information	DLI's region information	Regions and Endpoints

Step 1: Creating a DLI Data Connection on Yonghong BI

1. Start Yonghong BI.
2. On the Yonghong BI page, click Add Data Source.
3. On the New Connection Type page, choose GENERIC as the data source type.
4. Configure the new connection.

   Driver: Upload DLI's JDBC driver you have downloaded.

   URL: Enter the URL of DLI JDBC. Table 2 describes the URL format. Table 3 describes attribute configuration items.

   Specify a database.

   

   • In Schema, you can optionally enter the name of the database to be accessed. If you enter the name, only tables in the database are displayed during data set creation. If you do not enter the name, tables in all databases are displayed during data set creation.
   • Retain default values for other parameters. You do not need to select Request Login.

   Table 2 Database connection parameters

   Parameter

   Description

   URL

   The URL format is as follows: jdbc:dli://<endPoint>/<projectId>?<key1>=<val1>;<key2>=<val2>... NOTE: endpoint indicates DLI's endpoint. Obtain it by referring to Regions and Endpoints. projectId indicates the project ID, which can be obtained from the My Credentials page of the public cloud platform. ? is followed by other configuration items, with each item listed in the "key=value" format and multiple items are separated by semicolons (;). See Table 3.

   Table 3 Attribute configuration items

   Attribute (key)	Mandatory	Default Value (value)	Description
   enginetype	No	-	Type of the engine that executes jobs.
   queuename	Yes	-	DLI queue name.
   databasename	No	-	Default database to access. If not specified in the URL, you need to use db.table (for example, select * from dbother.tabletest) to access tables in the database.
   accesskey	Mandatory when authenticationmode is aksk.	-	Authentication mode that JDBC supports.
   secretkey	Mandatory when authenticationmode is aksk.	-	Authentication mode that JDBC supports.
   regionname	Mandatory when authenticationmode is aksk.	-	Obtain it by referring to Regions and Endpoints.
   servicename	Mandatory when authenticationmode is aksk.	-	Set it to dli as Yonghong BI connects to DLI.
   catalog	No	dli	Type of metadata that the job reads.

5. On the tool bar of the displayed page, click Test Connection. Once the test is complete, click Save. Enter the data source name and save the data source.
   

   Currently, you can only save the data source to an existing folder as you are not allowed to save any data sources to the root directory.

Step 2: Create a DLI Dataset on Yonghong BI

1. On the home page of the Yonghong SaaS production environment, click Create Data Set in the navigation pane on the left.
   Figure 1 Creating a dataset
   

2. On the displayed page, click SQL Data Set.
   Figure 2 Creating a SQL dataset
   

3. On the displayed page, select the added DLI data source from the Connection drop-down list box.
   Figure 3 Selecting a data source
   

4. In the Table area on the left pane, right-click and choose Update to update tables. All databases and their tables are listed in the area. Figure 4 shows the page displayed when Table Structure is not configured during connection creation.
   Figure 4 Updating tables
   

5. In the SQL Statement area on the left pane, enter the select * from table_name command to query tables. On the Preview Data page on the right pane, click . Metadata of the table, including fields and field types, is displayed.
   Figure 5 Querying the table
   

6. Click in the right pane to query data details.
   Figure 6 Querying data of the table
   

7. On the tool bar of the displayed page, click Save.

Once Yonghong BI is connected to the DLI data source and a dataset is created, you can create BI charts in Yonghong BI as needed.