Create a data API for TimescaleDB

Adding the psycopg2 library as a Lambda layer

1. Download and unzip the compiled psycopg2 library:

   wget https://github.com/jkehler/awslambda-psycopg2/archive/refs/heads/master.zip
   unzip master.zip

   Copy
2. In the directory you downloaded the library to, copy the psycopg2 files into a new directory called python. Make sure you copy the directory that matches your Python version:

   cd awslambda-psycopg2-master/
   mkdir python
   cp -r psycopg2-3.8/ python/

   Copy
3. Zip the python directory and upload the zipped file as a Lambda layer:

   zip -r psycopg2_layer.zip python/
   aws lambda publish-layer-version --layer-name psycopg2 \
   --description "psycopg2 for Python3.8" --zip-file fileb://psycopg2_layer.zip \
   --compatible-runtimes python3.8

   Copy
4. At the AWS Lambda console, check to see if your psycopg2 has been uploaded as a Lambda layer:
   

Creating a function to fetch and return data from the database

1. Create a new directory called timescaledb_api, to store the function code, and change into the new directory:

   mkdir timescaledb_api
   cd timescaledb_api

   Copy

2. In the new directory, create a new function called function.py, with this content:

   import json
   import psycopg2
   import psycopg2.extras
   import os
   
   def lambda_handler(event, context):
   
     db_name = os.environ['DB_NAME']
     db_user = os.environ['DB_USER']
     db_host = os.environ['DB_HOST']
     db_port = os.environ['DB_PORT']
     db_pass = os.environ['DB_PASS']
   
     conn = psycopg2.connect(user=db_user, database=db_name, host=db_host,
                          password=db_pass, port=db_port)
   
     sql = "SELECT * FROM stocks_intraday"
   
     cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
   
     cursor.execute(sql)
   
     result = cursor.fetchall()
     list_of_dicts = []
     for row in result:
       list_of_dicts.append(dict(row))
   
     return {
      'statusCode': 200,
      'body': json.dumps(list_of_dicts, default=str),
      'headers': {
          "Content-Type": "application/json"
          }
     }

   Copy

Uploading the function to AWS Lambda

1. At the command prompt, zip the function directory:

   zip function.zip function.py

   Copy
2. Upload the function:

   aws lambda create-function --function-name simple_api_function \
   --runtime python3.8 --handler function.lambda_handler \
   --role <ARN_LAMBDA_ROLE> --zip-file fileb://function.zip

   Copy
3. You can check that the function has been uploaded correctly by using this command in the AWS console:
   
4. If you make changes to your function code, you need to zip the file again and use the update-function-code command to upload the changes:

   zip function.zip function.py
   aws lambda update-function-code --function-name simple_api_function --zip-file fileb://function.zip

   Copy

Adding database configuration to AWS Lambda with environment variables

1. Create a JSON file that contains the variables required for the function:

   {
       "Variables": {"DB_NAME": "db",
         "DB_USER": "user",
         "DB_HOST": "host",
         "DB_PORT": "5432",
         "DB_PASS": "pass"}
   }

   Copy
2. Upload your connection details. In this example, the JSON file that contains the variables is saved at file://env.json:

   aws lambda update-function-configuration \
   --function-name simple_api_function --environment file://env.json

   Copy
3. When the configuration is uploaded to AWS Lambda, you can reach the variables using the os.environ parameter in the function:

   import os
   config = {'DB_USER': os.environ['DB_USER'],
         'DB_PASS': os.environ['DB_PASS'],
         'DB_HOST': os.environ['DB_HOST'],
         'DB_PORT': os.environ['DB_PORT'],
         'DB_NAME': os.environ['DB_NAME']}

   Copy

Testing the database connection

1. Invoke the function. Make sure you include the name of the function, and provide a name for an output file. In this example, the output file is called output.json:

   aws lambda invoke --function-name simple_api_function output.json

   Copy
2. If your function is working correctly, your output file looks like this:

   {
     "statusCode": 200,
     "body": "[
               {
                 \"bucket_day\": \"2021-02-01 00:00:00\",
                 \"symbol\": \"AAPL\",
                 \"avg_price\": 135.32576933380264,
                 \"max_price\": 137.956910987,
                 \"min_price\": 131.131547781
               },
               {
                 \"bucket_day\": \"2021-01-18 00:00:00\",
                 \"symbol\": \"AAPL\",
                 \"avg_price\": 136.7006897398394,
                 \"max_price\": 144.628477898,
                 \"min_price\": 126.675666886
               },
               {
                 \"bucket_day\": \"2021-05-24 00:00:00\",
                 \"symbol\": \"AAPL\",
                 \"avg_price\": 125.4228325920157,
                 \"max_price\": 128.32,
                 \"min_price\": 123.21
               },
               ...
             ]",
     "headers": {
     "Content-Type": "application/json"
     }
   }

   Copy

Creating a new API gateway

1. Create the API. In this example, the new API is called TestApiTimescale. Take note of the id field in the response, you need to use this to make changes later on:

   aws apigateway create-rest-api --name 'TestApiTimescale' --region us-east-1
   {
     "id": "4v5u26yw85",
     "name": "TestApiTimescale2",
     "createdDate": "2021-08-23T13:21:13+02:00",
     "apiKeySource": "HEADER",
     "endpointConfiguration": {
         "types": [
             "EDGE"
         ]
     },
     "disableExecuteApiEndpoint": false
   }

   Copy
