# Using Lambda to Export and Import Table DDL in a Redshift Database

<!--BEGIN STABILITY BANNER-->
---

![Stability: Stable](https://img.shields.io/badge/stability-Stable-success.svg?style=for-the-badge)

> **This is a stable example. It should successfully build out of the box**
>
> This example uses the core CDK library, and does not have any infrastructure prerequisites to build.

---
<!--END STABILITY BANNER-->

This example uses Lambda functions to handle export and import of table DDL in an Amazon Redshift database. One Lambda exports table DDL to S3, while the other imports table DDL from S3 to a Redshift database.


## Prerequisites

Required tooling:
* AWS Cloud Development Kit (CDK) v2 - used for synthesizing and deploying the application
* Docker Desktop - used to build a Lambda layer for ```psycopg2-binary``` for Python 3.8

**NOTE: The psycopg2-binary package is a practical choice for development and testing but in production it is advised to use the psycopg2 package built from sources.**


## Build

To build this app, you need to be in this example's root folder. This will install the necessary CDK, then this example's dependencies, and then build your files and your CloudFormation template.

```bash
# To manually create a virtualenv on MacOS and Linux
python3 -m venv .venv

# After the init process completes and the virtualenv is created, you can use the following to activate your virtualenv
source .venv/bin/activate
# Use this if you are on Windows
# .venv\Scripts\activate.bat

# Once the virtualenv is activated, you can install the required dependencies
pip install -r requirements.txt
```

To build the required Lambda layer, use the script located at: ```resources/lambda_layer/createlayer.sh```  
```bash
# Create Lambda layer based on requirements.txt in lambda_layer directory
cd resources/lambda_layer
chmod +x createlayer.sh
./createlayer.sh 3.8
cd ../..
```


## Synthesize CloudFormation Template

To see the CloudFormation template generated by the CDK, run ```cdk synth```, then check the output file in the ```cdk.out``` directory.


## Deploy

Run ```cdk deploy```. This will deploy / redeploy your Stack to your AWS account. After the deployment completes, Lambda functions are available for use to export DDL to S3 and import DDL to the Amazon Redshift.


## Testing

Sample input for Lambda function to export DDL from Redshift cluster to S3.
```json
{
  "connection": {
    "host": "redshiftcluster-XXX.YYY.<region>.redshift.amazonaws.com",
    "port": "5439",
    "db": "dev",
    "user": "admin",
    "password_secret_arn": "arn:aws:secretsmanager:<region>:<aws-account-number>:secret:<secret-name>"
  },
  "schemas": [
    "<schema-name>"
  ]
}
```

Sample input for Lambda function to import DDL from S3 to Redshift cluster.
```json
{
  "connection": {
    "host": "redshiftcluster-XXX.YYY.<region>.redshift.amazonaws.com",
    "port": "5439",
    "db": "dev",
    "user": "admin",
    "password_secret_arn": "arn:aws:secretsmanager:<region>:<aws-account-number>:secret:<secret-name>"
  },
  "ddl_s3_uris": [
    "s3://<s3-bucket-name>/<s3-key>"
  ]
}
```


## Useful commands

 * `cdk ls`          list all stacks in the app
 * `cdk synth`       emits the synthesized CloudFormation template
 * `cdk deploy`      deploy this stack to your default AWS account/region
 * `cdk diff`        compare deployed stack with current state
 * `cdk docs`        open CDK documentation


## Security

See [CONTRIBUTING](CONTRIBUTING.md#security-issue-notifications) for more information.


## License

This library is licensed under the MIT-0 License. See the LICENSE file.