LiftLab Data Delivery - SQL to SFTP Marketplace App Setup

Python Script that allows you to query a table in Redshift or BigQuery and export it as a csv file that gets sent directly to the designated LiftLab's SFTP server. If you are setting up a client net new, be sure to follow the prerequisite steps first to ensure successful SFTP authentication and onboarding.

Prerequisite

1. Generate the ssh key pair using the following command in local terminal:
   

   CODE

   ssh-keygen -t rsa -m PEM -C "{client_slug}-LiftLab-Key" -f id_rsa-{client_slug}-liftlab

   This will generate a .pub (public key) and id_rsa-lillypulitzer-liftlab will be the file that contains the private key that should be created in your /Users/{username}/.ssh directory in Finder.

The generated public key file is what you will need to give to your LiftLab contact for them to authenticate you and provide the SFTP credentials and file directory path needed for app installation.

2. Encode the private key file using base64 encoding so that it can be used in the app by running the following command in terminal after generating the public and private key files:
   

   CODE

   openssl enc -base64 -in .ssh/id_rsa-{client_slug}-liftlab -out id_rsa_{client_slug}_liftlab.base64

   This will output an encoded file ending in .base64 that will be need to be opened in later in the Marketplace App setup instructions.

Additional information on ssh-keygen and ssh keys can be found here.

How to setup Marketplace App

1. Install the app from the App Library in Alli - LiftLab Data Delivery - SQL to SFTP

2. Enter the required information into the new app:

   1. App Name - LiftLab Data Delivery - {Client_Slug}

   2. Notification Email - your email

   3. SQL Query - query for which you wish to export

   4. Database Type - Redshift or BigQuery

   5. Remote Path - directory where file should be uploaded (include the closing forward slash like example above)

   6. SFTP username - username provided from LiftLab

   7. Private Key Base64 - Copy and paste the text from the output file (id_rsa_{client_slug}_liftlab.base64) you created in the prerequisite steps

3. Click Save and Run after entering required details.

A file should be created upon successful execution in the directory specified on the LiftLab SFTP server. You can use FileZilla or CyberDuck to connect and view the file using the SFTP credentials.

Reach out to the Data Engineering team for assistance if any issues arise.