ssb-parquedit 0.0.4

SSB Parquedit

SSB Parquedit

A Python package for manually editing tabular data stored as Parquet files on DaplaLab — Statistics Norway's cloud data platform. Built on top of DuckDB and the DuckLake catalog, it provides a clean, SQL-injection-safe Python interface for creating tables, inserting data, and querying results directly from Google Cloud Storage (GCS). Intended use on single-table editing. Does not support primary- and foreign keys.

---

Features

• Create tables from a pandas DataFrame, a JSON Schema dict, or an existing GCS Parquet file
• Insert data from a pandas DataFrame or a gs:// Parquet path — rows are automatically assigned a unique _id (UUID)
• Query tables with structured filters, column selection, sorting, pagination, and multiple output formats (pandas, polars, pyarrow)
• Count rows with optional structured filter conditions
• Check table existence safely
• Partition tables by one or more columns
• DuckLake catalog integration — metadata stored in PostgreSQL, data stored in GCS
• SQL injection prevention — all user-supplied filter values are parameterized; column names, table names, and ORDER BY clauses are validated against strict allowlists
• Auto-configuration — reads Dapla environment variables to build connection config automatically

---

Requirements

• Python >=3.11, <4.0
• Access to a DaplaLab environment with the following environment variables set:
  • DAPLA_GROUP_CONTEXT — e.g. dapla-ffunk-developers
  • DAPLA_ENVIRONMENT — e.g. test or prod
• 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.4.1
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

---

Installation

poetry add ssb-parquedit

---

Usage

Basic setup

ParquEdit reads its connection configuration automatically from Dapla environment variables. You can also pass a custom config dict.

from ssb_parquedit import ParquEdit

# Auto-configure from environment
con = ParquEdit()

# Or pass a custom config
con = ParquEdit(config={
    "dbname": "my-database",
    "dbuser": "[e-mail]",
    "data_path": "gs://my-bucket/.parquedit_data",
    "catalog_name": "my_catalog",
    "metadata_schema": "my_schema",
})

Creating a table

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

import pandas as pd

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

# Create table from DataFrame (empty — schema only)
con.create_table("my_table", source=df, product_name="my-product")

# Create and immediately populate with data
con.create_table("my_table", source=df, product_name="my-product", fill=True)

# Create from a JSON Schema
schema = {
    "properties": {
        "name": {"type": "string"},
        "age":  {"type": "integer"},
    }
}
con.create_table("my_table", source=schema, product_name="my-product")

# Create from an existing GCS Parquet file (schema inferred from file)
con.create_table("my_table", source="gs://my-bucket/path/to/file.parquet", product_name="my-product")

# Create with partitioning
con.create_table("my_table", source=df, product_name="my-product", part_columns=["age"])

Note: product_name is required and is stored as a comment on the table. Table names must be lowercase, start with a letter or underscore, and contain only lowercase letters, numbers, and underscores (max 20 characters).

Inserting data

# Insert from a DataFrame
con.insert_data("my_table", source=df)

# Insert from a GCS Parquet file
con.insert_data("my_table", source="gs://my-bucket/path/to/file.parquet")

Each inserted row is automatically assigned a unique _id (UUID string).

Querying data

# View all rows (returns pandas DataFrame by default)
result = con.view("my_table")

# Limit and offset (pagination)
result = con.view("my_table", limit=10, offset=20)

# Select specific columns
result = con.view("my_table", columns=["name", "age"])

# Sort results
result = con.view("my_table", order_by="age DESC")

# Return as polars or pyarrow
result = con.view("my_table", output_format="polars")
result = con.view("my_table", output_format="pyarrow")

Filtering

Filters are structured dicts — never raw SQL strings — ensuring SQL injection safety.

# Single condition
con.view("my_table", filters={"column": "age", "operator": ">", "value": 25})

# Multiple conditions (implicit AND)
con.view("my_table", filters=[
    {"column": "age", "operator": ">", "value": 25},
    {"column": "name", "operator": "LIKE", "value": "A%"},
])

# Explicit AND / OR
con.view("my_table", filters={
    "or": [
        {"column": "name", "operator": "=", "value": "Alice"},
        {"column": "name", "operator": "=", "value": "Bob"},
    ]
})

# IN operator
con.view("my_table", filters={"column": "age", "operator": "IN", "value": [25, 30, 35]})

# BETWEEN operator
con.view("my_table", filters={"column": "age", "operator": "BETWEEN", "value": [20, 40]})

# NULL checks
con.view("my_table", filters={"column": "name", "operator": "IS NOT NULL"})

Supported operators: =, !=, <>, <, >, <=, >=, LIKE, IN, NOT IN, BETWEEN, IS NULL, IS NOT NULL.

Counting rows

total = con.count("my_table")
active_adults = con.count("my_table", filters=[
    {"column": "age", "operator": ">=", "value": 18},
])

Checking table existence

if con.exists("my_table"):
    print("Table found")

List all tables

con.list_tables()

---

Security

SSB Parquedit is designed with SQL injection prevention as a first-class concern. See SQL_INJECTION_PREVENTION.md and STRUCTURED_FILTERS.md for a detailed description of the sanitization strategy.

Key points:

• All filter values are passed as parameterized query parameters (never interpolated into SQL strings)
• Column names, table names, and ORDER BY clauses are validated against strict allowlists before being used in query construction
• Raw SQL string filters are not accepted

---

Project structure

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)
├── query.py          # Query operations (SELECT, COUNT, EXISTS)
├── functions.py      # Environment helpers (Dapla config auto-detection)
└── 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. 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).