📊 Database Profiling System

Comprehensive metadata extraction for Text-to-SQL applications

🚀 Quick Start

from ryoma_data import DataSource, DatabaseProfiler

# Create datasource
datasource = DataSource(
    "postgres",
    host="localhost",
    database="mydb",
    user="user",
    password="pass"
)

# Create profiler
profiler = DatabaseProfiler()

# Profile table
profile = profiler.profile_table(datasource, "customers")
print(f"Rows: {profile.row_count}")
print(f"Completeness: {profile.completeness_score}")

🎯 Core Features

📊 Feature	💡 Use Case
Row counts & NULL stats	Data completeness
Distinct-value ratios	Cardinality analysis
Statistical measures	Numeric profiling
String analysis	Text data insights
Top-k frequent values	Common patterns
LSH similarity	Column matching

🔧 API Methods

# Table profiling
profile = profiler.profile_table(datasource, "customers")

# Column analysis
column_profile = profiler.profile_column(datasource, "customers", "email")

# Configure profiler
profiler = DatabaseProfiler(
    sample_size=10000,
    top_k=20,
    enable_lsh=True
)

📊 Example Results

📋 Table Profile Example

{
  "table_profile": {
    "row_count": 150000,
    "completeness_score": 0.95,
    "consistency_score": 0.88
  },
  "profiling_summary": {
    "profiling_method": "ibis_enhanced",
    "total_columns": 12
  }
}

📋 Column Profile Example

{
  "email": {
    "semantic_type": "email",
    "data_quality_score": 0.92,
    "null_percentage": 5.2,
    "distinct_ratio": 0.98,
    "top_k_values": [
      {"value": "user@example.com", "count": 15, "percentage": 0.01}
    ]
  }
}

🎯 Advanced Features

🏷️ Semantic Type Detection

🔍 Type	🎯 Use Case
📧 Email	Contact analysis
📞 Phone	Communication data
🌐 URL	Web analytics
🆔 ID	Primary key detection

📊 Data Quality Formula

quality_score = (
    completeness * 0.5 +      # 1 - NULL%
    uniqueness * 0.3 +        # Distinct ratio
    reliability * 0.2         # Sample size
)

🔗 Column Similarity

# Find similar columns
similar = datasource.find_similar_columns("customer_id", threshold=0.8)
# → ["user_id", "client_id", "account_id"]

⚙️ Configuration

🚀 Quick Setup

from ryoma_data import DataSource, DatabaseProfiler

datasource = DataSource(
    "postgres",
    connection_string="postgresql://..."
)

profiler = DatabaseProfiler()

🎛️ Tuning Options

🎯 Use Case	📊 Sample	🔝 Top-K	🔗 LSH
🚀 Development	1K	5	Off
⚖️ Production	10K	10	On
🎯 High Accuracy	50K	20	On

🔧 Custom Configuration

from ryoma_data import DatabaseProfiler

profiler = DatabaseProfiler(
    sample_size=10000,
    top_k=10,
    enable_lsh=True
)

💻 Usage Examples

🚀 Basic Profiling

from ryoma_data import DataSource, DatabaseProfiler

datasource = DataSource(
    "postgres",
    connection_string="postgresql://user:pass@host:5432/db"
)

# Profile table
profiler = DatabaseProfiler()
profile = profiler.profile_table(datasource, "customers")
method = profile["profiling_summary"]["profiling_method"]
print(f"Method: {method}")

🔍 Advanced Analysis

# Column profiling
email_profile = datasource.profile_column("customers", "email")
if email_profile["semantic_type"] == "email":
    quality = email_profile["data_quality_score"]
    print(f"Email quality: {quality:.2f}")

# Enhanced catalog
catalog = datasource.get_enhanced_catalog(include_profiles=True)
for schema in catalog.schemas:
    for table in schema.tables:
        high_quality = table.get_high_quality_columns(min_quality_score=0.8)
        print(f"{table.table_name}: {len(high_quality)} high-quality columns")

