Hologres:Quick BI

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.

• You can configure a whitelist for access control on Quick BI only when you connect Quick BI to Hologres over the Internet or a specified VPC.

Connect Quick BI to Hologres to perform visualized data analysis

1. Log on to the Quick BI console.

2. Create a connection.

   1. In the Quick BI console, click Workspace in the top navigation bar.

   2. On the Workspace page, click Data Sources in the left-side navigation pane.

   3. On the Data Sources page, click Create Data Source.

   4. Select a type of connection that you want to configure to connect Quick BI to Hologres and configure the parameters as required.

      1. Select Alibaba Hologres.

      2. In the Alibaba Hologres dialog box, configure the parameters as required.

         • Parameters required for connecting Quick BI to Hologres over a specified VPC

           Parameter	Description	Remarks
           Name	The name of the data source. Specify this parameter based on your business requirements. It is used to distinguish data sources.	None.
           Database Address	The VPC endpoint of the Hologres instance to which you want to connect Quick BI.	You can log on to the Hologres console, go to the Instance Details page, and obtain the VPC endpoint in the Endpoint column in the Network Information section. Note The value of this parameter does not include the port number. Example: hgpostcn-cn-nwy364b5v009-cn-shanghai-vpc-st.hologres.aliyuncs.com.
           Port Number	The port number of the VPC with which the Hologres instance is associated.	You can log on to the Hologres console, go to the Instance Details page, and obtain the port number of the VPC endpoint in the Endpoint column in the Network Information section.
           Database	The name of the database to be connected in the Hologres instance.	You can view the name of the database on the Database Management page by clicking Database Management on the Instance Details page of the Hologres console.
           Schema	The name of the schema in the specified database. The default value is public.	You can specify a custom schema. After the connection is created, all the tables in the specified schema can be displayed. To 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.
           AccessKey ID	The AccessKey ID of your Alibaba Cloud account.	You can obtain the AccessKey ID from the Security Management page.
           AccessKey Secret	The AccessKey secret of your Alibaba Cloud account.	You can obtain the AccessKey secret from the Security Management page.
           VPC Data Source	You need to select this item.	None.
           AccessKey ID	The AccessKey ID of the account that is used to purchase the Hologres instance.	None.
           AccessKey Secret	The AccessKey secret of the account that is used to purchase the Hologres instance.	None.
           Instance ID	The ID of the Hologres instance.	None.
           Region	The region where the Hologres instance resides.	None.

         • Parameters required for connecting Quick BI to Hologres over the Internet

           Parameter	Description	Remarks
           Name	The name of the data source. Specify this parameter based on your business requirements. It is used to distinguish data sources.	None.
           Database Address	The public endpoint of the Hologres instance to which you want to connect Quick BI.	You can log on to the Hologres console, go to the Instance Details page, and obtain the public endpoint in the Endpoint column in the Network Information section. Note The value of this parameter does not include the port number.
           Port Number	The port number of the public endpoint used by the Hologres instance.	You can log on to the Hologres console, go to the Instance Details page, and obtain the port number of the public endpoint in the Endpoint column in the Network Information section.
           Database	The name of the database to be connected in the Hologres instance.	You can view the name of the database on the Database Management page by clicking Database Management on the Instance Details page of the Hologres console.
           Schema	The name of the schema in the specified database. The default value is public.	You can specify a custom schema. After the connection is created, all the tables in the specified schema can be displayed. To 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.
           AccessKey ID	The AccessKey ID of your Alibaba Cloud account.	You can obtain the AccessKey ID from the Security Management page.
           AccessKey Secret	The AccessKey secret of your Alibaba Cloud account.	You can obtain the AccessKey secret from the Security Management page.
           VPC Data Source	Do not select this item.	None.

         • Parameters required for connecting Quick BI to Hologres over the classic network

           Parameter	Description	Remarks
           Name	The name of the data source. Specify this parameter based on your business requirements. It is used to distinguish data sources.	None.
           Database Address	The endpoint of the Hologres instance in the classic network.	You can log on to the Hologres console, go to the Instance Details page, and obtain the classic network endpoint in the Endpoint column in the Network Information section. Note The value of this parameter does not include the port number. Example: hgpostcn-cn-nwy364b5v009-cn-shanghai-vpc.hologres.aliyuncs.com.
           Port Number	The port number of the classic network endpoint used by the Hologres instance.	You can log on to the Hologres console, go to the Instance Details page, and obtain the port number of the classic network endpoint in the Endpoint column in the Network Information section.
           Database	The name of the database to be connected in the Hologres instance.	You can view the name of the database on the Database Management page by clicking Database Management on the Instance Details page of the Hologres console.
           Schema	The name of the schema in the specified database. The default value is public.	You can specify a custom schema. After the connection is created, all the tables in the specified schema can be displayed. To 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.
           AccessKey ID	The AccessKey ID of your Alibaba Cloud account.	You can obtain the AccessKey ID from the Security Management page.
           AccessKey Secret	The AccessKey secret of your Alibaba Cloud account.	You can obtain the AccessKey secret from the Security Management page.
           VPC Data Source	Do not select this item.	None.

   5. After you configure 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. In this case, handle the exception based on the error message.

   6. Click OK.

3. Display data analysis results in a visualized manner.

   After the data source is connected, click the created connection in the My Data Sources section of the Data Sources 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.

   Procedures:

   • Perform drag-and-drop operations.

     1. Find a table for which you want to create a dataset and click the icon in the Actions column.

     2. In the Create Dataset dialog box, configure the Name and Save To parameters.

     3. Click OK.

     4. On the Datasets page, find the dataset that you want to manage and click an icon in the Actions column to perform the related operation.

        • Click the icon and view the data of the current dataset.

        • Click the icon and create a dashboard. Import the data of the table to generate a chart for visualized display.

        • Click the icon and create a workbook. Import the data of the table to generate a workbook for visualized display.

        • Click the icon and perform other operations, such as data masking, cache configuration, and cache clearing.

        For more information, see Dashboard overview.

        On the dashboard editing page, you can create a stacked column chart and configure a chart style to display data.

   • Execute an SQL statement for ad hoc queries.

     1. On the Data Sources page, click Ad Hoc Query in the upper-right corner to go to the Ad Hoc Query page.

     2. Enter an SQL statement to query data based on your business requirements and click Run.

     3. After you obtain the data that you want to query, click Create Dataset.

     4. In the Save Custom SQL dialog box, configure the Name, Save To, and SQL parameters.

     5. Click OK.

     After a dataset is created, you can analyze the data in a visualized manner on the Datasets page. For more information, see Use ad hoc queries for data modeling.

Configure a time filter

When you create a table in Hologres, you can configure 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.

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

   1. Log on to the Quick BI console. On the Data Sources page, click Create Dataset with SQL in the upper-right corner. The Create Code Snippet dialog box is displayed.

   2. 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')

   3. Click Parameter. In the Parameter panel, set a placeholder in the SQL statement.

   4. In the Variable Type column, select YYYY-MM-DD HH-MI-SS from the drop-down list.

   5. Click determine. Then, click Confirm edit.

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

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

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

   1. Log on to the Hologres console. In the left-side navigation pane, click Instances.

   2. Go to the details page of your Hologres instance. In the left-side navigation pane, click Database Management.

   3. On the Database Authorization page, click SQL Editor in the top navigation bar.

   4. Go to the SQL Editor tab. Click the New SQL Query icon in the upper-left corner.

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

   6. On the Results tab, view the execution plan.

      ->  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)

      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.