.. _elt_configuration: Extract and Load: configuration ================================ .. contents:: On this page :local: :depth: 2 The agent is driven entirely by a **YAML settings file**. By default this file is ``panorama_settings.yaml``; override it with the top-level ``--settings`` option. The repository ships several ready-made examples: * ``panorama_openedx_settings_example.yaml`` — Open edX (MySQL + course structures). * ``panorama_mysql_settings_example.yaml`` — generic MySQL. * ``panorama_csv_settings_example.yaml`` — generic CSV. * ``panorama_moodle_settings_example.yaml`` — Moodle. The ``config/`` directory contains additional real-world examples. File structure -------------- The settings file has two top-level sections: ``datalake`` and ``datasources``. .. code-block:: yaml datalake: type: aws panorama_raw_data_bucket: panorama base_prefix: openedx aws_access_key: aws_secret_access_key: aws_region: us-east-1 datalake_database: panorama datalake_workgroup: panorama base_partitions: - name: lms type: varchar value: lms.example.com datasources: - type: openedx_course_structures name: openedx_course_structures mongodb_host: localhost mongodb_username: '' mongodb_password: '' mongodb_database: edxapp mysql_username: mysql_password: mysql_host: mysql_database: edxapp tables: - name: course_structures fields: - name: module_location type: varchar - ... - type: mysql name: openedx_mysql mysql_username: mysql_password: mysql_host: mysql_database: edxapp tables: - name: student_courseenrollment fields: - name: id type: int - ... The ``datalake`` section ------------------------ .. list-table:: :header-rows: 1 :widths: 30 70 * - Key - Meaning * - ``type`` - Data lake technology. ``aws`` is the supported value. * - ``panorama_raw_data_bucket`` - Destination S3 bucket that stores the data lake CSV files. * - ``base_prefix`` - Optional subdirectory / name prefix grouping tables of the same kind of system (for example ``openedx`` or ``moodle``). Used both in the S3 path and in the default Athena table names. * - ``aws_access_key`` / ``aws_secret_access_key`` - Credentials for the IAM user created in :ref:`elt_datalake`. * - ``aws_region`` - Region of the Athena engine. * - ``datalake_database`` - Athena/Glue database name (for example ``panorama``). * - ``datalake_workgroup`` - Athena workgroup name (for example ``panorama``). * - ``base_partitions`` - Partitions common to a whole installation. These are not based on fields in the source data but appear as columns in the data lake. For multiple Open edX installations the default is a single partition named ``lms`` whose ``value`` is the LMS URL. .. _identify_the_lms: Identifying the LMS ~~~~~~~~~~~~~~~~~~~~ In ``datalake.base_partitions``, edit the ``value`` of the ``lms`` entry to the URL of the LMS (for example ``lms.example.com``). This is what keeps data from multiple installations separate inside the same shared data lake. The ``datasources`` section --------------------------- ``datasources`` is a list. Each entry has a ``type`` and a ``name``, connection settings for that type, and a list of ``tables``. Supported types: * ``mysql`` * ``openedx_course_structures`` * ``csv`` * ``xls`` (Excel) Connection settings for Open edX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For Open edX usage you typically set the following per data source. **MySQL:** * ``mysql_username`` * ``mysql_password`` * ``mysql_host`` * ``mysql_database`` **Course structures (MongoDB):** * ``mongodb_host`` * ``mongodb_username`` * ``mongodb_password`` * ``mongodb_database`` .. note:: The ``openedx_course_structures`` data source reads the modulestore from MongoDB and also uses the MySQL ``split_modulestore_django_splitmodulestorecourseindex`` table to find the active version of each course, so it needs both MongoDB and MySQL connection settings. Tables and fields ~~~~~~~~~~~~~~~~~~ Each element of ``tables`` describes a source table: .. code-block:: yaml tables: - name: student_courseenrollment # source table name datalake_s3_table: enrollment # (optional) name used in S3 and default Athena names datalake_table_name: ... # (optional) explicit raw table name datalake_table_view: ... # (optional) explicit view name partitions: # (optional) see the pipeline page partition_fields: - course_id timestamp_field: modified interval: 2 hour fields: - name: id type: int - name: course_id type: varchar - name: created type: datetime value: null # (optional) constant instead of querying the field Field notes: * ``type`` is the source data type. It drives how the :ref:`table view ` casts the column (numeric types to ``BIGINT``/``DOUBLE``, date/time types parsed as timestamps, everything else treated as a string with ``'NULL'`` converted to ``NULL``). * ``value`` (optional) overrides the field with a constant. The field is not queried from the database but is filled with the constant value. This is useful when a field is missing from the source but still needed in the data lake, for example ``value: null``. Table name overrides ~~~~~~~~~~~~~~~~~~~~~ By default the source table name is used for the S3 directory and for the default Athena table/view names. For example, with ``base_prefix: moodle`` and source table ``mdl_course``, the default S3 location is:: s3:///moodle/mdl_course//mdl_course.csv Use ``datalake_s3_table`` to keep the source table name for extraction while using a different name in S3 and in the default Athena names: .. code-block:: yaml tables: - name: mdl_course datalake_s3_table: course fields: ... This stores data under:: s3:///moodle/course//course.csv and the default Athena names become ``moodle_raw_course`` (raw table) and ``moodle_table_course`` (view). You can still override those explicitly with ``datalake_table_name`` and ``datalake_table_view``. Populating tables and fields automatically ------------------------------------------ Rather than writing every field by hand, you can let the agent introspect the database: * ``set-tables`` records which tables to extract. * ``set-tables-fields`` queries each table and fills in its fields. See :ref:`elt_commands` and :ref:`elt_other_sources` for the exact workflow. `Back to Home `_