Metadata-Version: 2.4
Name: soil-etl
Version: 0.1.0a5
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.1.0-a.2 (alpha) · **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
```

---

## 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
sample_information:            # Optional sample metadata (type, depth, horizon, dimensions)
results:                       # Generic result bindings
horizon_results:               # Horizon-specific results (includes hz_from, hz_to)
```

---

## 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)

