ddiff. Эффективное сравнение данных в разных БД

Как вы поступаете, когда нужно сравнить два больших набора данных? Скорее всего, первое, что вы делаете, – проверяете, одинаковое ли количество строк в каждом наборе и одинаковые ли суммы по некоторым столбцам. Можно еще сравнить количество уникальных значений в одних и тех же столбцах в двух наборах, минимальные и максимальные значения столбцов, сумму длин строк в строковых столбцах.

К сожалению, СУБД не предоставляют агрегатных функций для расчета хэша для набора строк. Поэтому для быстрого и грубого сравнения двух наборов данных приходится использовать возможности имеющихся агрегатных функций. Например:

В одной БД:

select count(*)               as cnt,
    count(distinct item_type) as cnt_type,
    sum(item_code)            as sum_code,
    sum(length(item_name))    as sum_name,
    min(item_code)            as min_code,
    max(item_code)            as max_code
from items
;

В другой БД:

select count(*)               as cnt,
    count(distinct art_type)  as cnt_type,
    sum(art_code)             as sum_code,
    sum(length(name))         as sum_name,
    min(art_code)             as min_code,
    max(art_code)             as max_code
from articles
;

Здесь поля item_code и article_code содержат уникальный код (в данном случае, товара, продукта) и являются первичным ключом соответствующих таблиц.

Если результаты двух приведенных запросов отличаются, нам нужно найти конкретные строки в двух наборах, ответственные на различие. Поскольку таблицы items и articles находятся в разных БД на разных серверах, возможно, физически далеко друг от друга, и, возможно, содержат десятки миллионов строк, то следующий подход или крайне неэффективен или невозможен:

with diff1 as (
    select item_code, item_type, item_name from items
    except
    select art_code, art_type, name from arts
), diff2 as (
    select art_code, art_type, name from arts
    except
    select item_code, item_type, item_name from items
)
select coalesce(item_code, art_code) code,
    item_type, art_type, 
    item_name, name
from diff1 full outer join diff2 on item_code = art_code
;

Как же быть?

Вместо того, чтобы сравнивать все строки одного набора со всеми строками другого набора, разобъем наборы на группы и сравним строки с агрегатами для этих групп. Пусть нам известно, что item_code и art_code имеют значения в диапазоне от 1 до 100000000 (сто миллионов), тогда разобъем наборы на 10000 групп (по 10000 строк максимум).

В одной БД:

select round(item_code, -4)   as code,
    count(*)                  as cnt,
    count(distinct item_type) as cnt_type,
    sum(item_code)            as sum_code,
    sum(length(item_name))    as sum_name,
    min(item_code)            as min_code,
    max(item_code)            as max_code
from items
group by round(item_code, -4)
;

В другой БД:

select round(art_code, -4)    as code,
    count(*)                  as cnt,
    count(distinct art_type)  as cnt_type,
    sum(art_code)             as sum_code,
    sum(length(name))         as sum_name,
    min(art_code)             as min_code,
    max(art_code)             as max_code
from articles
group by round(art_code, -4)
;

Сравним результаты этих запросов, временно полагая, что таблицы items и articles одновременно доступны:

with one as (
    -- в одной БД
    select round(item_code, -4)   as code,
        count(*)                  as cnt,
        count(distinct item_type) as cnt_type,
        sum(item_code)            as sum_code,
        sum(length(item_name))    as sum_name,
        min(item_code)            as min_code,
        max(item_code)            as max_code
    from items
    group by round(item_code, -4)
), two as (
    -- в другой БД
    select round(art_code, -4)    as code,
        count(*)                  as cnt,
        count(distinct art_type)  as cnt_type,
        sum(art_code)             as sum_code,
        sum(length(name))         as sum_name,
        min(art_code)             as min_code,
        max(art_code)             as max_code
    from articles
    group by round(art_code, -4)
)
with diff1 as (
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from one
    except
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from two
), diff2 as (
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from two
    except
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from one
)
select coalesce(d1.code, d2.code) code,
    d1.cnt, diff2.cnt, 
    d1.cnt_type, d2.cnt_type,
    d1.sum_code, d2.sum_code,
    d1.sum_name, d2.sum_name,
    d1.min_code, d2.min_code,
    d1.max_code, d2.max_code
from diff1 d1 full outer join diff2 d2 on d1.code = d2.code
;

