manera eficiente de comparar dos tablas en bigquery

Indagamos en diferentes espacios y así tenerte la respuesta para tu inquietud, si tienes alguna inquietud puedes dejarnos la pregunta y te contestaremos con gusto, porque estamos para ayudarte.

Solución:

En SQL estándar, encontramos usando un UNION ALL de dos EXCEPT DISTINCTfunciona para nuestros casos de uso:

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

UNION ALL

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

Esto producirá diferencias en ambas direcciones:

• filas en table1 que no están en table2
• filas en table2 que no están en table1

Notas y advertencias:

• table1 y table2 debe ser del mismo ancho y tener columnas en el mismo orden y tipo.
• esto no funciona directamente con STRUCT o ARRAY tipos de datos. deberías UNNESTo usar TO_JSON_STRING para convertir estos tipos de datos primero.
• esto no funciona directamente con GEOGRAPHY o bien, primero debe transmitir al texto usando ST_AsText

Primero, quiero mencionar problemas con su consulta original.

Los principales problemas son 1) usar LEFT JOIN ; 2) usando col != 0

A continuación se muestra cómo debe modificarse para capturar realmente TODAS las diferencias de ambas tablas
Ejecute su consulta original y debajo de una, y con suerte verá la diferencia

#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

o simplemente puede intentar ejecutar su versión original y superior contra datos ficticios para ver la diferencia

#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   

En segundo lugar, a continuación simplificará en gran medida su consulta general

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

Puede probarlo con el mismo ejemplo de datos ficticios que el anterior
Nota: en esta solución no necesita elegir columnas específicas, ¡solo compara todas las columnas! pero si necesita comparar solo columnas específicas, aún deberá seleccionarlas como en el ejemplo a continuación

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

Recuerda algo, que tienes la capacidad de parafrasear tu experiencia si descubriste tu incógnita en el momento clavado.

---

---

Utiliza Nuestro Buscador