Metadata-Version: 2.4
Name: soil-etl
Version: 0.2.9
Summary: YAML-driven ETL mappers for ForSITE soil database imports.
License: MIT
License-File: LICENSE
Keywords: soil,etl,yaml,postgis,forestry,forsite
Author: Olivier Forgues-Nhan
Author-email: olivier.forgues-nhan@agr.gc.ca
Requires-Python: >=3.10,<4.0
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Science/Research
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering :: GIS
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Dist: certifi (>=2025.8.3,<2026.0.0)
Requires-Dist: geoalchemy2 (>=0.17.1)
Requires-Dist: geopandas (>=1.0.1)
Requires-Dist: hydra-colorlog (>=1.2.0,<2.0.0)
Requires-Dist: hydra-core (>=1.3.2,<2.0.0)
Requires-Dist: nrcan-etl-toolbox (>=0.2.1)
Requires-Dist: openpyxl (>=3.1.0)
Requires-Dist: pandas (>=2.2.3)
Requires-Dist: psycopg2-binary (>=2.9.10)
Requires-Dist: pydantic (>=2.0)
Requires-Dist: pyproj (>=3.0)
Requires-Dist: pyyaml (>=6.0)
Requires-Dist: shapely (>=2.1.0)
Requires-Dist: sqlalchemy (>=2.0.40)
Requires-Dist: sqlmodel (>=0.0.25)
Requires-Dist: xlrd (>=2.0.2)
Project-URL: Changelog, https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl/-/blob/main/CHANGELOG.md
Project-URL: Documentation, https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl/-/blob/main/docs/README_yaml_format_en.md
Project-URL: Homepage, https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl
Project-URL: Issues, https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl/issues
Project-URL: Repository, https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl
Description-Content-Type: text/markdown

# SOIL - Structured Observation Ingestion Library

> **Version**: 0.2.8 · **Python**: ≥ 3.10 · **PyPI**: `soil-etl`

YAML-driven ETL package for importing Canadian forestry soil datasets into the ForSITE PostGIS database model,
built on the OGC Observations, Measurements & Samples specification.

## Acknowledgements

| Project Partners                                                                                               |
|:---------------------------------------------------------------------------------------------------------------|
| <img src="docs/logo/gc-logo.svg" width="310" style="background-color: white ; padding: 10px; padding-right: 55px" > |
| <img src="docs/logo/nrcan-logo.svg" width="310" style="background-color: white ; padding: 10px">                    |
| <img src="docs/logo/aafc_logo.svg" width="310" style="background-color: white ; padding: 10px">                     |

This project was developed through joint funding provided by Agriculture and Agri-Food Canada (`AAFC`) under
the [CSBO](https://sis.agr.gc.ca/cansis/biome/index.html) program and Natural Resources Canada (`NRCan`) for
the ForSITE-Soil degradation project.

The code developed as part of this project includes contributions from:

- [Olivier Forgues-Nhan](olivier.forgues-nhan@agr.gc.ca)
- [Xavier Malet](xavier.malet@nrcan-rncan.gc.ca)
- [Catlan Dallaire](catlan.dallaire@nrcan-rncan.gc.ca)
- [David Gagné](david.gagne@agr.gc.ca)

---

## Installation

```bash
pip install soil-etl
```

Or with Poetry:

```bash
poetry add soil-etl
```

---

## Quick Start

### YAML-only pipeline (recommended)

```python
from pathlib import Path
from soil_etl.yaml_mapper import YamlDatasetMapper

mapper = YamlDatasetMapper(
    config_path=Path("my_config.yaml"),
    data_file_path=Path("data.xlsx"),
)
metrics = mapper.import_data(
    database_connection_string="postgresql+psycopg2://user:password@host:5432/database",
)
print(metrics)
```

All new ETL pipelines **must** use `YamlDatasetMapper`. See `AGENTS.md` for full architecture guidance.

### Canonical imports

```python
from soil_etl.yaml_mapper import YamlDatasetMapper
from soil_etl.bindings import Binding, FromColumn
from soil_etl.db import ImportationInterface
```

---

## Boilerplate - Scaffolding for the new project

The fastest way to start a new import project is to **create a ready-to-use project** from the
packaged template. It creates a complete, self-contained project: 
- a runner (`.py` file used to import the data), 
- an importation template (`YAML config` file), 
- Hydra DB (`YAML config` file), 
config, an `.env.example`, and a **preconfigured Copilot agent** under `.github/` (chatmodes, prompts,
skills and embedded knowledge about the package, the YAML format and the database model).

### From the command line

```bash
# Scaffold into a new subfolder
soil-etl start-project my-soil-imports

# ...or into the current directory
soil-etl start-project

# Options
soil-etl start-project my-soil-imports --dest ./projects --overwrite
```

Equivalent module invocation:

```bash
python -m soil_etl start-project my-soil-imports
```

### From Python

```python
import soil_etl

soil_etl.start_project("my-soil-imports")     # into a new subfolder
soil_etl.start_project()                       # into the current directory
soil_etl.start_project(name="my-soil-imports", overwrite=True)
```

### Generated project layout

```
my-soil-imports/
+-- pyproject.toml                    # depends on soil-etl
+-- .env.example                      # env vars (DB_CONN, etc.)
+-- .gitignore
+-- configs/
|   +-- configs.yaml                  # Hydra root (selects db: local)
|   +-- db/
|   |   +-- base_con.yaml
|   |   +-- local.yaml
+-- data/                             # source files (Excel/CSV) - not versioned
+-- pipelines/
|   +-- import_example_dataset.py     # 3-line runner
|   +-- example_dataset.yaml          # pipeline YAML config (to adapt)
+-- .github/                          # Copilot agent + embedded knowledge
    +-- copilot-instructions.md
    +-- chatmodes/                    # soil-etl-pipeline agent (VS Code)
    +-- agents/                       # same agent (PyCharm / JetBrains)
    +-- prompts/                      # /new-pipeline, /review-yaml-config, /debug-failed-import
    +-- instructions/                 # auto-applied skills (YAML + Python runner)
    +-- knowledge/                    # YAML format, DB model, enums, template
```

Next steps after boilerplate exec.:

```bash
cd my-soil-imports
poetry install                       # installs soil-etl
Copy-Item|cp .env.example .env          # then fill in DB_CONN
poetry run python pipelines/import_example_dataset.py   # dry-run
```

> **Maintainers:** the editable source of the template lives in `project-template/` at the repo root
> and is synced into the packaged copy (`src/soil_etl/_project_template/`) with
> `python scripts/sync_template.py` (use `--check` in CI to detect drift).

---

## Database Connection

Connection resolution priority (first non-null wins):

1. Explicit `database_connection_string` argument to `import_data()`
2. `DB_CONN` environment variable
3. Hydra config directory (`database_config_path` / `CONFIG_PATH` env var)

```python
# Option 1 - direct connection string
mapper.import_data(
    database_connection_string="postgresql+psycopg2://user:password@host:5432/database",
    database_engine_kwargs={"pool_size": 10, "pool_pre_ping": True},
)

# Option 2 - Hydra config files
mapper.import_data(
    database_config_path="configs",
    database_config_name="configs.yaml",
)

# Option 3 - environment variables
import os
os.environ["DB_CONN"] = "postgresql+psycopg2://user:password@host:5432/database"
mapper.import_data()
```

**Environment variables recognized:**

| Variable                     | Description                                                          |
|------------------------------|----------------------------------------------------------------------|
| `DB_CONN`                    | Full SQLAlchemy connection string                                    |
| `DB_USER`                    | Database username (used alongside Hydra config)                      |
| `DB_PASSWORD`                | Database password (used alongside Hydra config)                      |
| `CONFIG_PATH`                | Hydra config directory path                                          |
| `IMPORT_BATCH_SIZE`          | Override batch size for this run (positive integer)                  |
| `EXPORT_RETREATED_DATAFRAME` | Export transformed DataFrame to `*_retreated.xlsx` (`1`/`true`/`yes`) |

---

## `import_data()` Signature

```python
mapper.import_data(
    error_file_path=Path("errors.xlsx"),            # defaults to <dataset_title>_ERROR.xlsx
    dry_run=False,                                   # True: extract + validate only, no DB writes
    database_connection_string="postgresql+...",
    database_config_path="configs",
    database_config_name="configs.yaml",
    database_engine_kwargs={"pool_size": 10},
) -> ImportRunMetrics
```

Returns an `ImportRunMetrics` dataclass:

```python
@dataclass(frozen=True)
class ImportRunMetrics:
    rows_processed: int
    error_rows: int
    generic_results: int
    horizon_results: int
    feature_relationships: int
    sample_information: int
    batches: int
    batch_size: int
    dry_run: bool
    extract_seconds: float
    persist_seconds: float
    total_seconds: float
```

---

## YAML Configuration

The YAML config drives the entire mapping: Excel reading parameters, project metadata, feature of interest
hierarchy, observations, and results.

Reference files (packaged):

| File                                               | Purpose                                            |
|----------------------------------------------------|----------------------------------------------------|
| `src/soil_etl/yaml_mapper/dummy_config_file.yaml`  | Fully-documented boilerplate with all options      |
| `src/soil_etl/yaml_mapper/template.yaml`           | Real-world example with YAML anchors               |
| `src/soil_etl/yaml_mapper/examples/on_master.yaml` | Province-specific example                          |
| `examples/configs/example_config.yaml`             | Additional annotated example                       |

For full documentation of the YAML format, see [docs/README_yaml_format_en.md](docs/README_yaml_format_en.md).

### Top-level sections

```yaml
source:                        # Excel reading params (sheet_name, header, skiprows, null_values...)
export_retreated_dataframe: false
import_batch_size: 1000        # Rows per DB batch (default: 5000)
project:                       # Project metadata (strategy, name, abbrev, province, type...)
partners:                      # Partner organizations
stewards:                      # Data steward contacts
feature_relationships:         # Optional parent-child FOI hierarchies (e.g. ProfileElement -> Sample)
sample_information:            # Optional sample metadata (type, horizon, dimensions)
profile_elements:              # Soil horizons/layers: depth bounds + designation on a PROFILE feature
observations:                  # v2: each observation carries one nested result (1:1)
```

> **v2 data model:** the pipeline targets `om_observation_v2` / `om_result_v2` / `profile_element`. Declare an
> `observations:` list where each observation carries exactly **one** nested `result:` (1:1). The observed property
> (`element_name`) lives on the observation. The observation `type` field is deprecated/ignored. Depth bounds live on
> `profile_elements` (a `PROFILE` feature of interest), not on the sample; a profile element can own an observation or
> be the parent of a `SAMPLE`. Soil horizons/layers are modeled with `profile_elements` (a `PROFILE` FOI carrying depth
> bounds + designation) with observations attached, following the chain `FOI > Profile > Observation(v2) > Result(v2)`
> (or `FOI > Profile > Sample > [FOI/Profile/Sample relations] > Observation(v2) > Result(v2)`). The
> former root keys `results:` / `horizon_results:` have been **removed** (rejected at load time). See
> [docs/README_yaml_format_en.md](docs/README_yaml_format_en.md).

---

## Python Subclass (advanced)

When YAML alone cannot express the required logic, subclass `YamlDatasetMapper` and override properties:

```python
from pathlib import Path
from soil_etl.yaml_mapper import YamlDatasetMapper
from soil_etl.model_bindings import FeatureOfInterestBinding

class MyMapper(YamlDatasetMapper):
    @property
    def _feature_of_interest(self) -> FeatureOfInterestBinding:
        # custom logic here
        ...

mapper = MyMapper(
    config_path=Path("my_config.yaml"),
    data_file_path=Path("data.xlsx"),
)
mapper.import_data(database_connection_string="postgresql+psycopg2://...")
```

See `examples/subclass_runner.py` for a complete example.

---

## Development

```bash
pip install poetry
poetry install --with dev,test
poetry run pytest tests
```

Tests use **testcontainers** with `postgis/postgis:17-3.4` - Docker must be running.

### Linting & formatting

```bash
poetry run black src tests
poetry run ruff check src tests
poetry run isort src tests
```

---

## Build & Publish

```bash
python -m build
twine check dist/*
twine upload dist/*
```

---

## Links

- **Repository**: [gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl](https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl)
- **Issues**: [soil-etl/issues](https://gitlab.com/nrcan-rncan-cfs-scf/lfc-cfl/soil-etl/issues)
- **YAML format reference**: [docs/README_yaml_format_en.md](docs/README_yaml_format_en.md)
- **Testing guide**: [docs/testing.md](docs/testing.md)
- **Project template**: [project-template/README.md](project-template/README.md)

