Extract and Load: command reference and first run

All commands are subcommands of panorama.py and are run from the installed virtual environment. Top-level options (--settings, --debug, --version) go before the command.

Selecting what to act on

Most commands accept a shared set of selectors to choose which data sources and tables they operate on:

Option

Meaning

--all / -a

Act on every table of every data source.

--datasource <name> / -d <name>

Act only on the named data source.

--tables <list> / -t <list>

Act only on the comma-separated list of tables. Do not put spaces between names.

Command summary

Command

Description

test-connections

Test all configured connections (data sources and data lake).

set-tables

Query the data source and update the tables section of the settings file.

set-tables-fields

Query each table and update its fields in the settings file.

create-datalake-tables

Create the raw data lake tables for the selected tables.

create-table-views

Create the typed views over the raw tables. Tables must exist first.

drop-datalake-tables

Delete data lake tables.

extract-and-load

Extract data from the data sources and upload it to the data lake. Accepts --force.

First run

Before running the commands that upload data, you must create the tables and views in the data lake catalog. Uploading before the tables exist will fail, because the upload routine tries to update partitions in tables that do not yet exist.

  1. Create the raw tables.

    ./panorama.py create-datalake-tables --all

    By default these tables are named <base_prefix>_raw_<table name> (for example openedx_raw_student_courseenrollment).

  2. Create the table views.

    ./panorama.py create-table-views --all

    The views have exactly the same fields as the underlying raw table, but with numeric and date fields converted to proper types, and strings with the value 'NULL' replaced with real NULL values. By default they are named <base_prefix>_table_<table name> (for example openedx_table_student_courseenrollment).

  3. Initial data upload.

    If any tables have incremental updates enabled, you must make an initial run to upload all partitions, even those that did not change recently. Use --force:

    ./panorama.py extract-and-load --all --force

    Warning

    If the tables are large, this can consume a lot of CPU and take a long time. Consider running it outside business hours, as it may impact the host’s performance.

    Note

    • You can start doing incremental updates first and perform the full first run later.

    • The course structures table is not partitioned.

Periodic updates

To run the extraction every hour, add a line similar to this to the crontab:

0 * * * * cd panorama-elt && venv/bin/python panorama.py extract-and-load --all >> panorama.log

Without --force, extract-and-load only updates the partitions that changed within the configured interval (see Extract and Load: the data pipeline), which keeps periodic runs cheap.

Tip

On Kubernetes-based Open edX (via the Tutor plugin), extraction is scheduled as a CronJob at a configurable period. On local / non-Kubernetes deployments you set up the cron entry yourself, as above.

Verifying connectivity

At any time, check that the agent can reach everything it needs:

./panorama.py test-connections

Back to Home