Как сравнить две таблицы на равенство в BigQuery

Сравните таблицы и извлеките их различия с помощью стандартного SQL

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

В этой статье мы покажем, как сравнивать две (или более) таблицы в BigQuery и извлекать записи, которые различаются (если они есть). В частности, мы покажем, как сравнивать таблицы с одинаковыми столбцами, а также таблицы с разным количеством столбцов.

Во-первых, давайте начнем с создания двух таблиц с некоторыми фиктивными значениями, на которые мы затем будем ссылаться в этом руководстве, чтобы продемонстрировать несколько различных концепций.

-- Create the first table
CREATE TABLE `temp.tableA` (
  `first_name` STRING,
  `last_name` STRING,
  `is_active` BOOL,
  `no_of_purchases` INT
)
INSERT `temp.tableA` (first_name, last_name, is_active, no_of_purchases)
VALUES 
  ('Bob', 'Anderson', True, 12),
  ('Maria', 'Brown', False, 0),
  ('Andrew', 'White', True, 4)



-- Create the second table
CREATE TABLE `temp.tableB` (
  `first_name` STRING,
  `last_name` STRING,
  `is_active` BOOL,
  `no_of_purchases` INT
)
INSERT `temp.tableB` (first_name, last_name, is_active, no_of_purchases)
VALUES 
  ('Bob', 'Anderson', True, 12),
  ('Maria', 'Brown', False, 0),
  ('Andrew', 'White', True, 6),
  ('John', 'Down', False, 0)

Сравнение записей таблиц с одинаковыми столбцами

Теперь, когда мы создали два примера таблиц, вы должны были заметить, что между ними есть несколько различий.

SELECT * FROM `temp.tableA`;

+------------+-----------+-----------+-----------------+
| first_name | last_name | is_active | no_of_purchases |
+------------+-----------+-----------+-----------------+
| Bob        | Anderson  | true      | 12              |
| Andrew     | White     | true      | 4               |
| Maria      | Brown     | false     | 0               |
+------------+-----------+-----------+-----------------+
SELECT * FROM `temp.tableB`;

+------------+-----------+-----------+-----------------+
| first_name | last_name | is_active | no_of_purchases |
+------------+-----------+-----------+-----------------+
| Bob        | Anderson  | true      | 12              |
| Andrew     | White     | true      | 6               |
| Maria      | Brown     | false     | 0               |
| John       | Down      | false     | 0               |
+------------+-----------+-----------+-----------------+

Теперь предположим, что таблица temp.tableB является последней версией некоторого набора данных, тогда как temp.tableA является более старой, и мы хотели бы увидеть фактические различия (с точки зрения записей) между двумя таблицами, все, что нам нужно, это следующий запрос:

WITH
  table_a AS (SELECT * FROM `temp.tableA`),
  table_b AS (SELECT * FROM `temp.tableB`),
  rows_mismatched AS (
    SELECT
      'tableA' AS table_name,
      *
    FROM (
      SELECT
        *
      FROM
        table_a EXCEPT DISTINCT
      SELECT
        *
      FROM
        table_b 
    )
    
    UNION ALL
    
    SELECT
      'tableB' AS table_name,
      *
    FROM (
      SELECT
        *
      FROM
        table_b EXCEPT DISTINCT
      SELECT
        *
      FROM
        table_a 
    )
  )

SELECT * FROM rows_mismatched

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

В наших конкретных примерах таблицы A и B имели разницу в двух записях; Первая запись, похоже, является записью для Andrew White, так как у этого человека другое значение для поля no_of_purchases. Кроме того, в таблице tableB есть одна дополнительная запись, которой нет даже в таблице tableA.

+------------+------------+-----------+-----------+-----------------+
| table_name | first_name | last_name | is_active | no_of_purchases |
+------------+------------+-----------+-----------+-----------------+
| tableB     | John       | Down      | false     | 0               |
| tableB     | Andrew     | White     | true      | 6               |
| tableA     | Andrew     | White     | true      | 4               |
+------------+------------+-----------+-----------+-----------------+

Примечание. Если вы не знакомы с предложением WITH и общими табличными выражениями (CTE) в SQL, обязательно прочитайте следующую статью:

Что такое CTE в SQL
Понимание Common Table Expression (CTE) в SQLtowardsdatascience.com

Сравнение записей таблиц с разными столбцами

Теперь предположим, что вы хотите сравнить записи между двумя таблицами, имеющими разное количество столбцов. Очевидно, нам пришлось бы проводить сравнение яблок с яблоками, что означает, что нам каким-то образом нужно извлечь только общие поля из обеих таблиц, чтобы иметь возможность выполнить значимое сравнение.

Давайте заново создадим наши таблицы, чтобы сгенерировать некоторые несоответствующие столбцы, чтобы мы могли затем продемонстрировать, как справляться с этими случаями:

-- Create the first table
CREATE TABLE `temp.tableA` (
  `first_name` STRING,
  `last_name` STRING,
  `is_active` BOOL,
  `dob` STRING
)
INSERT `temp.tableA` (first_name, last_name, is_active, dob)
VALUES 
  ('Bob', 'Anderson', True, '12/02/1993'),
  ('Maria', 'Brown', False, '10/05/2000'),
  ('Andrew', 'White', True, '14/12/1997')

