Блог

Я выполняю тестирование качества данных.

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

 select 
count(case when coalesce(x.col1,1) = coalesce(y.col1,1) then null else 1 end) as cnt_col1,
count(case when coalesce(x.col2,"1") = coalesce(y.col2,"1") then null else 1 end) as cnt_col2
from
`DatasetA.Table` x
OUTER JOIN
`DatasetB.Table` y
on x.col1 = y.col1
 

Вывод этого запроса выглядит следующим образом:

 col1, col2
null, null
null, null
1, null
null, 1
 

У меня есть 200 таблиц, для которых мне нужно выполнить этот тест, и их количество cols является динамическим. в приведенной выше таблице всего два столбца, в некоторых — 50.

У меня уже есть запросы к таблицам, но мне нужно объединить результаты всех тестов в один результат. Мой план состоит в том, чтобы преобразовать каждый запрос в единый вывод и объединить их вместе с помощью UNION ALL .

В выходном наборе должно быть указано:

 COLUMN, COUNT_OF_ERRORS
cnt_col1, 1
cnt_col2, 1
...
cnt_col15, 0
 

Мой вопрос заключается в следующем.
Как мне изменить это, чтобы я мог получить результат, который я ищу.

Спасибо

Как мне изменить это, чтобы я мог получить результат, который я ищу.

Предполагая, что у вас есть таблица `данные`

 col1    col2    col3
----    ----    ----     
null    null    null     
null    null    1    
null    1       1    
1       null    1    
1       null    1    
1       null    1   
 

И вам нужно повернуть его в обратном направлении, чтобы

 column      count_of_errors  
--------    ---------------
cnt_col1    3    
cnt_col2    1    
cnt_col3    5    
 

Ниже приведен стандартный SQL BigQuery, который делает именно это

 #standardSQL
WITH `data` AS (
  SELECT NULL AS col1, NULL AS col2, NULL AS col3 UNION ALL
  SELECT NULL, NULL, 1 UNION ALL
  SELECT 1, NULL, 1 UNION ALL
  SELECT NULL, 1, 1 UNION ALL
  SELECT 1, NULL, 1 UNION ALL
  SELECT 1, NULL, 1
)
SELECT r.* FROM (
  SELECT 
    [
    STRUCT<column STRING, count_of_errors INT64>
      ('cnt_col1', SUM(col1)),
      ('cnt_col2', SUM(col2)),
      ('cnt_col3', SUM(col3))
    ] AS row
  FROM `data`
), UNNEST(row) AS r   
 

Это достаточно просто и удобно для настройки на любое количество столбцов, которые у вас потенциально есть в вашей исходной таблице «данных» — вам просто нужно добавить соответствующее количество ('cnt_colN', SUM(colN)), — что можно сделать вручную, или вы можете просто написать простой скрипт для генерации этих строк (или целого запроса)

Что касается «сравнения 2 таблиц» в больших данных, я не думаю, что выполнение некоторых объединений — лучший подход, потому что объединения в целом довольно медленные, а затем вам приходится обрабатывать случай «внешних» соединений строк.

Я работал над этой темой много лет назад (https://community.hortonworks.com/articles/1283/hive-script-to-validate-tables-compare-one-with-an.html ) и теперь я пытаюсь перенести эти знания, чтобы сравнить таблицы Hive с таблицами BigQuery.

Одна из моих основных идей — использовать некоторые контрольные суммы, чтобы быть уверенным, что таблица полностью идентична другой. Вот «базовый пример»:

 with one_string as(
select concat( sessionid ,'|',referrercode ,'|',purchaseid ,'|',customerid ,'|', cast(bouncerateind as string),'|', cast( productpagevisit as string),'|', cast( itemordervalue as string),'|', cast( purchaseinsession as string),'|', cast( hit_time_gmt as string),'|',datedir ,'|',productcategory ,'|',post_cookies) as bigstring from bidwh2.omniture_2017_03_24_v2
),
shas as(
   select TO_BASE64( sha1( bigstring)) as sha from one_string
),
shas_prefix as(
   select substr( sha, 0 , 1) as prefix, sha from shas
),
shas_ordered as(
    select prefix, sha from shas_prefix order by sha
 ),
results_prefix as(
    select concat( prefix, ' ', TO_BASE64( sha1( STRING_AGG( sha, '|')))) as res from shas_ordered group by prefix
),
results_ordered as(
    select 1 as myall, res from results_prefix order by res
)
select SHA1( STRING_AGG( res, '|')) as sha from results_ordered group by myall;
 

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

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

1. подсчитайте строки для некоторых «сегментов» (групп строк, столбец которых с хорошим распределением имеет одинаковую контрольную сумму по модулю большого числа) и сравните результаты (потому что нет необходимости проверять всю таблицу, если количество строк не совпадает).
2. визуально показывает различия, если количество не совпадает
3. используйте метод bucket / rows некоторые другие «сегменты / столбцы», чтобы выполнить некоторые контрольные суммы аналогичным образом, как показано в примере выше. И сравните все эти контрольные суммы вместе.
4. визуально показывает различия, если контрольные суммы не совпадают

Редактировать 11.03.2017: сценарий завершен и его можно найти по адресу: https://github.com/bolcom/hive_compared_bq