# Handle S3 Multiple Folder-\>Json File Structure achieve using S3 load Manifest (If you already know the folder and file name)

**URL:** <https://matillioncommunity.discourse.group/t/handle-s3-multiple-folder-json-file-structure-achieve-using-s3-load-manifest-if-you-already-know-the-folder-and-file-name/1331>\
**Category:** Matillion ETL\
**Created:** [May 6, 2021, 1:06pm UTC](https://matillioncommunity.discourse.group/t/handle-s3-multiple-folder-json-file-structure-achieve-using-s3-load-manifest-if-you-already-know-the-folder-and-file-name/1331 "2021-05-06T13:06:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ganesh\_pitchai](https://avatars.discourse-cdn.com/v4/letter/g/67e7ee/32.png) [@ganesh\_pitchai](https://matillioncommunity.discourse.group/u/ganesh_pitchai)\
**Post date:** [May 6, 2021, 1:06pm UTC](https://matillioncommunity.discourse.group/t/handle-s3-multiple-folder-json-file-structure-achieve-using-s3-load-manifest-if-you-already-know-the-folder-and-file-name/1331/1 "2021-05-06T13:06:09Z")

</div>

![0694G00000E38vJQAR_0D54G00007fN58TSAS](https://us1.discourse-cdn.com/flex002/uploads/matillioncommunity/original/1X/7cd14e60fede2580d9123e72ea7e320f1ea76964.png)

&nbsp;

If you struggle like the above image Multiple Inside file structures and If you already know the folder and object name. We can load the objects in less time without using each Iterator in&nbsp; **Schedule**.

&nbsp;

Step 1: First We have to find the input source row count below 500 or above 500. The reason is&nbsp; **redshift cell limit 65k** &nbsp;restriction, you will know the reason following steps.

&nbsp;

Using python script we make a decision 500 above or below

&nbsp;

**cursor=context.cursor()**

**cursor.execute("select case when&nbsp;500\>=(select count(1) from tablename) then 1 else 0 end ")**

**result=cursor.fetchone()**

**result=int(result[0])**

**if result==1:**

**&nbsp;context.updateVariable(“manifest\_decision”, ‘below\_500’)**

**else:&nbsp;**

**&nbsp;context.updateVariable(“manifest\_decision”, ‘above\_500’)**

&nbsp;

Based on the decision we can load the data two different ways using the&nbsp; **“if”** &nbsp;component.

&nbsp;

Step 2:&nbsp; **Below 500 way**

**﻿**

 ![0694G00000E38vdQAB_0D54G00007fN58TSAS](https://us1.discourse-cdn.com/flex002/uploads/matillioncommunity/original/1X/102d929b8938257f891537816c27989744ebff03.png)

&nbsp;

In this part first, create manifest data as a view using folder name and object name from the source table.

&nbsp;

**create or replace view manifest\_view as**

**(select replace(‘{**

**&nbsp;“entries” : [’||listagg(manifest, ‘’)||‘]**

**}’,‘,]’,‘]’)from**

**(select&nbsp;**

**‘{**

**&nbsp;&nbsp;“url” : "s3://path/’||Foldername (from table)**

**||‘/’||Objectname (from table)||‘",**

**&nbsp;&nbsp;“mandatory” : true**

**&nbsp;},’ as manifest from table&nbsp;limit 500))**

&nbsp;

Based on the result create a CSV file using the text output component the file extension should be “.manifest”.

&nbsp;

After creating the file we need to clean up inside the file data for proper manifest data format. We can update using the&nbsp; **python component**.

&nbsp;

**import boto3**

**import json**

**s3 = boto3.resource(u’s3’)**

&nbsp;

**csv\_object = s3.Object(‘lcu-matillion’, ‘path/file.manifest’)**

&nbsp;

**f= csv\_object.get()[‘Body’].read().replace(‘“”’, ‘"’).replace(‘"{’,‘{’).replace(‘}"’,‘}’)**

**f = json.loads(f)**

&nbsp;

**with open(“manifest\_test.manifest”, “w”) as outfile:**

**&nbsp;json.dump(f, outfile)**

&nbsp;

**s3.meta.client.upload\_file(‘manifest\_test.manifest’, ‘lcu-matillion’, ‘path/file.manifest’)**

**os.remove(“manifest\_test.manifest”)**

&nbsp;

Note:_&nbsp;These two steps CSV creation and python update taking less time compare to complete python code write._

&nbsp;

Finally, we can load the bulk objects using this manifest file. You can refer matillion documentation for manifest file load.

&nbsp;

Step 2:&nbsp; **Above 500 Way**

In this way, we need to iterate the data and pass each 500 set limit till maximum records.

 ![0694G00000E38viQAB_0D54G00007fN58TSAS](https://us1.discourse-cdn.com/flex002/uploads/matillioncommunity/original/1X/6c76e2aa56b9815e8fde9b01b2e6a910e72ffa89.png)

&nbsp;

Like above need to create a temp table first table name as 500\_split.

&nbsp;

First, create an additional view to find out the maximum record and splitting limit.

&nbsp;

**create or replace view 500\_split\_view as**

**(select \* from 500\_split where**

**id \<=**

**(select**

**ceil((select max(row\_num) from**

**(select foldername,objectname,row\_number() over (order by foldername,objectname) as row\_num**

**from table))/500::float)) order by id)**

&nbsp;

Using&nbsp; **table iterator** &nbsp;pass the&nbsp; **start** &nbsp;and&nbsp; **end** &nbsp;values from&nbsp; **500\_split\_view .**

&nbsp;

 ![0694G00000E38w2QAB_0D54G00007fN58TSAS](https://us1.discourse-cdn.com/flex002/uploads/matillioncommunity/original/1X/d2ab6cd9286ca011ec7e75a54f2c5b5b2e143b8b.png)

&nbsp;

&nbsp;

Finally, create the orchestration job same flow like below 500 and first view creation only different like below

&nbsp;

**create or replace view staging.manifest\_view as**

**(select replace(‘{**

**&nbsp;“entries” : [’||listagg(manifest, ‘’)||‘]**

**}’,‘,]’,‘]’)from**

**(select&nbsp;**

**‘{**

**&nbsp;&nbsp;“url” : "s3://path/’||foledername**

**||‘/’||objectname||‘",**

**&nbsp;&nbsp;“mandatory” : true**

**&nbsp;},’ as manifest&nbsp;**

**&nbsp;from**

**&nbsp;(select foledername,objectname,row\_number() over (order by entityid,subentityid) as row\_num**

**from table) where row\_num between ${start} and ${end}**

**))**

&nbsp;

&nbsp;

.You can load data in multiple iterations with 500 set objects.

&nbsp;

&nbsp;

Hint:&nbsp;Based on my design 25 fields from JSON 500 files taking less than 5 minutes.

&nbsp;

---

<div class="post-metadata">

**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\
**Post date:** [May 6, 2021, 1:44pm UTC](https://matillioncommunity.discourse.group/t/handle-s3-multiple-folder-json-file-structure-achieve-using-s3-load-manifest-if-you-already-know-the-folder-and-file-name/1331/2 "2021-05-06T13:44:04Z")

</div>

Hi [@ganesh\_pitchai](https://matillioncommunity.discourse.group/u/ganesh_pitchai)​

This is a brilliant post, thank you for sharing, I like the use of multiple Matillion ETL Components from the table iterator to the orchestration job leading to an end result!

I think the community will find this really handy and I cannot wait to hear what other users' thoughts are in relation to this but also what have they achieved similar, amazing use of Matillion ETL!

Kind regards, Joe
