Metadata-Version: 2.4
Name: ssb-parquedit
Version: 0.1.0
Summary: SSB Parquedit
License-Expression: MIT
License-File: LICENSE
Author: Team Fellesfunksjoner
Author-email: osn@ssb.no
Maintainer: Statistics Norway, Data enablement Department (724)
Requires-Python: >=3.12
Classifier: Development Status :: 4 - Beta
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Requires-Dist: click (>=8.0.1)
Requires-Dist: duckdb (==1.5.2)
Requires-Dist: gcsfs (>=2026.1.0,<2027.0.0)
Requires-Dist: pandas (>=3.0.0,<4.0.0)
Requires-Dist: polars (>=1.38.1,<2.0.0)
Requires-Dist: pyarrow (>=23.0.1,<24.0.0)
Requires-Dist: tenacity (>=9.1.4,<10.0.0)
Project-URL: Changelog, https://github.com/statisticsnorway/ssb-parquedit/releases
Project-URL: Documentation, https://statisticsnorway.github.io/ssb-parquedit
Project-URL: Homepage, https://github.com/statisticsnorway/ssb-parquedit
Project-URL: Repository, https://github.com/statisticsnorway/ssb-parquedit
Description-Content-Type: text/markdown

# SSB Parquedit

[![PyPI](https://img.shields.io/pypi/v/ssb-parquedit.svg)][pypi status]
[![Status](https://img.shields.io/pypi/status/ssb-parquedit.svg)][pypi status]
[![Python Version](https://img.shields.io/pypi/pyversions/ssb-parquedit)][pypi status]
[![License](https://img.shields.io/pypi/l/ssb-parquedit)][license]

[![Documentation](https://github.com/statisticsnorway/ssb-parquedit/actions/workflows/docs.yml/badge.svg)][documentation]
[![Tests](https://github.com/statisticsnorway/ssb-parquedit/actions/workflows/tests.yml/badge.svg)][tests]
[![Coverage](https://sonarcloud.io/api/project_badges/measure?project=statisticsnorway_ssb-parquedit&metric=coverage)][sonarcov]
[![Quality Gate Status](https://sonarcloud.io/api/project_badges/measure?project=statisticsnorway_ssb-parquedit&metric=alert_status)][sonarquality]

[![pre-commit](https://img.shields.io/badge/pre--commit-enabled-brightgreen?logo=pre-commit&logoColor=white)][pre-commit]
[![Black](https://img.shields.io/badge/code%20style-black-000000.svg)][black]
[![Ruff](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/astral-sh/ruff/main/assets/badge/v2.json)](https://github.com/astral-sh/ruff)
[![Poetry](https://img.shields.io/endpoint?url=https://python-poetry.org/badge/v0.json)][poetry]

[pypi status]: https://pypi.org/project/ssb-parquedit/
[documentation]: https://statisticsnorway.github.io/ssb-parquedit
[tests]: https://github.com/statisticsnorway/ssb-parquedit/actions?workflow=Tests
[sonarcov]: https://sonarcloud.io/summary/overall?id=statisticsnorway_ssb-parquedit
[sonarquality]: https://sonarcloud.io/summary/overall?id=statisticsnorway_ssb-parquedit
[pre-commit]: https://github.com/pre-commit/pre-commit
[black]: https://github.com/psf/black
[poetry]: https://python-poetry.org/

A Python package for manually editing tabular data stored as Parquet files on [DaplaLab](https://manual.dapla.ssb.no/) — Statistics Norway's cloud data platform. Built on top of [DuckDB](https://duckdb.org/) and the [DuckLake](https://ducklake.select/) catalog, it provides a clean Python interface for creating tables, inserting data, querying results and editing rows directly from Google Cloud Storage (GCS).
Intended for single-table editing. Does not support primary- and foreign keys.

---

## Table of Contents

- [Features](#features)
- [Requirements](#requirements)
- [Installation](#installation)
- [Usage](#usage)
  - [Basic setup](#basic-setup)
  - [Creating a table](#creating-a-table)
  - [Inserting data](#inserting-data-in-an-existing-table)
  - [Editing a row](#editing-a-row)
  - [Deleting rows](#deleting-rows)
  - [Querying data](#querying-data)
  - [Counting rows](#counting-rows)
  - [Checking table existence](#checking-table-existence)
  - [List all tables](#list-all-tables)
  - [List edits](#list-edits)
  - [Drop table](#drop-table)
- [Maintenance](#maintenance)
  - [Flush inlined data](#flush-inlined-data)
  - [Merge adjacent files](#merge-adjacent-files)
  - [Export catalog](#export-catalog)
  - [Import catalog](#import-catalog)
- [Advanced](#advanced)
  - [Accessing the raw DuckDB connection](#accessing-the-raw-duckdb-connection)
  - [Setting up local connection](#setting-up-local-connection)
  - [Restoring a local catalog backup with GCS data](#restoring-a-local-catalog-backup-with-gcs-data)
- [Project structure](#project-structure)
- [Contributing](#contributing)
- [License](#license)

---

## Features

- **Auto-configuration** — reads Dapla environment variables to build connection config automatically
- **DuckLake catalog integration** — metadata stored in PostgreSQL, data stored in GCS
- **Create tables** from a pandas or polars DataFrame, a JSON Schema dict, or an existing GCS Parquet file
- **Insert data** from a pandas or polars DataFrame or a `gs://` Parquet path — rows are automatically assigned a unique `rowid` within a table
- **Edit data** - Update value(s) in a single row by its rowid.
- **Delete rows** - Delete one or more rows matching a where-condition, logged individually to the changelog.
- **Query tables** with where-conditions, column selection, sorting, pagination, and multiple output formats (`pandas`, `polars`, `pyarrow`)
- **Find edits** Retrieve historical column-level edits for a specified table
- **Count rows**
- **Check table existence** safely
- **Partition tables** by one or more columns
- **Export/import catalog** back up the DuckLake metadata catalog to a DuckDB file on GCS and restore it later


---

## Requirements

- Python `>=3.12`
- Access to a DaplaLab environment
- A PostgreSQL instance reachable at `localhost` for DuckLake metadata storage
- A GCS bucket following the naming convention `ssb-{team-name}-data-produkt-{environment}`

### Python dependencies

| Package    | Version              |
|------------|----------------------|
| `duckdb`   | `==1.5.2`            |
| `pandas`   | `>=3.0.0, <4.0.0`   |
| `polars`   | `>=1.38.1, <2.0.0`  |
| `pyarrow`  | `>=23.0.1, <24.0.0` |
| `gcsfs`    | `>=2026.1.0, <2027.0.0` |
| `click`    | `>=8.0.1`            |
| `tenacity` | `>=9.1.4,<10.0.0`    |

---

## Installation
```console
poetry add ssb-parquedit
```

---

## Usage

### Basic setup

`ParquEdit` reads its connection configuration automatically from Dapla-environment variables.
```python
from ssb_parquedit import ParquEdit

# Auto-configure from environment
con = ParquEdit()
```

### Creating a table

Tables can be created from a pandas or polars DataFrame schema, a JSON Schema dict, or an existing Parquet file.

```python
import pandas as pd

df = pd.DataFrame({"name": ["Alice", "Bob"], "age": [30, 25]})

# Option 1: Create from DataFrame (empty — schema only)
con.create_table(table_name="my_table_1",
                 source=df,
                 product_name="my-product",
                 user_defined_id=["name"])
```
```python

# Option 2: Create and immediately populate with data
con.create_table(table_name="my_table_2",
                 source=df,
                 product_name="my-product",
                 user_defined_id=["name"],
                 fill=True)
```
```python

# Option 3: Create from a JSON Schema
schema = {
    "properties": {
        "name": {"type": "string"},
        "age":  {"type": "integer"},
    }
}
con.create_table(table_name="my_table_3",
                 source=schema,
                 product_name="my-product",
                 user_defined_id=["name"])
```
```python

# Option 4: Create from an existing GCS Parquet file (schema inferred from file)
con.create_table(table_name="my_table_4",
                 source="gs://my-bucket/path/to/file.parquet",
                 product_name="my-product",
                 user_defined_id=["id", "year"])
```
```python

# Option 5: Create with partitioning and immediately populate with data
con.create_table(table_name="my_table_5",
                 source=df,
                 product_name="my-product",
                 part_columns=["age"],
                 user_defined_id=["name"],
                 fill=True)
```
```python

# Option 6: Create from a polars DataFrame
import polars as pl

df_polars = pl.DataFrame({"name": ["Alice", "Bob"], "age": [30, 25]})
con.create_table(table_name="my_table_6",
                 source=df_polars,
                 product_name="my-product",
                 user_defined_id=["name"],
                 fill=True)
```

> **Notes:**
> - `product_name` is required and is stored as a comment on the table.
> - `table_name` must be lowercase, start with a letter or underscore, contain only lowercase letters, numbers, and underscores, and be at most 20 characters.
> - `user_defined_id` — a list of columns that together uniquely identify a row in a table, used to mimic a primary key.
> - Column names must not exceed 63 bytes when UTF-8 encoded (PostgreSQL's identifier limit). Longer names — easy to hit with non-ASCII characters like `æ`/`ø`/`å`, which take 2 bytes each — raise a `ValueError` at table creation instead of silently corrupting the table later.

### Inserting data in an existing table
```python
# Insert from a pandas DataFrame
con.insert_data(table_name="my_table_1",
                 source=df)
```
```python
# Insert from a polars DataFrame
con.insert_data(table_name="my_table_6",
                 source=df_polars)
```
```python
# Insert from a GCS Parquet file
con.insert_data(table_name="my_table_4",
                 source="gs://my-bucket/path/to/file.parquet")
```
Both pandas and polars DataFrames are supported as `source` for `create_table()` and `insert_data()`.

Each inserted row is automatically assigned a unique `rowid` within the table


### Editing a row
`edit()` updates exactly one row — identified by its `rowid` — and logs the change reason and comment to the DuckLake snapshot.
```python
# First look up the rowid of the row you want to edit
result = con.view(table_name="my_table_1",
                  where="name = 'Alice'")
rowid = result["rowid"].iloc[0]

# Then edit it
con.edit(
    table_name="my_table_1",
    rowid=rowid,
    changes={"name":"Alice B", "age": 33},
    change_event_reason="REVIEW",
    change_comment="Corrected name and age after data review",
)
```
`changes` is a dict of `{column_name: new_value}` pairs.

`change_event_reason` must be one of: `OTHER_SOURCE`, `REVIEW`, `OWNER`, `MARGINAL_UNIT`, `DUPLICATE`, `OTHER`


### Deleting rows
`delete_row()` selects rows with a `where` clause — the same syntax as `view()` — and deletes all matching rows. Deletions of multiple rows are logged as one entry in the changelog `get_edits()`- The where-clause used and number of affected rows are logged.
```python
# Delete a single row by its rowid
con.delete_row(
    table_name="my_table_1",
    where="rowid = 1",
    change_event_reason="REVIEW",
    change_comment="Removed duplicate entry",
)
```
```python
# Delete multiple rows at once
con.delete_row(
    table_name="my_table_1",
    where="age < 18",
    change_event_reason="OTHER",
    change_comment="Removed underage entries",
)
```
`change_event_reason` must be one of: `OTHER_SOURCE`, `REVIEW`, `OWNER`, `MARGINAL_UNIT`, `DUPLICATE`, `OTHER`


### Querying data
```python
# View all rows (returns pandas DataFrame by default)
result = con.view(table_name="my_table_1")
```
```python
# Filter with a WHERE clause
result = con.view(table_name="my_table_1", where="age > 25")
result = con.view(table_name="my_table_1", where="name = 'Alice' AND age >= 30")
```
```python
# Limit and offset (pagination)
result = con.view(table_name="my_table_1",
                  limit=10,
                  offset=2)
```
```python
# Select specific columns
result = con.view(table_name="my_table_1",
                  columns=["name", "age"])
```
```python
# Sort results
result = con.view(table_name="my_table_1",
                   order_by="age DESC")
```
```python
# Return as polars or pyarrow
result = con.view(table_name="my_table_1",
                   output_format="polars")

result = con.view(table_name="my_table_1",
                   output_format="pyarrow")
```

### Counting rows
```python
total = con.count(table_name="my_table_1",
                   where="name='Alice'")
```

### Checking table existence
```python
if con.exists(table_name="my_table_1"):
    print("Table found")
```

### List all tables
```python
con.list_tables()
```

### List edits
`get_edits()` - Retrieves the full changelog for a table by reading DuckLake snapshot metadata.
Each row represents a single edit, with columns for who made the change, when,
the reason, which row was affected (identified by its unique key), and the old
and new values for all modified columns.

Optionally filter by table name, or omit it to get the changelog for all tables.
```python
# All edits for a specific table
df = con.get_edits(table_name="my_table")

# All edits across all tables
df = con.get_edits()
```

The returned DataFrame includes these changelog columns:

| Column | Description |
|---|---|
| `snapshot_time` | Timestamp of the edit |
| `changed_by` | User who made the edit |
| `change_event_reason` | Reason code (e.g. `REVIEW`, `OWNER`) |
| `change_comment` | Free-text comment from the editor |
| `table_name` | Table the edit was made on |
| `rowid` | Internal row identifier (`NaN` for deletions)|
| `user_defined_id` | Business key values identifying the row (`None` for deletions) |
| `old_values` | Dict of column → old value for changed columns (`None` for deletions) |
| `new_values` | Dict of column → new value for changed columns (`None` for deletions) |
| `where_clause` | Where-clause used on deletions (`None` for updates) |
| `change_type` | Type of change (`UPDATE` or `DELETE`) |
| `affected_rows` | Number of rows updated or deleted |
| `product_name` | Product name the table belongs to |

### Drop table
`drop_table()` - Drops a table from the DuckLake catalog. By default, only removes the table from the catalog. DuckLake preserves data files and snapshot history, so edit history remains accessible via get_edits() after a normal drop.
When cleanup=True, additionally expires snapshots and deletes GCS data files. This permanently destroys all history and cannot be undone.

```python
# Removes the table from the catalog
con.drop_table(table_name="my_table")
```
```python
# Removes the table from the catalog, expires snapshots and deletes data files
con.drop_table(table_name="my_table", cleanup=True)
```

---

## Maintenance

### Flush inlined data
Flush inlined data materializes inlined rows into Parquet files for a table. This is a maintenance operation for workloads with frequent small writes, helping keep storage layout efficient and query performance stable. It does not change table values, only how data is physically stored. The operation is safe to run repeatedly: Running it when nothing is pending has no effect.
```python
# Flushes inlined data for table 'my_table'
con.flush_inlined_table(table_name="my_table")
```

### Merge adjacent files
Merge adjacent files compacts a table’s small Parquet files into fewer, larger files. This is a maintenance step for tables that receive many small writes, improving scan efficiency and reducing file-management overhead. It preserves table data and history semantics, changing only physical file layout. The operation is safe to run repeatedly: Running it when nothing is mergeable has no effect.
```python
# Merge adjacent files for table 'my_table'
con.merge_adjacent_files(table_name="my_table")
```

### Export catalog
`export_catalog()` backs up the DuckLake metadata catalog. It flushes and merges inlined data for every table, then copies all tables from the PostgreSQL-backed catalog schema into a DuckDB file, which is uploaded to GCS. Returns the full GCS path (including filename) of the exported backup file.
```python
# Export using the default path ({data_path}/catalog-export)
backup_path = con.export_catalog()

# Export to a custom GCS path
backup_path = con.export_catalog(export_path="gs://bucket/backups")
```

### Import catalog
`import_catalog()` restores the DuckLake metadata catalog from a backup file produced by `export_catalog()`. For every table found in the backup, existing rows in the catalog are deleted and replaced with the backed-up rows.
```python
# Restore the catalog from a backup file
con.import_catalog(backup_file_path="gs://bucket/backups/20250101_120000_my_schema.duckdb")
```

## Advanced

### Accessing the raw DuckDB connection

`ParquEdit` wraps a `DuckDBConnection`, which exposes the underlying `duckdb.DuckDBPyConnection` via its `.raw` property. This is useful when integrating with libraries that require a native DuckDB connection, such as [Ibis](https://ibis-project.org/).

```python
import ibis
from ssb_parquedit import ParquEdit

con = ParquEdit()
raw = con._get_connection().raw  # duckdb.DuckDBPyConnection

ibis_conn = ibis.duckdb.connect(conn=raw)
table = ibis_conn.table("my_table_1")
```

> **Notes:**
> - `_get_connection()` is an internal method. The raw connection shares state with `ParquEdit` — closing either will affect both. Do not close the raw connection manually while `ParquEdit` is still in use.
> - When using the raw connection, the user is resposible to provide the required information that `ParquEdit`-methods gives. E.g when creating and editing tables.


### Setting up local connection
Create a ParquEdit instance backed by a persistent local SQLite catalog. Useful for local development and testing without GCS or PostgreSQL access. The catalog and data files are stored at ``path`` and persist across sessions. The directory is created if it does not already exist.
```python
con = ParquEdit().local(path="/home/onyxia/work/")
```

### Restoring a local catalog backup with GCS data
`ParquEdit.local_with_gcs_data()` attaches a local DuckDB catalog file (e.g. one produced by [`export_catalog()`](#export-catalog)) while the actual Parquet data still lives on GCS. Useful for inspecting or restoring from a DuckLake catalog backup without needing a live PostgreSQL connection. Must be used in DaplaLab to get access to GCS-buckets.
```python
con = ParquEdit.local_with_gcs_data(catalog_path="localcopy.duckdb")
```

---

## Project structure
```text
src/ssb_parquedit/
├── parquedit.py            # ParquEdit facade — main public API
├── connection.py           # DuckDB + DuckLake catalog connection management
├── ddl.py                  # DDL operations (CREATE TABLE, partitioning)
├── dml.py                  # DML operations (INSERT, EDIT, DELETE)
├── query.py                # Query operations (SELECT, COUNT, EXISTS)
├── maintenance.py          # Maintenance operations (flush inlined data, merge adjacent files)
├── catalogexportimport.py  # Catalog backup/restore (export/import to/from GCS)
├── functions.py            # Environment helpers (Dapla config auto-detection)
├── local.py                # Local DuckDB connection backed by SQLite (dev/testing)
├── local_backup.py         # Local DuckDB catalog file + GCS-hosted data connection
└── utils.py                # Schema utilities and SQL sanitization

```

---

## Contributing

Contributions are very welcome. To learn more, see the [Contributor Guide].

---

## License

Distributed under the terms of the [MIT license][license]. SSB Parquedit is free and open source software.

---

## Issues

If you encounter any problems, please [file an issue] along with a detailed description.

---

## Credits

This project was generated from [Statistics Norway]'s [SSB PyPI Template]. Maintained by Team Fellesfunksjoner at Statistics Norway (Data Enablement Department 724).

[statistics norway]: https://www.ssb.no/en
[pypi]: https://pypi.org/
[ssb pypi template]: https://github.com/statisticsnorway/ssb-pypitemplate
[file an issue]: https://github.com/statisticsnorway/ssb-parquedit/issues
[pip]: https://pip.pypa.io/

<!-- github-only -->

[license]: https://github.com/statisticsnorway/ssb-parquedit/blob/main/LICENSE
[contributor guide]: https://github.com/statisticsnorway/ssb-parquedit/blob/main/CONTRIBUTING.md
[reference guide]: https://statisticsnorway.github.io/ssb-parquedit/reference.html

