Configure Exports to Cloud Storage (S3 or GCS)

IMPORTANT: This document is specific to Sigma worksheets. To learn about export options for Sigma’s new Workbooks feature, please visit Send and Schedule Workbooks.

Sigma worksheets can be exported to Google Cloud Storage (GCS) or Amazon S3 from your Snowflake and Redshift connections. 

Cloud storage is particularly useful for large multi-GB data exports. These exports are sent from Sigma to your cloud storage bucket as a single file and can be accessed similarly to other exports. 

Summary of Content

Requirements
Connection Details and Setup
      Snowflake
      Redshift
            Configure S3 to Accept Sigma Exports
                  [Prerequisite] Identify/Create a bucket
                  [Step 1] Create an IAM role with a permission policy
                  [Step 2] Create a Trust Relationship
                  [Step 3] Add the IAM role to your Redshift cluster
            File Format and Naming in Redshift
URI Formatting
Send a Single Export
Create an Export Schedule
Related Resources

Requirements

  • You must be an organization Admin.
  • A Snowflake or Redshift connection. This feature is not supported for BigQuery or Postgres connections. 
  • If your connected warehouse is not yet configured for cloud storage exports, you will need the appropriate warehouse permissions to configure your cloud storage.
    Note: See your warehouse for required permissions.

 

Connection Details and Setup

Before you can export Sigma worksheets to cloud storage, you will need to set up your storage environment and identify the values required for Sigma to successfully export your data to your target storage bucket. This process is warehouse dependent. 

Snowflake

Snowflake supports exports to both S3 and GCS. Please follow Snowflake’s instructions to create your storage integration.

Supported file types: CSV, JSON, Parquet and Avro
GZIP compressions: supported
Export limit: 5 GB
Values required by Sigma: your target URI and your Snowflake Storage Integration name 

Redshift

Redshift supports worksheet exports to S3. To get started with S3 storage, visit Amazon’s S3 documentation

Supported file types: CSV
GZIP compressions: supported
Export limit: 6.2 GB
Values required by Sigma: your target URI and an IAM role

Configure S3 to Accept Sigma Exports

[Prerequisite] Identify/Create a bucket

Before starting, identify an existing S3 bucket for your exports, or create a new one. Your target bucket should live in the same instance as your connected Redshift cluster.

[Step 1] Create an IAM role with a permission policy

First you will need to create an Identity and Access Management (IAM) role for the user associated with your Redshift connection in Sigma. This role will be granted a policy to export files from Sigma to your bucket of choice. The permission policy can be created before or during the role creation process. The instructions below will walk you through creating both the role and policy simultaneously. 

  1. Open your AWS IAM console. 
  2. Under Access Management, select Roles.
  3. Click Create Role.
  4. Select S3 from the list of available services.
  5. Under Select your Use Case, select S3.
  6. Click Next: Permissions.
    In the next step, you will define the role’s permission policies.
  7. If you have already created a policy, skip ahead to step 16.
    Otherwise, click Create Policy. This will open a new Create policy tab in your browser. Do not close the original tab. You will return to it in step 14. 
  8. Open the JSON tab on this new page, and add the blob below. View AWS's S3 bucket management documentation
  9. {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "VisualEditor1",
               "Effect": "Allow",
               "Action": [
                   "s3:PutObject",
                   "s3:Get*",
                   "s3:List*"
               ],
               "Resource": [
                    "arn:aws:s3:::<bucket name>/<[optional] folder in bucket>/*"
               ]
           }
       ]
    }
  10. Click Next: Tags.
    Note: You can skip the Add tags page.
  11. Click Next: Review.
  12. Name your policy.
  13. [Optional] Add a Description.
  14. Click Create Policy.
    You will be redirected to the Policy page in the Access Management section of your IAM console. View AWS’s policy documentation.
  15. Switch back to your original Create role browser tab.
    Note: You should still be on step 2 of the Create role page (aka Attach permissions policies).
  16. Click the refresh button to refresh the policy list. 
  17. Search for and select your new policy from the list.
  18. Click Next: Tags.
    Note: You can skip the Add tags page.
  19. Click Next: Review.
  20. Name your role.
  21. [optional] Add a description.
  22. Click Create role.
    You will be redirected to the Role page in the Access Management section of your IAM console.

 

