.. _elt_overview:

Extract and Load: overview
==========================

.. contents:: On this page
   :local:
   :depth: 2

Panorama Extract and Load (``panorama-elt``) is the extraction agent. It provides a
command-line tool that connects to your data sources and uploads the extracted data to the
data lake. Data is uploaded **unmodified**, so the raw information is kept intact; data
transformation happens in the next layers of the pipeline.

Repository: `github.com/aulasneo/panorama-elt <https://github.com/aulasneo/panorama-elt>`_.
Licensed under Apache 2.0.

What it does
------------

To perform the extract-and-load function the agent will:

#. Connect to the data source.
#. Extract all the data in tabular form.
#. Upload the data to the data lake in CSV format.

It also provides helper commands to:

* Create the data lake **raw tables** in the Athena/Glue catalog.
* Create the **table views** that form the first layer of the data pipeline (fixing NULL
  values and applying the right data types to each field).
* Drop those tables and views.
* Introspect a data source to populate the settings file (``set-tables``, ``set-tables-fields``).
* Test all configured connections.

See :ref:`elt_commands` for the full command reference.

Supported data sources
-----------------------

Currently the agent supports these data source types:

* **Generic MySQL** — any MySQL database.
* **Generic CSV file.**
* **Generic Excel file.**
* **Open edX course structures** — read from the Open edX MongoDB modulestore (and the
  ``split_modulestore_django_splitmodulestorecourseindex`` MySQL table to determine the
  active course versions).

The settings file that ships with the repo is prepared for Open edX and already contains the
most common tables with their field definitions. The MySQL data source, however, works for
any MySQL installation. See :ref:`elt_other_sources`.

Design notes and current limitations
-------------------------------------

* **CSV storage only.** Data is currently stored as CSV text files. Because everything is
  stored as text, the first pipeline layer (the *table views*) is responsible for fixing
  ``NULL`` values and casting each field to the right data type.
* **AWS data lake only.** The provided ``PanoramaDatalake`` class targets an AWS data lake
  (S3 + Glue + Athena). Its modular architecture makes it possible to extend to other
  technologies by overriding its methods, but only AWS is supported out of the box.
* **SQL pipeline.** The data pipeline itself is implemented as SQL over the data lake. Further
  steps (joins, filters, calculated fields) take the form of Athena views. Examples are
  included in the repo under ``openedx_views/``.
* **External processing is possible.** Additional processing can be performed on the CSV files
  by external tools, but you must write the results back in the proper format and register the
  data lake tables accordingly.

How it is configured
--------------------

The agent uses a **YAML settings file** to describe the data sources and the data lake,
including connection information. The data source section describes each table and every
field, including data types and partitions. The repo ships several example settings files
(Open edX, MySQL, CSV, Excel, Moodle). See :ref:`elt_configuration` for the full structure.

Command-line usage
------------------

.. code-block:: text

   % ./panorama.py
   Usage: panorama.py [OPTIONS] COMMAND [ARGS]...

     Panorama ELT command line entry point. Loads settings and the datalake into
     the context.

   Options:
     --version        Show the version and exit.
     --debug          Enable debugging
     --settings TEXT  Configuration file
     --help           Show this message and exit.

   Commands:
     create-datalake-tables  Creates datalake tables for all tables defined...
     create-table-views      Creates views based on the tables defined.
     drop-datalake-tables    Deletes datalake tables
     extract-and-load        Extracts the data from the datasources and...
     set-tables              Queries the datasource's tables and updates the...
     set-tables-fields       Queries the datasource's tables and updates the...
     test-connections        Test all connections

Next steps
----------

* :ref:`elt_installation` — install the agent.
* :ref:`elt_datalake` — create the AWS data lake and permissions.
* :ref:`elt_configuration` — understand and edit the settings file.
* :ref:`elt_commands` — run the commands, including the important first run.

`Back to Home <index.html>`_