Alibaba Cloud Quick BI allows you to analyze a large amount of data online in real
time and provides abundant visualization features. This way, you can analyze data,
explore business data, and produce reports with ease by performing drag-and-drop operations
or executing SQL statements. This topic shows you how to connect Quick BI to Hologres
and analyze data in a visualized manner.
Background information
You can use a Hologres connection to efficiently connect Quick BI to Hologres. This
way, you can directly query Hologres data in Quick BI for visualized data analysis.
Note Quick BI Pro and Quick BI Standard allow you to use a Hologres connection to connect
Quick BI to Hologres. If you use another edition of Quick BI, you must use a PostgreSQL
connection. For more information about how to use a PostgreSQL connection, see
Add a cloud data source ApsaraDB RDS for PostgreSQL.
In this example, Quick BI Pro is connected to Hologres for visualized data analysis.
Limits
- You must connect Quick BI to Hologres by using a public endpoint or a classic network
endpoint, but not a virtual private cloud (VPC) endpoint.
- You can connect Quick BI to Hologres without the need to configure a whitelist in
Hologres.
- When you connect Quick BI to Hologres, you can determine the type of the connection
to be used based on the edition of Quick BI. We recommend that you use a Hologres
connection.
Connect Quick BI to Hologres to perform visualized data analysis
- Log on to the Quick BI console.
- Create a connection.
- In the Quick BI console, click Workspace in the top navigation bar.
- On the Workspace page, click Data Sources in the left-side navigation pane.
- On the Data Sources page, click Create Data Source in the upper-right corner.
- Select a type of connection that you want to configure to connect Quick BI to Hologres
and set the parameters as required.
- Choose .

