shutter-warehouse

English | Espanol

Batch data engineering over shutter-* photography tool data: PySpark ETL, an RDD MapReduce-paradigm job, and HiveQL-compatible Spark SQL, all running in Spark local mode with a real, CI-tested test suite. No fabricated claims: everything described as "CI-verified" below actually runs in .github/workflows/ci.yml on every push.

Why this exists

This repo exists to answer one job posting, verbatim:

Experience writing SQL queries. Experience using BI tools like Looker/Tableau/Power BI for visualizations and dashboards. 3 years with data processing software like Hadoop/Spark/Pig/Hive and algorithms like MapReduce/Flume. Experience with SQL/MySQL and Unix/Linux.

Rather than list those words on a resume, this repo is working code that exercises each one, processing data shaped like the output of Keivan's other shutter-* photography tools (gearwatch-style price history, and log-shaped text) at a small but real scale:

Requirement in the posting	Where it lives here
SQL queries	src/shutter_warehouse/hive_queries.py -- GROUP BY, RANK() OVER (PARTITION BY ...), a WITH CTE + ROW_NUMBER()
BI tools (Looker/Tableau/Power BI) for dashboards	docker-compose.yml stubs Apache Superset, the direct open-source equivalent -- phase 2, not built yet, see below
Hadoop/Spark and the MapReduce algorithm	src/shutter_warehouse/mapreduce.py -- raw RDD .map() / .flatMap() / .reduceByKey(), run through real PySpark in local mode
Hive	src/shutter_warehouse/hive_queries.py runs spark.sql(...) in HiveQL-compatible syntax; docker-compose.yml stubs a real standalone Hive metastore for phase 2
Flume	docker-compose.yml stub only -- phase 2, not built yet
SQL/MySQL and Unix/Linux	CI runs the whole suite on Ubuntu; MySQL specifically isn't used anywhere yet (see Phase 2)

What is CI-verified vs what needs docker compose up

To avoid overstating this repo the way an earlier sibling repo's README once did (it claimed a test count that didn't match the real one), here is the honest split:

CI-verified (runs on every push, Python 3.11 and 3.12, see the badge above):

• The PySpark DataFrame ETL job (etl.py): grouped averages, counts, and exact percentiles over data/price_history.csv, in Spark local mode (local[2]).
• The RDD MapReduce-paradigm job (mapreduce.py): .map() / .flatMap() / .reduceByKey() over data/sample_log.txt, cross-checked against an independent plain-Python count in the test suite.
• The HiveQL-syntax Spark SQL queries (hive_queries.py): GROUP BY, window functions (RANK() OVER (PARTITION BY ...)), and a CTE with ROW_NUMBER(), run via spark.sql(...) with enableHiveSupport() against Spark's embedded catalog (no external Hive metastore).
• 13 tests, all asserting on actual computed values (grouped averages, medians, rank orderings, word/log-level counts), not just "it ran without throwing." Run with the project's own pytest console script -- see Development below.

Needs docker compose up to reproduce yourself, not yet CI-tested:

• docker-compose.yml is a structurally-valid skeleton for a real single-node Hadoop HDFS namenode/datanode, a standalone Postgres-backed Hive metastore, Flume, and Apache Superset. None of these services are wired to the Spark jobs above yet, none are started in CI, and the compose file itself has never been brought up end-to-end. Every service binds only to 127.0.0.1.
• Nothing here reads from or writes to HDFS yet; the jobs read local data/ fixtures.
• No Flume agent config exists; the flume service in the compose file is an image reference with no mounted configuration.
• No Superset dashboard exists yet; the superset service has no database connection configured.
• MySQL is not used anywhere yet -- the posting's "SQL/MySQL" line is currently only covered by Spark SQL/HiveQL, not literal MySQL.

See "What phase 2 still needs" below for the concrete list.

Install and run (the part that works today)

Requires Python 3.11+ and a JDK (PySpark needs a JVM; tested against Java 17 and 21). Not on PyPI; install from source:

git clone https://github.com/keivanmalhani/shutter-warehouse.git
cd shutter-warehouse
python -m venv .venv && source .venv/bin/activate
pip install -e .

Run the DataFrame ETL job against the sample price-history fixture:

shutter-warehouse etl

Run the RDD MapReduce-paradigm jobs against the sample log fixture:

shutter-warehouse mapreduce

Run the HiveQL-syntax Spark SQL queries:

shutter-warehouse hive-sql

Each command prints its results as Spark DataFrame tables (or, for mapreduce, plain key/count lines) to stdout. All three read the fixtures under data/ by default; pass --price-history / --log-file to point at a different file.

Data

data/price_history.csv is a small, synthetic fixture (12 rows, 4 camera gear items across 3 marketplace sources) shaped like the price-history output of Keivan's gearwatch tool: item_id, price, condition, timestamp, source. data/sample_log.txt is a synthetic application log (12 lines) shaped like output from a shutter-* tool's ingest/pricing workers, used to exercise the RDD MapReduce-paradigm job. Neither file contains real data from any other repo; both are hand-written fixtures sized for fast, deterministic tests.

Architecture

data/price_history.csv --> etl.py         (DataFrame API: groupBy, agg, percentile)
                        --> hive_queries.py (spark.sql, HiveQL: GROUP BY, window fns, CTE)
data/sample_log.txt    --> mapreduce.py    (raw RDD: map / flatMap / reduceByKey)

All three modules share one local-mode SparkSession builder (spark_session.py, local[2], enableHiveSupport()). There is no cluster, no YARN, and (today) no external Hive metastore -- hive_queries.py uses Spark's bundled embedded catalog, which is enough to exercise real HiveQL syntax without standing up external services. docker-compose.yml is the phase-2 skeleton for the real Hadoop/Hive/Flume/Superset stack described above.

Development

pip install -e ".[dev]"
pytest

13 tests, no committed Spark binaries -- both fixture files are small, hand-written text checked into data/. CI (.github/workflows/ci.yml) installs a Temurin JDK 17 via actions/setup-java, installs the package, and runs the suite with the plain pytest console script (not python -m pytest) on Python 3.11 and 3.12, so a fresh clone and CI behave identically.

What phase 2 still needs

This is session 1 of a multi-session build. Concretely still open:

• Bring up docker compose up for real and verify the Hadoop HDFS namenode/datanode actually reach a healthy state.
• Wire hive_queries.py (or a new module) to the real standalone Hive metastore in docker-compose.yml instead of Spark's embedded Derby catalog, and add a hive-site.xml.
• Land the data/ fixtures (or larger synthetic data) in HDFS and read from there instead of the local filesystem.
• Configure Flume with a real agent config (spooldir or exec source -> HDFS sink) tailing a log source into HDFS, feeding the MapReduce-paradigm job from HDFS instead of a local file.
• Connect Superset to the Hive metastore (or a Spark Thrift server) and build an actual dashboard over price_stats_by_item / median_price_by_condition -- the real BI/dashboard deliverable the job posting asks for.
• Add a literal MySQL example (the posting names it specifically, separate from Hive), most likely as a small ingestion source feeding the ETL job.
• Spanish README (README.es.md), matching the rest of the shutter-* family -- intentionally deferred to a later session.
• CI coverage for the docker-compose stack itself (today CI only covers the PySpark jobs, not the phase-2 services).

License

MIT, see LICENSE.