Data engineering often feels like shouting into a void of complex query languages, but a recent open-source project is proving that natural language interfaces can sit comfortably atop heavy-duty big data stacks. The project, built by developer 3254988796aaacode, successfully integrates a Text-to-SQL AI assistant into a Hadoop and Spark data warehouse, allowing users to query Brazilian e-commerce data using plain English. This isn't just a toy demo; it's a full architectural breakdown of how to modernize legacy infrastructure with LLMs.

The Core Infrastructure Stack

The foundation of this warehouse relies on a robust, industry-standard stack. The developer utilized Hadoop 3.3.6 for storage and resource management via HDFS and YARN, paired with Hive 4.0.0 and Spark 3.5.1 for processing. PostgreSQL 16 serves as the metastore, ensuring metadata integrity, while Tableau Public handles visualization. The dataset itself is the Olist Brazilian E-commerce dataset, comprising approximately 100,000 orders, providing a realistic scale for testing ETL pipelines and query performance.

A Four-Layer Dimensional Model

To maintain order in the chaos of raw data, the project implements a standard four-layer dimensional data warehouse model. It begins with the ODS (Operational Data Store) for raw ingestion, moves to DWD (Data Warehouse Detail) for cleaned data using PySpark, aggregates into DWS (Data Warehouse Summary) for domain-specific metrics, and finally lands in ADS (Application Data Service) for BI reporting. A critical implementation detail here is the use of Parquet with Snappy compression for storage efficiency. The developer explicitly notes enforcing ETL through Spark SQL to bypass the notorious permission headaches associated with Hive-on-Tez, a practical tip for anyone wrestling with these tools.

The AI Twist: Qwen and Prompt Engineering

The standout feature is the integration of the Qwen LLM to handle natural language queries. Rather than relying on fine-tuning, the developer used prompt engineering, feeding the model schema descriptions, business rules, and few-shot examples. This approach allows the AI to generate precise Hive SQL based on user questions. In testing, the assistant generated SQL that matched official ADS-layer results 100% of the time across three complex business questions. The user interface for this interaction is built with Streamlit, keeping the frontend lightweight and accessible.

Actionable Insights from RFM Analysis

Beyond the architecture, the warehouse delivers tangible business value through RFM (Recency, Frequency, Monetary) analysis. One key finding highlighted in the project is that the "Important to retain" customer segment constitutes 23.5% of the user base. This insight identifies the prime target for re-engagement campaigns, demonstrating how the combination of structured data warehousing and accessible AI querying can drive specific marketing strategies.

Key Takeaways

  • The project uses Hadoop 3.3.6, Hive 4.0.0, and Spark 3.5.1 as the core big data stack.
  • Qwen LLM is integrated via prompt engineering, not fine-tuning, to generate Hive SQL.
  • Parquet + Snappy storage format is used to optimize efficiency and avoid Hive-on-Tez permissions issues.
  • The codebase, including ETL processes and the Streamlit interface, is open-source on GitHub.

The Bottom Line

This project proves you don't need a massive budget or a custom-trained model to bring AI to your data stack. It’s a pragmatic, builder-focused blueprint for making big data accessible.