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