') + ')', 'gi'); if (regex.test(text)) { found = true; var frag = document.createDocumentFragment(); var parts = text.split(regex); parts.forEach(function(part, i) { if (i % 2 === 0) { frag.appendChild(document.createTextNode(part)); } else { var span = document.createElement('span'); span.className = 'userscript-highlight'; span.textContent = part; frag.appendChild(span); } }); node.parentNode.replaceChild(frag, node); } }); } else if (node.nodeType === 1 && node.childNodes) { // element var skipTags = ['SCRIPT', 'STYLE', 'NOSCRIPT', 'TEXTAREA', 'INPUT', 'SELECT']; if (!skipTags.includes(node.tagName)) { Array.from(node.childNodes).forEach(highlight); } } } highlight(document.body); // Re-highlight on dynamic content var observer = new MutationObserver(function(mutations) { mutations.forEach(function(m) { m.addedNodes.forEach(function(node) { if (node.nodeType === 1 || node.nodeType === 3) highlight(node); }); }); }); observer.observe(document.body, { childList: true, subtree: true }); })(); } } catch(__e) { console.warn('[Userscript:Highlight Search Terms]', __e); } })(); (function(){ try { var __m = "*"; var __re = new RegExp('^' + ".*" + ', 'i'); if (__m === '*' || __re.test(location.href)) { // Strip utm_, fbclid, gclid, etc. from all links on page (function() { var trackingParams = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content', 'fbclid', 'gclid', 'dclid', 'msclkid', 'yclid', 'ref', 'ref_src', 'source', 'medium', 'campaign']; function cleanUrl(url) { try { var u = new URL(url, window.location.origin); var changed = false; trackingParams.forEach(function(p) { if (u.searchParams.has(p)) { u.searchParams.delete(p); changed = true; } }); return changed ? u.toString() : url; } catch (e) { return url; } } function cleanLinks() { document.querySelectorAll('a[href]').forEach(function(a) { var clean = cleanUrl(a.href); if (clean !== a.href) a.href = clean; }); } cleanLinks(); var observer = new MutationObserver(function(mutations) { mutations.forEach(function(m) { m.addedNodes.forEach(function(node) { if (node.nodeType === 1) { if (node.tagName === 'A') cleanLinks(); node.querySelectorAll('a[href]').forEach(function(a) { var clean = cleanUrl(a.href); if (clean !== a.href) a.href = clean; }); } }); }); }); observer.observe(document.body, { childList: true, subtree: true }); })(); } } catch(__e) { console.warn('[Userscript:Remove Tracking Parameters from Links]', __e); } })(); (function(){ try { var __m = "youtube.com"; var __re = new RegExp('^' + "youtube\\.com" + ', 'i'); if (__m === '*' || __re.test(location.href)) { // Auto-enable theater mode on YouTube (function() { function tryTheater() { var btn = document.querySelector('button[aria-label="Theater mode"], ytd-player #player button[title="Theater mode"]'); if (btn && !btn.classList.contains('activated')) { btn.click(); } } // Try immediately tryTheater(); // Try after navigation (SPA) var lastUrl = location.href; setInterval(function() { if (location.href !== lastUrl) { lastUrl = location.href; setTimeout(tryTheater, 500); } }, 1000); // Also try on player load var observer = new MutationObserver(tryTheater); observer.observe(document.body, { childList: true, subtree: true }); })(); } } catch(__e) { console.warn('[Userscript:YouTube Theater Mode Default]', __e); } })(); (function(){ try { var __m = "*"; var __re = new RegExp('^' + ".*" + ', 'i'); if (__m === '*' || __re.test(location.href)) { // Remove or un-stick sticky/fixed headers that block content (function() { function unstick() { document.querySelectorAll('header, nav, [role="banner"], .header, .navbar, .sticky, .fixed-top, [style*="position: fixed"], [style*="position:sticky"]').forEach(function(el) { if (el.style.position === 'fixed' || el.style.position === 'sticky' || getComputedStyle(el).position === 'fixed' || getComputedStyle(el).position === 'sticky') { el.style.position = 'static'; el.style.top = 'auto'; el.style.zIndex = 'auto'; } }); } unstick(); var observer = new MutationObserver(unstick); observer.observe(document.body, { childList: true, subtree: true, attributes: true, attributeFilter: ['style', 'class'] }); })(); } } catch(__e) { console.warn('[Userscript:Kill Sticky Headers]', __e); } })(); })(); mailing_list example: scheduled OpenAddresses staging loader on GCP Cloud Run · Issue #47 · SQLAnvil/sqlanvil · GitHub
Skip to content

mailing_list example: scheduled OpenAddresses staging loader on GCP Cloud Run #47

Open
Open
mailing_list example: scheduled OpenAddresses staging loader on GCP Cloud Run#47

Description

@ihistand
ihistand
opened on Jul 4, 2026

Goal

Complete the OpenAddresses story in examples/supabase_bigquery_mailing_list (652bf2f) with a scheduled ingestion path: a Python staging loader deployed on GCP Cloud Run Jobs, fired by Cloud Scheduler, feeding the example's existing type: "import" action.

Architecture (division of labor)

Cloud Scheduler (cron, e.g. weekly)
│
▼
Cloud Run Job: load_openaddresses (Python container)
│ download region/state from OpenAddresses.io → unzip → normalize filename
▼
gs://<staging-bucket>/openaddresses/<region>.csv ← staging only; NO warehouse credentials
│
▼
sqlanvil import action (location: gs://…, format: csv) ← already in the example
│ run on a SQLAnvil Cloud workflow cron (or local `sqlanvil run`)
▼
oa_ext.openaddresses_us → stg_addresses → addresses_cache → mailing_list

Key property: the Python job only stages files — it never holds database credentials. The warehouse load + downstream models + assertions run through the normal sqlanvil workflow with run history.

Deliverables

  • examples/supabase_bigquery_mailing_list/loader/ — load_openaddresses.py (region/state arg, download from OpenAddresses, unzip, upload to GCS), Dockerfile, requirements.txt
  • Deploy runbook in the loader README: gcloud builds submit → gcloud run jobs create → gcloud scheduler jobs create http (job execution via OIDC), incl. required IAM (storage.objectAdmin on the staging bucket, run.invoker for the scheduler SA)
  • Example README: new "Scheduling the refresh" section — loader cron + SQLAnvil Cloud workflow cron pairing, and the storage: credentials entry for gs:// in .df-credentials.json
  • Switch guidance for import.location from the bundled sample CSV to the staged gs:// URI (Cloud-compatible — hosted runs reject local paths)

Notes

  • Deploy pattern mirrors the existing SQLAnvil Cloud runner (Cloud Run Jobs, image via Cloud Build), so no new platform concepts.
  • Keep the loader minimal: stage the standard 11-column CSV as-is; normalization stays in stg_addresses (SQL), not Python.
  • Adapted from a production BigQuery ingestion plan (staged load, clustered target) — here the sqlanvil import + Postgres composite indexes fill those roles.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions