Exporting Data Using Outbound Connectors

Nitro supports exporting data using outbound connectors for AWS and SFTP.

Support for AWS

Nitro customers using Amazon Web Services (AWS) can export data in bulk to their own Amazon S3 bucket using Outbound Connectors. This allows Nitro to exchange data with commercial and enterprise data applications.

Two variables are available to support date and datetime formats without hyphens for use in outbound connectors:

  • $[CDW_jobYYYYMMDD] - Stamps the job start date in YYYYMMDD format
  • $[CDW_jobYYYYMMDDHHMMSS] - Stamps the job start datetime in YYYYMMDDHHMMSS format

AWS Outbound Connector Prerequisites

Before provisioning a new outbound connector, customers must do the following in their AWS account:

  1. Create an S3 bucket. This serves as the target for the Nitro outbound connector.
  2. Ensure the Block all public access checkbox is selected in the Permission section of the S3 settings.
  3. Create an IAM Role for Nitro to use to connect to the S3 bucket and load data:
    • The trusted entity type is 'Another AWS account '
    • The Account ID is 822646232312 (the Nitro Production AWS Account ID)
  4. Create a Policy for the S3 bucket. This is used by the IAM Role to load data to the S3 bucket. The Permission must enable the following actions:
    • PutObjects
    • ListBucket
  5. Example

    Copy
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                    "s3:PutObject",
                    "s3:ListBucket"
                ],
                "Resource": [
                    "arn:aws:s3:::cdw-verteo-demo-s3/*",
                    "arn:aws:s3:::cdw-verteo-demo-s3"
                ]
            }
        ]
    }
  6. Attach the newly created Policy to the newly created IAM Role.

Creating an AWS Outbound Connector:

To create an outbound connector in Nitro:

  1. Open the NAC.
  2. Select Outbound from the Connectors menu.
  3. Select New Connector.

  1. Enter the display name and connector name.
  2. Select the Active checkbox.
  3. Enter the following information for the connector:
    • Description
    • Target Endpoint
    • AWS Credentials - from the access role created in AWS, for example, "arn:aws:iam::755709757004:role/vcdw-verteo-S3RemoteAccessRole"
    • UNLOAD Parameters
    • Nitro supports the Amazon Redshift UNLOAD command options that are currently supported as documented here https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html.

  4. Select Add to open the SQL editor.
  5. Add the source query(s) and the target location and file name.
  6. Select Save to close the query page.
  7. Select Save to create the outbound connector.

Generating the Trust Relationship

After an outbound connector is created in Nitro, admins must generate the trust credentials in the proper JSON format needed for the target S3 bucket. This makes it easier to write data to S3 and eliminates the need for a support ticket.

To retrieve the JSON trust credentials:

  1. Open the NAC.
  2. Navigate to the AWS_S3 Outbound Connector details page.
  3. Select the Key button. A modal displays with the JSONs corresponding to the Nitro instance.
  4. Select the Copy to Clipboard icon for each JSON text.
  5. Log in to the AWS Console and paste the JSON to the Trust Relationship of the IAM Role.

Running the Export

In the NAC:

  1. Select the appropriate instance from the drop-down menu in the top right corner.
  2. Select Run Job.
  3. Select the appropriate outbound connector.
  4. Select Run Outbound Connector Export.
  5. Select Execute.

Users can also schedule a Job to run the export.

Support for SFTP

Nitro customers can export data in bulk to SFTP targets using Outbound Connectors. SFTP is a widely leveraged protocol for bulk data transfers between IT systems within the Nitro customer base. With support for SFTP targets in Outbound Connectors, Nitro is better able to integrate with these systems without the need for custom development or support from additional infrastructure.

Creating an Outbound Connector for SFTP targets:

To create an outbound connector in Nitro:

  1. Open the NAC.
  2. Select Outbound from the Connectors menu.
  3. Select New Connector.
  4. Enter the display name and connector name.
  5. Select SFTP as the target type.
  6. Select the Active checkbox.
  7. Enter the following information for the connector:
    • Description
    • Target Endpoint - the endpoint for the SFTP server
    • Port - the SFTP port
    • Auth Type - this is defaulted to Credential and is not editable
    • User - Username for the SFTP server
    • Password - Password for the user
    • UNLOAD Parameters
    • Nitro supports the Amazon Redshift UNLOAD command options that are currently supported as documented here https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html.

  8. If the SFTP user has a subfolder and transfers the files to the subfolder, specify the subfolder + ‘/’ in the Target location and Filename field.

  9. Select Add to open the SQL editor.
  10. Add the source query(s) and the target location and file name.

    Example

    Copy
    Target Location and Filename: account/HCO
    (SFTP Server) User Home > account > HCO_000_part_0001.csv

    Example

    Copy
    Target Location and Filename: HCO
    (SFTP Server) User Home > HCO_000_part_0001.csv
  11. Select Save to close the query page.
  12. Select Save to create the outbound connector.

Running the Export

Select the Run Job icon on the connector detail page. Users can also schedule a job to run the export.