Данный запрос может вернуть всего несколько строк, или даже одну строку, например, со значением code = 1050000. Это означает, что для нахождения индивидуальных различающихся строк в наборах данных нам теперь нужно сравнить между собой только строки items, где round(item_code, -4) = 1050000, и строки articles, где round(art_code, -4) = 1050000. А это максимум 10000 строк из каждого набора – вместо 100000000 (ста миллионов) строк:

with diff1 as (
    select item_code, item_type, item_name 
    from items 
    where round(item_code, -4) = 1050000
    except
    select art_code, art_type, name 
    from arts 
    where round(art_code, -4) = 1050000
), diff2 as (
    select art_code, art_type, name 
    from arts 
    where round(art_code, -4) = 1050000
    except
    select item_code, item_type, item_name 
    from items 
    where round(item_code, -4) = 1050000
)
select coalesce(item_code, art_code) code,
    item_type, art_type, 
    item_name, name
from diff1 full outer join diff2 on item_code = art_code
;

Данный запрос покажет нам конкретные строки, ответственные за различия в двух наборах.

Если сравнение 10000 индивидуальных строк кажется расточительным, можно еще сильнее уменьшить количество сравниваемых строк, вначале разбив 10000 строк, среди которых находятся проблемные, на 100 групп (по 100 строк максимум) и найдя различающиеся группы среди них:

with one as (
    -- в одной БД
    select round(item_code, -2)   as code,
        count(*)                  as cnt,
        count(distinct item_type) as cnt_type,
        sum(item_code)            as sum_code,
        sum(length(item_name))    as sum_name,
        min(item_code)            as min_code,
        max(item_code)            as max_code
    from items
    where round(item_code, -4) = 1050000
    group by round(item_code, -2)
), two as (
    -- в другой БД
    select round(art_code, -2)    as code,
        count(*)                  as cnt,
        count(distinct art_type)  as cnt_type,
        sum(art_code)             as sum_code,
        sum(length(name))         as sum_name,
        min(art_code)             as min_code,
        max(art_code)             as max_code
    from articles
    where round(art_code, -4) = 1050000
    group by round(art_code, -2)
)
with diff1 as (
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from one
    except
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from two
), diff2 as (
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from two
    except
    select code, cnt, cnt_type, sum_code, sum_name, min_code, max_code from one
)
select coalesce(d1.code, d2.code) code,
    d1.cnt, diff2.cnt, 
    d1.cnt_type, d2.cnt_type,
    d1.sum_code, d2.sum_code,
    d1.sum_name, d2.sum_name,
    d1.min_code, d2.min_code,
    d1.max_code, d2.max_code
from diff1 d1 full outer join diff2 d2 on d1.code = d2.code
;

Допустим, результатом выполнения этого запроса оказались две строки со значениями code 1051200 и 1051300. Тогда, для нахождения конкретных различающихся строк нам нужно сравнить всего по 200 строк из каждого набора:

with diff1 as (
    select item_code, item_type, item_name 
    from items 
    where round(item_code, -2) in (1051200, 1051300)
    except
    select art_code, art_type, name 
    from arts 
    where round(art_code, -2) in (1051200, 1051300)
), diff2 as (
    select art_code, art_type, name 
    from arts 
    where round(art_code, -2) in (1051200, 1051300)
    except
    select item_code, item_type, item_name 
    from items 
    where round(item_code, -2) in (1051200, 1051300)
)
select coalesce(item_code, art_code) code,
    item_type, art_type, 
    item_name, name
from diff1 full outer join diff2 on item_code = art_code
;

Запросы выше написаны так, как будто таблицы items и articles непосредственно доступны для этих запросов. На практике это далеко не всегда так, и поиск расхождений в наборах данных распадается на два этапа:

1. извлечь данные из двух разных БД разными запросами и сохранить их в одной БД, и
2. сравнить межу собой данные, извлеченные из двух разных БД и сохраненные в одной БД.

Запросы для извлечения данных из разных БД – это запросы (CTE) one и two в приведенных примерах, а запросы для сравнения извлеченных данных - это запросы diff1, diff2 и запрос, выполняющий полное внешнее соединение diif1 и diff2.

Как видим, для нахождения нескольких различающихся строк среди ста миллионов строк оказалось достаточно извлечь из каждой БД всего 10000 + 100 + 200 = 10300 строк тремя последовательными запросами.

