Eric2mangel/DuckDB_database_analyzer
0
1import streamlit as st2import duckdb3import pandas as pd4import plotly.express as px5import plotly.graph_objects as go6import numpy as np7 8# Configuration de la page9st.set_page_config(10 page_title="DuckDB Database Analyzer",11 page_icon="🦆",12 layout="wide",13 initial_sidebar_state="expanded"14)15 16st.title("🦆 DuckDB Database Analyzer")17st.markdown("**Analysez vos bases de données sans les importer !**")18 19# Sidebar20st.sidebar.header("⚙️ Paramètres de connexion")21 22# Gestion du reset23if "reset_counter" not in st.session_state:24 st.session_state.reset_counter = 025if "test_url" not in st.session_state:26 st.session_state.test_url = ""27if "analysis_done" not in st.session_state:28 st.session_state.analysis_done = False29if "analysis_data" not in st.session_state:30 st.session_state.analysis_data = {}31 32# Champ URL avec clé dynamique33url_input = st.sidebar.text_input(34 "📍 URL de la base de données",35 value=st.session_state.test_url,36 placeholder="https://example.com/data.parquet",37 help="Formats supportés : Parquet, CSV, JSON, HTTP, S3, etc.",38 key=f"url_input_{st.session_state.reset_counter}"39)40 41# Bouton Reset42col1, col2 = st.sidebar.columns([4, 1])43with col2:44 if st.sidebar.button("🗑️ Reset"):45 st.session_state.reset_counter += 146 st.session_state.test_url = ""47 st.session_state.analysis_done = False48 st.session_state.analysis_data = {}49 st.rerun()50 51# Options52with st.sidebar.expander("🔧 Options avancées"):53 max_rows_sample = st.slider("Lignes échantillon", 50, 2000, 100)54 55# Bouton d'analyse56if st.sidebar.button("🚀 Analyser la base de données", type="primary"):57 if url_input:58 st.session_state.test_url = ""59 60 with st.spinner("🔍 Analyse en cours..."):61 try:62 con = duckdb.connect()63 con.execute("INSTALL httpfs; LOAD httpfs;")64 65 # Test de lecture66 formats_to_try = [67 ("parquet", f"read_parquet('{url_input}')"),68 ("csv", f"read_csv_auto('{url_input}')"),69 ("json", f"read_json_auto('{url_input}')")70 ]71 72 read_func = ""73 detected_format = ""74 75 for fmt_name, fmt in formats_to_try:76 try:77 result = con.execute(f"SELECT COUNT(*) FROM {fmt}").fetchone()78 if result and result[0] is not None:79 read_func = fmt80 detected_format = fmt_name81 st.success(f"✅ Format détecté : {fmt_name}")82 break83 except:84 continue85 86 if not read_func:87 st.error("❌ Impossible de lire le fichier. Vérifiez l'URL.")88 st.stop()89 90 # Nombre total de lignes91 total_rows = con.execute(f"SELECT COUNT(*) FROM {read_func}").fetchone()[0]92 93 # Nombre de colonnes94 sample_df = con.execute(f"SELECT * FROM {read_func} LIMIT 1").df()95 num_columns = len(sample_df.columns)96 97 # TAILLE FICHIER98 file_size = "N/A"99 try:100 if detected_format == "parquet":101 metadata_result = con.execute(f"""102 SELECT COUNT(*) as row_groups 103 FROM parquet_metadata('{url_input}')104 """).fetchone()105 if metadata_result:106 row_groups = metadata_result[0]107 estimated_mb = row_groups * 4.5108 file_size = f"~{estimated_mb:.0f} MB"109 except:110 pass111 112 # Analyse des variables113 sample_1000 = con.execute(f"SELECT * FROM {read_func} LIMIT 1000").df()114 115 columns_info = []116 for col in sample_1000.columns:117 col_data = sample_1000[col].dropna()118 119 # Détection type120 if len(col_data) == 0:121 col_type = "UNKNOWN"122 detail_type = "VIDE"123 elif pd.api.types.is_integer_dtype(col_data):124 col_type = "INTEGER"125 detail_type = "ENTIER"126 elif pd.api.types.is_float_dtype(col_data):127 col_type = "FLOAT"128 detail_type = "DÉCIMAL"129 elif pd.api.types.is_datetime64_any_dtype(col_data):130 col_type = "DATETIME"131 detail_type = "DATE/HEURE"132 elif pd.api.types.is_bool_dtype(col_data):133 col_type = "BOOLEAN"134 detail_type = "BOOLEEN"135 else:136 col_type = "TEXT"137 try:138 pd.to_numeric(col_data, errors='raise')139 detail_type = "NUMÉRIQUE"140 except:141 detail_type = "TEXTE"142 143 # Taux de remplissage sur l'échantillon144 null_count_sample = sample_1000[col].isna().sum()145 fill_rate = ((1000 - null_count_sample) / 1000 * 100)146 147 example = str(col_data.iloc[0])[:30] if len(col_data) > 0 else "N/A"148 149 columns_info.append({150 'Variable': col,151 'Type': col_type,152 'Type_Détaillé': detail_type,153 'Valeurs_Manquantes': null_count_sample,154 'Taux_Remplissage': round(fill_rate, 1),155 'Exemple': example156 })157 158 columns_df = pd.DataFrame(columns_info)159 160 # Échantillon pour affichage161 sample_display = con.execute(f"SELECT * FROM {read_func} LIMIT {max_rows_sample}").df()162 163 # Sauvegarder les résultats164 st.session_state.analysis_data = {165 'total_rows': total_rows,166 'num_columns': num_columns,167 'file_size': file_size,168 'detected_format': detected_format,169 'columns_df': columns_df,170 'sample_display': sample_display,171 'read_func': read_func,172 'url_input': url_input173 }174 st.session_state.analysis_done = True175 176 con.close()177 st.success("✅ **Analyse terminée avec succès !**")178 st.rerun()179 180 except Exception as e:181 st.error(f"❌ Erreur lors de l'analyse : {str(e)}")182 st.info("💡 Vérifiez que l'URL est accessible et publique")183 else:184 st.warning("⚠️ Veuillez saisir une URL valide")185 186# URLs de test187with st.sidebar.expander("🧪 URLs de test"):188 st.markdown("**URL fonctionnelles pour tester :**")189 190 test_urls = [191 ("SIREN Entreprises France", "https://object.files.data.gouv.fr/data-pipeline-open/siren/stock/StockUniteLegale_utf8.parquet"),192 ("NYC Taxi Oct 2025", "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-10.parquet"),193 ("Open Data Paris Ilôts de fraîcheur", r"https://opendata.paris.fr/api/explore/v2.1/catalog/datasets/ilots-de-fraicheur-equipements-activites/exports/csv?lang=fr&timezone=Europe%2FBerlin&use_labels=true&delimiter=%3B")194 ]195 196 for i, (name, url) in enumerate(test_urls):197 if st.button(f"📊 {name}", key=f"test_{i}", use_container_width=True):198 st.session_state.reset_counter += 1199 st.session_state.test_url = url200 st.rerun()201 202# AFFICHAGE DES RÉSULTATS AVEC ONGLETS203if st.session_state.analysis_done:204 data = st.session_state.analysis_data205 206 tab1, tab2, tab3, tab4 = st.tabs(["📊 Dashboard", "📋 Variables", "💾 Données", "💻 Code"])207 208 # ============================================209 # ONGLET 1: DASHBOARD210 # ============================================211 with tab1:212 # Calcul des métriques pour le rapport de qualité213 avg_fill = data['columns_df']['Taux_Remplissage'].mean()214 missing_cols = len(data['columns_df'][data['columns_df']['Taux_Remplissage'] < 100])215 complete_cols = len(data['columns_df']) - missing_cols216 217 # Style CSS pour les cards218 st.markdown("""219 <style>220 .metric-card {221 background-color: #f0f2f6;222 border-radius: 10px;223 padding: 15px;224 text-align: center;225 box-shadow: 0 2px 4px rgba(0,0,0,0.1);226 height: 100px;227 display: flex;228 flex-direction: column;229 justify-content: center;230 align-items: center;231 }232 .metric-label {233 font-size: 0.85em;234 color: #666;235 margin-bottom: 5px;236 line-height: 1.2;237 min-height: 32px;238 display: flex;239 align-items: center;240 justify-content: center;241 }242 .metric-value {243 font-size: 1.8em;244 font-weight: bold;245 color: #262730;246 }247 </style>248 """, unsafe_allow_html=True)249 250 # Cards en haut - 7 colonnes251 col1, col2, col3, col4, col5, col6, col7 = st.columns(7)252 253 with col1:254 st.markdown(f"""255 <div class="metric-card">256 <div class="metric-label">📊 Observations</div>257 <div class="metric-value">{data['total_rows']:,}</div>258 </div>259 """, unsafe_allow_html=True)260 261 with col2:262 st.markdown(f"""263 <div class="metric-card">264 <div class="metric-label">📋 Colonnes</div>265 <div class="metric-value">{data['num_columns']}</div>266 </div>267 """, unsafe_allow_html=True)268 269 with col3:270 st.markdown(f"""271 <div class="metric-card">272 <div class="metric-label">💾 Taille fichier</div>273 <div class="metric-value">{data['file_size']}</div>274 </div>275 """, unsafe_allow_html=True)276 277 with col4:278 st.markdown(f"""279 <div class="metric-card">280 <div class="metric-label">📄 Format</div>281 <div class="metric-value">{data['detected_format'].upper()}</div>282 </div>283 """, unsafe_allow_html=True)284 285 with col5:286 st.markdown(f"""287 <div class="metric-card">288 <div class="metric-label">✅ Taux moyen</div>289 <div class="metric-value">{avg_fill:.1f}%</div>290 </div>291 """, unsafe_allow_html=True)292 293 with col6:294 st.markdown(f"""295 <div class="metric-card">296 <div class="metric-label">⚠️ Colonnes incomplètes</div>297 <div class="metric-value">{missing_cols}</div>298 </div>299 """, unsafe_allow_html=True)300 301 with col7:302 st.markdown(f"""303 <div class="metric-card">304 <div class="metric-label">✔️ Colonnes complètes</div>305 <div class="metric-value">{complete_cols}</div>306 </div>307 """, unsafe_allow_html=True)308 309 st.markdown("<br>", unsafe_allow_html=True)310 311 # Graphiques côte à côte312 col_left, col_right = st.columns([2, 1])313 314 with col_left:315 # Graphique vertical du taux de remplissage316 fig_fill = px.bar(317 data['columns_df'].sort_values('Taux_Remplissage'), 318 y='Variable', 319 x='Taux_Remplissage',320 title="Taux de remplissage par variable (1000 premières lignes)",321 color='Taux_Remplissage',322 color_continuous_scale='RdYlGn',323 orientation='h',324 range_color=[0, 100],325 height=500326 )327 328 fig_fill.update_layout(329 showlegend=False,330 xaxis_title="Taux de Remplissage (%)",331 yaxis_title="",332 margin=dict(t=50, b=50, l=200, r=20)333 )334 fig_fill.update_traces(marker_line_width=0, marker_cornerradius=5)335 fig_fill.update_yaxes(tickmode='linear')336 337 st.plotly_chart(fig_fill, use_container_width=True)338 339 with col_right:340 # Camembert des types341 type_counts = data['columns_df']['Type_Détaillé'].value_counts()342 fig_pie = px.pie(343 values=type_counts.values,344 names=type_counts.index,345 title="Répartition des types"346 )347 fig_pie.update_traces(textposition='inside', textinfo='percent+label')348 fig_pie.update_layout(height=500)349 st.plotly_chart(fig_pie, use_container_width=True)350 351 # ============================================352 # ONGLET 2: VARIABLES353 # ============================================354 with tab2:355 st.header("📋 Structure des Variables")356 357 # Tableau fusionné358 display_df = data['columns_df'][['Variable', 'Type', 'Type_Détaillé', 'Valeurs_Manquantes', 'Taux_Remplissage', 'Exemple']].copy()359 display_df.columns = ['Variable', 'Type', 'Type Détaillé', 'Valeurs Manquantes (sur 1000)', 'Taux de Remplissage (%)', 'Exemple']360 361 st.dataframe(display_df, use_container_width=True, height=600)362 363 # ============================================364 # ONGLET 3: DONNÉES365 # ============================================366 with tab3:367 st.header("💾 Échantillon des Données")368 369 col1, col2 = st.columns([1, 3])370 with col1:371 st.metric("Lignes affichées", f"{len(data['sample_display']):,}")372 with col2:373 st.caption(f"sur {data['total_rows']:,} total")374 375 st.dataframe(data['sample_display'], use_container_width=True, height=600)376 377 # ============================================378 # ONGLET 4: CODE379 # ============================================380 with tab4:381 st.header("💻 Code Python prêt à l'emploi")382 383 st.code(f"""384import duckdb385 386# Connexion387con = duckdb.connect()388con.execute("INSTALL httpfs; LOAD httpfs;")389 390# Lecture des données391df = con.execute("SELECT * FROM {data['read_func']} LIMIT 1000").df()392print(f"Forme: {{df.shape}}")393print("Colonnes:", df.columns.tolist())394 395# Nombre total de lignes396total_rows = con.execute("SELECT COUNT(*) FROM {data['read_func']}").fetchone()[0]397print(f"Total lignes: {{total_rows:,}}")398 """, language="python")399 400else:401 st.info("👆 Veuillez saisir une URL et cliquer sur **Analyser la base de données** pour commencer l'analyse")