Connect Pigment with AWS S3

This article explains how to connect a Amazon S3 bucket with your Pigment Organization to load any of csv files stored in that bucket into a Pigment’s block.

Overview

This process starts in AWS, with the creation of an IAM user with programmatic access. Next, you’ll add a policy to create permission for Pigment to access AWS.  This user and policy are used to generate the access key needed when establishing the connection in Pigment.  Establishing a connection within Pigment follows the standard steps of adding a new connection, uploading access keys, and defining the area to grab data from.  AWS has the added benefit of being able to choose from a fixed or most recent file.

Setting up access in AWS

Step 1 - Creating an IAM user 

Pigment uses Programmatic access based on the access key (“Access key ID”/”Secret access key”) to connect with your AWS S3 bucket.  For security reasons, Pigment recommends a dedicated IAM user. 

Follow these AWS instructions to create a user with Access Key - Programmatic access.

Step 2 - Set Permissions for IAM user 

On the next step, select Attach existing policies directly and click on Create policy.

Follow these AWS instructions to Create a policy with the following two permission statements:

Do they need to select a specific type of policy?  Json/visual editor? 

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "PigmentS3ReadFiles",
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": [
        "arn:aws:s3:::<MY_BUCKET_NAME>/*"
      ]
    },
    {
      "Sid": "PigmentS3ListFiles",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": [
        "arn:aws:s3:::<MY_BUCKET_NAME>"
      ]
    }
  ]
}

‘PigmentS3ReadFiles’ is mandatory (so that Pigment can access to files you need to load).
‘PigmentS3ListFiles’  is only needed if you want you the lan to use the “Pattern Mode”

Step 3 - Attach Permissions to IAM user to generate access key.

Once the policy is created, attach it to Pigment User that you created earlier.  

The last step of the user Creation process will prompt you with an access key generated for this user. Keep the “Access key ID” and the “Secret access key” you will need these for the next step. 

Establishing connection in Pigment 

Like for other integrations, go to the integration page from the Settings tab.

Next click on + Add next to the Amazon S3 integration.

Fill out the form with the following information. You will need to establish a connection for each individual AWS bucket. 

Name: give a name to your connection   Bucket name: name of the bucket which can be accessed with this connection. (ex: pigment-export ) Bucket region: AWS region associated to the bucket (ex: Europe (Frankfurt) )  Access key ID: ID of the access key associated to Pigment User from Step 3. Secret access key: secret of the access key assoiated to Pigment User from Step 3. Application access: Select the applications which will be allowed to use this connection

Use the connection in Pigment 

Once the Amazon S3 connection is configured, open an Application that the connection is available on and open the “Import Data” interface for the object you want to import data into.  For example, if you were importing data into a Transactions, you would open up list and then select Import and Download and then Import .

Instead of “Upload file”, select the Integration option. From here, select your Amazon S3 Connection.

Select the Import mode you want.

Fixed file

This import mode will choose one individual file name.

Just input the file name and location (ex: folder1/folder2/file.csv) and click on Import.

The file should load in Pigment within a few seconds!  If this configuration is saved, it will only upload that file name.

Most recent file

This import mode allows you to define a path and Pigment will upload the most recent file depending on a defined naming convention that must contain a ISO-8601 compatible date format within it. 

Naming convention in Amazon S3 

When naming your files in Amazon s3, your file name must follow a consistent naming convention with the date included.  Here are some examples of file names for a rev.csv with the ISO-8601 compatible date formats located at the start.

When writing the file path in Pigment

Similar to the Fixed file, you must first define the path with the location(ex: folder1/folder2/ ) ,then you must define file naming convention with a date included in the file name.  When entering the case sensitive path in Pigment, use {{date}} where the date is located. 

It should look something like this  folder1/folder2/file-{{date}}.csv

Here are some examples of file names and the results depending on what path you entered.

If the bucket contains the following files:

• /myfolderA/2022-09-15-salaries.csv
• /myfolderA/2022-10-15-salaries.csv
• /myfolderA/2022-10-16-revenue.csv
• /myfolderB/2022-11-15-salaries.csv

Here are the results I would get depending on my file path :

• path "myfolderA/{{date}}-salaries.csv" will import /myfolderA/2022-10-15-salaries.csv because this is the most recent file in myfolderA.
• path "myfolderB/{{date}}-salaries.csv" will import /myfolderB/2022-11-15-salaries.csv because this is the most recent file in myfolderB.
• path "/{{date}}-salaries.csv" will import nothing because its missing the folder location.

Advance: Server Side encryption 

The Pigment connector is compatible with Server Side Encrypted AWS buckets (SSE-S3 and SSE-KMS).

if you are using using SSE-KMS with a Customer managed Key (CMK), you must adjust the Policy of the CMK associated to the bucket to allow Pigment User to decrypt files. (AWS doc)

Add the below statement to the CMK policy:

        {
            "Sid": "Allow use of the key to decrypt",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<MY_AWS_ACCOUNT_NUMBER>:role/<MY_PIGMENT_IAM_ROLE_NAME>"
            },
            "Action": [
                "kms:Decrypt"
            ],
            "Resource": "*"
        }