def threat_log_engine(input_threat_log):
if check_create_bookmark(input_threat_log) == "log":
log = DailyThreatLogEngine(input_threat_log,
config.COUNTRY_DB_FILEPATH,
utils.get_db_engine(), config.SPARK)
log.run()
set_bookmark(input_threat_log, "complete")
def traffic_log_engine(input_traffic_log):
if check_create_bookmark(input_traffic_log) == "log":
log = DailyTrafficLogEngine(input_traffic_log,
config.COUNTRY_DB_FILEPATH,
utils.get_db_engine(), config.SPARK)
log.run()
set_bookmark(input_traffic_log, "rule")
def is_threat_log_already_processed(input_threat_log):
processed_logs_from_db = pd.read_sql_table(
'fh_prd_thrt_log_f',
utils.get_db_engine()).set_index("log_name").to_dict()["id"]
if input_threat_log in processed_logs_from_db:
return True
return False
def main():
engine = utils.get_db_engine()
today = utils.get_current_time()
resp = utils.get_uri_content(uri=const.DARK_SKY_URI, content_type='json')
for key in resp.keys():
if isinstance(resp.get(key), dict) and 'data' in resp.get(key):
for n, i in enumerate(resp.get(key)['data']):
resp.get(key)['data'][n]['currentTime'] = today
resp['currently']['lat'] = resp['latitude']
resp['currently']['lng'] = resp['longitude']
current_df = pd.DataFrame([resp['currently']])
daily_df = pd.DataFrame(resp['daily']['data'])
hourly_df = pd.DataFrame(resp['hourly']['data'])
minutely_df = pd.DataFrame(resp['minutely']['data'])
tables = [
'current_weather', 'daily_weather', 'hourly_weather',
'minutely_weather'
]
data_to_import = [current_df, daily_df, hourly_df, minutely_df]
for data, table in zip(data_to_import, tables):
data.to_sql(table, con=engine, if_exists='append', index=False)
def traffic_chart_engine(input_traffic_log):
if check_create_bookmark(input_traffic_log) == "chart":
chart = DailyChartEngine(input_traffic_log,
spark=config.SPARK,
db_engine=utils.get_db_engine())
chart.run()
set_bookmark(input_traffic_log, "complete")
def main():
engine = utils.get_db_engine()
results = engine.execute(
'select * from results order by dateof desc limit 1').fetchone()
prediction = results.prediction
if prediction >= .6:
utils.send_email('Take Action', 'Preemptive action may be necessary')
def set_bookmark(input_log, bookmark):
engine = utils.get_db_engine()
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
rs = session.execute(
f"UPDATE fh_bookmark set bookmark = '{bookmark}' where log_name='{input_log}'"
)
session.commit()
session.close()
def get_database_and_connection(self, local_base_name: str):
database_obj = self.execute_database_action(
BasesTable, "first", local_name=local_base_name
)
return database_obj, get_db_engine(
database_obj.type,
ip=database_obj.ip,
port=database_obj.port,
username=database_obj.username,
database=database_obj.database,
password=database_obj.password,
)
def ready_for_staging():
engine = utils.get_db_engine()
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
try:
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_app_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_dst_ip_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_new_src_ip_dy_a")
rs = session.execute(
"TRUNCATE TABLE fh_stg_trfc_mis_req_frm_blip_dy_a")
rs = session.execute(
"TRUNCATE TABLE fh_stg_trfc_mis_req_frm_blip_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_mis_res_to_blip_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_f")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_hr_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_log_dtl_dy_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_rule_f")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_app_dt_hr_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_con_dt_hr_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_ip_dt_hr_a")
rs = session.execute("TRUNCATE TABLE fh_stg_trfc_chrt_tm_srs_dt_hr_a")
rs = session.execute("TRUNCATE TABLE fh_stg_thrt_log_dtl_f")
rs = session.execute("TRUNCATE TABLE fh_stg_thrt_log_dtl_evnt_f")
rs = session.execute("TRUNCATE TABLE fh_stg_tt_anmly_f")
print("TRUNCATE STAGE TABLE COMPLETED")
logger.info(f'truncate stage table completed')
except:
session.rollback()
raise
finally:
session.commit()
session.close()
pass
def check_create_bookmark(input_log):
engine = utils.get_db_engine()
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
rs = session.execute(
f"SELECT log_name from fh_bookmark where log_name = '{input_log}'")
if (rs.fetchone() is None):
rs = session.execute(
f"INSERT INTO fh_bookmark (datetime, log_name, bookmark) VALUES ('{datetime.datetime.now()}','{input_log}','none')"
)
session.commit()
session.close()
return "none"
else:
rs = session.execute(
f"SELECT bookmark from fh_bookmark where log_name = '{input_log}'")
return (rs.fetchone()[0])
session.commit()
session.close()
def main():
engine = utils.get_db_engine()
today = utils.get_current_time()
resp = utils.get_uri_content(uri=const.POLLEN_URI,
headers=const.POLLEN_HEADERS,
content_type='json')
allergens = resp['Location']['periods']
for n, row in enumerate(allergens):
allergens[n]['Triggers'] = ', '.join(
[i['Name'] for i in allergens[n]['Triggers']])
allergens[n]['dateof'] = today
allergens_df = pd.DataFrame(allergens)
allergens_df.to_sql('allergens',
con=engine,
if_exists='append',
index=False)
def main():
engine = utils.get_db_engine()
today = utils.get_current_time()
soup = utils.get_uri_content(uri=const.AIRNOW_URI, content_type='html')
aqi_table = soup.find_all('table', {'width': '65%'})[0]
rows = []
for table_row in aqi_table.find_all('tr'):
pollutant = table_row.find_all(
'td', {'class': 'AQDataPollDetails'})[0].text.strip()
value = table_row.find_all('td', {'height': '27'})[0].text.strip()
row = {'pollutant': pollutant, 'value': value, 'dateof': today}
rows.append(row)
pollutant_df = pd.DataFrame(rows)
pollutant_df.to_sql('pollutants',
con=engine,
if_exists='append',
index=False)
def test_insert_into_table():
engine = utils.get_db_engine()
Temp_table.__table__.create(bind=engine, checkfirst=True)
data = main.read_data_from_csv(source="test/vehicle.csv0001_part_00")
long, short = main.split_into_long_and_normal_lists(data_list=data,
mater=list(),
vehicles=list())
test_short = list()
for row in short:
test_short.append(row[:3])
test_short.append(test_short[6])
main.insert_into_table(data_list=test_short,
table=Temp_table,
engine=engine)
length = engine.execute("SELECT COUNT(*) FROM test_table").scalar()
Temp_table.__table__.drop(bind=engine)
assert length == 7
def add_database(
self, base_type, description, local_name=None, **con_params
) -> str:
if not database_health_check(get_db_engine(base_type, **con_params)):
raise ConnectionError("Can't connect to database")
if not local_name:
local_name = "_".join(
[
con_params["ip"],
con_params["port"],
con_params["database"],
con_params["user"],
]
)
if self._is_db_exists(local_name):
raise DatabaseAlreadyExistsError(local_name)
new_database = BasesTable(
type=base_type, description=description, local_name=local_name, **con_params
)
self.db_session.add(new_database)
self._commit()
return local_name
def threat_mis_engine(input_threat_log):
if (check_create_bookmark(input_threat_log) == "none"):
mis = DailyThreatMISEngine(config.SPARK, utils.get_db_engine(),
input_threat_log)
mis.run()
set_bookmark(input_threat_log, "log")
def traffic_mis_engine(input_traffic_log):
if check_create_bookmark(input_traffic_log) == "none":
mis = DailyTrafficMISEngine(config.SPARK, utils.get_db_engine(),
input_traffic_log)
mis.run()
set_bookmark(input_traffic_log, "log")
def commit_changes_to_production():
engine = utils.get_db_engine()
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
try:
# traffic log mis
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_mis_dy_a',
'fh_prd_trfc_mis_dy_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_mis_new_app_dy_a',
'fh_prd_trfc_mis_new_app_dy_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_mis_new_dst_ip_dy_a',
'fh_prd_trfc_mis_new_dst_ip_dy_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_mis_new_src_ip_dy_a',
'fh_prd_trfc_mis_new_src_ip_dy_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_mis_req_frm_blip_dy_a',
'fh_prd_trfc_mis_req_frm_blip_dy_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_mis_res_to_blip_dy_a',
'fh_prd_trfc_mis_res_to_blip_dy_a')
# traffic log log
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_f',
'fh_prd_trfc_log_dtl_f')
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_hr_a',
'fh_prd_trfc_log_dtl_hr_a')
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_log_dtl_dy_a',
'fh_prd_trfc_log_dtl_dy_a')
# traffic log rule
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_rule_f',
'fh_prd_trfc_rule_f')
# traffic log chart
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_chrt_app_dt_hr_a',
'fh_prd_trfc_chrt_app_dt_hr_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_chrt_con_dt_hr_a',
'fh_prd_trfc_chrt_con_dt_hr_a')
insert_stage_data_to_prod_table(session, 'fh_stg_trfc_chrt_ip_dt_hr_a',
'fh_prd_trfc_chrt_ip_dt_hr_a')
insert_stage_data_to_prod_table(session,
'fh_stg_trfc_chrt_tm_srs_dt_hr_a',
'fh_prd_trfc_chrt_tm_srs_dt_hr_a')
# traffic log trouble ticket
# threat log log
insert_stage_data_to_prod_table(session, 'fh_stg_thrt_log_dtl_f',
'fh_prd_thrt_log_dtl_f')
insert_stage_data_to_prod_table(session, 'fh_stg_thrt_log_dtl_evnt_f',
'fh_prd_thrt_log_dtl_evnt_f')
insert_stage_data_to_prod_table(session, 'fh_stg_tt_anmly_f',
'fh_prd_tt_anmly_f')
logger.info(f'stage data written to prod table completed')
except:
session.rollback()
raise
finally:
session.commit()
session.close()
def traffic_rule_engine(input_traffic_log):
if check_create_bookmark(input_traffic_log) == "rule":
rule = DailyTrafficRuleEngine(input_traffic_log, utils.get_db_engine(),
config.SPARK)
rule.run()
set_bookmark(input_traffic_log, "chart")
def test_get_db_engine_works():
engine = utils.get_db_engine()
r = engine.execute("SHOW DATABASES;").scalar()
assert r == "ACT"
finally:
print(
f"All update done. Result can be found in table {pistoncup.__table__.name}."
)
session.close()
if __name__ == '__main__':
# Download, extract and save the data files to disk
utils.mkdir(directory=constants.DATA_FOLDER)
for file in constants.FILES:
download_and_save_gzip_from_url(source=file,
destination=constants.DATA_FOLDER)
# initialize objects to talk to database
engine = utils.get_db_engine()
if not database.is_initialized(engine):
database.initialize_database(engine)
if not check_db_filled(engine):
vehicle_table = database.Vehicle
mater_table = database.Mater
# Read csv files into lists
mater: List[List[str]] = list()
vehicles: List[List[str]] = list()
for file in os.listdir(constants.DATA_FOLDER):
file_vehicle_list = read_data_from_csv(
source=os.path.join(constants.DATA_FOLDER, file))
mater, vehicles = split_into_long_and_normal_lists(
file_vehicle_list, mater, vehicles)
def traffic_log_engine(input_traffic_log):
log = DailyTrafficLogEngine(input_traffic_log,
config.TRAFFIC_LOGS_OUTPUT_DIR,
config.COUNTRY_DB_FILEPATH,
utils.get_db_engine(), config.SPARK)
log.run()
