S3 Download only if File is New

Question

Hi All,

I'm hoping to figure out the best solution for the following use case -

I have an s3 bucket with a series of files that get uploaded on an irregular basis (could be daily, weekly, monthly). I also have a table that previous files have been loaded into that have all file_names previously uploaded.

The goal would be to check my s3 bucket on a daily basis, see if any of the files are new, and if they are, import them into our Redshift Table.

Does anyone have a way that they have handled this?

IvarsK · Answer

Hi @mceleavey  
Downloaded the toolkit and it seems really well made, unfortunately there is no guidelines how to set up AWS s3 Objects tool and. Trying to upload multiple CSV files from one workflow in bucket, but I am getting this error -

When I use regular macro upload, filepath is fine and CSV files are uploaded.. 
Any suggestions what could be an issue here or where more info about the itself could be found?

Thanks

mceleavey · Answer

Hi @hatfielda24 ,

alternatively to that proposed by @jamielaird , I strongly recommend the Teknion Toolkit. You can download that HERE.

This allows you to read the list of files in the S3 bucket, cross reference with those you already have and then download the remainder. This acts much like the Directory tool.

Hope this helps,

M.

jamielaird · Answer

Hi @hatfielda24 ,

You can use the AWS CLI to create a JSON file that lists the objects in a bucket and then read and parse this file in Alteryx, and then dynamically select the files you are interested in. This is outlined here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Latest-File-from-AWS-S3-Bucket/td-p/451295

Here's a quick demo that I ran just now:

1) After installing and configuring the AWS CLI, you can use Alteryx to create and run a batch file that executes the following command:

2) You can then read this file into Alteryx and leverage the Last_Modified date to identify new files

3) You could then either dynamically read in the relevant files from S3 using the Dynamic Input tool or run a further command in the AWS CLI to download the files locally for reading into Alteryx

4) Outputting to Redshift can be done using a standard Output Data tool