loopable / platform /harness /amazon_ads_store.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
3dbbea8 verified
Raw History Blame Contribute Delete
44.9 kB
"""amazon_ads_store.py: the Amazon Ads loader into the Amazon tenant mirror (S79-T12, contract C6).
python platform/harness/amazon_ads_store.py --sync --tenant royal-imports
python platform/harness/amazon_ads_store.py --status --tenant royal-imports
One table per ad type and report, `amzn_ads_<sp|sb|sd>_<report>`, spawned as `ut_amzn_ads_*`.
⛔ HISTORY IS KEPT FROM THE CONNECT DAY. The first sync stamps the connect day once and reaches
back the longest window Amazon documents per table (`connector_spec.AMAZON_ADS_BACKFILL_DAYS`).
A later sync re-reads the table's daily or intraday cadence window and upserts by row identity.
No sync deletes a day an earlier sync stored, so a day Amazon has aged out stays here.
⛔ STANDING RULE 1. Every report is read whole, every file of it. The limits that cannot be removed
(one report at a time per client ID, about 100 a day, a report v1 does not offer) are written to `_sync_state` as
`amzn_limit_ads_<table>` with the reason a person reads, and stated on the table description.
Receipts use the Amazon mirror's markers: `amzn_sync_mark_ads_<table>` is the newest landed day,
`amzn_sync_floor_ads_<table>` the oldest, `amzn_ads_<table>` the table state.
"""
import argparse
import hashlib
import importlib
import json
import logging
import sys
import tempfile
import threading
import time
import uuid
from datetime import date, datetime, timedelta, timezone
from pathlib import Path
_HERE = Path(__file__).resolve().parent
if str(_HERE.parent) not in sys.path:
sys.path.insert(0, str(_HERE.parent))
from core import connector_spec # noqa: E402
from harness import amazon_client # noqa: E402
from harness import amazon_ads_client as client # noqa: E402
from harness import amazon_ads_schema as schema # noqa: E402
from harness import amazon_store # noqa: E402
from harness import datastore # noqa: E402
#: C6: called as `fn(tenant_key, synced_tables)` after a sync lands rows. Imported lazily; a
#: failure is one log line, never a failed sync.
ADS_AFTER_SYNC = ("modules.amazon_operator:on_ads_sync",)
TABLES = tuple(schema.TABLE_SPECS)
#: Keep the exported default compatible. Requests use each table's own cadence.
RESTATEMENT_DAYS = amazon_client.CADENCE["ads_sp_campaigns"]["lookbackDays"]
CONNECT_DAY_KEY = amazon_store.entity_key("ads_connect_day")
BUDGET_KEY = amazon_store.entity_key("ads_report_budget")
INSERT_BATCH_ROWS = 500
ORIGIN_COLUMNS = ("_connection_id", "store")
#: One `_sync_state` entity per (connection, table) holds a report Amazon is still preparing.
PENDING_PREFIX = "ads_pending_"
PREPARING_REASON = "Amazon is still preparing this report. The next sync reads it."
TIME_LIMIT_REASON = "This sync reached its time limit. The next sync continues."
#: A partial pass asks the caller to run the next pass after this many seconds.
CONTINUE_AFTER_SECONDS = 600.0
#: The pass clock for `max_seconds`. A gate patches it to stand in for Amazon's preparing time.
_clock = time.monotonic
def table_name(table):
"""`sp_campaigns` -> `amzn_ads_sp_campaigns`, the mirror table. One place, so a rename lands whole."""
stream = schema.stream_spec(table)
if stream is not None:
return stream["source"]
return schema.TABLE_SPECS[schema.short_name(table)]["source"]
def _marker_table(table):
"""The `<table>` the Amazon mirror markers name: `ads_sp_campaigns`."""
return table_name(table)[len(amazon_store.PREFIX):]
def limit_key(table):
return amazon_store.entity_key("limit_" + _marker_table(table))
def table_description(table, con=None):
"""The description a person reads, plus any limit the last sync recorded. Never raises."""
stream = schema.stream_spec(table)
if stream is not None:
return stream["description"]
try:
key = schema.TABLE_SPECS[schema.short_name(table)]["key"]
except KeyError:
return None
text = connector_spec.amazon_ads_description(key) or ""
if con is None:
return text
try:
state = amazon_store._read_state(con, limit_key(table))
reason = str(json.loads(state["detail"]).get("reason") or "") if state and state.get("detail") else ""
except Exception: # noqa: BLE001
reason = ""
return f"{text} {reason}" if reason and reason not in text else text
def _utc_today(now=None):
now = now or datetime.now(timezone.utc)
return now.date() if isinstance(now, datetime) else now
def _utc_now(now=None):
"""Normalize datetime and date callers to the same UTC cadence clock."""
now = now or datetime.now(timezone.utc)
if not isinstance(now, datetime):
now = datetime(now.year, now.month, now.day, tzinfo=timezone.utc)
return now.replace(tzinfo=timezone.utc) if now.tzinfo is None else now.astimezone(timezone.utc)
def _state_key(key, connection_id=None):
"""Keep the public table markers and give each connection its own continuation markers."""
return f"{key}::connection::{connection_id}" if connection_id else key
def _cadence_due(con, table, now, connection_id=None):
"""Only this connection's completed pass can delay its next cadence interval."""
cadence = amazon_client.CADENCE["ads_" + table]
tick = amazon_store._read_state(con, _state_key(
amazon_store.entity_key("sync_tick_" + _marker_table(table)), connection_id))
if not tick or tick.get("state") != "done":
return True
try:
stamp = amazon_store._utc(tick.get("detail") or tick["updated_at"])
except (ValueError, TypeError, AttributeError, OverflowError):
return True
return now >= stamp + timedelta(days=1) / cadence["perDay"]
def _intraday(con, table, today, connection_id=None):
"""Retry the daily lookback until every request for this connection succeeds."""
day = amazon_store._read_state(con, _state_key(
amazon_store.entity_key("lookback_day_" + _marker_table(table)), connection_id))
return bool(day and day.get("state") == "done" and day.get("detail") == today.isoformat())
def _record_cadence(con, table, now, connection_id=None):
"""Persist successful empty and nonempty passes with their daily lookback completion."""
tick = _state_key(amazon_store.entity_key("sync_tick_" + _marker_table(table)), connection_id)
stamp = now.isoformat()
amazon_store._record_state(con, tick, "done", detail=stamp)
con.execute("UPDATE _sync_state SET updated_at = ? WHERE entity = ?", [stamp, tick])
amazon_store._record_state(con, _state_key(
amazon_store.entity_key("lookback_day_" + _marker_table(table)), connection_id),
"done", detail=now.date().isoformat())
def _connect_day(con, today, connection_id=None, *, legacy=False):
"""The connect day, stamped once on the first sync and never moved."""
key = _state_key(CONNECT_DAY_KEY, connection_id)
state = amazon_store._read_state(con, key)
inherited = False
if state is None and legacy and connection_id:
state = amazon_store._read_state(con, CONNECT_DAY_KEY)
inherited = state is not None
if state and state.get("detail"):
try:
day = date.fromisoformat(str(state["detail"])[:10])
if inherited:
amazon_store._record_state(con, key, "done", detail=day.isoformat())
return day
except ValueError:
pass
amazon_store._record_state(con, key, "done", detail=today.isoformat())
return today
def _day_marker(con, key):
state = amazon_store._read_state(con, key)
try:
return date.fromisoformat(str(state["detail"])[:10]) if state and state.get("detail") else None
except ValueError:
return None
def windows(con, table, connect_day, today, connection_id=None):
"""The report windows one sync asks for, oldest first, each at most 120 days (`client.MAX_DAYS_PER_REQUEST`)."""
table = schema.short_name(table)
spec = schema.TABLE_SPECS[table]
cadence = amazon_client.CADENCE["ads_" + table]
lookback = cadence["intradayLookbackDays" if _intraday(con, table, today, connection_id) else "lookbackDays"]
last = today - timedelta(days=1)
target_floor = max(connect_day, today) - timedelta(days=spec["backfill_days"])
mark_name = _marker_table(table)
newest = _day_marker(con, _state_key(amazon_store.entity_key(amazon_store.WATERMARK_PREFIX + mark_name), connection_id))
oldest = _day_marker(con, _state_key(amazon_store.entity_key(amazon_store.BACKFILL_FLOOR_PREFIX + mark_name), connection_id))
spans = []
if newest is None or oldest is None:
spans.append((target_floor, last))
else:
if oldest > target_floor:
spans.append((target_floor, oldest - timedelta(days=1)))
spans.append((max(target_floor, min(newest, last - timedelta(days=lookback - 1))), last))
out = []
for start, end in spans:
cursor = start
while cursor <= end:
stop = min(cursor + timedelta(days=client.MAX_DAYS_PER_REQUEST - 1), end)
out.append((cursor, stop))
cursor = stop + timedelta(days=1)
return out
def _columns_for(table):
if schema.stream_spec(table) is not None:
return schema.stream_spec(table)["columns"]
return tuple(dict.fromkeys(schema.columns_for(table) + schema.ORIGIN_COLUMNS + ORIGIN_COLUMNS))
def _ensure_table(con, table):
if schema.stream_spec(table) is not None:
return _ensure_stream_tables(con)
name = table_name(table)
cols = _columns_for(table)
have = {r[0] for r in con.execute(
"SELECT column_name FROM information_schema.columns WHERE table_name = ?", [name]).fetchall()}
if not have:
con.execute(f'CREATE TABLE IF NOT EXISTS "{name}" ('
+ ", ".join(f'"{c}" {schema.coltype(c) if not c.startswith("_") else "VARCHAR"}'
for c in cols) + ")")
return
for c in cols:
if c not in have:
con.execute(f'ALTER TABLE "{name}" ADD COLUMN "{c}" '
f'{schema.coltype(c) if not c.startswith("_") else "VARCHAR"}')
def _upsert(con, table, rows):
"""Delete-then-insert by `_row_key` only. ⛔ Never by window: a day absent from a re-read stays."""
if not rows:
return 0
name = table_name(table)
cols = _columns_for(table)
by_key = {r["_row_key"]: r for r in rows}
keys = list(by_key)
one = f'({", ".join("?" for _ in cols)})'
for i in range(0, len(keys), INSERT_BATCH_ROWS):
part = keys[i:i + INSERT_BATCH_ROWS]
con.execute(f'DELETE FROM "{name}" WHERE "_row_key" IN ({",".join("?" for _ in part)})', part)
# One multi-row INSERT per batch: DuckDB's executemany is about 5 times slower at 24 months a pass.
con.execute(f'INSERT INTO "{name}" ({", ".join(chr(34) + c + chr(34) for c in cols)}) '
f'VALUES {", ".join(one for _ in part)}', [by_key[k].get(c) for k in part for c in cols])
return len(by_key)
def _budget(con, today, connection_id=None, *, legacy=False):
state = amazon_store._read_state(con, _state_key(BUDGET_KEY, connection_id))
if state is None and legacy and connection_id:
state = amazon_store._read_state(con, BUDGET_KEY)
try:
detail = json.loads(state["detail"]) if state and state.get("detail") else {}
except (ValueError, TypeError):
detail = {}
return int(detail.get("used") or 0) if detail.get("day") == today.isoformat() else 0
def _spend_budget(con, today, used, connection_id=None):
amazon_store._record_state(con, _state_key(BUDGET_KEY, connection_id), "done", rows=used,
detail=json.dumps({"day": today.isoformat(), "used": used}))
def _record_limit(con, table, reason, connection_id=None, **extra):
amazon_store._record_state(con, _state_key(limit_key(table), connection_id), "done",
detail=json.dumps({"reason": reason, **extra}))
def _clear_limit(con, table, connection_id=None):
con.execute("DELETE FROM _sync_state WHERE entity = ?", [_state_key(limit_key(table), connection_id)])
def _count(con, table, connection_id=None):
where = ' WHERE "_connection_id" = ?' if connection_id is not None else ''
return con.execute(f'SELECT count(*) FROM "{table_name(table)}"' + where,
[connection_id] if connection_id is not None else []).fetchone()[0]
def _runtime_for(key):
try:
from harness import runtime
return runtime.get_runtime(key)
except Exception:
return None
def _connections(rt, key, profile_id, report):
"""Read named business credentials without changing process state or the client's resolver."""
from core import keychain
if rt is None and client.fixture_mode():
return [("", "Amazon Ads", profile_id, True)]
try:
entries = sorted((row for row in keychain.list_entries(rt) if row.get("type") == "amazon_ads"),
key=lambda row: (int(row.get("slot") or 1), str(row["id"])))
default = keychain.resolve_connection_id(rt, "amazon_ads")
except Exception:
report["problems"].append("The Amazon Ads connections could not be read. Retry this sync.")
return []
out = []
for row in entries:
cid = str(row.get("connection_id") or row["id"])
label = str(row.get("label") or cid)
try:
fields = keychain.amazon_ads_creds(rt, connection_id=cid)
except Exception:
report["problems"].append(f"{label}: the Amazon Ads credential could not be read. Test this connection again.")
continue
if fields is None:
continue # The public resolver excludes personal credentials.
if not all(fields.get(name) for name in client.CREDENTIAL_FIELDS):
report["problems"].append(f"{label}: the Amazon Ads credential is incomplete. Check its four fields.")
continue
out.append((cid, label, str(fields["profile_id"]), cid == default))
return out
def _row_key(connection_id, key):
if not connection_id:
return key
payload = json.dumps([connection_id, key], separators=(",", ":")).encode("utf-8")
return hashlib.sha256(payload).hexdigest()
def _refresh_origin(con, table, connection_id, label, legacy):
"""Migrate the former default's rows once and refresh labels across retained history."""
name = table_name(table)
changed = con.execute(f'SELECT count(*) FROM "{name}" WHERE '
'("_connection_id" = ? AND store IS DISTINCT FROM ?) '
'OR ("_connection_id" IS NULL AND ?)', [connection_id, label, legacy]).fetchone()[0]
if legacy:
rows = con.execute(f'SELECT "_row_key" FROM "{name}" WHERE "_connection_id" IS NULL').fetchall()
for (key,) in rows:
con.execute(f'UPDATE "{name}" SET "_row_key" = ?, "_connection_id" = ?, store = ? '
'WHERE "_row_key" = ? AND "_connection_id" IS NULL',
[_row_key(connection_id, key), connection_id, label, key])
con.execute(f'UPDATE "{name}" SET store = ? WHERE "_connection_id" = ?', [label, connection_id])
return changed
def _pending_key(table, connection_id=None):
return _state_key(amazon_store.entity_key(PENDING_PREFIX + _marker_table(table)), connection_id)
def _read_pending(con, table, connection_id=None):
"""The report this connection left preparing for `table`, or None."""
state = amazon_store._read_state(con, _pending_key(table, connection_id))
if not state or state.get("state") != "pending" or not state.get("detail"):
return None
try:
entry = json.loads(state["detail"])
return {"report_id": str(entry["report_id"]), "start": date.fromisoformat(entry["start"]),
"end": date.fromisoformat(entry["end"]), "created": amazon_store._utc(entry["created"])}
except (ValueError, TypeError, KeyError, AttributeError, OverflowError):
return None
def _write_pending(con, table, connection_id, report_id, start, end, created):
amazon_store._record_state(con, _pending_key(table, connection_id), "pending", detail=json.dumps(
{"report_id": report_id, "start": start.isoformat(), "end": end.isoformat(),
"created": created.isoformat()}))
def _clear_pending(con, table, connection_id=None):
"""Keep the row with no detail, so a store that merges state rows cannot bring the entry back."""
amazon_store._record_state(con, _pending_key(table, connection_id), "done")
def _land(con, table, rows, report_id, start, end, profile, connection_id, label):
"""Upsert one finished report window and advance this connection's markers. -> rows stored."""
stored = []
for raw in rows:
row = schema.row_from_vendor(table, raw, profile_id=profile, report_id=report_id)
row.update(_connection_id=connection_id, store=label,
_row_key=_row_key(connection_id, row["_row_key"]))
stored.append(row)
pulled = _upsert(con, table, stored)
mark = _marker_table(table)
# Only this connection's landed window advances its continuation.
for prefix, day, later in ((amazon_store.WATERMARK_PREFIX, end, True),
(amazon_store.BACKFILL_FLOOR_PREFIX, start, False)):
public = amazon_store.entity_key(prefix + mark)
for marker in dict.fromkeys((_state_key(public, connection_id), public)):
previous = _day_marker(con, marker)
if previous is None or (day > previous if later else day < previous):
amazon_store._record_state(con, marker, "done", _count(con, table), day.isoformat())
return pulled
def _run_report(key, table, start, end, rt, profile_id, connection_id, **extra):
"""Run the group report that fills `table` under this connection's own credential. -> GroupReport."""
options = {"rt": rt, "profile_id": profile_id, **extra}
if connection_id:
options["connection_id"] = connection_id
return client.run_group_report(key, schema.group_of(table), start, end, **options)
class _Pass:
"""One connection's sync pass: its budget, clock, and what its group reports already covered.
`eligible` holds the tables a group report may land into this pass: requested, offered, due and
not yet finished. `covered` holds each table's spans landed this pass, `pulled` the rows a
sibling's report landed, `outcome` a group's failure, `notes` Amazon's text per table.
"""
def __init__(self, con, key, rt, connection, report, *, today, used, deadline, stamp, checkpointed):
self.con, self.key, self.rt, self.report = con, key, rt, report
self.cid, self.label, self.profile = connection[:3]
self.today, self.used, self.deadline, self.stamp = today, used, deadline, stamp
self.checkpointed = checkpointed
self.eligible, self.covered, self.pulled, self.outcome, self.notes = set(), {}, {}, {}, {}
self.blocked = False
def wait(self, spec, reason):
self.report["waits"].append(f"{self.label}: {spec['label']}: {reason}")
def checkpoint(self, table):
if table_name(table) not in self.checkpointed:
self.checkpointed.append(table_name(table))
def covers(self, table, start, end):
"""True when reports this pass already read every day from `start` to `end` for `table`."""
spans = self.covered.get(table, ())
return all(any(lo <= start + timedelta(days=i) <= hi for lo, hi in spans)
for i in range((end - start).days + 1))
def bounds(self):
"""The poll limit for one new report: none, or the time left in this bounded pass."""
return {} if self.deadline is None else {"max_wait": max(0.0, self.deadline - _clock()), "now": _clock}
def fail(self, table, exc):
"""Record a group report's failure for every member. -> (state, reason) for `table`."""
self.outcome[schema.group_of(table).name] = exc
return self.failure(table, exc)
def failure(self, table, exc):
spec = schema.TABLE_SPECS[table]
if isinstance(exc, client.AdsReportUnavailable):
return "not_offered", _not_offered_reason(spec, exc)
reason = str(exc)[:client.ERROR_TEXT_LIMIT]
self.report["problems"].append(f"{self.label}: {spec['label']}: {reason}")
return "failed", reason
def land(self, table, result, start, end):
"""Land one group report into `table` and each eligible sibling, covering the span. -> rows into `table`."""
own = 0
for member in schema.group_of(table).members:
if member != table and member not in self.eligible:
continue
_ensure_table(self.con, member)
rows = _land(self.con, member, result.rows.get(member, []), result.report_id, start, end,
self.profile, self.cid, self.label)
self.covered.setdefault(member, []).append((start, end))
self.notes[member] = list(dict.fromkeys(self.notes.get(member, []) + list(result.notes)))
self.checkpoint(member)
if member == table:
own = rows
else:
self.pulled[member] = self.pulled.get(member, 0) + rows
for note in result.notes:
if f"{self.label}: {note}" not in self.report["problems"]:
self.report["problems"].append(f"{self.label}: {note}")
return own
def _resume(p):
"""Read every report this connection left preparing before it creates another. -> {table: outcome}."""
resumed = {}
for table in TABLES:
pending = _read_pending(p.con, table, p.cid)
if pending is None:
continue
spec = schema.TABLE_SPECS[table]
_ensure_table(p.con, table)
p.checkpoint(table)
expired = f"Amazon Ads report {pending['report_id']} did not finish in 3 hours"
age = (p.stamp() - pending["created"]).total_seconds()
if age >= client.REPORT_MAX_WAIT_SECONDS:
_clear_pending(p.con, table, p.cid)
resumed[table] = (*p.failure(table, client.AdsError(expired)), 0)
continue
limit = client.REPORT_MAX_WAIT_SECONDS - age
if p.deadline is not None:
limit = min(limit, max(0.0, p.deadline - _clock()))
try:
result = _run_report(p.key, table, pending["start"], pending["end"], p.rt, p.profile, p.cid,
max_wait=limit, report_id=pending["report_id"], now=_clock)
except client.AdsReportPending:
if (p.stamp() - pending["created"]).total_seconds() < client.REPORT_MAX_WAIT_SECONDS:
p.blocked = True
p.wait(spec, PREPARING_REASON)
resumed[table] = ("deferred", PREPARING_REASON, 0)
continue
_clear_pending(p.con, table, p.cid)
resumed[table] = (*p.failure(table, client.AdsError(expired)), 0)
continue
except client.AdsError as exc:
_clear_pending(p.con, table, p.cid)
resumed[table] = (*p.fail(table, exc), 0)
continue
_clear_pending(p.con, table, p.cid)
resumed[table] = ("done", "", p.land(table, result, pending["start"], pending["end"]))
return resumed
def _run_windows(p, table, connect_day):
"""Run the group report for each window of `table` no report covered yet. -> (state, reason, rows)."""
spec = schema.TABLE_SPECS[table]
failed = p.outcome.get(schema.group_of(table).name)
if failed is not None:
return (*p.failure(table, failed), 0)
pulled = 0
for start, end in windows(p.con, table, connect_day, p.today, p.cid):
if p.covers(table, start, end):
continue
if p.used >= client.REPORTS_PER_DAY:
reason = (f"{connector_spec.AMAZON_ADS_THROUGHPUT} Today's {client.REPORTS_PER_DAY} "
f"reports are used, so days from {start.isoformat()} load on the next sync.")
p.report["problems"].append(f"{p.label}: {spec['label']}: waiting for tomorrow's report limit")
p.report["waits"].append(p.report["problems"][-1])
return "deferred", reason, pulled
if p.deadline is not None and _clock() >= p.deadline:
p.wait(spec, TIME_LIMIT_REASON)
return "deferred", TIME_LIMIT_REASON, pulled
p.used += 1
_spend_budget(p.con, p.today, p.used, p.cid)
created = p.stamp()
try:
result = _run_report(p.key, table, start, end, p.rt, p.profile, p.cid, **p.bounds())
except client.AdsReportPending as exc:
if (p.deadline is None or not exc.report_id
or (p.stamp() - created).total_seconds() >= client.REPORT_MAX_WAIT_SECONDS):
return (*p.failure(table, exc), pulled)
_write_pending(p.con, table, p.cid, exc.report_id, start, end, created)
p.checkpoint(table)
p.blocked = True
p.wait(spec, PREPARING_REASON)
return "deferred", PREPARING_REASON, pulled
except client.AdsError as exc:
return (*p.fail(table, exc), pulled)
pulled += p.land(table, result, start, end)
return "done", "", pulled
def sync(tenant_key="royal-imports", *, rt=None, con=None, tables=None, now=None, log=print,
profile_id=None, max_seconds=None):
"""Pull every offered Ads report into the tenant's Amazon mirror. -> a report dict.
When this call opens the connection and has a runtime, it saves landed windows and cadence
through `amazon_store.persist_mirror`. A caller that lends `con` persists itself.
`max_seconds` bounds the pass's wait on Amazon. A report still preparing then waits in the
pending ledger, and the next pass reads it before it creates any other report.
One group report fills every requested member table, so a sibling's covered days spend no report.
"""
key = amazon_store._tenant_key(tenant_key)
report = {"tenant": key, "tables": {}, "problems": [], "limits": {}, "connections": [],
"pendingReports": 0, "partial": False, "continueAfterSeconds": None, "waits": []}
if rt is None and not client.fixture_mode():
rt = _runtime_for(key)
if rt is not None and str(getattr(rt, "key", "") or "").strip().lower() != key:
report["problems"].append("The runtime does not match the requested tenant. No Amazon Ads rows were pulled.")
return report
connections = _connections(rt, key, profile_id, report)
if not connections:
report["problems"].append("No Amazon Ads credential is stored for this tenant. Open Amazon Ads Setup to add a connection.")
return report
own = con is None
if own:
con, _path = amazon_store._connect_tenant_mirror(key, rt)
started = _clock()
deadline = None if max_seconds is None else started + float(max_seconds)
synced, checkpointed = [], []
try:
datastore._ensure_sync_state(con)
now = _utc_now(now)
today = _utc_today(now)
def stamp():
return now + timedelta(seconds=max(0.0, _clock() - started))
requested = [schema.short_name(t) for t in (tables or TABLES)]
for connection in connections:
cid, label, _profile, legacy = connection
connect_day = _connect_day(con, today, cid, legacy=legacy)
report.setdefault("connectDay", connect_day.isoformat())
entry = {"id": cid, "label": label, "connectDay": connect_day.isoformat(), "tables": {}}
report["connections"].append(entry)
p = _Pass(con, key, rt, connection, report, today=today, used=_budget(con, today, cid, legacy=legacy),
deadline=deadline, stamp=stamp, checkpointed=checkpointed)
p.eligible = {t for t in requested if schema.TABLE_SPECS[t]["offered"] and _cadence_due(con, t, now, cid)}
resumed = _resume(p)
for table in list(dict.fromkeys(requested + list(resumed))):
landed = _sync_table(p, table, entry, connect_day, now, legacy, table in requested,
resumed.get(table), log)
p.eligible.discard(table)
if landed and table_name(table) not in synced:
synced.append(table_name(table))
_record_tables(con, report, connections)
if own and rt is not None and (synced or checkpointed):
_persist(key, rt, con, list(dict.fromkeys(synced + checkpointed)), report, log)
finally:
if own:
con.close()
_after_sync(key, synced, log)
return report
_SEVERITY = {"skipped": -1, "done": 0, "not_offered": 1, "deferred": 2, "failed": 3}
def _sync_table(p, table, entry, connect_day, now, legacy, requested, prior, log):
"""One table's turn in one connection's pass: run or skip it, record its state. -> True if it changed."""
con, cid, label, report = p.con, p.cid, p.label, p.report
spec = schema.TABLE_SPECS[table]
_ensure_table(con, table)
origin_changed = _refresh_origin(con, table, cid, label, legacy)
state, reason, pulled = prior or ("done", "", 0)
pulled += p.pulled.pop(table, 0)
if state != "done" or not requested:
pass
elif not spec["offered"]:
state, reason = "not_offered", spec["not_offered_reason"]
elif not _cadence_due(con, table, now, cid):
state = "skipped"
elif p.blocked:
state, reason = "deferred", PREPARING_REASON
p.wait(spec, reason)
else:
state, reason, rows = _run_windows(p, table, connect_day)
pulled += rows
if state == "done":
_record_cadence(con, table, now, cid)
if state == "done" and not reason and p.notes.get(table):
reason = " ".join(p.notes[table])
if state == "deferred" and reason in (PREPARING_REASON, TIME_LIMIT_REASON):
report["partial"] = True
if reason:
_record_limit(con, table, reason, cid, state=state)
shown = f"{label}: {reason}" if cid else reason
report["limits"][table] = " ".join(filter(None, (report["limits"].get(table), shown)))
elif state == "done":
_clear_limit(con, table, cid)
total = _count(con, table)
entry["tables"][table] = {"state": state, "pulled": pulled, "in_mirror": _count(con, table, cid)}
if state != "skipped":
amazon_store._record_state(con, _state_key(amazon_store.entity_key(_marker_table(table)), cid),
"done" if state == "done" else "partial", _count(con, table, cid), state)
previous = report["tables"].get(table, {})
report["tables"][table] = {"state": max((state, previous.get("state", state)), key=_SEVERITY.get),
"pulled": previous.get("pulled", 0) + pulled, "in_mirror": total}
log(f" {table_name(table):<36} pulled {pulled:>7} mirror total {total:>7} {state}")
return bool(pulled or origin_changed)
def _record_tables(con, report, connections):
"""Write each table's aggregate state and limit across connections, and the pass's pending count."""
for table, state in report["tables"].items():
if state["state"] == "skipped":
continue
amazon_store._record_state(con, amazon_store.entity_key(_marker_table(table)),
"done" if state["state"] == "done" else "partial",
state["in_mirror"], state["state"])
if table in report["limits"]:
_record_limit(con, table, report["limits"][table], state=state["state"])
else:
_clear_limit(con, table)
report["pendingReports"] = sum(_read_pending(con, table, cid) is not None
for cid, *_ in connections for table in TABLES)
if report["partial"]:
report["continueAfterSeconds"] = CONTINUE_AFTER_SECONDS
def _not_offered_reason(spec, exc):
return (f"Amazon Reporting v1 does not offer {spec['label']} to this advertiser "
f"({str(exc)[:client.ERROR_TEXT_LIMIT]}). The table keeps the days it has. To ask for it, "
f"contact Amazon Ads support and quote Amazon's message.")
def _persist(tenant_key, rt, con, synced, report, log):
"""Save the Ads tables that landed rows, with their markers. A failure is a problem, never a raise."""
try:
amazon_store.persist_mirror(tenant_key, rt, con, tuple(synced), report=report, log=log)
except Exception as exc: # noqa: BLE001
report["problems"].append(f"The Amazon Ads rows landed here but could not be saved to the "
f"store ({type(exc).__name__}). The next sync saves them again.")
log(report["problems"][-1])
def _after_sync(tenant_key, synced, log):
"""C6: each callback is imported lazily; any failure is one log line and changes nothing."""
if not synced:
return
for target in ADS_AFTER_SYNC:
try:
module_name, _, name = target.partition(":")
getattr(importlib.import_module(module_name), name)(tenant_key, tuple(synced))
except Exception as exc: # noqa: BLE001
log(f"Amazon Ads after sync callback {target} failed ({type(exc).__name__}); "
f"the sync result is unchanged.")
def status(tenant_key="royal-imports", rt=None):
"""Rows per Ads table in the mirror now, None when never synced. Never fetches."""
con, _path = amazon_store._connect_tenant_mirror(tenant_key, rt)
out = {}
try:
for table in TABLES:
try:
out[table_name(table)] = _count(con, table)
except Exception: # noqa: BLE001
out[table_name(table)] = None
finally:
con.close()
return out
STREAM_TABLE = schema.STREAM_TABLE_SPECS["sp_hourly"]["source"]
STREAM_EVENTS = STREAM_TABLE + "_events"
STREAM_STATE_ENTITIES = ("amzn_sync_mark_ads_sp_hourly", "amzn_ads_sp_hourly")
_STREAM_LOCK = threading.Lock()
_STREAM_STORES = {}
def _stream_store(tenant_key, rt):
"""Keep stream checkpoints and locks separate from daily report publications and other tenants."""
from harness.mirror_store import MirrorStore
if getattr(rt, "key", None) != tenant_key:
raise ValueError("The stream runtime belongs to another tenant")
key = (tenant_key, str(Path(rt.datastore_path).resolve()))
with _STREAM_LOCK:
if key not in _STREAM_STORES:
spec = connector_spec.ConnectorMirrorSpec(
tables=(STREAM_EVENTS, STREAM_TABLE), state_prefix="amzn_ads_stream_",
state_entities=STREAM_STATE_ENTITIES)
_STREAM_STORES[key] = MirrorStore("amazon_marketing_stream", spec)
return _STREAM_STORES[key]
def restore_stream(tenant_key, rt):
"""Restore the hourly aggregate and event ledger together before accepting new deltas."""
store = _stream_store(tenant_key, rt)
result = store.restore_mirror(tenant_key, rt, log=lambda *_: None)
if result.get("state") == "refused" or result.get("problems") or result.get("refused_tables"):
raise RuntimeError("The stream checkpoint could not be restored")
return result
def _ensure_stream_tables(con):
datastore._ensure_sync_state(con)
for name, columns in ((STREAM_EVENTS, schema.STREAM_COLUMNS),
(STREAM_TABLE, schema.STREAM_TABLE_SPECS["sp_hourly"]["columns"])):
con.execute(f'CREATE TABLE IF NOT EXISTS "{name}" (' + ", ".join(
f'"{column}" {schema.stream_coltype(column)}' for column in columns) + ")")
con.execute(f'CREATE UNIQUE INDEX IF NOT EXISTS amzn_ads_stream_event_id '
f'ON "{STREAM_EVENTS}" (dataset_id, idempotency_id)')
def _apply_stream_rows(con, rows):
"""Deduplicate immutable deltas before summing them at the complete common traffic and conversion grain."""
columns = schema.STREAM_COLUMNS
for row in rows:
previous = con.execute(f'SELECT * FROM "{STREAM_EVENTS}" WHERE dataset_id=? AND idempotency_id=?',
[row["dataset_id"], row["idempotency_id"]]).fetchone()
if previous:
# A reused identity with changed content is corruption, not an additive correction.
old = dict(zip(columns, previous))
if any(old.get(column) != row.get(column) for column in columns if column != "received_at"):
raise ValueError("The stream event identity was reused with different content")
continue
con.execute(f'INSERT INTO "{STREAM_EVENTS}" VALUES ({",".join("?" for _ in columns)})',
[row.get(column) for column in columns])
grain = schema.STREAM_GRAIN + ("_connection_id",)
# Rebuild only affected grains in SQL, without joining raw traffic to raw conversions.
affected = {tuple(row[column] for column in grain) for row in rows}
predicate = " AND ".join(f'"{column}" IS NOT DISTINCT FROM ?' for column in grain)
for values in affected:
con.execute(f'DELETE FROM "{STREAM_TABLE}" WHERE {predicate}', list(values))
expressions = {column: f'"{column}"' for column in grain}
expressions.update(target_id='MAX(keyword_id)', keyword_text='MAX(keyword_text)', match_type='MAX(match_type)',
_row_key='?', spend='SUM(cost)', sales='SUM(attributed_sales_14d)',
orders='SUM(attributed_conversions_14d)', attribution_window="'14d'",
event_count='COUNT(*)', last_received_at='MAX(received_at)')
expressions.update({name: f'SUM("{name}")' for name in schema.STREAM_METRICS})
key = hashlib.sha256(json.dumps(values, separators=(",", ":")).encode()).hexdigest()
aggregate_columns = schema.STREAM_TABLE_SPECS["sp_hourly"]["columns"]
con.execute(f'INSERT INTO "{STREAM_TABLE}" SELECT ' + ", ".join(expressions[c] for c in aggregate_columns)
+ f' FROM "{STREAM_EVENTS}" WHERE {predicate} GROUP BY '
+ ", ".join(f'"{column}"' for column in grain), [key, *values])
def write_stream_events(tenant_key, rt, binding, rows, *, mirror_store=None):
"""Publish the ledger and aggregate durably before returning an acknowledgement receipt."""
from harness.amazon_marketing_stream import Binding
from harness import mirror_store as mirrors
if (not isinstance(binding, Binding) or tenant_key != binding.tenant or getattr(rt, "key", None) != tenant_key
or not rows or any(row.get("profile_id") != binding.profile_id
or row.get("advertiser_id") != binding.advertiser_id
or row.get("marketplace_id") != binding.marketplace_id
or row.get("_connection_id") != binding.connection_id for row in rows)):
raise ValueError("Stream rows do not belong to the requested runtime and authoritative binding")
store = mirror_store or _stream_store(tenant_key, rt)
with store.lock:
restored = store.restore_mirror(tenant_key, rt, log=lambda *_: None)
if restored.get("state") == "refused" or restored.get("problems") or restored.get("refused_tables"):
raise RuntimeError("Restore the durable stream checkpoint before ingesting events")
published, _path = store._connect(tenant_key, rt)
con, installed = None, False
report = {"problems": []}
try:
# An event batch is a complete incremental source cohort, staged with the shared engine's primitives.
con, started = mirrors._open_candidate(store, tenant_key, rt, published)
_ensure_stream_tables(con)
con.execute("BEGIN TRANSACTION")
try:
_apply_stream_rows(con, rows)
stamp = datetime.now(timezone.utc).isoformat()
activation = amazon_store._read_state(con, "amzn_ads_stream_first_received")
if activation is None:
amazon_store._record_state(con, "amzn_ads_stream_first_received", "done", detail=stamp)
checkpoint = uuid.uuid4().hex
amazon_store._record_state(con, "amzn_ads_stream_checkpoint", "done", detail=checkpoint)
for entity in STREAM_STATE_ENTITIES:
amazon_store._record_state(con, entity, "done", detail=stamp)
con.execute("COMMIT")
except Exception:
con.execute("ROLLBACK")
raise
metadata = {"snapshot_id": checkpoint, "started_at": started, "completed_at": stamp, "state": "complete"}
with tempfile.TemporaryDirectory(prefix="stream-publish-") as tmp:
paths = mirrors._export_tables(store, con, tmp)
manifest = store.persist_mirror(tenant_key, rt, con, (STREAM_EVENTS, STREAM_TABLE),
report=report, log=lambda *_: None, _publication=metadata)
if (report["problems"] or not isinstance(manifest, dict) or manifest.get("tenant") != tenant_key
or manifest.get("snapshot_id") != checkpoint
or not {STREAM_EVENTS, STREAM_TABLE} <= set(manifest.get("tables", {}))
or not manifest.get("sync_state")):
raise RuntimeError("The stream ledger and hourly rows did not reach a durable checkpoint")
state_rows = store._state_rows(con)
try:
store._install_snapshot(published, manifest, state_rows, paths)
installed = True
store._after_publish(tenant_key, manifest)
except BaseException:
store.cache.pop(store._mirror_cache_key(tenant_key, rt), None)
raise
for listener in list(mirrors._PUBLISH_LISTENERS):
try:
listener(store.connector, tenant_key, manifest, state_rows, paths)
notify_state = "ok"
except Exception as exc:
# Durable data remains recoverable when a web handoff needs retry.
notify_state = "failed:" + type(exc).__name__
if getattr(store, "stream_handoff_state", "ok") != notify_state:
logging.getLogger(__name__).log(logging.INFO if notify_state == "ok" else logging.WARNING,
"Stream handoff tenant=%s state=%s", tenant_key, notify_state)
store.stream_handoff_state = notify_state
coverage = con.execute(f'SELECT MIN(time_window_start), MAX(time_window_start), COUNT(*) '
f'FROM "{STREAM_TABLE}" WHERE profile_id=?', [binding.profile_id]).fetchone()
return {"durable": True, "tenant": tenant_key, "profile_id": binding.profile_id,
"checkpoint": checkpoint, "coverage_start": coverage[0], "coverage_end": coverage[1],
"hourly_rows": coverage[2], "first_received_at": (activation or {}).get("detail", stamp)}
finally:
if con is not None:
con.close()
published.close()
if installed:
Path(rt.datastore_path).with_suffix(f".{store.connector}.candidate.duckdb").unlink(missing_ok=True)
def main(argv=None):
ap = argparse.ArgumentParser(description="Pull Amazon Ads reports into the tenant's mirror.")
ap.add_argument("--sync", action="store_true")
ap.add_argument("--status", action="store_true")
ap.add_argument("--tenant", default="royal-imports")
a = ap.parse_args(argv)
if a.status:
for name, rows in status(a.tenant).items():
print(f" {name:<36} {'(never synced)' if rows is None else rows}")
return 0
if not a.sync:
ap.print_help()
return 2
rep = sync(a.tenant)
for problem in rep["problems"]:
print(" PROBLEM:", problem)
return 1 if rep["problems"] else 0
__all__ = ["ADS_AFTER_SYNC", "TABLES", "RESTATEMENT_DAYS", "table_name", "limit_key",
"table_description", "windows", "sync", "status", "write_stream_events", "restore_stream",
"STREAM_TABLE", "STREAM_EVENTS"]
if __name__ == "__main__":
sys.exit(main())