⚡ Direct Ibis Access

# Custom analysis with Ibis
conn = datasource.connect()
ibis_table = conn.table("customers")

stats = ibis_table.describe().to_pandas()
age_mean = ibis_table.age.mean().to_pandas()
top_cities = ibis_table.city.value_counts().limit(5).to_pandas()

🔧 Backend Support

🗄️ Database	🚀 Native Features	🎯 Best For
PostgreSQL	pg_stats, histograms	Production OLTP
BigQuery	ML functions, INFORMATION_SCHEMA	Analytics
DuckDB	Advanced analytics	OLAP workloads
SQLite	PRAGMA optimizations	Development
Snowflake	Cloud-native views	Data warehouse
MySQL	INFORMATION_SCHEMA	Web backends

🤖 Text-to-SQL Benefits

🔗 Enhanced Schema Linking

🎯 Feature	💡 How It Helps
Statistical relevance	Uses row counts for table selection
Semantic types	Detects emails, phones, IDs
Quality scores	Filters low-quality columns
Similarity analysis	Finds related columns

⚡ Smarter Query Generation

# Profiling-informed optimization
if column_profile["distinct_ratio"] > 0.8:
    query += f"GROUP BY {column_name}"  # High cardinality
elif column_profile["null_percentage"] < 5:
    query += f"WHERE {column_name} IS NOT NULL"  # Low nulls

🛡️ Error Prevention

• NULL handling based on actual percentages

• Type safety using semantic types

• Cardinality awareness for optimization

🚀 Production Guide

✅ Deployment Checklist

• Create DatabaseProfiler instance

• Configure sampling for your data size

• Set quality thresholds

• Monitor profiling overhead

• Schedule regular updates

📊 Scaling Guide

📏 Table Size	⚙️ Config	📝 Notes
< 100K rows	Default	Full analysis
100K-1M rows	sample_size=5000	Balanced
> 1M rows	sample_size=10000	Optimized

🔍 Monitoring & Troubleshooting

# Check method used
method = profile["profiling_summary"]["profiling_method"]
if method == "standard":
    print("⚠️ Ibis not used - check compatibility")

# Monitor duration
duration = profile["table_profile"]["profiling_duration_seconds"]
if duration > 10:
    print("⚠️ Consider reducing sample_size")

📚 API Reference

🔧 Core Methods

# Table profiling
profile = datasource.profile_table(table_name, schema=None)

# Column profiling
column_profile = datasource.profile_column(table_name, column_name, schema=None)

# Enhanced catalog
catalog = datasource.get_enhanced_catalog(include_profiles=True)

# Column similarity
similar = datasource.find_similar_columns(column_name, threshold=0.8)

⚙️ Configuration Options

from ryoma_data import DatabaseProfiler

profiler = DatabaseProfiler(
    sample_size=10000,
    top_k=10,
    enable_lsh=True
)

🚨 Troubleshooting

⚠️ Issue	💡 Solution
Slow profiling	Reduce sample_size
Memory errors	Set enable_lsh=False
Permission errors	Check DB user permissions
Type errors	Auto-handled by fallback

🔍 Debug Examples

# Check profiling method
method = profile["profiling_summary"]["profiling_method"]
if method == "standard":
    print("Ibis not used - check compatibility")

# Reduce sample for large tables
profiler = DatabaseProfiler(sample_size=1000)

🔮 Roadmap

🚀 Upcoming Features

• 🤖 ML-based pattern detection - Advanced semantic types

• 🔗 Cross-table relationships - Statistical correlation analysis

• ⏰ Temporal profiling - Data quality tracking over time

• 🎯 Custom semantic types - Domain-specific definitions

---

📖 References

📚 Resource	🔗 Link
Research Paper	“Automatic Metadata Extraction for Text-to-SQL”
Ibis Framework	ibis-project.org
MinHash/LSH	Locality-Sensitive Hashing algorithms
Statistical Methods	Database profiling techniques