Posted by In this post, we will focus on presenting the currently available data transfer options from internal resources to the Qlik Cloud.
The cloud simply refers to servers administered by Qlik for our convenience, but they are not within our network. As a result, the question often arises of how to transfer data to Qlik Cloud, which is typically isolated within the internal network.
When data is stored in the cloud (e.g. Snowflake, BigQuery), we can connect to it directly with Qlik Cloud, enabling cloud-to-cloud communication – so this will not be covered.
This article will focus on connecting internal resources to Qlik Cloud.
We will discuss four options:
- Opening the data server for connections from the cloud.
- Qlik Data Transfer.
- Qlik Data Gateway: Direct Access.
- Qlik Data Integration.
Summary
First, a summary table of all the solutions.
| 1 | 2 | 3 | 4 | |
| Solution | Opening the source server | Qlik Data Transfer | Direct Access | QDI |
| Short description | Open ports in local firewall to three Qlik Cloud IPs | Locally installed, pushes files to the cloud | Locally installed, allows direct access from the Cloud | Locally installed, a separate set of ETL class tools |
| Installation program? | – | Qlik Data Transfer | Qlik Data Gateway: Direct Access | Qlik Data Gateway: Data Movement |
| Extra server needed? | No | Recommended | Recommended | Linux server needed Temporary storage space (ex. AWS) needed |
| Flat files transfer? | No | Yes | No | No |
| File/data size limit | No | Yes | No | No |
| Security | Dependent on the source database | API and HTTPS | Mutually encrypted transmission | Mutually encrypted transmission |
| Ease of implementation | Easy | Easy | Moderate | Hard, specialized knowledge needed |
| Additional functionalities | – | Supports ODBC Can send flat files Can trigger app reload in Cloud | – | Complete ETL tool including transforming data and incremental load |
| Monitoring of data completeness | Your own app in Qlik Cloud | Difficult, as data is pushed from on-prem to the Cloud | Your own app in Qlik Cloud | Included in QDI |
| Drivers/databases (the list constantly changes) | Only those available in Qlik Cloud | All you can install on-prem, including ODBC | Only those available in Qlik Cloud | Only those available in QDI |
Ad 1. Opening the data server for connections from the cloud
We connect to the database directly, using drivers available as standard in Qlik. It is required to open ports in the local server’s firewall to communicate with external servers. The good news is that it is enough to open communication by narrowing it down to one port and three IP addresses – servers from which connections can be initiated by Qlik Cloud. (Three IP addresses, different for each Qlik Cloud Region). For a bit extra security, you can modify the standard port.
Advantages
- simplicity and speed of configuration (“just” open the ports),
- convenience of data selection – using data selection wizards in the Qlik Sense application “the old way” as in Qlik Sense Client Managed or in QlikView,
- once configured, it can be used in Qlik Hub, eliminating the need to jump between servers.
Disadvantages
- security strictly dependent on the source database,
- no support for ODBC drivers (we cannot “upload” our own drivers in Qlik Cloud).
Ad 2. Qlik Data Transfer
It was the first program created by Qlik to transfer data to the Qlik cloud, right after the launch of the Cloud service. Installed locally on the company server, after starting it works as a local service. Data “pushes” to the Qlik cloud using references via API to your Qlik Cloud tenant, over HTTPS.
In the first step, the data is generated locally (in the form of QVD), then sent to the cloud. This applies to any of the three configurations:
- Monitoring the folder with files (including “flat” files!) and uploading new and updated files.
- Creating connections to individual tables in local databases. Each of these connections can, according to a schedule, download data (temporarily to QVD) and then send it to the Qlik Cloud. We need to setup a separate connection for every source table transferred.
- Launching a Qlik Sense application which loads the data into itself. Then the data from the data model is sent to the Cloud in separate tables.
Qlik Data Transfer includes the Qlik data processing engine. The application script gets executed, it creates an internal data model, QDT extracts all the tables and sends them to the Cloud. This is an interesting solution as you can program data processing already in this stage, before sending it to the Cloud.
Each of the above transfer methods may additionally trigger reloads in the cloud of the indicated Qlik Sense application (for example, an application that loads the transferred data to itself).
Advantages
- ease of installation and configuration,
- can be installed on the same server where, for example, Qlik Sense Client Managed is running,
- usually safer than opening a database server,
- supports ODBC drivers,
- allows you to upload flat files (CSV, Excel) to the cloud.
Disadvantages
- can be unstable, especially with large tables,
- has a limit on the size (in GB) of the table being sent,
- Logging into the in-house server (RDP) is required every time a new data source is to be programmed..
See how to configure Qlik Data Transfer:
- Simple Installation Guide:https://share.vidyard.com/watch/XYrKHrRm3NNLneLLVrhb89
- An exemplary data transfer configuration instructions:https://share.vidyard.com/watch/X9QJYTiajGxfSVjt2pP8yq
Ad 3. Qlik Data Gateway: Direct Access
It works completely securely behind the corporate firewall. It uses data encryption, transmits the data through a mutually authenticated connection – a “tunnel”. It allows you to use local sources as conveniently as if both servers (cloud and local source) were in the same network. Qlik Cloud sends a request to Qlik Data Gateway, this service sends a local request to the appropriate database, then the response (downloaded data) is sent directly to Qlik Cloud cloud drives via WS-Security.
Advantages
- convenient data selection with the native Qlik interface straight to the application,
- direct transfer of data to the cloud, without storing them in intermediate stages,
- reliable and efficient,
- after installation – used in the Qlik Sense Cloud Hub.
Disadvantages
- slightly more complex installation on a local server required,
- recommended installation on a server other than the data source and other than Qlik Sense Client Managed – due to server load during operation. May require additional investment in infrastructure,
- at the moment (February 2023) there is no support for ODBC drivers, but as you read this text, the announcement of adding this option may already be realized.
Ad 4. QDI – Qlik Data Integration
Qlik Data Integration is a set of tools of a different class than the examples mentioned above. It is a cloud implementation of the Attunity Compose and Replicate products acquired by Qlik. In a separate set of tools (though still in the same Qlik Sense Cloud Hub), we design streaming data from local sources to cloud drives. A dedicated service is required on the local server. This is Qlik Data Gateway – but in the “Data Movement” version (not “Direct Access”).
The solution requires a bit more investment in implementation and infrastructure (needs a local Linux server and transfer disk resources, e.g. AWS or Google Cloud Storage), which is why it is recommended for larger data sets. It is characterized by stability, the ability to professionally design data transformation and has its own support for incremental data loading (CDC – Change Data Capture). It is an ETL class tool.
What’s most interesting, the tool can be used to migrate data between any environments, none of which has to be Qlik! For example: local data to Snowflake, SAP data to BigQuery, etc.
Advantages
- a stable solution that supports large data volumes,
- independently supports incremental data loading (so-called CDC – Change Data Capture),
- after installation, support completely in the Qlik Sense Cloud Hub,
- allows you to design data processing at the transfer stage – an ETL class tool,
- has a wide range of applications – it can transfer data between systems, none of which is Qlik!
Disadvantages
- requires additional investment in a local Linux server and a transfer disk resource (e.g. AWS),
- requires expertise to handle transmission design, Linux server startup, and AWS / Google Cloud Storage resource deployment.
What about flat, configuration files (Excel, CSV)?
In solutions 1, 3 (this will change soon) and 4, to upload flat files, we need to use an additional solution.
It could be:
- file storage in OneDrive / SharePoint. Files are easily accessible to users – they can “connect” a folder with files to their computers and edit them in the storage location,
- manually uploading files to Qlik Cloud. Good for configuration files that only change once in a while. Not for source data files that need to be refreshed daily.