Crowe AWS Developer Technical Challenge

6 min readFeb 6, 2020

Introduction

Part of this technical challenge, I was given ten tasks to demonstrate my technical knowledge in serverless development skills as well as my understanding of web technology. I was also given a table and a .xlsx file which includes the following details.

Tasks to complete includes:
1. Create or use an existing AWS cloud account
2. Create a PostgreSQL database with the provided criteria.
3. Create tables in the database
4. Create a “S3” bucket named “fuel-qoutes”
5. Copy the file “Chevron Fuel Prices” into this bucket
6. Create a Cloud9 development environment
7. Create a Python3.6 lambda function to perform CRUD operations on the “fueltypes” table
8. Create a Python3.6 lambda function to read the data from the “Chevron Fuel Prices” spreadsheet and write or update the data in the “fuelprices” table
9. Create an “put” and “get” “Api gateway endpoints” for the fueltypes lambda function
10. Create an “s3 trigger” to automatically process the “fuelprices” lambda function when a new file is uploaded to the “S3 bucket”.

1. Create or use an existing AWS cloud account

I created a new gmail account as the primary user for the AWS cloud account called chun-rex-fong-crowe. It was rather straightforward. Besides that I also created a separate account with all access and got my programmatic access credentials which I used in the latter part of this project.

2. Create a PostgreSQL database with criteria

I used RDS from the AWS suite and created a PostgreSQL database with the lowest hardware settings to qualify for the free tier. According to AWS documentation, the default database was Postgres so I created the fuelservices database after I got access to Postgres via pg command-line tool. I created the database, two tables, schema afterward using the below SQL queries.

- CREATE DATABASE fuelservices;- CREATE SCHEMA testdata;- CREATE TABLE IF NOT EXISTS fueltypes (
  id SERIAL PRIMARY KEY, 
  fuel_type ALPHANUM, UNIQUE( fuel_type ));- CREATE TABLE IF NOT EXISTS fuelprices 
  (id SERIAL,created_on TIMESTAMP WITH TIME ZONE DEFAULT   CURRENT_TIMESTAMP,
  discount FLOAT8 DEFAULT NULL ,
  price FLOAT8  DEFAULT NULL ,
  fuel_type INT  DEFAULT NULL ,
  vendor ALPHANUM  DEFAULT NULL ,
  location TEXT, 
  effective_date DATE DEFAULT NULL ,
  PRIMARY KEY (id));

I also created a domain specifically to validate alphanum type.

CREATE DOMAIN alphanum as varchar(20) check (value ~ ‘^[a-zA-Z0-9]+$');

I notice that in the instructions, there’s no column named “location” even though it was one of the key from the .xlsx file. The provided .xlsx sheet data would fail the UNIQUE attribute validation, therefore, I took the liberty to add (location) to the composite key of (vendor, fuel_type,effective_date)

ALTER TABLE fuelprices ADD UNIQUE (vendor, fuel_type, effective_date, location);

4. Create a “S3” bucket named “fuel-qoutes”

I went to AWS dashboard and created an S3 bucket with the name “fuel-qoutes”.

5. Copy the file “Chevron Fuel Prices” into this bucket

Subsequently, I uploaded the .xlsx file there. I set everything to be public to reduce the work in the latter part of the project. However, I had experiences dealing with S3 bucket permission as per my Github account. I also picked Canada(Central) as my bucket location to be consistent with the project.

6. Create a Cloud9 development environment

I created a Cloud9 development environment using the free-tier and created a Python 3.6 environment. I encountered a bug in the environment which prevented me to load up the pip modules I need to complete the project. I discovered that I was not alone and it was recommended to install the modules locally to the project. One module the instance was not able to find was the psycopg2 module, which some suggest downloading the binary version of it. Also performance started to suffer as the environment was a free-tier. So I decided to move to the serverless framework to finish the rest of the project.

7. Create a Python3.6 lambda function to perform CRUD operations on the “fueltypes” table

With the serverless framework, I was able to create that relatively easy. I also went on to build the project with Javascript instead of Python which I’m more comfortable with and can best demonstrate my abilities. I could produce another project with Python3.6 however if requested.

I was able to use my local machine to create a lambda project which has a couple of benefits over developing via cloud9.

I was able to setup all of my configuration across AWS API Gateway, Postgres, S3, Lambda and Cloudwatch all in the same file that can be committed to a code repository. It will then generate a Cloudformation template and upload to AWS when I deploy.

# serverless.yml
service: crowe
plugins:
  - serverless-offline
  - serverless-s3-local
provider:
  name: aws
  runtime: nodejs12.x
  iamRoleStatements:
    - Effect: Allow
      Action:
        - s3:*
      Resource: "*"
  stage: dev
  region: ca-central-1  environment:
    USER: userfuel
    PASSWORD: *******
    HOST: fuelservices.c55btmimc6xf.ca-central-1.rds.amazonaws.com
    DATABASE: fuelservices
    DEBUG: truefunctions:
  system_check:
    handler: handler.system_check
    events:
     - http:
         path: system_check
         method: get
  fueltypes_restful_get:
    handler: handler.fueltypes_restful_get
    events:
     - http:
         path: fueltypes
         method: get
  fueltypes_restful_getbyid:
    handler: handler.fueltypes_restful_getbyid
    events:
     - http:
         path: fueltypes/{id}
         method: get
  fueltypes_restful_post:
    handler: handler.fueltypes_restful_post
    events:
     - http:
         path: fueltypes
         method: post
  fueltypes_restful_put:
    handler: handler.fueltypes_restful_put
    events:
     - http:
         path: fueltypes/{id}
         method: put
  fueltypes_restful_delete:
    handler: handler.fueltypes_restful_delete
    events:
     - http:
         path: fueltypes/{id}
         method: delete
  s3_xls_parser:
    handler: handler.s3_xls_parser
    events:
      - s3: 
        bucket: fuel-qoutes
        event: s3:ObjectCreated:*
        rules:
          -suffix: .xlsx

I was also able to develop the endpoints and s3 trigger using my localhost server. Speaking of the S3 event, I found it convenient to refer to this document that lists out all of the events.

Deployment is as simple as a single line

serverless deploy -stage dev

All of the lambda functions were deployed successfully. I also created all 4 endpoints for each CRUD actions:

AWS API Gateway

GET — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/system_check
GET — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/fueltypes
GET — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/fueltypes/{id}
POST — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/fueltypes
PUT — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/fueltypes/{id}
DELETE — https://ks6ncut33d.execute-api.ca-central-1.amazonaws.com/dev/fueltypes/{id}

Lastly, comes the time of production testing!

With a Postgres GUI, I was able to confirm the s3 file creation triggered the lambda function and parsed the xlsx document.

Conclusion

This project showcase various techniques and knowledge I have regarding serverless development. I have included a tool that I made to create a RESTful server along with Authentication capability in this github link.

Contact email: rexfng@gmail.com
Lambda Github: https://github.com/rexfng/crowe-technical-assignment