Qlik is a classic business intelligence (BI) platform. It provides various BI tools such as Qlik Sense Desktop. Qlik can help you develop and deliver applications and dashboards for interactive guidance and analysis. This topic describes how to connect Qlik Sense Desktop to Hologres and analyze data in a visualized manner.
Limits
Qlik Sense Desktop has the following limits.
You cannot synchronize schemas of foreign tables in PostgreSQL to Qlik Sense Desktop. Therefore, you cannot query metadata of foreign tables on the following page.
You can still query data by executing SQL statements, as shown in the following figure.
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
Install Qlik Sense Desktop.
For more information about how to install Qlik Sense Desktop, see official documentation of Qlik.
Connect Qlik Sense Desktop to Hologres.
Log on to Qlik Sense Desktop. On the Qlik Sense Desktop hub page, click Create new app in the upper-right corner.
In the Create new app dialog box, enter a name for the application that you want to create and click Create.
In the message that appears, click Open app. On the page that appears, click Add data from files and other sources.
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.
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.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.
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.
NoteIn 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.
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.
Go to the details page of the application created in Step 2. Choose .
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.
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;
Click Load data in the upper-right corner. Then, Qlik Sense Desktop instantly sends a request to the Hologres database to query data.
Click the .
On the page that appears, click Charts in the left-side pane and select a chart type for data visualization.
NoteIf 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.
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.
Configure DNS settings
Choose
, 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.
NoteUser 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.
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.
NoteIn 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.