Qliksense Amazon S3 Web Connector - Loading Multiple JSON Files

[robzagey1]

Hi,

I am using the Amazon S3 Web connector and have managed to connect to S3 in sense, load a single JSON file. 

I am struggling to load multiple files in a bucket at once and have researched that web files won't support wild card loads, so it a for each loop doesn't seem to work. 

Anyone who has done this? 

Your help is appreciated. 

Rob

[PriyankaShivhare · Accepted solution]

u need to install S3 Web connectors on your Qlik Server and connect it to the S3 bucket and fetch the required file names which needs to be loaded

AmazonS3Connector_ListObjects_Channel_Data:
LOAD Key as ListObjects_Key,
LastModified as ListObjects_LastModified,
Timestamp(timestamp#(LastModified, 'YYYY-MM-DD hh:mm:ss')) as ListObjects_LastModified_qlik_timestamp,
Date(date#(left(LastModified, 10), 'YYYY-MM-DD')) as ListObjects_LastModified_qlik_date,
Time(time#(mid(LastModified, 12, 8), 'hh:mm:ss')) as ListObjects_LastModified_qlik_time,
ETag as ListObjects_ETag,
Size as ListObjects_Size,
OwnerId as ListObjects_OwnerId,
StorageClass as ListObjects_StorageClass
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=AmazonS3Connector&table=ListObjects&accessKey=sta_gen_1%3avZouXzzC9TjX27OjWNkr1A%3d%3d%3arWIqEddiHfhwtwmWTQUV%2bMQyOwY60ixDinSqAlFKBbA%3d&secretKey=sta_gen_1%3aVG7PGaIJRZLxVD1evpPyrQ%3d%3d%3aKDZ8hI%2brTdyGGUkAszqMfPUczkkVvKXXXXXXXXXXIqxXtKotbVuGlFeii3p5Uc&region=ap-West-2&bucketName=test-data&appID=], qvx)
WildMatch(Key,'$(EnvName_Channel)*');

Now Do a loop on Filenames:

Let VRowno = FieldValueCount('ListObjects_Key');

Trace Total New Files Available $(VRowno);

for i = 1 to $(VRowno);

LET vFile_Object = FieldValue('ListObjects_Key', $(i)) ;

Set Root = 'http://localhost:5555/data?connectorID=AmazonS3Connector&table=GetRawObjectAsText&accessKey=sta_gen_1%3aJhnf7u%2fgMQRyc6mObXTG2Q%3d%3d%3a9rHbuaMA2zYVcMJk5G%2bktofIc3hdZ29KVd2vtOz5ycI%3d&secretKey=sta_gen_1%3aWNHg8G7wP5QPghXXXXXXNw%3d%3d%3adYWLp6KmCYMsWMR1L%2fdri%2fP2Jd4QgetI47aBmRSHnAf1%2fYO9%2bp9ZryRLVEIvm8gM&region=ap-west-2&bucketName=test-data&' ;

Set vfilename = '$(Root)objectKey=$(vFile_Object)&appID=}';

Trace File name $(vfilename);

LIB CONNECT TO 'Data (user1)';

RestConnectorMasterTable:
SQL SELECT
"Field1",
"Field2",
"Field3",
"Field4",
"Field5"
FROM JSON (wrap on) "root"
WITH CONNECTION(Url "$(vfilename)"
);

Data:
LOAD [Field1],
[Field2],
[Field3],
[Field4],
[Field5]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

next ;

store Data into [$(vConnectionName)Data.qvd](qvd);

Hope This Helps

Thanks,

Priyanka

[robzagey1]

Thank you very much Prinyanka, 

After a bit of troubleshooting I got it and it works perfectly!! 

Thank you!!

[robzagey1]

Hi Priyanka,

One more question, apologies am new to JSONs. 

My JSON has what I think is called a nest, so some fields have sub fields, how do I extract these subfields? These some fields can change depending on the JSON. 

  "eventData": {

    "entityNumber": "12345",

    "idNumber": "861203588"}

[masilabalu]

Hi Priyanka,

what is this connection 

LIB CONNECT TO 'Data (user1)';

i am not understand , please explain what is use.

regards,

Balu.