Stitch S3 DB Integration

· 2 min read

Given

  1. Source S3 path and file delimiter
  2. data warehouse connection details (endpoint, port, username, password and database name)
  3. data warehouse schema name and table name
  4. Run frequency

Steps

  1. Log into your stitch account, here
  2. Click on the Destination tab and use the data warehouse connection details to establish a destination database.
  3. Click on Add Integration button on your dashboard. stitch-s3-1
  4. Select Amazon S3 CSV as the integration in the next page. stitch-s3-2
  5. In the next page, select Integration Name by default this will be the name of the schema where the S3 file is going to be copied into. Use the give data warehouse schema name.
  6. In the Search Pattern type in the file name from the given S3 path.
  7. In the Table name type in the given data warehouse table name.stitch-s3-3
  8. Use the given file delimiter for the Delimiter option, set Replication frequency to the given replication frequency and click on Authorize. stitch-s3-4
  9. In the next page you will be provide with details for a access policy in terms of JSON and account id, external id and role name for an AWS IAM role to access your S3 bucket. stitch-s3-5
  10. Log into your AWS account here
  11. Go to Services -> IAM, click on Policies on the left tab and press the Create Policy button. stitch-s3-6
  12. In the next Create policy page, choose the JSON tab and paste the JSON from step 9 as shown. stitch-s3-7
  13. In the next page, use an appropriate policy name like stitch_s3_policy and press Create Policy. stitch-s3-8
  14. Now go to Services -> IAM and choose the Roles option in the left pane and select Create Role. stitch-s3-9
  15. In the Create role page, choose Another AWS account and use the account ID and external ID from step 9.stitch-s3-10
  16. In the next page add the policy you created earlier in step 13 and press Next: tags. stitch-s3-11
  17. In the next page, enter the Role name value from step 9 and click on create role. stitch-s3-12
  18. Now go back to your stitch account and press check and save. stitch-s3-13
  19. You will see a testing connection page, wait for this to complete. stitch-s3-14
  20. Then you can choose the table which you want to load the data into, this will be the table name you entered in step 7, make sure to check the table checkbox and after clicking on the table to check all the columns check box.click on Finalize your selection. stitch-s3-15