DOCSEARCHbyAlgolia
Submit Docs

reladiff

repository·master·Indexed 19 days ago

https://github.com/erezsh/reladiff

A high-performance command-line tool and Python library (v0.6.0) for diffing large datasets across different databases. It minimizes data transfer by performing calculations within the database, supporting massive datasets of billions of rows. It features cross-database diffing via hashes and intra-database diffing via joins, with support for drivers including PostgreSQL, MySQL, ClickHouse, Presto, Trino, Vertica, DuckDB, Snowflake, and Oracle.

Tokens
15.3K
Snippets
40
Records
60
Agent score
68%

What's inside reladiff

  1. Overview of Reladiff

    master

    Reladiff is a high-performance tool and library designed for diffing large datasets across different databases. It achieves high performance by executing diff calculations within the database itself, which minimizes data transfer.

    Reladiff supports two primary modes of operation:

    1. Cross-Database Diff: Uses a divide-and-conquer algorithm based on matching hashes to identify modified segments. It only downloads the necessary data for comparison, making it highly efficient when differences are minimal. It can handle precision mismatches (e.g., different timestamp precisions) by rounding according to database specifications.
    2. Intra-Database Diff: When both tables are in the same database, Reladiff uses a join operation for comparison. This mode supports materializing the diff into a local table and collecting extra statistics.

    Reladiff is threaded for performance, highly configurable, and outputs both JSON and git-like diffs (+ and -) for easy integration into CI/CD pipelines.

  2. How Cross-DB Diff works in Reladiff

    master

    Reladiff uses a divide-and-conquer algorithm based on hashing to compare tables across different databases.

    1. Segmentation: The table is divided into segments based on the --bisection-factor.
    2. Checksumming: Reladiff computes checksums for each segment in both databases. This pushes the computation into the databases, minimizing data transfer.
    3. Bisection: If a segment's checksums mismatch, Reladiff subdivides that segment into smaller pieces and repeats the process.
    4. Terminal Comparison: Once a segment's size falls below the --bisection-threshold, Reladiff pulls all rows from that segment in both databases and performs a memory-based comparison to identify the specific differing rows.

  3. Tune Cross-DB Diff with bisection parameters

    master

    To optimize the performance of cross-database comparisons, you can tune the following parameters:

    • --bisection-factor: Controls how many segments the table is initially split into. For very large tables, increasing this value can help prevent timeouts.
    • --bisection-threshold: Defines the segment size at which Reladiff stops subdividing and instead pulls all rows for a local in-memory comparison. If you expect a high volume of changes, consider increasing this value.

  4. Configure database driver dependencies in pyproject.toml

    master

    To allow users to install your driver's specific dependencies without bloating the core installation, define them in the [tool.poetry.extras] section of pyproject.toml.

    Example for a PostgreSQL driver:

    [tool.poetry.extras]
postgresql = ["psycopg2"]

    Users can then install it using:

    pip install 'reladiff[postgresql]'

  5. Register and test a new database driver

    master

    After implementing the driver, you must register it in the test suite to ensure coverage.

    1. Register in test_database_types.py

    Add your class to the DATABASE_TYPES dictionary. The key is the class, and the value is a dictionary mapping supported categories (int, datetime, float, uuid) to lists of database-specific type strings.

    DATABASE_TYPES = {
    ... 
    db.PostgreSQL: {
        "int": [ "int", "bigint" ],
        "datetime": [
            "timestamp(6) without time zone",
            "timestamp(3) without time zone",
            "timestamp(0) without time zone",
            "timestamp with time zone",
        ],
        ...
    },
}

    2. Run Tests

    Use unittest or unittest-parallel to execute the tests.

    Debugging Tips:

    • Set the environment variable LOG_LEVEL=DEBUG to see all executed queries and detected column types.
    • Use the -f flag with unittest to stop on the first error.
    • Use the -k flag to run a specific test case: python -m unittest -k test_name.

  6. Use reladiff from the command line

    master

    You can run reladiff directly from your shell using two different syntaxes depending on whether your tables are in the same or different databases.

    Cross-DB diff (using hashes)

    Use this when comparing tables across two different database connections:

    reladiff  DB1_URI  TABLE1_NAME  DB2_URI  TABLE2_NAME  [OPTIONS]

    Same-DB diff (using outer join)

    Use this when both tables reside within the same database connection:

    reladiff  DB_URI  TABLE1_NAME  TABLE2_NAME  [OPTIONS]

    Note: DB_URI can be a SQLAlchemy-formatted database URL or a named database definition from a configuration file. It is recommended to wrap URLs in quotes to prevent shell syntax collisions.

    # Cross-DB diff