- In the Add Hologres Database dialog box, set the parameters as required.
The following table describes the parameters.
Parameter |
Description |
Name |
The custom name of the connection.
|
Database Address |
The endpoint of the Hologres instance.
You can view the endpoint or port number of the Hologres instance on the Configurations tab of the instance details page in the Hologres console.
Note You must connect Quick BI to Hologres by using a public endpoint or a classic network
endpoint, but not a VPC endpoint.
|
Port Number |
The port number of the Hologres instance.
You can view the endpoint or port number of the Hologres instance on the Configurations tab of the instance details page in the Hologres console.
Note You must connect Quick BI to Hologres by using a public endpoint or a classic network
endpoint, but not a VPC endpoint.
|
Database |
The name of the database to be connected in the Hologres instance.
You can view the name of the database on the Databases tab of the instance details page in the Hologres console.
|
Schema |
The name of the schema in the specified database. The default value is public. You can also specify a custom schema. After the connection is created, all the tables
of the specified schema can be displayed. When you execute an SQL statement for ad
hoc queries, you must specify the name of the table that you want to reference in
a schema in the format of Schema name.Table name.
|
Username |
The AccessKey ID of the account that you use to connect to Hologres.
You can view the AccessKey ID on the Security Management page.
|
Password |
The AccessKey secret of the account that you use to connect to Hologres.
You can view the AccessKey secret on the Security Management page.
|
VPC Data Source |
Specifies whether the data source is in a VPC. Do not select this check box.
You must connect Quick BI to Hologres by using a public endpoint or a classic network
endpoint, but not a VPC endpoint.
|
After you set the parameters, you can click
Test Connection to test the connection between Hologres and Quick BI.
- If the The add operation is completed message appears, the connection between Hologres and Quick BI is established.
- If the Quick BI cannot connect to the specified data source. Verify that all settings are
correct message appears, the connection between Hologres and Quick BI fails to be established.
Handle the exception based on the error message.
- Click OK.
- Analyze data in a visualized manner.
After the data source is connected, click the created connection in the
My Data Sources section of the page. Then, all tables in the data source are displayed.
- If you want to perform visualized data analysis on the GUI, you can perform drag-and-drop
operations.
- If you want to perform visualized data analysis by executing SQL statements, you can
execute an SQL statement for ad hoc queries.
To use the two methods to analyze data, perform the following steps:
Configure a time filter
When you create a table in Hologres, you can set the segment_key or event_time_column property to optimize indexes for filter conditions of time types. This prevents full
table scans and accelerates queries. By default, Hologres uses the first field of
the TIMESTAMP type in a table as the segment key.
Quick BI supports SQL statements that contain placeholders. You can use an SQL statement
that contains placeholders to configure a time filter. For more information, see Use ad hoc queries for data modeling.
- Set a placeholder based on a time field.
Before you perform the following steps, you must create an internal table named LINEITEM
in Hologres and import data from the public.odps_lineitem_10g table in the MaxCompute
project MAXCOMPUTE_PUBLIC_DATA of the public dataset to the LINEITEM table. For more
information, see
Query data in Hologres.
- Go to the Data Sources page in the Quick BI console. Click Ad Hoc Query in the upper-right corner to go to the Ad Hoc Query page.
- Enter the following SQL statement to retrieve the timestamp data from the LINEITEM
table:
SELECT
*
FROM
"public"."lineitem" AS AME_T_1_
WHERE
AME_T_1_."l_shipdate" >= TO_TIMESTAMP('${report_date.get(0)}', 'yyyy-MM-dd hh24:mi:ss')
AND AME_T_1_."l_shipdate" <= TO_TIMESTAMP('${report_date.get(1)}', 'yyyy-MM-dd hh24:mi:ss')
- Click Parameter. In the Parameter panel, set a placeholder in the SQL statement.
- In the Variable Type column, select YYYY-MM-DD HH-MI-SS from the drop-down list.
- Click determine. Then, click Confirm edit.
- On the dashboard editing page, click the Filter Bar icon in the top toolbar. Then, click the New filter icon on the canvas. In the Query condition setting dialog box, select the placeholder that you set as a filter field.
- On the dashboard editing page, find the chart that you want to manage, click the More icon in the upper-right corner, and then select View SQL Statements. The following SQL statement is obtained:
SELECT
AME_T_1_."l_shipmode" AS T_A0_2_,
AME_T_1_."l_shipinstruct" AS T_A1_3_,
SUM(AME_T_1_."l_extendedprice") AS T_A2_4_
FROM
"public"."lineitem" AS AME_T_1_
WHERE
AME_T_1_."l_shipdate" >= TO_TIMESTAMP('1993-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
AND AME_T_1_."l_shipdate" <= TO_TIMESTAMP('1998-12-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
GROUP BY
AME_T_1_."l_shipmode",
AME_T_1_."l_shipinstruct"
LIMIT
1000 OFFSET 0
- Verify whether the segment key takes effect.
You can check whether the Segment Filter keyword appears in the execution plan of the obtained SQL statement to verify whether
the segment key takes effect.
- Log on to the Hologres console. In the left-side navigation pane, click Instances.
- Go to the details page of your Hologres instance. In the left-side pane, click Databases.
- On the Database Authorization page, click SQL Editor in the top navigation bar.
- On the SQL Editor tab, click the Ad-hoc Query icon in the upper-left corner.
- On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statement in the SQL editor, and then
click Run.
The following SQL statement is used to query the execution plan of the obtained SQL
statement and verify whether the segment key takes effect.

explain SELECT
AME_T_1_."l_shipmode" AS T_A0_2_,
AME_T_1_."l_shipinstruct" AS T_A1_3_,
SUM(AME_T_1_."l_extendedprice") AS T_A2_4_
FROM
"public"."lineitem" AS AME_T_1_
WHERE
AME_T_1_."l_shipdate" >= TO_TIMESTAMP('1993-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
AND AME_T_1_."l_shipdate" <= TO_TIMESTAMP('1998-12-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
GROUP BY
AME_T_1_."l_shipmode",
AME_T_1_."l_shipinstruct"
LIMIT
1000 OFFSET 0
- On the Results tab, view the execution plan.
Limit (cost=0.00..1.01 rows=1 width=24)
-> Gather Motion (cost=0.00..1.01 rows=1 width=24)
-> Limit (cost=0.00..1.01 rows=1 width=24)
-> Partial HashAggregate (cost=0.00..1.01 rows=1 width=24)
Group Key: l_shipmode, l_shipinstruct
-> Redistribute Motion (cost=0.00..1.01 rows=10 width=24)
-> Result (cost=0.00..1.01 rows=10 width=24)
-> Partial HashAggregate (cost=0.00..1.01 rows=10 width=24)
Group Key: l_shipmode, l_shipinstruct
-> Parallelism (Gather Exchange) (cost=0.00..1.01 rows=32 width=24)
-> Result (cost=0.00..1.01 rows=32 width=24)
-> DecodeNode (cost=0.00..1.01 rows=32 width=24)
-> Partial HashAggregate (cost=0.00..1.01 rows=32 width=24)
Group Key: l_shipmode, l_shipinstruct
-> Index Scan using holo_index:[1] on lineitem (cost=0.00..1.00 rows=1000 width=24)
Segment Filter: ((l_shipdate >= '1993-01-01 00:00:00+08'::timestamp with time zone) AND (l_shipdate <= '1998-12-31 23:59:59+08'::timestamp with time zone))
The execution plan contains the Segment Filter keyword. This indicates that the segment key has taken effect, and a time filter
can be configured by using an SQL statement that contains placeholders.