-- Create the second table
CREATE TABLE `temp.tableB` (
  `first_name` STRING,
  `last_name` STRING,
  `is_active` BOOL,
  `no_of_purchases` INT
)
INSERT `temp.tableB` (first_name, last_name, is_active, no_of_purchases)
VALUES 
  ('Bob', 'Anderson', True, 12),
  ('Maria', 'Brown', True, 0),
  ('Andrew', 'White', True, 6),
  ('John', 'Down', False, 0)

Теперь наши новые таблицы имеют только три общих столбца, а именно first_name, last_name и is_active.

SELECT * FROM `temp.tableA`;

+------------+-----------+-----------+--------------+
| first_name | last_name | is_active | dob          |
+------------+-----------+-----------+--------------+
| Bob        | Anderson  | true      | '12/02/1993' |
| Andrew     | White     | true      | '10/05/2000' |
| Maria      | Brown     | false     | '14/12/1997' |
+------------+-----------+-----------+--------------+
SELECT * FROM `temp.tableB`;

+------------+-----------+-----------+-----------------+
| first_name | last_name | is_active | no_of_purchases |
+------------+-----------+-----------+-----------------+
| Bob        | Anderson  | true      | 12              |
| Andrew     | White     | true      | 6               |
| Maria      | Brown     | false     | 0               |
| John       | Down      | false     | 0               |
+------------+-----------+-----------+-----------------+

Теперь, если мы попытаемся запустить запрос, который мы выполнили в предыдущем разделе, где две таблицы имели одинаковые столбцы, мы получим эту ошибку:

Column 4 in EXCEPT DISTINCT has incompatible types: STRING, INT64 at [13:7]

Это абсолютно нормально, учитывая, что в наших таблицах больше нет соответствующих столбцов. Нам нужно немного изменить наш первоначальный запрос, чтобы самые первые CTE выбирали только взаимные столбцы для каждой таблицы. Наш запрос будет выглядеть следующим образом:

WITH
  table_a AS (
    SELECT 
      first_name,
      last_name,
      is_active
    FROM 
      `temp.tableA`
  ),
  table_b AS (
    SELECT 
      first_name,
      last_name,
      is_active 
    FROM 
      `temp.tableB`
  ),
  rows_mismatched AS (
    SELECT
      'tableA' AS table_name,
      *
    FROM (
      SELECT
        *
      FROM
        table_a EXCEPT DISTINCT
      SELECT
        *
      FROM
        table_b 
    )
    
    UNION ALL
    
    SELECT
      'tableB' AS table_name,
      *
    FROM (
      SELECT
        *
      FROM
        table_b EXCEPT DISTINCT
      SELECT
        *
      FROM
        table_a 
    )
  )

SELECT * FROM rows_mismatched

Таблицы, созданные в этом разделе, имели следующие несоответствия (при рассмотрении только их общих столбцов):

Запись для Maria Brown имеет отличия в столбце is_active
В таблице tableB есть одна дополнительная запись (John Down), которой нет в tableA.

Эти различия можно наблюдать в результатах запроса, представленных ниже:

+------------+------------+-----------+-----------+
| table_name | first_name | last_name | is_active |
+------------+------------+-----------+-----------+
| tableB     | Maria      | Brown     | false     |
| tableB     | John       | Down      | false     | 
| tableA     | Maria      | Brown     | true      | 
+------------+------------+-----------+-----------+

Последние мысли

В этой статье мы предоставили подробное руководство о том, как сравнивать таблицы в BigQuery. Мы подчеркнули важность этой задачи для обеспечения точности и согласованности данных и продемонстрировали несколько методов сравнения таблиц с одинаковыми столбцами, а также таблиц с разным количеством столбцов. Мы также рассмотрели процесс извлечения записей, различающихся между таблицами (если они есть).

В целом, цель этой статьи — предоставить читателям необходимые инструменты и знания для эффективного и действенного сравнения таблиц в BigQuery. Я надеюсь, что вы сочли полезным!

Стать участником и читать все истории на Medium. Ваш членский взнос напрямую поддерживает меня и других писателей, которых вы читаете. Вы также получите полный доступ ко всем историям на Medium.

Присоединяйтесь к Medium по моей реферальной ссылке — Giorgos Myrianthous
Как участник Medium, часть вашего членского взноса идет авторам, которых вы читаете, и вы получаете полный доступ к каждой истории… gmyrianthous.medium.com

Статьи по теме, которые вам также могут понравиться

ETL и ELT: в чем разница?
Сравнение между ETL и ELT в контексте Data Engineeringtowardsdatascience.com

Что такое dbt (инструмент для создания данных)
Нежное введение в dbt, который захватывает мир данныхtowardsdatascience.com

Как сравнить две таблицы на равенство в BigQuery

Сравните таблицы и извлеките их различия с помощью стандартного SQL

Сравнение записей таблиц с одинаковыми столбцами

Сравнение записей таблиц с разными столбцами

Последние мысли

Вопросы по теме