Qlik Community

Qlik Sense Documents

Documents about Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER

Using Amazon S3 with Qlik Sense

MVP
MVP

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.



Comments
Partner
Partner

Hey petter-s,

Very creative and interesting. Thanks for sharing the knowledge.

I have a similar requirement in QlikView - to call a WebService to capture the SMSESSION cookie (that has the Log In ID of the User).

May be we can do in a Load Script or vbscipt in Macro (I checked this which generates XML https://community.qlik.com/message/779723?et=watches.email.thread#779723).

If a similar way we can do this to capture SMSESSION.

Please let me know.

0 Likes
Contributor III
Contributor III

Hi petter-s‌,

I was looking forward to the second part:

Using Amazon S3 with Qlik Sense - Approach #2 - using WebFile and Qlik Web Connector.


Is already online?

If you need someone to test the solution, I could test immediately when available.


As an ideal solution, my idea is to save to export from Redshift to S3 periodically the dataset (using UNLOAD and already aggregated). A dataset, for me, is a folder with compressed csv files (one for each table, usually 15-20 per app). Then automate Qlik Sense to load the data from a specific folder; ideally, with one click reload all the tables.


How can I get the "Qlik Web Connector"? is a commercial solution, freeware?

From QVSource Trial Request there is a link to Qlik Sense support.

Thanks 

0 Likes
Not applicable

Great post. It is one of the most convenient way to automate in Cloud Business IMHO. But security is a big issue here.

Did you by any chance have also cracked the way how to access the non-public S3 files?

0 Likes
MVP
MVP

Sorry for a late response...

Regarding the Qlik Web Connector it is available for any existing Qlik Customer and Qlik Partner. They can log in with their customer id or partner id to the secured download area and find it there.

The Qlik Web Connector has a lot of different sub-connectors of which some are free and most have to be licensed on an individual basis. Some others are beta and are thus free to use until they turn into release at a later stage.

0 Likes
MVP & Luminary
MVP & Luminary

Hi petter-s

I am using Qlik web connectors to read the files in Amazon S3, I am able to read the single file successfully, is it possible to read all the files in entire folder dynamically using the For loop?  Please let me know if any references.

Regards,

Jagan.

0 Likes
MVP
MVP

Hi Jagan,

Yes it is. It is possible to read a listing of the files and based on that it is easy to do a FOR loop over the different files. The online help.qlik.com does not provide any documentation for beta-connectors such as Amazon S3. So you either have to look at the dialogboxes in the installed product or you can have a look at this older documentation: http://wiki.qvsource.com/Amazon-S3-Connector-For-QlikView-And-Qlik-Sense.ashx

Anyway the "table" to access is called "ListObjects" which gives you a listing of objects you can access in Amazon S3.

0 Likes
Partner
Partner

Hi Petter,

Would you be able to give an example of the FOR loop over the different files from the List Objects? That would be very helpful.

Thanks!

Contributor III
Contributor III

I found a very easy solution.

AWS Glue Crawler read files (any formats from csv to headers to parquet) from S3 and load in AWS Athena.

Qlik can connect to Athena with JDBC connector.

No server running, unlimited data space, very cheap cost for storage (S3 pricing), it seems that the speed is acceptable also for large files.

I'm also using S3 versioning to update tables data without files proliferation.

Multiple databases (S3 folders) allows granular access control.

I crunch the data with RedShift big data processor and AWS Machine learning (Tensorflow), output to s3 and switch off when finished. I also re-flow the output stored in S3 again in RedShift using Spectrum.

Please, any comment or feedback is useful. I'm still at PoC stage, running in beta next week.

Contributor
Contributor

Hey Melissa

I have a similar requirement. were you able to solution the reading of multiple files in loop from Qlik Sense?

thanks

Ram Gubbala

0 Likes
Partner
Partner

Hi Ram,

In the end, I reworked my structure, so I didn't end up needing to use this script, however, I did get it to work with some help from Qlik. We used the Qlik webconnectors to get the full filename with the required access/encryption keys.

Here is what we came up with. This only works if you disable standard mode, however.

//URL Encoding Mapping Load - Helps to encode the URL to avoid errors


URL_Encoding_Reference:

MAPPING LOAD

    Replace(Character,'space',' ')  as Character,

                Text("From UTF-8")                                                                 as URL_Encoding

FROM [https://www.w3schools.com/tags/ref_urlencode.asp]

(html, utf8, embedded labels, table is @1);


AmazonConnector_ListObjects:

LOAD

    Key as ListObjects_Key,

    LastModified as ListObjects_LastModified,

    ETag as ListObjects_ETag,

    Size as ListObjects_Size,

    OwnerId as ListObjects_OwnerId,

    StorageClass as ListObjects_StorageClass

FROM

[http://localhost:5555/data?connectorID=AmazonConnector&table=ListObjects&region="Encryption key goes here" &bucketName="bucketname goes here"&appID=]

(qvx);


// IMPORTANT: If, when loading the above script, you receive a 'Script Error' Dialog box with a 'Field Not Found'

// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]

// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.


set s3_prefix = "http://localhost:5555/data?connectorID=AmazonConnector&table=GetRawObject&region="Encryption key goes here"&bucketName="bucket name goes here"&objectKey=";

set s3_suffix = "&appID=";



for i = 0 to  NoOfRows('AmazonConnector_ListObjects')-1;

//let s3_object= mapsubstring('URL_Encoding_Reference','Dummy data.xlsx');

let s3_filename= peek ('ListObjects_Key',i,'AmazonConnector_ListObjects')   ;


let s3_object= mapsubstring('URL_Encoding_Reference','$(s3_filename)');


set var = "$(s3_prefix)$(s3_object)$(s3_suffix)";


[$(s3_filename)]:

Load

*

From

[$(var)]

(ooxml, embedded labels, header is 0 lines, table is Sheet1);

;


Next i



Version history
Revision #:
1 of 1
Last update:
‎2017-03-10 09:42 AM
Updated by: