.. _elt_pipeline: Extract and Load: the data pipeline =================================== .. contents:: On this page :local: :depth: 2 This page explains what the agent writes to the data lake, how the raw tables and views are built, and the two mechanisms that keep large tables manageable: **field partitions** and **incremental updates**. Data lake directory structure ----------------------------- For each table (or for each field-based partition of each table, when enabled) the agent generates one CSV file and uploads it. The file has the same name as the table, with a ``.csv`` extension, unless ``datalake_s3_table`` is configured. The layout is:: s3:///[/]/[/][field partitions/]
.csv Where: * **bucket** — the ``panorama_raw_data_bucket`` setting. * **base prefix** — optional subdirectory grouping tables of the same kind of system (for example ``openedx``). It can receive files from multiple sources as long as the table names and field structure match. * **table name** — base location of the data lake table. All text files inside this directory must have exactly the same column structure. * **base partitions** — partitions common to a whole installation, written in Hive format. These are not source fields but appear as columns in the data lake. For multiple Open edX installations the default is ``lms=``, for example ``lms=openedx.example.com``. * **field partitions** — optional. For large tables you can split the source into multiple CSV files by the value of one or more fields. The field is removed from the CSV and becomes a partition column. The default Open edX setting partitions ``courseware_studentmodule`` by ``course_id``. Raw tables ---------- ``create-datalake-tables`` registers each uploaded table as an **external** Athena table over the CSV files. Key characteristics of the generated table: * Every column is declared as ``string`` (the raw layer does no typing). * Base partitions and field partitions become partition columns. * The table uses the OpenCSV serde with a comma separator, ``"`` quote character, ``\`` escape character, and skips one header line. * The ``LOCATION`` points at ``s3:////
/`` (the partition subdirectories are discovered underneath). Because the table is external, dropping it (``drop-datalake-tables``) removes only the catalog entry, not the CSV data in S3. Table views (first transformation layer) ---------------------------------------- ``create-table-views`` creates a ``CREATE OR REPLACE VIEW`` over each raw table. This is the first layer of the pipeline and its job is to make the string data usable: * **Numeric types** (``INT``, ``TINYINT``, ``SMALLINT``, ``MEDIUMINT``, ``BIGINT``) become ``TRY_CAST(... AS BIGINT)``. * **Floating types** (``FLOAT``, ``DOUBLE``, ``DECIMAL``) become ``TRY_CAST(... AS DOUBLE)``. * **Date/time types** (``DATETIME``, ``DATE``, ``TIMESTAMP``, ``TIME``, ``YEAR``) are parsed with ``date_parse(..., '%Y-%m-%d %H:%i:%s.%f')`` inside ``TRY(...)``. * **JSON** and **string** types get ``NULLIF(, 'NULL')``, so the literal text ``NULL`` becomes a real ``NULL``. * Base partitions are appended to the view as additional columns. The default view name is ``_table_
``. These typed views are what the next pipeline layers (and the BI tool) build on. Curated views ------------- Further pipeline steps are implemented as additional Athena views that join the typed table views, filter them, and add calculated fields. The repository includes ready-made Open edX examples under ``openedx_views/``: * ``openedx_view_enrollments.sql`` — a wide enrollment view joining ``student_courseenrollment`` with users, profiles, grades, course overviews, course structures, country coordinates, course access roles, and manual enrollment history. It derives fields such as ``days_since_enrolled``, ``percent_grade``, ``is_student``, and role flags. * ``openedx_view_student_courseaccessrole.sql`` — aggregates course access roles per user and course into boolean flags (``is_course_staff``, ``is_course_instructor``, and so on). * ``openedx_view_student_manualenrollment.sql`` — collapses the manual enrollment audit into one row per enrollment with the last transition, counts, and ordered history. These are provided as SQL you run in Athena to build the curated layer that dashboards read. Field partitions ---------------- To partition a table by a field, add a ``partitions`` section listing the fields under ``partition_fields``: .. code-block:: yaml tables: - name: courseware_studentmodule datalake_table_name: courseware_studentmodule_raw partitions: partition_fields: - course_id fields: ... Each distinct value in the specified column produces a separate CSV file, placed in a Hive format subdirectory such as ``course_id=``. The partition field is removed from the CSV body and becomes a partition column in the data lake. .. warning:: This can create a very large number of subdirectories. Plan field partitions carefully. Incremental updates ------------------- Incremental updates let periodic runs upload only the partitions that changed recently, instead of the whole table. **A table must be partitioned by field to support incremental updates.** To enable them, add a ``timestamp_field`` and an ``interval`` to the table's ``partitions`` section: .. code-block:: yaml tables: - name: courseware_studentmodule datalake_table_name: courseware_studentmodule_raw partitions: interval: 2 hour partition_fields: - course_id timestamp_field: modified fields: ... * ``timestamp_field`` must be a ``datetime`` field holding the last-modified time. * ``interval`` uses MySQL interval syntax. As a rule of thumb, set it to **double** your query period: if you extract hourly, use a 2 hour interval. **How it works.** The agent first queries the distinct values of the partition fields that have records with a timestamp within the last ``interval`` from now. Then it updates only the field-based partitions that changed. Note that the **whole** matching partition is rewritten, not just the changed rows. This is why the :ref:`first run ` uses ``--force``: it uploads every partition once, including those older than the interval, so the data lake starts complete. `Back to Home `_