Extract and Load: overview

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. Licensed under Apache 2.0.

What it does

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

  1. Connect to the data source.

  2. Extract all the data in tabular form.

  3. 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 Extract and Load: command reference and first run 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 Extract and Load: non-Open edX 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 Extract and Load: configuration for the full structure.

Command-line usage

% ./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

Back to Home