reladiff  "postgresql://user:pass@host/db1"  table1  "mysql://user:pass@host/db2"  table2

# Same-DB diff
reladiff  "postgresql://user:pass@host/db"  table1  table2

  7. Install BigQuery drivers for Reladiff

    master

    Reladiff does not automatically install BigQuery drivers via the [all] or specific driver extras. To use BigQuery with Reladiff, you must install the official Google Cloud BigQuery driver manually:

    pip install google-cloud-bigquery

  9. Implement a new database driver for Reladiff

    master

    To add support for a new database, you must implement a database module in the reladiff/databases directory. A driver consists of a Dialect (to normalize/cast fields like Numbers or Timestamps) and a Database class (to handle connections and querying).

    Note: This guide is considered out-of-date. New databases should ideally be added via Sqeleton.

    Implementation Steps:

    1. Define Dependencies: Add 3rd party libraries to pyproject.toml under [tool.poetry.extras] so users can install them via pip install 'reladiff[your_driver]'.
    2. Choose a Base Class:
      • Inherit from base.ThreadedDatabase for cursor-based connections (e.g., MySQL, PostgreSQL) that require a connection pool to support multithreading.
      • Inherit from base.Database for cloud databases (e.g., Snowflake, BigQuery) that support simultaneous queries from any thread.
    3. Handle Imports: Do not import 3rd party libraries at the module level. Use the @import_helper("package_name") decorator to import and initialize them within a function to provide informative error messages if the dependency is missing.
    4. Implement Core Methods: Implement _query(), schema retrieval methods, and type mapping (see details below).
    5. Add Tests: Register your driver in tests/test_database_types.py and run tests using unittest or unittest-parallel.

  10. Install Reladiff with database drivers

    master

    Reladiff uses pip's "extra" syntax to install specific database drivers alongside the core library. It is highly recommended to install Reladiff within a virtual environment because the drivers introduce many dependencies.

    Install all supported drivers

    You can install all available drivers at once using the [all] extra:

    pip install 'reladiff[all]'

    This is equivalent to:

    pip install reladiff[duckdb,mysql,postgresql,snowflake,presto,oracle,trino,clickhouse,vertica]

    Install specific drivers

    To minimize dependencies, you can install only the drivers you need by comma-separating them inside the brackets. For example, to diff between PostgreSQL and DuckDB:

    pip install 'reladiff[duckdb,postgresql]'

  12. Optimize Reladiff performance

    master

    Use these strategies to improve the speed and efficiency of your diff operations:

    • Indexing: Ensure you have indexes on the columns being compared (preferably a compound index). Use --key-column for the primary key and --update-column for a timestamp/version column.
    • Concurrency: Increase the number of simultaneous queries per database using --threads.
    • Early Exit: If you only need to know if a change exists rather than finding every specific row, use --limit 1.
    • Column Selection: Minimize the number of columns being verified using --columns. Verifying only an updated_at column is much faster than verifying all columns, including large serialized fields like JSON.
    • Interactive Mode: Use --interactive to see the EXPLAIN plan for the checksum queries before they execute. This helps verify if your queries are correctly utilizing indexes.