Hologres:Qlik

Create an app and add a connection

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

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

3. Click Open app. On the app page, click Add data from files and other sources.

4. Select PostgreSQL to create a new connection and configure the following parameters.

   Parameter	Description	Required
   Host name	The public endpoint of the Hologres instance. Get it from the Network Information section of the Hologres console.	Yes
   Port	The port number of the Hologres instance. Get it from the Network Information section of the Hologres console.	Yes
   Database	The name of the database in Hologres.	Yes
   User Name	Your Alibaba Cloud AccessKey ID. See Create an AccessKey pair.	Yes
   Password	Your Alibaba Cloud AccessKey secret. See Create an AccessKey pair.	Yes

5. Click Test connection. If Connection succeeded appears, click Create to save the connection.

6. Select a target Owner (a schema in Hologres — for example, public). In the Tables area, select the table to analyze.

7. Click Add data. Qlik Sense imports the data from Hologres and makes it available for analysis.

In this mode, Qlik Sense loads all data into its in-memory engine rather than sending real-time queries to Hologres. For petabyte-scale production data, use Direct Query mode instead.

Set up Direct Query mode

Standard import loads all data into memory at once. Direct Query sends queries to Hologres on demand, making it suitable for large production datasets.

To enable Direct Query mode:

1. Open your app. In the upper navigation, choose Data manager > Data load editor.

2. On the right side, click the icon to insert the connection string into the editor.

3. Add DIRECT QUERY at the beginning of your query script. The following example queries two joined tables:

   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='en-US';
   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. Click Load data in the upper-right corner. Qlik Sense sends a real-time query to Hologres.

5. Choose Sheet > Edit sheet to start building a visualization.

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

Do not drag fields directly onto the canvas — this prevents Qlik Sense from generating a Direct Query. Always use Charts to create visualizations. Add measures before dimensions to improve query response speed.

For more information about data operations in Qlik Sense, see the official Qlik documentation.

Configure a DSN

A Data Source Name (DSN) stores the connection details that Qlik Sense uses to reach Hologres via ODBC.

Use a System DSN (not a User DSN) when Qlik Sense runs on a web BI server. A System DSN is accessible to all users who log on to the system, whereas a User DSN is limited to the user who created it.

1. Open Control Panel > Administrative Tools, then open ODBC Data Sources (64-bit).

2. Switch to the System DSN tab and click Add.

3. Select PostgreSQL Unicode(x64) from the driver list and click Complete.

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

4. Enter the Hologres connection details:

   Parameter	Description	Required
   User Name	Your Alibaba Cloud AccessKey ID. Get it from the AccessKey Management page.	Yes
   Password	Your Alibaba Cloud AccessKey secret. Get it from the AccessKey Management page.	Yes
   Server	The public endpoint of the Hologres instance. Get it from the Network Information section of the Hologres console.	Yes
   Port	The port number of the Hologres instance. Get it from the Network Information section of the Hologres console.	Yes
   Database	The name of the database in Hologres.	Yes

5. Click Test. If the connection succeeds, a success page appears.

6. Click Save to save the DSN.

Connect Qlik Sense to Hologres

1. Open Qlik Sense Desktop and click Add data from files and other sources.

2. Click ODBC to create a new connection.

3. Select the DSN you created and enter a connection name.

4. Click Create to save the connection. Select an Owner (a schema in Hologres — for example, public). This connection method lets Qlik Sense read the schema of foreign tables.

5. Select the table to analyze and click Add data. Qlik Sense imports the data from Hologres.

In this mode, Qlik Sense loads all data into its in-memory engine rather than sending real-time queries to Hologres.