SQL Compare Tool for Tie-Outs
This tool generates sql commands that will compare two tables in different databases as long as they are on the same sql server (current limitation). GitHub link https://github.com/waterfield/compare .
Install
To install the compare tool on a machine:
curl https://raw.githubusercontent.com/waterfield/install-compare/main/install.sh | bash
Then you must log out and back in.
Usage
compare my_file.yml # generate script
compare help # get general help
compare help my_file.yml # get help with configuration
compare help TABLE # get options for table
compare new [config] my_file.yml # make a new config
compare new table TABLE # make a new table definition
compare new filter NAME # make a new filter definition
compare update # update the compare tool
Running
Create a file to define your case. See below for a full example. You would run this with:
compare example.yml
Then use the script it produces in SQL Studio or Azure Studio.
# example.yml
# This example compares owner_amounts for two
# different databases, one on an older version
# and another on a newer.
# You would run this example like so:
#
# compare oa_tieout.yml
#
# This would print the script, as well as saving
# a copy to scripts/oa_tieout.sql.
# You can create a new blank template query using:
#
# compare new my_file.yml
# The table is specified at the top level, but
# you could technically override this in one or
# both of your sources.
table: owner_amounts
# Scope defines the scope of data you want to look
# at. You can specify them here, in which case they
# apply to both sources, or make them specific to
# each source (or both!)
scope:
# Both these scopes are defined by a developer
# and can be found at https://github.com/waterfield/compare.
# These particular scopes will end up joining
# to `moments` in the generated script. You can get a list
# of supported scope attributes for your query:
#
# compare help oa_tieout.yml
company: Some Company
acct: 2022-12-01
# To set a global tolerance, use this
tolerance: 0.01
# Or you can override with tolerances for individual columns
tolerances:
quantity: 0.1
value: 0.5
# This section is optional; you can tell the compare
# tool to add, remove, or replace the columns used
# for grouping the data
group_by:
remove:
- source
- status
add:
- value_unit_id
# This section is optional; you can tell the compare
# tool to add, remove, or replace the columns used
# for getting numeric values
values:
replace:
- quantity
- net
# Sources is where you specify what two sets you're
# comparing. Here they're called old and new,
# but you can call them whatever you want; this will
# affect the column naming in the script.
sources:
# The first source just lists the database
old:
database: vef_v10_project
# But the second source has to specify an additional
# scope, to hide some junk data for instance
new:
database: vef_v10_project3
scope:
# Note that `production_date` is not a built-in
# scope option; instead, `compare` assumes that
# this is a column on the comparison table
production_date: NOT IN ('2022-10-01')
Example YML Files
terra_OA.yml gme_payments.yml gme_payable.yml terra_allocated_rev.yml terra_value_rev.yml