Дедупликация BigQuery для двух столбцов в качестве уникального ключа

Мы строго используем BigQuery и имеем две таблицы, которые, по сути, обновлялись параллельно разными процессами. У меня есть проблема: у нас нет уникального идентификатора для таблиц, и цель состоит в том, чтобы объединить две таблицы с нулевым дублированием, если это возможно. Уникальный идентификатор - это два объединенных столбца.

Я пробовал различные запросы на основе MySQL, но, похоже, ни один из них не работает в BigQuery. Итак, я отправляю сюда некоторую помощь. :)

Шаг 1. Скопируйте «чистую» таблицу в новую объединенную таблицу.

Шаг 2. Запросите "грязную" (старую) таблицу и вставьте все недостающие записи.

Попытка запроса 1:

SELECT
  COUNT(c.*)
FROM
  [flash-student-96619:device_data.device_datav3_20160530] AS old
WHERE NOT EXISTS (
  SELECT
    1
  FROM
    [flash-student-96619:device_data_v7_merged.20160530] AS new
  WHERE
    new.dsn = old.dsn
    AND new.timestamp = old.timestamp 
)

Ошибка: ошибка: 6.1–10.65. Одновременно может выполняться только один запрос.

Попытка запроса 2:

SELECT
  *
FROM
  [flash-student-96619:device_data.device_datav3_20160530]
WHERE
  (dsn, timestamp) NOT IN (
  SELECT
    dsn,
    timestamp
  FROM
    [flash-student-96619:device_data_v7_merged.20160530] 
  )

Ошибка: обнаружено "", "", "" в строке 6, столбце 7. Ожидалось: ")" ...

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

Любая помощь?


sql google-bigquery gcp
person Dovy    schedule 18.07.2016    source источник

Ответы (2)


arrow_upward
5
arrow_downward

Что-то вроде ниже должно работать

SELECT * 
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY dsn, timestamp) AS dup
  FROM
    [flash-student-96619:device_data.device_datav3_20160530],
    [flash-student-96619:device_data_v7_merged.20160530] 
) 
WHERE dup = 1

Я рекомендую использовать явный список полей вместо * во внешнем SELECT, чтобы вы могли опустить dup из фактического вывода

person Mikhail Berlyant    schedule 18.07.2016
comment
А как насчет двух столов? Я не хочу выводить дубликаты на саму одну таблицу, а на другую. Думаю, я могу записать в таблицу, а затем переписать в таблицу ... - person Dovy; 19.07.2016
comment
это было направление, которому следовало следовать: o) - в любом случае добавлена ​​вторая таблица - идея состоит в том, чтобы вывести объединенные данные (из обеих таблиц) и записать их в окончательную чистую таблицу. надеюсь, это то, чего ты хотел достичь - person Mikhail Berlyant; 19.07.2016
comment
Ты горишь, мой друг. - person Dovy; 19.07.2016

arrow_upward
1
arrow_downward

Немного поздно, но я хотел бы отметить, что ваш исходный запрос работает с небольшими изменениями с использованием стандартного SQL (снимите флажок «Использовать устаревший SQL» в разделе «Показать параметры»). Мне просто пришлось изменить new на что-то другое, поскольку это зарезервированное ключевое слово. Например, этот запрос действителен:

WITH OldData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([1, 2, 3, 4]) AS x),
NewData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([5, 2, 1, 6]) AS x)
SELECT
  COUNT(*)
FROM OldData oldData
WHERE NOT EXISTS (
  SELECT 1
  FROM NewData newData
  WHERE
    newData.dsn = oldData.dsn
    AND newData.timestamp = oldData.timestamp
);
+-----+
| f0_ |
+-----+
|   2 |
+-----+

Что касается второй попытки, вы можете:

WITH OldData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([1, 2, 3, 4]) AS x),
NewData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([5, 2, 1, 6]) AS x)
SELECT
  *
FROM OldData
WHERE
  STRUCT(dsn, timestamp) NOT IN (
  SELECT AS STRUCT
    dsn,
    timestamp
  FROM NewData);
+-----+---------------------+
| dsn |      timestamp      |
+-----+---------------------+
|   3 | 2016-07-21 11:54:08 |
|   4 | 2016-07-21 10:54:08 |
+-----+---------------------+
person Elliott Brossard    schedule 21.07.2016