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.

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 in the upper-right corner.
   4. Select a type of connection that you want to configure to connect Quick BI to Hologres and set the parameters as required.
      1. Choose Cloud Data Sources > Hologres.
      2. 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.
   5. Click OK.
3. Analyze data 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.

   To use the two methods to analyze data, perform the following steps:

   • 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, set 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 corresponding 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 de-identification, cache configuration, and cache clearing.
        For more information, see Dashboard overview.
   • 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, set 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 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.

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. 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.
   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 pane, click Databases.
   3. On the Database Authorization page, click SQL Editor in the top navigation bar.
   4. On the SQL Editor tab, click the Ad-hoc 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)
        ->  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.