2. Retrieve the id of the root resource, to add a new GET endpoint:

   aws apigateway get-resources --rest-api-id <API_ID> --region us-east-1
   {
     "items": [
       {
           "id": "hs26aaaw56",
           "path": "/"
       },
       {
           "id": "r9cakv",
           "parentId": "hs26aaaw56",
           "pathPart": "ticker",
           "path": "/ticker",
           "resourceMethods": {
               "GET": {}
           }
       }
     ]
   }

   Copy
3. Create a new resource. In this example, the new resource is called ticker:

   aws apigateway create-resource --rest-api-id <API_ID> \
   --region us-east-1 --parent-id <RESOURCE_ID> --path-part ticker
   {
     "id": "r9cakv",
     "parentId": "hs26aaaw56",
     "pathPart": "ticker",
     "path": "/ticker"
   }

   Copy
4. Create a GET request for the root resource:

   aws apigateway put-method --rest-api-id <API_ID> \
   --region us-east-1 --resource-id <RESOURCE_ID> \
   --http-method GET --authorization-type "NONE" \
   --request-parameters method.request.querystring.symbol=false

   Copy
5. Set up a 200 OK response to the method request of GET /ticker?symbol={symbol}:

   aws apigateway put-method-response --region us-east-1 \
   --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
   --http-method GET --status-code 200

   Copy
6. Connect the API Gateway to the Lambda function:

   aws apigateway put-integration --region us-east-1 \
   --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
   --http-method GET --type AWS --integration-http-method POST \
   --uri <ARN_LAMBDA_FUNCTION> \
   --request-templates file://path/to/integration-request-template.json

   Copy
7. Pass the Lambda function output to the client as a 200 OK response:

   aws apigateway put-integration-response --region us-east-1 \
   --rest-api-id <API_ID> / --resource-id <RESOURCE_ID> \
   --http-method GET --status-code 200 --selection-pattern ""

   Copy
8. Deploy the API:

   aws apigateway create-deployment --rest-api-id <API_ID> --stage-name test

   Copy

Creating a Lambda function to insert data into the database

1. Create a new function called insert_function.py, with this content:

   import json
   import psycopg2
   import psycopg2.extras
   from psycopg2.extras import execute_values
   import os
   from typing import Dict
   
   def lambda_handler(event, context):
   
       db_name = os.environ['DB_NAME']
       db_user = os.environ['DB_USER']
       db_host = os.environ['DB_HOST']
       db_port = os.environ['DB_PORT']
       db_pass = os.environ['DB_PASS']
   
       conn = psycopg2.connect(user=db_user, database=db_name, host=db_host,
                             password=db_pass, port=db_port)
   
       cursor = conn.cursor()
       sql = "INSERT INTO stocks_intraday VALUES %s"
       
       records = json.loads(event["body"]).get("records")
       if  isinstance(records, Dict):
           values = [[value for value in records.values()], ]
       else:
           values = [[value for value in item.values()] for item in records]
       execute_values(cursor, sql, values)
       conn.commit()
       conn.close()
   
       return {
           'statusCode': 200,
           'body': json.dumps(event, default=str),
           'headers': {
               "Content-Type": "application/json"
               }
       }

   Copy

2. Upload the function to AWS Lambda:

   zip insert_function.zip insert_function.py
   aws lambda create-function --function-name insert_function \
   --runtime python3.8 --handler function.lambda_handler \
   --role <ARN_LAMBDA_ROLE> --zip-file fileb://insert_function.zip

   Copy

3. Create a new API Gateway, called InsertApi:

   aws apigateway create-rest-api --name 'InsertApi' --region us-east-1

   Copy

4. Retrieve the id of the root resource, and add a new POST endpoint:

   aws apigateway get-resources --rest-api-id <API_ID> --region us-east-1
   {
     "items": [
       {
           "id": "hs26aaaw56",
           "path": "/"
       },
       {
           "id": "r9cakv",
           "parentId": "hs26aaaw56",
           "pathPart": "ticker",
           "path": "/ticker",
           "resourceMethods": {
               "GET": {}
           }
       }
     ]
   }

   Copy

5. Create a new resource. In this example, the new resource is called insert:

   aws apigateway create-resource --rest-api-id <API_ID> --region us-east-1 
   --parent-id <RESOURCE_ID> --path-part insert
   {
       "id": "arabc2",
       "parentId": "r5fc0ufn0h",
       "pathPart": "insert",
       "path": "/insert"
   }

   Copy

6. Create a POST request for the insert resource:

   aws apigateway put-method --rest-api-id <API_ID> --region us-east-1 \
   --resource-id <RESOURCE_ID> --http-method POST --authorization-type "NONE"

   Copy

7. Set up a 200 OK response to the method request of POST /insert:

   aws apigateway put-method-response --region us-east-1 \
   --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
   --http-method POST --status-code 200

   Copy

8. Connect the API Gateway to the Lambda function:

   aws apigateway put-integration --region us-east-1 \
   --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
   --http-method POST --type AWS --integration-http-method POST \
   --uri <ARN_LAMBDA_FUNCTION> \
   --request-templates '{ "application/json": "{\"statusCode\": 200}" }'

   Copy

9. Pass the Lambda function output to the client as a 200 OK response:

   aws apigateway put-integration-response --region us-east-1 \
   --rest-api-id <API_ID> / --resource-id <RESOURCE_ID> \
   --http-method POST --status-code 200 --selection-pattern ""

   Copy

10. Deploy the API:

    aws apigateway create-deployment --rest-api-id <API_ID> --stage-name test_post_api

    Copy