Hologres:Qlik

Limits

Qlik does not support visualizing Hologres foreign tables. However, you can use SQL statements to query foreign tables and perform visual analytics in the Data load editor.

Connect Qlik Sense Desktop to Hologres

1. Install Qlik Sense Desktop

   Install Qlik Sense Desktop. For more information, see the official Qlik document.

2. Connect to Hologres

   1. Open Qlik Sense Desktop. In the upper-right corner of the page, click Create new app.

   2. In the Create new app dialog box, enter a name for the application and click Create.

   3. In the dialog box that appears, click Open app. On the application page, click Add data from files and other sources to add data.

   4. In the dialog box that appears, select PostgreSQL to create a new connection and configure the parameters.

      Parameter	Description
      Host name	The public endpoint of the Hologres instance. Go to the instance details page in the Hologres console and obtain the endpoint from the Network Information section.
      Port	The port number of the Hologres instance. Go to the instance details page in the Hologres console and obtain the port number from the Network Information section.
      Database	The name of the database that you created in Hologres.
      User Name	The AccessKey ID of your Alibaba Cloud account. For more information about how to obtain an AccessKey ID, see Create an AccessKey pair.
      Password	The AccessKey secret of your Alibaba Cloud account. For more information about how to obtain an AccessKey secret, see Create an AccessKey pair.

   5. Click Test connection. If the message Connection succeeded appears, the connection is successful. Click Create in the lower-right corner of the dialog box to save the new connection information.

   6. Configure the PostgreSQL data connection.

      • Select a target Owner, which is a schema in Hologres. In this example, select public.

      • In the Tables area, select the table that you want to analyze.

   7. Click Add data at the bottom. After the data is added, Qlik Sense imports the data from Hologres. You can then view the data from Hologres in Qlik.

      Note

      In this mode, Qlik Sense loads all data into its engine instead of sending real-time queries to the database based on page operations.

3. Configure Direct Query mode

   In production scenarios, a database can contain petabytes (PB) of data. We recommend that you use Direct Query mode in Qlik Sense instead of importing data. For a detailed explanation of Direct Query mode, see the official Qlik document.

   1. Open the application that you created. In the upper part of the page, choose Data manager > Data load editor.

   2. The data source connection information is displayed on the right side of the page. Click the icon in the lower-right corner to insert the connection information into the editor.

   3. In the script editor, enter Direct Query at the beginning of the query script to enable Direct Query mode. The following figure and script show an example:

      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 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. In the upper-right corner of the window, click Load data. Qlik Sense then sends an instant query.

   5. In the upper part of the page, choose Sheet > Edit sheet to start creating a visualization.

   6. On the edit sheet page, click Charts on the left to select a chart type and create a visualization.

      Note

      • If you drag fields directly to the canvas, Qlik Sense cannot generate a Direct Query query. Therefore, click Charts to create a visualization.

      • Due to the processing logic of Qlik Sense, add measures before you add dimensions to improve response speed.

      

   For more information about data operations and analysis in Qlik Sense, see the official Qlik document.

Connect to Hologres using a custom ODBC connection

To query foreign tables in the table wizard of Qlik Sense, you can connect to Hologres using a custom Open Database Connectivity (ODBC) connection.

1. Prerequisites

   • The PostgreSQL ODBC driver must be installed on both the Qlik Sense server and client. You can download the ODBC driver from the official PostgreSQL website.

   • Because Hologres is compatible with the PostgreSQL 11 ecosystem, make sure to install psqlODBC 11.01.0000 or a later version.

2. Configure DNS

   • Choose Control Panel > Administrative Tools, and then find and open the ODBC Data Sources (64-bit) tool.

   • Switch to the System DSN tab and click the Add button.

   • In the dialog box that appears, select PostgreSQL Unicode(x64) from the list of drivers and click the Complete button.

     Note

     A User DSN can be used only by specific users, whereas a System DSN is available to all users who log on to the system. If users need to access Hologres through ODBC on a web BI server, they should use a System DSN.

   • In the dialog box that appears, enter the Hologres server information. The following table describes the parameters.

     Parameter	Description
     User Name	The AccessKey ID of your Alibaba Cloud account. Go to the AccessKey Management page to obtain the AccessKey ID.
     Password	The AccessKey secret of your Alibaba Cloud account. Go to the AccessKey Management page to obtain the AccessKey secret.
     Server	The public endpoint of the Hologres instance. Go to the instance details page in the Hologres console and obtain the public endpoint from the Network Information section.
     Port	The port number of the Hologres instance. Go to the instance details page in the Hologres console and obtain the port number from the Network Information section.
     Database	The name of the database in Hologres.

   • Click the Test button. If the page shown in the following figure appears, the connection is successful.

   • Click Save to save the DSN.

3. Connect to Hologres

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

   • Open the Qlik Sense Desktop application. Click Add data from files and other sources.

   • In the window that appears, click ODBC to create a new connection.

   • From the list, select the DSN that you created and enter a name.

   • Click Create to save the new connection information. You can then select an Owner, which is a schema in Hologres. For example, select public. This connection method lets you read the schema of foreign tables.

   • Select the table that you want to analyze and click Add data. Qlik Sense imports the data from Hologres. After the import is complete, you can start your analysis.

   Note

   In this mode, Qlik Sense loads all data into its engine instead of sending real-time queries to the database based on page operations.