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
Generate the ssh key pair using the following command in local terminal:
CODEssh-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.
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:
CODEopenssl 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
Install the app from the App Library in Alli -
LiftLab Data Delivery - SQL to SFTP
Enter the required information into the new app:
App Name -
LiftLab Data Delivery - {Client_Slug}
Notification Email -
your email
SQL Query -
query for which you wish to export
Database Type -
Redshift or BigQuery
Remote Path -
directory where file should be uploaded (include the closing forward slash like example above)
SFTP username -
username provided from LiftLab
Private Key Base64 -
Copy and paste the text from the output file (id_rsa_{client_slug}_liftlab.base64) you created in the prerequisite steps
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.