Cartolafc reports

This project builds a data lake and data warehouse from Cartola FC data (a fantasy football game based on the Brazilian national football championship). The stack includes:

Service	Role
Airflow	Schedules and orchestrates ingestion pipelines
Hadoop (HDFS)	Stores raw, curated, and trusted data layers
Hive	SQL engine and warehouse over HDFS
DataHub	Metadata catalog and data lineage
Superset	Dashboards and ad-hoc querying

Data flows through three HDFS layers:

• raw/ — files extracted from external sources
• curated/ — cleaned Parquet tables exposed as Hive external tables (curated schema)
• trusted/ — managed Hive tables built via ELT from the curated layer (trusted schema)

Prerequisites

• Docker and Docker Compose (v1 docker-compose or v2 docker compose)
• At least 8 GB of free RAM recommended (the DataHub stack is memory-intensive)
• Ports 8020, 8080, 8088, 9002, 9870, 10000, and 10002 available on the host

How to start

Services are defined in compose files at the project root. Use quickstart.sh for convenience, or run docker-compose directly.

All services

Starts Airflow, Hadoop, Hive, DataHub, and Superset:

bash quickstart.sh

Equivalent manual command:

docker-compose \
    --file docker-compose.airflow.yaml \
    --file docker-compose.hive.yaml \
    --file docker-compose.datahub.yaml \
    --file docker-compose.superset.yaml \
    up --detach

Operational stack (pipelines only)

Starts Airflow, Hadoop, Hive, and DataHub — without Superset:

bash quickstart.sh airflow

docker-compose \
    --file docker-compose.airflow.yaml \
    --file docker-compose.hive.yaml \
    --file docker-compose.datahub.yaml \
    up --detach

Querying stack (visualization)

Starts Superset, Hadoop, Hive, and DataHub (DataHub is included so Superset metadata can be ingested on startup):

bash quickstart.sh superset

docker-compose \
    --file docker-compose.superset.yaml \
    --file docker-compose.hive.yaml \
    --file docker-compose.datahub.yaml \
    up --detach

Stop all services

bash quickstart.sh stop

On first boot, containers run health checks and one-time setup jobs (Hive table creation, Superset dashboard import, and so on). Allow a few minutes before the web UIs are ready.

Web interfaces

Service	URL	Default credentials
Airflow	http://localhost:8080	admin / admin
DataHub	http://localhost:9002	—
Hadoop NameNode	http://localhost:9870	—
HiveServer2	http://localhost:10002	—
Superset	http://localhost:8088	admin / admin

Hive can also be queried on port 10000 (JDBC/Beeline).

Project layout

.
├── airflow/          # DAGs, extractors, transformers, and Airflow image
├── config/           # Hadoop and Hive site configuration
├── datahub/          # MySQL initialization for DataHub
├── docs/             # Architecture diagrams, screenshots, and sample SQL
├── hadoop/           # HDFS namenode/datanode image and setup scripts
├── hive/             # Hive metastore/server image and DDL scripts
├── superset/         # Superset image, dashboards, and lineage ingestion
├── docker-compose.*.yaml
└── quickstart.sh     # Convenience wrapper for compose commands

Airflow pipelines

Two DAGs orchestrate ingestion:

DAG	Schedule	Source	Description
cartolafc_history	@yearly (2014–2020)	caRtola GitHub repo	Backfills historical seasons from CSV files
cartolafc_daily	@daily	Cartola FC REST API	Ingests current-round match and player data

Each DAG extracts data to HDFS, transforms it into the curated layer, and loads trusted Hive tables. Metadata is published to DataHub at the end of the history pipeline.

Data warehouse schema

The Hive warehouse mirrors the trusted HDFS layer. External tables in the curated schema point at Parquet files; managed tables in the trusted schema are populated via ELT. Views in the superset schema join trusted tables for dashboard consumption.

Dashboards

Two Superset dashboards consume data from the superset schema views — one focused on clubs and another on player performance:

Dashboards are imported automatically by the superset_setup container on first start.

Data catalog and lineage

DataHub ingests metadata from Airflow, Hive, and Superset:

Query examples

Sample queries live in docs/sql. Below are two common analyses.

Best lineup by position (from docs/sql/best_lineup.sql) — top-scoring players per position slot across all seasons since 2014:

SELECT temporada, rodada, clube, jogador, pontos, media, posicao
FROM (
    SELECT
        scouts.temporada, partidas.rodada, clubes.nome AS clube,
        atletas.apelido AS jogador, scouts.pontos, scouts.pontosmedia AS media,
        posicoes.abreviacao AS posicao,
        RANK() OVER (PARTITION BY scouts.posicaoid ORDER BY scouts.pontos DESC) AS scoutsrank
    FROM trusted.scouts
    JOIN trusted.clubes ON scouts.clubeid = clubes.clubeid
    JOIN trusted.partidas ON scouts.partidaid = partidas.partidaid
    JOIN trusted.atletas ON scouts.atletaid = atletas.atletaid
    JOIN trusted.posicoes ON scouts.posicaoid = posicoes.posicaoid
) ranked_players
WHERE
    (posicao IN ('gol', 'tec') AND scoutsrank = 1) OR
    (posicao IN ('mei', 'ata') AND scoutsrank <= 3) OR
    (posicao IN ('zag', 'lat') AND scoutsrank <= 2);

Best single-round performance per season (from docs/sql/best_players.sql):

SELECT temporada, rodada, clube, jogador, pontos, media
FROM (
    SELECT
        scouts.temporada, partidas.rodada, clubes.nome AS clube,
        atletas.apelido AS jogador, scouts.pontos, scouts.pontosmedia AS media,
        RANK() OVER (PARTITION BY scouts.temporada ORDER BY scouts.pontos DESC) AS scoutrank
    FROM trusted.scouts
    JOIN trusted.clubes ON clubes.clubeid = scouts.clubeid
    JOIN trusted.partidas ON partidas.partidaid = scouts.partidaid
    JOIN trusted.atletas ON atletas.atletaid = scouts.atletaid
) ranked_scouts
WHERE scoutrank = 1
ORDER BY pontos DESC;

Other queries in docs/sql cover home/away victories, draws, top players, and row counts per table.