Using Amazon S3 with Qlik Sense

    Summary

    Amazon S3 - the cloud offering from Amazon allows customers of Amazon to store objects and files in a very simple and affordable way on the internet and having them accesible via standard internet protocols. S3 is an abbreviation of Simple Storage Services. To use S3 from Qlik Sense is very straightforward from any of the access methods that is available in Qlik Sense when it comes to files that are publicly available. Full authentication can also be provided with the “Qlik Web Connector for Amazon S3” used as a proxy. This connector will be able to provide full access for all of the Qlik access methods. There will be additonal blog posts on the secured way of accessing Amazon S3 with Qlik Sense "Using Amazon S3 with Qlik Sense - Approach #2 - using WebFile and Qlik Web Connector".

     

    Approach #1 – using WebFile

     

    Make a file or object on Amazon S3 “public” by clicking [Make public] with the Amazon S3 Console as shown below. If you have Qlik Sense installed and running on Amazon EC2 you can set up limited access to the buckets or network


    2017-03-10 13_34_16-S3 Management Console.png

    Then simply go to Data Manager and and do the following steps:

     

    2017-03-10 13_40_27-Qlik Sense Desktop.png


    For the URL for the connection paste in the URL that is shown in the Amazon S3 Console. Give the connection a name and click next.


    2017-03-10 13_40_09-Qlik Sense Desktop.png


    At this point you have to select fixed record and select Unicode (UTF-8) as Character set. We will have to manually set it to be of type JSON later in the load script editor.

     


    2017-03-10 13_42_56-Qlik Sense Desktop.png


    Press "Load data and finish"


    Go to the load script editor. First unlock the “Auto-generated section” by clicking on the LOCK icon. Go into this section and change the [@1:n] to * and then change fix to json – as is shown on the following two screenshots:


    2017-03-10 13_44_24-Qlik Sense Desktop.png



    2017-03-10 13_46_03-Qlik Sense Desktop.png



    Press the [Load Data] button:


    2017-03-10 13_49_28-Qlik Sense Desktop.png




    And this will happen:


    2017-03-10 13_50_20-Qlik Sense Desktop.png



    That’s all there is too it. The normal file types apply for Qlik Sense. So basically you can read CSV, TXT, QVD, QVX, XML, KML ,XLS and XLSX or even binary loading QVW or QVF files. You could also host common shared include files for load scripts in an S3 bucket. In addition you can read JSON files that are not nested also with the Qlik Sense 3.2 SR1 version and later. The JSON files can’t be used in preview and has to be hand coded in the load script as of version 3.2 SR1 but later release will allow you to use the preview dialog with JSON also.


    Be aware that this approach by reading the JSON data with WebFile is limited to the first level of JSON data. That is any JSON field that is a complex or nested type will not be interpreted. There is however additional approaches to be able to handle nested JSON. Among those are using the Qlik REST Connector instead of WebFile which is very similar to this recipe. The other approach is to un-nest the data yourself if it is only a matter of two levels or maybe three that is not too much work.

     

     

    A last caution on security


    You have many options of tuning and setting the security in your Amazon S3 environment. It would be sensible to host your Qlik Sense environment on an Amazon EC2 server to take full advantage of the free transfer of data within the Amazon cloud. That way you can more tightly control the security and access also. Setting files to Public comes with the danger of exposing the data to anyone. So make sure you do the right decisions on what can be made public and what can't. Even when a file is set to public it is not necessarily easy to find - but it is a dangerous kind of security to presume that it wouldn't be found. Obfuscating the filenames would make them extremely hard to find though. For some use-cases like testing and for common availability to the public it would be acceptable and necessary with Public Access.