[Step 2] Create a Trust Relationship

Creating a trust relationship allows the user associated with your Redshift connection in Sigma to assume the IAM role in Redshift when attempting to export to S3. View AWS’s related documentation

  1. Open the Role page in the Access Management section of your IAM console.
  2. Select the IAM role you created in [Step 1].
  3. Open the Trust relationships tab under the role summary.
  4. Click Edit trust relationship.
  5. Enter the policy below with your Amazon Resource Name (ARN).
    Note: Set <user-name>, to the User defined on your Redshift connection in Sigma.
    Note: To find your <account-id>, follow AWS's instructions.
    {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Principal": {
           "Service": "redshift.amazonaws.com"
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
              "sts:ExternalId": [
          "arn:aws:redshift:
    <region>:<account-id>:dbuser:<cluster-name>/<user-name>",
             ]
           }
         }
       }
     ]
    }
  6. Click Update Trust Policy.

 

[Step 3] Add the IAM role to your Redshift cluster

View Redshift’s instructions.

  1. Open the CLUSTERS tab in your Amazon Redshift console.
  2. Select your cluster. 
  3. Under the Actions drop down menu, select Manage IAM roles.
  4. Add the role created in [step 1] under Available IAM roles.
  5. Click Associate IAM role.
  6. Click Save changes.

 

File Format and Naming in Redshift

Sigma exports your data with the file name and format you specify. However, Redshift adds 000 to the end of file names and removes the extension (e.g. .csv) while still observing the file format. 

Example: File_name.csv exports to File_name000

URI Formatting

A URI represents your export’s target file path in your cloud storage provider. 

The following template can be used to format your URI: <schema>://<bucket>/<file path>/<filename><filetype><compression>

Schema: “s3” if exporting to S3 or “gcs” if exporting to GCS. 

Bucket: the higher level storage object that these providers support, but can be thought of as a root folder. 

File Path (optional): the subdirectories under your bucket. If this value is excluded, your files will be added directly to the root of your bucket. 

File Type:  “.csv”, “.avro”, “.json” or “.parquet”.
Note: Not all file types are supported by all connections. Check your connection’s “supported file types” to confirm your options. 

Compression: (optional) can only be “.gz” 
Note: Not all file types are supported by all connections. Check your connection’s “supported file types” to confirm your options. 

Example URI – s3://my-sigma-bucket/cloud-exports/sales.csv.gz
Note: If your URI is identical to an existing file in the bucket, it will be overwritten. 

 

Send a Single Export

  1. Open the worksheet you would like to export. 
  2. Click the vertical ••• menu in your worksheet’s header to open its header menu.
  3. Under the Data Export submenu, select your storage provider: Amazon S3 or Google Cloud Storage.
    Screen_Shot_2021-03-16_at_2.54.19_PM.png

  4. You will now see the Export to Cloud modal. The value(s) required for export are dependent on your data’s warehouse type.  
    For a Snowflake sourced worksheet, enter a URI and Storage Integration.
    For a Redshift sourced worksheet, enter a URI and IAM Role
  5. Click Export.
    An export confirmation will be sent to your email. 

 

Create an Export Schedule

  1. Open the worksheet you would like to export. 
  2. Click the vertical ••• menu in your worksheet’s header to open its header menu.
  3. Click Schedule… to open the Scheduled Exports modal.
  4. Click Add Schedule.
  5. Under Destination, select Cloud Export.
    Screen_Shot_2021-03-26_at_4.24.15_PM.png
  6. Select your Storage Provider: Amazon S3 or Google Cloud Storage.
  7. The value(s) required for export are dependent on your data’s warehouse type.  
    For a Snowflake sourced worksheet, enter a URI and Storage Integration.
    For a Redshift sourced worksheet, enter a URI and IAM Role.
    Important: The URI targeted file will be rewritten to the newest version on each scheduled export.
  8. Select a schedule under the heading Repeat by.
  9. [optional] Select your Send condition.
  10. Click Create Schedule.

 

Related Resources

Send or Schedule Reports
Connect to Snowflake
Connect to Redshift

IMPORTANT: This document is specific to Sigma worksheets. To learn about export options for Sigma’s new Workbooks feature, please visit Send and Schedule Workbooks.