Hologres:Qlik

Limits

Qlik does not support visual display of Hologres foreign tables. However, you can query foreign tables by executing SQL statements in the data loading editor. Then, you can analyze related data in a visualized manner.

Connect Qlik Sense Desktop to Hologres

1. Install Qlik Sense Desktop.

   For more information about how to install Qlik Sense Desktop, see official documentation of Qlik.

2. Connect Qlik Sense Desktop to Hologres.

   1. Log on to Qlik Sense Desktop. On the Qlik Sense Desktop hub page, click Create new app in the upper-right corner.

   2. In the Create new app dialog box, enter a name for the application that you want to create and click Create.

   3. In the message that appears, click Open app. On the page that appears, click Add data from files and other sources.

   4. In the dialog box that appears, select PostgreSQL and configure the parameters that are described in the following table.

      Parameter	Description
      Host name	The public endpoint of the Hologres instance to which you want to connect. You can obtain the public endpoint of the Hologres instance from the Network Information section of the instance details page in the Hologres console.
      Port	The port number of the Hologres instance to which you want to connect. You can obtain the port number of the Hologres instance from the Network Information section of the instance details page in the Hologres console.
      Database	The name of the Hologres database to which you want to connect.
      User Name	The AccessKey ID of the Alibaba Cloud account used to connect to the Hologres database. For more information about how to obtain the AccessKey ID, see Create an Alibaba Cloud account.
      Password	The AccessKey secret of the Alibaba Cloud account used to connect to the Hologres database. For more information about how to obtain the AccessKey secret, see Create an Alibaba Cloud account.

   5. Click Test connection. If the Connection succeeded message appears, the connection is established. Click Create in the lower-right corner to save the connection information.

   6. Specify the data that you want to import by using the PostgreSQL connection.

      • Owner: Select the schema of the Hologres table whose data you want to import. In this example, public is selected.

      • Tables: Select the table whose data you want to import.

   7. Click Add Data. After you specify the data, Qlik Sense Desktop imports the data from Hologres. Then, you can view the imported Hologres data in Qlik Sense Desktop.

      Note

      In this mode, Qlik Sense Desktop loads all data in the Hologres table to the Qlik Sense engine. This way, Qlik Sense Desktop does not need to send real-time requests to the Hologres database each time a query is performed.

3. Configure the Direct Query mode.

   In daily production scenarios, a database stores up to petabytes of data. We recommend that you use the Direct Query mode in Qlik Sense Desktop. This way, you do not need to import data to Qlik Sense Desktop. For more information about the Direct Query mode, see official documentation of Qlik.

   1. Go to the details page of the application created in Step 2. Choose Data manager > Data load editor.

   2. Click the icon in the Data connections pane to insert the connection information that is displayed in the upper-right corner into the data loading editor.

   3. In the data loading editor, enter Direct Query before the query statements to enable the Direct Query mode. The following figure and script are for your reference.

      SET ThousandSep=',';
      SET DecimalSep='.';
      SET MoneyThousandSep=',';
      SET MoneyDecimalSep='.';
      SET MoneyFormat='¥#,##0.00;-¥#,##0.00';
      SET TimeFormat='TTh:mm:ss';
      SET DateFormat='YYYY/M/D';
      SET TimestampFormat='YYYY/M/D TTh:mm:ss[.fff]';
      SET FirstWeekDay=6;
      SET BrokenWeeks=1;
      SET ReferenceDay=0;
      SET FirstMonthOfYear=1;
      SET CollationLocale='zh-CN';
      SET CreateSearchIndexOnReload=1;
      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
      SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
      
      LIB CONNECT TO 'PostgreSQL_hgpostcn-cn-st21y8nlm007-cn-hangzhou.hologres.aliyuncs.com';
      
      -- Enter Direct Query to enable the Direct Query mode.
      DIRECT QUERY
      
      dimension
      l_shipmode,
      l_shipdate,
      o_orderstatus
      
      measure
      l_tax,
      l_quantity
      
      FROM public.lineitem INNER JOIN public.orders
      ON public.lineitem.l_orderkey = public.orders.o_orderkey;

   4. Click Load data in the upper-right corner. Then, Qlik Sense Desktop instantly sends a request to the Hologres database to query data.

   5. Click the Sheet > Edit sheet.

   6. On the page that appears, click Charts in the left-side pane and select a chart type for data visualization.

      Note

      • If you directly drag a field to the canvas, Qlik Sense Desktop cannot obtain related data in Direct Query mode. We recommend that you click Charts and create a chart for data visualization.

      • To improve the response speed, we recommend that you add measures and then dimensions. This method works better with the processing logic of Qlik Sense Desktop.

      

   For more information about how to use Qlik Sense Desktop to manage and analyze data, see official documentation of Qlik.

Connect Qlik Sense Desktop to Hologres by creating a custom ODBC connection

If you want to use the Direct Query mode of Qlik Sense Desktop to query foreign tables, you must connect Qlik Sense Desktop to Hologres by creating a custom Open Database Connectivity (ODBC) connection.

1. Prerequisites

   • A PostgreSQL ODBC driver is installed on the Qlik Sense Desktop server and client. You can download the ODBC driver from the official website of PostgreSQL.

   • Hologres is compatible with PostgreSQL 11. Therefore, you must install an ODBC driver whose version is Vpsqlodbc_11_01_0000 or later.

2. Configure DNS settings

   • Choose Control Panel > Administrative Tools, and double-click ODBC Data Sources (64-bit).

   • Go to the System DSN tab and click Add.

   • In the dialog box that appears, select PostgreSQL Unicode(x64) and click Finish.

     Note

     User DSNs are available to specific users, whereas system DSNs are available to all users. If you want to connect a web BI server to Hologres by using ODBC, you must use a system DSN.

   • In the dialog box that appears, configure the parameters that are described in the following table.

     Parameter	Description
     User Name	The AccessKey ID of the Alibaba Cloud account used to access the Hologres instance. You can obtain the AccessKey ID from the Security Management page.
     Password	The AccessKey secret of the Alibaba Cloud account used to access the Hologres instance. You can obtain the AccessKey secret from the Security Management page.
     Server	The public endpoint of the Hologres instance to which you want to connect. You can obtain the public endpoint of the Hologres instance from the Network Information section of the instance details page in the Hologres console.
     Port	The port number of the Hologres instance to which you want to connect. You can obtain the port number of the Hologres instance from the Network Information section of the instance details page in the Hologres console.
     Database	The name of the Hologres database to which you want to connect.

   • Click Test. If the Connection successful message appears, the connection is established.

   • Click Save to save the DSN settings.

3. Connect Qlik Sense Desktop to Hologres

   After you configure the DSN and install Qlik Sense Desktop, perform the following steps to connect Qlik Sense Desktop to Hologres:

   • Log on to Qlik Sense Desktop. Click Add data from files and other sources.

   • In the dialog box that appears, select ODBC to establish a connection.

   • Select the created DSN and enter the name of the connection.

   • Click Create to save the connection information. Then, configure Owner. The Owner parameter specifies the schema of the Hologres foreign table. In this example, the schema public is selected. If the ODBC connection is used, Qlik Sense Desktop can read the schema of a Hologres foreign table.

   • Select the table whose data you want to analyze and click Add data. Then, the data is imported from Hologres to Qlik Sense Desktop. After the data is imported, you can analyze the data.

   Note

   In this mode, Qlik Sense Desktop loads all data in the Hologres table to the Qlik Sense engine. This way, Qlik Sense Desktop does not need to send real-time requests to the Hologres database each time a query is performed.