Extending Integration with AWS

[DanH]

Amazon offers many different services that can be accessed from the AWS Command Line Interface (aka the AWS CLI). Alteryx workflows can take advantage of these services using the AWS CLI from the Run Command tool.
 

Some of the common uses of the Amazon CLI within Alteryx include:

• Using S3 with IAM roles
• Uploading/Downloading file types not currently supported by the S3 Upload / Download tools
• Interacting with Athena

As an example of using the AWS CLI, let’s build a process that can take a local file and copy to S3, using an AWS IAM profile. Here are the steps involved:

• Review the documentation for the service. In this example, we're using the "S3" service, but Amazon has many different AWS services.
• Install the AWS CLI on any machines that will need to execute the AWS functions
• From Windows Command Line, create a working command
  1. (Optional step to make calling the CLI easier in the future) – Add reference to the AWS CLI executable to Window Environment variables via My Computer > Right Click > Properties > Advanced System Settings > Environment Variables > System Variables > Select “Path” > Edit > (Add the path to the directory of your installed “aws.exe” file, separated by a semicolon. Do not delete/modify any pre-existing values in the Path variable.) > OK > OK
  2. Per the AWS documentation, create a local profile using your IAM role. The local profile references your credentials so that you don't have to pass the credentials in every command.
  3. Test the following command in your Windows command prompt, making sure to use a valid local file to copy, and a valid destination location on your S3, and a valid profile name:

s3 cp "C:\temp\mylocalfile.csv" "s3://mylocation/mydestinationfilename.csv" --profile MyIAMUser

• Once you've validated your command in the command prompt, transfer it to an Alteryx workflow
  1. In a new workflow, pull in a “Run Command” tool from the “Developer” category
  2. Configure the Run Command tool using the working command:

Test the above

• As an optional step, we can wrap this functionality into a macro to make it easy to update, and to support batch uploads
  1. Add a Control Parameter for “Local File Path” and configure its action to update the specific string value “C:\temp\myfile.csv”
  2. Add a Control Parameter for “Destination File Path” and configure its action to update the specific string value “s3://mybucket/myfiletest1.csv”
  3. Add a Text Box and configure its action to update the command value “aws” in case the user hasn’t placed the CLI exe in their PATH variables

The example above is attached in the 11.3 workflow, SaveToS3_usage.yxzp.

[kirk_yates]

Thanks for this article Dan!

We have been trying to utilize the command tool to write to an AWS bucket that we do not own (but have permissions to).  We have followed your instructions and cannot get past an "Error: Run Command (3): The external program "aws" returned an error code: 1"

We have copied exactly the Command that works in the command prompt and cannot figure out why we are receiving this error.

In the image above, I replaced my userid with "machine" and the actual external AWS bucket with "external-bucket".  Everything else is as is and what works directly in the command prompt.  We receive the same error with the quotes around the file and destination.  

The "--profile IAMUser" was not required in the command prompt and tried it in Alteryx, and that generates a 255 error.  Is the --profile required for Alteryx?

I have read everything I could find about the Write Source and Read Source and cannot wrap my brain around what their point is and how we are supposed to use them.  Is that causing the problem?

Do you have any ideas why it would not work?

Thank you!

kirk

[DanH]

Hi @kirk_yates 

Here's what I would check:

• In my example, I had mapped the AWS CLI in my environment variables, which allows me to reference it as "AWS" and not have to worry about changing directories to the full path of the executable. If you have not mapped yours in environment variables, you'll need to supply the full directory location.
• From the screenshot, it looks like there's a space in "machine \Desktop".
• In my example, I'm referencing specific files in the source and destination. Are you doing this, or are you trying to copy whole directories?
• I would think the IAM Profile reference would be required in most cases. I know you can also explicitly provide credentials in the call, but an IAM reference is probably more standard. I think there can be a default IAM Profile for the executing Windows user (in which case maybe you don't need to authenticate), but I haven't tested that behavior. Does write access to the bucket require authentication (I would assume so)?
• When you successfully run the command in the Windows command line, are you running as the same Windows account as you're running Designer?
• "Write Source" is intended for cases where the command requires a specific file to operate on, and you have an incoming connection feeding into the Run Command tool, which then is written before the command executes. "Read Results" is intended for cases where the command produces or modifies a file, in which case you can read that resulting file directly back into the outgoing stream after the command is done executing. I generally include this input/output file definition, even if it's referencing a preexisting dummy file, so that I can hook the Run Command into a larger process. The command itself doesn't necessarily have reference either file.

I hope this helps!

-Dan

[kirk_yates]

Thanks for the response @DanH !

Below are the responses to your questions:

• Yes we have mapped the AWS CLI in environment variables, however, we put the entire path there, with the same result.
• Yeah the space was a bad copy/paste job to white out my user id!  There is no space in the path.
• We are referencing a specific file in the source and destination.
• We definitely need to authenticate to write.  We tried it using the --profile name we have set up and received the same result (confirmed using the same --profile name in the WIndows Command line and it worked there)
• I am not sure I understand your comment about the same windows account.  If we use the --profile that is set up in the AWS configuration, not sure how they could be different?  Am I missing something there?
• We put a dummy input and output file for the Read and Write sources.  Same result.

Any further thoughts on the topic would be very helpful.  Feels like we are so close, but cannot get over the finish line on this. 

Thanks for your help.   

- kirk

[kirk_yates]

It is me again @DanH !

Although this time with great news! 

Yesterday I updated Alteryx Designer to Version 2020.2.3.27789.  I reopened the same Alteryx .yxmd file, changed nothing, hit Run and everything worked fine!

Not sure what was changed in the Designer version, or if it is just coincidence and I was having something weird going on in my environment, but we are in business. 

Thanks once again for your help and attention!

-kirk

kirk

[DanH]

Hi @kirk_yates 

I'm glad to hear you got this working. Sorry for the delayed response- I was trying to do some additional testing on my end to give you some next steps.

I looked through the 2020.2 features and fixes, and don't really see anything should have changed the behavior of Run Command. It's possible there was some bug in an older version that had already been resolved pre-20.2, or it's also possible that there was something environmental related to the install location or permissions that was different between your two versions. The error code makes me suspect a path or permission issue. If you ever discover what the issue was, I'd be curious to know.

Thanks,

Dan

[nitinpango]

I tried doing this on Alteryx server but I got error code 253

[phil_rimmer]

I am also getting error code 253 when attempting to run this on server, did you manage to resolve @nitinpango 

[phil_rimmer]

I managed to get around the "error code 253".  The problem was that although we had setup the server config, when running the workflow the AWS Key was not available.  I then ran a workflow on the server with a Run Command Tool with this 

cd\

cd program files\amazon\awscliv2

aws configure set aws_access_key_id <INSERT ACCESS KEY HERE> && aws configure set aws_secret_access_key <INSERT SECRET KEY HERE> && aws configure set default.region <Insert Region>

[MKanchwa]

Hi @DanH ,

Can we run the workflow which uses AWS CLI & Run Command tool on alteryx gallery?

I have AWS CLI installed on my machine.

Regards,

Mustafa Kanchwala

[DanH]

@MKanchwa  Yes,  since the engine on Server is the same as on Designer, you can execute the same commands on a (private) Server that had the CLI installed. Note that there may be authentication considerations to manage in an environment with multiple users. Note also that there's a Server configuration option ("Safe Mode)" that intentionally prevents executing the Run Command tool. Also note that the "public" gallery (https://gallery.alteryx.com) doesn't allow execution of tools with Run Command.   

Thanks,

Dan