jdaarevalo

ProcessorFunction:
  Type: AWS::Serverless::Function
  Properties:
    CodeUri: src/
    Handler: processor_lambda.lambda_handler
    Runtime: python3.9
    Architectures:
      - x86_64
    Policies: 
      - AmazonAthenaFullAccess

jdaarevalo

ProcessedDataS3Bucket:
  Type: 'AWS::S3::Bucket'
  Properties:
    BucketName: !Join
      - "-"
      - - "scatter-gather-processed-data"
        - !Select
          - 0
          - !Split
            - "-"

jdaarevalo

GlueRawDataNYTimesCovidTable:
  Type: 'AWS::Glue::Table'
  Properties: 
    CatalogId: !Ref AWS::AccountId     
    DatabaseName: !Ref GlueRawDataBase
    TableInput: 
      Description: "Raw Data on COVID-19 cases from NY Times at US state level."
      TableType: "EXTERNAL_TABLE"
      Retention: 0
      Name: covid_nytimes_states

jdaarevalo

"""
Purpose

Run an Athena query and post in Slack the output location as CSV
Tag the user who does the request
"""

import os
import json
import time

jdaarevalo

...
volumes: 
    - ./postgres-data:/var/lib/postgresql/data
    # copy the sql script to create tables
    - ./sql/create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql
    # copy the sql script to fill tables
    - ./sql/fill_tables.sql:/docker-entrypoint-initdb.d/fill_tables.sql

jdaarevalo

-- Filling of order_status
INSERT INTO order_status
select gen_random_uuid ()
	, date_sale + random()* (date_sale + '5 days' - date_sale)
	, sale_id
	, floor(random() * (current_setting('my.status_names')::int) + 1)::int
from sale;

jdaarevalo

floor(random() * (current_setting('my.number_of_products')::int) + 1)::int

jdaarevalo

TO_TIMESTAMP(start_date, 'YYYY-MM-DD HH24:MI:SS') +
random()* (TO_TIMESTAMP(end_date, 'YYYY-MM-DD HH24:MI:SS') 
				 - TO_TIMESTAMP(start_date, 'YYYY-MM-DD HH24:MI:SS'))

jdaarevalo

CREATE EXTENSION pgcrypto;
--select gen_random_uuid ();

...

-- Filling of sales  

INSERT INTO sale;
select gen_random_uuid ()
	, round(CAST(float8 (random() * 10000) as numeric), 3)

jdaarevalo

...
-- Filling of products
INSERT INTO product
select id, concat('Product ', id) 
FROM GENERATE_SERIES(1, current_setting('my.number_of_products')::int) as id;
...

...
-- Filling of cities
INSERT INTO city