Эффективный способ сравнить две таблицы в BigQuery

Question

Мне интересно сравнить, содержат ли две таблицы одинаковые данные.

Я мог бы сделать это так:

#standardSQL
SELECT
    key1, key2
FROM
(
    SELECT 
    table1.key1,
    table1.key2,
    table1.column1 - table2.column1 as col1,
    table1.col2 - table2.col2 as col2
    FROM
        `table1` AS table1
    LEFT JOIN
        `table2` AS table2
    ON
        table1.key1 = table2.key1
    AND
        table1.key2 = table2.key2
)
WHERE 
    col1 != 0
OR
    col2 != 0

Но когда я хочу сравнить все числовые столбцы, это довольно сложно, особенно если я хочу сделать это для нескольких комбинаций таблиц.

Поэтому мой вопрос: знает ли кто-нибудь о возможности перебирать все числовые столбцы и ограничивать результирующий набор теми ключами, где любое из этих различий не равно нулю?

Answer 1

Во-первых, я хочу поднять проблемы с вашим исходным запросом

Основные проблемы: 1) использование LEFT JOIN; 2) используя col!= 0

Ниже показано, как его следует изменить, чтобы действительно отразить ВСЕ различия из обеих таблиц.
Запустите исходный запрос и ниже одного - и, надеюсь, вы увидите разницу

#standardSQL
SELECT key1, key2
FROM
(
    SELECT 
    IFNULL(table1.key1, table2.key1) key1,
    IFNULL(table1.key2, table2.key2) key2,
    table1.column1 - table2.column1 AS col1,
    table1.col2 - table2.col2 AS col2
    FROM `table1` AS table1
    FULL OUTER JOIN `table2` AS table2
    ON table1.key1 = table2.key1
    AND table1.key2 = table2.key2
)
WHERE IFNULL(col1, 1) != 0
OR    IFNULL(col2, 1) != 0

или вы можете просто попробовать запустить исходную и выше версию с фиктивными данными, чтобы увидеть разницу

#standardSQL
WITH `table1` AS (
  SELECT 1 key1, 1 key2, 1 column1, 2 col2 UNION ALL
  SELECT 2, 2, 3, 4 UNION ALL
  SELECT 3, 3, 5, 6
), `table2` AS (
  SELECT 1 key1, 1 key2, 1 column1, 29 col2 UNION ALL
  SELECT 2, 2, 3, 4 UNION ALL
  SELECT 4, 4, 7, 8
)
SELECT key1, key2
FROM
(
    SELECT 
    IFNULL(table1.key1, table2.key1) key1,
    IFNULL(table1.key2, table2.key2) key2,
    table1.column1 - table2.column1 AS col1,
    table1.col2 - table2.col2 AS col2
    FROM `table1` AS table1
    FULL OUTER JOIN `table2` AS table2
    ON table1.key1 = table2.key1
    AND table1.key2 = table2.key2
)
WHERE IFNULL(col1, 1) != 0
OR    IFNULL(col2, 1) != 0   

Во-вторых, ниже очень упростит ваш общий запрос

#standardSQL
SELECT 
  IFNULL(table1.key1, table2.key1) key1,
  IFNULL(table1.key2, table2.key2) key2
FROM `table1` AS table1
FULL OUTER JOIN `table2` AS table2
ON table1.key1 = table2.key1
AND table1.key2 = table2.key2
WHERE TO_JSON_STRING(table1) != TO_JSON_STRING(table2)  

Вы можете проверить это с тем же примером фиктивных данных, что и выше
Примечание: в этом решении вам не нужно выбирать конкретные столбцы - просто сравните все столбцы! но если вам нужно сравнить только определенные столбцы - вам все равно нужно будет выбрать их, как показано ниже

#standardSQL
SELECT 
  IFNULL(table1.key1, table2.key1) key1,
  IFNULL(table1.key2, table2.key2) key2
FROM `table1` AS table1
FULL OUTER JOIN `table2` AS table2
ON table1.key1 = table2.key1
AND table1.key2 = table2.key2
WHERE TO_JSON_STRING((table1.column1, table1.col2)) != TO_JSON_STRING((table2.column1, table2.col2))

Answer 2

В стандартном SQL мы обнаружили, что UNION ALL из двух EXCEPT DISTINCTработает для наших вариантов использования:

(
  SELECT * FROM table1
  EXCEPT DISTINCT
  SELECT * from table2
)

UNION ALL

(
  SELECT * FROM table2
  EXCEPT DISTINCT
  SELECT * from table1
)

Это приведет к различиям в обоих направлениях:

• ряды в table1 что не в table2
• ряды в table2 что не в table1

Примечания и предостережения:

• table1 а также table2 должны иметь одинаковую ширину и столбцы в одинаковом порядке и типе.
• это не работает напрямую с STRUCT или ARRAYтипы данных. Вы должны либоUNNEST, или используйте TO_JSON_STRING чтобы сначала преобразовать эти типы данных.
• это не работает напрямую с GEOGRAPHY либо сначала вы должны преобразовать текст, используя ST_AsText

Answer 3

Вам нужно будет указать числовые столбцы, но быстрое сравнение даст возможность просмотреть их все:

#standardSQL
WITH table_a AS (
  SELECT 1 id, 2 n1, 3 n2
), table_b AS  (
  SELECT 1 id, 2 n1, 4 n2
)


SELECT id
FROM table_a a
JOIN table_b b
USING(id)
WHERE TO_JSON_STRING([a.n1, a.n2]) != TO_JSON_STRING([b.n1, b.n2])