Skip to main content
Skip table of contents

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:

CODE
curl https://raw.githubusercontent.com/waterfield/install-compare/main/install.sh | bash

Then you must log out and back in.

Usage

CODE
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:

CODE
compare example.yml

Then use the script it produces in SQL Studio or Azure Studio.

CODE
# 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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.