.. _elt_commands:

Extract and Load: command reference and first run
=================================================

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

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:

.. list-table::
   :header-rows: 1
   :widths: 30 70

   * - 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
---------------

.. list-table::
   :header-rows: 1
   :widths: 30 70

   * - 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``.

.. _elt_first_run:

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.

#. **Create the raw tables.**

   .. code-block:: shell

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

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

#. **Create the table views.**

   .. code-block:: shell

      ./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``).

#. **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``:

   .. code-block:: shell

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

.. code-block:: shell

   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 :ref:`elt_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:

.. code-block:: shell

   ./panorama.py test-connections

`Back to Home <index.html>`_