Описанный подход сравнения двух наборов данных реализуется утилитой командной строки ddiff. Найти ее можно на github. Утилита написана на python, и конфигурируется с помощью конфиг-файла, который также представляет собой модуль python. Результатом работы утилиты является отчет об обнаруженных расхожениях, сохраненный в файлах html.

Для знакомства с форматом конфиг-файла см. тестовый конфигурационный файл ddiff-test.py в директории cfg.

Ниже приведен фрагмент конфиг-файла для сравнения items и articles из рассмотренного выше примера. Здесь

• "sources" содержит названия двух источников данных (определенных в другой части файла),
• "queries" задает два запроса, для первого и второго источников, соответственно, а
• "pk" определяет поля, уникально идентифицирующие строки, возвращаемые запросами.

    "items": {
        "sources": ["ONE", "TWO"],
        #
        # уровень 1, группы по 10 000 строк
        #
        "queries": [
            """
            select round(item_code, -4)   as code,
                count(*)                  as cnt,
                count(distinct item_type) as cnt_type,
                sum(item_code)            as sum_code,
                sum(length(item_name))    as sum_name,
                min(item_code)            as min_code,
                max(item_code)            as max_code
            from items
            group by round(item_code, -4)
            """,
            """
            select round(art_code, -4)    as code,
                count(*)                  as cnt,
                count(distinct art_type)  as cnt_type,
                sum(art_code)             as sum_code,
                sum(length(name))         as sum_name,
                min(art_code)             as min_code,
                max(art_code)             as max_code
            from articles
            group by round(art_code, -4)
            """
        ],
        "pk": ["code"],
        "items": {
            #
            # уровень 2, группы по 100 строк
            #
            "queries": [
                """
                select round(item_code, -2)   as code,
                    count(*)                  as cnt,
                    count(distinct item_type) as cnt_type,
                    sum(item_code)            as sum_code,
                    sum(length(item_name))    as sum_name,
                    min(item_code)            as min_code,
                    max(item_code)            as max_code
                from items
                where round(item_code, -4) in ({%for row in argrows %}'{{row[0]}}'{{"," if not loop.last}}{%- endfor %})
                group by round(item_code, -2)
                """,
                """
                select round(art_code, -2)    as code,
                    count(*)                  as cnt,
                    count(distinct art_type)  as cnt_type,
                    sum(art_code)             as sum_code,
                    sum(length(name))         as sum_name,
                    min(art_code)             as min_code,
                    max(art_code)             as max_code
                from articles
                where round(art_code, -4) in ({%for row in argrows %}'{{row[0]}}'{{"," if not loop.last}}{%- endfor %})
                group by round(art_code, -2)
                """
            ],
            "pk": ["code"],
            "items": {
                #
                # уровень 3, отдельные строки
                #
                "queries": [
                    """
                    select item_code code, item_type, item_name 
                    from items 
                    where round(item_code, -2) in ({%for row in argrows %}'{{row[0]}}'{{"," if not loop.last}}{%- endfor %})
                    """,
                    """
                    select art_code, art_type, name 
                    from arts 
                    where round(art_code, -2) in ({%for row in argrows %}'{{row[0]}}'{{"," if not loop.last}}{%- endfor %})
                    """
                ],
                "pk": ["code"]
            }
        }
    }

Запросы на каждом уровне – это шаблоны jinja2, при подготовке которых методу render передается список argrows со строками-расхождениями, найденными на предыдущем уровне.

Если следовать вышерассмотренному примеру, то результаты запросов уровня 1 различаются одной строкой с code = 1050000. Следовательно, эта строка передается методу render через argrows для подготовки к выполнению запросов уровня 2. Далее, результаты запросов уровня 2 различаются двумя строками с code 1051200 и 1051300, и эти строки используются при подготовке запросов уровня 3. Наконец, запросы уровня 3 возвращают индивидуальные различающиеся строки.

В общем случае, набор полей, уникально идентифицирующий возвращаемые запросами строки, может отличаться от уровня к уровню, тогда поле "pk" на разных уровнях будет содержать разные списки ключевых полей.

Я использую утилиту ddiff для поиска расхождений между БД хранилища данных (КХД) и другими БД, которые являются источниками данных для КХД. Утилита выполняется каждую ночь с конфиг-файлом, где заданы запросы для нескольких десятков наборов данных. Сгенерированный отчет о расхождениях автоматически отправляется по электронной почте утилитой hedwig (тоже скрипт на python, конфигурируемый с помощью собственного конфиг-файла).