# app.py — Unified Data Catalog (Streamlit, Hugging Face friendly) # Features: # - Upload CSV/XLSX (header-only mode supported) # - Auto glossary: type, null%, PII flags, examples # - Role-based PII masking (Marketing/CS vs Data Steward) # - Manual key tagging + scored relationship inference (name/type/overlap) # - Exports: CSV/JSON/Markdown for glossary; key_hints.json; relationships.csv # - Optional DuckDB persistence # - LLM Enrichment (OpenAI): embeddings -> clustering -> cluster naming -> interactive graph (pyvis) import os import json import re from typing import Dict, List import pandas as pd import numpy as np import streamlit as st # ---------------- Optional deps (graceful fallbacks) ---------------- HAS_DUCKDB = False try: import duckdb HAS_DUCKDB = True except Exception: HAS_DUCKDB = False HAS_RAPIDFUZZ = False try: from rapidfuzz import fuzz HAS_RAPIDFUZZ = True except Exception: HAS_RAPIDFUZZ = False HAS_SKLEARN = False try: from sklearn.decomposition import PCA from sklearn.cluster import KMeans HAS_SKLEARN = True except Exception: HAS_SKLEARN = False HAS_PYVIS = False try: from pyvis.network import Network import streamlit.components.v1 as components import networkx as nx HAS_PYVIS = True except Exception: HAS_PYVIS = False # OpenAI (new SDK) HAS_OPENAI = False try: from openai import OpenAI HAS_OPENAI = True except Exception: HAS_OPENAI = False # ---------------- App config ---------------- st.set_page_config(page_title="Unified Data Catalog — MVP", layout="wide") EMAIL_RE = r'[^@\s]+@[^@\s]+\.[^@\s]+' PHONE_RE_BR = r'^\+?\d{10,14}$' CPF_RE = r'^\d{11}$' KNOWN_ID_HINTS = { "user_id","customer_id","client_id","id","contact_id", "order_id","order","email","cpf","phone","document" } # ====================== Core helpers ====================== def infer_simple_type(s: pd.Series) -> str: dt = str(s.dtype) if dt.startswith(("int","Int")): return "integer" if dt.startswith(("float","Float")): return "float" if dt == "bool": return "boolean" try: pd.to_datetime(s.dropna().head(20), errors="raise", utc=True) return "datetime" except Exception: return "string" def detect_pii_values(vals: List[str]): flags = {"email": False, "phone": False, "cpf": False} for v in vals: if v is None: continue sv = str(v).strip() if re.search(EMAIL_RE, sv, flags=re.I): flags["email"] = True if re.match(PHONE_RE_BR, sv): flags["phone"] = True if re.match(CPF_RE, sv): flags["cpf"] = True return flags def suggest_description(col: str) -> str: c = (col or "").lower() if "email" in c: return "Customer email address." if "phone" in c or "telefone" in c: return "Customer phone number." if "document" in c or "cpf" in c: return "Government ID (CPF)." if "name" in c: return "Customer or receiver name." if "order" in c: return "Order identifier or attribute." if "sku" in c: return "Product SKU attribute." if "utm" in c: return "Marketing attribution parameter (UTM)." if "payment" in c: return "Payment-related field." if "status" in c: return "Operational status." if "date" in c or "created" in c: return "Datetime attribute." if "city" in c or "uf" in c or "country" in c or "postal" in c: return "Geographic attribute." return "Business attribute extracted from CSV." def profile_dataframe(table_name: str, df: pd.DataFrame, headers_only: bool) -> pd.DataFrame: cols = [] for col in df.columns: series = df[col] desc = suggest_description(col) if headers_only: inferred = "string" distinct = None null_pct = None examples = [] pii_flags = { "email": "email" in col.lower(), "phone": "phone" in col.lower() or "telefone" in col.lower(), "cpf": "cpf" in col.lower() or "document" in col.lower() } else: inferred = infer_simple_type(series) distinct = int(series.nunique(dropna=True)) null_pct = float(series.isna().mean() * 100.0) examples = series.dropna().astype(str).head(3).tolist() pii_flags = detect_pii_values(series.dropna().astype(str).head(50).tolist()) pii = any(pii_flags.values()) cols.append({ "table": table_name, "column": col, "inferred_type": inferred, "distinct_count": distinct, "null_pct": None if null_pct is None else round(null_pct, 2), "pii": pii, "pii_email": bool(pii_flags.get("email")), "pii_phone": bool(pii_flags.get("phone")), "pii_cpf": bool(pii_flags.get("cpf")), "examples": examples, "description_suggestion": desc }) return pd.DataFrame(cols) # ================= Relationships (scored) ================= def _type_bucket(s: pd.Series) -> str: dt = str(s.dtype) if dt.startswith(("int","Int")): return "int" if dt.startswith(("float","Float")): return "float" if dt == "bool": return "bool" try: pd.to_datetime(s.dropna().head(10), errors="raise") return "date" except Exception: return "str" def _name_similarity(a: str, b: str) -> float: a, b = a.lower(), b.lower() if HAS_RAPIDFUZZ: return max(fuzz.token_sort_ratio(a,b), fuzz.partial_ratio(a,b))/100.0 # simple fallback return 1.0 if a == b else (min(len(a), len(b))/max(len(a), len(b)) if a in b or b in a else 0.0) def _uniqueness(series: pd.Series) -> float: s = series.dropna() if len(s)==0: return 0.0 return s.nunique()/len(s) def infer_relationships_scored( tables: Dict[str, pd.DataFrame], headers_only: bool, hints: Dict[str, list], overlap_sample:int=5000, base_thresh:float=0.55 ) -> pd.DataFrame: if headers_only: return pd.DataFrame() rows = [] names = list(tables.keys()) for i in range(len(names)): for j in range(i+1, len(names)): t1, t2 = names[i], names[j] df1, df2 = tables[t1], tables[t2] # candidate columns: manual hints first, then heuristics cand1 = hints.get(t1) or [c for c in df1.columns if any(k in c.lower() for k in ["id","order","email","cpf","user","customer"])] cand2 = hints.get(t2) or [c for c in df2.columns if any(k in c.lower() for k in ["id","order","email","cpf","user","customer"])] cand1 = cand1 or list(df1.columns[:5]) cand2 = cand2 or list(df2.columns[:5]) for c1 in cand1: s1 = df1[c1] type1 = _type_bucket(s1) uniq1 = _uniqueness(s1) vals1 = set(s1.dropna().astype(str).unique()[:overlap_sample]) if not vals1: continue for c2 in cand2: s2 = df2[c2] type2 = _type_bucket(s2) uniq2 = _uniqueness(s2) vals2 = set(s2.dropna().astype(str).unique()[:overlap_sample]) if not vals2: continue name_sim = _name_similarity(c1, c2) # 0..1 type_match = 1.0 if (type1==type2 or {"int","float"}=={type1,type2}) else 0.0 inter = len(vals1 & vals2) denom = max(1, min(len(vals1), len(vals2))) value_overlap = inter/denom # 0..1 score = 0.40*name_sim + 0.30*type_match + 0.30*value_overlap # bonus for likely keys null1 = float(s1.isna().mean()); null2 = float(s2.isna().mean()) if uniq1>0.7 and uniq2>0.7 and null1<0.3 and null2<0.3: score += 0.05 if score >= base_thresh: rows.append({ "left_table": t1, "left_key": c1, "right_table": t2, "right_key": c2, "name_similarity": round(name_sim,3), "type_match": int(type_match), "value_overlap": round(value_overlap,3), "left_uniqueness": round(uniq1,3), "right_uniqueness": round(uniq2,3), "score": round(min(score,1.0),3), "relationship": "potential join" }) df = pd.DataFrame(rows).sort_values("score", ascending=False) return df def keyword_search(glossary_df: pd.DataFrame, query: str, top_k: int = 12) -> pd.DataFrame: if glossary_df is None or glossary_df.empty or not query.strip(): return pd.DataFrame() q = query.lower() mask = (glossary_df["column"].str.lower().str.contains(q)) | ( glossary_df["description_suggestion"].str.lower().str.contains(q) ) df = glossary_df[mask].copy() if df.empty: tokens = [t for t in q.split() if len(t) > 2] def hit(row): text = f"{row['table']} {row['column']} {row['description_suggestion']}".lower() return any(tok in text for tok in tokens) df = glossary_df[glossary_df.apply(hit, axis=1)] return df.head(top_k) def export_markdown(glossary_df: pd.DataFrame) -> str: lines = ["# Unified Data Dictionary", ""] for t in sorted(glossary_df["table"].unique()): lines.append(f"## {t}") sub = glossary_df[glossary_df["table"] == t] lines.append("| Column | Type | Null % | PII | Description | Examples |") lines.append("|---|---:|---:|:---:|---|---|") for _, r in sub.iterrows(): ex = ", ".join([str(x) for x in (r.get("examples") or [])]) lines.append(f"| {r['column']} | {r['inferred_type']} | {'' if r['null_pct'] is None else r['null_pct']} | {'✅' if r['pii'] else '—'} | {r['description_suggestion']} | {ex} |") lines.append("") return "\n".join(lines) def save_to_duckdb(glossary_df: pd.DataFrame, db_path: str = "catalog.duckdb"): if not HAS_DUCKDB: st.warning("DuckDB not installed. Skipping persistence.") return con = duckdb.connect(db_path) con.execute("CREATE SCHEMA IF NOT EXISTS catalog") con.execute("CREATE TABLE IF NOT EXISTS catalog.glossary AS SELECT * FROM glossary_df LIMIT 0") con.register("glossary_df", glossary_df) con.execute("DELETE FROM catalog.glossary") con.execute("INSERT INTO catalog.glossary SELECT * FROM glossary_df") con.close() # ====================== UI: Header, Sidebar ====================== st.title("Unified Data Catalog — CSV/Excel MVP") st.caption("Upload → Glossary → Relationships → Q&A → (optional) LLM Enrichment & Graph") with st.sidebar: st.header("1) Upload files") uploaded = st.file_uploader("Upload CSV/Excel files", type=["csv","xlsx","xls"], accept_multiple_files=True) st.header("2) Options") headers_only = st.toggle("Header-only mode (no data profiling)", value=False) role = st.selectbox("Viewer role (PII masking)", ["Marketing/CS (masked)", "Data Steward (full)"]) persist = st.toggle("Persist glossary to DuckDB", value=False, help="Saves to catalog.duckdb (requires duckdb)") st.header("3) LLM Enrichment") use_llm = st.toggle("Enrich with LLM (clusters + graph)", value=False) openai_key = st.text_input("OPENAI_API_KEY", type="password") if use_llm else None k_groups = st.slider("Number of semantic groups", 4, 12, 6) if use_llm else None tables: Dict[str, pd.DataFrame] = {} if uploaded: for file in uploaded: name = file.name if name.lower().endswith(".csv"): df = pd.read_csv(file) if not headers_only else pd.read_csv(file, nrows=0) tables[name.replace(".csv","")] = df else: # Excel (first sheet) xls = pd.ExcelFile(file) first = xls.sheet_names[0] if xls.sheet_names else None if first: df = pd.read_excel(xls, sheet_name=first) if not headers_only else pd.read_excel(xls, sheet_name=first, nrows=0) tables[name.rsplit(".",1)[0]] = df if not tables: st.info("Upload at least one CSV/Excel to begin.") st.stop() # ====================== Manual key tagging ====================== if "key_hints" not in st.session_state: st.session_state.key_hints = {} st.subheader("Key Fields (optional manual tagging)") for tname, df in tables.items(): cols = list(df.columns) default = [c for c in cols if c.lower() in {"id","order","order_id","email","cpf","user_id","customer_id"}] picked = st.multiselect( f"Select likely key columns in **{tname}**", cols, default=default, key=f"keys_{tname}" ) st.session_state.key_hints[tname] = picked st.download_button( "Download key hints (JSON)", json.dumps(st.session_state.key_hints, indent=2).encode("utf-8"), file_name="key_hints.json" ) # ====================== Detected tables ====================== st.subheader("Detected Tables") tabs = st.tabs([t for t in tables.keys()]) for i, tname in enumerate(tables.keys()): with tabs[i]: df = tables[tname] st.write(f"**{tname}** — rows: {len(df):,} (headers only: {headers_only}), columns: {len(df.columns)}") st.dataframe(df.head(10) if not headers_only else pd.DataFrame(columns=df.columns)) # ====================== Glossary ====================== st.subheader("Unified Glossary") profiles = [] for t, df in tables.items(): tmp = df if not headers_only else pd.DataFrame(columns=df.columns) prof = profile_dataframe(t, tmp, headers_only=headers_only) profiles.append(prof) glossary_df = pd.concat(profiles, ignore_index=True) # Role-based masking of examples if role.startswith("Marketing"): def mask_examples(lst): out = [] for v in lst or []: if isinstance(v, str) and (re.search(EMAIL_RE, v) or re.match(PHONE_RE_BR, v) or re.match(CPF_RE, v)): if len(v) <= 4: out.append("*"*len(v)) else: out.append(v[:2] + "*"*(len(v)-4) + v[-2:]) else: out.append(v) return out glossary_df["examples"] = glossary_df["examples"].apply(mask_examples) left, right = st.columns([3,1]) with left: st.dataframe(glossary_df, use_container_width=True, height=420) with right: st.metric("Tables", len(tables)) st.metric("Total fields", len(glossary_df)) st.metric("PII fields", int(glossary_df["pii"].sum())) # Downloads st.download_button("Download glossary (CSV)", glossary_df.to_csv(index=False).encode("utf-8"), file_name="glossary.csv") st.download_button("Download glossary (JSON)", glossary_df.to_json(orient="records").encode("utf-8"), file_name="glossary.json") md = export_markdown(glossary_df) st.download_button("Export data dictionary (Markdown)", md.encode("utf-8"), file_name="data_dictionary.md") # Persistence if persist: save_to_duckdb(glossary_df, db_path="catalog.duckdb") st.success("Glossary saved to DuckDB (catalog.duckdb).") # ====================== Relationships ====================== st.subheader("Potential Relationships (scored)") rels = infer_relationships_scored( tables, headers_only=headers_only, hints=st.session_state.key_hints, base_thresh=0.55 ) if rels.empty: st.info("No strong relationships detected (or disabled in header-only mode).") else: st.dataframe(rels, use_container_width=True, height=320) st.download_button("Download relationships (CSV)", rels.to_csv(index=False).encode("utf-8"), file_name="relationships.csv") # ====================== Q&A ====================== st.subheader("Ask the Catalog") q = st.text_input("Type a keyword or short question (e.g., 'delivery', 'utm campaign', 'order id')") if st.button("Search"): res = keyword_search(glossary_df, q, top_k=12) if res.empty: st.warning("No matches found. Try different keywords.") else: st.write(f"Top matches for: **{q}**") st.dataframe(res[["table","column","inferred_type","null_pct","pii","description_suggestion","examples"]]) # ====================== LLM Enrichment (optional) ====================== def build_text_repr(row): ex = ", ".join([str(x) for x in (row.get("examples") or [])]) return f"{row['table']}.{row['column']} | {row['inferred_type']} | PII={row['pii']} | {row['description_suggestion']} | ex: {ex}" def get_embeddings_openai(texts, api_key): # Keep batches small to avoid timeouts in Spaces client = OpenAI(api_key=api_key) vectors = [] for t in texts: resp = client.embeddings.create(model="text-embedding-3-small", input=t) vectors.append(resp.data[0].embedding) return np.array(vectors, dtype=np.float32) def cluster_columns(embeddings, k:int=6): if not HAS_SKLEARN: raise RuntimeError("scikit-learn not installed") X = PCA(n_components=min(50, embeddings.shape[1])).fit_transform(embeddings) km = KMeans(n_clusters=k, n_init="auto", random_state=42) labels = km.fit_predict(X) return labels def label_cluster_via_llm(api_key, cluster_samples:list): client = OpenAI(api_key=api_key) prompt = f""" You are a data catalog taxonomist. Name and describe this group of fields in 3-8 words (business-oriented), then list 3 bullets of what analysts can do with it. Fields: {chr(10).join(cluster_samples[:12])} Return JSON with keys: name, description, bullets. """ resp = client.chat.completions.create( model="gpt-4o-mini", messages=[{"role":"user","content":prompt}], temperature=0.2, response_format={"type":"json_object"}, max_tokens=250 ) return json.loads(resp.choices[0].message.content) def build_graph(glossary_df, rels_df, cluster_labels, cluster_meta): if not HAS_PYVIS: raise RuntimeError("pyvis/networkx not installed") G = nx.Graph() # cluster nodes for cid, name in cluster_labels.items(): G.add_node(f"cluster::{cid}", label=name, color="#6C5CE7", shape="ellipse") # table nodes for t in sorted(glossary_df["table"].unique()): G.add_node(f"table::{t}", label=t, color="#0984E3", shape="box") # column nodes (can be heavy for very wide tables; keep for now) for _, r in glossary_df.iterrows(): cid = int(r["cluster_id"]) node_id = f"col::{r['table']}.{r['column']}" G.add_node(node_id, label=r["column"], color="#74B9FF", shape="dot") G.add_edge(f"table::{r['table']}", node_id, weight=1) G.add_edge(f"cluster::{cid}", node_id, weight=1) # relationship edges between tables for _, e in rels_df.iterrows(): lt = e["left_table"]; rt = e["right_table"] w = float(e.get("score", 0.6)) G.add_edge(f"table::{lt}", f"table::{rt}", weight=5*w, color="#00B894") return G def render_pyvis(G, height="680px"): net = Network(height=height, width="100%", notebook=False, cdn_resources="in_line") net.from_nx(G) net.toggle_physics(True) html = net.generate_html() components.html(html, height=700, scrolling=True) if use_llm: # Dependency/key checks if not HAS_OPENAI: st.warning("LLM enrichment unavailable: OpenAI SDK not installed.") elif not openai_key: st.info("Enter OPENAI_API_KEY in the sidebar to enable enrichment.") elif not HAS_SKLEARN: st.warning("LLM enrichment unavailable: scikit-learn not installed.") else: st.subheader("Semantic Groups (LLM-enriched)") try: # 1) texts & embeddings texts = [build_text_repr(r) for _, r in glossary_df.iterrows()] with st.spinner("Computing embeddings..."): embs = get_embeddings_openai(texts, openai_key) # 2) clustering with st.spinner("Clustering columns..."): labels = cluster_columns(embs, k=k_groups or 6) glossary_df["cluster_id"] = labels # 3) label clusters cluster_labels, cluster_meta = {}, {} for cid in sorted(set(labels)): samples = [texts[i] for i in np.where(labels==cid)[0][:15]] try: meta = label_cluster_via_llm(openai_key, samples) cluster_labels[cid] = meta.get("name") or f"Group {cid}" cluster_meta[cid] = meta except Exception: cluster_labels[cid] = f"Group {cid}" cluster_meta[cid] = {"description":"", "bullets":[]} # show groups for cid in sorted(cluster_labels.keys()): sub = glossary_df[glossary_df["cluster_id"]==cid] st.markdown(f"**{cluster_labels[cid]}** — {len(sub)} fields") if cluster_meta[cid].get("description"): st.caption(cluster_meta[cid]["description"]) cols_preview = ", ".join([f"{r['table']}.{r['column']}" for _, r in sub.head(8).iterrows()]) if cols_preview: st.write(cols_preview) # 4) graph st.subheader("Visual Map — Data Ecosystem") if not rels.empty and HAS_PYVIS: G = build_graph(glossary_df, rels, cluster_labels, cluster_meta) render_pyvis(G) elif not HAS_PYVIS: st.info("Install pyvis & networkx to render the interactive graph.") else: st.info("No relationships available to render between tables.") # 5) downloads payload = { "cluster_labels": cluster_labels, "cluster_meta": cluster_meta, "columns": glossary_df[["table","column","cluster_id"]].to_dict(orient="records") } st.download_button( "Download semantic groups (JSON)", json.dumps(payload, indent=2).encode("utf-8"), file_name="semantic_groups.json" ) except Exception as e: st.error(f"LLM enrichment failed: {e}")