def parse_aemo_url_optimized_bulk(
url: str,
table_set: AEMOTableSet | None = None,
persist_to_db: bool = True) -> ControllerReturn | AEMOTableSet:
"""Optimized version of aemo url parser that stores the files locally in tmp
and parses them individually to resolve memory pressure"""
d = download_and_unzip(url)
cr = ControllerReturn()
onlyfiles = [Path(d) / f for f in os.listdir(d) if (Path(d) / f).is_file()]
logger.debug(f"Got {len(onlyfiles)} files")
ts = AEMOTableSet()
for f in onlyfiles:
logger.info(f"parsing {f}")
if f.suffix.lower() not in [".csv"]:
continue
ts = parse_aemo_file(str(f), table_set=ts)
if not persist_to_db:
return ts
controller_returns = store_aemo_tableset(ts)
cr.inserted_records += controller_returns.inserted_records
if cr.last_modified and controller_returns.last_modified and cr.last_modified < controller_returns.last_modified:
cr.last_modified = controller_returns.last_modified
return cr
def crawl_apvi_forecasts(crawler: CrawlerDefinition,
last_crawled: bool = True,
limit: bool = False,
latest: bool = False) -> ControllerReturn:
"""Runs the APVI crawl definition"""
apvi_return = ControllerReturn()
if crawler.latest:
apvi_forecast_return = run_apvi_crawl()
return apvi_forecast_return
# run the entire date range
else:
for date in date_series(get_today_nem().date(),
length=crawler.limit,
reverse=True):
apvi_forecast_return = run_apvi_crawl(date)
apvi_return.processed_records += apvi_forecast_return.processed_records
apvi_return.total_records += apvi_forecast_return.total_records
apvi_return.inserted_records += apvi_forecast_return.inserted_records
apvi_return.errors += apvi_forecast_return.errors
if not apvi_return.server_latest or apvi_return.server_latest < apvi_forecast_return.server_latest:
apvi_return.server_latest = apvi_forecast_return.server_latest
return apvi_return
def process_nem_price(table: AEMOTableSchema) -> ControllerReturn:
"""Stores the NEM price for both dispatch price and trading price"""
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
price_field = "price"
if table.full_name == "dispatch_price":
price_field = "price_dispatch"
for record in table.records:
# @NOTE disable pk track
trading_interval = parse_date(record["settlementdate"])
primary_key = set([trading_interval,
record["regionid"]]) # type: ignore
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controllers.nem",
"network_region": record["regionid"],
"trading_interval": trading_interval,
price_field: record["rrp"],
})
cr.processed_records += 1
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={price_field: getattr(stmt.excluded, price_field)},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting NEM price records")
logger.error(e)
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def process_dispatch_interconnectorres(
table: AEMOTableSchema) -> ControllerReturn:
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
for record in table.records:
primary_key = set(
[record["settlementdate"], record["interconnectorid"]])
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controller",
"facility_code": record["interconnectorid"],
"trading_interval": record["settlementdate"],
"generated": record["mwflow"],
})
cr.processed_records += 1
# insert
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval", "network_id", "facility_code", "is_forecast"
],
set_={"generated": stmt.excluded.generated},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
cr.errors = cr.processed_records
return cr
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def process_meter_data_gen_duid(table: AEMOTableSchema) -> ControllerReturn:
cr = ControllerReturn(total_records=len(table.records))
records = generate_facility_scada(
table.records,
interval_field="interval_datetime",
facility_code_field="duid",
power_field="mwh_reading",
)
cr.processed_records = len(records)
cr.inserted_records = bulkinsert_mms_items(FacilityScada, records,
["generated"])
cr.server_latest = max([i["trading_interval"] for i in records])
return cr
def process_unit_solution(table: AEMOTableSchema) -> ControllerReturn:
cr = ControllerReturn(total_records=len(table.records))
records = generate_facility_scada(
table.records,
interval_field="settlementdate",
facility_code_field="duid",
power_field="initialmw",
)
cr.processed_records = len(records)
cr.inserted_records = bulkinsert_mms_items(FacilityScada, records,
["generated"])
cr.server_latest = max(
[i["trading_interval"] for i in records if i["trading_interval"]])
return cr
def process_unit_scada_optimized(table: AEMOTableSchema) -> ControllerReturn:
cr = ControllerReturn(total_records=len(table.records))
records = generate_facility_scada(
table.records, # type:ignore
interval_field="settlementdate",
facility_code_field="duid",
power_field="scadavalue",
)
cr.processed_records = len(records)
cr.inserted_records = bulkinsert_mms_items(
FacilityScada, records, ["generated", "eoi_quantity"]) # type: ignore
cr.server_latest = max(
[i["trading_interval"] for i in records if i["trading_interval"]])
return cr
def store_apvi_forecastset(forecast_set: APVIForecastSet) -> ControllerReturn:
"""Persist an APVI forecast set to the database"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not forecast_set.intervals:
return cr
cr.total_records = len(forecast_set.intervals)
for _rec in forecast_set.intervals:
records_to_store.append(
{**_rec.dict(exclude={"state"}), "created_by": "apvi.controller", "is_forecast": False}
)
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "facility_code", "is_forecast"],
set_={
"generated": stmt.excluded.generated,
"created_by": stmt.excluded.created_by,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def process_rooftop_actual(table: AEMOTableSchema) -> ControllerReturn:
cr = ControllerReturn(total_records=len(table.records))
records = generate_facility_scada(
table.records,
interval_field="interval_datetime",
facility_code_field="regionid",
power_field="power",
network=NetworkAEMORooftop,
)
records = [rooftop_remap_regionids(i) for i in records if i]
records = [i for i in records if i]
cr.processed_records = len(records)
cr.inserted_records = bulkinsert_mms_items(FacilityScada, records,
["generated", "eoi_quantity"])
cr.server_latest = max([i["trading_interval"] for i in records])
return cr
def store_aemo_tableset(tableset: AEMOTableSet) -> ControllerReturn:
if not tableset.tables:
raise Exception("Invalid item - no tables located")
cr = ControllerReturn()
for table in tableset.tables:
if table.full_name not in TABLE_PROCESSOR_MAP:
logger.info("No processor for table %s", table.full_name)
continue
process_meth = TABLE_PROCESSOR_MAP[table.full_name]
if process_meth not in globals():
logger.info("Invalid processing function %s", process_meth)
continue
logger.info("processing table {} with {} records".format(
table.full_name, len(table.records)))
record_item = None
try:
record_item = globals()[process_meth](table)
logger.info(
f"Stored {len(table.records)} records for table {table.full_name}"
)
except Exception as e:
logger.error(f"Error processing {table.full_name}: {e}")
continue
if record_item:
cr.processed_records += record_item.processed_records
cr.total_records += record_item.total_records
cr.inserted_records += record_item.inserted_records
cr.errors += record_item.errors
cr.error_detail += record_item.error_detail
cr.server_latest = record_item.server_latest
return cr
def process_rooftop_forecast(table: AEMOTableSchema) -> ControllerReturn:
cr = ControllerReturn(total_records=len(table.records))
records = generate_facility_scada(
table.records, # type: ignore
interval_field="interval_datetime",
facility_code_field="regionid",
power_field="powermean",
is_forecast=True,
network=NetworkAEMORooftop,
)
records = [rooftop_remap_regionids(i) for i in records
if i] # type: ignore
records = [i for i in records if i]
cr.processed_records = len(records)
cr.inserted_records = bulkinsert_mms_items(FacilityScada, records,
["generated"]) # type: ignore
cr.server_latest = max([i["trading_interval"] for i in records])
return cr
def store_wem_facility_intervals(
balancing_set: WEMFacilityIntervalSet) -> ControllerReturn:
"""Persist WEM facility intervals"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not balancing_set.intervals:
return cr
cr.total_records = len(balancing_set.intervals)
cr.server_latest = balancing_set.server_latest
for _rec in balancing_set.intervals:
records_to_store.append({
"created_by": "wem.controller",
"network_id": "WEM",
"trading_interval": _rec.trading_interval,
"facility_code": _rec.facility_code,
"generated": _rec.generated,
"eoi_quantity": _rec.eoi_quantity,
})
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(FacilityScada).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval", "network_id", "facility_code", "is_forecast"
],
set_={
"generated": stmt.excluded.generated,
"eoi_quantity": stmt.excluded.eoi_quantity,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def store_bom_observation_intervals(
observations: BOMObservationReturn) -> ControllerReturn:
"""Store BOM Observations"""
engine = get_database_engine()
cr = ControllerReturn(total_records=len(observations.observations))
latest_forecast: Optional[datetime] = max([
o.observation_time for o in observations.observations
if o.observation_time
])
if latest_forecast:
latest_forecast = latest_forecast.astimezone(
ZoneInfo("Australia/Sydney"))
logger.debug("server_latest is {}".format(latest_forecast))
cr.server_latest = latest_forecast
records_to_store = []
for obs in observations.observations:
records_to_store.append({
"station_id": observations.station_code,
"observation_time": obs.observation_time,
"temp_apparent": obs.apparent_t,
"temp_air": obs.air_temp,
"press_qnh": obs.press_qnh,
"wind_dir": obs.wind_dir,
"wind_spd": obs.wind_spd_kmh,
"wind_gust": obs.gust_kmh,
"cloud": obs.cloud,
"cloud_type": obs.cloud_type,
"humidity": obs.rel_hum,
})
cr.processed_records += 1
if not len(records_to_store):
return cr
stmt = insert(BomObservation).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["observation_time", "station_id"],
set_={
"temp_apparent": stmt.excluded.temp_apparent,
"temp_air": stmt.excluded.temp_air,
"press_qnh": stmt.excluded.press_qnh,
"wind_dir": stmt.excluded.wind_dir,
"wind_spd": stmt.excluded.wind_spd,
"wind_gust": stmt.excluded.wind_gust,
"cloud": stmt.excluded.cloud,
"cloud_type": stmt.excluded.cloud_type,
"humidity": stmt.excluded.humidity,
},
)
with get_scoped_session() as session:
try:
session.execute(stmt)
session.commit()
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = cr.processed_records
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
cr.inserted_records = cr.processed_records
return cr
def store_wem_balancingsummary_set(
balancing_set: WEMBalancingSummarySet) -> ControllerReturn:
"""Persist wem balancing set to the database"""
engine = get_database_engine()
session = get_scoped_session()
cr = ControllerReturn()
records_to_store = []
if not balancing_set.intervals:
return cr
cr.total_records = len(balancing_set.intervals)
cr.server_latest = balancing_set.server_latest
for _rec in balancing_set.intervals:
records_to_store.append({
"created_by": "wem.controller",
"trading_interval": _rec.trading_day_interval,
"network_id": "WEM",
"network_region": "WEM",
"is_forecast": _rec.is_forecast,
"forecast_load": _rec.forecast_mw,
"generation_total": _rec.actual_total_generation,
"generation_scheduled": _rec.actual_nsg_mw,
"price": _rec.price,
})
cr.processed_records += 1
if len(records_to_store) < 1:
return cr
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=[
"trading_interval",
"network_id",
"network_region",
],
set_={
"price": stmt.excluded.price,
"forecast_load": stmt.excluded.forecast_load,
"generation_total": stmt.excluded.generation_total,
"is_forecast": stmt.excluded.is_forecast,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = len(records_to_store)
except Exception as e:
logger.error("Error: {}".format(e))
cr.errors = len(records_to_store)
cr.error_detail.append(str(e))
finally:
session.close()
engine.dispose()
return cr
def process_trading_regionsum(table: AEMOTableSchema) -> ControllerReturn:
engine = get_database_engine()
if not table.records:
logger.debug(table)
raise Exception("Invalid table no records")
cr = ControllerReturn(total_records=len(table.records))
limit = None
records_to_store = []
records_processed = 0
primary_keys = []
for record in table.records:
if not isinstance(record, dict):
raise Exception("Invalid record type")
trading_interval = parse_date(
record["settlementdate"],
network=NetworkNEM,
dayfirst=False,
date_format="%Y/%m/%d %H:%M:%S",
)
if not trading_interval:
continue
_pk = set([trading_interval, record["regionid"]])
if _pk in primary_keys:
continue
primary_keys.append(_pk)
net_interchange = None
if "netinterchange" in record:
net_interchange = clean_float(record["netinterchange"])
records_to_store.append({
"network_id": "NEM",
"created_by": "opennem.controller.nem",
"network_region": record["regionid"],
"net_interchange_trading": net_interchange,
"trading_interval": trading_interval,
})
records_processed += 1
if limit and records_processed >= limit:
logger.info("Reached limit of: {} {}".format(
limit, records_processed))
break
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={
"net_interchange_trading": stmt.excluded.net_interchange_trading,
},
)
session = get_scoped_session()
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
records_to_store = []
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr
def process_dispatch_regionsum(table: AEMOTableSchema) -> ControllerReturn:
session = get_scoped_session()
engine = get_database_engine()
cr = ControllerReturn(total_records=len(table.records))
records_to_store = []
primary_keys = []
for record in table.records:
if not isinstance(record, dict):
continue
trading_interval = parse_date(record.get("settlementdate"))
primary_key = set([trading_interval, record["regionid"]])
if primary_key in primary_keys:
continue
primary_keys.append(primary_key)
if "demand_and_nonschedgen" not in record:
raise Exception("bad value in dispatch_regionsum")
records_to_store.append({
"network_id":
"NEM",
"created_by":
"opennem.controller",
"network_region":
record["regionid"],
"trading_interval":
trading_interval,
"net_interchange":
record["netinterchange"],
"demand":
record["totaldemand"],
"demand_total":
record["demand_and_nonschedgen"],
})
cr.processed_records += 1
stmt = insert(BalancingSummary).values(records_to_store)
stmt.bind = engine
stmt = stmt.on_conflict_do_update(
index_elements=["trading_interval", "network_id", "network_region"],
set_={
"net_interchange": stmt.excluded.net_interchange,
"demand_total": stmt.excluded.demand_total,
"demand": stmt.excluded.demand,
},
)
try:
session.execute(stmt)
session.commit()
cr.inserted_records = cr.processed_records
cr.server_latest = max(
[i["trading_interval"] for i in records_to_store])
except Exception as e:
logger.error("Error inserting records")
logger.error(e)
cr.errors = cr.processed_records
finally:
session.rollback()
session.close()
engine.dispose()
return cr