Download platform/harness/amazon_ads_store.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 44.9 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_ads_store.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/harness/amazon_ads_store.py
-
curl -L -o amazon_ads_store.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_ads_store.py
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()) | |