Cloud File Services and Qlik Sense Business

Sense Business is Qlik’s flagship Cloud offering, and it is now possible to build and securely share full featured apps in the Cloud. Not having to provision a server and manage authentication can save a lot of time for a small business wanting to use Sense. If you are loading flat files though (such as CSVs or Excel files) it is not so obvious how to bring them in. This post explains all.

Background

In case you are not aware of what Sense Business is, it’s a version of Qlik Sense which is hosted by Qlik in their environment. You can sign up for it online and get started straight away. Details of how to sign up for a trial of Sense Business were covered in a previous blog post. It has the full functionality of Sense, including the recently added ability to import extensions for use in the Cloud.

Subscriptions are purchased on a per-user basis. The service is slightly limited, most notably in that only 50 reloads can be carried out a day and that only five spaces (the Cloud equivalent of Streams, which define permission groups) are allowed. Full details of the limitations can be found here https://www.qlik.com/us/pricing, if you need to go beyond this Enterprise is for you.

Access to a Sense site is done via Qlik’s single-sign-on, meaning that users are responsible for setting up their own accounts, once the site admin sends an invite to them via email.

Having everything in the Cloud makes things very simple, but the problem is how to get your data in there to be analysed. This is where the large number of connectors provided with Sense Business comes in. If your data is coming from a cloud source, such as Google Analytics or Jira, this makes perfect sense as the connection is simply from one Cloud to another. If you have an on premise (or Cloud) database, it’s simply a case of opening the appropriate firewall ports to make the connection.

Flat files which are stored on premise can present a bit more of a challenge though.

Connecting and Loading from Cloud Storage

In order to load flat files into Sense Business the files either need to be dragged into Sense, through the pre-configured DataFiles library, or loaded from a Cloud storage provider.

Qlik provide connectors for Amazon S3, Microsoft Azure, Dropbox and Google Drive. In each case simply click on the Create New Connection button, select the provider and click the Authenticate button. This will take you to a sign in screen for the provider, logging in and following the steps will give you an authentication key that can be pasted back into Sense Business. Give the connection a name, click to create it then it can be used just like a Folder connection in Sense Desktop or Enterprise.

The advantage of a provider like Dropbox or Google Drive is that there are desktop apps which can automatically upload modified files to online storage. This means that an on-premise process (manual or automatic) can write files to a location and they are automatically made available in the Cloud, and therefore to Sense.

Something that is frequently required when working with flat files is that you want to load from many of them at a time. For example, you may have monthly extract files from a system that you wish to analyse together. In a previous blog post I have looked how to enumerate around multiple files in a Qlik load script. The other approach you may have used is to load from multiple files using a wildcard (the * character).

Unfortunately, neither of these approaches work with Cloud data providers. There is a way of achieving the same thing, with just a little more code.

Looping Through Files in Qlik Sense Business

For each of the Cloud data providers you will find that there are two connectors, one under the File Locations section, the other under Data Sources. The first is where you load data from, this works much like a Folder connector. The other is a Metadata connector, this allows you to load lists of files from the storage provider. These lists can then be enumerated around to load from a number of files. The Metadata connectors also give other bits of interesting information about the files on that provider – I will leave that for you to explore.

Connecting to the Metadata connector for each provider is done in the same way as the connector itself, you need to repeat the authentication and copying of a key.

When loading files from Dropbox and Google Drive, be aware that these providers allow you to get to superseded and deleted versions of files. It is important that you skip these files when getting a list of files from the metadata.

The best way to find the list of files to load is to use the wizard. Once you have done this though you can simplify the code, to ignore fields that you don’t need.

To load from all files matching a particular mask you can then use the code below, specific for the provider you are using. Note that you will either need to name your connectors and folders to match mine or change them in the code.

Dropbox

LIB CONNECT TO 'Dropbox_Metadata';

tmpFiles:
LOAD
    path_lower
    WHERE right(path_lower, 4) = 'xlsx'
    ;
SELECT
    path_lower
FROM List
WITH PROPERTIES (
dropboxFolder='',
recursive='false',
includeDeleted='false'
);

for iFile = 0 to NoOfRows('tmpFiles') - 1
    let vFile = peek('path_lower', iFile, 'tmpFiles');

    LOAD
        *
    FROM [lib://Dropbox$(vFile)]
    (ooxml, embedded labels);
next

DROP TABLE tmpFiles;

Google Drive

LIB CONNECT TO 'Google_Drive_&_Spreadsheets';

tmpFiles:
LOAD
     title
;
SELECT
     title
FROM ListFiles
WITH PROPERTIES (
query='title contains "csv" and trashed = false'
);

for iFile = 0 to NoOfRows('tmpFiles') -1
    let vFile = peek('title', iFile, 'tmpFiles');
    LOAD
         *,
         filename() as FileName
    FROM [lib://Google_Drive/root/$(vFile)] (txt);
Next

DROP TABLE tmpFiles;

Amazon S3

LIB CONNECT TO 'Amazon_S3_Metadata';

tmpFiles:
LOAD
    Key
    WHERE right(Key, 3) = 'qvd'
    ;
SELECT
    Key
FROM List
WITH PROPERTIES (
dir='SubFolder',
maxResults=''
);

for i = 0 to NoOfRows('tmpFiles') -1
    let vFile = peek('Key', i, 'tmpFiles');
    
    LOAD
        *,
        filename() as FileName
    FROM [lib://Amazon_S3/$(vFile)] (qvd);
next

DROP TABLE tmpFiles;

Azure Storage also has the same concept of File Location connector and Metadata connector. You should be able to do the same thing with Azure as the other providers, but this is not something I have tried yet. Let me know if you get this working and I will include the code above.

Hopefully, using this method, you will be able to set up and schedule a reload in Qlik Sense Business from flat files which you have on a Cloud data provider.

If you have any problems getting it working just drop a comment below and I will try and give some pointers.