def execute(self):
for i in range(1, 5):
script = ScriptReader.get_script(SCRIPT_PATH[str(i)])
r = RedshiftDataManager.run_query(script, DB_CONNECTION)
bucket_name = "crimeanalysisyk"
file_name = "result" + str(datetime.now()) + ".txt"
lambda_path = "/tmp/" + file_name
s3_path = "output/" + file_name
s3 = boto3.resource("s3")
s3.Bucket(bucket_name).put_object(Key=s3_path,
Body=(bytes(r.encode('UTF-8'))),
ServerSideEncryption='AES256')
return RedshiftDataManager.run_query(script, DB_CONNECTION)
def load(self):
print(SCHEMA_PATH)
schema = ScriptReader.get_script(SCHEMA_PATH)
r = RedshiftDataManager.run_query(schema, DB_CONNECTION)
copy_data = LoadData()
copy_data.loadstates()
copy_data.loadgunviolence()
copy_data.loadhatecrimes()
def loadstates(self):
copy_command = '''copy dbapp.ustates
from 's3://crimeanalysisyk/ustates.csv'
iam_role 'arn:aws:iam::234001707946:role/RedshiftLab'
delimiter ',';'''
#script = ScriptReader.get_script(SCRIPT_PATH)
result = RedshiftDataManager.run_update(copy_command, DB_CONNECTION)
def loadhatecrimes(self):
copy_command = '''copy
dbapp.hatecrimes
from
's3://crimeanalysisyk/hate_crime.csv'
iam_role
'arn:aws:iam::234001707946:role/RedshiftLab'
csv;'''
result = RedshiftDataManager.run_update(copy_command, DB_CONNECTION)
def loadgunviolence(self):
copy_command = '''copy
dbapp.gun_violence
from
's3://crimeanalysisyk/gun-violence.csv'
iam_role
'arn:aws:iam::234001707946:role/RedshiftLab'
delimiter
','
ACCEPTANYDATE
dateformat
'MM/DD/YYYY';'''
result = RedshiftDataManager.run_update(copy_command, DB_CONNECTION)
def redshift_query(event, context, script_path):
logger.info(event)
logger.info(context)
DB_CONNECTION = get_creds()
script = ScriptReader.get_script(script_path)
result = RedshiftDataManager.run_query(script, DB_CONNECTION)
event = {
"ExecutionState": result['ExecutionState'],
"ExecutionMessage": result['ExecutionMessage']
}
return event