Metadata-Version: 2.1
Name: subsetter
Version: 0.3.1
Summary: MySQL database subsetting CLI tool
Home-page: http://github.com/msg555/subsetter/
Author: Mark Gordon
Author-email: msg555@gmail.com
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: License :: OSI Approved :: BSD License
Classifier: Operating System :: OS Independent
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlalchemy[mypy] ~=2.0
Requires-Dist: pydantic ~=2.6
Requires-Dist: pyyaml ~=6.0
Requires-Dist: faker ~=19.3
Requires-Dist: typing-extensions
Provides-Extra: all
Requires-Dist: sqlalchemy[postgresql_psycopg2binary,pymysql] ; extra == 'all'
Provides-Extra: postgresql
Requires-Dist: sqlalchemy[postgresql] ; extra == 'postgresql'
Provides-Extra: postgresql_psycopg2binary
Requires-Dist: sqlalchemy[postgresql_psycopg2binary] ; extra == 'postgresql_psycopg2binary'
Provides-Extra: pymysql
Requires-Dist: sqlalchemy[pymysql] ; extra == 'pymysql'

# Subsetter

Subsetter is a Python utility that can be used for subsetting portions of
relational databases. _Subsetting_ is the action extracting a smaller set of rows
from your database that still maintain expected foreign-key relationships
between your data. This can be useful for testing against a small but
realistic dataset or for generating sample data for use in demonstrations.
This tool also supports filtering that allows you to remove/anonymize rows that
may contain sensitive data.

Similar tools include Tonic.ai's platform and [condenser](https://github.com/TonicAI/condenser).
This is meant to be a simple CLI tool that overcomes many of the difficulties in
using `condenser.

## Limitations

The subsetter tool takes an approach of "one table, one query". This means that
the subsetter will sample each table using only a single query. It cannot
support calculating a full transitive closure of foreign key relationships for
schemas that contain cycles. In general, as long as your schema contains no
foreign key cycles and no target is reachable from another target, the subsetter
will be able to automatically generate a plan that can sample your data.

# Usage

## Create a sampling plan

The first step in subsetting a database is to generate a sampling plan. A
sampling plan defines the queries that will be used to sample each table.
You'll want to create a configuration file similar to
[planner_config.example.yaml](planner_config.example.yaml) that tells the
planner what tables you want to sample along with any additional constraints
that should be considered. Then you can create a plan with the below command:

```sh
subsetter plan -c my-config.yaml > plan.yaml
```

If you inspect the generated plan YAML document you will see a syntax tree
that defines how each table will be sampled, potentially referencing other
tables. Queries can reference either source tables or previously sampled tables.
If you need to customize the way that tables are sampled beyond what the planner
can automatically produce this is the place to do it. If needed, you can even
write direct SQL here.

## Sample a database with a plan

The sample sub-command will sample rows from the source database into your
target output (either a database or as json files) using a plan generated
using the plan sub-command. This tool will **not** copy schema from the source
database. Any sampled tables must already exist in the destination database.
Additionally you must pass `--truncate` if you wish to clear any existing data
in the sampled tables that may interfere with the sampling process.

```sh
subsetter sample --config my-sample-config.yaml --plan my-plan.yaml --truncate
```

The sampling process proceeds in three phases:

1. If `--truncate` is specified any tables about to be sampled will be first truncated.
2. Any sampled tables that are referenced by other tables will first be
materialized into temporary tables on the source database.
3. Data is copied for each table from the source to destination.

The sampler also supports filters which allow you to transform and anonymize your
data using simple column filters. See
[sampler_config.sample.yaml](sampler_config.sample.yaml) for more details on what
filters are available and how to configure them.

## Plan and sample in one action

There's also a `subset` subcommand to perform the `plan` and `sample` actions
together. This will automatically feed the generated plan into the sampler,
in addition to ensuring the same source database configuration is used for
each.

```sh
subsetter subset --plan-config my-plan-config.yaml --sample-config my-sample-config.yaml
```

# Sampling Multiplicity

Sampling usually means condensing a large dataset into a semantically consistent
small dataset. However, there are times that what you really want to do is
create a semantically consistent large dataset from your existing data. The
sampler has support for this by setting the multiplicity factor.

Multiplicity works by creating multiple copies of your sampled dataset in your
output database. To ensure these datasets do not collide it remaps all foreign
keys into a new key-space. Note that this process assumes your foreign keys are
opaque integers identifiers.

# FAQ

## How do multiple targets work

When using multiple targets each target table will be sampled entirely
independently unless another target table directly or indirectly depends on some
rows from it through a series of foreign keys. In the later case the subsetter
will sample a union of the rows from the independently sampling of the table and
those rows that other targets depend on.

## How does the subsetter use foreign keys?

The subsetter uses the foreign keys present in the database schema to understand
relationships between data and generate a sampling plan. Foreign key
relationships can be followed in both directions if need be. For example,
suppose there was a `users` and an `orders` table where `orders` had a foreign key
to the `users` table.

If `users` was sampled first the subsetter would sample `orders` from `users` by
sampling all rows from `orders` such that their corresponding user row existed.
This represents the _maximal_ set of rows that can be included without violating
foreign key constraints.

Otherwise if `orders` was sampled first the subsetter would sample `users` from
`orders` by sampling all rows from `users` such that they had at least one
`order`. This represents the _minimal_ set of rows that can be included without
violating foreign key constraints.

In general the subsetter will always sample tables in an order such that all
foreign key relationships to previously sampled tables are going in the same
direction. If they are followed in the forwards direction (as in our first case)
the subsetter will select the _intersection_ of all rows that obey each foreign
key relationship. Otherwise if they are followed in the backwards direction (as
in our second case) the subsetter will select the _union_ of all rows that obey
each foreign key relationship. This strategy ensures no foreign key
relationships are violated in the sampled data.
