Qlik

Limitations

Qlik does not support visualizing Hologres foreign tables. However, you can use the Data load editor to query foreign tables with SQL statements for visual analysis.

Connect Qlik Sense Desktop to Hologres

1. Install Qlik Sense Desktop

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

2. Connect to Hologres

   1. Open Qlik Sense Desktop and click Create new app in the upper-right corner.

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

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

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

      Parameter	Description
      Host name	The public endpoint of the Hologres instance. To obtain the endpoint, go to the instance details page in the Hologres console and find the endpoint in the Network Information section.
      Port	The port of the Hologres instance. To obtain the port, go to the instance details page in the Hologres console and find the port in the Network Information section.
      Database	The name of the database in Hologres.
      User name	Your Alibaba Cloud AccessKey ID. For more information, see Create an AccessKey pair.
      Password	Your Alibaba Cloud AccessKey Secret. For more information, see Create an AccessKey pair.

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

   6. Configure the PostgreSQL data connection.

      • Select a target Owner (which corresponds to a schema in Hologres). For example, select public.

      • In the Tables area, select the table to analyze.

   7. Click Add data. Qlik Sense imports the data from Hologres. You can then view the data in Qlik Sense.

      Note

      In this mode, Qlik Sense loads all data into its in-memory engine rather than sending real-time queries to the database based on page actions.

3. Configure Direct Query mode

   In production environments where databases can contain petabytes of data, we recommend using Direct Query mode in Qlik Sense instead of importing data. For more information about Direct Query mode, see the official Qlik documentation.

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

   2. The data source connection information appears on the right side of the page. Click the icon in the lower-right corner to insert the connection string into the editor. The tooltip for this icon is Insert connection string.

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

      SET ThousandSep=',';
      SET DecimalSep='.';
      SET MoneyThousandSep=',';
      SET MoneyDecimalSep='.';
      SET MoneyFormat='$#,##0.00;-$#,##0.00';
      SET TimeFormat='h:mm:ss TT';
      SET DateFormat='M/D/YYYY';
      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
      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_hgpcxxx-xxxxxxxxxxxxx-xx-xxxxxxhou.hologres.aliyuncs.com';
      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;

      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='1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月';
      SET LongMonthNames='一月;二月;三月;四月;五月;六月;七月;八月;九月;十月;十一月;十二月';
      SET DayNames='周一;周二;周三;周四;周五;周六;周日';
      SET LongDayNames='星期一;星期二;星期三;星期四;星期五;星期六;星期日';
      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';
      --输入 Direct Query，启用 Direct Query 模式
      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 then sends a real-time query.

   5. In the top navigation bar, choose Sheet > Edit sheet to start creating visualizations.

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

      Note

      • If you drag fields directly to the canvas, Qlik Sense cannot generate a query in Direct Query mode. We recommend that you click Charts to create visualizations.

      • To improve response time, add measures before dimensions.

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

Connect to Hologres using a custom ODBC connection

If you need to query foreign tables in the Qlik Sense table wizard, you can connect to Hologres using a custom ODBC connection.

1. Prerequisites

   • Install the PostgreSQL ODBC driver 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, install Vpsqlodbc_11_01_0000 or a later version.

2. Configure a DSN

   • Go to Control Panel > Tools, and open the ODBC Data Sources (64-bit) tool.

   • Switch to the System DSN tab and click Add.

   • In the driver list, select PostgreSQL Unicode(x64) and click Complete.

     Note

     A User DSN is user-specific, whereas a System DSN is available to all users on the system. If you need to access Hologres through ODBC on a web BI server, you must use a System DSN.

   • In the dialog, enter the Hologres server information as described in the following table. In the psqlODBC configuration dialog, set Data Source to Hologres and set SSL Mode to disable.

     Parameter	Description
     User name	Your Alibaba Cloud AccessKey ID. Go to the AccessKey Management page to obtain your AccessKey ID.
     Password	Your Alibaba Cloud AccessKey Secret. Go to the AccessKey Management page to obtain your AccessKey Secret.
     Server	You can find the public endpoint for the Hologres instance on the instance details page in the Hologres console under the Network information section.
     Port	You can find the port for the Hologres instance on the instance details page in the Hologres console, under the Network Information section.
     Database	The name of the database in Hologres.

   • After entering the information, click the Test button. If the Connection successful message appears, the configuration is correct.

   • Click Save to save the DSN. Set Data Source to Hologres and SSL Mode to disable.

3. Connect to Hologres

   After configuring the local DSN and installing Qlik Sense, follow these steps to connect Qlik Sense to Hologres.

   • 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. On the Connect to a new data source page, select the ODBC connector and click Next.

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

   • Click Create to save the connection. You can now select an Owner (a schema in Hologres), such as public. This connection method allows Qlik Sense to read the schema of foreign tables. After a successful connection, select the target database, such as tpch_100, from the Database drop-down list. Then, select the corresponding schema, such as public, from the Owner drop-down list. The available tables are listed in the Tables area.

   • From the Tables list, select the tables to analyze, such as orders, and preview their fields and data in the right-side panel. After confirming the information, click Next and then Add data. Qlik Sense imports the data from Hologres for 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 actions.